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.

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.

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.

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.

Overview: I’d build a staged, evidence-driven attribution pipeline: ingest clean user-level path data, compare heuristic and probabilistic/algorithmic models (Shapley, Markov, Bayesian), validate with experiments/incrementality, then convert channel credits into marginal ROI and an optimization for budgets.

Data needs

• User-level touchstream: timestamped events (impressions, clicks, opens), channel, campaign, cost, conversion label, order value.
• Identity stitching (user_id/session_id), device/channel dedup, attribution window rules.
• Business metadata: channel costs, LTV vs. immediate conversion, seasonality, external factors.
• Tools: SQL for ETL, Python/R for modeling, BI for dashboards.

Model choices

• Heuristic baseline: last-touch, first-touch, linear, time-decay — fast, interpretable baseline.
• Probabilistic/algorithmic:
  • Markov chain removal: estimates transition probabilities, computes channel contribution via drop in conversions when removing a state — handles sequential dependence.
  • Shapley-value (game theory) on paths: fairly apportions credit across touches considering permutations.
  • Causal/Uplift & Bayesian models: hierarchical logistic regression or Bayesian structural time series to estimate incremental lift per channel (better for bias/uncertainty).
  • Hybrid: use Shapley for descriptive crediting, Bayesian uplift to estimate causal impact.

Validation

• Priority: run randomized holdout or geo/temporal experiments to measure incremental ROAS. If full RCT not possible, use quasi-experiments (difference-in-differences, propensity-score matching) and compare predicted incremental conversions vs. observed.
• Backtest: simulate historical spends and compare predicted vs actual conversions; check stability across cohorts/time.
• Sensitivity: test different windows, deduping rules, and path definitions.
• Metrics: MAPE for conversion forecasting, confidence intervals for lift, and consistency with experiment results.

From model outputs to budgets

1. Convert attributed conversions to incremental conversions using causal model outputs or experimental scaling factors.
2. Compute marginal ROI = (incremental revenue per $) per channel and marginal cost curves (diminishing returns estimated via dose–response models).
3. Optimize allocation: maximize total incremental profit subject to constraints (budget, minimum channel spend) using convex optimization or simple greedy allocation across marginal ROI until marginal ROI equals shadow price.
4. Implementation: produce recommended budget by channel with sensitivity bands and suggested experiments for high-uncertainty channels.
5. Monitor: weekly KPI dashboard, re-run models monthly, and validate via rolling experiments.

Example: use SQL to build path table, Python to estimate Markov and Bayesian uplift, run a 12-week geo experiment on paid-search to calibrate uplift multiplier, then solve budget allocation with scipy.optimize to maximize expected profit.

This approach balances interpretability, causal validity, and operational actionability for stakeholder decisions.