Rockbuster Video Rental

Rockbuster Stealth LLC is a movie rental business with stores around the world. It’s trying to compete with popular streaming services by launching an online video rental service.

  • As a data analyst, my goal was to assist Rockbuster in launching their online video rental service by using SQL and Tableau to analyse Rockbuster's database and answer ad-hoc business questions.

    1. Which movies contributed 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?

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

    Rockbuster is a fictional company and the database was supplied by CareerFoundry for this project.

  • The query language SQL was used in the relational database management system PostgreSQL.

    DBVisualizer was used to construct an Entity Relationship Diagram of Rockbusters database.

    PowerPoint was used alongside Tableau to present the analysis to stakeholders.

    Excel was used to document queries and results to senior analysts.

  • This is a project I completed as part of the data analytics course at CareerFoundry.

Approach and Process

Understanding the Data

An entity relationship diagram was constucted in DBVisualizer to assist with querying and creating a data dictionary.

Entity relationship diagram of Rockbuster’s database made in DBVisualizer.

Data Preparation

Consistency checks were made to ensure there were no duplicates or missing values in important tables. Consistency checks were also conducted using the DISTINCT query on specific columns.

Data Querying

Various SQL queries were made to answer simple and complex business questions. The use of filtering, joins, subqueries, views, data aggregations, and common table expressions were essential for this task.

Select C.customer_id, C.first_name, C.last_name, CO.country, CI.city, SUM(p.amount) AS total_amount_paid
FROM payment P
INNER JOIN customer C on P.customer_id = C.customer_id
INNER JOIN address A on C.address_id = A.address_id
INNER JOIN CI on A.city_id = CI.city_id
INNER JOIN country CO on CI.country_id = CO.country_id
GROUP BY C.customer_id, CO.country, CI.city
ORDER BY SUM(P.amount) DESC
LIMIT 10;

Example of Query - “Where are customers with a high lifetime value based?”

Data Analysis and Visualisation

Tableau Dashboards were developed to visualise numerous query results. This analysis included bar charts to visualise top and bottom movie rental performances, as well as maps showing the distribution of Rockbuster’s most loyal customers and the company’s performance across different countries.

This bar chart revealed the high and low performers in Rockbuster’s inventory. It also led to a further query finding that 42 movies in Rockbuster’s database had never been rented.

This map highlighted India and China as the countries with the most customers and highest revenue. This contrasts the distribution of customers who have spent the most at Rockbuster.

Results and Deliverables

Summary of Results

  • Rockbuster’s top and bottom performing movies were identified (including those which were unrented), allowing Rockbuster to consider movie licenses to drop and save money.

  • Global customer and revenue distributions were mapped. This indicated that Rockbuster should diversity its library to include movies in other languages and a need to investigate marketing strategies in top countries like India and China.

  • Top customers were identified which can be a starting point for Rockbuster in developing personalised recommedation systems for their new online rental service to enhance user satisfaction and retention.

Deliverables

  • Final Presentation: PowerPoint summarizing key findings and complete recommendations for Rockbuster.

  • SQL Queries File: Excel file storing all SQL queries that contributed to the final presentation and their outputs.

  • Data Dictionary: Comprehensive document detailing the structure and relationship of Rockbuster’s database.