Signup to Purchase Conversion by Traffic Source

Decomposition

• Core metric: Signup to first purchase conversion rate (percentage)
• Dimensions: Traffic source
• Time window: Last month (signups from last month, tracking if they made a first purchase)
• Edge cases: Users with multiple signups, refunded/cancelled orders, signups without traffic source data

Data Needs

Assumed Schema

users: user_id, signup_date, traffic_source
orders: order_id, user_id, order_date, status

Table Mapping

• users - Primary table for signups and traffic source attribution
• orders - Event table for purchase activities

Join Strategy

• LEFT JOIN from users to orders to preserve all signups (including those who didn’t purchase)
• Filter orders to first purchase only using ROW_NUMBER window function
• INNER JOIN assumptions: None needed, we want to see all signups regardless of purchase

SQL Query

WITH last_month_signups AS (
  SELECT
    user_id,
    signup_date,
    traffic_source
  FROM users
  WHERE signup_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
    AND signup_date < DATE_TRUNC('month', CURRENT_DATE)
),

first_purchases AS (
  SELECT
    o.user_id,
    o.order_date AS first_purchase_date,
    ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.order_date) AS purchase_rank
  FROM orders o
  WHERE o.status NOT IN ('cancelled', 'refunded')
),

signup_conversion AS (
  SELECT
    s.user_id,
    s.traffic_source,
    s.signup_date,
    fp.first_purchase_date,
    CASE WHEN fp.user_id IS NOT NULL THEN 1 ELSE 0 END AS converted
  FROM last_month_signups s
  LEFT JOIN first_purchases fp 
    ON s.user_id = fp.user_id 
    AND fp.purchase_rank = 1
)

SELECT
  COALESCE(traffic_source, 'Unknown') AS traffic_source,
  COUNT(DISTINCT user_id) AS total_signups,
  SUM(converted) AS conversions,
  ROUND(100.0 * SUM(converted) / COUNT(DISTINCT user_id), 2) AS conversion_rate_pct
FROM signup_conversion
GROUP BY traffic_source
ORDER BY conversion_rate_pct DESC;

Interview Talking Points

High-level approach (30 seconds):

“I’ll solve this in three CTEs. First, identify all signups from last month with their traffic source. Second, find each user’s first valid purchase (excluding cancelled/refunded orders). Third, join signups to purchases and calculate conversion rate by traffic source.”

Key design decisions:

• LEFT JOIN preserves all signups, even those who haven’t purchased (denominator must include non-converters)
• ROW_NUMBER to identify first purchase per user (handles users with multiple orders)
• Filter by signup_date for the cohort window, not purchase_date
• Exclude cancelled/refunded orders from conversion definition
• COALESCE handles NULL traffic_source values

Verification strategy:

“I’d validate by: (1) checking total_signups matches SELECT COUNT(*) FROM users WHERE signup_date BETWEEN ..., (2) spot-checking a few users to verify their first purchase is correctly identified, (3) ensuring conversion_rate never exceeds 100%”

Common variations:

• Time-bound conversion: Add WHERE first_purchase_date <= signup_date + INTERVAL '30 days' to measure 30-day conversion
• Multiple time windows: Calculate D1, D7, D30 conversion rates in separate columns
• Revenue metrics: Join to order amounts to calculate average revenue per signup by source