Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics and Reporting › Advanced

Building a Warehouse Sales Report for a Garden Centre Chain

An invented garden centre chain wants weekly online sales by channel and product category from its warehouse rather than the analytics interface. You specify the reference, fact and report tables, define revenue, orders and categories on the basis finance uses, write the plain explanation for every way the report will differ from the interface and design the refresh and dashboard settings that keep it fast and cheap.
ExerciseAdvanced levelModule 1: Warehouse data for reporting5 minUpdated

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.

Illustrative example — not a real result

Bramblecote Garden Centres: the data as it stands

  • Analytics property (GA4) 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 UTM 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:

SourceOrdersRevenue
Interface1,840£101,200 including VAT and delivery
Online shop platform1,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

  1. Specify the tables, their grain, sources and key logic, from raw events to the report table.
  2. Define net sales, orders, average order value and category for the report, and say which figures add up across categories and which do not.
  3. List every reason the report will differ from the interface, with the sentence the trading director will see.
  4. Design the refresh schedule and dashboard settings, and estimate data scanned with and without the summary.

Worked answer

Tables.

LayerTableGrainKey logic
ReferenceChannel mapSource and mediumLower-cases both; maps every email variant to Email; owned by marketing operations
ReferenceProductsItem IDCatalogue category and VAT rate; the only source of category
FactOrdersTransactionPurchase and refund events from daily tables; session last-click source; revenue net of tax and shipping; refunds netted to the order; fulfilment type
FactOrder linesTransaction and itemUnnested items joined to products by item ID, never by the site's category text
ReportWeekly salesTrading week, channel, categoryNet 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 (AOV) 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.

ReasonNote shown on the report
Sales basisSales here are net of VAT and delivery, as finance reports them; the interface shows gross revenue
Channel creditChannels use the session's last-click source; the interface may spread credit with a model
Category sourceCategories follow the product catalogue, not what the site sent
Week boundariesWeeks run Sunday to Saturday, the trading week
ExactnessSmall categories appear here that the interface hides, and nothing is sampled
Modelled dataThe interface may add modelled orders where consent was declined; this report counts recorded orders
In-store cancellationsNot 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.

Illustrative example — not a real result

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.

Pages this lesson applies

Terms used on this page

Also related

Related