Signups and Upgrades by UTM Source

Medium
412.

Signups and Upgrades by UTM Source

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

ColumnType
user_idint
landing_urlvarchar
signed_up_ondate
upgraded_ondate

Primary key: user_id.

One row per signup; upgraded_on is the day the user moved to a paid plan, or NULL.

Example

The rows must come back in the order shown.

signups

user_idlanding_urlsigned_up_onupgraded_on
1https://app.taskora.in/signup?utm_source=google&utm_medium=cpc2024-04-012024-04-10
2https://app.taskora.in/signup?utm_medium=email&utm_source=newsletter2024-04-02NULL
3https://app.taskora.in/signup2024-04-02NULL
4https://app.taskora.in/signup?utm_source=google&utm_medium=cpc&utm_campaign=diwali2024-04-032024-04-20
5https://app.taskora.in/signup?ref=footer2024-04-05NULL
6https://app.taskora.in/signup?utm_source=linkedin2024-04-062024-04-07
7https://app.taskora.in/signup?utm_source=google&utm_medium=organic2024-04-08NULL
8https://app.taskora.in/signup?utm_source=newsletter2024-04-092024-05-01

Output

utm_sourcesignupspaid_signups
google32
direct20
newsletter21
linkedin11
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
user_idint
landing_urlvarchar
signed_up_ondate
upgraded_ondate

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_idlanding_urlsigned_up_onupgraded_on
1https://app.taskora.in/signup?utm_source=google&utm_medium=cpc2024-04-012024-04-10
2https://app.taskora.in/signup?utm_medium=email&utm_source=newsletter2024-04-02NULL
3https://app.taskora.in/signup2024-04-02NULL
4https://app.taskora.in/signup?utm_source=google&utm_medium=cpc&utm_campaign=diwali2024-04-032024-04-20
5https://app.taskora.in/signup?ref=footer2024-04-05NULL
6https://app.taskora.in/signup?utm_source=linkedin2024-04-062024-04-07
7https://app.taskora.in/signup?utm_source=google&utm_medium=organic2024-04-08NULL
8https://app.taskora.in/signup?utm_source=newsletter2024-04-092024-05-01

Output

utm_sourcesignupspaid_signups
google32
direct20
newsletter21
linkedin11

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_source

Another 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 →