Rockbuster | Data-Driven Insights for an Online Video Rental Service
In the face of growing competition from streaming giants like Netflix and Amazon Prime, Rockbuster Stealth LLC, a global movie rental company, is planning a strategic shift from physical stores to an online rental platform. This article delves into a comprehensive data analysis, uncovering key insights about customer demographics, movie preferences, and revenue contributions.
5/8/20244 min read
Project Background
Rockbuster Stealth LLC is a movie rental company that used to have stores around the world. Facing stiff competition from the streamlining services such as Netflix and Amazon Primes, Rockbuster management is planning to use its existing movie licences to launch an online video rental service.
Key Questions and Objectives
The Rockbuster Stealth Management Board has posed a series of business questions, expecting data-driven insights to inform their 2020 company strategy. The key questions are:
What is the average rental duration for all videos?
Which countries are Rockbuster customers based in?
Which movies contributed the most or least to revenue?
Where are customers with a high lifetime value located?
Do sales figures vary between different geographic regions?
Tools
Techniques
Data Filtering and Cleaning: Ensuring the data used was accurate and relevant.
Table Joins: Combining multiple tables to provide a complete dataset.
Subqueries and Common Table Expressions (CTEs): To filter and analyze subsets of data.
Data Visualization: Spatial analysis, line and column charts, and treemaps to present insights clearly.
PostgreSQL: Query language used to retrieve data.
Lucidchart: For creating Entity-Relationship (ER) diagrams.
DbVisualizer: To visualize query results.
Database Schema
The provided ERD (Entity-Relationship Diagram) does not follow a typical star or snowflake schema. A star schema centers around a clear fact table, while a snowflake schema normalizes dimensions into sub-dimensions. Rockbuster's schema, however, appears to be customized and doesn’t align with either traditional design. It reflects a more tailored approach to managing the company's data.
Rockbuster dictionary: containing the ERD and fact tables
A basic descriptive analysis was conducted to understand customer preferences and the distribution of films across different markets. Notably, all movies in the Rockbuster catalog were released in 2006, spanning 22 genres and being available in 109 countries.
Rental Duration: The minimum rental duration was 3 days, the maximum was 7 days, with an average of 5 days.
Data Analysis
Market Analysis
A more detailed analysis of the top five countries provided insights into the most popular genres and movies in these regions. A Tableau dashboard was used to explore country-specific data further.
As the EDA analysis showed Rockebuster has presence in 109 countries. Rockbuster's top markets, in terms of both revenue and customer numbers, are India and China, followed by the United States, Japan, and Mexico.
The following SQL query highlights how subqueries were used to filter records, such as identifying the top 5 customers in specific countries:
COUNT(DISTINCT top_5_customers.customer_id) AS top_customer_count
FROM customer A
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_id = D.country_id
LEFT JOIN (
SELECT E.customer_id, D.country, C.city, SUM(E.amount) AS cs_amount_paid
FROM payment E
INNER JOIN customer A ON E.customer_id = A.customer_id
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_id = D.country_id
WHERE C.city IN (
SELECT C.city
FROM customer A
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_id = D.country_id
GROUP BY D.country
ORDER BY COUNT(A.customer_id) DESC
LIMIT 10
)
GROUP BY C.city
ORDER BY COUNT(A.customer_id) DESC
LIMIT 10
) AS top_5_customers
ON top_5_customers.country = D.country
GROUP BY D.country
ORDER BY top_customer_count DESC;
Top Revenue-Contributing Movies: The 2006 musical Telegraph Voyage was the highest-grossing film, followed by the comedy Zorro Ark. Harry Idaho (Drama) and Dogma Family (Animation) had the longest rental durations, with an average of 5 days.
Revenue by Genre:
Sports was the top revenue-generating genre, contributing $4,892 USD.
Sci-Fi followed with $4,336 USD, and Animation generated $4,242 USD.
In contrast, films like Duffle Apocalypse (Documentary), Oklahoma Jumaji (New), Texas Watch (Horror), and Freedom Cleopatra (Comedy) contributed the least to revenue.
Least Popular Movies: A total of 42 movies were never rented, with a significant portion being rated PG (26.19%) or PG-13 (23.8%). The least rented genres were Foreign (26.19%) and Documentary (11.9%)
Conclusion and Recommendations
Rockbuster Stealth LLC plans to transition from a physical rental service to an online rental platform. However, the company faces significant challenges in competing with streaming giants like Netflix and Amazon Prime.
Key Findings:
Strong presence in global markets, particularly India, China, the US, Japan, and Mexico.
All content is English-only and dates back to 2006.
Recommendations:
Move to a Subscription Model: Transition from individual movie rentals to a subscription-based platform to stay competitive.
Expand Language Options: To better serve global audiences, movies should be offered in multiple languages, not just English.
Regularly Update Content: Introduce more recent films and update the catalog regularly to remain relevant to current audience preferences.
Targeted Advertising: Actively promote new titles to global audiences to boost views and engagement. This will provide real-time feedback that can guide content strategies and improve user experience.
By implementing these strategies, Rockbuster can increase its chances of successfully transitioning into the competitive online video rental market.
