Learn Introduction to Attribution Logic | Attribution and Campaign Performance

Attribution in marketing analytics is the process of assigning credit for conversions—such as purchases or sign-ups—to specific marketing touchpoints that a user interacts with along their journey. This is crucial for understanding which marketing channels or campaigns are most effective at driving results, allowing you to optimize your marketing spend and strategy.


              12345678910
            
-- Example: events table showing multiple campaign touchpoints for a single user
SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp,
    campaign_id
FROM events
WHERE user_id = 4
ORDER BY event_timestamp;

Definition

Attribution logic determines how credit for a conversion is distributed among marketing channels. This logic can greatly affect how you evaluate the effectiveness of each campaign or touchpoint.

There are several common attribution models you will encounter. The first-touch attribution model assigns all the credit for a conversion to the first marketing interaction a user has. In contrast, the last-touch attribution model gives all the credit to the most recent campaign event before the conversion. The linear attribution model distributes credit equally across all touchpoints a user had before converting. Each model offers different insights, and the choice depends on your marketing goals and the complexity of your user journeys.


              1234567891011121314151617
            
-- SQL query: Assign conversion to the most recent campaign event (last-touch attribution)
SELECT
    e.user_id,
    e.event_id AS conversion_event_id,
    e.event_timestamp AS conversion_time,
    e.campaign_id AS last_touch_campaign_id
FROM events e
WHERE e.event_type = 'purchase'
    AND e.event_timestamp = (
        SELECT MAX(e2.event_timestamp)
        FROM events e2
        WHERE
            e2.user_id = e.user_id
            AND e2.event_timestamp <= e.event_timestamp
            AND e2.campaign_id IS NOT NULL
    );
-- This query finds, for each purchase, the campaign_id from the most recent event for that user at or before the conversion time.

This query implements last-touch attribution by assigning each conversion event to the most recent marketing campaign interaction that occurred before or at the time of purchase.

The query filters the events table to only include conversion events (event_type = 'purchase'). For each purchase, it uses a correlated subquery to search within the same user's event history and identify the latest event timestamp that occurred at or before the conversion time and has a non-null campaign_id. This ensures that only valid campaign interactions are considered for attribution.

The correlation is performed on user_id, allowing the subquery to dynamically evaluate campaign activity per user. The MAX(event_timestamp) aggregation is used to select the most recent qualifying touchpoint, which represents the "last touch" in attribution modeling.

Overall, this approach combines event filtering, correlated subqueries, and aggregate functions to accurately link each purchase to the campaign that most likely influenced the conversion. It is commonly used in marketing analytics, ROI analysis, and campaign performance reporting when last-interaction attribution is required.

Task

Swipe to start coding

Use the events table to assign each user's purchase to their first marketing touchpoint, following the first-touch attribution logic. Your goal is to identify the earliest campaign event for each user that happened before or at the time of their purchase, and assign the conversion to that campaign.

Write a query that, for each user who made a purchase, finds the campaign_id from their earliest campaign event (by event_timestamp) that occurred on or before their purchase event.
The result should include these columns: user_id, conversion_event_id, conversion_time, first_touch_campaign_id.
Only include users who actually made a purchase.
Do not use any code samples already shown above in this chapter.

Solution

Everything was clear?

Thanks for your feedback!

Section 4. Chapter 1

single

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain how first-touch attribution would be implemented in SQL?

What are the pros and cons of using last-touch attribution compared to other models?

Can you show how linear attribution could be calculated using SQL?

Swipe to show menu


              12345678910
            
-- Example: events table showing multiple campaign touchpoints for a single user
SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp,
    campaign_id
FROM events
WHERE user_id = 4
ORDER BY event_timestamp;