Brief
Brisk Kitchens is an invented company that designs and installs kitchens in several countries. Customers book a design appointment online, meet a designer, receive a quote and, if they buy, have the kitchen installed weeks later. Leadership hears different numbers each month from marketing, sales and finance, the forecast is a single spreadsheet figure, and nobody can say what paid media adds. A new analyst who writes SQL has joined, and the company has a BigQuery project that few people use.
You lead marketing analytics. The managing director wants a plan that ends the monthly argument, gives finance a forecast it can use in the autumn budget round and answers the paid media question honestly.
Inputs and constraints
Brisk Kitchens: markets, funnel and spend last year
| Market | Currency | Appointments booked online | Attended | Kitchens installed | Average installed value | Median weeks from appointment to install | Paid media |
|---|---|---|---|---|---|---|---|
| UK | GBP | 6,200 | 4,900 | 1,470 | £14,500 | 10 | £1.10m |
| Ireland | EUR | 1,300 | 1,050 | 290 | €15,800 | 11 | €0.26m |
| Netherlands | EUR | 1,700 | 1,250 | 330 | €16,400 | 12 | €0.34m |
Contribution margin on installed kitchens: 30%. Finance recognises revenue at installation.
Current reports and known data problems
| Report | Audience | Built from | Issue |
|---|---|---|---|
| Weekly marketing dashboard | Marketing team | Analytics interface connector | Slow; appointment key events include duplicates |
| Monthly sales report | Sales director | Customer relationship system | Appointments by appointment date, not booking date |
| Monthly board pack | Leadership | Finance ledger | Revenue at installation; no link to marketing |
| Agency reports | Head of marketing | Ad platforms | Attributed appointments add up to 1.7 times the system's total |
- The appointment key event in the analytics property (GA4Google's event-based analytics platform.) fires on form submission and again on calendar confirmation for some visitors.
- The appointment ID is not passed to analytics, so web sessions cannot be joined to sales records.
- Salespeople pick an appointment source from a drop-down; 30% are recorded as "Other".
- Showroom partner links carry UTMURL tags identifying traffic source. values in several spellings.
- Euro spend is converted in a spreadsheet at whatever rate the author chooses.
- The BigQuery export was linked fourteen months ago; one view exists, built by a former contractor; no one owns billing or access.
Team: the analyst; a marketing operations manager who owns the tag container; a part-time finance analyst; an agency running paid media in all three markets. Data tools budget: about £25,000 a year. Tests must avoid the January peak.
Task
Write one plan with these sections, in order. Tie every report, table, test and rule to a decision.
- Decisions first. List the audiences (leadership, finance, sales, marketing, market managers, the agency) and the decisions each makes and how often. Map every current report to a decision, retire what serves none, and set the weekly, monthly and quarterly views from the marketing reporting cadence playbook.
- Data model. Specify the warehouse tables: closed-day events from the export, as the BigQuery export basics guide describes; appointments, attendance, quotes and installs from the customer relationship system; spend by market and currency; a rate table; and reference tables for channels and the campaign taxonomy. State the join key and how the appointment ID reaches analytics. Write governed definitions for appointments, attended appointments, installs, installed revenue and marketing cost following the metrics layer guide, each with a reconciliation note against the system it must match. Revisit the garden centre warehouse report exercise for the layering.
- Dashboards. Name the certified dashboards, their owners, sources and review dates, under the dashboard governance guide, and say which current dashboards are retired.
- Forecasting. Replace the single figure with low, central and high scenarios for appointments by market, built on marginal cost per appointment as the marginal returns and forecasting guide sets out, then converted to installs and installed revenue using attendance, conversion and the lag to installation. Give ranges and say where the data runs out, as in the online optician forecasting exercise.
- Tests finance will see. Using the guide to choosing an attribution approach, assign attribution, IncrementalityConversions caused by marketing. tests or modelling to each decision. Design the first test: channel, market, regions, outcome measured in the sales system, minimum effect worth detecting, duration including the lag, guardrails and decision rule, recorded in the experiment hypothesis template. Say how its result will adjust reports and the forecast.
- Governance. Write the failure log for the known problems, the taxonomy and tagging rules, and the weekly checks with owners and tolerances, following the marketing data quality guide and the coach tour data audit exercise. Verify the fixed appointment tag with the tracking verification checklist.
- Cadence across markets. Set the reporting currency, budget and actual rates, time zone, period and date rules (booking, appointment and installation), the market field and the consolidated layout, extending the monthly marketing report template. Name each KPIA metric tied directly to an objective. on the leadership page and its definition.
- Roadmap. Sequence the work by quarter with owners, putting fixes that unblock everything else first.
Evaluation criteria
Assess your plan against each row, or ask a peer to. A strong plan meets every row.
| Criterion | What a strong answer shows | Common gap |
|---|---|---|
| Decisions first | The decisions each audience makes, with every report mapped to one and the rest retired | Starting from the data available and building every chart |
| Data model | Warehouse tables joining web, sales and spend data on a stated key, with governed definitions and reconciliation notes | Reporting kept on platform interfaces |
| Forecasts and tests | Scenario forecasts with ranges that carry the lag to installation, and scheduled tests that check the key assumptions | Attributed results presented as causal |
| Governance | A taxonomy, weekly checks and owners, so failures are caught before reports go out | Data problems fixed ad hoc after reports go out |
| Cadence across markets | Currency, period, date and consolidation rules with a review rhythm | Each market left to report its own way |
Submission checklist
When you have finished, test the plan against one question: if leadership asks next month what paid media added, which page answers it and how confident can they be? If the plan makes that answer routine, the level has done its job. Return to the intermediate marketing analytics level for the SQL, attribution and finance reporting this plan builds on.
