How to Join Product Usage and CRM Data to Understand Account Health

-- Account Health Scoring: Join Product Usage with CRM Data
WITH usage_metrics AS (
  SELECT 
    account_id,
    account_name,
    
    -- Usage Activity Metrics
    COUNT(DISTINCT user_id) as active_users_30d,
    COUNT(DISTINCT DATE(event_time)) as active_days_30d,
    COUNT(*) as total_events_30d,
    
    -- Feature Adoption Metrics
    COUNT(DISTINCT CASE WHEN event_type = 'feature_used' THEN event_properties:feature_name END) as features_used,
    COUNT(DISTINCT CASE WHEN event_type = 'advanced_feature_used' THEN user_id END) as power_users,
    
    -- Engagement Depth
    AVG(session_length_minutes) as avg_session_length,
    MAX(DATE(event_time)) as last_activity_date,
    MIN(DATE(event_time)) as first_activity_date,
    
    -- Value Realization Signals
    COUNT(DISTINCT CASE WHEN event_type = 'goal_completed' THEN event_id END) as goals_completed,
    COUNT(DISTINCT CASE WHEN event_type = 'integration_connected' THEN event_id END) as integrations_active
    
  FROM amplitude_events 
  WHERE event_time >= CURRENT_DATE - INTERVAL 30 DAY
    AND account_id IS NOT NULL
  GROUP BY account_id, account_name
),

crm_context AS (
  SELECT 
    id as account_id,
    name as account_name,
    annual_recurring_revenue__c as arr,
    contract_start_date__c as contract_start,
    contract_end_date__c as contract_end,
    customer_success_manager__c as csm_id,
    account_tier__c as tier,
    industry,
    employees as company_size,
    
    -- Contract Health
    DATEDIFF(contract_end_date__c, CURRENT_DATE) as days_to_renewal,
    DATEDIFF(CURRENT_DATE, contract_start_date__c) as days_since_start,
    
    -- Account Value Metrics
    CASE 
      WHEN annual_recurring_revenue__c >= 100000 THEN 'Enterprise'
      WHEN annual_recurring_revenue__c >= 25000 THEN 'Mid-Market' 
      ELSE 'SMB'
    END as revenue_segment
    
  FROM salesforce_accounts 
  WHERE type = 'Customer'
    AND is_deleted = FALSE
),

account_health AS (
  SELECT 
    u.account_id,
    u.account_name,
    c.arr,
    c.contract_end,
    c.days_to_renewal,
    c.revenue_segment,
    c.tier,
    c.industry,
    
    -- Usage Health Scores (0-100)
    LEAST(100, u.active_users_30d * 10) as user_adoption_score,
    LEAST(100, u.active_days_30d * 3.33) as frequency_score,
    LEAST(100, u.features_used * 5) as feature_breadth_score,
    LEAST(100, u.power_users * 20) as depth_score,
    LEAST(100, u.goals_completed * 2) as value_realization_score,
    
    -- Engagement Metrics
    u.active_users_30d,
    u.active_days_30d,
    u.features_used,
    u.power_users,
    u.avg_session_length,
    u.goals_completed,
    u.integrations_active,
    
    -- Risk Factors
    DATEDIFF(CURRENT_DATE, u.last_activity_date) as days_since_last_activity,
    CASE 
      WHEN DATEDIFF(CURRENT_DATE, u.last_activity_date) > 14 THEN 'High Risk'
      WHEN DATEDIFF(CURRENT_DATE, u.last_activity_date) > 7 THEN 'Medium Risk'
      ELSE 'Active'
    END as activity_risk_level,
    
    -- Expansion Signals
    CASE 
      WHEN u.power_users >= 5 AND u.features_used >= 15 THEN 'High Expansion Potential'
      WHEN u.power_users >= 2 AND u.features_used >= 8 THEN 'Medium Expansion Potential'
      ELSE 'Low Expansion Potential'
    END as expansion_potential
    
  FROM usage_metrics u
  LEFT JOIN crm_context c ON u.account_id = c.account_id
)

SELECT 
  account_id,
  account_name,
  arr,
  revenue_segment,
  tier,
  industry,
  
  -- Overall Health Score (weighted average)
  ROUND(
    (user_adoption_score * 0.25 + 
     frequency_score * 0.20 + 
     feature_breadth_score * 0.20 + 
     depth_score * 0.15 + 
     value_realization_score * 0.20), 1
  ) as overall_health_score,
  
  -- Individual Component Scores
  user_adoption_score,
  frequency_score, 
  feature_breadth_score,
  depth_score,
  value_realization_score,
  
  -- Engagement Details
  active_users_30d,
  active_days_30d,
  features_used,
  power_users,
  ROUND(avg_session_length, 1) as avg_session_minutes,
  goals_completed,
  integrations_active,
  
  -- Risk Assessment
  days_since_last_activity,
  activity_risk_level,
  days_to_renewal,
  
  -- Opportunity Classification
  expansion_potential,
  
  -- Health Categorization
  CASE 
    WHEN ROUND((user_adoption_score * 0.25 + frequency_score * 0.20 + feature_breadth_score * 0.20 + depth_score * 0.15 + value_realization_score * 0.20), 1) >= 80 THEN 'Excellent'
    WHEN ROUND((user_adoption_score * 0.25 + frequency_score * 0.20 + feature_breadth_score * 0.20 + depth_score * 0.15 + value_realization_score * 0.20), 1) >= 60 THEN 'Good'
    WHEN ROUND((user_adoption_score * 0.25 + frequency_score * 0.20 + feature_breadth_score * 0.20 + depth_score * 0.15 + value_realization_score * 0.20), 1) >= 40 THEN 'At Risk'
    ELSE 'Critical'
  END as health_category,
  
  contract_end

FROM account_health
ORDER BY overall_health_score DESC, arr DESC;