Skip to main content
To optimize marketing spend, you need to know which channels don’t just generate activity, but which ones actually drive wins. By calculating win rates, you can measure how effectively each channel converts prospects into paying customers. This guide provides two essential models for measuring channel win rates:
  • First-Touch Win Rate: Which channels are best at acquiring prospects who eventually convert?
  • Last-Touch Win Rate: Which channels are most effective at closing deals?

​
The Result

The queries below will produce data that can be combined into a single, powerful table. This view allows you to compare channels on their ability to both source and close deals, informing a more sophisticated budget allocation strategy.
channelfirst_touch_win_ratelast_touch_win_rate
Paid Search12%25%
Organic18%15%
Email5%45%
Paid Social8%10%
From this, you can see Organic Search is a top acquisition channel (high first-touch win rate), while Email is your most effective closing channel (high last-touch win rate).

​
The Queries

Here are the queries to calculate win rates for both attribution models. You can run them separately and join the results on the channel column to produce the summary table above.

​
First-Touch Win Rate

This model measures how many customers acquired by a specific channel eventually convert. It answers: “Of all the new prospects a channel brings us, what percentage become customers?” 
WITH FirstTouchProspects AS (
    -- For every customer, find their very first touchpoint.
    SELECT
        customer_id,
        source AS channel
    FROM customer_stream
    QUALIFY ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY ts ASC) = 1
),
Conversions AS (
    -- Identify every customer who converted.
    SELECT DISTINCT customer_id
    FROM customer_stream
    WHERE activity = 'created_subscription'
)
SELECT
    ftp.channel,
    COUNT(DISTINCT ftp.customer_id) AS total_prospects,
    COUNT(DISTINCT c.customer_id) AS total_conversions,
    (COUNT(DISTINCT c.customer_id) * 1.0 / COUNT(DISTINCT ftp.customer_id)) AS first_touch_win_rate
FROM FirstTouchProspects ftp
LEFT JOIN Conversions c ON ftp.customer_id = c.customer_id
GROUP BY 1
ORDER BY 4 DESC;

​
Last-Touch Win Rate

This model measures the effectiveness of a channel at closing the deal. It answers: “Of all the prospects whose last marketing interaction was with a specific channel, what percentage converted?” 
WITH LastTouchProspects AS (
    -- For every customer, find their final marketing touchpoint.
    SELECT
        customer_id,
        source AS channel
    FROM customer_stream
    -- To isolate marketing, you can filter for specific sources.
    -- WHERE source IN ('paid_social', 'paid_search', 'organic', 'email')
    QUALIFY ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY ts DESC) = 1
),
Conversions AS (
    -- Identify every customer who converted.
    SELECT DISTINCT customer_id
    FROM customer_stream
    WHERE activity = 'created_subscription'
)
SELECT
    ltp.channel,
    COUNT(DISTINCT ltp.customer_id) AS total_prospects,
    COUNT(DISTINCT c.customer_id) AS total_conversions,
    (COUNT(DISTINCT c.customer_id) * 1.0 / COUNT(DISTINCT ltp.customer_id)) AS last_touch_win_rate
FROM LastTouchProspects ltp
LEFT JOIN Conversions c ON ltp.customer_id = c.customer_id
GROUP BY 1
ORDER BY 4 DESC;