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.

 4840_SUB_BAS_subscription_churn_month Tranformation
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.