wix_churn

Purpose: This view provides the basis to calculate wix_churn numbers Fields:

date: day of the year
active_subscriptions: number of new active subscriptions on date
cancelled: number of subscriptions cancelled on date
active_subscriptions_cumsum: number of active subscriptions as of date
active_subscriptions_30_days_ago: number of active subscriptions as of 30 days ago…used for churn calculation 
cancellations_last_30: number of cancelled subscriptions over the last 30 days…used for churn calculation

Notes:

Using wix stats data up until end of June 30, 2018
Data after June 30, 2018 uses data from wix_webhooks
In the future, may want to swap this view for a materialized view as the size of data grows
Currently only exists in sql land. If you want to call it in Rails

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

CREATE VIEW wix_churn AS
WITH daily_wix_numbers AS (
  SELECT 
    date, 
    SUM(upgraded - cancelled) AS active_subscriptions,
    SUM(cancelled) AS cancelled
  FROM 
    raw_wix_data 
  WHERE 
    date < '07-01-2018' 
  GROUP BY 
    date
  UNION 
  SELECT 
    COALESCE(wix_created_at, created_at)::DATE AS date,
    SUM(CASE WHEN event = 'PURCHASE_IMMEDIATE' THEN 1 WHEN event = 'CANCEL_IMMEDIATE' THEN -1 ELSE 0 END) AS active_subscriptions,
    SUM(CASE WHEN event = 'CANCEL_IMMEDIATE' THEN 1 ELSE 0 END) AS cancelled
  FROM 
    wix_webhooks
  WHERE
    COALESCE(wix_created_at, created_at)::DATE >= '07-01-2018'
  GROUP BY 
    COALESCE(wix_created_at, created_at)::DATE
)
SELECT 
  daily_wix_numbers.date,
  daily_wix_numbers.active_subscriptions,
  daily_wix_numbers.cancelled, 
  SUM(active_subscriptions) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS active_subscriptions_cumsum,
  SUM(active_subscriptions) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 30 PRECEDING) AS active_subscriptions_30_days_ago,
  SUM(cancelled) OVER(ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS cancellations_last_30
FROM 
  daily_wix_numbers
;