Priya Agrawal

Rockbuster Stealth Video Rental Analysis

Overview

Movie rental stores have become obsolete and online video rental and streaming services have become the popular choice among people. To stay competitive with streaming services such as Amazon Prime and Netflix, Rockbuster Stealth has decided to launch a new online video rental service to replace their stores. The objective of this analysis is to answer business questions from Rockbuster Stealth’s management board which will help to inform the development of the launch strategy for the online video rental service.

Purpose and Context

The Rockbuster Stealth Video Rental project was a project I completed as part of my data analytics program at CareerFoundry. This project showcases my skills in data analysis with SQL and visualization with Tableau.

Key Questions

  • Which movies contributed the most and 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?

Data

The Rockbuster dataset used for this project includes information on Rockbuster Stealth’s payment transactions, film inventory, stores, customers, and more.

Tools

  • PostgreSQL: The Relational Database Management System (RDBMS) that is used to store and execute queries on the data.
  • pgAdmin4: To manage the PostgreSQL database management system.
  • Tableau Public: To generate visualizations from the data and share them.

Techniques

The following techniques were used in this project:

  • Extracting an Entity Relationship Diagram
  • Creating a data dictionary
  • Data cleaning
  • Data filtering
  • Database querying
  • Database subqueries
  • Data summarizing
  • Joining tables
  • Common table expressions (CTE)
  • Visualization in Tableau

Entity Relationship Diagram (ERD)

Analyzing Data

I ran several queries, subqueries, and used common table expressions to extract data from the database to answer the business questions from Rockbuster’s management board.

Identifying Movies that Contributed the Most and Least to Revenue Gain

The ten movies that contribute the most to revenue gain are Telegraph Voyage, Zorro Ark, Wife Turn, Hustler Party, Innocent Usual, Saturday Lambs, Titans Jerk, Torque Bound, Dogma Family, and Harry Idaho with revenue contributions ranging from $168.72 to $215.75. The ten movies that contribute the least to revenue gain are Texas Watch, Freedom Cleopatra, Oklahoma Jumanji, Duffel Apocalypse, Rebel Airport, Cruelty Unforgiven, Treatment Jekyll, Young Language, Japanese Run, and Lights Deer with revenue contributions ranging from $5.94 to $7.94. There is a significant difference between both groups in terms of revenue generated.

Identifying Average Rental Duration

Using queries and subqueries in SQL, I extracted several film rental statistics in addition to average rental duration. Average rental duration is 5 days, minimum rental duration is 3 days, and maximum rental duration is 7 days. Average rental rate is $2.98, minimum rental rate is $0.99, and maximum rental rate is $4.99. Average film length is 115 minutes, minimum film length is 46 minutes, and maximum film length is 185 minutes. Average replacement cost is $19.98, minimum replacement cost is $9.99, and maximum replacement cost is $29.99.

Rockbuster’s film inventory consists of 1000 films, all of which were released in 2006. More films in the inventory are rated PG-13 than any other rating.

Identifying the Countries Rockbuster’s Customers are Located In

Rockbuster’s customers are based in many countries around the world, with India and China having the largest number of customers.

Identifying Locations of Customers with High Lifetime Values

Customers with high lifetime values are based in the cities of Atlixco, Sivas, Celaya, Aurora, and Adoni.

Identifying Sales Variations in Different Geographic Regions

Sales vary across geographic regions with Asia, North America, and South America having the highest sales.

Tableau Storyboard

You can access my Tableau Storyboard to see the visualizations generated for this project along with the highlights of the analysis.

Results and Recommendations

Results:

  • The ten movies that contribute the most to revenue gain are Telegraph Voyage, Zorro Ark, Wife Turn, Hustler Party, Innocent Usual, Saturday Lambs, Titans Jerk, Torque Bound, Dogma Family, and Harry Idaho with revenue contributions ranging from $168.72 to $215.75. The ten movies that contribute the least to revenue gain are Texas Watch, Freedom Cleopatra, Oklahoma Jumanji, Duffel Apocalypse, Rebel Airport, Cruelty Unforgiven, Treatment Jekyll, Young Language, Japanese Run, and Lights Deer with revenue contributions ranging from $5.94 to $7.94.
  • Average rental duration is 5 days for all videos.
  • The top 10 countries with the most Rockbuster customers are India, China, United States, Japan, Mexico, Russian Federation, Brazil, Philippines, Turkey, and Indonesia.
  • Customers with high lifetime values are based in the cities of Atlixco, Sivas, Celaya, Aurora, and Adoni.
  • Sales figures vary between geographic regions with Asia, North America, and South America having the highest sales.

Recommendations:

  • Rockbuster can remove films from their inventory that do not generate much in revenue, such as the ten lowest revenue-generating films.
  • Marketing funding, campaigns, and resources for the new online video rental service can be focused on the countries that have the most customers and generate the most revenue such as India, China, United States, Japan, Mexico, etc.
  • The online video rental service can first be launched in India, China, and the United States since these countries have the most customers and highest sales.