Zoho Books and Google BigQuery integration

What moves between them

Zoho Books invoices, bills, purchase orders, and payments flow one direction: into BigQuery. ml-connector subscribes to Zoho Books webhook events (invoice.created, invoice.updated, bill.created, bill.updated, purchaseorder.created, purchaseorder.updated, invoice.unpaid, bill.overdue) and streams them into BigQuery tables. If webhooks are unavailable, ml-connector polls the Zoho Books list endpoints on a cadence you set (typically daily or every 4 hours). Because BigQuery is read-only for this integration, no data flows backward into Zoho Books. All records are immutable inserts into BigQuery; updates are appended as new rows with a later timestamp.

How ml-connector handles it

ml-connector stores both credential sets encrypted. On the Zoho Books side, it parses the OAuth2 refresh token, routes all calls to the correct regional base URL (resolved from the region field in the token response), and appends the organization_id query parameter. Access token expiry is tracked proactively so refresh happens before a call fails. When a webhook fires, ml-connector verifies the HMAC signature against the configured secret, parses the JSON payload, maps Zoho Books fields to BigQuery column names, and inserts the row with insertId for deduplication. If the webhook delivery succeeds but BigQuery insert fails, ml-connector queues a retry. If webhook delivery fails (5xx, timeout), Zoho Books retries at its own cadence; ml-connector polls the webhook history endpoint periodically to catch any events that were never delivered. On the BigQuery side, the service account JWT is signed locally and exchanged for an access token that is cached until 5 minutes before expiry, preventing token-fetch delays on hot paths. Streaming inserts use the insertId to prevent duplicates if the same event arrives twice. Every record carries a full audit trail (source event ID, arrival timestamp, BigQuery row ID) and can be replayed if needed.

A real-world example

A growing e-commerce SMB uses Zoho Books for accounting across US and EU operations. The finance team needs to track customer and vendor payments in real time, build custom dashboards in Data Studio, and audit invoice-to-payment timelines across both regions. Before the integration, they exported reports from Zoho Books weekly and uploaded them to Google Sheets for pivot analysis, a manual process that made late payment discovery slow. With Zoho Books and BigQuery connected, each invoice and payment lands in BigQuery automatically, the regional base URL routing is handled transparently, and the finance team runs daily SQL queries to identify overdue invoices and payment patterns. Month-end reporting is now self-serve; the manual export step is gone.

What you can do

• •Stream Zoho Books invoices, bills, purchase orders, and payments into BigQuery via webhook push or polling fallback.
• •Handle Zoho Books OAuth2 with region-specific routing so US, EU, and other regional instances work transparently.
• •Verify webhook signatures and deduplicate events using Zoho Books event IDs and BigQuery insertId.
• •Refresh OAuth2 access tokens proactively and track token expiry so no call fails due to stale credentials.
• •Replay missed webhook events by querying Zoho Books webhook history and backfilling BigQuery.

Questions

How does ml-connector handle Zoho Books' region-specific base URLs?

Zoho Books returns the region (or api_domain) in the OAuth2 token response. ml-connector stores this region credential and routes all API calls to the matching regional base URL (US, EU, India, Australia, Japan, Canada, China, or Saudi Arabia). Each customer organization lives in exactly one region, so routing is transparent and per-customer.

What happens if a webhook delivery fails or the event is never received?

If Zoho Books fails to deliver a webhook, it retries at its own cadence. ml-connector periodically queries the Zoho Books webhook history endpoint (filtered by from_date and to_date) to discover any events that were never delivered to ml-connector. Those events are backfilled into BigQuery so no invoice or payment is ever missed, even if a connection drops.

How does ml-connector prevent duplicate records in BigQuery?

Zoho Books webhooks include a unique event ID that ml-connector stores and uses as the BigQuery insertId. BigQuery's streaming inserts use insertId for best-effort deduplication; if the same event arrives twice, only one row is inserted. The full audit trail (event ID, arrival timestamp) is logged so duplicates can be detected and replayed if needed.