Zoho CRM and Books reporting design example

All records are fictional, normalised teaching data created by Turing BI.
The files are not native exports, API responses or a client case study.
There is no live Zoho connection, tested Zoho workspace or report file in this pack.

Reporting basis
- One fictional organisation, DEMO-UK, with assumed GBP amounts.
- Money is integer pence. Divide by 100 to display pounds.
- Open pipeline: sum of Open deals at the 30 September 2026 snapshot, unweighted.
- Invoiced: three Posted invoices issued in September 2026, gross including VAT.
- Allocated: payments allocated to those same invoices by 30 September inclusive.
- Outstanding: invoiced minus allocated for this selected invoice cohort.
- Invoiced is not recognised revenue. Allocated payments are not total bank cash.
- Outstanding is not a full aged-debt balance. August invoices are outside this cohort.
- The model does not cover credit notes, refunds, write-offs, exchange rates or tax reporting.

What to inspect
1. customer-map.csv links two CRM accounts to two Books customers for one organisation.
2. crm-deals.csv contains two September Open deals, a Won deal and an older snapshot.
3. books-invoices.csv contains three September Posted invoices, a Void row and an August row.
4. payment-allocations.csv includes two partial payments for INV-2048 and an October payment.
5. expected-results.csv shows the two customer rows, in pence.

Expected totals in GBP
Open pipeline: 7,000. Invoiced: 2,800. Allocated: 1,500. Outstanding: 1,300.
INV-2048: 1,200 less payments of 600 and 300 leaves 300 outstanding.
The October 400 allocation is excluded at the September cutoff.
The 1,200 Won deal is not added to invoiced or treated as a linked invoice.

Why the table grain matters
Joining INV-2048 straight to its two payment rows repeats its invoice amount:
1,200 + 1,200 = 2,400. Aggregate allocations to invoice first.
Keep pipeline and invoice facts separate; compare their customer aggregates through
the approved mapping. This example does not establish a deal-to-invoice relationship.

Running the included SQL locally
Use an empty SQLite database and import the four input CSVs under these table names:
customer_map, crm_deals, books_invoices, payment_allocations.
Keep identifiers and dates as text, and money fields as integers.
Execute reconcile.sql. It returns the two rows in expected-results.csv.
The repository validator also performs this check with explicit table types.

Applying the design in Zoho Analytics
Zoho's Finance connector help documents adding Finance to a CRM Analytics workspace,
with configurable lookups. Use your own source table and field names and validate
the relationships, account permissions, plan limits and sync history.
The SQL is a local validation example, not a verified Zoho Query Table definition.
The direct cross-application blending documented by Zoho is limited to one Finance
organisation. Multiple organisations need a separately assessed design.

Official setup references, checked 25 October 2026
https://www.zoho.com/analytics/help/connectors/zoho-crm.html
https://www.zoho.com/analytics/help/connectors/zoho-books.html

For commissioned reporting, agree the measure definitions, customer mapping, cutoff,
source reconciliation and user access checks with the people who own the reports.