Query Builder & Billing Recon

Billing truth lived in two places that never agreed: a SQL-backed internal Query Builder API holding subscriptions, pricing, and loan/AUM data, and HubSpot, where revenue was supposed to be tracked. Worse, the platform data was sharded across four geographic data centers, so no single query could see the whole company at once.

This system stitches it all back together. It federates the four data centers into one unified dataset, reconciles that against HubSpot, and writes 15+ analysis tabs to Google Sheets — turning a manual, error-prone revenue audit into a repeatable, single-pane report.

The system runs as a Google Apps Script project that talks directly to the internal Query Builder API. Because each tenant lives on its home data center, the script fans out across all four servers and automatically unions the results — handling the non-obvious case where some tables (like role assignments) only exist on a tenant's home server.

Authentication uses SHA1-HMAC request signing, with a workaround for an API bug that corrupts the underlying SQL when a parameter is empty. Rather than lean on SQL's LEFT-JOIN — which multiplies rows — pricing is resolved with a three-query join performed in GAS, cascading preferred → country → default to land on the correct final price. Every call is wrapped in retry-with-backoff and automatic pagination, and 11 modules feed the 15+ reconciliation tabs.