-- First establish some base parameters for our users WITH config AS ( SELECT -- For now we're using this as our free-trial filter. We'll treat users -- that churn during this period differently since they likely haven't actually -- tried the software very much. We'll also use this to isolate feature usage -- from this period, as it's likely that user goals are different at this stage. 14 AS short_term, -- This is our ~day74 cutoff. For users, we want to see patterns of usage here -- that might differ from those who either churn later or hang around for a while. -- For feature aggregates, this should represent the "serious" efforts to use ZenMaid. 90 AS medium_term, -- This is a (in theory) rarer group of churned users that lasted beyond our day 74 -- danger zone but still churned. -- Beyond this we'll consider anyone remaining a "success" (even if they churned at some -- point) since things like company closures, etc. start to come into play more -- frequently. -- For features, we'll look at this as features used in the company's day-to-day, and not -- evaluation attempts. 360 AS long_term, -- We'll only look back a few years since we want to make fairish comparisons betwen users -- exposed to "modern" ZenMaid tools. CAST('2023-01-01' AS timestamp) AS date_cutoff ), -- Then establish a standard core set of user data users_with_churn_stats AS ( SELECT id, active, billing_state, DATE_TRUNC('month', created_at) AS month_created, created_at, free_trial_ends_at, updated_at, updated_at::date - created_at::date AS lifespan FROM users, config -- We'll only look at newish users. WHERE created_at >= config.date_cutoff ), -- Next use that core data to extract the records we want to analyze -- and categorize them by date and status users_to_examine AS ( -- First the users who exited just after the trial SELECT 'quick-exit' AS category, uwcs.* FROM users_with_churn_stats uwcs, config WHERE billing_state = 'CHURNED' AND lifespan <= config.short_term UNION -- Next, users who gave the tools some time but still left SELECT 'fair-trial' AS category, uwcs.* FROM users_with_churn_stats uwcs, config WHERE billing_state = 'CHURNED' AND lifespan < config.medium_term AND lifespan > config.short_term UNION -- Then some longer-time users who still churned. -- We'll cut this off after 6 months since afterwards -- company closures, etc. could factor in. SELECT 'short-termer' AS category, uwcs.* FROM users_with_churn_stats uwcs, config WHERE billing_state = 'CHURNED' AND lifespan >= config.medium_term AND lifespan < config.long_term UNION -- Finally, people who are still kicking. -- This cohort is going to be a little wrong for older -- dates since it only includes those who are STILL around. SELECT 'long-termer' AS category, uwcs.* FROM users_with_churn_stats uwcs, config -- We could also filter this by the following if we wanted to look at current users: -- billing_state NOT IN ('CHURNED', 'ONBOARDING', 'UNKNOWN') -- ...but for a multi-year peek into history, we'll lose too many good users who left -- for other reasons like closures, etc. WHERE lifespan >= config.long_term ), -- Checklists created by era checklist_counts AS ( -- Establish a summary of records with ages WITH summary AS ( SELECT checklist_templates.user_id, checklists.created_at::date - users.created_at::date AS created_age FROM config, checklist_templates JOIN users ON checklist_templates.user_id = users.id JOIN checklists ON checklists.checklist_template_id = checklist_templates.id WHERE checklists.created_at >= config.date_cutoff AND users.created_at >= config.date_cutoff AND checklists.created_at <= users.created_at::date + config.long_term ), -- Group by week for line charts weekly_counts AS ( SELECT user_id, jsonb_object_agg( 'week_' || weeks_in, weekly_count ) AS weekly_counts, COUNT(*) AS total FROM (SELECT user_id, FLOOR(created_age / 7) AS weeks_in, COUNT(*) as weekly_count FROM summary GROUP BY user_id, weeks_in) by_weeks GROUP BY user_id ), -- Group by era for bar charts bucket_counts AS ( SELECT user_id, SUM(CASE WHEN created_age < config.short_term THEN 1 ELSE 0 END) AS short_term, SUM(CASE WHEN created_age >= config.short_term AND created_age < config.medium_term THEN 1 ELSE 0 END) AS medium_term, SUM(CASE WHEN created_age >= config.medium_term AND created_age < config.long_term THEN 1 ELSE 0 END) AS long_term, COUNT(*) AS total FROM config, summary GROUP BY user_id ) -- Put it all together SELECT bucket_counts.*, weekly_counts.weekly_counts FROM bucket_counts JOIN weekly_counts ON weekly_counts.user_id = bucket_counts.user_id ), -- Checklist events created by era checklist_filled_counts AS ( -- Establish a summary of records with ages WITH summary AS ( SELECT employees.user_id, checklist_item_events.created_at::date - users.created_at::date AS created_age FROM config, employees JOIN users ON employees.user_id = users.id JOIN checklist_item_events ON checklist_item_events.employee_id = employees.id WHERE checklist_item_events.created_at >= config.date_cutoff AND users.created_at >= config.date_cutoff AND checklist_item_events.created_at <= users.created_at::date + config.long_term ), -- Group by week for line charts weekly_counts AS ( SELECT user_id, jsonb_object_agg( 'week_' || weeks_in, weekly_count ) AS weekly_counts, COUNT(*) AS total FROM (SELECT user_id, FLOOR(created_age / 7) AS weeks_in, COUNT(*) as weekly_count FROM summary GROUP BY user_id, weeks_in) by_weeks GROUP BY user_id ), -- Group by era for bar charts bucket_counts AS ( SELECT user_id, SUM(CASE WHEN created_age < config.short_term THEN 1 ELSE 0 END) AS short_term, SUM(CASE WHEN created_age >= config.short_term AND created_age < config.medium_term THEN 1 ELSE 0 END) AS medium_term, SUM(CASE WHEN created_age >= config.medium_term AND created_age < config.long_term THEN 1 ELSE 0 END) AS long_term, COUNT(*) AS total FROM config, summary GROUP BY user_id ) -- Put it all together SELECT bucket_counts.*, weekly_counts.weekly_counts FROM bucket_counts JOIN weekly_counts ON weekly_counts.user_id = bucket_counts.user_id ) -- Finally, we'll flatten it all out into a repurposable flat table SELECT users_to_examine.id, users_to_examine.active, users_to_examine.billing_state, users_to_examine.free_trial_ends_at, users_to_examine.category, users_to_examine.created_at, users_to_examine.month_created, users_to_examine.updated_at, users_to_examine.lifespan, COALESCE(checklist_counts.short_term, 0) AS checklists_short_term, COALESCE(checklist_counts.medium_term, 0) AS checklists_medium_term, COALESCE(checklist_counts.long_term, 0) AS checklists_long_term, COALESCE(checklist_counts.total, 0) AS checklists, COALESCE(checklist_counts.weekly_counts, '{}'::jsonb) AS checklists_weekly_counts, COALESCE(checklist_filled_counts.short_term, 0) AS checklists_filled_short_term, COALESCE(checklist_filled_counts.medium_term, 0) AS checklists_filled_medium_term, COALESCE(checklist_filled_counts.long_term, 0) AS checklists_filled_long_term, COALESCE(checklist_filled_counts.total, 0) AS checklists_filled, COALESCE(checklist_filled_counts.weekly_counts, '{}'::jsonb) AS checklists_filled_weekly_counts FROM users_to_examine LEFT JOIN checklist_counts ON checklist_counts.user_id = users_to_examine.id LEFT JOIN checklist_filled_counts ON checklist_filled_counts.user_id = users_to_examine.id ;