Rockbuster Stealth LLC
To remain competitive, Rockbuster Stealth, a brick and mortar movie rental store, is using its remaining movie licenses to launch an online video rental service. Rockbuster management has requested help with the launch planning.
Analysis of movie revenue and trends, movie performance, and customer preferences will address management’s questions and provide any other insights that can be discovered.
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?
Tools used
PostgreSQL
Excel
DBeaver
Tableau
Powerpoint
Skills required
Exploring relational databases
Understanding and using the ERD
Database querying with SQL
Filtering, cleaning, and summarizing
Joining tables
Using subqueries and Common Table Expressions (CTE)
Tableau visualizations
Data available
Initial Exploratory Analysis
Working to understand the tables, data available, and relationships between tables
Explore the ERD for an overall understanding of the database
Look at the content of tables that might be used
Investigate certain data points for usability in the analysis
Data Checks and Cleaning
Testing the data integrity and cleaning the data as needed
The database proved to be clean and usable and I was able to proceed to analysis.
Check for NULL values that might influence our findings
Confirm we don’t have duplicate rows that should be deleted
Summarize the data for a logic check against issues like outliers
Analysis
Provide a full analysis of the data to address Rockbuster management’s questions and uncover any other insights possible
Revenue by Movie Rating
Rating should be considered during movie selection.
PG-13 is Rockbuster’s highest grossing rating followed by NC-17.
G rated movies produce the least revenue.
What to do differently
I selected a pie chart due to the limited number of ratings. Unfortunately, it is difficult to read quickly. A different presentation might provide a better view.
Product Group Popularity by Region
Top 10 Countries with Rockbuster Customers
India and China lead in customer count
United States, Japan, and Mexico follow close behind
Top cities within these countries (alpha order)
Brazil - So Leopoldo
China - Shanwei
China - Tianjin
India - Ambattur
Indonesia - Cianjur
Japan - Iwaki
Mexico - Acua
Russian Federation - Teboksary
United States - Aurora
United States - Citrus Heights
Revenue by Movie Category
Sports, Sci-Fi, and Animation lead in revenue.
Music, Travel, and Thriller categories could be considered to eliminate.
Opportunities
Eliminating certain categories was done to address the limited movie licenses that Rockbuster has available. A second phase of movie selections should be done after initial launch using updated performance data to plan for new movies to add to the existing titles.
Understanding our most loyal customers and their locations provides focus for marketing, customer rewards programs, and sets expectations regarding market penetration.
Rockbuster Final Report and Recommendations
Movie Selection for Launch
Secure additional licensing for top revenue producing movies; known high revenue movies should be promoted in all regions
Review and revise rental plan and eliminate late fees
Establish either a ‘per rental’ OR ‘per view’ fee structure
Emphasis should be given to:
PG-13, NC-17, and PG rated movies
Sports, Sci-Fi, and Animation categories
To minimize licensing fees and give focus to high revenue rentals reduce emphasis on R and G rated movies and eliminate Music, Travel, and Thriller categories
Countries and Customers for Launch
Support countries with a solid customer base: India, China, United States, Japan, Mexico are top 5 in revenue
Bolster marketing in other top 10 countries: Russian Federation, Brazil, Philippines, Turkey, Indonesia
Implement loyalty programs to acquire new and retain existing customers
Post-launch, assess other countries within the top revenue regions for additional marketing campaigns: Asia, Europe, North America