Signups and Upgrades by UTM Source — SQL Strings Problem
Marketing attributes each signup to the utm_source parameter of the URL it landed on — the text after utm_source= up to the next & or the end of the URL.
- Difficulty: Medium
- Topics: Strings, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #412
Problem statement
Marketing attributes each signup to the utm_source parameter of the URL it landed on — the text after utm_source= up to the next & or the end of the URL. The parameter can appear anywhere in the query string; a URL without a utm_source parameter counts as direct. A signup is a paid signup when upgraded_on is not NULL.
Return utm_source, signups and paid_signups per source, ordered by signups descending, then utm_source. Every URL is lower-case.
Tables
Table: signups
| Column | Type |
|---|---|
| user_id | int |
| landing_url | varchar |
| signed_up_on | date |
| upgraded_on | date |
Primary key: user_id.
One row per signup; upgraded_on is the day the user moved to a paid plan, or NULL.
Examples
Example 1
signups
| user_id | landing_url | signed_up_on | upgraded_on |
|---|---|---|---|
| 1 | https://app.taskora.in/signup?utm_source=google&utm_medium=cpc | 2024-04-01 | 2024-04-10 |
| 2 | https://app.taskora.in/signup?utm_medium=email&utm_source=newsletter | 2024-04-02 | NULL |
| 3 | https://app.taskora.in/signup | 2024-04-02 | NULL |
| 4 | https://app.taskora.in/signup?utm_source=google&utm_medium=cpc&utm_campaign=diwali | 2024-04-03 | 2024-04-20 |
| 5 | https://app.taskora.in/signup?ref=footer | 2024-04-05 | NULL |
| 6 | https://app.taskora.in/signup?utm_source=linkedin | 2024-04-06 | 2024-04-07 |
| 7 | https://app.taskora.in/signup?utm_source=google&utm_medium=organic | 2024-04-08 | NULL |
| 8 | https://app.taskora.in/signup?utm_source=newsletter | 2024-04-09 | 2024-05-01 |
Output
| utm_source | signups | paid_signups |
|---|---|---|
| 3 | 2 | |
| direct | 2 | 0 |
| newsletter | 2 | 1 |
| 1 | 1 |
How to solve Signups and Upgrades by UTM Source
The work is in parsing. SUBSTRING_INDEX(url, 'utm_source=', -1) returns everything after the marker — google&utm_medium=cpc — and SUBSTRING_INDEX(…, '&', 1) cuts that at the first &, leaving google; when the parameter is last there is no & and the whole remainder is the value. Because the marker is searched for anywhere, the order of parameters does not matter.
If the marker is absent, SUBSTRING_INDEX returns the whole URL, which would create a junk source per URL. So the expression is guarded: LOCATE('utm_source=', url) > 0 (or INSTR(url, …) = 0 in the IF form) decides between the parsed value and 'direct'. A ?ref=footer URL has a query string but no source, so it is direct too.
Once each row has a source, the rest is grouping: COUNT(*) signups and a conditional sum — or COUNT(upgraded_on), which skips NULLs — for paid signups. Parsing in a derived table keeps the expression written once; MySQL also allows grouping by the expression itself. One scan of the table.
Reference solution (MySQL)
SELECT utm_source,
COUNT(*) AS signups,
SUM(CASE WHEN upgraded_on IS NOT NULL THEN 1 ELSE 0 END) AS paid_signups
FROM (
SELECT CASE WHEN LOCATE('utm_source=', landing_url) > 0
THEN SUBSTRING_INDEX(SUBSTRING_INDEX(landing_url, 'utm_source=', -1), '&', 1)
ELSE 'direct' END AS utm_source,
upgraded_on
FROM signups
) s
GROUP BY utm_source
ORDER BY signups DESC, utm_sourceAnother way
SELECT IF(INSTR(landing_url, 'utm_source=') = 0, 'direct', SUBSTRING_INDEX(SUBSTRING(landing_url, INSTR(landing_url, 'utm_source=') + 11), '&', 1)) AS utm_source,
COUNT(*) AS signups, COUNT(upgraded_on) AS paid_signups
FROM signups
GROUP BY IF(INSTR(landing_url, 'utm_source=') = 0, 'direct', SUBSTRING_INDEX(SUBSTRING(landing_url, INSTR(landing_url, 'utm_source=') + 11), '&', 1))
ORDER BY signups DESC, utm_source← Power Users Active on Four or More Days a Month · Monthly Active Users, New Versus Returning →