From One Fragile Table to a Tested Data Platform: Modernizing Creator-Commerce Analytics
By rebuilding a fragile, hand-maintained setup as a tested and automated platform in the client's own Google Cloud, DSC gave this creator-commerce brand reporting its team can trust and run.
The Challenge
Our client, a creator-led e-commerce brand that launches limited-run products with online creators, ran its analytics on a fragile, hand-maintained foundation. Dozens of legacy pipelines were at their usage limit, key sources were years out of date, and every executive report and dashboard depended on a single manually maintained table.
They needed a platform their small in-house data team, one data lead and an analyst, could trust, own and run. Over two phases, DSC rebuilt it as a modern, tested and automated data platform in the client's own Google Cloud.
Before DSC, the client's analytics foundation carried four critical risks:
Every executive view and dashboard depended on one hand-maintained intermediate table, with no version control, no tests and no documentation.
26 legacy pipelines had hit their event ceiling. The AirTable connector behind creator and campaign operations had been broken for over two years, and ad and email data were months to years stale.
More than 6 TB of storefront product-page events, along with creator and campaign operations data, had never been modelled for analysis.
Marketing's attribution tool and the warehouse disagreed on headline metrics like “Marketing % of Sales,” leaving leadership unsure which figure to trust.
Key Pain Points
A Single Point of Failure
Ingestion at Its Limits
Stranded Data
Conflicting Numbers
Business Goal
No Single Point of Failure
Retire the single hand-maintained table behind every executive report and dashboard
Reliable Ingestion
Replace legacy pipelines with managed, reliable ingestion for every key source
Complete Data Coverage
Model stranded storefront and creator operations data for the first time
Trusted Numbers
Reconcile conflicting marketing numbers into figures leadership can trust
Team Ownership
Hand over a platform the client's own data team can confidently run
Tech Stack
What We Did
Approach
Rather than patch the legacy setup, DSC rebuilt it in layers, starting with a 3-week architecture audit followed by a build of roughly 12 weeks:
1. Architecture Audit
- A full inventory of 35 datasets and 26 pipelines, a gap assessment and a target architecture.
- Proof that every source could be loaded before the build began.
2. Data Source & Ingestion
- Fivetran manages ingestion for Shopify, HubSpot, Klaviyo, Triple Whale, Zendesk and Google, Meta and TikTok Ads, with full history backfilled.
- A custom AirTable loader on Cloud Run restored creator and campaign data, and storefront events now flow in from PostHog.
- Everything runs in the client's own Google Cloud project, with connectors and infrastructure defined in Terraform.
3. Data Modeling
- A dbt transformation layer on BigQuery replaced the hand-maintained table entirely.
Modeling Approach
Data moves from staging to core models (orders, order lines and products), then into marts for campaigns, customers, creators, marketing and revenue operations.
Key Elements
- 90 dbt models in production, all version-controlled
- 177 automated tests and 26 source-freshness checks on every daily run
- 38 GitHub pull requests, each reviewed and merged by the client's data lead
4. Orchestration & Quality
- Hourly and daily runs on Cloud Run and Cloud Scheduler keep every model current.
- Cloud Monitoring sends an email alert whenever any pipeline job fails.
Validation Approach
Not “looks right,” but proven. Every dashboard was compared value for value with its legacy version before switch-over, with differences explained down to the order level.
- 3.65M: Shopify orders reloaded back to 2018, with 99.97% parity
- Within 0.4%: Executive customer KPIs against legacy reporting
- Exact: Apparel dashboard and platform traffic metrics
5. Dashboards & Reverse ETL
- Platform, Campaign Performance, Apparel and a partner-facing dashboard moved onto the new layer in Looker Studio, with Executive reporting following.
- Live sales and locked payout figures for every creator deal are ready to sync into HubSpot through Fivetran Activations.
6. Deployment & Handover
- Handoff documentation, an operating walkthrough and a change-tracking workflow, with all code in the client's own repositories.
- The client's data lead reviewed the work throughout and ran production operations herself, so the handover was a formality.
- A final validation run caught an edge case, orders deleted in Shopify leaving stray lines, and it was fixed before automated testing went live.
Proof Point: A Dashboard Rebuilt in a Day
On the final day, the client asked DSC to take over their main platform dashboard. DSC rebuilt it on the new layer with the same columns, so no charts broke, validated it against the old version, and the client switched over the next morning. Data processed dropped from about 3.8 TB per load to one 24 GB daily build.
Outcomes
Single Point of Failure Retired
Every report now comes from version-controlled, tested models instead of one hand-maintained table.
10 Sources, One Trusted Foundation
Commerce, CRM, email, support, advertising, creator operations and storefront data now live in one platform, with full history.
Dashboards Validated Before Switch-Over
Apparel and traffic metrics matched exactly, and executive customer KPIs landed within 0.4% of legacy reporting.
Production-Ready From Day One
At handover, the full production run passed all 90 models, 177 tests and freshness checks, with failure alerts live for the client's team.
A Team That Can Run It
The client's data lead reviewed and merged every change, ran production operations herself and now owns the platform.
