4840_SUB_BAS_subscription_churn_month Tranformation

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.

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))