CURRENT SITUATION
Rockbuster Stealth LLC is a movie rental company that used to have stores worldwide. The management team plans to use its existing movie licenses to launch an online video rental service to stay competitive.
GOAL
Obtain data-driven answers to help (BI) department with the launch strategy for the new online video service.
KEY QUESTION
-
Which movies contributed the most/least to revenue gain?
-
What was the average rental duration for all videos?
-
Which countries are customers based in? Where are customers with a high lifetime value based?
-
Do sales figures vary between geographic regions?
DATA EXPLORATION & QUERYING
During this stage, I carried out the following:
-
Loaded data into a relational database management system.
-
Extracted entity relationship diagram (ERD) using the DBVisualizer tool.
-
Created a Data Dictionary that documented the relationships between tables, keys, data types, etc. Click this Data Dictionary to see the details.
-
Cleaned, filtered, grouped, and ordered data using SQL.
-
Wrote queries that joined tables, and used subqueries and Common Table Expressions (CTE).
ANALYSIS & VISUALIZATION
To provide answers to key questions of BI department was:
-
Found out the Top movie category by revenue.
-
Determined the highest-grossing movies globally.
-
Compared rental duration through movie categories.
-
Identified customer base and Top 5 customers globally.
-
Analyzed revenue by geo regions.
Queries were used to create appropriate Tableau dashboards for key business questions.
SOME OF INSIGHTS
-
India is the largest customer base, followed by China and the United States.
-
46 % of global revenue comes from the Asia region, followed by the America region (25%).
-
56% of customers are based in the Top 10 countries.
SOME OF RECOMMENDATIONS
-
It's advisable to concentrate sales efforts on regions with high customer numbers and revenue, such as Asia, America, and Europe.
-
Maximize your promotional efforts and boost sales by investing marketing resources in the three most profitable countries - India, China, and the USA.
-
Implementing a loyalty program targeting the most active customers can incentivize repeat business and drive increased revenue for the company.
RETROSPECTIVE
High Points
I enjoyed creating an entity relationship diagram, which gave me a clear view of the whole variables and the connection between tables.
Project Challenges
Working with subqueries can be challenging, especially when dealing with complex queries. I believe CTE will be more useful for maintainability and multiple referencing. For example, if I want to update CTE, I just need to make changes in one place.