Why this matters
A weekly channel summary is the table most marketing dashboards are built on, and it is usually where the first wrong number enters. The guide to SQL for marketers teaches the shapes used here: aggregate each side to the join grain, join on the shared dimensions, compute ratios from sums. The guide to metrics layers and definitions explains why formula, grain, filters and time rule must come from a written definition. This exercise makes a query obey one, including the awkward part: sign-ups whose campaign tags are missing.
The provider and its tables
Cobaltline Broadband is an invented regional broadband provider. It buys paid search, paid social and affiliate
placements. Platform costs load daily into campaign_costs, sign-ups arrive from the order system into signups,
and a hand-maintained channel_map turns raw UTMURL tags identifying traffic source. source and medium values into reporting channels.
Cobaltline's tables, definitions and one week of data
| Table | Grain | Columns used |
|---|---|---|
campaign_costs | Day, platform and campaign | cost_date (UK time), utm_source, utm_medium, cost_gbp, clicks |
signups | One sign-up | signup_id, signup_ts (UTC), utm_source, utm_medium, is_test |
channel_map | Source and medium pair, lower case | utm_source, utm_medium, channel |
| Metric | Formula | Grain | Rules |
|---|---|---|---|
| Cost per sign-up | cost_gbp ÷ signups | ISO week by channel | Test sign-ups excluded; counted on UK date; null when sign-ups are zero |
| Conversion rate | signups ÷ clicks | ISO week by channel | Paid clicks only; from weekly sums |
The reporting period is the four ISO weeks from 2026-08-31 to 2026-09-27. For the week starting 2026-09-07:
| Channel (after mapping) | Cost | Clicks | Sign-ups |
|---|---|---|---|
| Paid search | £4,200 | 3,500 | 84 |
| Paid social | £2,600 | 5,200 | 39 |
| Affiliate | £1,500 | 1,000 | 40 |
| No source or medium | £0 | 0 | 22 |
Tagged facebook / paid-social, not in the map | £0 | 0 | 6 |
The web team also placed three test sign-ups that week.
Joining cost to outcomes
Costs are daily by campaign; sign-ups are single records with a UTC timestamp. Joining them row by row multiplies something. Bring each to week by channel in its own common table expression, then join the results. The join type is part of the definition: a full outer join keeps cost without sign-ups and sign-ups without cost, and both are findings.
Missing tags need a decision, not a filter. Give sign-ups with no source the channel "Untagged", and tagged values the map does not recognise the channel "Unmapped". The first is a tagging fault, the second a gap in the map, and they have different fixes.
Task
- Aggregate
campaign_coststo ISO week and mapped channel for the reporting period. - Exclude test sign-ups, convert each timestamp to its UK date, assign "Untagged" or "Unmapped" where the map cannot help, and aggregate to ISO week and channel.
- Join the two sides without losing rows from either, and compute cost per sign-up and conversion rate from the weekly sums, safely when a denominator is zero.
- Add a flag column that is true for rows affected by missing or unmapped tags.
- State the values your query should return for the sample week, and write two sentences for the head of acquisition on what the flagged rows mean for the paid channels.
Worked answer
The channel summary query in BigQuery SQL
WITH costs AS (
SELECT
DATE_TRUNC(c.cost_date, ISOWEEK) AS week_start,
COALESCE(m.channel, 'Unmapped') AS channel,
SUM(c.cost_gbp) AS cost_gbp,
SUM(c.clicks) AS clicks
FROM campaign_costs AS c
LEFT JOIN channel_map AS m
ON m.utm_source = LOWER(c.utm_source)
AND m.utm_medium = LOWER(c.utm_medium)
WHERE c.cost_date BETWEEN '2026-08-31' AND '2026-09-27'
GROUP BY week_start, channel
),
signups_clean AS (
SELECT
s.signup_id,
DATE(s.signup_ts, 'Europe/London') AS signup_date,
CASE
WHEN s.utm_source IS NULL OR s.utm_medium IS NULL THEN 'Untagged'
ELSE COALESCE(m.channel, 'Unmapped')
END AS channel
FROM signups AS s
LEFT JOIN channel_map AS m
ON m.utm_source = LOWER(s.utm_source)
AND m.utm_medium = LOWER(s.utm_medium)
WHERE COALESCE(s.is_test, FALSE) = FALSE
),
outcomes AS (
SELECT
DATE_TRUNC(signup_date, ISOWEEK) AS week_start,
channel,
COUNT(DISTINCT signup_id) AS signups
FROM signups_clean
WHERE signup_date BETWEEN '2026-08-31' AND '2026-09-27'
GROUP BY week_start, channel
)
SELECT
COALESCE(c.week_start, o.week_start) AS week_start,
COALESCE(c.channel, o.channel) AS channel,
COALESCE(c.cost_gbp, 0) AS cost_gbp,
COALESCE(o.signups, 0) AS signups,
SAFE_DIVIDE(c.cost_gbp, o.signups) AS cost_per_signup,
SAFE_DIVIDE(o.signups, c.clicks) AS conversion_rate,
COALESCE(c.channel, o.channel) IN ('Untagged', 'Unmapped') AS utm_flag
FROM costs AS c
FULL OUTER JOIN outcomes AS o
ON o.week_start = c.week_start
AND o.channel = c.channel
ORDER BY week_start, channel
Expected rows for the week starting 2026-09-07:
| Channel | Cost per sign-up | Conversion rate | Flag |
|---|---|---|---|
| Affiliate | £1,500 ÷ 40 = £37.50 | 40 ÷ 1,000 = 4.0% | false |
| Paid search | £4,200 ÷ 84 = £50.00 | 84 ÷ 3,500 = 2.4% | false |
| Paid social | £2,600 ÷ 39 = £66.67 | 39 ÷ 5,200 = 0.75% | false |
| Unmapped | null (6 sign-ups, no cost) | null | true |
| Untagged | null (22 sign-ups, no cost) | null | true |
Total sign-ups: 84 + 39 + 40 + 6 + 22 = 191, test sign-ups excluded. Flagged rows hold 28 of them, about 15%.
The choices that matter:
- Aggregate before joining. Each CTE returns one row per week and channel, so the join cannot fan out and cost still ties to the platforms.
- Time rule. Timestamps become UK dates before truncation; a UTC date moves late Sunday sign-ups into the next week. Converting once on the way into the warehouse is better, and until then this belongs in the definition's known differences.
- Ratios from sums. Cost per acquisition (CPACost of each conversion.) and conversion rate (CVRShare of visits or clicks that convert.) come from weekly totals, never averaged from days.
- Nulls, not zeros.
SAFE_DIVIDEreturns null for flagged rows; zero would claim those sign-ups were free. - Full outer join. Untagged sign-ups have no cost row, so any other join drops them and the evidence with them.
- Unmapped cost rows. A campaign with broken tags lands in "Unmapped" on the cost side and is flagged too.
For the head of acquisition: "Most flagged sign-ups probably came from paid channels, so paid cost per sign-up is
overstated by an amount we cannot assign yet. Adding the facebook / paid-social pair to the channel map fixes the
unmapped rows; the untagged rows need the week's new ad links checked."
Once agreed, the query belongs in the modelled table the certified dashboard reads, with an owner and a change log, as the dashboard governance guide describes.
Common mistakes
- Joining raw tables. Summing after a row-level join multiplies cost by sign-ups per day.
- Averaging daily ratios. Weekly cost per sign-up is total over total.
- Truncating the UTC date. The week ends at UK midnight on Sunday.
- Filtering missing tags out. The total looks clean and paid channels look worse, with no explanation.
- Lumping untagged and unmapped together. Different owners, different fixes.
- Ignoring case. Lower-case sources before matching the map.
Next, take the BI foundations checkpoint, or return to the intermediate marketing analytics level.
