historical_churn

historical_churn

Purpose: This view provides the basis to calculate historical churn numbers. Fields:

 date: day of the year
 num_active: number of active paying users as of that da
 num_cancelled: number of users who are no longer active as of that day 
 active_paying_users_30_days_ago: num_active 30 days ago…used for churn calculation
 cancellations_last_30: SUM(num_cancelled) over the last 30 days…used for churn calculation

Notes:

• 12-01-2017 is used as the beginning of the date series so that churn can be easily calculated for the beginning of the year.
• Currently only being used on the company_goals page and should be updated yearly for performance reasons
• Currently only exists in sql land. If you want to call it in Rails

query = "SELECT * FROM historical_churn"
records = ActiveRecord::Base.connection.execute(query)

---

Query To Generate This View

CREATE VIEW historical_churn AS 
WITH active_users AS (
  SELECT
date.date,
COUNT(DISTINCT user_id) AS num_active
  FROM (
SELECT 
  cc.user_id,
  ass.created_at,
  ass.downgraded_at
FROM 
  app_subscriptions ass
  INNER JOIN credit_cards cc ON cc.id = ass.credit_card_id
UNION
SELECT
  ps.user_id,
  ps.created_at,
  ps.downgraded_at
FROM pro_subscriptions ps
) subs
INNER JOIN GENERATE_SERIES('12-01-2017'::DATE, NOW()::DATE, '1 day') date ON subs.created_at < date.date AND (subs.downgraded_at IS NULL OR subs.downgraded_at > date.date)
  GROUP BY date.date
), cancelled_users AS (
  WITH subs_w_flag AS (
SELECT 
  created_at,
  user_id,
  downgraded_at,
  SUM(CASE WHEN downgraded_at IS NULL THEN 1 ELSE 0 END) OVER(PARTITION BY user_id) as flag
FROM
(  SELECT
    ass.created_at,
    cc.user_id,
    ass.downgraded_at
  FROM
    app_subscriptions ass
    INNER JOIN credit_cards cc ON cc.id = ass.credit_card_id
  UNION 
  SELECT
    ps.created_at,
    ps.user_id,
    ps.downgraded_at
  FROM
    pro_subscriptions ps
) subs
  )
  SELECT
date.date,
count(distinct user_id) AS num_cancelled
  FROM 
subs_w_flag sf
INNER JOIN GENERATE_SERIES('12-01-2017'::DATE, NOW()::DATE, '1 day') date ON sf.flag = 0 AND sf.downgraded_at::DATE = date.date
  AND NOT EXISTS ( SELECT 1 FROM subs_w_flag sf2 WHERE sf2.user_id = sf.user_id AND sf2.downgraded_at::DATE > date.date)
  GROUP BY date.date
)
SELECT
  au.date::DATE,
  au.num_active,
  cu.num_cancelled,
  SUM(au.num_active) OVER(ORDER BY au.date ROWS BETWEEN 30 PRECEDING AND 30 PRECEDING) AS active_paying_users_30_days_ago,
  SUM(cu.num_cancelled) OVER(ORDER BY au.date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS cancellations_last_30
FROM
  active_users au
  JOIN cancelled_users cu ON cu.date = au.date;