Attribution & Conversion Measurement Questions

Multi-touch attribution assigns credit for a conversion across multiple marketing touchpoints. Approaches fall into rule-based/heuristic and data-driven — each with different assumptions, complexity, and validation needs.

Rule-based / heuristic

• Last-touch: credit all to final touch. Simple, stable, easy to implement (SQL/dashboard), but overweights lower-funnel channels and ignores prior influence.
• Linear: equal credit across touches. Neutral, easy to explain, but assumes equal contribution which is rarely true.
• U-shaped (position-based): more weight to first and last touches (e.g., 40/20/40). Reflects discovery and conversion, intuitive for marketers, but weights are arbitrary and may misallocate mid-funnel impact.

Data-driven

• Regression-based (e.g., logistic/linear regression, uplift models): model conversion probability as function of exposures; coefficients estimate marginal contributions. Assumes independence, correct feature specification, and sufficient data. Good for interpretability and controlling covariates (seasonality, spend), but vulnerable to multicollinearity and selection bias.
• Markov chain / removal effect: model touchpoint transitions and compute removal impact on conversion probability. Makes fewer parametric assumptions, captures sequence effects, and quantifies incremental impact. Requires session-level sequence data and is compute/heavy; may under-represent long-term brand effects.

Trade-offs

• Simplicity vs. accuracy: heuristics are fast and explainable; data-driven approaches are more accurate but require data quality, engineering, and statistical expertise.
• Interpretability: regression is interpretable; Markov yields intuitive marginal effects but is less directly linked to spend-efficiency.
• Data requirements: more advanced methods need session-level logs, user identifiers, and sufficient sample size.

Validation under business constraints

1. Sanity checks: compare aggregated channel credit to known spend/CPAs; check seasonality and unusual spikes.
2. Holdout / incrementality tests: run A/B or geo experiments for one or two channels (if possible) to compare predicted vs. observed lift — gold standard for validation.
3. Predictive validation: use model to predict conversions on holdout periods; measure calibration, AUC, or mean absolute error vs. real conversions.
4. Sensitivity analysis: vary model assumptions (e.g., weights in heuristics, feature sets in regression) and measure stability of channel rankings.
5. Business-criteria checks: align results with qualitative knowledge (campaign timing, creative changes) and stakeholder feedback.

Recommended practical path for a Data Analyst

• Start with heuristics (last-touch, linear, U-shaped) in dashboards for quick insights and stakeholder alignment.
• Parallel-build a regression or Markov implementation (sessionized data) to demonstrate improved allocation and run predictive/holdout validation.
• Where feasible, run targeted incrementality tests to confirm high-impact re-allocations before changing budgets.

Example quick SQL for last-touch aggregation:

SELECT channel, COUNT(*) AS conversions
FROM (
  SELECT user_id, MAX(event_time) AS last_time,
         MAX(CASE WHEN event_time = MAX(event_time) OVER (PARTITION BY user_id) THEN channel END) AS last_channel
  FROM events WHERE is_conversion OR is_touch
  GROUP BY user_id
) t
GROUP BY channel;

This balanced approach provides actionable insights while managing complexity and business risk.

Requirements & constraints:

• Goals: new user acquisition and revenue (LTV). Channels: email, paid search, social. Need channel-level and campaign-level ROI, incremental lift, cross-device coverage, privacy-compliant.

High-level plan:

1. Primary events to track

• Exposure events: ad_impression (platform, campaign, creative, timestamp, click_id)
• Interaction events: ad_click, email_send, email_open, email_click
• Engagement & conversion: signup (user_id, anonymous_id, ts), purchase (order_id, user_id, revenue, ts), downstream events (subscription_renewal, churn)
• Micro-conversions: product_view, add_to_cart
• User identity events: login, account_create (email/hash), device_id, cookie_id

2. Attribution window & model

• Window: clicks — 7-day for acquisition, 30-day for revenue; view-through — 1 day for paid; configurable per analysis.
• Primary model: multi-touch probabilistic MTA (Markov chains or Shapley) for channel contribution across touchpaths, supplemented by last-click for simple operational reporting.
• Use time-decay weighting for analyses focusing nearer interactions.

3. Necessary data joins & keys

• Source tables: Ad platforms (gclid/fbclid/etc), Email platform, Web analytics (page events), Backend orders, CRM.
• Join keys: deterministic: user_id (logged-in), email_hash, order_id, click_id when available. Fallback: cookie_id/device_id with timestamp overlap.
• Pipeline: ingest raw events -> normalize schema (event_type, ts, attrs) -> identity stitching -> sessionize -> touchpath construction.
• SQL joins: prefer left joins from conversions to exposures using click/impression windows and dedupe by earliest relevant touch.

4. Incrementality vs baseline

• Best practice: randomized holdout (test/control) at user or geo level for campaigns; measure difference in acquisition and revenue over the attribution window. Compute lift = (metric_test - metric_control) / metric_control with bootstrap CIs.
• If holdouts unavailable: use observational causal methods — difference-in-differences, propensity score matching, or uplift models; still report uncertainty and potential biases.
• Surface baseline: control group's metric baseline; incremental dashboards show absolute lift, relative lift, p-value, and confidence intervals.

5. Cross-device handling

• Deterministic stitching first: unify events by authenticated user_id/email_hash across devices.
• Probabilistic linking: device graph using IP/time/UA patterns only when deterministic absent, with conservative thresholds and privacy review.
• Report coverage: percent of conversions deterministically linked vs probabilistic; flag analyses limited to deterministic subset to avoid overclaiming.

6. Reporting & dashboards

• KPI views: acquisition (new users), revenue (7/30/90 day), CAC, ROAS, LTV.
• Attribution reports: MTA channel contributions, touchpath funnel, last-click table.
• Incrementality reports: test vs control, lift by channel/campaign, statistical significance, recommended budget reallocations.
• Data quality metrics: missing IDs, duplicate orders, join failure rates.

Trade-offs & notes:

• MTA gives nuanced credit but needs good touchpath data; holdouts give causal lift but cost exposure of withheld audiences.
• Be explicit about assumptions, windows, and identity coverage in all stakeholder reports.

Instrumentation and approach (brief):

• Randomize treatment at the user (or user-device if necessary) level and record assignment persistently.
• Log every exposure event with user_id, timestamp, touch_type (in-app banner, email, etc.), experiment_arm, and context (session_id, campaign_id).
• Also log conversions with timestamps and conversion value.

Attribution windows & deduplication:

• Choose a primary window based on expected causal latency (e.g., 7 days for social-share prompts) and pre-register it. Use secondary sensitivity windows (1, 14, 30 days).
• Deduplicate per user per conversion by taking the first conversion timestamp within the window. For event-level attribution, dedupe exposures with ROW_NUMBER() partitioned by user_id, conversion_id ordered by timestamp to identify eligible touches.

SQL example (dedupe eligible touches per conversion):

WITH conv AS (
  SELECT user_id, conversion_ts FROM conversions
),
touches AS (
  SELECT t.*, c.conversion_ts,
         ROW_NUMBER() OVER (PARTITION BY t.user_id, c.conversion_ts ORDER BY t.ts) AS rn
  FROM touches t JOIN conv c
    ON t.user_id = c.user_id
   AND t.ts BETWEEN c.conversion_ts - INTERVAL '7 days' AND c.conversion_ts
)
SELECT * FROM touches WHERE rn = 1; -- first-touch within 7d

Weighting multi-touch exposures:

• Pre-specify a primary attribution model. Options:
  • First-touch / last-touch for simplicity (transparent but biased).
  • Fractional weighting: assign 1/n to each touch within the window.
  • Time-decay: heavier weight to recent touches (exponential decay).
  • Shapley-value for fair contribution if clicks/actions many (computationally heavier).
• For causal inference, prefer intention-to-treat (ITT) at user level to avoid post-treatment bias. Use exposure dosage analyses (e.g., number of touches as treatment intensity) with caution.

Analysis choices to maintain unbiased estimates:

• Primary analysis: ITT — compare outcome rates between randomized treatment and control cohorts regardless of actual exposures.
• Secondary (per-protocol / dose-response): model conversion ~ treatment_assignment + covariates using logistic or Poisson regression; include exposure count as mediator with instrumented approaches (e.g., randomized encouragement design + IV using assignment as instrument for exposure).
• For rare events: use aggregated counts (user-level binary converted or not within window), and apply:
  • Fisher’s exact test or permutation tests for small counts,
  • Poisson or negative binomial regression for counts,
  • Exact or bootstrap confidence intervals for rates.
• Adjust for clustering and multiple exposures: cluster standard errors by user; if users nested by cohort/region, cluster accordingly.
• Pre-specify endpoints and stopping rules; avoid peeking—use group-sequential or alpha-spending if interim checks needed.

Practical safeguards:

• Validate randomization balance on pre-period covariates and stratify or reweight if imbalance exists.
• Monitor instrumentation (exposure loss, ID collisions) and do backfill checks.
• Run sensitivity analyses across attribution windows and weighting schemes; report both ITT and adjusted estimates with clear causal assumptions.

Takeaway:
Prioritize clear randomization + ITT for unbiased causal claims, log rich timestamps for flexible attribution, use conservative statistical methods for rare events, and present sensitivity analyses across windows and weighting to show robustness.

Approach: compute transactions in the target month, link each transaction to the user's sessions up to the transaction time. For last-touch: pick the most recent session before (or at) the transaction and attribute full amount to that session's channel (fall back to users.first_touch_channel if no prior session). For equal-weighted multi-touch across sessions: find all distinct sessions for that user in a lookback window (e.g., same month or X days before transaction), split the transaction amount equally across the session channels.

SQL (Postgres-style) — parameterize target_month_start:

-- Set month boundaries (example: '2025-04-01')
WITH params AS (
  SELECT date '2025-04-01' AS month_start,
         (date '2025-04-01' + interval '1 month') AS month_end
),

tx_in_month AS (
  SELECT t.*
  FROM transactions t
  JOIN params p ON t.occurred_at >= p.month_start AND t.occurred_at < p.month_end
),

-- sessions before or at each transaction (for last-touch)
tx_sessions AS (
  SELECT
    tx.transaction_id,
    tx.user_id,
    tx.amount,
    s.session_id,
    s.channel,
    s.occurred_at AS session_at,
    tx.occurred_at AS tx_at
  FROM tx_in_month tx
  LEFT JOIN sessions s
    ON s.user_id = tx.user_id
    AND s.occurred_at <= tx.occurred_at
),

last_touch AS (
  -- pick the latest session per transaction
  SELECT DISTINCT ON (transaction_id)
    transaction_id,
    COALESCE(channel, u.first_touch_channel) AS attrib_channel,
    amount
  FROM tx_sessions ts
  LEFT JOIN users u ON ts.user_id = u.user_id
  ORDER BY transaction_id, session_at DESC NULLS LAST
),

last_touch_revenue AS (
  SELECT attrib_channel AS channel, SUM(amount) AS revenue
  FROM last_touch
  GROUP BY attrib_channel
),

-- Multi-touch equal-weighted: use all sessions in the same month before tx (change window as needed)
tx_sessions_mt AS (
  SELECT
    tx.transaction_id,
    tx.user_id,
    tx.amount,
    s.session_id,
    s.channel
  FROM tx_in_month tx
  LEFT JOIN sessions s
    ON s.user_id = tx.user_id
    AND s.occurred_at >= (SELECT month_start FROM params)
    AND s.occurred_at <= tx.occurred_at
),

tx_session_counts AS (
  SELECT transaction_id, COUNT(DISTINCT session_id) AS session_count
  FROM tx_sessions_mt
  GROUP BY transaction_id
),

attrib_mt AS (
  SELECT
    tsm.transaction_id,
    COALESCE(tsm.channel, u.first_touch_channel) AS channel,
    tsm.amount::numeric / NULLIF(tsc.session_count,0) AS split_amount
  FROM tx_sessions_mt tsm
  LEFT JOIN tx_session_counts tsc USING (transaction_id)
  LEFT JOIN users u ON tsm.user_id = u.user_id
  -- Handle transactions with zero sessions: attribute to first_touch_channel below
),

-- handle transactions with zero sessions: attribute full amount to first_touch_channel
no_session_tx AS (
  SELECT tx.transaction_id, u.first_touch_channel AS channel, tx.amount::numeric AS split_amount
  FROM tx_in_month tx
  LEFT JOIN (
    SELECT transaction_id FROM tx_session_counts WHERE session_count = 0
  ) z USING (transaction_id)
  JOIN users u ON tx.user_id = u.user_id
)

SELECT 'last_touch' AS model, channel, revenue
FROM last_touch_revenue

UNION ALL

SELECT 'multi_touch_equal' AS model, channel, SUM(split_amount) AS revenue
FROM (
  SELECT channel, split_amount FROM attrib_mt
  UNION ALL
  SELECT channel, split_amount FROM no_session_tx
) x
GROUP BY channel
ORDER BY model, revenue DESC;

Key points:

• Last-touch uses the single latest session before the transaction; fallback to first_touch_channel if none.
• Multi-touch splits amount equally across sessions in the chosen window (here: same month up to tx). Change window to N days if needed.
  Edge cases:
• No prior sessions -> use first_touch_channel.
• Duplicate sessions with same channel -> you may dedupe by channel if you want channel-level unique touches.
• NULL channels should be handled (coalesce or filter).
  Performance:
• Ensure indexes on sessions(user_id, occurred_at) and transactions(user_id, occurred_at) for scale.

High-level approach

• Prioritize deterministic joins (persistent user_id from login) and fall back to probabilistic device-graph joins only for aggregated reporting. Instrument server-side events to avoid client-side loss (cookie deletion).

Instrumentation

• user_id (nullable): stable app/account ID set at login/registration.
• anon_id / device_id: app-install ID (IDFA/AAID or SDK-generated GUID) and a browser anon_id stored server-side when available.
• event_id, timestamp, event_type, channel, campaign, attribution_metadata.
• login events: emit before/after merge so you capture pre-login anon activity.
• hashed_pii: only store salted SHA256(email) when consented; rotate salt periodically.
• consent flag and deletion flags.

Data joining logic

1. Deterministic: join by user_id across platforms — attribute all events before/after login to that user (use login-time mapping to backfill).
2. Deterministic secondary: join hashed_pii where consent exists.
3. Probabilistic/device-graph: link anon_ids across devices using heuristics (same IP + user agent + short time window) but mark links as low-confidence and keep TTL (e.g., 30 days).
4. Build a unified events table with columns: user_id, anon_id, device_confidence (high/low), timestamp, touch_attribution (first/last/touch_weight).

SQL example (simplified deterministic backfill)

-- backfill anon events to user after login
WITH logins AS (
  SELECT user_id, anon_id, MIN(timestamp) AS first_login
  FROM events WHERE event_type='login' GROUP BY 1,2
)
UPDATE events e
SET user_id = l.user_id
FROM logins l
WHERE e.anon_id = l.anon_id AND e.timestamp < l.first_login AND e.user_id IS NULL;

Attribution/reporting strategy

• Maintain multi-model output: first-touch, last-touch, and a weighted multi-touch (e.g., time-decay).
• Use deterministic attribution for any conversion where user_id exists at conversion → attribute across earlier touches tied to same user_id.
• For conversions without deterministic link, report separately as "probabilistic" with confidence buckets; exclude low-confidence links from high-stakes decisions.
• Aggregate reporting only: avoid exposing device-level graphs in dashboards. Show conversion counts, revenue, and confidence intervals.
• Privacy safeguards: hash identifiers, drop raw device identifiers after graph construction, honor Do Not Track/consent flags, and surface modelled vs deterministic proportions.

Metrics & monitoring

• Track % conversions attributed deterministically vs probabilistically, % anonymous conversions, and model precision (via A/B tests or deterministic holdouts).
• Provide stakeholders both conservative (deterministic-only) and full (deterministic+probabilistic) views, with clear labels and confidence guidance.