First-Touch vs Last-Touch Revenue by Channel

Hard
291.

First-Touch vs Last-Touch Revenue by Channel

The marketing mix review compares two attribution models side by side. For a conversion, the qualifying touches are the same user's touchpoints at or before converted_at. First touch credits the conversion's whole revenue to the earliest qualifying touch (equal times: the lower touch_id); last touch credits it to the latest (equal times: the higher touch_id). Conversions with no qualifying touch are credited to nobody.

Return channel, first_touch_revenue and last_touch_revenue (the revenue credited to the channel under each model, 0 when none) for every channel credited under at least one model. Order by channel.

Tables

Table: Touchpoint

ColumnTypeValues
touch_idint
user_idint
channelenumpaid_search, paid_social, email, affiliate, organic
touched_atdatetime

Primary key: touch_id.

One row per tracked visit from a marketing channel.

Table: Conversion

ColumnType
conversion_idint
user_idint
converted_atdatetime
revenueint

Primary key: conversion_id.

One row per order; revenue in rupees.

Example

The rows must come back in the order shown.

Touchpoint

touch_iduser_idchanneltouched_at
17paid_social2024-11-01 09:00:00
27email2024-11-02 18:30:00
37paid_search2024-11-03 10:00:00
48affiliate2024-11-02 12:00:00
58email2024-11-02 12:00:00
69organic2024-11-05 08:15:00
77organic2024-11-06 08:00:00

Conversion

conversion_iduser_idconverted_atrevenue
10172024-11-03 20:00:001500
10282024-11-02 12:00:002600
10392024-11-04 22:00:00800
10472024-11-02 19:00:00400

Output

channelfirst_touch_revenuelast_touch_revenue
affiliate26000
email03000
paid_search01500
paid_social19000
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.

First-Touch vs Last-Touch Revenue by Channel — SQL Problem

The marketing mix review compares two attribution models side by side.

  • Difficulty: Hard
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #291

Problem statement

The marketing mix review compares two attribution models side by side. For a conversion, the qualifying touches are the same user's touchpoints at or before converted_at. First touch credits the conversion's whole revenue to the earliest qualifying touch (equal times: the lower touch_id); last touch credits it to the latest (equal times: the higher touch_id). Conversions with no qualifying touch are credited to nobody.

Return channel, first_touch_revenue and last_touch_revenue (the revenue credited to the channel under each model, 0 when none) for every channel credited under at least one model. Order by channel.

Tables

Table: Touchpoint

ColumnType
touch_idint
user_idint
channelenum(paid_search, paid_social, email, affiliate, organic)
touched_atdatetime

Primary key: touch_id.

One row per tracked visit from a marketing channel.

Table: Conversion

ColumnType
conversion_idint
user_idint
converted_atdatetime
revenueint

Primary key: conversion_id.

One row per order; revenue in rupees.

Examples

Example 1

Touchpoint

touch_iduser_idchanneltouched_at
17paid_social2024-11-01 09:00:00
27email2024-11-02 18:30:00
37paid_search2024-11-03 10:00:00
48affiliate2024-11-02 12:00:00
58email2024-11-02 12:00:00
69organic2024-11-05 08:15:00
77organic2024-11-06 08:00:00

Conversion

conversion_iduser_idconverted_atrevenue
10172024-11-03 20:00:001500
10282024-11-02 12:00:002600
10392024-11-04 22:00:00800
10472024-11-02 19:00:00400

Output

channelfirst_touch_revenuelast_touch_revenue
affiliate26000
email03000
paid_search01500
paid_social19000

How to solve First-Touch vs Last-Touch Revenue by Channel

Start by joining every conversion to its qualifying touches — same user, touched_at <= converted_at. A conversion with no such touch disappears from the inner join, which is exactly "credited to nobody". Touches after the conversion (user 7's organic visit on 6 November) never enter.

Within each conversion, ROW_NUMBER() ordered by touched_at, touch_id marks the first touch with 1, and the same window ordered descending marks the last touch. Breaking ties by touch_id in opposite directions is what the statement asks for — two touches at 12:00 on 2 November give the affiliate the first touch and email the last. A conversion with a single touch has first_rn = 1 and last_rn = 1 on the same row, so both conditional sums pick it up.

Aggregating per channel with SUM(CASE WHEN first_rn = 1 THEN revenue ELSE 0 END) (and the same for last) gives both models in one pass; filtering to the winning rows keeps channels that were merely touched from appearing with two zeros. The alternatives find each winner with an ordered scalar subquery, or with a NOT EXISTS "nobody earlier / later" anti join, and stack the two credit streams with UNION ALL. The window plan sorts the joined rows twice; the subquery plans probe an index on (user_id, touched_at) per conversion.

Reference solution (MySQL)

WITH q AS (
  SELECT v.conversion_id, v.revenue, t.channel,
         ROW_NUMBER() OVER (PARTITION BY v.conversion_id ORDER BY t.touched_at, t.touch_id) AS first_rn,
         ROW_NUMBER() OVER (PARTITION BY v.conversion_id ORDER BY t.touched_at DESC, t.touch_id DESC) AS last_rn
  FROM Conversion v
  JOIN Touchpoint t ON t.user_id = v.user_id AND t.touched_at <= v.converted_at
)
SELECT channel,
       SUM(CASE WHEN first_rn = 1 THEN revenue ELSE 0 END) AS first_touch_revenue,
       SUM(CASE WHEN last_rn = 1 THEN revenue ELSE 0 END) AS last_touch_revenue
FROM q
WHERE first_rn = 1 OR last_rn = 1
GROUP BY channel
ORDER BY channel

Another way

WITH credit AS (
  SELECT (SELECT t.channel FROM Touchpoint t WHERE t.user_id = v.user_id AND t.touched_at <= v.converted_at
          ORDER BY t.touched_at, t.touch_id LIMIT 1) AS channel, v.revenue AS f, 0 AS l
  FROM Conversion v
  UNION ALL
  SELECT (SELECT t.channel FROM Touchpoint t WHERE t.user_id = v.user_id AND t.touched_at <= v.converted_at
          ORDER BY t.touched_at DESC, t.touch_id DESC LIMIT 1), 0, v.revenue
  FROM Conversion v
)
SELECT channel, SUM(f) AS first_touch_revenue, SUM(l) AS last_touch_revenue
FROM credit WHERE channel IS NOT NULL
GROUP BY channel ORDER BY channel

Another way

WITH q AS (
  SELECT v.conversion_id, v.revenue, t.touch_id, t.channel, t.touched_at
  FROM Conversion v JOIN Touchpoint t ON t.user_id = v.user_id AND t.touched_at <= v.converted_at
), firsts AS (
  SELECT a.channel, a.revenue FROM q a
  WHERE NOT EXISTS (SELECT 1 FROM q b WHERE b.conversion_id = a.conversion_id
                    AND (b.touched_at < a.touched_at OR (b.touched_at = a.touched_at AND b.touch_id < a.touch_id)))
), lasts AS (
  SELECT a.channel, a.revenue FROM q a
  WHERE NOT EXISTS (SELECT 1 FROM q b WHERE b.conversion_id = a.conversion_id
                    AND (b.touched_at > a.touched_at OR (b.touched_at = a.touched_at AND b.touch_id > a.touch_id)))
)
SELECT channel, SUM(f) AS first_touch_revenue, SUM(l) AS last_touch_revenue FROM (
  SELECT channel, revenue AS f, 0 AS l FROM firsts UNION ALL SELECT channel, 0, revenue FROM lasts
) x GROUP BY channel ORDER BY channel

← Email Subscribers Who Open but Never Click · Streaks of Three or More Overspent Days →