What this checkpoint covers
The first module of the intermediate marketing analytics level: the query patterns in the guide to SQL for marketers, the definitions and versioning in the guide to metrics layers and definitions, and the ownership, certification and retirement routine in the dashboard governance guide. It builds on the channel summary query exercise.
Task
- Answer every question in a sentence or two, with your reason, before opening the explanations.
- Compare your reasoning, not just your conclusion, with each explanation.
- For any miss, reread the linked section and write down the rule you would now apply.
Questions
- A new dashboard shows a month of paid social spend at nearly three times the figure on the platform's invoice. The query joins the raw spend table to the raw sessions table on date and channel and then sums. What has happened, and what is the fix?
- An analyst reports monthly conversion rate by averaging the daily rates. A manager notices that the monthly figure moves when nothing much changed. What is wrong?
- A query left-joins spend to sessions and then adds a
WHEREcondition on a column from the sessions table. Days with spend and no sessions have vanished from the result. Why? - Marketing reports cost per lead on media spend only; finance reports it including agency fees. Both teams insist theirs is the right one. How do you resolve it?
- The definition of a certified KPIA metric tied directly to an objective. changes so that refunds are removed from revenue. What has to happen beyond editing the formula?
- A large share of last month's sign-ups carry no UTMURL tags identifying traffic source. tags. A colleague proposes filtering them out of the channel report so the table looks clean. What do you say?
- The person who built the paid media dashboard has left. A week later its charts are blank. What failed, and what rule prevents it next time?
- The quarterly review finds a report with no views for a quarter, but its creator wants to keep it. What do you do?
Answers explained
- A fan-out. Several spend rows per day and channel met several session rows for the same key, so every spend row was repeated and the sum inflated. Aggregate each side to day and channel first, then join; the SQL guide's pitfalls section shows the pattern, and a check that the modelled total ties to the platform catches it before anyone else does.
- Ratios are recomputed from sums, never averaged. A daily average weights a quiet day as heavily as a busy one, so conversion rate (CVRShare of visits or clicks that convert.) drifts with the mix of days rather than with performance. The metrics layer guide states the roll-up rule in the definition's grain field.
- The filter turned the left join into an inner join. Unmatched rows carry nulls in the sessions columns and
fail the condition. Move it into the
ONclause or into the CTE that builds the sessions side. - Define two metrics with two names. Media-only cost per lead and fully loaded cost per lead answer different questions and both are legitimate. The problem is two numbers sharing one name, and the metrics layer guide records the difference in each definition's known-differences field.
- Version it. Issue a new version with a dated change log, restate history where the raw rows allow it, publish a before-and-after for the key periods, and use the inventory to find every dashboard that shows the metric so each is updated. A silent edit moves every historical number with no explanation.
- Keep them, labelled. Dropping untagged rows understates the total and makes the paid channels look worse without saying why. Report them as their own row, flagged, and send the tagging fault to its owner, as the channel summary exercise did.
- The data source credential belonged to a person, and ownership did not transfer. The dashboard governance guide requires credentials on a service account or a role, and a named owner reassigned before anyone leaves.
- Move it out of the shared space rather than delete it. If its creator still wants it, it becomes a personal or team dashboard under their ownership, named accordingly and removed from the certified folder. If nobody claims it, deprecate it with a sunset date and archive it, because deleting without warning teaches people to keep private copies.
Where to go next
If you missed the query questions, rework the channel summary query exercise and check the totals against the sources. If you missed the definition or governance questions, write a full definition for one metric you report, using the field list in the metrics layer guide, and an inventory entry for the dashboard that shows it. Then start the second module with the measurement approach exercise.
