Marketing attribution

Take the same two purchase journeys and allocate revenue under first touch, last touch, linear, time decay, and channel weights — then shorten the lookback and watch the credit shift.

This advanced tutorial assumes entity bindings from E-commerce and array operations from OBT modeling . Inside TRANSFORM(), read the nested SELECT as SQL over one conversion's touchpoint array. Run Data and Model before comparing attribution rules.

Data

Two purchases, six touchpoints, four campaigns. Alex’s path includes a low-value Display impression so weighted models have something to down-weight.

FeatureQL

/* SQL */
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 Jan 20): Blog → Search → Display impression → Email. Blake ($60 on Jan 15): Search → Email.

Model

Conversions are the entity you bind. LOOKBACK_DAYS is an input — the window is a parameter, not a hard-coded filter. Touchpoints live in one array feature; each query filters that array for the conversion under evaluation.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.MKT AS
SELECT
    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 conversions and the shared touchpoint array:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.MKT AS
SELECT
    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

A touch counts if it belongs to the converting user, is strictly before the conversion, and falls inside the lookback window.

First touch (30 days)

100% of revenue to the earliest qualifying touch.

FeatureQL

WITH
    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(
        SELECT * WHERE USER_ID = CU AND TS < CTS AND TS >= DATE_ADD(CTS, 'day', -LB)
    ),
    sorted := qual.TRANSFORM(SELECT * ORDER BY TS ASC),
    n_qual := ARRAY_COUNT(sorted),
    win := IF(n_qual > 0, sorted[1][campaign], NULL(VARCHAR))
SELECT
    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

Blog 90 (Alex), Search 60 (Blake).

Last touch (30 days)

100% to the latest qualifying touch.

FeatureQL

WITH
    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(
        SELECT * WHERE USER_ID = CU AND TS < CTS AND TS >= DATE_ADD(CTS, 'day', -LB)
    ),
    sorted := qual.TRANSFORM(SELECT * ORDER BY TS ASC),
    n_qual := ARRAY_COUNT(sorted),
    win := IF(n_qual > 0, ELEMENT_AT_POS(sorted, -1)[campaign], NULL(VARCHAR))
SELECT
    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

Email 150 — both conversions end on Email.

Linear (30 days)

Split each conversion evenly across its qualifying touches.

FeatureQL

WITH
    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(
        SELECT * WHERE USER_ID = CU AND TS < CTS AND TS >= DATE_ADD(CTS, 'day', -LB)
    ),
    n_qual := ARRAY_COUNT(qual),
    line_part := CARRY(n_qual AS nq INTO CARRY(crev AS creva INTO qual)).TRANSFORM(
        SELECT CAMPAIGN AS CAMPAIGN, CAST(CREVA AS DOUBLE) / CAST(NQ AS DOUBLE) AS PART_AMT
    ),
    u := UNNEST(line_part),
    cname := u[campaign],
    pa := u[part_amt]
SELECT
    campaign := cname,
    attributed := 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 DECIMAL
Blog	22.5
Display	22.5
Email	52.5
Search	52.5

Alex has four touches → $22.50 each. Blake has two → $30 each. Totals: Search 52.5, Email 52.5, Blog 22.5, Display 22.5.

Shorter lookback

Same first-touch rule; only LOOKBACK_DAYS changes to 7. Early touches drop out of the window.

FeatureQL

WITH
    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(
        SELECT * WHERE USER_ID = CU AND TS < CTS AND TS >= DATE_ADD(CTS, 'day', -LB)
    ),
    sorted := qual.TRANSFORM(SELECT * ORDER BY TS ASC),
    n_qual := ARRAY_COUNT(sorted),
    win := IF(n_qual > 0, sorted[1][campaign], NULL(VARCHAR))
SELECT
    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 fall outside seven days → first credit moves to Display 90. Blake’s Search drops → Email 60. Lookback is part of the model definition, not a footnote.

Time decay (7-day half-life)

Weight each touch by 2^(-days_before_conversion / 7), normalize to 1 per conversion, then allocate revenue.

FeatureQL

WITH
    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(
        SELECT * WHERE USER_ID = CU AND TS < CTS AND TS >= DATE_ADD(CTS, 'day', -LB)
    ),
    wts := CARRY(cts AS conv_ts INTO qual).TRANSFORM(
        SELECT CAMPAIGN, POW(2E0, CAST(-DATE_SUBTRACT(CONV_TS, TS, 'DAY') AS DOUBLE) / 7E0) AS RW
    ),
    sumw := ARRAY_SUM(wts[rw]),
    alloc := CARRY(sumw AS swm INTO CARRY(crev AS creva INTO wts)).TRANSFORM(
        SELECT CAMPAIGN, CAST(CREVA AS DOUBLE) * (RW / SWM) AS PART_AMT
    ),
    u := UNNEST(alloc),
    cname := u[campaign],
    pa := u[part_amt]
SELECT
    campaign := cname,
    attributed := 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 DECIMAL
Blog	9.662152071656072
Display	26.008584668970048
Email	73.66363808501173
Search	40.66562517436216

Email leads (~~74); Blog is smallest (~~10) because it sits farthest from the purchase. Late touches get exponentially more credit than linear.

Channel-weighted

Like linear, but each touch’s share is proportional to a channel weight. Here impressions are 0.3; everything else is 1.0.

FeatureQL

WITH
    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(
        SELECT * WHERE USER_ID = CU AND TS < CTS AND TS >= DATE_ADD(CTS, 'day', -LB)
    ),
    tw := qual.TRANSFORM(
        SELECT
            CAMPAIGN,
            CASE
                WHEN CHANNEL = 'meta' AND TOUCHPOINT_TYPE = 'ad_impression' THEN 0.3E0
                ELSE 1.0E0
            END AS TW
    ),
    sumtw := ARRAY_SUM(tw[tw]),
    alloc := CARRY(sumtw AS swm INTO CARRY(crev AS creva INTO tw)).TRANSFORM(
        SELECT CAMPAIGN, CAST(CREVA AS DOUBLE) * (TW / SWM) AS PART_AMT
    ),
    u := UNNEST(alloc),
    cname := u[campaign],
    pa := u[part_amt]
SELECT
    campaign := cname,
    attributed := 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 DECIMAL
Blog	27.272727272727273
Display	8.181818181818182
Email	57.27272727272727
Search	57.27272727272727

Display falls to ~8 (vs 22.5 linear). Search and Email stay near 57 each. Impressions still participate — they just count for less than clicks.

What's next

Analytics overview — concept map for this series
Healthcare episodes — merge overlaps, readmissions, lab trends
SaaS metrics — MRR and customer health
Financial consolidation — rule-driven rollups

Last update at: 2026/08/04 11:25:38

On this page

Data Model First touch (30 days)Last touch (30 days)Linear (30 days)Shorter lookback Time decay (7-day half-life)Channel-weighted What's next