Attribution & Conversion Measurement Questions

Requirements & objectives:

• Accurately credit channels (ads, promotions), restaurant partners, referrals across multi-touch journeys with possible delayed conversions.
• Support near-real-time and batch reporting, explainability, experiments validation, and scale to millions of events/day.

Data collection:

• Event-level ingestion: impressions, clicks, promotion views/redemptions, referral codes, order events, restaurant interactions; include timestamps, user_id, device_id, campaign_id, partner_id, attribution_token, client and server-side identifiers.
• Persist touch sequences per user (sessionization, dedupe, TTL for stale touches).
• Ingest offline conversions (call-center, POS) with deterministic joins (order id, phone, referral code) and probabilistic matching (device graphs) plus confidence scores.
• Store feature tables accessible to modeling layer.

Architecture (high-level):

• Streaming pipeline (Kafka) → enrichment (user graph, campaign metadata) → sessionizer → touch-store (timeseries DB / BigQuery / Snowflake) → attribution engine (batch/model serving) → reporting/ML features.
• Compute: distributed Spark/Dataproc for heavy ETL; Flink/Beam for streaming attribution.

Attribution methods & trade-offs:

• Rule-based (last-touch, first-touch, time-decay): simple, fast, explainable; biased for multi-touch and delayed effects.
• Model-based (probabilistic, uplift, survival models): estimate incremental impact, handle time-lag (time-to-conversion models, hazard models), control for confounders, better for long-term ROI. Requires careful feature engineering and causal assumptions.
• Game-theoretic (Shapley): fair, axiomatic credit allocation using marginal contribution; interpretable per-order but expensive: naive Shapley is O(2^n). Use sampling (Monte Carlo Shapley), or weighted/approximate Shapley (Truncated sequences, kernel SHAP-like).

Recommended hybrid approach:

• Use rule-based for realtime low-latency dashboards (e.g., last-click with TTL), and model-based + approximate Shapley offline for billing/strategic decisions.
• Train a conversion model (e.g., gradient boosted trees) to predict probability of conversion given sequence and time; compute marginal contributions by perturbing touches or using SHAP values on the model to approximate Shapley.

Handling offline conversions:

• Deterministic joins when possible; propagate attribution using stored touch sequences and time windows.
• For probabilistic matches, attach match-confidence and propagate uncertainty into credit using weighted contributions.

Computational scaling:

• Precompute sessionized sequences in batch and cache per-user touch windows.
• For Shapley: sample subsets, limit max touches (e.g., last 6), parallel Monte Carlo across cluster, use influence functions or model-agnostic SHAP approximations to reduce calls.
• Use vectorized model scoring and map-reduce for marginal computations; store intermediate results for reuse.

Validation against randomized experiments:

• Run holdout/A/B experiments: randomize exposure to treatments (ads, promos, restaurant placements) at user or region level.
• Compare attribution-derived incremental estimates to experiment-measured lift (ATE); calibrate model outputs to match experimental lift.
• Use backtesting: simulate attribution over past experiment windows; check bias, variance, and confidence intervals.
• Perform sensitivity analyses (vary time windows, matching thresholds, number of Shapley samples).

Metrics & monitoring:

• Track consistency: predicted incremental ROI vs experimental ROI, unexplained conversion fraction, attribution stability over time.
• Monitor latency, pipeline failure rates, match confidence distributions, and cost per attributed conversion.

Governance & productization:

• Version models and attribution rules; document assumptions.
• Expose transparency: per-order attribution breakdown, confidence intervals, and recommended actionability (pause/scale campaigns).
• Align finance/biz stakeholders on which method feeds billing vs optimization.

Key trade-offs summary:

• Latency vs accuracy: real-time rules vs slower model/Shapley pipelines.
• Explainability vs causal validity: rules are clear but biased; model/Shapley more accurate but rely on model/assumption quality.
• Cost vs fidelity: approximate Shapley and sampling provide practical middle ground.

This design balances operational constraints with rigorous causal validation and provides paths to scale and reconcile online and offline conversions.

Situation: Marketing reports that the recommender, which favors higher‑margin items, may be “cannibalizing” sales of promoted (discounted) items — we need a causal attribution to measure net revenue impact.

Approach (high level)

• Run a randomized experiment where users (or sessions) are randomly assigned to: Control (current recommender) vs Treatment (margin‑aware recommender). Where randomization isn’t feasible, use quasi‑experimental causal methods (instrumental variable, difference‑in‑differences, or propensity‑score–matched synthetic controls) or an uplift/randomized encouragement design if only exposure can be nudged.
• Combine A/B for average treatment effect and uplift modeling for targeted impact (who gains/loses).

Datasets required

• Orders table: order_id, user_id, timestamp, item_ids, quantities, item_prices, discounts, net_revenue
• Catalog: item_id, base_price, cost, margin, promo_flag
• Exposure logs: which recommendation(s) were shown/clicked, rank, page
• User profile & behavior: past orders, segments, tenure, recent sessions
• Marketing/promotion calendar: active promotions by item/time
• Returns/refunds and long‑term LTV data

Modeling choices

• Primary: A/B test with random assignment -> estimate ITT and complier average causal effect (CACE) if not everyone sees/clicks recommendations.
• Secondary: Uplift model (two-model or direct uplift) to identify heterogenous effects for targeting: who increases basket size vs who shifts promo purchases.
• Adjustments: Regression with covariates (pre‑treatment behavior), clustering by user, and pre‑post difference‑in‑differences if rollout is staggered.
• Causal checks: balance tests, SUTVA considerations, pre‑trend tests, and power calculations accounting for skewed revenue distribution (use log or heavy‑tailed robust estimators, or quantile treatment effects).

Attribution logic

• Net revenue impact = change in total net_revenue (including margin), not just AOV, within evaluation window.
• Decompose effect into:
  1. Incremental full‑price / higher‑margin sales
  2. Decline in promo item revenue (cannibalization)
  3. Changes in conversion rate and order frequency
• Compute per‑user and aggregated uplift in margin dollars over a suitable horizon (30–90 days) to capture downstream effects.

Guardrail metrics to report

• Primary: Incremental net margin per user and total incremental margin (with confidence intervals)
• Secondary/guardrails:
  • Conversion rate (orders per active user)
  • Promo item revenue and units sold (absolute and % change)
  • Average order value and basket size
  • Promo cannibalization rate = reduction in promo revenue attributable to treatment
  • Customer churn / repeat purchase rate and 30/90‑day LTV uplift
  • Return/refund rate
  • Exposure→click and click→buy funnel metrics (to detect UI effects)
  • Segment breakouts (new vs returning, high vs low spend)
• Statistical/operational: sample size, p‑values, lift with CIs, and runtime stability (time‑series of daily effect).

Why this approach

• Randomized A/B gives unbiased average causal effect; uplift modeling adds actionability by identifying who to target to maximize net margin while minimizing promo cannibalization. Guardrails ensure we measure both intended benefit and unintended harms (marketing KPIs, retention, long‑term value).

Attribution window length directly changes which post-click/post-view conversions are credited to a campaign, so measured conversions and ROI scale with window size. Short windows (1-day) miss later conversions (understating performance) but reduce false positives from unrelated conversions; long windows (30-day) capture more conversions but increase noise and overlap (multi-touch), inflating ROI. Choice also affects pacing, bidding, and budget allocation.

Sensitivity analysis approach:

• Compute cumulative conversion curves: for each campaign/ad cohort, calculate cumulative conversions vs days-since-click up to 30 days.
• Plot distributions (median, 25/75 pct) and the incremental conversion gain from 1→7→30 days.
• Calculate ROI and CPA for each window: revenue_attributed(window) / spend and spend / conversions.
• Quantify stability: relative percent change and elasticity = (ROI(window2)-ROI(window1))/Δdays.
• Test significance: survival analysis (Kaplan–Meier) or hazard models to model time-to-conversion and bootstrapped CIs on ROI differences.
• Check overlap: compute fraction of conversions attributed to multiple campaigns as window grows; run incremental experiments (holdouts/geo splits) where possible to measure true lift per window.

Choosing a default for a consumer app with median time-to-conversion = 3 days:

• Use a window slightly above the median to capture the bulk of conversions — recommend a 7-day window. Rationale: 7-day captures most users beyond the 3-day median (covers tail), balances signal vs noise, aligns with industry norms, and provides stable ROI estimates. Validate by showing cumulative curve: if 80–90% of conversions occur by day 7, it's appropriate. Also provide policy: use 1-day for fast-funnel tactical reports, 7-day default for performance/optimization, and 30-day for long-term lifetime attribution and sensitivity reporting. Regularly re-evaluate as user behavior or product funnels change.

Goal & framing:

• Allocate incremental LTV to channels accounting for heterogeneous user responses and time-varying channel effects, focusing on causal incremental contribution rather than naive last-touch.

Modeling approach:

1. Causal backbone: combine randomized experiments when available with observational causal methods (propensity scores / double machine learning) to adjust for selection.
2. Customer-level LTV model: hierarchical Bayesian model that predicts individual discounted future value using covariates (demographics, past behavior), exposures, and channel interactions. Use flexible learners (GBM/NN) as components for baseline propensity to purchase and spend.
3. Time-varying channel effects: model channel ad-exposure effects with dynamic coefficients (state-space / local-level models or time-varying splines) so uplift of a channel can drift over calendar/time. Allow channel effects to interact with customer segments (hierarchical random slopes).
4. Attribution logic: estimate incremental uplift on expected LTV by counterfactual simulation — set exposures for a channel to zero (or baseline) and recompute predicted LTV, attributing difference to that channel. For multiple channels, use Shapley-style decomposition on counterfactuals to fairly apportion overlaps.

Required data:

• User-level: timestamps of exposures (impression/click), device, campaign, creative, channel, acquisition date, purchases, returns, cancellations, customer covariates, lifetime horizon observed.
• Channel-level: spend, bids, reach, frequency caps, inventory constraints, calendar features (seasonality, holidays).
• Experiment logs and instrumentation to link exposures to users.
• Aggregates for load-testing model (campaign/channel spend timeseries).

Validation:

• Holdout validation: temporal holdout users and backtesting of predicted LTV vs. observed realized LTV.
• Incrementality tests: compare model-implied incremental lift to results from available A/B tests; run targeted holdout experiments for uncertainty reduction.
• Posterior predictive checks, calibration plots, and uplift curve evaluation (Qini/AUUC).
• Sensitivity analyses: different discount rates, alternative model specifications, and shock scenarios (budget shifts).
• Business metrics alignment: compare predicted ROI ranking of channels to observed short-term KPIs.

Communicating uncertainty:

• Report full distributions (posterior) of attributed LTV and ROI, not point estimates; show median and 50/90% credible intervals.
• Use scenario dashboards: best/worst/most-likely budget allocations and expected ROI bands.
• Translate uncertainty into decisions: expected value of perfect information (EVPI) to prioritize experiments.
• Visuals: time-series of channel marginal ROI with shaded uncertainty, and Shapley attribution with error bars.
• State assumptions and limitations explicitly (unobserved confounding risk, horizon truncation).

Why this works:

• Hierarchical + time-varying structure captures user heterogeneity and evolving channel effectiveness; counterfactual simulation ensures causal incremental attribution; Bayesian framework naturally quantifies uncertainty for leadership trade-offs.

Approach: treat each user's first "view" on a given date as the cohort anchor, then check if that user has any "purchase" event within 7 days (inclusive) after that first view. Count unique users who viewed that day and how many converted.

WITH first_views AS (
  -- get each user's first view timestamp per day (based on event date)
  SELECT
    user_id,
    DATE(occurred_at) AS view_date,
    MIN(occurred_at) AS first_view_ts
  FROM events
  WHERE event_type = 'view'
  GROUP BY user_id, DATE(occurred_at)
),
purchases AS (
  -- keep purchase timestamps to join against
  SELECT user_id, occurred_at AS purchase_ts
  FROM events
  WHERE event_type = 'purchase'
)
SELECT
  fv.view_date,
  COUNT(DISTINCT fv.user_id) AS views,
  COUNT(DISTINCT CASE WHEN p.user_id IS NOT NULL THEN fv.user_id END) AS purchases_within_7d,
  ROUND(
    COUNT(DISTINCT CASE WHEN p.user_id IS NOT NULL THEN fv.user_id END)::numeric
    / NULLIF(COUNT(DISTINCT fv.user_id),0)
    , 4
  ) AS conversion_rate
FROM first_views fv
LEFT JOIN LATERAL (
  -- check if user has any purchase within 7 days (inclusive) after the first view
  SELECT 1
  FROM purchases p
  WHERE p.user_id = fv.user_id
    AND p.purchase_ts >= fv.first_view_ts
    AND p.purchase_ts < fv.first_view_ts + INTERVAL '7 days' + INTERVAL '1 second'
  LIMIT 1
) p ON true
GROUP BY fv.view_date
ORDER BY fv.view_date;

Key assumptions and notes:

• Timestamps are compared as-is; ensure events.occurred_at is in a consistent timezone (prefer storing UTC). If not, convert with AT TIME ZONE before DATE() and comparisons.
• We define cohort by the user's first view on that calendar date (DATE(occurred_at)); multiple views same day count the user once.
• A user can appear in multiple view_date cohorts if they viewed on different dates.
• The 7-day window starts at the first view timestamp and is inclusive of the start; adjust endpoint as business requires (e.g., <= first_view_ts + INTERVAL '7 days').
• Conversion uses unique users (de-duplicated) not events—typical for conversion-rate analysis.