Why this matters
A warehouse report is only worth building if people trust it more than the interface they already use. That trust comes from three decisions: defining sales the way finance does, explaining in advance why the figures will not match the interface, and keeping the dashboard fast enough that nobody drifts back. The BigQuery export basics guide covers the schema and the daily tables, and the diagnosis of an export that does not match the interface covers the mechanics of each gap. Here the job is the reporting design around them.
The chain and its sampled reports
Bramblecote Garden Centres is an invented chain of garden centres with an online shop offering home delivery and click-and-collect. The trading director builds a weekly view by channel and category in interface explorations, which sample at busy times and hide small categories.
Bramblecote Garden Centres: the data as it stands
- Analytics property (GA4Google's event-based analytics platform.) in the UK time zone, exporting daily to BigQuery since last spring.
- Finance's trading week runs Sunday to Saturday; sales are reported net of VAT and excluding delivery.
- Purchase events carry revenue including VAT and delivery, plus tax and shipping values and a fulfilment parameter.
- Item rows carry a category set by the site template; 14% are blank or use retired names such as "Plants" and "plants-and-bulbs". A product catalogue table with item ID, category and VAT rate lands in BigQuery nightly.
- Online returns send a refund event; click-and-collect orders cancelled in store do not.
- Email links use the UTMURL tags identifying traffic source. medium values "email", "Email" and "newsletter".
- The export grows by about 1.2 GB a day. The planned dashboard has 6 charts, defaults to the last 13 weeks and is opened by 14 managers every Monday morning.
One closed week, for orientation:
| Source | Orders | Revenue |
|---|---|---|
| Interface | 1,840 | £101,200 including VAT and delivery |
| Online shop platform | 1,905 | £104,800 gross; £83,300 net of VAT, excluding delivery |
From raw events to a trusted summary table
Build in layers. Reference tables hold the rules that change by decision: the channel mapping and the product catalogue. Fact tables hold one row per order and per order line from closed daily tables, with the rules applied once. A small report table holds exactly the grain the dashboard shows. The SQL for marketers guide covers joins and grain; the decision here is which rules live where, so that a change to one rule is made once.
Task
- Specify the tables, their grain, sources and key logic, from raw events to the report table.
- Define net sales, orders, average order value and category for the report, and say which figures add up across categories and which do not.
- List every reason the report will differ from the interface, with the sentence the trading director will see.
- Design the refresh schedule and dashboard settings, and estimate data scanned with and without the summary.
Worked answer
Tables.
| Layer | Table | Grain | Key logic |
|---|---|---|---|
| Reference | Channel map | Source and medium | Lower-cases both; maps every email variant to Email; owned by marketing operations |
| Reference | Products | Item ID | Catalogue category and VAT rate; the only source of category |
| Fact | Orders | Transaction | Purchase and refund events from daily tables; session last-click source; revenue net of tax and shipping; refunds netted to the order; fulfilment type |
| Fact | Order lines | Transaction and item | Unnested items joined to products by item ID, never by the site's category text |
| Report | Weekly sales | Trading week, channel, category | Net sales, orders, order lines; partitioned by week |
Definitions. Net sales are item revenue net of VAT, excluding delivery, after refunds: finance's basis, so the report reconciles with the ledger. Orders are distinct transaction IDs. Average order value (AOVAverage revenue per order.) is net sales divided by orders, shown in total and by channel only. One order often spans several categories, so the category view shows net sales and order lines; "orders containing a category" would not add up to total orders.
Differences from the interface.
| Reason | Note shown on the report |
|---|---|
| Sales basis | Sales here are net of VAT and delivery, as finance reports them; the interface shows gross revenue |
| Channel credit | Channels use the session's last-click source; the interface may spread credit with a model |
| Category source | Categories follow the product catalogue, not what the site sent |
| Week boundaries | Weeks run Sunday to Saturday, the trading week |
| Exactness | Small categories appear here that the interface hides, and nothing is sampled |
| Modelled data | The interface may add modelled orders where consent was declined; this report counts recorded orders |
| In-store cancellations | Not yet sent to the warehouse; known gap until the shop sends them |
Put these on a notes page and a weekly reconciliation row against the shop platform, with the tolerance written down.
Refresh and dashboard. A scheduled query runs each morning once yesterday's daily table exists, retrying later if it does not, and rebuilds the last few days so late refunds land on their orders. The report table is rebuilt after the Saturday table arrives. The dashboard reads only the report table, with data freshness set to half a day, owner's credentials so viewers share the cache, a default of the last complete trading week with comparison, and one question per page, as the guide to Looker Studio performance and caching recommends.
Data scanned on a Monday morning
Raw events: 13 weeks × 7 days × 1.2 GB ≈ 109 GB per chart if every column is read; 6 charts × 14 managers = 84 queries, about 9.2 TB. Selecting columns cuts it, but every open still scans raw rows.
Report table: two years of weeks × about 8 channels × about 25 categories ≈ 20,800 rows, a few megabytes; 84 queries scan well under 1 GB, and cached opens scan nothing.
Common mistakes
- Copying the interface's revenue. Gross revenue cannot reconcile with finance; define the basis first.
- Trusting the site's category text. Blank and retired names fragment the report; join to the catalogue.
- Average order value by category. Orders span categories; the figure misleads.
- Explaining gaps after someone asks. Publish the notes with the first report.
Test your judgement in the warehouse reporting checkpoint, or revisit SQL and metric layers at the intermediate marketing analytics level.
