What this checkpoint covers
Inkhollow Print Studio is an invented online print shop with a trade account for small businesses. Its analytics property (GA4Google's event-based analytics platform.) exports to BigQuery, and a new analyst is moving monthly reporting onto the warehouse. The questions draw on the BigQuery export basics guide, the diagnosis of an export that does not match the interface and the guide to Looker Studio performance and caching.
Task
- Answer every question with a reason before reading on; a right action with a wrong reason is a miss.
- Reread the linked page behind every miss, confident ones first.
Questions
- The marketing manager wants to retire every interface report and use only the warehouse, including for ad cost and channel credit. What does the warehouse give her that the interface does not, and what will she lose?
- Finance asks for "customers this month" and the analyst offers distinct user identifiers from the export. What is wrong with that, and what should the board pack carry?
- A channel report built from the export shows paid search far smaller than the interface does, especially for campaigns that started this month. The query reads the user-level traffic source record. What has happened?
- Every chart on the new dashboard queries the raw event tables, which is slow, and the analyst has had a warning about query volume. What do you build?
- Trade sales staff want today's orders during a launch week; the board pack needs closed monthly figures. How do you set up sources, freshness and refresh for each?
- Partner links arrive with UTMURL tags identifying traffic source. sources spelled several ways, and the analyst groups them with a regular-expression calculated field on every chart. What do you change?
- Using the example below, the finance director asks why the warehouse figure is lower than the one marketing used last month. What do you say?
Inkhollow Print Studio: one closed month
| Source | Orders | Revenue net of VAT |
|---|---|---|
| Interface report | 2,410 | £48,200 |
| Warehouse report | 2,350 | £46,900 |
| Shop platform | 2,470 | £49,100 |
Consent for analytics is declined by a meaningful share of visitors; the interface includes modelled data. The warehouse report reads closed daily tables only and counts each order once by transaction ID.
Answers explained
- Exact, joinable events; she loses modelled and linked views. The export holds every event with no thresholds or sampling, and joins to cost, margin and customer data. The interface adds modelled data, attribution-model credit and linked ad cost, none of which arrive in the event export; cost needs its own feed. See why export raw events.
- Identifiers are devices and browsers, not customers. One customer on two devices counts twice, and the interface's users are active users with estimation on top, so neither source counts customers. Customers come from the order system; report analytics users only as a traffic measure, labelled. See sessions and users counted differently.
- The wrong scope. The user-level record holds the first source that ever brought each user, so returning users' paid search sessions are credited to their original source. For a channel report use the session's last-click record, and name the scope in the output. See source read at the wrong scope.
- Summary tables at the dashboard's grain. A scheduled query aggregates closed days into small tables by date, channel and product, and every chart reads those: faster, cheaper, and the definitions live in one place. See moving to a warehouse source.
- Two sources with different jobs. The board pack reads summary tables with a long freshness window, owner's credentials and a shared default period, so opens are cached. The launch view reads the streaming intraday table on its own page, labelled provisional and never used for closed figures. See daily vs streaming and caching behaviour.
- Map once, upstream. A regular expression on every chart runs on every query and drifts between charts. Put a source-and-medium mapping table in the warehouse, join it in the summary query, and fix the link builder so new variants stop arriving. See blends, calculated fields and filters.
- Three figures, three jobs, one tolerance. Revenue for any financial KPIA metric tied directly to an objective. comes from the shop platform. The interface adds modelled orders, so it sits above the warehouse; the warehouse counts recorded orders exactly and sits below the platform by orders whose tags never ran. State the tolerance, show the ratio each month, and investigate only when it moves. See prevention: reconciliation kept with the project.
Question seven worked through
Warehouse against platform: 2,350 ÷ 2,470 ≈ 95% of orders and £46,900 ÷ £49,100 ≈ 95.5% of revenue. Interface against platform: 2,410 ÷ 2,470 ≈ 98% of orders. If the recorded tolerance is 94% to 97% for the warehouse, this month is inside it and the explanation is the standing one. A learner with six or seven right moves on; four or five rereads the pages behind the misses.
Where to go next
If questions two, three or seven caught you out, redo the warehouse sales report exercise for a garden centre chain. Otherwise move on to forecasting with the spend scenarios exercise for an online optician, and keep the intermediate marketing analytics level to hand.
