Last-Click Attribution Analysis

The Business Problem: Attribution Crisis Inside the Dashboard

A growth-stage DTC brand was running active paid social campaigns across TikTok, Facebook, and YouTube. Their Q2 performance report — pulled from their default analytics setup — showed all three channels delivering $0 in attributed revenue.

The obvious next move: cut the budget. Why fund channels that aren't producing?

The problem is that the report wasn't lying about the data — it was lying about what the data meant. Last-click attribution, the default model in most analytics platforms, assigns 100% of conversion credit to the final touchpoint before a purchase.

If a customer clicked a TikTok ad, then a retargeting ad, then typed the URL directly — Direct Traffic gets the full credit. TikTok gets nothing.

In a world where customers touch 3–4 channels before converting, this isn't a minor rounding error. It's a misreading of the structure of how revenue is actually generated.

My Approach: Engineering the Attribution Workflow

To correctly credit revenue across the full customer journey, I bypassed platform-level reports entirely. I built a custom SQL attribution engine directly within the data warehouse, creating a single source of truth that ad platform reporting incentives couldn't manipulate.

Here is the step-by-step build process:

Before any analysis could begin, I needed a structured schema to hold the raw CSV data. This ensures strict data types and prevents formatting errors during the transformation process.

CREATE TABLE q2_customer_journeys (
    customer_id VARCHAR(50) NOT NULL,
    path VARCHAR(255) NOT NULL,
    revenue NUMERIC(10, 2) DEFAULT 0.00,
    purchase_status VARCHAR(50),
    PRIMARY KEY (customer_id, path)
);

Building the Foundation: Normalizing Messy Journey Data

Before any attribution model could run, the raw CSV required normalization. The dataset used three different path separators across records — a common real-world data quality issue that would silently corrupt touchpoint counts if not resolved first.

1. Path Separator Normalization

Problem: The path column mixed " > ", " -> " and " | " as separators across rows, treating the same channel combinations as different paths.

CREATE OR REPLACE VIEW completed_journeys AS
SELECT
    customer_id,
    -- Normalizes all separator variations (' | ', '|', ' -> ', '->') into a clean ' > '
    REPLACE(REPLACE(REPLACE(REPLACE(path, ' | ', ' > '), '|', ' > '), ' -> ', ' > '), '->', ' > ') AS path_clean,
    revenue,
    purchase_status
FROM q2_customer_journeys
WHERE purchase_status = 'Completed'
  AND revenue > 0;

2. Unpacking Paths via Recursive CTE

Most CRMs log touchpoints as a single, messy text string. To lay the foundation for the multi-touch attribution model, I use a recursive query to unpack these strings into individual, trackable rows systematically.

CREATE OR REPLACE VIEW touchpoint_credits AS
WITH RECURSIVE path_splitter AS (
    SELECT
        customer_id, revenue, CAST(path_clean AS CHAR(1000)) AS path_clean,
        CAST(TRIM(SUBSTRING_INDEX(path_clean, ' > ', 1)) AS CHAR(255)) AS touchpoint,
        CAST(IF(LOCATE(' > ', path_clean) > 0, TRIM(SUBSTRING(path_clean, LOCATE(' > ', path_clean) + 3)), NULL) AS CHAR(1000)) AS remainder, 
        1 AS touch_position,
        CAST((LENGTH(path_clean) - LENGTH(REPLACE(path_clean, ' > ', ''))) / 3 + 1 AS SIGNED) AS total_touches
    FROM completed_journeys
    UNION ALL
    SELECT
        customer_id, revenue, path_clean,
        CAST(TRIM(SUBSTRING_INDEX(remainder, ' > ', 1)) AS CHAR(255)) AS touchpoint,
        CAST(IF(LOCATE(' > ', remainder) > 0, TRIM(SUBSTRING(remainder, LOCATE(' > ', remainder) + 3)), NULL) AS CHAR(1000)) AS remainder,
        touch_position + 1, total_touches
    FROM path_splitter
    WHERE remainder IS NOT NULL
),
cleaned AS (
    SELECT
        customer_id, revenue, total_touches, touch_position,
        CASE
            WHEN touchpoint IN ('Direct', 'Direct_Traffic') THEN 'Direct'
            WHEN touchpoint = 'Google_Brand_Search' THEN 'Brand Search'
            ELSE REPLACE(touchpoint, '_', ' ')
        END AS channel,
        CASE WHEN touch_position = 1 THEN 1 ELSE 0 END AS is_first,
        CASE WHEN touch_position = total_touches THEN 1 ELSE 0 END AS is_last,
        revenue / total_touches AS linear_credit
    FROM path_splitter
    WHERE touchpoint IS NOT NULL AND CHAR_LENGTH(TRIM(touchpoint)) > 0
)
SELECT * FROM cleaned;

Proving That Multi-Touch Journeys Drive Conversions

Before building attribution models, I needed to establish a behavioral baseline: do customers who convert actually touch multiple channels, or is the single-touch assumption defensible?

3. Converter vs. Bouncer Path Length Comparison

Question: Do converting customers actually interact with more channels than those who bounce?

SELECT
    purchase_status,
    ROUND(AVG(
        LENGTH(REPLACE(REPLACE(path, '|', '>'), '->', '>')) 
        - LENGTH(REPLACE(REPLACE(REPLACE(path, '|', '>'), '->', '>'), '>', '')) 
        + 1
    ), 2) AS avg_path_length
FROM q2_customer_journeys
GROUP BY purchase_status;

Running Three Attribution Models Against the Same Revenue

With the data cleaned and the multi-touch behavior confirmed, I applied three attribution models to the same 621 converting journeys. Each model answers the same question differently.

4. Building the Last-Click Model

Question: What does revenue attribution look like when only the final touchpoint is credited?

SELECT
    channel,
    ROUND(SUM(revenue), 0) AS last_click_revenue,
    COUNT(DISTINCT customer_id) AS conversions
FROM touchpoint_credits
WHERE is_last = 1
GROUP BY channel
ORDER BY last_click_revenue DESC;

5. Building the First-Click Model

Next, I flipped the attribution logic entirely. To find out where these converting journeys actually began, I built a First-Click model to see which channels were driving initial discovery.

SELECT
    channel,
    ROUND(SUM(revenue), 0) AS first_click_revenue,
    COUNT(DISTINCT customer_id) AS conversions
FROM touchpoint_credits
WHERE is_first = 1
GROUP BY channel
ORDER BY first_click_revenue DESC;

6. Building the Linear (Multi-Touch) Model

Question: What does revenue attribution look like when every channel in a converting path receives equal credit?

SELECT
    channel,
    ROUND(SUM(linear_credit), 0) AS linear_revenue
FROM touchpoint_credits
GROUP BY channel
ORDER BY linear_revenue DESC;

Quantifying the Budget Decision Error

7. Channel-Level Misattribution Delta

Question: For each channel, what is the gap between what last-click reported and what multi-touch modeling shows?

This is the core business finding. By combining the last-click and linear outputs, I isolated the exact financial gap and identified which channels were structurally undervalued by default reporting.

WITH last_click AS (
    SELECT channel, ROUND(SUM(revenue), 0) AS lc_revenue
    FROM touchpoint_credits WHERE is_last = 1 GROUP BY channel
),
linear_model AS (
    SELECT channel, ROUND(SUM(linear_credit), 0) AS ln_revenue
    FROM touchpoint_credits GROUP BY channel
),
all_channels AS (
    SELECT channel FROM last_click
    UNION
    SELECT channel FROM linear_model
)
SELECT
    a.channel, COALESCE(lc.lc_revenue, 0) AS last_click_revenue,
    COALESCE(ln.ln_revenue, 0) AS linear_revenue,
    COALESCE(ln.ln_revenue, 0) - COALESCE(lc.lc_revenue, 0) AS delta,
    CASE
        WHEN COALESCE(ln.ln_revenue, 0) - COALESCE(lc.lc_revenue, 0) > 0  THEN 'Undervalued'
        WHEN COALESCE(ln.ln_revenue, 0) - COALESCE(lc.lc_revenue, 0) < 0  THEN 'Over-credited'
        ELSE 'Neutral'
    END AS attribution_status
FROM all_channels a
LEFT JOIN last_click lc ON a.channel = lc.channel
LEFT JOIN linear_model ln ON a.channel = ln.channel
ORDER BY delta DESC;

8. Top Converting Path Sequences

Question: What are the actual multi-step paths that generate the most revenue — and do they confirm the channel role hypothesis?

Analyzing the most frequent multi-step sequences proved that paid social channels were initiating the journeys, but direct traffic was getting all the credit for closing them.

SELECT
    REPLACE(REPLACE(REPLACE(path_clean, 'Direct_Traffic', 'Direct'), 'Google_Brand_Search', 
    'Brand Search'), '_', ' ') AS customer_path,
    COUNT(*) AS conversions,
    ROUND(SUM(revenue), 0) AS total_revenue
FROM completed_journeys
GROUP BY customer_path
HAVING COUNT(*) >= 2
ORDER BY total_revenue DESC
LIMIT 10;

Business Impact & Recommendations

Short-Term: Shift the Reporting Baseline

Switch from last-click to a linear or position-based model in your primary reporting layer. Most standard analytics platforms support this without requiring additional data engineering. Run both models in parallel for 60 days to build historical confidence before presenting the new baseline to leadership.

Medium-Term: Transition to Algorithmic Attribution

Commission a Markov chain attribution model. Unlike heuristic, rule-based models (last-click, linear, first-click), Markov chains calculate the actual incremental contribution of each channel by modeling how the overall conversion probability changes when a specific channel is removed from the path. This is the current industry standard for rigorous, defensible attribution.