Synced via Snip

gistfile1.txt

WITH leads_by_email AS (
  SELECT 
    l.email,
    COUNT(*) as lead_count,
    MIN(l.created_at) as first_lead_created_at,
    MAX(l.created_at) as last_lead_created_at,
    ARRAY_AGG(STRUCT(l.created_at, l.quiz_answers, lr.utm_source, lr.utm_medium, lr.utm_campaign) ORDER BY l.created_at ASC LIMIT 1)[OFFSET(0)] as first_lead,
    ARRAY_AGG(STRUCT(l.created_at, l.quiz_answers, lr.utm_source, lr.utm_medium, lr.utm_campaign) ORDER BY l.created_at DESC LIMIT 1)[OFFSET(0)] as last_lead
  FROM `airbyte_data.leads` l
  LEFT JOIN `airbyte_data.lead_referrers` lr ON lr.lead_id = l.id
  WHERE 
    LOWER(l.email) NOT LIKE '%@msrd.co'
    AND LOWER(l.email) NOT LIKE '%@trymeasured.com'
    AND LOWER(l.email) NOT LIKE '%test%'
    AND (l.first_name IS NULL OR LOWER(l.first_name) NOT LIKE '%test%')
    AND (l.last_name IS NULL OR LOWER(l.last_name) NOT LIKE '%test%')
  GROUP BY l.email
),
user_charges AS (
 SELECT 
    user_id,
    SUM(amount_captured) / 100.0 as total_revenue,
    MIN(amount_captured) / 100.0 as initial_revenue
  FROM `airbyte_data.charges`
  WHERE paid_at IS NOT NULL
   AND status = 'succeeded' 
   AND refunded is false
  GROUP BY user_id
),
user_purchase_data AS (
  SELECT
    u.id as user_id,
    u.email,
    u.created_at as user_created_at,
    
    -- Subscription information
    s.id as subscription_id,
    s.status as subscription_status,
    
    -- Product information - get first and current products
    FIRST_VALUE(p.name IGNORE NULLS) OVER (
      PARTITION BY u.id 
      ORDER BY s.created_at ASC 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as initial_product,
    
    LAST_VALUE(p.name IGNORE NULLS) OVER (
      PARTITION BY u.id 
      ORDER BY s.created_at ASC 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as current_product,
    
    -- Active status
    CASE WHEN s.status IN ('active', 'trialing') THEN true ELSE false END as is_active,
    
    -- Revenue calculations from pre-aggregated CTE
    COALESCE(uc.total_revenue, 0) as total_revenue,
    COALESCE(uc.initial_revenue, 0) as initial_revenue,
    
    -- Medical consultation completion
    MIN(mc.completed_at) OVER (PARTITION BY u.id) as ic_completed_at,
    
    -- Row number to deduplicate users
    ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY s.created_at DESC) as rn
    
  FROM `airbyte_data.users` u
  LEFT JOIN `airbyte_data.stripe_subscriptions` s ON u.id = s.user_id
  LEFT JOIN `airbyte_data.subscription_products` sp ON s.id = sp.subscription_id
  LEFT JOIN `airbyte_data.products` p ON sp.product_id = p.id
  LEFT JOIN user_charges uc ON u.id = uc.user_id
  LEFT JOIN `airbyte_data.medical_consultations` mc ON u.id = mc.user_id AND mc.completed_at IS NOT NULL
  WHERE 
    LOWER(u.email) NOT LIKE '%@msrd.co'
    AND LOWER(u.email) NOT LIKE '%@trymeasured.com'
    AND LOWER(u.email) NOT LIKE '%test%'
    AND (u.first_name IS NULL OR LOWER(u.first_name) NOT LIKE '%test%')
    AND (u.last_name IS NULL OR LOWER(u.last_name) NOT LIKE '%test%')
)
SELECT
  DATE(upd.user_created_at) as created_date,
  SUBSTR(TO_HEX(SHA256(upd.email)), 1, 12) as email_hash,
  `airbyte_data.obfuscateEmail`(upd.email) as email_obs,
  upd.user_id,
  upd.initial_product,
  upd.current_product,
  COALESCE(upd.is_active, false) as is_active,
  
  -- Funnel stage based on user progress
  CASE 
    WHEN upd.ic_completed_at IS NOT NULL THEN 'consultation_completed'
    WHEN upd.subscription_id IS NOT NULL THEN 'subscribed'
    WHEN lbe.email IS NOT NULL THEN 'lead'
    ELSE 'user'
  END as funnel_id,
  
  -- New pixel tracking (based on UTM source presence)
  CASE 
    WHEN lbe.first_lead.utm_source IS NOT NULL THEN true
    ELSE false
  END as new_pixel,
  
  upd.total_revenue,
  upd.initial_revenue,
  upd.ic_completed_at
FROM user_purchase_data upd
LEFT JOIN leads_by_email lbe ON LOWER(upd.email) = LOWER(lbe.email)
WHERE upd.rn = 1  -- Deduplicate users

ORDER BY created_date DESC, upd.user_created_at DESC