Rockbuster  video rental

Tasked by Rockbuster Stealth’s business intelligence (BI) department to help with the launch strategy for the new online video service. I explored their data in PostgreSQL to analyze their data and present findings visually using Tableau.

  • Rockbuster Stealth LLC is a movie rental company that used to have retail stores located globally. 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 to stay competitive.

  • Rockbuster Stealth requested a performance analysis and data-driven insights to inform its future company strategy

  • As an analyst, I was tasked to use SQL to explore the data, address the key questions, and create visualizations using Tableau

  • Which movies contributed the most/least to revenue gain?

    What was the average rental duration for all videos?

    Which countries are Rockbuster customers based in?

    Where are customers with a high lifetime value based?

    Do sales figures vary between geographic regions?

  • The database for this project can be found through the following link

Data Management

01. Data Setup

Understanding the role of online analytical processing databases (OLAP) and relational database management systems (RDBMS), I set up a database environment using PostgreSQL. This laid the groundwork for collecting and structuring data in a way that's both efficient and organized.

02. Data Exploration

Moving into the exploration phase, I dove into the intricacies of keys, indexes, and common data types within relational databases. I explored different relational database schemas and their components. I extracted entity relationship diagrams and began crafting my own data dictionary. This phase was all about equipping myself to navigate and comprehend the underlying structure of data, fostering a deeper understanding.

03. Data Analysis

Here I covered everything from basic commands to constraints and executing fundamental business queries. As I progressed, I tackled more advanced aspects like querying, filtering, summarizing, and joining tables of data. Followed by subqueries and CTEs. The ultimate aim was not just to analyze data but to present findings visually.

Data Exploration & Querying

Entity Relationship Diagram

The top 5 customers in the top 10 cities who have paid the highest total amounts to Rockbuster.

Query Example

Insights & Visualisations

Located globally, Rockbuster forms a strong customer basis for expansion. There is a strong performing presence in India, China, Japan, the United States & Brazil. India & China contribute 30.46% to overall revenue and hold a large portion of the customer base.

Customer Overview

When breaking down the payments for each customer we can see that average order value is a contributor to the lifetime value of a customer

A higher average order value makes a bigger contribution to overall revenue vs. increased order numbers.

Genre Overview

Based on Genre popularity for countries that have the largest audience 2 things should be focused on:

Ensure there is investment into top-performing genres (Sports, Animation, Drama)

Slightly increase the price on these genres to be incorporated into the monthly cost.

Invest in more sports-based films & be sure to have a pricing strategy for the top genres.

Rental rate vs. Popularity

Sports, Sci-Fi, & Animation were found to be the top performing genres from both a units sold perspective and an amount made perspective. Sports lead animation sales by 13 units, however, experience $647 more than animation - purely due to rental pricing differences.

Recommendations

** Please view final presentation here and data dictionary here

  • Reinvest in markets with high customer counts. Doube down on their genres of interest and bring up average order value of these films by incorporating it into a monthly pricing plan for a streaming platform.

  • Incentivize high-value customers with monthly discounts/offerings. Upon launch create a media PR pack to send to these customers for their loyalty and encourage them to act as ambassadors on behalf of Rockbuster LLC.

  • Reinvest in popular genre themes: Sport, Sci-Fi, Animation. Ensure the Top 5 popular genres are costed at >$3.

  • If moving to a monthly payment rate - bridge the gap between low order value and high order value - work off average monthly cost that incorporates all popular films at a cost of $3 per film. Include low order value/unpopular films on the platform as an in-app purchase option