Rockbuster DVD Video Rental - SQL Project
Optimizing Business operations through SQL analysis
Background
Rockbuster Stealth LLC is a movie rental company that used to have stores around the world. Facing stiff competition from streaming services such as Netflix and Amazon Prime, the Rockbuster Stealth management team is planning to use its existing movie licenses to launch an online video rental service in order to stay competitive.
Key Questions and Objectives
Which movies contributed the most/least to revenue gain?
What was the average rental duration for all videos?
Which countries are Rockbuster customers based in?
Where are customers with a high lifetime value based?
Do sales figures vary between geographic regions?
Skills
Relational databases
SQL
Database querying, filtering, cleaning & summarizing
Subqueries
Common table expression
Tools
PowerPoint
SQL
Tableau
Excel
Data Sets
The database used for this project can be found in the following link.
Steps for Data Analysis:
Description
Step
Create entity relationships diagram(ERD) and dictionary to clearly show the information and relationships between the data table.
1
Use SQL to clean, query, filter and summarize data.
2
Create visualization of SQL results and create a presentation of finding.
3
Data Exploration and Querying
1. Database Exploration
Create a data dictionary that clearly displays the contents of each dataset and their connections.
2. Data Querying
Data aggregation
Cleaning Data
Joins
Subqueries
Common Table Expressions
Views
Data Visualization
Use SQL to filter and select the top 10 and bottom 10 movies, then display them using Tableau.
Use SQL to filter and calculate the top 10 countries with high revenue and customer numbers, then use pie charts to display their revenue and customer proportions of total.
Use SQL to calculate the Average Rental Duration by category and display it using a bar chart.
Use SQL to filter the top 10 highest revenue-generating customers and display their distribution using a map.
Recommendation
Product Level
Focus on promoting the top ten revenue-generating movies: Given that these movies have already proven their market appeal, the company should consider further promoting these films through various marketing activities and special offers to increase user engagement and revenue.
Examine rental duration: The average rental duration is 5 days, while the top ten movies have an average rental duration of 3.5 days. Consider adjusting pricing strategies or introducing short-term rental promotions for these high-demand movies.
Region Level
Focus on the top ten countries' markets: The customer numbers and cumulative revenue from these countries already account for more than half of the total. The company should analyze the specific needs and preferences of these markets in depth and tailor marketing strategies to further improve customer satisfaction and loyalty.
Customer Level
Personalized recommendations and services: Using the data from the top 10 users with the highest cumulative payments, the company can develop personalized recommendation systems and custom services to enhance the satisfaction and retention rates of these users.