Rockbuster Analysis using SQL & Tableau
Rockbuster Stealth LLC, a global movie rental company, is launching an online video rental service to compete with streaming giants like Netflix and Amazon Prime. The goal of this analysis is to provide insights that will help optimize operational efficiency and develop a strategic plan to target high-revenue regions and customer segments.
-
Goals
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.
-
Skills & Tools
Relational databases
SQL
Database querying
Filtering
Cleaning and summarizing
Joining tables
Subqueries
Common table expressions
-
Key Focus Areas
Top-performing and low-performing movies by revenue.
Customer locations and their lifetime value.
Sales performance across different geographic regions.
Genre-based revenue insights.
Which movies contributed most/least to revenue gain?
Top Performing Genres
Sports | Total Revenue: $4,892
Sci-Fi | Total Revenue: $4,336
Animation | Total Revenue: $4,245
Lowest Performing Genre
Thriller | Total Revenue: $47
Where are the majority of Rockbusters customers?
Rockbuster customers span across the world, but the majority of customers can be found in areas such as: India, China, and the United States.
Where are customers with a high lifetime value based?
Reunion
Eleanor Hunt
$211
Each of the top 5 customers for Rockbuster spent over $120 in movie rentals.
Do sales figures vary between geographic regions?
Asia, China, and the United States are the top performing geographic regions, where Asia accounts for almost half of Rockbusters entire revenue.
Belarus
Clara Shaw
$189
Turkey
Casey Mena
$130
Mexico
Sara Perry
$128
Indonesia
Leslie Seward
$123
-
Data Limitations
The first step involved setting up a comprehensive relational database with 15 tables covering different aspects of Rockbuster's operations, including customers, payments, films, and rentals.
A data dictionary was created to ensure clarity around the structure of the data.
-
Challenges
The key challenge was managing complex SQL joins across multiple tables to extract the necessary insights:
Joins & Subqueries: Joins were used to combine customer records with film and payment data, while subqueries helped drill down into specific regions and customer groups to answer business-critical questions.
Cleaning Data: The dataset initially had missing variables that were necessary for visualization. After identifying this issue, the data was cleaned and refined again in Excel before proceeding with further analysis.
-
Recommendations
Focus Marketing Efforts on high-revenue regions like India, China, and the U.S.
Implement a Customer Loyalty Program to incentivize high-value customers through rewards and referrals.
Expand Popular Genres such as Sports, Sci-Fi, and Animation to maintain engagement and drive more rentals.