Attribution & Conversion Measurement Questions

Approach: join conversions to channel touches for the same user that occurred within the 14 days before the conversion, pick the most recent (last-touch) channel per conversion using ROW_NUMBER(), then aggregate counts per channel.

WITH touches_in_window AS (
  SELECT
    c.order_id,
    c.user_id,
    c.conversion_at,
    ch.channel,
    ch.occurred_at,
    ROW_NUMBER() OVER (
      PARTITION BY c.order_id
      ORDER BY ch.occurred_at DESC NULLS LAST
    ) AS rn
  FROM conversions c
  JOIN channels ch
    ON c.user_id = ch.user_id
   AND ch.occurred_at BETWEEN (c.conversion_at - INTERVAL '14' DAY) AND c.conversion_at
)
SELECT
  COALESCE(channel, 'no_touch_within_14d') AS channel,
  COUNT(DISTINCT order_id) AS conversion_count
FROM touches_in_window
WHERE rn = 1
GROUP BY COALESCE(channel, 'no_touch_within_14d')
ORDER BY conversion_count DESC;

Key points:

• We join on user_id and limit touches to the 14-day window before conversion.
• ROW_NUMBER per order_id picks the last-touch (most recent occurred_at). NULLS LAST ensures conversions with no touches in window are handled if you choose to outer-join instead.
• COUNT(DISTINCT order_id) counts conversions (unique orders) attributed to each last-touch channel.
  Edge cases:
• Multiple touches at the exact same timestamp: ROW_NUMBER arbitrarily picks one; use additional tie-breaker if needed.
• Users with no touches in window: adjust join to LEFT JOIN and handle NULL channel.
  Alternatives:
• Use MAX(occurred_at) + JOIN back to channels instead of ROW_NUMBER.
• Use sessionization or weighted multi-touch models for different attribution rules.

Approach: For each purchase event, find its session's UTM campaign. If that campaign is non-"direct" (and non-null), attribute to it. If it's "direct" (or null), look back across the same user's prior sessions within a configurable lookback window (e.g., 30 days) ordered newest→oldest and pick the first session whose campaign is not 'direct' and not null.

SQL (Postgres-style, using a 30-day lookback and LEFT JOIN LATERAL for clarity):

WITH purchases AS (
  SELECT
    e.event_id,
    e.user_id,
    e.event_time,
    s.session_id,
    s.session_start,
    s.campaign AS session_campaign
  FROM events e
  JOIN sessions s USING (session_id)
  WHERE e.event_name = 'purchase'
),
lookback AS (
  SELECT
    p.*,
    lb.campaign AS last_non_direct_campaign
  FROM purchases p
  LEFT JOIN LATERAL (
    SELECT campaign
    FROM sessions s2
    WHERE s2.user_id = p.user_id
      AND s2.session_start <= p.event_time           -- session occurred on or before purchase
      AND s2.session_start >= p.event_time - INTERVAL '30 days' -- lookback window
      AND s2.campaign IS NOT NULL
      AND LOWER(s2.campaign) <> 'direct'
    ORDER BY s2.session_start DESC
    LIMIT 1
  ) lb ON true
)
SELECT
  event_id,
  user_id,
  event_time,
  session_id,
  -- prefer current session campaign unless it's 'direct' or null, then use last_non_direct_campaign
  CASE
    WHEN session_campaign IS NOT NULL AND LOWER(session_campaign) <> 'direct' THEN session_campaign
    ELSE last_non_direct_campaign
  END AS attributed_campaign
FROM lookback;

Key points:

• Configurable lookback (here 30 days).
• Treats 'direct' case-insensitively and ignores NULLs.
• Uses LATERAL to efficiently find the most recent non-direct session per user.
  Edge cases:
• No prior non-direct campaign → attributed_campaign will be NULL.
• If business wants last non-direct even when current session is non-direct, adjust logic.
• For very large data, consider indexing sessions(user_id, session_start) and pre-aggregating last non-direct session per user-day for performance.

Audit objective: identify why the vendor attribution dataset diverges from our internal metrics, quantify differences, and produce reproducible evidence and recommendations.

1. Scope & prep

• Clarify definitions: user, conversion, attribution window, dedup rules, session vs event, touch/channel taxonomy, timezone.
• Snapshot time range and datasets (raw event logs, aggregated tables) on both sides.

2. Reconciliation checks (systematic, from coarse to fine)

• Totals: compare overall counts (users, sessions, conversions) by day, channel, campaign.
• Unique users: compute distinct user_id / device_id / hashed_puid, and compare overlap via ID joins. Report matched / unmatched proportions.
• Conversions by date/channel: pivoted tables for vendor vs internal; compute absolute and relative gaps.
• Time alignment: normalize timestamps (UTC), check day-boundary attribution differences (local midnight vs UTC), rolling attribution windows.
• Attribution model differences: single-touch vs multi-touch; simulate attribution on raw touchstream to align rules.
• Deduplication: re-run dedupe logic (sessionization, event_id uniqueness); identify duplicates causing inflation.
• Sampling: detect sampling by comparing raw event rate to aggregated counts; check for consistent scaling factor.
• Granularity mismatch: hourly vs daily aggregations.

3. Deep-dive tests

• Join-level diagnostics: left/right/full joins to surface which IDs/events are missing and why.
• Event-level reconciliation: compare event hashes/timestamps for matched IDs.
• Manual log-sampling: pull N=200-1000 raw log rows across dates/channels and trace through vendor pipeline; document transformations.
• Time-window sensitivity: rerun reconciliations with sliding windows (24h, 48h) to see lag effects.

4. Statistical tests & metrics

• Bias and significance: paired t-test or Wilcoxon signed-rank on daily counts to test systematic shift.
• Proportion tests: two-proportion z-test for conversion rate differences by channel.
• Correlation: Pearson/Spearman between vendor and internal daily series.
• Error metrics: MAPE, RMSE, %diff, KS-test on timestamp distributions to detect drift.

5. Visualizations to communicate findings

• Time series line charts (vendor vs internal) with shaded difference band and annotations for major gaps.
• Waterfall chart showing contribution of factors (time alignment, dedupe, sampling, attribution model).
• Scatter plot vendor vs internal with 45° reference and regression line; annotate outliers.
• Heatmap of relative differences by date × channel.
• Cumulative difference plot to show persistent bias.
• Sankey (or flow) showing matched→mismatched→missing counts.

6. Deliverables & next steps

• Reproducible notebook (SQL + Python/R) with join queries and tests, export sample logs used.
• Prioritized action items: immediate fixes (timezone/dedup), model alignment (attribution rules), monitoring thresholds and alerts.
• Proposed SLA: daily reconciliation report and weekly vendor sync to close gaps.

This plan emphasizes reproducible, data-driven evidence, quantification of impact, and recommended remediation steps for vendor collaboration.

Last-touch, linear, and time-decay are simple multi-touch attribution heuristics with different business assumptions and risks.

• Last-touch

  • Assumption: The final interaction before conversion delivered all incremental credit.
  • When useful: Simple conversion funnel where last interaction typically closes revenue (e.g., coupon click).
  • Misleads: Overcredits channels that merely capture intent (branded search, retargeting); undervalues upper-funnel channels and long nurturing.

• Linear

  • Assumption: Every touch in the path contributed equally to the conversion.
  • When useful: When you want an unbiased, easy-to-communicate split across channels.
  • Misleads: Masks timing/intensity differences — early-awareness vs. late-intent channels get same weight even though impact differs.

• Time-decay

  • Assumption: Recent touches matter more; contribution decays with time from conversion.
  • When useful: Purchase decisions influenced primarily by recent interactions.
  • Misleads: Choice of decay window is subjective; can undercredit long-term brand-building.

Pragmatic SQL to compute linear attribution per order (one row per touch event table: touches(order_id, touch_time, channel, revenue)) — split order revenue equally across touches:

WITH touch_counts AS (
  SELECT order_id, COUNT(*) AS touches
  FROM touches
  GROUP BY order_id
),
attribution AS (
  SELECT
    t.order_id,
    t.channel,
    t.touch_time,
    tc.touches,
    t.revenue / NULLIF(tc.touches,0) AS credit_per_touch
  FROM touches t
  JOIN touch_counts tc USING (order_id)
)
SELECT
  channel,
  SUM(credit_per_touch) AS attributed_revenue,
  COUNT(*) AS touch_events
FROM attribution
GROUP BY channel
ORDER BY attributed_revenue DESC;

Notes:

• Handle sessions and deduplication (merge close-in-time touches), bots, and unknown/NULL revenue.
• For time-decay, replace equal split with weights like EXP(-lambda * seconds_from_conversion) normalized per order.

Approach framework: clarify goals (conversion lift vs channel ROI vs audience performance), constraints (no 3P cookies, limited deterministic IDs), and success metrics (incremental conversions, CPA, LTV).

Solution components:

1. Server-side tracking (first-party): instrument events from server (purchase, sign-up) tied to first-party identifiers (hashed emails, user IDs) captured at login/consent. Feed into data warehouse (Snowflake/BigQuery) and BI layer to join marketing touch metadata (campaign IDs, creative) passed via URL params or postbacks. Benefits: higher data quality, persistence across devices; costs: engineering effort, privacy/compliance overhead, possible attribution gaps for anonymous users.

2. Aggregated modelling: where deterministic joins fail, build privacy-safe aggregate attribution models (e.g., incrementality-style MMM at weekly granularity using Bayesian hierarchical models) to estimate channel contribution. Use aggregated features (geo, cohort, time) to protect PII. Display model outputs in dashboards with uncertainty intervals.

3. Randomized holdouts & experiments: implement geo or user-level holdouts to measure causal lift. Use A/B tests or geo-based experimentation for major channels; feed results into BI as causal lift metrics and adjust budget allocation. Trade-off: experiments are gold-standard but reduce short-term reach and can be expensive.

Trade-offs and recommendations:

• Accuracy vs cost: server-side + deterministic IDs gives best accuracy but high engineering and consent dependency. Aggregated modelling is cheaper and scalable but introduces bias and wider confidence intervals. Holdouts provide causal clarity but limit exposure and require careful design.
• Practical stack: collect first-party IDs server-side, build pipelines to compute deterministic joins, fallback to aggregated modeling for anonymous traffic, and run periodic holdouts to validate models. Surface results in dashboards with clear labels: method, confidence, and recommended action.