This case study is contained within the Serious SQL by Danny Ma. With this Marketing Analytics Case Study, I was asked to support the customer analytics team at DVD Rental Co who have been tasked with generating the necessary data points required to populate specific parts of this first-ever customer email campaign.
- 🛠️ Requirements
- 📂 Data Overview
- 🚀 Solution
- 🧙♂️ Result
- 🐋 Bonus Section
The marketing team have shared with me a draft of the email they wish to send to their customers:
Click to view
For each customer, I need to identify the top 2 categories each customer based off their past rental history.
Click to view
The marketing team has also requested for the 3 most popular films for each customer’s top 2 categories.
Click to view
The number of films watched by each customer in their top 2 categories is required as well as some specific insights.
For the 1st category, the marketing requires the following insights (requirement 3):
- How many total films have they watched in their top category?
- How many more films has the customer watched compared to the average DVD Rental Co customer?
- How does the customer rank in terms of the top X% compared to all other customers in this film category?
For the second ranking category (requirement 4):
- How many total films has the customer watched in this category?
- What proportion of each customer’s total films watched does this count make?
Click to view
Along with the top 2 categories, marketing has also requested top actor film recommendations where up to 3 more films are included in the recommendations list as well as the count of films by the top actor.
In this project, I have a total of 7 tables in our ERD (Entity Relationship Diagram), highlighting the important columns which I should use to join my tables for the data analysis task.
Therefore, the first section will cover the data inspection process of these tables in order to find out the best JOIN type that will be the most suitable for the later problem solving stage.
Now that I’ve identified the key columns and highlighted some things I need to keep in mind when performing some table joins for my data analysis - next exciting step is to join them together.
Finally, after I’ve combined all of different datasets together into a single base table which I can use for our insights, this section will aim to cover those core calculated fields which I broke down in the first Key Business Requirements section of this case study.
Assume this email template will be sent to a customer with customer_id = 1, I will first go back to the requirements of the marketing team and by that, answer each question one by one regarding this customer's scenario.
Requirement 1: Top 2 Categories
| customer_id | category_name | rental_count | category_rank |
|---|---|---|---|
| 1 | Classics | 6 | 1 |
| 1 | Comedy | 5 | 2 |
Requirement 2: Category Film Recommendations
| customer_id | category_name | category_rank | film_id | title | rental_count | reco_rank |
|---|---|---|---|---|---|---|
| 1 | Classics | 1 | 891 | TIMBERLAND SKY | 31 | 1 |
| 1 | Classics | 1 | 358 | TIMBERLAND SKY | 28 | 2 |
| 1 | Classics | 1 | 951 | VOYAGE LEGALLY | 28 | 3 |
| 1 | Comedy | 2 | 1000 | ZORRO ARK | 31 | 1 |
| 1 | Comedy | 2 | 127 | CAT CONEHEADS | 30 | 2 |
| 1 | Comedy | 2 | 638 | OPERATION OPERATION | 27 | 3 |
Requirement 3 & 4: Individual Customer Insights
FIRST CATEGORY INSIGHTS
| customer_id | category_name | rental_count | average_comparison | percentile |
|---|---|---|---|---|
| 1 | Classics | 6 | 4 | 1 |
SECOND CATEGORY INSIGHTS
| customer_id | category_name | rental_count | category_percentage |
|---|---|---|---|
| 1 | Comedy | 5 | 16 |
Requirement 5: Favorite Actor Recommendations
Result:
| customer_id | first_name | last_name | rental_count | title | film_id | actor_id | reco_rank |
|---|---|---|---|---|---|---|---|
| 1 | VAL | BOLGER | 6 | PRIMARY GLASS | 697 | 37 | 1 |
| 1 | VAL | BOLGER | 6 | ALASKA PHANTOM | 12 | 37 | 2 |
| 1 | VAL | BOLGER | 6 | METROPOLIS COMA | 572 | 37 | 3 |
Hooray! Finally, this is what out final input looks like:
Contributions, issues, and feature requests are welcome!
To contribute to this project, see the GitHub documentation on creating a pull request.
Give a ⭐️ if you like this project!
© 2021 Leah Nguyen