Rockbuster DVD Video Rental - SQL Project

Background:

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.

Key Questions and Objectives:

1. Which movies contributed the most/least to revenue gain?
2. What was the average rental duration for all videos?
3. Which countries are Rockbuster customers based in?
4. Where are customers with a high lifetime value based?
5. Do sales figures vary between geographic regions?

Goal

As a Data Analyst at Rockbuster, I am supposed to address these business questions using SQL and visualizations in Tableau.

Context

This is a project I did as part of a course in data analytics at Career Foundry.

Data Sets

The database used for this project can be found in the following link.

• View the complete Project

• View the Storyboard in Tableau

Data Exploration and Querying:

	SELECT f.title, COUNT(DISTINCT i.inventory_id) AS number_copies, 				
	COUNT(r.rental_id) AS rental_count, 				
	ROUND(SUM(p.amount),0) AS revenue				
	FROM film f				
	JOIN inventory i ON f.film_id = i.film_id				
	JOIN rental r ON i.inventory_id = r.inventory_id				
	JOIN payment p ON r.rental_id = p.rental_id				
	GROUP BY f.film_id				
	ORDER BY SUM(p.amount) DESC;

Data Analysis and Visualization

Analysis includes line chart to visualize evolution of sales, customer segmentation based on spending, and a cohort Analysis to measure lifetime interaction of customers throughout time

In this graph we can see the distribution of total sales by customer's purchase power that ranges from $20 to $200. We can also see the number of customers that are concentated in each range of purchase. Loyal customers who made purchases over$140 only account for 10% of the revenue(7% +1%+1%+1%), while the gross of the revenue is among those customers whose spending was between $80 and $120 (29% +29%+19%). Instead of rewarding the most loyal customers, Rockbuster should focus where most of its business condense (customers with a higher spending of $80), meaning the PARETO CUSTOMERS.

In general, most of the categories share the same popularity except for the thriller genre.

Final Recommendations

Rockbuster should redirect it's efforts towards the customers that represent most of its revenue, Pareto Customers. Just focusing on the best 10 customers won't make the business prosper or reach sales target easily. For this, implementing strategies of acquisition to expand the customer base will payoff better than encouraging loyal customers to rent more movies.

For Rockbuster to transition from rental-based to a suscription-based businesss it needs to define a flat rate princing model. These transition will imply changing its business model, for instance closing physical stores and developing strong capabilities to stream online content. All of these changes suggest an initial heavy investmen and a complete disrupt on how Rockbuster operates.