Calculating subscription churn rates of two segments using sqlite.
Exploring the subscriptions table reveals that there are columns for id, subscription_start, subscription_end, and segment.
Using MIN and MAX will provide the range of months I will calculate the churn for.
Based on this information, I'll calculate churn rates for the first three months of 2017 - December won't be included as there are no subscription end values yet.
First, I'll make a temporary table 'months' to use later so that I can see the subscriptions that are active for each month.
Then, I'll make a temporary table 'cross_join' from the subscriptions and months tables.
The next temporary table needed will be called 'status,' that will allow us to compare users' active status between the two segments for each of the three months. This can be seen in the following snippet. Cases are used to find users from each segment who existed prior to the beginning of the month, returning 1 if true. Cases are also used to tell us if the user canceled their subscription during the month, which will return a 1 in is_canceled columns.
Another temporary table, status_aggregate, will provide us with the sums of active and canceled subscriptions for each segment for each month.
Finally, we have our information in the right place to calculate the churn rates.
Based on the results, it is clear that segment 30’s churn rate is lower, therefore less users are unsubscribing. From this information, we can look at what segment 30 is doing successfully and what segment 87 could improve upon.