SQL Project

Rockbuster DVD Video Rental - SQL Project

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

Analyze the data to address the business questions using SQL and visualizations in Tableau

First step- Data Exploration and Data Cleaning

Extract an entity relationship diagram and create a first draft of a data dictionary.

Second step- Data Analytics

Perform data aggregation, clean data, configure table joins, create subqueries, common table expressions, add views, bar charts, box and whisker plots, and scatter plots to compare data and discard outliers that affect the main statistics.

Third Step- Insights and Data 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 concentrated 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.

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.