Data Analysis for Rockbuster Stealth
Optimizing Business Operations for Rockbuster Stealth LLC
Introduction
Rockbuster Stealth LLC is a movie rental company that once operated physical stores globally. Due to increasing competition from streaming services like Netflix and Amazon Prime, Rockbuster is transitioning to an online video rental service. As a data analyst in the Business Intelligence (BI) department, I was tasked with analyzing the company’s data to inform the launch strategy for this new service.
Key Questions & Objectives
The Rockbuster Stealth Management Board outlined several critical questions to guide their 2020 strategy:
Identify the movies that contributed the most and least to revenue gain.
Determine the average rental duration for all videos.
Analyze the geographic distribution of Rockbuster customers.
Identify customer segments with a high lifetime value.
Assess sales performance across different geographic regions.
Data & Tools
For this analysis, Rockbuster's relational database management system and Postgre SQL were utilized. The dataset encompasses comprehensive information concerning Rockbuster’s film inventory, customers, payments, stores, staff, films, actors, rental history, and addresses.
Download the Rockbuster data set (To access the data set It is necessary to load the data set into the PostgreSQL database.)
Tools Used: PostgreSQL, Tableau
Approach and Process
Setting Up the Database:
Loaded the Rockbuster dataset into PostgreSQL.
Created an entity-relationship diagram to understand table relationships.
Data Cleaning:
Ran queries to identify and handle missing values and duplicates.
Cleaned data to ensure accuracy and consistency.
Exploratory Data Analysis:
Used SQL for descriptive statistics and initial data profiling.
Identified key metrics and trends.
Data Analysis
Revenue Analysis:
Identified top- and bottom-performing movies in terms of revenue.
Analyzed genres and ratings contributing to revenue.
Rental Duration:
Calculated the average rental duration for all videos.
Customer Geography:
Analyzed the geographic distribution of customers.
Determined regions with the highest customer density and revenue.
Customer Segmentation:
Identified high-lifetime-value customer segments.
Sales Performance:
Assessed sales performance across different geographic regions.
Visualization:
Created visualizations using Tableau to present findings to stakeholders.
Data Exploration and Querying
Entity Relationship Diagram (ERD): Mapped out relationships to streamline query processes.
Basic Queries: Cleaned and prepared data.
Join Statements: Connected multiple tables to gather comprehensive insights.
Complex Queries: Used nested statements and CTEs for in-depth analysis.
Challenges and Solutions
Data Complexity:
Managed integration from multiple tables, ensuring integrity.
Solution: Regular consistency checks and data validation.
Query Optimization:
Ensured efficiency and accuracy.
Solution: Refined queries used indexing and optimized joins.
Conclusions and Recommendations
Revenue Insights:
Movies with 'PG-13' or 'NC-17' ratings generated higher revenue.
Top earning genres: Sports, Sci-Fi, Animation, Drama, and comedy.
Lowest earning genres: Music, Travel, Thriller.
Geographic Distribution:
43% of customers and revenue are from Asia; Europe follows with 20%.
Top countries by revenue: United States, Mexico, Brazil, India, and China.
Recommendations:
Focus marketing on top revenue-generating markets (India, China, and the USA).
Emphasize popular genres and ratings specific to each market.
Consider price adjustments for high-demand movies and genres.
Develop loyalty programs for high-value customers.
Final Thoughts
This project underscored the importance of leveraging data analytics to guide strategic decisions. The insights gained provide a solid foundation for Rockbuster Stealth’s transition to an online video rental service, ensuring a data-driven approach to compete in the digital market.