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

Marketing Analytics and Reporting › Advanced

Capstone: An Analytics and Reporting Plan for a Kitchen Installer

The capstone of the advanced marketing analytics level asks you to write a year's analytics and reporting plan for an invented kitchen company that sells across borders and installs weeks after the first appointment. It brings together decision-led reporting, a warehouse model joining web, sales and spend data, scenario forecasts, incrementality tests finance will see, data governance and a reporting rhythm across markets.
Capstone projectAdvanced levelModule 3: Data quality and reporting at scale5 minUpdated

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

Illustrative example — not a real result

Brisk Kitchens: markets, funnel and spend last year

MarketCurrencyAppointments booked onlineAttendedKitchens installedAverage installed valueMedian weeks from appointment to installPaid media
UKGBP6,2004,9001,470£14,50010£1.10m
IrelandEUR1,3001,050290€15,80011€0.26m
NetherlandsEUR1,7001,250330€16,40012€0.34m

Contribution margin on installed kitchens: 30%. Finance recognises revenue at installation.

Illustrative example — not a real result

Current reports and known data problems

ReportAudienceBuilt fromIssue
Weekly marketing dashboardMarketing teamAnalytics interface connectorSlow; appointment key events include duplicates
Monthly sales reportSales directorCustomer relationship systemAppointments by appointment date, not booking date
Monthly board packLeadershipFinance ledgerRevenue at installation; no link to marketing
Agency reportsHead of marketingAd platformsAttributed appointments add up to 1.7 times the system's total
  • The appointment key event in the analytics property (GA4) 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 UTM 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.

  1. 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.
  2. 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.
  3. Dashboards. Name the certified dashboards, their owners, sources and review dates, under the dashboard governance guide, and say which current dashboards are retired.
  4. 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.
  5. Tests finance will see. Using the guide to choosing an attribution approach, assign attribution, Incrementality 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.
  6. 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.
  7. 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 KPI on the leadership page and its definition.
  8. 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.

CriterionWhat a strong answer showsCommon gap
Decisions firstThe decisions each audience makes, with every report mapped to one and the rest retiredStarting from the data available and building every chart
Data modelWarehouse tables joining web, sales and spend data on a stated key, with governed definitions and reconciliation notesReporting kept on platform interfaces
Forecasts and testsScenario forecasts with ranges that carry the lag to installation, and scheduled tests that check the key assumptionsAttributed results presented as causal
GovernanceA taxonomy, weekly checks and owners, so failures are caught before reports go outData problems fixed ad hoc after reports go out
Cadence across marketsCurrency, period, date and consolidation rules with a review rhythmEach market left to report its own way

Submission checklist

0/12

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.

Pages this lesson applies

Terms used on this page

Also related

Related