This case study can be found in the Serious SQL course made by Danny Ma.
- 💿About
- 📋 Requirements
- 🔎 Data Exploration
- ⚙️ Problem Approach
- 🧱 Join Implementation
- 🔧 Problem Solving
- 🔮 Final Output
The DVD Rental Co customer analytics asked for our support. The team needed to generate the necessary data points required to populate specific parts of this first-ever customer email campaign.
The marketing team shared with us an email template of what they wanted to send to their customers.
The top 2 categories for each customer needs to be identified based off their past rental history.
The marketing team need to recommend the 3 most popular films for each customer's top 2 categories.
❗️ A film can not be recommended if the customer has already watched it.
❗️ If the customer does not have any film recommendations for either category, the customer must be excluded from the email campaign.
For the requirement #3, the following insights are needed on the first category:
1. How mny total films have they watched in their top category?
2. How many more films has the customer watched compared to the average DVD Rental Co customer?
3. How does the customer rank in terms of the top X% compared to all other customers in this film category?
For the requirement #4, the following insights are needed on the second category:
1. How many total films has the customer watched in this category?
2. What proportion of each customers's total films watched does the count make?
Marketing has also requested for the 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.
❗️ If there are any ties the actors should be choosen in alphabetical order.
❗️ Any films that have already been recommended in the top 2 categories must not be included as actor recommendations.
❗️ If the customer does not have at least 1 film recommendation - they also need to be flagged with a separate actor exclusion flag.
The analytics team provided an entity-relationship diagram (ERD).
The 7 tables from the ERD were explored in the link below.
In this section the problem approach was shown and the key columns from the ERD were identified for proceeding with the table joins.
In this section the approach on doing the joins is shown and also how the base tables for the category and actor insights were generated.
There are two parts, the first part shows the base table for the category insights while the second part shows the base table for the actor insights.
In this section all of the requirements outputs were generated using the base tables previously made in the join implementations.
In this final section all of the individual outputs previously made in the problem solving section were combined into one dataset so that the marketing team can have access to all the requierements from the email template in one place.
