Marketing attribution

This tutorial allocates the same purchase revenue five different ways so you can see exactly what an attribution model changes.

You will compare first touch, last touch, linear, time-decay, and channel-weighted credit. You will also shorten the lookback window and watch previously eligible touches disappear.

This tutorial assumes entity bindings from E-commerce . The new working pattern is TRANSFORM(): read each nested RETURN as a small SQL query over one conversion's touchpoint array. Run Data and Model before comparing attribution rules.

Meet the two journeys

The whole dataset is two purchases, six touchpoints, and four campaigns:

FeatureQL

CREATE SCHEMA IF NOT EXISTS tutorial_mkt;
--
DROP TABLE IF EXISTS tutorial_mkt.conversions;
--
DROP TABLE IF EXISTS tutorial_mkt.campaigns;
--
CREATE TABLE tutorial_mkt.campaigns (
  id BIGINT,
  name VARCHAR
);
--
INSERT INTO tutorial_mkt.campaigns VALUES
  (1, 'Search'),
  (2, 'Blog'),
  (3, 'Email'),
  (4, 'Display');
--
CREATE TABLE tutorial_mkt.conversions (
  id BIGINT,
  user_id BIGINT,
  revenue BIGINT,
  conv_ts TIMESTAMP
);
--
INSERT INTO tutorial_mkt.conversions VALUES
  (1, 1, 90, TIMESTAMP '2024-01-20 15:00:00'),
  (2, 2, 60, TIMESTAMP '2024-01-15 16:00:00');
--
SELECT CAST(COUNT() AS INTEGER) AS cnt FROM tutorial_mkt.conversions;

Result

Count BIGINT
2

Alex, $90 on January 20: Blog → Search → Display impression → Email
Blake, $60 on January 15: Search → Email

Alex's Display touch is an impression rather than a click. That gives the weighted model a reason to assign it less credit.

Model conversions and touchpoints

Conversions define the outer grain: each calculation begins with one purchase and its revenue. LOOKBACK_DAYS is an input, so the eligibility window can change without rewriting the attribution formula.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.MKT AS
PERSIST
    conversions := ENTITY(),
    conversion_id := INPUT(BIGINT#conversions),
    lookback_days := INPUT(BIGINT)
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.MKT.CONVERSIONS	CREATED	Feature created as not exists
FM.MKT.CONVERSION_ID	CREATED	Feature created as not exists
FM.MKT.LOOKBACK_DAYS	CREATED	Feature created as not exists

Map the conversions and the shared touchpoint array:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.MKT AS
PERSIST
    tables.conversions := EXTERNAL_COLUMNS(
        id BIGINT#conversions BIND TO conversion_id,
        user_id BIGINT,
        revenue BIGINT,
        conv_ts TIMESTAMP
        FROM TABLE(tutorial_mkt.conversions)
    ),
    touchpoint_seed := ARRAY(
        ROW(
            1 AS tp_id,
            1 AS user_id,
            2 AS campaign_id,
            'Blog' AS campaign,
            'organic' AS channel,
            'blog_visit' AS touchpoint_type,
            TIMESTAMP '2024-01-05 10:00:00' AS ts
        ),
        ROW(
            2 AS tp_id,
            1 AS user_id,
            1 AS campaign_id,
            'Search' AS campaign,
            'google' AS channel,
            'ad_click' AS touchpoint_type,
            TIMESTAMP '2024-01-12 14:00:00' AS ts
        ),
        ROW(
            3 AS tp_id,
            1 AS user_id,
            4 AS campaign_id,
            'Display' AS campaign,
            'meta' AS channel,
            'ad_impression' AS touchpoint_type,
            TIMESTAMP '2024-01-15 10:00:00' AS ts
        ),
        ROW(
            4 AS tp_id,
            1 AS user_id,
            3 AS campaign_id,
            'Email' AS campaign,
            'email' AS channel,
            'email_click' AS touchpoint_type,
            TIMESTAMP '2024-01-18 11:00:00' AS ts
        ),
        ROW(
            5 AS tp_id,
            2 AS user_id,
            1 AS campaign_id,
            'Search' AS campaign,
            'google' AS channel,
            'ad_click' AS touchpoint_type,
            TIMESTAMP '2024-01-08 09:00:00' AS ts
        ),
        ROW(
            6 AS tp_id,
            2 AS user_id,
            3 AS campaign_id,
            'Email' AS campaign,
            'email' AS channel,
            'email_click' AS touchpoint_type,
            TIMESTAMP '2024-01-14 10:00:00' AS ts
        )
    )::ARRAY<
        ROW<
            tp_id BIGINT,
            user_id BIGINT,
            campaign_id BIGINT,
            campaign VARCHAR,
            channel VARCHAR,
            touchpoint_type VARCHAR,
            ts TIMESTAMP
        >
    >
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.MKT.TABLES.CONVERSIONS	CREATED	Feature created as not exists
FM.MKT.TOUCHPOINT_SEED	CREATED	Feature created as not exists

Every model starts with the same qualifying set. A touch counts only when it:

belongs to the converting user;
occurred before the purchase; and
falls within the bound lookback window.

The following sections change how credit is divided, not which purchase revenue is available.

Give all credit to the first touch

With a 30-day lookback, assign each purchase entirely to its earliest qualifying touch:

FeatureQL

DEFINE
    cu := tables.conversions[user_id],
    crev := tables.conversions[revenue],
    cts := tables.conversions[conv_ts],
    qual := CARRY(
        lookback_days AS lb
        INTO CARRY(cts AS cts INTO CARRY(cu AS cu INTO touchpoint_seed))
    ).TRANSFORM(
        RETURN *
        WHERE user_id = cu AND ts < cts AND ts >= DATE_ADD(cts, -lb, 'day')
    ),
    sorted := qual.TRANSFORM(
        RETURN *
        ORDER BY ts
    ),
    n_qual := ARRAY_COUNT(sorted),
    win := IF(n_qual > 0, sorted[1][campaign], NULL::VARCHAR)
RETURN
    campaign := win,
    attributed := SUM(crev) GROUP BY (win)
FROM FM.MKT
FOR
    lookback_days := BIND_VALUE(30),
    conversion_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY campaign
;

Result

CAMPAIGN VARCHAR	ATTRIBUTED BIGINT
Blog	90
Search	60

Alex's 90 goes to Blog. Blake's 60 goes to Search.

Give all credit to the last touch

Keep the same journeys and window, but select the latest touch instead:

FeatureQL

DEFINE
    cu := tables.conversions[user_id],
    crev := tables.conversions[revenue],
    cts := tables.conversions[conv_ts],
    qual := CARRY(
        lookback_days AS lb
        INTO CARRY(cts AS cts INTO CARRY(cu AS cu INTO touchpoint_seed))
    ).TRANSFORM(
        RETURN *
        WHERE user_id = cu AND ts < cts AND ts >= DATE_ADD(cts, -lb, 'day')
    ),
    sorted := qual.TRANSFORM(
        RETURN *
        ORDER BY ts
    ),
    n_qual := ARRAY_COUNT(sorted),
    win := IF(n_qual > 0, ELEMENT_AT_POS(sorted, -1)[campaign], NULL::VARCHAR)
RETURN
    campaign := win,
    attributed := SUM(crev) GROUP BY (win)
FROM FM.MKT
FOR
    lookback_days := BIND_VALUE(30),
    conversion_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY campaign
;

Result

CAMPAIGN VARCHAR	ATTRIBUTED BIGINT
Email	150

Both journeys end on Email, so Email receives the full 150.

The underlying events did not change. Only the rule for choosing a touch changed.

Split credit evenly

Linear attribution divides each conversion equally across all of its qualifying touches:

FeatureQL

DEFINE
    cu := tables.conversions[user_id],
    crev := tables.conversions[revenue],
    cts := tables.conversions[conv_ts],
    qual := CARRY(
        lookback_days AS lb
        INTO CARRY(cts AS cts INTO CARRY(cu AS cu INTO touchpoint_seed))
    ).TRANSFORM(
        RETURN *
        WHERE user_id = cu AND ts < cts AND ts >= DATE_ADD(cts, -lb, 'day')
    ),
    n_qual := ARRAY_COUNT(qual),
    line_part := CARRY(n_qual AS nq INTO CARRY(crev AS creva INTO qual)).TRANSFORM(
        RETURN
            campaign,
            part_amt := creva::DOUBLE / nq::DOUBLE
    ),
    u := UNNEST(line_part),
    cname := u[campaign],
    pa := u[part_amt]
RETURN
    campaign := cname,
    attributed := UNSAFE SUM(pa) GROUP BY (cname)
FROM FM.MKT
FOR
    lookback_days := BIND_VALUE(30),
    conversion_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY campaign
;

Result

CAMPAIGN VARCHAR	ATTRIBUTED DOUBLE
Blog	22.5
Display	22.5
Email	52.5
Search	52.5

Alex has four touches, so each receives 22.50. Blake has two, so each receives 30.

After adding the journeys together, Search and Email each receive 52.50; Blog and Display each receive 22.50. Each conversion still allocates exactly 100% of its revenue.

Shorten the lookback window

Return to first-touch attribution but bind LOOKBACK_DAYS to 7 instead of 30:

FeatureQL

DEFINE
    cu := tables.conversions[user_id],
    crev := tables.conversions[revenue],
    cts := tables.conversions[conv_ts],
    qual := CARRY(
        lookback_days AS lb
        INTO CARRY(cts AS cts INTO CARRY(cu AS cu INTO touchpoint_seed))
    ).TRANSFORM(
        RETURN *
        WHERE user_id = cu AND ts < cts AND ts >= DATE_ADD(cts, -lb, 'day')
    ),
    sorted := qual.TRANSFORM(
        RETURN *
        ORDER BY ts
    ),
    n_qual := ARRAY_COUNT(sorted),
    win := IF(n_qual > 0, sorted[1][campaign], NULL::VARCHAR)
RETURN
    campaign := win,
    attributed := SUM(crev) GROUP BY (win)
FROM FM.MKT
FOR
    lookback_days := BIND_VALUE(7),
    conversion_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY campaign
;

Result

CAMPAIGN VARCHAR	ATTRIBUTED BIGINT
Display	90
Email	60

Alex's Blog and Search touches are now too old, so the first eligible touch becomes Display and receives 90. Blake's Search touch also drops out, moving 60 to Email.

This result shows why the lookback is part of the model, not a reporting footnote: it changes which channels can receive credit.

Favor recent touches with time decay

Return the lookback to 30 days for the remaining models. Time-decay attribution avoids choosing only one touch: give each touch a weight of 2^(-days_before_conversion / 7), normalize the weights to one within each journey, then allocate revenue.

FeatureQL

DEFINE
    cu := tables.conversions[user_id],
    crev := tables.conversions[revenue],
    cts := tables.conversions[conv_ts],
    qual := CARRY(
        lookback_days AS lb
        INTO CARRY(cts AS cts INTO CARRY(cu AS cu INTO touchpoint_seed))
    ).TRANSFORM(
        RETURN *
        WHERE user_id = cu AND ts < cts AND ts >= DATE_ADD(cts, -lb, 'day')
    ),
    wts := CARRY(cts AS conv_ts INTO qual).TRANSFORM(
        RETURN
            campaign,
            rw := POW(2e0, (-DATE_SUBTRACT(conv_ts, ts, 'day'))::DOUBLE / 7e0)
    ),
    sumw := ARRAY_SUM(wts[rw]),
    alloc := CARRY(sumw AS swm INTO CARRY(crev AS creva INTO wts)).TRANSFORM(
        RETURN
            campaign,
            part_amt := creva::DOUBLE * (rw / swm)
    ),
    u := UNNEST(alloc),
    cname := u[campaign],
    pa := u[part_amt]
RETURN
    campaign := cname,
    attributed := UNSAFE SUM(pa) GROUP BY (cname)
FROM FM.MKT
FOR
    lookback_days := BIND_VALUE(30),
    conversion_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY campaign
;

Result

CAMPAIGN VARCHAR	ATTRIBUTED DOUBLE
Blog	9.662152071656072
Display	26.008584668970048
Email	73.66363808501173
Search	40.66562517436216

Email receives the most credit, about 74, because it is closest to both purchases. Blog receives the least, about 10, because it is farthest from Alex's purchase.

The normalized shares preserve the total 150 while favoring recent evidence.

Apply channel-quality weights

Keep the 30-day lookback, but change the weight for a different reason. Clicks receive 1.0; impressions receive 0.3. Each touch's share is proportional to that channel weight.

FeatureQL

DEFINE
    cu := tables.conversions[user_id],
    crev := tables.conversions[revenue],
    cts := tables.conversions[conv_ts],
    qual := CARRY(
        lookback_days AS lb
        INTO CARRY(cts AS cts INTO CARRY(cu AS cu INTO touchpoint_seed))
    ).TRANSFORM(
        RETURN *
        WHERE user_id = cu AND ts < cts AND ts >= DATE_ADD(cts, -lb, 'day')
    ),
    tw := qual.TRANSFORM(
        RETURN
            campaign,
            tw := CASE
                WHEN channel = 'meta' AND touchpoint_type = 'ad_impression' THEN 0.3e0
                ELSE 1.0e0
            END
    ),
    sumtw := ARRAY_SUM(tw[tw]),
    alloc := CARRY(sumtw AS swm INTO CARRY(crev AS creva INTO tw)).TRANSFORM(
        RETURN
            campaign,
            part_amt := creva::DOUBLE * (tw / swm)
    ),
    u := UNNEST(alloc),
    cname := u[campaign],
    pa := u[part_amt]
RETURN
    campaign := cname,
    attributed := UNSAFE SUM(pa) GROUP BY (cname)
FROM FM.MKT
FOR
    lookback_days := BIND_VALUE(30),
    conversion_id := BIND_VALUES(ARRAY(1, 2))
ORDER BY campaign
;

Result

CAMPAIGN VARCHAR	ATTRIBUTED DOUBLE
Blog	27.272727272727273
Display	8.181818181818182
Email	57.27272727272727
Search	57.27272727272727

Display falls to about 8, compared with 22.50 under linear attribution. Blog receives about 27, while Search and Email each rise to roughly 57. The impression still participates; it simply carries less evidence than a click.

What's next

You have held the journeys constant while changing eligibility, selection, and weighting rules. That separation makes attribution models easier to compare and explain.

Continue with Healthcare episodes for interval and sequence analysis, or OBT modeling for deeper work inside nested arrays.

Last update at: 2026/10/13 08:32:21

On this page

Meet the two journeys Model conversions and touchpoints Give all credit to the first touch Give all credit to the last touch Split credit evenly Shorten the lookback window Favor recent touches with time decay Apply channel-quality weights What's next