Rockbuster Stealth LLC, a video store, faces stiff competition from streaming services and plans to use its existing movie licenses to launch an online video rental service to stay competitive.
The results of this analysis and the recommendations given will determine the movies and regions to focus on when launching the online platform. They will also positively impact cost effectiveness and customer retention.
Key Questions:
- Which movies contributed most/least to revenue gain?
- Which countries are Rockbuster customers based in?
- Where are customers with a high lifetime value based?
- Do sales figures vary between geographical regions?
Project Overview
Data
•The Rockbuster Data Set was provided in the project brief.
•The dataset is fictional.
•This relational database includes information on Rockbuster’s film inventory, locations, customers, rentals, and payments.
Tools Used
Analysis
In SQL
•Addressed data anomalies and missing values and ensured data integrity.
•Augmented datasets by incorporating additional data, while enforcing data integrity through the application of constraints.
•Represented the structure of relational databases using an Entity Relationship Diagram (ERD).
•Executed queries to retrieve and analyze data from relational databases, including analytical functions and statistical operations.
•Derived new data elements by employing calculated fields, expressions, and transformations, enhancing the analytical depth of the dataset.
•Created a data dictionary, documenting data definitions.
•Utilized advanced operations such as joins, subqueries, and Common Table Expressions (CTEs) to merge data from multiple tables and perform nested queries.
Project Links
Project Data Analysis
ERD, Cleaning, & Summary Stats
1
To begin analyzing the data, first an understanding of the relationships between the tables in the database was needed, and an entity relationship diagram was created in DbVisualizer.
2
Next the data was cleaned in SQL for duplicates, missing data, or inconsistent data.
3
Then, summary statistics were calculated to understand a basic overview of the data.
Top Countries and Customers
4
Outputs from various SQL queries were used to create Tableau visuals to further understand the data. Most revenue is coming from Asia, North America, and South America. India and China are by far the frontrunners.
5
To determine how many of our top 5 customers were based within each country, joins and subqueries were used.
Movies and Revenue
Each of the top 10 revenue-generating movies
has a rental rate of $4.99.
However, the highest volume of movies
rented for $0.99.
Rockbuster Recommendations
Results
- Rockbuster should increase the rental rates of some of the most popular movie titles in the most popular genres that are currently renting for $0.99.
- Rockbuster should continue to keep a good stock of movies to rent at the customer-preferred rate of $0.99.
- Focus on the top 10 revenue-generating countries when marketing the online platform.
- Create a rewards program for high-value customers.
- Next Step: Compare each movie’s licensing costs against the revenue it brings in to determine which movies should be removed from inventory.
Kay Stonecypher 