One clean model every channel feeds into.
Shopify, Amazon, your ad platforms and GA4, pulled on a schedule into a single documented BigQuery dataset. It's the foundation every dashboard and every AI insight sits on — and it lives in your Google Cloud account, not mine.
analytics_prod.orders_fact
| Field name | Type | Mode | Description |
|---|---|---|---|
| order_date | DATE | REQUIRED | partition key |
| channel | STRING | REQUIRED | shopify | amazon |
| order_id | STRING | REQUIRED | deduplicated |
| gross_revenue | NUMERIC | REQUIRED | before returns |
| net_revenue | NUMERIC | REQUIRED | after returns |
| cogs | NUMERIC | NULLABLE | joined from cogs_daily |
| ad_spend_alloc | NUMERIC | NULLABLE | allocated by channel |
| contribution_margin | NUMERIC | NULLABLE | computed |
| sku | STRING | REQUIRED | joined to product_dim |
| customer_id | STRING | NULLABLE | hashed |
| Field name | Type | Mode | Description |
|---|---|---|---|
| spend_date | DATE | REQUIRED | partition key |
| platform | STRING | REQUIRED | meta | google | tiktok |
| campaign_id | STRING | REQUIRED | |
| campaign_name | STRING | NULLABLE | |
| spend | NUMERIC | REQUIRED | |
| impressions | INTEGER | NULLABLE | |
| clicks | INTEGER | NULLABLE | |
| platform_conversions | NUMERIC | NULLABLE | as the platform counts them |
| Field name | Type | Mode | Description |
|---|---|---|---|
| sku | STRING | REQUIRED | primary key |
| product_title | STRING | REQUIRED | |
| category | STRING | NULLABLE | |
| unit_cost | NUMERIC | REQUIRED | from cogs_daily |
| launched_at | DATE | NULLABLE | |
| active | BOOL | REQUIRED |
| Field name | Type | Mode | Description |
|---|---|---|---|
| customer_id | STRING | REQUIRED | hashed, primary key |
| first_order_at | DATE | NULLABLE | |
| orders_count | INTEGER | REQUIRED | |
| lifetime_revenue | NUMERIC | REQUIRED | |
| acquisition_channel | STRING | NULLABLE | first touch |
| Field name | Type | Mode | Description |
|---|---|---|---|
| raw_source | STRING | REQUIRED | |
| raw_medium | STRING | NULLABLE | |
| channel | STRING | REQUIRED | the name used everywhere else |
| paid | BOOL | REQUIRED |
| Field name | Type | Mode | Description |
|---|---|---|---|
| cost_date | DATE | REQUIRED | partition key |
| sku | STRING | REQUIRED | |
| unit_cost | NUMERIC | REQUIRED | |
| landed_cost | NUMERIC | NULLABLE | incl. freight and duty |
SELECT order_date, channel, sku, SUM(net_revenue) AS revenue, SUM(cogs + ad_spend_alloc) AS cost, SAFE_DIVIDE( SUM(net_revenue - cogs - ad_spend_alloc), SUM(net_revenue)) AS contribution_margin FROM analytics_prod.orders_fact WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 400 DAY) GROUP BY 1, 2, 3
| order_date | channel | order_id | net_revenue | cogs | margin |
|---|---|---|---|---|---|
| 2026-06-09 | shopify | SH-88412 | 1,248.00 | 512.30 | 31.4% |
| 2026-06-09 | amazon | AM-31907 | 864.50 | 402.10 | 28.9% |
| 2026-06-08 | shopify | SH-88361 | 2,110.75 | 881.20 | 33.1% |
| 2026-06-08 | shopify | SH-88344 | 648.00 | 254.60 | 30.2% |
| 2026-06-08 | amazon | AM-31882 | 1,392.20 | 640.80 | 27.6% |
| 2026-06-07 | shopify | SH-88290 | 978.40 | 388.90 | 32.0% |
Example output · illustrative data · runs in your own accounts
You'll get the most out of this if…
- You're pulling numbers from four or more platforms and they never agree.
- Someone rebuilds the same spreadsheet every Monday from CSV exports.
- You've outgrown platform-native reporting but nobody wants to own a warehouse.
- You want the data in your own cloud, not locked inside another SaaS tool.
Exports aren't a data strategy.
Most ecommerce reporting starts the same way: someone downloads a CSV from Shopify, another from Meta, another from Amazon Seller Central, and joins them by hand in a spreadsheet. It works until the business gets big enough that it takes a day, and by then the month it describes is already over.
The deeper problem is that nothing reconciles. Each platform counts a conversion its own way and none of them know about the others, so there is no single figure anyone can defend in a meeting. Decisions get made on whichever number was open at the time.
A warehouse fixes the cause rather than the symptom. Every source lands raw, gets modelled once into a schema you can point at, and every report downstream reads from that one place.
Delivered, not described.
Every source connected
Shopify, Amazon SP-API, Meta Ads, Google Ads, TikTok Ads, GA4, Klaviyo and QuickBooks where relevant. Read-only access throughout.
Scheduled ingestion
Automated pulls on a schedule you choose. Raw data lands untouched, so the original figures are always recoverable.
A modelled layer
Raw becomes staging becomes fact tables. Deduplicated, blended, and documented — one schema every channel agrees on.
Historical backfill
As far back as each platform's API allows, so you have a real baseline rather than starting from today.
Documentation and handover
Every table and every metric definition written down. You can hand it to another analyst and they'll understand it.
What actually happens.
Access
You give read-only API access and a Google Cloud project. I'll create one if you don't have it.
Audit
Week one is finding where your numbers currently disagree and why. You get that written up either way.
Build
Ingestion, staging, fact tables. You'll see the first unified view within days, not at the end.
Handover
Documentation, access, and a walkthrough. It's your warehouse from that point on.
He really is the Looker Studio Guru that he says he is. The only person I found that could help me figure out why those issues were happening.
VERIFIED CLIENT REVIEW · UPWORK · JAN 2024
Asked on nearly every call.
Why BigQuery and not a managed reporting tool?
Because it's yours. A managed tool rents you a view of your own data and stops the day you stop paying. BigQuery sits in your Google Cloud account, costs very little at ecommerce data volumes, and every other tool can read from it.
What does it cost to run?
For most single-brand setups, storage and query costs land in single-digit dollars a month. I'll size it against your actual data volumes before you commit to anything.
Do we need an engineer?
No. That's the point. You provide access; everything else is on me, including the maintenance if you take the monthly retainer.
What happens if we stop working together?
Nothing breaks. The warehouse, the pipelines and the documentation are in your account and stay there.
Want this on your own numbers?
Fifteen minutes, no pitch. Bring the figure you least trust and I'll tell you what's likely behind it.
Book a 15-minute call