ZOHO CRM TO LOOKER STUDIO: SYNTHETIC SHEETS EXAMPLE
Prepared 17 October 2026. Fictional records and amounts; no client data or live connection.

Purpose
A six-deal current-snapshot report. Use the prepared CSV in Google Sheets, then
connect that worksheet to Google's reporting tool. Current Google documentation
calls it Data Studio; the guide retains the familiar Looker Studio query wording.
This download is a data fixture, not a published Google report or connector.

Files
zoho-crm-export.csv: illustrative selected export columns, six invented records.
Actual CRM labels, fields and exports vary by organisation; map your own export.
stage-map.csv: our teaching mapping to open/won/lost, not universal Zoho statuses.
normalise-zoho-export.py: optional Python 3 standard-library local transformation.
looker-studio-deals.csv: prepared reporting rows for a single Sheets worksheet.
expected-results.csv: independent expected controls for this fictional snapshot.

Run locally
Read normalise-zoho-export.py, then run python3 normalise-zoho-export.py in this
folder. It reads local files only and rewrites looker-studio-deals.csv. It rejects
duplicate/blank deal IDs, unmapped stages, missing/non-finite/negative amounts,
unsupported currency and invalid dates. It does not fetch Zoho data or stage history.

Google Sheets and Looker Studio steps
Create a private Google Sheet. Import looker-studio-deals.csv into a new worksheet
using File > Import. Keep the same header row and one row per deal. Preserve IDs
as text in a live export, especially long numeric CRM IDs. Avoid merged cells and
total rows. Store amount as numeric, closing_date as Date and snapshot_at as Text.
Open Looker Studio/Data Studio > Create > Data Source > Google Sheets, select the
spreadsheet and worksheet, use the first row as headers, then Connect.
Set deal_id, owner_id, currency, stage, deal_status and snapshot_at to Text;
amount to Number and closing_date to Date. Make COUNT_DISTINCT(deal_id) a metric
and use SUM(amount) for value. Add filters for currency GBP and deal_status open
on the pipeline count/value cards. Filter won and lost on separate value cards.
Add a deal table and compare six rows and GBP 50000 with the control file.
Compare the same snapshot, status mapping, sharing basis and filters in CRM.
For this example, leave date filtering off: Closing Date is the selected current
CRM field, not verified stage-transition, invoice, cash or recognition history.

Expected figures
Open deals: 3; open value: GBP 30,000; won deals: 2 / value: GBP 15,000;
lost deals: 1 / value: GBP 5,000; closed deals: 3; current closed-deal win rate: 2/3 (66.67%).
This win rate is a status-based snapshot calculation, not a period/cohort KPI.
The six-row total GBP 50,000 is a reconciliation control, not pipeline or revenue.
Use COUNT_DISTINCT(deal_id), rather than a sum of identifiers. Compute ratios from
summed counts rather than averaging owner/stage percentages. Values are current
deal amounts; they do not establish invoices, recognised revenue or cash receipts.

Updating and sharing
For a manual route, replace the prior snapshot rows in the same worksheet and
record the new snapshot timestamp. Appending snapshots changes the grain and will
repeat deals unless the report selects one snapshot. Looker Studio data freshness
controls read the Sheet; they do not re-export or synchronise Zoho CRM.
Choose owner/viewer credentials and restrict the underlying Sheet/report sharing
according to your team's access rules. Do not publish live CRM exports publicly.
A live API path needs authorised access, field mapping, pagination, incremental
upserts/deletion handling, ownership and monitored scheduling, scoped separately.

Validation boundary
Local transformation, fixtures and arithmetic were checked. No authenticated
Zoho/Google extraction, interactive Sheet import, Looker Studio chart execution,
sharing test or scheduled refresh was executed. Validate those with your own
accounts before relying on a production report.
