8weeksqlchallenge - Week3

Case Study #3 - Foodie-Fi



Introduction

Foodie-Fi is a subscription-based streaming service for foody content. With this case study, we have chance to understand the metrics relating subscription business industry.


Case Study Insights

The one most important thing of subscription business is understand of customer’s behavior by answering questions relating to the fluctuations in customer decisions. This scenario gives us statistics that owner concern about and hope that these numbers can support the owner making right decision with a full picture with every perspective of business.

Here are the key points:

  • With Part A Customer Journey and Question 1, 2,3 can give an overall picture of Foodie-Fi Business.
  • Question 4,5 is about churning metrics.
  • Question 6,7,8 breaks down data to clear picture of every customer segment.
  • Question 9,10 shows us about customer retention, renewal metric.


    • These are some other key metrics that you can refer with a subscription business.

  • 6 Key Metrics for a Subscription Business
  • 14 essential subscription KPIs you need to measure


    • SQL code

    The techniques that you can learn from this case study :

  • JOINS SQL : 5 Best Practices for Writing SQL JOINS
  • Aggregate Functions : COUNT(), AVG() and SUM() Functions
  • Window Functions : How to use Window Functions
  • CTE : What Is a Common Table Expression (CTE) in SQL?
  • Date Functions : Date and time data types and functions
  • String Functions : Concat() Function SQL Server


    • Let’s get through some questions of this study case to review of technique.

    A. Customer Journey

  • For a overall picture of business, we can 2 more columns (days_diff, month_diff) to illustrate how customers change their subscription by days and month.
  • In this question, we also apply the Lag Function (a window function) that support you to compare data of current row to previous row order by appropriate conditions.
  • Using DATEDIFF functions to calculate the day different between start_date and next date when customer buy a new subscription.

    • Click for code

    												
                                                    SELECT s.customer_id,
                                                    p.plan_name,
                                                    s.start_date,
                                                    DATEDIFF(day, LAG(start_date) OVER (PARTITION BY customer_id ORDER BY start_date),start_date ) AS days_diff,
                                                    DATEDIFF(month,LAG(start_date) OVER (PARTITION BY customer_id ORDER BY start_date),start_date) as months_diff
                                             FROM   subscriptions_demo AS s
                                             JOIN   plans AS p
                                             ON     s.plan_id = p.plan_id

    B.Data Analysis Questions
    Question 6. What is the number and percentage of customer plans after their initial free trial?
    They key phrases here is “after initial free trial”.

  • So We create next_plan column with LEAD() method to find the current plan. If theres is not next plan, result would be NULLstrong.
  • One thing to consider is setting correctly for WHERE condition. I set “plan_id = 0” and “next_plan is not Null” to make sure I only select customer who booked a new subscription after free trial.

    • Click for code

    												
                                                    WITH CTE AS(
                                                        SELECT *, 
                                                        LEAD(plan_id,1) OVER( PARTITION BY customer_id ORDER BY plan_id) As next_plan
                                                        FROM subscriptions
                                                                            ) 
                                                        
                                                        SELECT plan_name, count(*) as num_plan, Cast(count(next_plan) as float) * 100 / (select count(distinct customer_id) from subscriptions) as perc_next_plan
                                                        FROM CTE c 
                                                        LEFT JOIN plans p ON c.next_plan = p.plan_id
                                                        WHERE  c.plan_id = 0 and next_plan is not NULL
                                                        GROUP BY plan_name,next_plan;


    We have 3 PARTS with 13 questions. You can access my github link for full code and explanation

    My Github