One warehouse for Meta, attribution and every Shopify order

A DTC pet brand ran its daily decisions from a spreadsheet stitched together by hand. We moved Meta, its attribution tool and both Shopify stores into BigQuery, reconciled them to the cent, and found where the numbers it had been trusting were wrong.

  • ClientDTC pet care brand, US market
  • StackTwo Shopify stores, Meta Ads, a multi-touch attribution tool
  • ScaleSeven-figure monthly Meta spend, 1,000+ ads with spend each week
  • Built withBigQuery, Cloud Run, Cloud Scheduler, Python, Meta Marketing API, Shopify GraphQL Admin API
0.000%

difference from the team's own funnel-stage numbers on every settled day checked

900K+

Shopify orders loaded, the full history of both stores

Every 2 hours

refresh, with a nightly re-pull of the last 7 days because platforms restate them

26%

more purchases reported by Meta than the attribution tool credited to Meta

The problem

The brand sells through two Shopify stores: the main store, and a second store that bottom-of-funnel ads send traffic to. It spends seven figures a month on Meta and uses a multi-touch attribution tool to decide which ads get more budget.

Every morning, the head of marketing copied numbers from Meta and the attribution tool into a Google Sheet: spend, revenue and purchases, split into top and bottom of funnel. It worked, but it took time every day, it only covered what fit in a sheet, and nobody could easily check whether the three sources agreed. Numbers for recent days also kept changing after they'd been copied, because Meta and the attribution tool both restate recent data.

The brief: one place where Meta, attribution and Shopify numbers are accurate and consistent for any date range.

What we built

  1. Pipelines into the client's own Google Cloud project. Ad-level daily data from the Meta Marketing API, ad-level attribution data from the attribution tool, and the full order history of both Shopify stores through the GraphQL Admin API. All sources are pulled on New York days, the timezone every platform reports in.
  2. A refresh schedule that copes with restated data. A Cloud Run job refreshes today and yesterday every two hours. A nightly job re-pulls the last 7 days, so late corrections from Meta and the attribution tool land automatically instead of quietly going stale.
  3. Models that match how the team thinks. BigQuery views turn raw data into daily overviews, top vs bottom of funnel tables, and an ad table that reads the team's ad naming convention: format, angle, creative strategist and editor. Ads named before the convention fall back to campaign-level rules.
  4. A reconciliation table. Every day compares Meta spend with attributed spend, and attributed revenue and orders with Shopify. Known data problems are labelled, so nobody draws conclusions from a broken day.
  5. Delivery the team already uses. The daily Google Sheet now rebuilds itself overnight, before the team starts work. A custom dashboard and creative library with video playback are being built on the same warehouse.

What the data showed

The warehouse matches the team's own numbers. On every settled day we checked, the top and bottom of funnel tables match the hand-built sheet to 0.000% on spend, revenue and purchases. That's what made it safe to switch.

Meta was claiming far more purchases than it drove. Over 22 days, Meta reported 26% more purchases than the attribution tool credited to Meta. On 7 of those days, Meta's purchase count was higher than the total orders both stores took from every channel combined. Our guide on why Meta revenue doesn't match Shopify explains why this happens.

The attribution tool had gaps of its own. Checked against Shopify, the attribution tool undercounted orders and revenue by 12 to 24% on a recurring weekday through the first half of the year, and showed extra Meta spend on a handful of days in August. Those days are now labelled in the reconciliation table, and Shopify and Meta are used as the source of truth where they disagree.

The naming convention covers most, but not all, spend. Ads following the convention account for 71% of spend. The rest are older ads or statics, mapped with fallback rules so they still appear in funnel-stage reporting.

Timeline and cost

The first version of the daily numbers, reconciled across all three sources, was delivered the day after we got access. The pipelines were live and reconciled within a week of starting the full build.

Everything runs in the client's own Google Cloud project and accounts, so they own it outright. At this volume it runs within Google Cloud's free tiers, so the running cost is close to zero.

Want the same for your store? See our BigQuery data warehouse service and the guide to getting Shopify data into BigQuery, or read more about Owais, who built it.

Plan your data build.

Tell us which sources you use and which decisions you want the data to drive. We'll reply within one business day with questions, a proposed architecture and a fixed quote.

Plan a custom data build