Target Database Table
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|
|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|
To View Larger Image, Right Click and Choose Open Image In New Tab.
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.