The e-commerce company had tripled revenue in two years — but its reporting hadn’t moved past copy-paste. A data analyst spent two full days every week pulling numbers from five platforms into Google Sheets and emailing PDFs. We replaced the whole workflow with an automated pipeline that refreshes revenue every 15 minutes and costs about $120/month to run.
TL;DR
- Client: A fast-growing e-commerce business, revenue up 3× in two years, reporting still manual.
- Problem: ~2 analyst-days/week of spreadsheet wrangling across five platforms, week-old data, and formulas that broke on every new product category.
- Result: Reporting fully automated (~100+ analyst-hours/year reclaimed), revenue data fresh to 15 minutes, all on ~$120/month of GCP — and the cross-department revenue discrepancy eliminated.
The Problem
Data lived in five systems — Shopify, GA4, Meta Ads, Stripe, Zendesk — each with its own API, format, and definition of “revenue” (Shopify counted gross; Stripe counted net after fees). The Google Sheet tried to reconcile them and got it wrong half the time. By the time leadership saw the numbers, they were a week old — and nobody trusted them because the analyst’s formulas broke whenever someone added a product category.
What Was at Stake
A company scaling this fast was steering on a week-old, frequently-wrong dashboard. Ad budgets, inventory buys, and pricing were being decided against numbers that didn’t reconcile — and the single analyst holding it together was a bus-factor of one. The manual process also simply wouldn’t survive the next holiday peak; volume was about to break it.
The Approach
Why GCP. The company was already on Google Workspace. BigQuery’s serverless model meant no clusters to scale or patch — pay per query, scale to zero when idle. Cloud Functions gave event-driven ETL for fractions of a cent per run.
- Extraction — one Cloud Function per source: authenticate via Secret Manager, pull incremental data using watermark timestamps, land raw JSON in Cloud Storage, log to Cloud Logging. Scheduled per source (Shopify/Stripe every 15 min, GA4/Meta daily, Zendesk hourly).
- Transformation — currency normalization to USD, a single reconciled revenue definition (gross − refunds − discounts, before processor fees), customer identity resolution across Shopify/Stripe/Zendesk, and GA4 attribution modeling. Output lands in BigQuery as date-partitioned, category-clustered tables.
- Data marts — four department-specific materialized views (Sales, Marketing, Operations, Support) that auto-refresh when upstream tables update.
- Dashboards — Domo (their existing license, non-technical-friendly): a daily executive scorecard, drill-through deep-dives, and automated alerts on conversion drops, ad-budget overruns, and ticket spikes.
- Infrastructure as code — the entire pipeline in Terraform, so onboarding a new e-commerce client is clone-repo, swap-credentials,
terraform apply— under an hour.
The Results
- Reporting time: ~2 days/week manual → fully automated (100+ analyst-hours/year reclaimed)
- Data freshness: weekly snapshots → 15-minute refresh for revenue
- Cost: ~$120/month total GCP spend (Functions + BigQuery + Storage)
- Accuracy: cross-department revenue discrepancy eliminated entirely
- Scalability: same pipeline absorbed 10× holiday-peak order volume with no changes
What I’d Do Differently
The reconciled revenue definition should have been the first deliverable, not a transformation-layer detail. Most of the “nobody trusts the numbers” pain was a business-definition problem, not an engineering one — and agreeing on that single definition with finance up front would have de-risked everything downstream. I’d also add data-contract tests at the extraction layer earlier; the formulas that “broke on new categories” were really schema-drift going undetected.
The Stack
| Layer | Tool | Why |
|---|---|---|
| Extraction | GCP Cloud Functions | Serverless, event-driven, per-source isolation |
| Orchestration | Cloud Scheduler | Cron triggers, no servers |
| Staging | Cloud Storage | Raw JSON landing zone, lifecycle policies |
| Warehouse | BigQuery | Serverless, pay-per-query, partitioning/clustering |
| Transformation | Cloud Functions + SQL | Light transforms in Python, heavy joins in SQL |
| Data Marts | BigQuery materialized views | Auto-refresh, department schemas |
| Dashboards | Domo | Non-technical preference, drag-and-drop |
| IaC | Terraform | Reproducible, version-controlled infra |
Simba Hu helps companies make better decisions with data and AI — from strategy to implementation. Based in Tokyo, serving clients globally. Book a strategy call or visit simbahu.com.