BI 400: Subscription Analytics for Growth-Focused Products using Data Distiller

Unlocking Key Subscription Metrics to Drive Growth and Retention with Powerful Visualizations

Last updated 6 months ago

Overview

Subscription analytics involves tracking and analyzing key metrics that measure the health and growth potential of a subscription-based business. These metrics provide insights into customer behavior, revenue trends, and overall product engagement, which are crucial for making informed business decisions. Common metrics in subscription analytics include churn rate, Monthly Recurring Revenue (MRR), Average Revenue Per User (ARPU), Customer Lifetime Value (CLTV), Daily Active Users (DAU), and Monthly Active Users (MAU). Monitoring these metrics helps companies understand how well they are retaining customers, growing revenue, and encouraging product usage over time.

Subscription analytics and metrics like the above are widely used across various many industries:

Software as a Service (SaaS): SaaS companies use subscription metrics to monitor user adoption, gauge engagement, adjust pricing, and reduce churn. Key metrics like MRR, ARPU, and CLTV help them assess profitability and the long-term value of customers
Media and Entertainment: Streaming services like Netflix and Spotify, along with digital content platforms, use subscription analytics to track subscriber growth, engagement, and retention. DAU and MAU help measure content consumption frequency, while churn rate indicates the stability of the subscriber bas
Telecommunications: Telecom companies use subscription metrics to manage services like mobile plans, internet packages, and TV subscriptions. These metrics help track customer satisfaction, prevent service cancellations, and identify upselling opportunities.
Health and Fitness: Subscription-based fitness apps, online coaching, and gym memberships use these metrics to measure user engagement and assess the effectiveness of their services. By tracking user activity and cancellations, companies can identify ways to improve customer experiences and reduce churn.
E-Learning and EdTech: Online learning platforms and educational subscription services use subscription analytics to track student engagement, course completion rates, and subscription renewals. Metrics like MRR and CLTV help determine the financial impact of customer acquisition efforts and retention strategies.
E-Commerce and Subscription Boxes: Companies offering subscription boxes (e.g., beauty products, meal kits) or membership programs track these metrics to monitor growth, manage customer retention, and optimize recurring revenue. Understanding churn and CLTV helps improve product offerings and marketing strategies.
Financial Services: Subscription-based financial services (e.g., investment platforms, credit monitoring services) use these metrics to evaluate customer usage patterns and assess the impact of retention initiatives on overall revenue.

Data Distiller excels in visualizing key product metrics, offering a comprehensive and intuitive experience compared to other tools. With its powerful data processing capabilities, Data Distiller allows for seamless aggregation and visualization of important metrics such as churn rate, MRR, ARPU, CLTV, DAU, and MAU. The product's flexibility in handling different data sources and transforming raw data into actionable insights makes it particularly effective for tracking user engagement trends and customer lifetime metrics.

Compared to other vendors that provide pre-defined metric calculations and dashboards, Data Distiller enables deeper customization and more granular analysis. This allows teams to identify underlying issues, such as differences between DAU and MAU, and drill down into specific user behaviors that contribute to churn. The ability to build tailored visualizations helps in making more data-driven decisions for growth strategies, empowering companies to proactively address problems, optimize customer retention, and drive sustainable growth.

Prerequisites

Download the following dataset:

Use the following tutorial to onboard this CSV data:

PREP 500: Ingesting CSV Data into Adobe Experience Platform

The dataset above customer journey events for 10000 users across a 12-month period. It is designed to capture key actions taken by users as they interact with a SaaS application, providing a timeline of events such as signups, logins, conversions to paid plans, monthly payments, and churn:

Visitor_ID: A unique identifier for each user.
Month: The month in which a particular event occurred. It covers a 12-month period from January to December. Each user has a timeline of events that can span multiple months, depending on their activities.
Event: The type of event or action taken by the user. This could be one of the following:
- browse: When the user is just browsing the website but has not signed up.
- signup: When the user initially signs up for the app. When they do, they get access to the free version but need to pay to get access to the premium features.
- login: Each time the user logs into the app. Users log in with varying frequencies.
- convert_to_paid: If the user upgrades from the free plan to a paid subscription.
- payment: For users who have converted to paid, this represents the monthly payment they make (assumed to be $5.99/month).
- churn: If the user cancels their subscription after converting to paid.
Amount (only for payment events): The dollar amount for the subscription payment. It is set to $5.99 for users who are paying for the app, applicable only for rows where the Event is 'payment.'

You will also need the following prerequisite for the dashboards we will build:

BI 200: Create Your First Data Model in the Data Distiller Warehouse for Dashboarding

and

BI 300: Dashboard Authoring with Data Distiller Query Pro Mode

Modeling the Customer Journey

When you're analyzing customer journeys, you're essentially mapping a series of interactions that a user has with a product, platform, or service. These interactions aren't linear; they vary widely depending on user behavior, preferences, and decisions. Think of it like a decision branch where each action (signup, login, upgrade to paid, churn, etc.) creates a new path in the user's journey, and each path has its own implications for metrics like revenue, engagement, and retention. However, no matter how intricate the customer journey is—filled with multiple branching decisions, complex behaviors, and varied touchpoints—you can still bring that data into a simplified, flat schema. This allows you to standardize and analyze the different paths users take.

Once your data is in a canonical schema, you can build templates for SQL queries that accommodate multiple outcomes. This is crucial when modeling customer journeys because you can’t predict every individual path a user will take. Instead of writing one-off queries for each possible decision branch (e.g., “users who signed up in January and converted in February but churned in April”), you can generalize the query to cover all possible scenarios.

Because the schema is flat, the query structure stays consistent, and only the parameters (like dates, actions, or conditions) need to change. This ensures that the analysis remains scalable as the user base grows, and different decision branches are taken.

For this tutorial, I have modeled the customer journey as an example, but as a product manager, defining this journey is crucial for your specific use case. The key is to pinpoint the critical touchpoints that matter most in the user experience. While my example is simplified, your journey may include additional events, such as feature launches or user engagement with specific features.

Visits/Browse Events: These are visitors browsing the SaaS app without signing up. If they don't sign up, they won't generate any further events such as logins, paid conversions, or churn.
Signups: Each user has a signup event recorded for the month they initially join the app. This event is crucial for tracking when a user becomes part of the system. Upon signing up, users gain access to the app's free features. It's important to note that even if a user churns, they can still continue to use the free features.
Logins: Users log in multiple times after signing up. The login frequency varies across users and is randomly generated for the months following their signup. This helps calculate metrics like DAU (Daily Active Users) and MAU (Monthly Active Users).
Paid Conversions: Some users convert to a paid plan after signing up. The conversion happens after the signup event, at some random time within the 12 months. This event is crucial for tracking conversion rates and revenue generation.
Payments: Users who have converted to a paid plan are charged $5.99 per month. The payment event occurs monthly for each user as long as they remain subscribed and have not churned.
Churn: Users who have converted to a paid plan can churn (cancel their subscription). The churn event occurs randomly after the conversion, with a 15% churn rate. Once a user churns, no further payment events are recorded for that user.

I am also going to define DAU, MAU and other SaaS style metrics for the purposes of this analysis:

Engagement

DAU (Daily Active Users) is defined as at least one login per day per user. This is a time-series metric because it tracks user activity over time, specifically on a daily basis. Each day's DAU represents the number of unique users who logged in at least once that day.
MAU (Monthly Active Users) is defined as at least 5 logins per month per user. This is a time-series metric because it tracks user activity over time, specifically on a monthly basis. Each month's MAU represents the number of unique users who logged in at least 5 times during that month
Churn Rate represents the percentage of our users who cancel their subscription (indicated by a churn event) or have stopped using the product (no login after a signup event) after upgrading to a paid plan within a given period. It helps measure how quickly we are losing paying customers. The Retention Rate is 1 - Churn Rate.

\text{Churn Rate} = \left( \frac{\text{Number of Churned Customers} + \text{Number of Inactive Customers}}{\text{Total Number of Paid Customers}} \right) \times 100

Revenue

MRR (Monthly Recurring Revenue): This measures the predictable revenue our app generates from paid users on a monthly basis. In our case, every user who converts to a paid plan pays $5.99 per month. MRR allows us to track how much recurring revenue is generated each month as users upgrade from the free version to the paid plan.

\text{MRR} = \text{Number of Paying Users} \times \text{Subscription Fee Per User}

ARR (Annual Recurring Revenue) projects the yearly value of our monthly subscriptions. In this example, ARR is calculated by taking the Monthly Recurring Revenue (MRR) from the last month and multiplying it by 12. This represents a full year's worth of payments from all paying users, based on the MRR of the most recent month.
Average Revenue Per User (ARPU) is an essential metric that represents the average revenue generated per user over a specific period— in this case, on a monthly basis. It provides insight into how much revenue, on average, each user (including both free and paid users) contributes to your business.

\text{ARPU} = \frac{\text{Total Monthly Recurring Revenue (MRR)}}{\text{Total Number of Active Users (Free and Paid)}}

Customer Lifetime Value (CLTV) is a metric that estimates the total revenue a business can expect to generate from a customer over the entire duration of their relationship. It helps businesses understand the long-term value of their customers and can guide decisions on customer acquisition and retention strategies. A high CLTV indicates that customers are valuable and tend to stick around for longer periods, contributing more revenue over time. The formula below is derived in the .

\text{CLTV} = \frac{\text{Average Revenue per User (ARPU)}}{\text{Churn Rate}}

Conversion

Signup to Paid Conversion Rate: The percentage of users who convert from a free trial or a free plan to a paid plan..

\text{Conversion Rate} = \left( \frac{\text{Number of Users Who Convert to Paid}}{\text{Total Signups}} \right) \times 100

Cohort Analysis (Excluded from this Chapter)

Cohort Retention: Track how different cohorts (groups of users who signed up in the same period) behave over time in terms of engagement, retention, and conversion.
Cohort Conversion: Analyzing which signup cohorts are converting into paid users, and whether certain months or marketing strategies perform better.

Create the Table in the Data Distiller Warehouse

Execute each of these three statements, one at a time using the Run Selected Query feature

-- Create the Database in the Warehouse
CREATE DATABASE saas WITH (TYPE=QSACCEL, ACCOUNT=acp_query_batch);

-- Create the data model
CREATE SCHEMA saas.facttable;

-- Create an empty dataset
CREATE TABLE saas.facttable.customer_journey_saas AS
       SELECT
          cast(null as string) AS Visitor_ID,
          cast(null as string) AS Month, 
          cast(null as string) AS Event,
          cast(null as decimal(18,2)) AS Amount
       WHERE false;
       
-- Rename the data model for readability in Data Distiller Dashboards
ALTER MODEL saas.facttable RENAME TO saas_analysis;

-- Hydrate the table
INSERT INTO saas.facttable.customer_journey_saas
SELECT * FROM customer_journey_saas;

Note that there are two datasets named customer_journey_saas. One is stored in the data lake, ingested via a CSV upload, and the other is the one we created in the Warehouse. When dealing with datasets that share the same name, you must specify the full path for the dataset in the Warehouse to ensure the Data Distiller engine correctly identifies which one to use.

Launch the Data Distiller Query Pro Mode Editor by navigating to Dashboards->Create Dashboard->Name of dashboard: saas->Choose Query Pro Mode->Add new Widget->Enter SQL
Before you run the SQL query, make sure you choose saas_analysis in the data model dropdown:

SELECT * FROM saas.facttable.customer_journey_saas;

You will follow the process of creating your SQL query with the saas_analysis data model and then selecting the appropriate visualization. This should be fairly intuitive, but if you need assistance, you can refer to the detailed steps in the tutorial .

Foundational Metrics

Total Unique Visitors (Yearly)

SQL code that you will paste in the Query Pro Mode is:

SELECT 
    COUNT(DISTINCT Visitor_ID) AS Total_Unique_Visitors
FROM customer_journey_saas;

Total Unique Signups (Yearly)

SQL code that you will paste in the Query Pro Mode is:

SELECT COUNT(DISTINCT Visitor_ID) AS Total_Signups
FROM customer_journey_saas
WHERE Event = 'signup';

The visualization that you should be able to get to is this one:

Total Paid Unique Customers (Yearly)

SELECT 
    COUNT(DISTINCT Visitor_ID) AS Total_Unique_Paid_Customers
FROM customer_journey_saas
WHERE Event = 'convert_to_paid';

Paid Conversion Rate (Over the Year)

SQL code that you will paste in the Query Pro Mode Editor is:

WITH Total_Signups AS (
    SELECT COUNT(DISTINCT Visitor_ID) AS Total_Signups
    FROM customer_journey_saas
    WHERE Event = 'signup'
),
Paid_Conversions AS (
    SELECT COUNT(DISTINCT Visitor_ID) AS Total_Paid
    FROM customer_journey_saas
    WHERE Event = 'convert_to_paid'
)
SELECT 
    (Paid_Conversions.Total_Paid * 100.0) / Total_Signups.Total_Signups AS Paid_Conversion_Rate
FROM Total_Signups, Paid_Conversions;

The visualization that you should be able to get to is this one. In fact, this is the ratio of the signup to paid customers from the big number charts above.

Total Churned Unique Customers (Yearly)

The query would be:

SELECT 
    COUNT(DISTINCT Visitor_ID) AS Total_Unique_Churned_Customers
FROM customer_journey_saas
WHERE Event = 'churn';

Churn Rate (Yearly)

You would be led to believe that this would be approximately 133/838=15% on a yearly basis, based on the numbers above. You would think that this represents the ratio of unique churned customers to unique paid customers

WITH Total_Paid_Users AS (
    SELECT COUNT(DISTINCT Visitor_ID) AS Total_Paid
    FROM customer_journey_saas
    WHERE Event = 'convert_to_paid'
),
Churned_Users AS (
    SELECT COUNT(DISTINCT Visitor_ID) AS Churned
    FROM customer_journey_saas
    WHERE Event = 'churn'
)
SELECT 
    (Churned_Users.Churned * 100.0) / Total_Paid_Users.Total_Paid AS Churn_Rate
FROM Total_Paid_Users, Churned_Users;

But our definition requires that we also consider users who are either paying but not actively using the product, or those who have access to the free version but are not using it.

WITH Paid_Customers AS (
    -- Total unique paid customers who converted to a paid plan
    SELECT DISTINCT Visitor_ID
    FROM customer_journey_saas
    WHERE Event = 'convert_to_paid'
),
Churned_Customers AS (
    -- Users with an explicit churn event
    SELECT DISTINCT Visitor_ID
    FROM customer_journey_saas
    WHERE Event = 'churn'
),
Inactive_Customers AS (
    -- Users who have stopped using the product (no login after signup)
    SELECT DISTINCT s.Visitor_ID
    FROM customer_journey_saas s
    LEFT JOIN customer_journey_saas l 
    ON s.Visitor_ID = l.Visitor_ID 
    AND l.Event = 'login' 
    AND l.Month > s.Month 
    WHERE s.Event = 'signup'
    AND l.Visitor_ID IS NULL -- No login after signup
)
-- Calculate the Churn Rate based on both churned and inactive customers
SELECT 
    (COUNT(DISTINCT c.Visitor_ID) + COUNT(DISTINCT i.Visitor_ID)) * 100.0 / COUNT(DISTINCT p.Visitor_ID) AS Churn_Rate
FROM Paid_Customers p
LEFT JOIN Churned_Customers c ON p.Visitor_ID = c.Visitor_ID
LEFT JOIN Inactive_Customers i ON p.Visitor_ID = i.Visitor_ID;

The code above requires some explanation. Consider the following example:

Visitor_ID

Month

Event

Jan

signup

Feb

Jan

signup

Mar

signup

Apr

You can clearly see that Visitor_ID=2 has churned as an inactive user for the timespan above.

If we examine the following code fragment, we see:

SELECT DISTINCT s.Visitor_ID
    FROM customer_journey_saas s
    LEFT JOIN customer_journey_saas l 
    ON s.Visitor_ID = l.Visitor_ID 
    AND l.Event = 'login' 
    AND l.Month > s.Month 
    WHERE s.Event = 'signup'
    AND l.Visitor_ID IS NULL -- No login after signup

LEFT JOIN means the rows on the left hand side that cannot be matched with any rows fro the right hand side will remain. Also, let us explore the ON condition which is part of the LEFT JOIN clause and it specifies how the two tables (s and l) should be joined. Let’s break down what each part of this condition means:

s.Visitor_ID = l.Visitor_ID: This condition joins the records from the two tables (s and l) based on matching Visitor_ID. It ensures that we are comparing events for the same user in both tables.
AND l.Event = 'login': This condition filters the joined records to include only rows where the event in the l table is a 'login' event. This means we are only interested in login activities when joining the tables.
AND l.Month > s.Month: This condition checks that the login event (l.Month) occurred after the signup event (s.Month). It ensures that we are only considering login activities that happened after the user signed up.

Now, it turns out that rows in Table s that cannot be joined with Table l will have all the valuees of the l Table all appear as NULLs as seeen for Visitor_ID=2

Visitor_ID

s.Month

s.Event

l.Month

l.Event

l.Visitor_ID

Jan

signup

Feb

Jan

signup

NULL

Mar

signup

Apr

We need these rows and so the following filter condition woul extract that information out.

WHERE s.Event = 'signup'
AND l.Visitor_ID IS NULLAND l.Visitor_ID IS NULL

The chart should look liken which is depressing:

Monthly Recurring Revenue

We will convert the month names to numerical values to ensure the time axis on the chart is properly ordered. The line chart cannot automatically order string values since it lacks the inherent ordering information from the data. Additionally, note that if there are around 1,000 paid users, each paying $6 per month, the graph should not exceed $60,000. Here is the SQL query:

SELECT 
    Month, 
    SUM(Amount) AS MRR
FROM customer_journey_saas
WHERE Event = 'payment'
GROUP BY Month
ORDER BY 
    CASE 
        WHEN Month = 'Jan' THEN 1
        WHEN Month = 'Feb' THEN 2
        WHEN Month = 'Mar' THEN 3
        WHEN Month = 'Apr' THEN 4
        WHEN Month = 'May' THEN 5
        WHEN Month = 'Jun' THEN 6
        WHEN Month = 'Jul' THEN 7
        WHEN Month = 'Aug' THEN 8
        WHEN Month = 'Sep' THEN 9
        WHEN Month = 'Oct' THEN 10
        WHEN Month = 'Nov' THEN 11
        WHEN Month = 'Dec' THEN 12
        ELSE NULL 
    END;

The ORDER BY clause with the CASE statement in the query is used to sort the results in chronological order based on the month names (e.g., 'Jan', 'Feb', etc.). The CASE statement assigns a numeric value to each month name:

'Jan' is assigned the value 1.
'Feb' is assigned the value 2.
'Mar' is assigned the value 3.
And so on, up to 'Dec', which is assigned the value 12.

This mapping converts the month names into a numeric order, representing the natural sequence of months.

Annual Recurring Revenue

This query takes the last month which is around $4000 MRR for December and multiplies that by 12.

SELECT 
    SUM(MRR) * 12 AS ARR
FROM (
    SELECT 
        SUM(Amount) AS MRR
    FROM customer_journey_saas
    WHERE Event = 'payment'
    -- Find the most recent month (you can adjust this depending on your date format)
    GROUP BY Month
    ORDER BY CASE 
        WHEN Month = 'Jan' THEN '01'
        WHEN Month = 'Feb' THEN '02'
        WHEN Month = 'Mar' THEN '03'
        WHEN Month = 'Apr' THEN '04'
        WHEN Month = 'May' THEN '05'
        WHEN Month = 'Jun' THEN '06'
        WHEN Month = 'Jul' THEN '07'
        WHEN Month = 'Aug' THEN '08'
        WHEN Month = 'Sep' THEN '09'
        WHEN Month = 'Oct' THEN '10'
        WHEN Month = 'Nov' THEN '11'
        WHEN Month = 'Dec' THEN '12'
        ELSE NULL 
    END DESC
    -- Limit to the last month only
    LIMIT 1
) AS Last_Month_MRR;

Average Revenue Per User (Monthly)

To calculate the Average Revenue Per User (ARPU) by dividing the total revenue by the total number of unique users (both free and paid, excluding non-signups) for each month, you can use the following SQL query:

WITH Active_Signups AS (
    -- Get all users who signed up (excluding non-signups)
    SELECT DISTINCT 
        Month,
        Visitor_ID
    FROM customer_journey_saas
    WHERE Event IN ('signup', 'login')
),
Monthly_Revenue AS (
    -- Calculate the total revenue generated from payments in each month
    SELECT 
        Month,
        SUM(Amount) AS Total_Revenue
    FROM customer_journey_saas
    WHERE Event = 'payment'
    GROUP BY Month
)
-- Calculate ARPU by dividing total revenue by the number of active signups
SELECT 
    a.Month,
    COALESCE(mr.Total_Revenue, 0) / COUNT(DISTINCT a.Visitor_ID) AS ARPU
FROM Active_Signups a
LEFT JOIN Monthly_Revenue mr ON a.Month = mr.Month
GROUP BY a.Month, mr.Total_Revenue
ORDER BY 
    CASE 
        WHEN a.Month = 'Jan' THEN '01'
        WHEN a.Month = 'Feb' THEN '02'
        WHEN a.Month = 'Mar' THEN '03'
        WHEN a.Month = 'Apr' THEN '04'
        WHEN a.Month = 'May' THEN '05'
        WHEN a.Month = 'Jun' THEN '06'
        WHEN a.Month = 'Jul' THEN '07'
        WHEN a.Month = 'Aug' THEN '08'
        WHEN a.Month = 'Sep' THEN '09'
        WHEN a.Month = 'Oct' THEN '10'
        WHEN a.Month = 'Nov' THEN '11'
        WHEN a.Month = 'Dec' THEN '12'
        ELSE NULL 
    END;

Customer Lifetime Value (Yearly)

This just takes the formula of ARPU and Churn Rate at the end of the year and divides them. You can see that it should be approximately

WITH Active_Signups AS (
    -- Get all users who signed up or logged in (excluding non-signups)
    SELECT DISTINCT 
        Month,
        Visitor_ID
    FROM customer_journey_saas
    WHERE Event IN ('signup', 'login')
),
Monthly_Revenue AS (
    -- Calculate the total revenue generated from payments in each month
    SELECT 
        Month,
        SUM(Amount) AS Total_Revenue
    FROM customer_journey_saas
    WHERE Event = 'payment'
    GROUP BY Month
),
ARPU_Calculation AS (
    -- Calculate ARPU for December
    SELECT 
        a.Month,
        COALESCE(mr.Total_Revenue, 0) / COUNT(DISTINCT a.Visitor_ID) AS ARPU
    FROM Active_Signups a
    LEFT JOIN Monthly_Revenue mr ON a.Month = mr.Month
    WHERE a.Month = 'Dec'
    GROUP BY a.Month, mr.Total_Revenue
),
Paid_Customers AS (
    -- Total unique paid customers who converted to a paid plan during the year
    SELECT DISTINCT Visitor_ID
    FROM customer_journey_saas
    WHERE Event = 'convert_to_paid'
),
Churned_Customers AS (
    -- Users with an explicit churn event during the year
    SELECT DISTINCT Visitor_ID
    FROM customer_journey_saas
    WHERE Event = 'churn'
),
Inactive_Customers AS (
    -- Users who have stopped using the product (no login after signup throughout the year)
    SELECT DISTINCT s.Visitor_ID
    FROM customer_journey_saas s
    LEFT JOIN customer_journey_saas l 
    ON s.Visitor_ID = l.Visitor_ID 
    AND l.Event = 'login' 
    AND l.Month > s.Month 
    WHERE s.Event = 'signup'
    AND l.Visitor_ID IS NULL -- No login after signup
),
Churn_Rate_Calculation AS (
    -- Calculate the Churn Rate for the entire year based on both churned and inactive customers
    SELECT 
        (COUNT(DISTINCT c.Visitor_ID) + COUNT(DISTINCT i.Visitor_ID)) * 100.0 / COUNT(DISTINCT p.Visitor_ID) AS Churn_Rate
    FROM Paid_Customers p
    LEFT JOIN Churned_Customers c ON p.Visitor_ID = c.Visitor_ID
    LEFT JOIN Inactive_Customers i ON p.Visitor_ID = i.Visitor_ID
)
-- Calculate the CLTV at the end of the year (using ARPU for December and Churn Rate for the entire year)
SELECT 
    'Dec' AS Period,
    ac.ARPU,
    cr.Churn_Rate,
    COALESCE(ac.ARPU, 0) / NULLIF(cr.Churn_Rate / 100.0, 0) AS CLTV -- Convert Churn Rate to decimal
FROM ARPU_Calculation ac
CROSS JOIN Churn_Rate_Calculation cr;

It's crucial for the company to recognize that a 15% cancellation rate might initially seem manageable, leading them to estimate the CLTV (Customer Lifetime Value) at around $11.2 per customer, assuming a steady revenue stream and ignoring the usage statistics. However, a deeper look reveals a concerning trend: many customers are not actively using the product. This inactivity is a strong indication that these customers may eventually cancel their subscriptions. Relying solely on the churn rate without considering engagement metrics could create a false sense of security, masking the risk of future cancellations and revenue loss. It's a signal that the company needs to address product adoption and engagement to truly understand and improve customer retention.

Monthly Active Users (MAU)

Based on our definition earlier, a user will count towards a MAU if they have logged in atleast 5 times a month.

WITH Monthly_Logins AS (
    -- Count the number of logins per user per month
    SELECT 
        Month,
        Visitor_ID,
        COUNT(*) AS Login_Count
    FROM customer_journey_saas
    WHERE Event = 'login'
    GROUP BY Month, Visitor_ID
)
-- Calculate MAU by counting users with at least 5 logins in the month
SELECT 
    Month,
    COUNT(DISTINCT Visitor_ID) AS MAU
FROM Monthly_Logins
WHERE Login_Count >= 5
GROUP BY Month
ORDER BY 
    CASE 
        WHEN Month = 'Jan' THEN 1
        WHEN Month = 'Feb' THEN 2
        WHEN Month = 'Mar' THEN 3
        WHEN Month = 'Apr' THEN 4
        WHEN Month = 'May' THEN 5
        WHEN Month = 'Jun' THEN 6
        WHEN Month = 'Jul' THEN 7
        WHEN Month = 'Aug' THEN 8
        WHEN Month = 'Sep' THEN 9
        WHEN Month = 'Oct' THEN 10
        WHEN Month = 'Nov' THEN 11
        WHEN Month = 'Dec' THEN 12
        ELSE NULL
    END;

The chart should look like this:

Daily Active Users (DAU)

Since we don't have timestamps to identify daily login activity, we need to make assumptions to estimate the Daily Active Users (DAU). One common approach is to assume that user activity is evenly distributed across the days in the month. In other words, we assume that the number of active users is approximately the same for each day.

If we know the Monthly Active Users (MAU)—the number of unique users who logged in at least 5 times during the month—we can distribute this number across the days in the month to estimate the average number of daily active users.

The formula for this estimation is:

\text{Estimated DAU} = \frac{\text{MAU}}{\text{Number of Days in the Month}}

The SQL code will need to be adapted for this approximation:

WITH Monthly_Logins AS (
    -- Count the number of users with at least 5 logins in the month (as per the previous MAU calculation)
    SELECT 
        Month,
        COUNT(DISTINCT Visitor_ID) AS MAU
    FROM customer_journey_saas
    WHERE Event = 'login'
    GROUP BY Month
)
-- Estimate DAU based on uniform distribution
SELECT 
    Month,
    MAU / CASE 
             WHEN Month IN ('Jan', 'Mar', 'May', 'Jul', 'Aug', 'Oct', 'Dec') THEN 31
             WHEN Month IN ('Apr', 'Jun', 'Sep', 'Nov') THEN 30
             WHEN Month = 'Feb' THEN 28 -- or 29 for leap years
             ELSE NULL
         END AS Estimated_DAU
FROM Monthly_Logins
ORDER BY 
    CASE 
        WHEN Month = 'Jan' THEN 1
        WHEN Month = 'Feb' THEN 2
        WHEN Month = 'Mar' THEN 3
        WHEN Month = 'Apr' THEN 4
        WHEN Month = 'May' THEN 5
        WHEN Month = 'Jun' THEN 6
        WHEN Month = 'Jul' THEN 7
        WHEN Month = 'Aug' THEN 8
        WHEN Month = 'Sep' THEN 9
        WHEN Month = 'Oct' THEN 10
        WHEN Month = 'Nov' THEN 11
        WHEN Month = 'Dec' THEN 12
        ELSE NULL
    END;

The chart will look like the following:

Data Distiller Dashboard

Analysis & Recommendations

Summary of key metrics:

Unique Visitors: 9,279 yearly.
Total Signups: 2,887 yearly.
Unique Paid Customers: 838 yearly.
Churned Customers: 133 yearly
Paid Conversion Rate: 29.03% yearly
Churn Rate: 54.42% annually.
Monthly Recurring Revenue (MRR): $4200 in December
Annual Recurring Revenue (ARR): $50,675
Average Revenue Per User (ARPU): $2.98 yearly
Customer Lifetime Value (CLTV): $2.98 yearly
MAU: 2200 in Dec
DAU: 83 in Dec

Analysis

High Churn Rate (54.42%): Over half of the paying customers are leaving within a year, which significantly impacts revenue and Customer Lifetime Value (CLTV).
Low CLTV ($2.98) Relative to Subscription Fee ($5.99): The CLTV is much lower than the monthly subscription cost, indicating that many customers do not stay subscribed long enough to cover even one full month.
Low Average Revenue Per User (ARPU) ($2.98): The ARPU is also quite low, suggesting that the overall revenue generated per user is not sufficient to compensate for high churn.
Disparity Between MAU (2,200) and DAU (83): The significant difference between Monthly Active Users (MAU) and Daily Active Users (DAU) indicates that many users are not engaging with the product frequently, potentially leading to cancellations.
Paid Conversion Rate (29.03%): Nearly a third of the users who sign up for the free version convert to a paid plan, which shows that the product does provide perceived value to a portion of the users.
Growth Potential: With 9,279 unique visitors and 2,887 total signups annually, there is a sizable number of potential customers. Increasing the conversion rate or re-engaging inactive users could lead to significant growth.
Monthly Recurring Revenue (MRR) ($4,200 in December): The MRR for December indicates some revenue stability, which can be used as a foundation to build upon by reducing churn and boosting engagement.

Recommendations

Reduce Churn: Addressing churn should be the top priority. Strategies may include offering discounts for longer-term commitments, improving customer support, and proactively engaging customers who show signs of potential churn.
Boost Engagement: Focus on increasing DAU by enhancing user engagement. Improve onboarding to highlight the product's benefits, introduce gamification or loyalty rewards, and release new features to incentivize daily usage.
Increase CLTV and ARPU: Introduce premium tiers or add-ons to increase ARPU. Additionally, emphasize upselling and cross-selling to current customers to improve CLTV.
Leverage Conversion Opportunities: With a conversion rate of 29.03%, there is room for improvement. Implementing targeted marketing campaigns or incentivizing trials could help increase the number of users converting from free to paid.

Appendix: Derivation of the CLTV Formula

In a retention decay model, the probability that a customer remains subscribed at any time t (where t is in months, for example) is given by an exponential decay function:

R(t) = e^{-\text{Churn Rate} \times t}

This describes how many customers remain at time t, given a constant churn rate.

Let’s assume that the business earns a fixed Average Revenue per User (ARPU) per month. Over time, the total revenue from a customer who stays until time t is

\text{Revenue at time } t = \text{ARPU} \times e^{-\text{Churn Rate} \times t}

To calculate the total expected revenue (CLTV) over a customer’s lifetime, we need to sum (integrate) the revenue contribution from time t=0 to infinity (as we assume the time horizon for the customer relationship to be infinite in this model). Thus, we integrate the revenue flow over time:

\text{CLTV} = \int_0^\infty \text{ARPU} \times e^{-\text{Churn Rate} \times t} \, dt

which gives

\text{CLTV} = \text{ARPU} \times \frac{1}{\text{Churn Rate}}

\text{Churn Rate} = \left( \frac{\text{Number of Churned Customers} + \text{Number of Inactive Customers}}{\text{Total Number of Paid Customers}} \right) \times 100

\text{MRR} = \text{Number of Paying Users} \times \text{Subscription Fee Per User}

\text{ARPU} = \frac{\text{Total Monthly Recurring Revenue (MRR)}}{\text{Total Number of Active Users (Free and Paid)}}

\text{CLTV} = \frac{\text{Average Revenue per User (ARPU)}}{\text{Churn Rate}}

\text{Conversion Rate} = \left( \frac{\text{Number of Users Who Convert to Paid}}{\text{Total Signups}} \right) \times 100

\text{Estimated DAU} = \frac{\text{MAU}}{\text{Number of Days in the Month}}

R(t) = e^{-\text{Churn Rate} \times t}

\text{Revenue at time } t = \text{ARPU} \times e^{-\text{Churn Rate} \times t}

\text{CLTV} = \int_0^\infty \text{ARPU} \times e^{-\text{Churn Rate} \times t} \, dt

\text{CLTV} = \text{ARPU} \times \frac{1}{\text{Churn Rate}}