Last-Touch Channel for Each Conversion

Medium
285.

Last-Touch Channel for Each Conversion

MediumSubqueriesJoins

MySQL

Under last-touch attribution, a conversion is credited to the most recent marketing touchpoint of the same user at or before the moment of conversion. Touches after the conversion don't count.

Return conversion_id and last_touch_channel for every conversion. When two qualifying touches share the latest timestamp, the one with the higher touch_id wins. A conversion with no qualifying touch is credited to 'direct'. Return the rows in any order.

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, in rupees.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Touchpoint

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

Conversion

conversion_iduser_idconverted_atrevenue
10172024-11-03 20:00:001499
10282024-11-03 12:00:002599
10392024-11-04 22:00:00799
104102024-11-05 11:00:00349

Output

conversion_idlast_touch_channel
101email
102email
103direct
104direct
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.

Last-Touch Channel for Each Conversion — SQL Problem

Under last-touch attribution, a conversion is credited to the most recent marketing touchpoint of the same user at or before the moment of conversion.

  • Difficulty: Medium
  • Topics: Subqueries, Joins
  • Dialect: MySQL
  • Problem: #285

Problem statement

Under last-touch attribution, a conversion is credited to the most recent marketing touchpoint of the same user at or before the moment of conversion. Touches after the conversion don't count.

Return conversion_id and last_touch_channel for every conversion. When two qualifying touches share the latest timestamp, the one with the higher touch_id wins. A conversion with no qualifying touch is credited to 'direct'. Return the rows in any order.

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, 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-04 10:00:00
48affiliate2024-11-03 12:00:00
58email2024-11-03 12:00:00
69organic2024-11-05 08:15:00

Conversion

conversion_iduser_idconverted_atrevenue
10172024-11-03 20:00:001499
10282024-11-03 12:00:002599
10392024-11-04 22:00:00799
104102024-11-05 11:00:00349

Output

conversion_idlast_touch_channel
101email
102email
103direct
104direct

How to solve Last-Touch Channel for Each Conversion

Each conversion needs one value from a set of rows: among the user's touches at or before the conversion, the latest. A correlated scalar subquery expresses that directly — filter by user and time, sort by touched_at DESC, touch_id DESC so equal timestamps are decided by the higher id, and take one row. With no qualifying touch the subquery returns NULL, which COALESCE reports as 'direct'. A touch at exactly the conversion time qualifies, because the window is "at or before".

The window alternative LEFT JOINs every qualifying touch to the conversion and keeps row number 1 per conversion; a conversion without touches survives the LEFT JOIN as one row with a NULL channel. The third form keeps only the touch that no other qualifying touch beats (later, or same time and higher id) — a classic greatest-per-group anti join.

With an index on (user_id, touched_at) each subquery is one short index range scan.

Reference solution (MySQL)

SELECT v.conversion_id,
       COALESCE((
         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
       ), 'direct') AS last_touch_channel
FROM Conversion v

Another way

SELECT conversion_id, COALESCE(channel, 'direct') AS last_touch_channel FROM (
  SELECT v.conversion_id, t.channel,
         ROW_NUMBER() OVER (PARTITION BY v.conversion_id ORDER BY t.touched_at DESC, t.touch_id DESC) AS rn
  FROM Conversion v
  LEFT JOIN Touchpoint t ON t.user_id = v.user_id AND t.touched_at <= v.converted_at
) x WHERE rn = 1

Another way

SELECT v.conversion_id, COALESCE(t.channel, 'direct') AS last_touch_channel
FROM Conversion v
LEFT JOIN Touchpoint t ON t.user_id = v.user_id AND t.touched_at <= v.converted_at
  AND NOT EXISTS (SELECT 1 FROM Touchpoint u WHERE u.user_id = v.user_id AND u.touched_at <= v.converted_at
                  AND (u.touched_at > t.touched_at OR (u.touched_at = t.touched_at AND u.touch_id > t.touch_id)))

← Campaign Months That Overspent the Budget · Email Open Rate by Hour of Send →