Feature Adoption Rate

-- Calculate feature adoption rates and usage patterns
WITH user_base AS (
  SELECT 
    user_id,
    created_date as signup_date,
    subscription_tier,
    user_segment,
    is_active,
    DATE_TRUNC('month', created_date) as signup_month
  FROM users
  WHERE created_date >= CURRENT_DATE - INTERVAL '12 months'
    AND is_active = true
),
feature_usage AS (
  SELECT 
    e.user_id,
    e.feature_name,
    e.event_date,
    e.session_id,
    e.feature_category,
    e.usage_duration_seconds,
    e.successful_completion,
    -- Categorize usage depth
    CASE 
      WHEN e.usage_duration_seconds >= 300 THEN 'Deep Usage'
      WHEN e.usage_duration_seconds >= 60 THEN 'Moderate Usage'
      WHEN e.usage_duration_seconds >= 10 THEN 'Light Usage'
      ELSE 'Brief Interaction'
    END as usage_depth,
    -- First time usage indicator
    ROW_NUMBER() OVER (PARTITION BY e.user_id, e.feature_name ORDER BY e.event_date) as usage_sequence,
    DATE_TRUNC('month', e.event_date) as usage_month,
    DATE_TRUNC('week', e.event_date) as usage_week
  FROM events e
  WHERE e.event_type = 'feature_usage'
    AND e.event_date >= CURRENT_DATE - INTERVAL '12 months'
),
feature_adoption_metrics AS (
  SELECT 
    fu.feature_name,
    fu.feature_category,
    fu.usage_month,
    -- User adoption metrics
    COUNT(DISTINCT fu.user_id) as adopting_users,
    COUNT(DISTINCT CASE WHEN fu.usage_sequence = 1 THEN fu.user_id END) as new_adopters,
    COUNT(DISTINCT CASE WHEN fu.usage_sequence > 1 THEN fu.user_id END) as returning_users,
    -- Usage depth distribution
    COUNT(DISTINCT CASE WHEN fu.usage_depth = 'Deep Usage' THEN fu.user_id END) as deep_users,
    COUNT(DISTINCT CASE WHEN fu.usage_depth = 'Moderate Usage' THEN fu.user_id END) as moderate_users,
    COUNT(DISTINCT CASE WHEN fu.usage_depth = 'Light Usage' THEN fu.user_id END) as light_users,
    -- Success metrics
    COUNT(DISTINCT CASE WHEN fu.successful_completion = true THEN fu.user_id END) as successful_users,
    -- Usage frequency
    COUNT(*) as total_usage_events,
    ROUND(AVG(fu.usage_duration_seconds), 1) as avg_usage_duration,
    -- User base for adoption rate calculation
    COUNT(DISTINCT ub.user_id) as total_active_users
  FROM feature_usage fu
  LEFT JOIN user_base ub ON fu.usage_month = ub.signup_month 
                        OR (fu.usage_month > ub.signup_month AND ub.signup_month >= fu.usage_month - INTERVAL '12 months')
  GROUP BY fu.feature_name, fu.feature_category, fu.usage_month
),
adoption_rates AS (
  SELECT 
    feature_name,
    feature_category,
    usage_month,
    adopting_users,
    new_adopters,
    returning_users,
    total_active_users,
    -- Adoption rate calculations
    ROUND(100.0 * adopting_users / NULLIF(total_active_users, 0), 1) as adoption_rate,
    ROUND(100.0 * new_adopters / NULLIF(total_active_users, 0), 1) as new_adoption_rate,
    ROUND(100.0 * returning_users / NULLIF(adopting_users, 0), 1) as retention_rate,
    ROUND(100.0 * successful_users / NULLIF(adopting_users, 0), 1) as success_rate,
    -- Usage depth metrics
    ROUND(100.0 * deep_users / NULLIF(adopting_users, 0), 1) as deep_usage_rate,
    ROUND(100.0 * moderate_users / NULLIF(adopting_users, 0), 1) as moderate_usage_rate,
    -- Engagement intensity
    ROUND(total_usage_events / NULLIF(adopting_users, 0), 1) as avg_events_per_user,
    avg_usage_duration,
    -- Trend analysis
    LAG(adoption_rate) OVER (PARTITION BY feature_name ORDER BY usage_month) as prev_month_adoption,
    LAG(adopting_users) OVER (PARTITION BY feature_name ORDER BY usage_month) as prev_month_users
  FROM feature_adoption_metrics
)
SELECT 
  feature_name,
  feature_category,
  usage_month,
  adopting_users,
  total_active_users,
  adoption_rate,
  new_adoption_rate,
  retention_rate,
  success_rate,
  deep_usage_rate,
  avg_events_per_user,
  avg_usage_duration,
  ROUND(adoption_rate - COALESCE(prev_month_adoption, 0), 1) as adoption_rate_change,
  ROUND(100.0 * (adopting_users - COALESCE(prev_month_users, 0)) / NULLIF(COALESCE(prev_month_users, adopting_users), 0), 1) as user_growth_rate,
  RANK() OVER (PARTITION BY usage_month ORDER BY adoption_rate DESC) as adoption_rank
FROM adoption_rates
WHERE total_active_users >= 100  -- Minimum user base for reliable metrics
ORDER BY usage_month DESC, adoption_rate DESC;

-- Analyze feature adoption funnel from awareness to habitual use
WITH feature_awareness AS (
  SELECT 
    e.user_id,
    e.feature_name,
    MIN(e.event_date) as first_awareness_date,
    COUNT(*) as awareness_touchpoints
  FROM events e
  WHERE e.event_type IN ('feature_tooltip_shown', 'feature_highlighted', 'feature_tutorial_started')
    AND e.event_date >= CURRENT_DATE - INTERVAL '6 months'
  GROUP BY e.user_id, e.feature_name
),
feature_trial AS (
  SELECT 
    fu.user_id,
    fu.feature_name,
    MIN(fu.event_date) as first_trial_date,
    COUNT(*) as trial_attempts,
    MAX(fu.successful_completion) as had_successful_trial
  FROM feature_usage fu
  WHERE fu.usage_sequence = 1  -- First time usage
    AND fu.event_date >= CURRENT_DATE - INTERVAL '6 months'
  GROUP BY fu.user_id, fu.feature_name
),
feature_activation AS (
  SELECT 
    fu.user_id,
    fu.feature_name,
    MIN(CASE WHEN fu.successful_completion = true THEN fu.event_date END) as first_success_date,
    COUNT(CASE WHEN fu.successful_completion = true THEN 1 END) as successful_uses,
    COUNT(*) as total_attempts
  FROM feature_usage fu
  WHERE fu.event_date >= CURRENT_DATE - INTERVAL '6 months'
  GROUP BY fu.user_id, fu.feature_name
  HAVING COUNT(CASE WHEN fu.successful_completion = true THEN 1 END) > 0
),
feature_habit AS (
  SELECT 
    fu.user_id,
    fu.feature_name,
    MIN(fu.event_date) as habit_start_date,
    COUNT(DISTINCT fu.usage_week) as weeks_used,
    COUNT(*) as total_usage_events,
    ROUND(AVG(fu.usage_duration_seconds), 1) as avg_usage_duration
  FROM feature_usage fu
  WHERE fu.event_date >= CURRENT_DATE - INTERVAL '6 months'
  GROUP BY fu.user_id, fu.feature_name
  -- Define habitual use as 3+ weeks of usage with 5+ total events
  HAVING COUNT(DISTINCT fu.usage_week) >= 3 AND COUNT(*) >= 5
),
funnel_analysis AS (
  SELECT 
    COALESCE(fa.feature_name, ft.feature_name, fac.feature_name, fh.feature_name) as feature_name,
    -- Funnel stages
    COUNT(DISTINCT fa.user_id) as awareness_users,
    COUNT(DISTINCT ft.user_id) as trial_users,
    COUNT(DISTINCT fac.user_id) as activation_users,
    COUNT(DISTINCT fh.user_id) as habit_users,
    -- Conversion rates
    ROUND(100.0 * COUNT(DISTINCT ft.user_id) / NULLIF(COUNT(DISTINCT fa.user_id), 0), 1) as awareness_to_trial_rate,
    ROUND(100.0 * COUNT(DISTINCT fac.user_id) / NULLIF(COUNT(DISTINCT ft.user_id), 0), 1) as trial_to_activation_rate,
    ROUND(100.0 * COUNT(DISTINCT fh.user_id) / NULLIF(COUNT(DISTINCT fac.user_id), 0), 1) as activation_to_habit_rate,
    -- Overall funnel efficiency
    ROUND(100.0 * COUNT(DISTINCT fh.user_id) / NULLIF(COUNT(DISTINCT fa.user_id), 0), 1) as awareness_to_habit_rate,
    -- Time to conversion metrics
    ROUND(AVG(EXTRACT(days FROM AGE(ft.first_trial_date, fa.first_awareness_date))), 1) as avg_days_awareness_to_trial,
    ROUND(AVG(EXTRACT(days FROM AGE(fac.first_success_date, ft.first_trial_date))), 1) as avg_days_trial_to_activation,
    ROUND(AVG(EXTRACT(days FROM AGE(fh.habit_start_date, fac.first_success_date))), 1) as avg_days_activation_to_habit
  FROM feature_awareness fa
  FULL OUTER JOIN feature_trial ft ON fa.user_id = ft.user_id AND fa.feature_name = ft.feature_name
  FULL OUTER JOIN feature_activation fac ON ft.user_id = fac.user_id AND ft.feature_name = fac.feature_name
  FULL OUTER JOIN feature_habit fh ON fac.user_id = fh.user_id AND fac.feature_name = fh.feature_name
  GROUP BY COALESCE(fa.feature_name, ft.feature_name, fac.feature_name, fh.feature_name)
),
user_segment_adoption AS (
  SELECT 
    fu.feature_name,
    ub.user_segment,
    ub.subscription_tier,
    COUNT(DISTINCT fu.user_id) as adopting_users,
    COUNT(DISTINCT ub.user_id) as segment_users,
    ROUND(100.0 * COUNT(DISTINCT fu.user_id) / NULLIF(COUNT(DISTINCT ub.user_id), 0), 1) as segment_adoption_rate,
    ROUND(AVG(fu.usage_duration_seconds), 1) as avg_usage_duration,
    COUNT(*) as total_usage_events
  FROM feature_usage fu
  JOIN user_base ub ON fu.user_id = ub.user_id
  WHERE fu.event_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY fu.feature_name, ub.user_segment, ub.subscription_tier
  HAVING COUNT(DISTINCT ub.user_id) >= 20  -- Minimum segment size
)
SELECT 
  'Funnel Analysis' as analysis_type,
  feature_name,
  awareness_users as stage_1_users,
  trial_users as stage_2_users,
  activation_users as stage_3_users,
  habit_users as stage_4_users,
  awareness_to_trial_rate as conversion_rate_1_2,
  trial_to_activation_rate as conversion_rate_2_3,
  activation_to_habit_rate as conversion_rate_3_4,
  awareness_to_habit_rate as overall_conversion_rate
FROM funnel_analysis
WHERE awareness_users >= 50  -- Minimum sample size
UNION ALL
SELECT 
  'Segment Analysis' as analysis_type,
  CONCAT(feature_name, ' - ', user_segment, ' (', subscription_tier, ')') as feature_name,
  segment_users as stage_1_users,
  adopting_users as stage_2_users,
  NULL as stage_3_users,
  NULL as stage_4_users,
  segment_adoption_rate as conversion_rate_1_2,
  NULL as conversion_rate_2_3,
  NULL as conversion_rate_3_4,
  segment_adoption_rate as overall_conversion_rate
FROM user_segment_adoption
ORDER BY analysis_type, overall_conversion_rate DESC;

-- Analyze correlation between feature adoption and business outcomes
WITH user_feature_adoption AS (
  SELECT 
    fu.user_id,
    COUNT(DISTINCT fu.feature_name) as features_adopted,
    COUNT(DISTINCT CASE WHEN fu.feature_category = 'core' THEN fu.feature_name END) as core_features_adopted,
    COUNT(DISTINCT CASE WHEN fu.feature_category = 'advanced' THEN fu.feature_name END) as advanced_features_adopted,
    COUNT(*) as total_feature_usage_events,
    ROUND(AVG(fu.usage_duration_seconds), 1) as avg_usage_duration,
    MAX(fu.event_date) as last_feature_usage,
    MIN(fu.event_date) as first_feature_usage,
    -- Feature engagement score
    ROUND(LOG(COUNT(*) + 1) * COUNT(DISTINCT fu.feature_name) * 
          AVG(fu.usage_duration_seconds) / 60, 1) as feature_engagement_score
  FROM feature_usage fu
  WHERE fu.event_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY fu.user_id
),
user_outcomes AS (
  SELECT 
    ub.user_id,
    ub.subscription_tier,
    ub.user_segment,
    -- Retention metrics
    CASE WHEN ub.is_active = true THEN 1 ELSE 0 END as is_retained,
    EXTRACT(days FROM AGE(CURRENT_DATE, ub.created_date)) as days_since_signup,
    -- Usage metrics
    COALESCE(ua.sessions_last_30_days, 0) as sessions_last_30_days,
    COALESCE(ua.minutes_last_30_days, 0) as minutes_last_30_days,
    -- Revenue metrics
    COALESCE(s.mrr, 0) as monthly_revenue,
    CASE WHEN s.subscription_status = 'active' THEN 1 ELSE 0 END as is_paying,
    -- Satisfaction metrics
    COALESCE(cs.nps_score, 0) as nps_score,
    COALESCE(cs.satisfaction_score, 0) as satisfaction_score
  FROM user_base ub
  LEFT JOIN user_analytics ua ON ub.user_id = ua.user_id 
                              AND ua.period_start >= CURRENT_DATE - INTERVAL '30 days'
  LEFT JOIN subscriptions s ON ub.user_id = s.user_id AND s.subscription_status = 'active'
  LEFT JOIN customer_satisfaction cs ON ub.user_id = cs.user_id 
                                     AND cs.survey_date >= CURRENT_DATE - INTERVAL '90 days'
),
adoption_value_correlation AS (
  SELECT 
    ufa.user_id,
    ufa.features_adopted,
    ufa.core_features_adopted,
    ufa.advanced_features_adopted,
    ufa.feature_engagement_score,
    uo.is_retained,
    uo.sessions_last_30_days,
    uo.minutes_last_30_days,
    uo.monthly_revenue,
    uo.is_paying,
    uo.nps_score,
    uo.satisfaction_score,
    uo.subscription_tier,
    uo.user_segment,
    -- Feature adoption tiers
    CASE 
      WHEN ufa.features_adopted >= 10 THEN 'Power User (10+ features)'
      WHEN ufa.features_adopted >= 5 THEN 'Active User (5-9 features)'
      WHEN ufa.features_adopted >= 2 THEN 'Moderate User (2-4 features)'
      WHEN ufa.features_adopted = 1 THEN 'Single Feature User'
      ELSE 'No Feature Usage'
    END as adoption_tier
  FROM user_feature_adoption ufa
  FULL OUTER JOIN user_outcomes uo ON ufa.user_id = uo.user_id
),
correlation_analysis AS (
  SELECT 
    adoption_tier,
    COUNT(*) as user_count,
    -- Retention correlation
    ROUND(100.0 * AVG(is_retained), 1) as retention_rate,
    -- Engagement correlation
    ROUND(AVG(sessions_last_30_days), 1) as avg_sessions,
    ROUND(AVG(minutes_last_30_days), 1) as avg_minutes,
    -- Revenue correlation
    ROUND(AVG(monthly_revenue), 2) as avg_monthly_revenue,
    ROUND(100.0 * AVG(is_paying), 1) as conversion_to_paid_rate,
    -- Satisfaction correlation
    ROUND(AVG(CASE WHEN nps_score > 0 THEN nps_score END), 1) as avg_nps_score,
    ROUND(AVG(CASE WHEN satisfaction_score > 0 THEN satisfaction_score END), 1) as avg_satisfaction_score,
    -- Feature usage patterns
    ROUND(AVG(features_adopted), 1) as avg_features_adopted,
    ROUND(AVG(feature_engagement_score), 1) as avg_engagement_score
  FROM adoption_value_correlation
  GROUP BY adoption_tier
),
feature_impact_analysis AS (
  SELECT 
    fu.feature_name,
    fu.feature_category,
    COUNT(DISTINCT fu.user_id) as feature_users,
    -- Users who adopted this feature vs those who didn't
    ROUND(AVG(CASE WHEN avc.user_id IS NOT NULL THEN avc.monthly_revenue ELSE 0 END), 2) as avg_revenue_adopters,
    ROUND(AVG(CASE WHEN avc.user_id IS NULL THEN uo.monthly_revenue ELSE 0 END), 2) as avg_revenue_non_adopters,
    ROUND(100.0 * AVG(CASE WHEN avc.user_id IS NOT NULL THEN avc.is_retained ELSE 0 END), 1) as retention_rate_adopters,
    ROUND(100.0 * AVG(CASE WHEN avc.user_id IS NULL THEN uo.is_retained ELSE 0 END), 1) as retention_rate_non_adopters,
    -- Feature value indicators
    ROUND(AVG(CASE WHEN avc.user_id IS NOT NULL THEN avc.monthly_revenue ELSE 0 END) - 
          AVG(CASE WHEN avc.user_id IS NULL THEN uo.monthly_revenue ELSE 0 END), 2) as revenue_lift,
    ROUND(100.0 * AVG(CASE WHEN avc.user_id IS NOT NULL THEN avc.is_retained ELSE 0 END) - 
          100.0 * AVG(CASE WHEN avc.user_id IS NULL THEN uo.is_retained ELSE 0 END), 1) as retention_lift
  FROM feature_usage fu
  LEFT JOIN adoption_value_correlation avc ON fu.user_id = avc.user_id
  CROSS JOIN user_outcomes uo  -- For comparison baseline
  WHERE fu.event_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY fu.feature_name, fu.feature_category
  HAVING COUNT(DISTINCT fu.user_id) >= 50  -- Minimum sample size
)
SELECT 
  feature_name,
  feature_category,
  feature_users,
  avg_revenue_adopters,
  avg_revenue_non_adopters,
  revenue_lift,
  retention_rate_adopters,
  retention_rate_non_adopters,
  retention_lift,
  RANK() OVER (ORDER BY revenue_lift DESC) as revenue_impact_rank,
  RANK() OVER (ORDER BY retention_lift DESC) as retention_impact_rank
FROM feature_impact_analysis
ORDER BY (revenue_lift + retention_lift) DESC;