October challenge board
Dirty Data Lab™ · Challenge 08
Medium-Hard
Monday, October 26 · 20–25 minutesUTM Aggregation Puzzle
Marketing thinks there are ten campaign rows. After case and whitespace are normalized, several rows collapse into the same campaign key.
The assignment
Cleanup rules
There is one finished state. Use the rules to get the board there.
- Trim landing_url and lowercase utm_source, utm_medium and utm_campaign.
- Convert clicks and conversions to whole numbers.
- Group by landing_url + utm_source + utm_medium + utm_campaign.
- Sum clicks and conversions for rows that collapse to the same normalized key.
- Sort by landing_url then utm_source.
- Calculate the overall conversion rate as conversions / clicks.
Loading the puzzle board…
Loading your Lab profile…
Pass the puzzle
Send this one to a friend.
The friend link carries Dirty Data Lab™ referral attribution, so referred signups can be measured separately from newsletter and ad traffic.
Take the habit into Google Sheets
Practice here. Preflight real sheets with CTS Guard.
Clean That Sheet Guard checks the active Google Sheet for duplicates, missing data, formatting issues and other handoff problems before you share or import it.