Online Video Rental Analysis
I performed an analysis of data about movie rentals and customers to answer business questions leading to the launch of an online video rental service.
OVERVIEW:
​
In this project, I analyzed data in a relational database management system (RDBMS) from a movie rental company to inform the launch of an online video service. Using SQL, I queried the data to provide data-driven insights about customers. I also created some data visualizations to illustrate my findings. These results were used to help the company understand their customer base and create recommendations utilizing this information.
​
​
PURPOSE & CONTEXT:
​
-
Rockbuster Stealth LLC is a fictional version of Blockbuster looking to adapt their business in order to stay competitive.
​
-
For a portfolio project, I analyzed their data to uncover information about their revenue streams and customer base globally.
​
-
I used the results of my analysis to advise the board regarding their best customers and suggest a strategy involving them.
​
DATA:
​
-
Database includes information about film inventory, customers, payments, etc., provided by PostgreSQL Tutorial
TOOLS & TECHNIQUES:
​
-
SQL:
-
JOINs
-
Subqueries
-
Common table expressions
-
-
Tableau:
-
Data visualizations​
-
-
PowerPoint:
-
Presenting results​
-
PROCESS:
First, I loaded the data into a relational database management system (RDBMS) and extracted an entity relationship diagram:
​
Next, I performed some exploratory data analysis (EDA) by creating data profiles of summary statistics. Then I queried the database in order to answer business questions about sales and customers.
​
WHERE ARE CUSTOMERS LOCATED?
I found the top 10 countries in terms of number of customers with the following SQL query utilizing JOINs:
Next, I found the top 10 cities from those countries in terms of number of customers with the following SQL query utilizing a subquery and JOINs:
The results of these queries are shown in my final presentation as follows:
WHERE DOES REVENUE COME FROM?
I wanted to find out if the countries with the most customers were the same as the countries with the highest revenue. I created the following SQL query to answer this question, then made a visualization in Tableau to illustrate the results that can be seen on the slide below from my presentation:
Next, it made sense to create a map visualization so that the audience could see at a glance how countries compared to each other. I created the following SQL query whose results I used to create the map below in Tableau:
If you are interested in exploring this map further, please use this link:
​
WHO ARE THE MOST LOYAL CUSTOMERS?
In order to discover the most loyal customers (in terms of dollars spent), I created the following SQL query utilizing subqueries and JOINs:
RECOMMENDATIONS:
-
Based on customer numbers and revenue, we should focus our launch in these countries: India, China, US, Japan, Mexico, Brazil, Russia, Philippines, Turkey, and Indonesia.
​
-
We should reward the top 5 customers identified by letting them test the new online video service in return for their feedback, as well as encouraging them to share their experience with us on social media.
