August, 2023
SQL Case Study:
Foodie-Fi
SQL
Business Sense
Ad-hoc Analysis
I did a Case Study on a Foodie-Fi data using SQL-queries. I will be diving into the background, my full process of creating the database and using queries to find answers from the data.
Tools Used: MySQL Workbench | MySQL
Quick Links: GitHub Repository
Below is a table of contents in case you want to go to any section.
Table Contents:
- Introduction
- Data Dictionary
- Process
- MySQL
- Misc.
- Case Study
- Customer Journey
- Data Analysis
- Finished Project
- Overview of Data
- Insights
- Recommendations
- What I Learned
- Resources
INTRODUCTION
Subscription based businesses are super popular and Danny realised that there was a large gap in the market - he wanted to create a new streaming service that only had food related content - something like Netflix but with only cooking shows!
Danny finds a few smart friends to launch his new startup Foodie-Fi in 2020 and started selling monthly and annual subscriptions, giving their customers unlimited on-demand access to exclusive food videos from around the world!
Danny created Foodie-Fi with a data driven mindset and wanted to ensure all future investment decisions and new features were decided using data.
This case study focuses on using Foodie-Fi data; a subscription style digital data to answer important business questions that could help the startup have an insight of critical business metrics.
Overall Goal: Generate insights relating to customer journey, payment transactions, and overall business performance.
DATA DICTONARY
Entity Relationship Diagram
1. Plans
Customers can choose which plans to join Foodie-Fi when they first sign up.
Basic plan customers have limited access and can only stream their videos and is only available monthly at $9.90.
Pro plan customers have no watch time limits and are able to download videos for offline viewing. Pro plans start at $19.90 a month or $199 for an annual subscription.
Customers can sign up to an initial 7 day free trial will automatically continue with the pro monthly subscription plan unless they cancel, downgrade to basic or upgrade to an annual pro plan at any point during the trial.
When customers cancel their Foodie-Fi service - they will have a churn plan record with a null price but their plan will continue until the end of the billing period.
Table Schema
```markdown
|Column Name|Data Type|Description |
|-----------|---------|-------------------------|
|plan_id |INTEGER |A unique ID for each Plan|
|plan_name |VARCHAR |Name of the Plan |
|price |FLOAT |Price of the Plan |
Sample Data
```markdown
|plan_id|plan_name |price |
|-------|-------------|------|
|0 |trial |0.00 |
|1 |basic monthly|9.90 |
|2 |pro monthly |19.90 |
|3 |pro annual |199.00|
|4 |churn |null |
2. Subscriptions
Customer subscriptions show the exact date where their specific plan_id starts.
If customers downgrade from a pro plan or cancel their subscription - the higher plan will remain in place until the period is over - the start_date in the subscriptions table will reflect the date that the actual plan changes.
When customers upgrade their account from a basic plan to a pro or annual pro plan - the higher plan will take effect straightaway.
When customers churn - they will keep their access until the end of their current billing period but the start_date will be technically the day they decided to cancel their service.
Table Schema
```markdown
|Column Name|Data Type|Description |
|-----------|---------|---------------------------------|
|customer_id|INTEGER |A unique ID for each Customer |
|plan_id |INTEGER |An ID of Plan (can be duplicates)|
|start_date |DATE |Date when the plan starts |
Sample Data
```markdown
|customer_id|plan_id|start_date|
|-----------|-------|----------|
|1 |0 |2020-08-01|
|1 |1 |2020-08-08|
|2 |0 |2020-09-20|
|2 |3 |2020-09-27|
|3 |0 |2020-01-13|
Process
Overview: I used MySQL Workbench (GUI of MySQL) to create a database and to find anwers using queries.
MySQL
First I created the database using ERD and at the same time I familiarized myself with the data. Then connected the tables using Primary and Foreign Key constraints. And then used my quering skills to find answers.
MISC.
Microsoft Word
Notes - Notes for the project including the answer of queries, what I was looking for, and anything else that has to do with the project.
CASE STUDY
This case study is split into an initial data understanding question before diving straight into data analysis questions before finishing with 1 single extension challenge.
Customer Journey
Based off the 8 sample customers (1,2,11,13,15,16,18,19) from the subscriptions table, write a brief description about each customer’s onboarding journey.
view query
```sql
SELECT customer_id, plans.plan_name, start_date
FROM subscriptions
JOIN plans ON plans.plan_id = subscriptions.plan_id
WHERE customer_id IN (1,2,11,13,15,16,18,19)
ORDER BY 1;
view data
```markdown
|customer_id|plan_name |start_date|
|-----------|-------------|----------|
|1 |trial |2020-08-01|
|1 |basic monthly|2020-08-08|
|2 |trial |2020-09-20|
|2 |pro annual |2020-09-27|
|11 |trial |2020-11-19|
|11 |churn |2020-11-26|
|13 |trial |2020-12-15|
|13 |basic monthly|2020-12-22|
|13 |pro monthly |2021-03-29|
|15 |trial |2020-03-17|
|15 |pro monthly |2020-03-24|
|15 |churn |2020-04-29|
|16 |trial |2020-05-31|
|16 |basic monthly|2020-06-07|
|16 |pro annual |2020-10-21|
|18 |trial |2020-07-06|
|18 |pro monthly |2020-07-13|
|19 |trial |2020-06-22|
|19 |pro monthly |2020-06-29|
|19 |pro annual |2020-08-29|
Customer 1 starts with a free trial plan on 2020-08-01 and when the trial ends, upgrades to basic monthly plan on 2020-08-08.
Customer 2 starts with a free trial plan on 2020-09-20 and when the trial ends, upgrades to pro annual plan on 2020-09-27.
Customer 11 starts with free trial plan on 2020-11-19 and churns at the end of the free trial plan on 2020-11-26.
Customer 13 starts with free trial plan on 2020-15-12 and when the trial ends subscribes to a basic monthly plan on the 2020-12-22, and 3 months later upgrades to a pro monthly plan on 2021-03-29.
Customer 15 starts with a free trial plan on 2020-03-17, and when the trail ends automatically upgrades to the pro monthly plan on 2020-03-24 and then churns one month later on 2020-04-29.
Customer 16 starts with a free trial plan on 2020-05-31, and when the trial ends, subscribes to a basic monthly plan on 2020-06-07 and 4 months later upgrades to a pro annual plan on 2020-10-21.
Customer 18 starts with a free trial plan on 2020-07-06 and when the trial ends, automatically upgrades to pro monthly plan on the 2020-07-13.
Customer 19 starts with a free trial plan on 2020-06-22, automatically ugrades to pro monthly on 2020-06-29, and 2 months later upgrades to pro annual plan on 2020-08-29.
Data Analysis
1. How many customers has Foodie-Fi ever had?
view query
```sql
SELECT COUNT(DISTINCT(customer_id)) AS unique_customers
FROM subscriptions;
view data
```markdown
|unique_customers|
|----------------|
|1000 |
2. What is the monthly distribution of trial plan start_date values for our dataset - use the start of the month as the group by value?
view query
```sql
SELECT MONTHNAME(start_date) AS month,
COUNT(*) AS total_plans
FROM subscriptions
JOIN plans ON plans.plan_id = subscriptions.plan_id
WHERE plan_name = 'trial'
GROUP BY 1
ORDER BY 2 DESC;
view data
```markdown
|month |total_plans|
|---------|-----------|
|March |94 |
|July |89 |
|August |88 |
|January |88 |
|May |88 |
|September|87 |
|December |84 |
|April |81 |
|June |79 |
|October |79 |
|November |75 |
|February |68 |
3. Which plan's start_date values occur after the year 2020 for our dataset? Show the breakdown by a count of events for each plan_name.
view query
```sql
SELECT plan_name, COUNT(*) AS event_2021
FROM plans
JOIN subscriptions ON plans.plan_id = subscriptions.plan_id
WHERE start_date > '2020-12-31'
GROUP BY 1;
view data
```markdown
|plan_name |event_2021|
|-------------|----------|
|churn |71 |
|pro monthly |60 |
|pro annual |63 |
|basic monthly|8 |
4. What is the customer count and percentage of customers who have churned rounded to 1 decimal place?
view query
```sql
WITH churn_count AS (
SELECT COUNT(*) as churned
FROM subscriptions
JOIN plans ON plans.plan_id = subscriptions.plan_id
WHERE plan_name = 'churn'
)
SELECT churned, CONCAT(ROUND((100 * churned / unique_customers), 1), '%') AS churn_rate
FROM churn_count, (SELECT COUNT(DISTINCT(customer_id)) AS unique_customers
FROM subscriptions) AS total;
view data
```markdown
|churned|churn_rate|
|-------|----------|
|307 |30.7% |
5. How many customers have churned straight after their initial free trial — what's the percentage rounded to the nearest whole number?
view query
```sql
WITH previous_plan_cte AS (
SELECT *, LAG(plan_id) OVER(PARTITION BY customer_id ORDER BY plan_id) AS previous_plan
FROM subscriptions
)
SELECT COUNT(*) AS churned,
CONCAT(ROUND(100 * COUNT(*) / unique_customers), '%') AS churn_rate
FROM previous_plan_cte,
(SELECT COUNT(DISTINCT(customer_id)) AS unique_customers
FROM subscriptions) AS total
WHERE plan_id = 4 AND previous_plan = 0;
view data
```markdown
|churned|churn_rate|
|-------|----------|
|92 |9% |
6. What is the number and percentage of customer choosing plans after their initial free trial?
view query
```sql
WITH next_plan_cte AS (
SELECT *,
LEAD(plan_id) OVER(PARTITION BY customer_id ORDER BY plan_id) AS next_plan
FROM subscriptions
),
customer_count AS (
SELECT plan_name, COUNT(*) AS customers
FROM next_plan_cte
JOIN plans ON plans.plan_id = next_plan_cte.next_plan
WHERE next_plan_cte.plan_id = 0
GROUP BY next_plan
)
SELECT plan_name, customers,
CONCAT(ROUND((100 * customers / unique_customers), 1), '%') AS percentage
FROM customer_count,
(SELECT COUNT(DISTINCT(customer_id)) AS unique_customers
FROM subscriptions) AS total;
view data
```markdown
|plan_name |customers|percentage|
|-------------|---------|----------|
|basic monthly|546 |54.6% |
|pro monthly |325 |32.5% |
|pro annual |37 |3.7% |
|churn |92 |9.2% |
7.What is the customer count and percentage breakdown of all 5 plan_name values at 2020-12-31?
view query
```sql
WITH next_date_cte AS (
SELECT *,
LEAD(start_date) OVER(PARTITION BY customer_id ORDER BY start_date) AS next_date
FROM subscriptions
WHERE start_date <= '2020-12-31'
),
customer_count AS (
SELECT plan_name, COUNT(*) AS customers
FROM next_date_cte
JOIN plans ON plans.plan_id = next_date_cte.plan_id
WHERE next_date IS NULL
GROUP BY 1
)
SELECT plan_name, customers,
CONCAT(ROUND((100 * customers / unique_customers), 1), '%') AS percentage
FROM customer_count,
(SELECT COUNT(DISTINCT(customer_id)) AS unique_customers
FROM subscriptions) AS total;
view data
```markdown
|plan_name |customers|percentage|
|-------------|---------|----------|
|trial |19 |1.9% |
|basic monthly|224 |22.4% |
|pro monthly |326 |32.6% |
|pro annual |195 |19.5% |
|churn |236 |23.6% |
8. How many customers have upgraded to an annual plan in 2020?
view query
```sql
SELECT COUNT(DISTINCT customer_id) AS customers
FROM subscriptions
JOIN plans ON plans.plan_id = subscriptions.plan_id
WHERE plan_name = 'pro annual'
AND YEAR(start_date) <=2020;
view data
```markdown
|customers|
|---------|
|195 |
9. How many days on average does it take for a customer to an annual plan from the day they join Foodie-Fi?
view query
```sql
WITH trial_plan AS (
SELECT customer_id, start_date AS trial_date
FROM subscriptions
JOIN plans ON plans.plan_id = subscriptions.plan_id
WHERE plan_name = 'trial'
),
annual_plan AS (
SELECT customer_id, start_date AS annual_date
FROM subscriptions
JOIN plans ON plans.plan_id = subscriptions.plan_id
WHERE plan_name = 'pro annual'
)
SELECT ROUND(AVG(DATEDIFF(annual_date, trial_date))) AS avg_days_to_upgrade
FROM trial_plan
JOIN annual_plan ON trial_plan.customer_id = annual_plan.customer_id;
view data
```markdown
|avg_days_to_upgrade|
|-------------------|
|105 |
10. Can you further breakdown this average value into 30 day periods (i.e. 0-30 days, 31-60 days etc)
view query
```sql
WITH trial_plan AS (
SELECT customer_id, start_date AS trial_date
FROM subscriptions
JOIN plans ON plans.plan_id = subscriptions.plan_id
WHERE plan_name = 'trial'
),
annual_plan AS (
SELECT customer_id, start_date AS annual_date
FROM subscriptions
JOIN plans ON plans.plan_id = subscriptions.plan_id
WHERE plan_name = 'pro annual'
)
SELECT
CONCAT(FLOOR(DATEDIFF(annual_date, trial_date) / 30) * 30, '-',
FLOOR(DATEDIFF(annual_date, trial_date) / 30) * 30 + 30, ' days') AS period,
COUNT(*) AS total_customers,
ROUND(AVG(DATEDIFF(annual_date, trial_date)), 0) AS avg_days_to_upgrade
FROM trial_plan tp
JOIN annual_plan ap ON tp.customer_id = ap.customer_id
WHERE ap.annual_date IS NOT NULL
GROUP BY FLOOR(DATEDIFF(annual_date, trial_date) / 30);
view data
```markdown
|period |total_customers|avg_days_to_upgrade|
|------------|---------------|-------------------|
|0-30 days |48 |10 |
|30-60 days |25 |42 |
|60-90 days |33 |71 |
|90-120 days |35 |100 |
|120-150 days|43 |133 |
|150-180 days|35 |162 |
|180-210 days|27 |190 |
|210-240 days|4 |224 |
|240-270 days|5 |257 |
|270-300 days|1 |285 |
|300-330 days|1 |327 |
|330-360 days|1 |346 |
The majority of customers opt to subscribe or upgrade to an annual plan within the first 30 days.
A smaller percentage of customers make the decision to subscribe or upgrade after 210 days.
After 270 days, there is almost no customer activity in terms of purchasing a plan.
11. How many customers downgraded from a pro monthly to a basic monthly plan in 2020?
view query
```sql
WITH plan_list AS (
SELECT customer_id,
sub.plan_id,
plan_name,
LEAD(sub.plan_id)
OVER(PARTITION BY customer_id ORDER BY start_date) AS next_plan_id
FROM subscriptions AS sub
JOIN plans ON plans.plan_id = sub.plan_id
WHERE YEAR(start_date) = 2020
)
SELECT COUNT(*) AS downgraded
FROM plan_list
WHERE plan_id = 2
AND next_plan_id = 1;
view data
```markdown
|downgraded|
|----------|
|0 |
FINISHED PROJECT
Here is my finished project: SQL Case Study: Foodie-Fi
Overview of Data
Data:
INSIGHTS
Below are the general insights:
RECOMMENDATIONS
WHAT I LEARNED
RESOURCES
This SQL Case Study was part of 8-week SQL Challenge by Danny, to view the challenge click here.