Tulo Engage BI

Churn model

The base for the churn model relies on the subscription and subscription period models to build reliable data every night.

Churn data is collected every night for different aspects of the subscription stock, making it possible to filter and slice data in various ways. On mondays daily statistics are aggregated into weekly statistics and on 1:st day of month monthly statistics data is aggregated.

Churn calculations

Calculating a churn rate is made by dividing the number of deactivated subscriptions with the number of active subscriptions at the start of the period being inspected.

    (number of deactivations in period / number of active subscriptions at start of period) * 100 = churn rate in percent

When calculating churn we summarize all deactivation categories except deactivated_campaign_transformation, since those are made internally by Payway as a part of transforming a subscription from campaign to a standard subscription.

Daily data

Table: engage_bi.pw_churn_data_daily

column	type	comments
working_date	date	Date statistics was collected
organisation_id	string	The organisation that owns the subscription
product_code	string	Product code for the subscription.
product_type	string	Type of product. Codes and values.
product_title_code	string	Title code for the product
order_category	string	Category of order. Codes and values.
order_type	string	Type of order, "normal", "no_order", or "voucher" if order was created through redeeming a voucher.
order_period_type	string	Period type on current order. "year", "month", "week" or "day".
order_period_length	int	Length of period
order_payment_method	string	Payment method used when purchasing order. Codes and values.
campaign_product_code	string	The product code the campaign is based upon if order is campaign.
subscription_type	string	Indicates if the subscription is "recurring", "limited" or "free". Codes and values.
subscription_category	string	Indicates if the subscription is an actual "subscription" or a "lead". Codes and values.
subscription_business_model	string	Indicates if the subscription belongs to a company ("b2b") or a private person ("b2c"). Determined by the package property "Company package" which is accessible from "Field configuration" on a package.
customer_age	int	Age of customer if known.
customer_gender	string	Gender of customer if known, "M" = Male or "F" = Female, "?" if unknown.
product_tags	string	Comma separated list of tags for the subscription.
active_subscriptions	int	Number of active subscriptions current working date.
deactivated_customer_cancel	int	Number of deactivations initiated by customer
deactivated_customer_passive_payment	int	Number of deactivations due to failure to pay
deactivated_subscription_expired	int	Number of deactivations due to expiration of time limited subscription
deactivated_subscription_change	int	Number of deactivations due to change of subscription
deactivated_subscription_downgrade	int	Number of deactivations due to downgrade of subscription
deactivated_subscription_upgrade	int	Number of deactivations due to upgrade of subscription
deactivated_subscription_extension	int	Number of deactivations due to extension of subscription
deactivated_campaign_transformation	int	Number of deactivations due to campaign transformation (not included in churn calculations)
deactivated_system	int	Number of deactivations due to other reasons than mentioned above, could be internal or customer ordered jobs for example.
added_subscriptions	int	Number of subscriptions added this working date.
shared_subscriptions	int	Number of shared subscriptions.

Weekly data

Table: engage_bi.pw_churn_data_weekly

column	type	comments
week_start_date	date	Start date of week
week_end_date	date	End date of week
week_no	date	Week number
organisation_id	string	The organisation that owns the subscription
product_code	string	Product code for the subscription.
product_type	string	Type of product. Codes and values.
product_title_code	string	Title code for the product
order_category	string	Category of order. Codes and values.
order_type	string	Type of order, "normal", "no_order", or "voucher" if order was created through redeeming a voucher.
order_period_type	string	Period type on current order. "year", "month", "week" or "day".
order_period_length	int	Length of period
order_payment_method	string	Payment method used when purchasing order. Codes and values.
campaign_product_code	string	The product code the campaign is based upon if order is campaign.
subscription_type	string	Indicates if the subscription is "recurring", "limited" or "free". Codes and values.
subscription_category	string	Indicates if the subscription is an actual "subscription" or a "lead". Codes and values.
subscription_business_model	string	Indicates if the subscription belongs to a company ("b2b") or a private person ("b2c"). Determined by the package property "Company package" which is accessible from "Field configuration" on a package.
customer_age	int	Age of customer if known.
customer_gender	string	Gender of customer if known, "M" = Male or "F" = Female, "?" if unknown.
product_tags	string	Comma separated list of tags for the subscription.
active_subscriptions_start	int	Number of active subscriptions at start of week.
active_shared_start	int	Number of shared subscriptions at start of week.
active_subscriptions_end	int	Number of active subscriptions at end of week.
active_shared_end	int	Number of shared subscriptions at end of week.
deactivated_customer_cancel	int	Number of deactivations initiated by customer
deactivated_customer_passive_payment	int	Number of deactivations due to failure to pay
deactivated_subscription_expired	int	Number of deactivations due to expiration of time limited subscription
deactivated_subscription_change	int	Number of deactivations due to change of subscription
deactivated_subscription_downgrade	int	Number of deactivations due to downgrade of subscription
deactivated_subscription_upgrade	int	Number of deactivations due to upgrade of subscription
deactivated_subscription_extension	int	Number of deactivations due to extension of subscription
deactivated_campaign_transformation	int	Number of deactivations due to campaign transformation (not included in churn calculations)
deactivated_system	int	Number of deactivations due to other reasons than mentioned above, could be internal or customer ordered jobs for example.
added_subscriptions	int	Number of subscriptions added this week.

Monthly data

Table: engage_bi.pw_churn_data_monthly

column	type	comments
month_start_date	date	Start date of month
month_end_date	date	End date of month
organisation_id	string	The organisation that owns the subscription
product_code	string	Product code for the subscription.
product_type	string	Type of product. Codes and values.
product_title_code	string	Title code for the product
order_category	string	Category of order. Codes and values.
order_type	string	Type of order, "normal", "no_order", or "voucher" if order was created through redeeming a voucher.
order_period_type	string	Period type on current order. "year", "month", "week" or "day".
order_period_length	int	Length of period
order_payment_method	string	Payment method used when purchasing order. Codes and values.
campaign_product_code	string	The product code the campaign is based upon if order is campaign.
subscription_type	string	Indicates if the subscription is "recurring", "limited" or "free". Codes and values.
subscription_category	string	Indicates if the subscription is an actual "subscription" or a "lead". Codes and values.
subscription_business_model	string	Indicates if the subscription belongs to a company ("b2b") or a private person ("b2c"). Determined by the package property "Company package" which is accessible from "Field configuration" on a package.
customer_age	int	Age of customer if known.
customer_gender	string	Gender of customer if known, "M" = Male or "F" = Female, "?" if unknown.
product_tags	string	Comma separated list of tags for the subscription.
active_subscriptions_start	int	Number of active subscriptions at start of month.
active_shared_start	int	Number of shared subscriptions at start of month.
active_subscriptions_end	int	Number of active subscriptions at end of month.
active_shared_end	int	Number of shared subscriptions at end of month.
deactivated_customer_cancel	int	Number of deactivations initiated by customer
deactivated_customer_passive_payment	int	Number of deactivations due to failure to pay
deactivated_subscription_expired	int	Number of deactivations due to expiration of time limited subscription
deactivated_subscription_change	int	Number of deactivations due to change of subscription
deactivated_subscription_downgrade	int	Number of deactivations due to downgrade of subscription
deactivated_subscription_upgrade	int	Number of deactivations due to upgrade of subscription
deactivated_subscription_extension	int	Number of deactivations due to extension of subscription
deactivated_campaign_transformation	int	Number of deactivations due to campaign transformation (not included in churn calculations)
deactivated_system	int	Number of deactivations due to other reasons than mentioned above, could be internal or customer ordered jobs for example.
added_subscriptions	int	Number of subscriptions added this working date.

Added subscriptions

A subscription is classified as added a specific day if it has a subscription period with a start date matching the specific day. This can include subscriptions that have been purchased with a future start date, then the start date will be the activation date of the subscription. It will also include a subscription that previously have been deactivated due to failure to pay in time, but later have been reactivated at the specific day due to a late payment.

Added and deactivated

As a step in the nightly aggregation of churn data, added and deactivated subscriptions are also collected into separate tables so it is easy to find which subscriptions that have been added or deactivated a specific day.

Added

Table: engage_bi.pw_subscriptions_added_daily

column	type	comments
working_date	date	Date statistics was collected
organisation_id	string	The organisation that owns the subscription
subscription_id	string	The subscription added
created_from	string	Indicates how the subscription was created, from "migration" or a "purchase" for example. Codes and values.

Deactivated

Table: engage_bi.pw_subscriptions_deactivated_daily

column	type	comments
working_date	date	Date statistics was collected
organisation_id	string	The organisation that owns the subscription
subscription_id	string	The subscription added
deactivation_reason	string	The reason for deactivation of the subscription if it is deactivated. Codes and values.

Sample queries

Active subscriptions a specific day

Find number of active "digital only" subscriptions for the first day of the year which are actually paid. Meaning we do not want to include free subscriptions and also not any campaigns that have started with a totally zero cost period, also check product type to filter digital only products.

SELECT working_date, 
       SUM(active_subscriptions)
FROM engage_bi.pw_churn_data_daily
WHERE working_date = '2025-01-01'       
AND subscription_type != 'free'
AND order_category != 'zero_campaign'
AND product_type = 'digital_only'
GROUP BY working_date;

Active subscriptions weekly

Same as above, but get weekly overview including netgain.

SELECT week_start_date,
       week_end_date, 
       SUM(active_subscriptions_start) AS active_start,
       SUM(active_subscriptions_end) AS active_end,
       (active_end - active_start) AS netgain
FROM engage_bi.pw_churn_data_weekly
WHERE subscription_type != 'free'
AND order_category != 'zero_campaign'
AND product_type = 'digital_only'
AND week_start_date > '2025-01-01'
GROUP BY week_start_date, week_end_date
ORDER BY week_start_date DESC;

Active subscriptions monthly

Same as above, but get monthly overview including netgain.

SELECT month_start_date,
       month_end_date, 
       SUM(active_subscriptions_start) AS active_start,
       SUM(active_subscriptions_end) AS active_end,
       (active_end - active_start) AS netgain
FROM engage_bi.pw_churn_data_monthly
WHERE subscription_type != 'free'
AND order_category != 'zero_campaign'
AND product_type = 'digital_only'
AND month_start_date = '2025-01-01'
GROUP BY month_start_date, month_end_date
ORDER BY month_start_date DESC;

Active subscriptions and churn rate monthly

Same as above, but also add churn rate

SELECT month_start_date,
       month_end_date, 
       SUM(active_subscriptions_start) AS active_start,
       SUM(active_subscriptions_end) AS active_end,
       (active_end - active_start) AS netgain,
       SUM(deactivated_customer_cancel
           +deactivated_customer_passive_payment
           +deactivated_subscription_change
           +deactivated_subscription_expired
           +deactivated_subscription_downgrade
           +deactivated_subscription_upgrade
           +deactivated_subscription_extension           
           +deactivated_system) AS deactivated,
       ROUND((deactivated / active_start::FLOAT)*100, 2) AS churn_rate
FROM engage_bi.pw_churn_data_monthly
WHERE subscription_type != 'free'
AND order_category != 'zero_campaign'
AND product_type = 'digital_only'
AND month_start_date = '2025-01-01'
GROUP BY month_start_date, month_end_date
ORDER BY month_start_date DESC;