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

Marketing Analytics and Reporting › Intermediate

Writing a Channel Summary Query for a Broadband Provider

An exercise in writing the query behind a weekly channel summary: aggregate costs and sign-ups separately, join them on week and channel, compute cost per sign-up and conversion rate exactly as the metric dictionary defines them, and flag the rows that untidy campaign tagging leaves behind. The worked answer explains every clause and the mistakes that make a plausible query return the wrong total.
ExerciseIntermediate levelModule 1: SQL, metric layers and dashboard governance4 minUpdated

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 UTM source and medium values into reporting channels.

Illustrative example — not a real result

Cobaltline's tables, definitions and one week of data

TableGrainColumns used
campaign_costsDay, platform and campaigncost_date (UK time), utm_source, utm_medium, cost_gbp, clicks
signupsOne sign-upsignup_id, signup_ts (UTC), utm_source, utm_medium, is_test
channel_mapSource and medium pair, lower caseutm_source, utm_medium, channel
MetricFormulaGrainRules
Cost per sign-upcost_gbp ÷ signupsISO week by channelTest sign-ups excluded; counted on UK date; null when sign-ups are zero
Conversion ratesignups ÷ clicksISO week by channelPaid 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)CostClicksSign-ups
Paid search£4,2003,50084
Paid social£2,6005,20039
Affiliate£1,5001,00040
No source or medium£0022
Tagged facebook / paid-social, not in the map£006

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

  1. Aggregate campaign_costs to ISO week and mapped channel for the reporting period.
  2. 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.
  3. 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.
  4. Add a flag column that is true for rows affected by missing or unmapped tags.
  5. 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

Illustrative example — not a real result

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:

ChannelCost per sign-upConversion rateFlag
Affiliate£1,500 ÷ 40 = £37.5040 ÷ 1,000 = 4.0%false
Paid search£4,200 ÷ 84 = £50.0084 ÷ 3,500 = 2.4%false
Paid social£2,600 ÷ 39 = £66.6739 ÷ 5,200 = 0.75%false
Unmappednull (6 sign-ups, no cost)nulltrue
Untaggednull (22 sign-ups, no cost)nulltrue

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 (CPA) and conversion rate (CVR) come from weekly totals, never averaged from days.
  • Nulls, not zeros. SAFE_DIVIDE returns 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.

Pages this lesson applies

Terms used on this page

Also related

Related