Target Database Table
drp.subscription_churn_monthly.
Script Description and Logic
Pulls the month and subscription-related counts at the monthly level. Subscription counts from all Recharge integrations are included in the primary source table, drp.subscription_profiles, and reported by month and year. From the drp.subscription_profiles table, a rollup of counts is performed to the monthly level and stored as prepare.
A calendar derived table is generated from a rollup of drp.calendar to the monthly level. Both derived tables are joined as calendar_join. A lookup is performed on the prepare table to generate a list of products and a list of shipping frequency at the monthly level, these two tables are then joined with a list of months in the calendar_month as calendar_master.
A lookup is performed on the prepare table to obtain month and the date the subscription product started as start_sub. active_start_month is generated by a lookup against calendar_join, and active_month_end is queried to generate active_end_month.
The calendar_join is queried to produce a list of sub active at the end of the month as subs_churned. calendar_master, active_start_month, active_month_end, subs_churned and start_sub are then joined together to generate subscription churn table by month and year, subscription_churn_monthly.
All records are removed from the final table, subscription_churn_monthly and updated by way of insertion from the query (upsert).
Source Tables Used In Script
Schema | Table (or Derived Table) Name | Table Type | Purpose |
drp | subscription_profiles | database | Primary |
drp | calendar | database | Lookup |
N/A | prepare | derived | Rollup of drp.subscription_profiles to the monthly level |
N/A | calendar_month | derived | Rollup of drp.calendar to the month level |
N/A | calendar_join | derived | Joins prepare derived table and calendar_month |
N/A | dates | derived | Obtains a list of months from calendar_month derived table |
N/A | shipping_frequency | derived | Obtains a list of unique shipping frequency days from prepare derived table |
N/A | products | derived | Obtains a list of unique product from prepare derived table |
N/A | calendar_master | derived | Joins dates, shipping_frequency and products |
N/A | start_sub | derived | Obtains month a subscription was started from prepare derived table |
N/A | active_start_month | derived | Obtains a list of subscriptions active this month from calendar_join |
N/A | active_end_month | derived | Obtains a list of subscriptions that will still be active at the end of the month from active_start_month |
N/A | subs_churned | derived | Obtains a count of subscriptions that will end in the month from calendar_join |
SQL Flow
To View Larger Image, Right Click and Choose Open Image In New Tab.
/SQL%20Transformations/4840_SUB_BAS_subscription_churn_month%20Tranformation.png?width=688&name=4840_SUB_BAS_subscription_churn_month%20Tranformation.png)
Calculated and Derived Fields
Target Column | Target Column Data Type | Source schema.table | Source Column | Transformation/Logic |
subscriptions_at_start | BIGINT | drp.subscription_profiles | recharge_subscription_id | COUNT(unique recharge_subscription_id) WHERE subscription_start_date < calendar_month |
subscriptions_at_end | BIGINT | drp.subscription_profiles | recharge_subscription_id | Pulls subscriptions_at_start for the next month for current month |
existing_subscriptions_at_end | BIGINT | drp.subscription_profiles | recharge_subscription_id | subs_at_end_date - num_start |
subscriptions_churned | BIGINT | drp.subscription_profiles | recharge_subscription_id | COUNT(unique recharge_subscription_id) WHERE subscription end month = calendar_month |
churns_per_customer_day | BIGINT | drp.subscription_profiles | recharge_subscription_id | subs_churned / ((subs_at_start * days_in_month) * (0.5 * existing_subscriptions_at_end * days_in_month)) |
monthly_churn_rate | DECIMAL | drp.subscription_profiles | recharge_subscription_id | days_in_month * subs_churned / ((subs_at_start * days_in_month) + (0.5 * existing_subscriptions_at_end * days_in_month)) |
All content © Daasity 2021. Do not copy, share or distribute.