Assessing consumer activity with SQL
Evaluate streaming market potential through customer data analysis
Identify which movies contributed the most/least to revenue gain
Find the average rental duration for all videos
Map countries Rockbuster customers are based in
Locate customers with a high lifetime value-based
Calculate sales figures in various geographic regions
Data
Context
Rockbuster Stealth is a fictitious film rental company seeking to enter the streaming market. In this student project, I evaluate past rental trends and itdentify top markets and customers using structured query language (SQL).
Professional Competencies
Entity relationship diagrams (ERDs)
SQL querying
Data filtering
Data profiling
Data cleaning
Summary statistics
Common table expressions (CTEs)
Dava visualizations
Objectives
This project utilizes a relational database with fictionalized internal data.
Phase 1: Organize Relational Database
The data for this project arrived as multiple tables. To prepare it for analysis in SQL, I needed to create an entity relationship diagram (ERD) and data dictionary.
This step is easier to complete in the DBVisualizer program, but I encountered a common error in which the program could not link to my PostgreSQL database. I was able to create a manual version in Lucidpress and a set of fact tables to help with readability.
Phase 2: Identify Sources of Revenue
Next, I gathered as much information as possible about Rockbuster's movie rental patterns. I looked at factors such as rental rate, film duration, length of rental, and revenue generation.
The client's primary goal was to learn which movies generated the most revenue. After running the appropriate queries, I learned that the top titles were not in the top genres. Only one top title was in the top 10 genres.
I also noticed that none of the top genres or titles were standout favorites. The distribution sloped relatively evenly downward. Rockbuster could use any of these titles and genres beneficially in their marketing, but we needed more information to build a strategy. Since Rockbuster was planning to enter the streaming market globally, I needed to focus on finding the highest-potential regions
Phase 3: Analyze Revenue Generation by Region
Rockbuster also wanted to know what countries, cities, and customers generated the most revenue. Using common table expressions (CTEs) to create cross-table queries, I generated tables that reported total earnings by country. Transferring this data to Tableau via Excel, I was able to create a map that visualized revenue by country.
This map highlights the countries that generate the most revenue. Using this data in combination with the data on top revenue generating titles, Rockbuster can begin developing a global streaming strategy.
Recommendations for Further Analysis
What is the streaming market like in Rockbuster's top-earning countries? Rockbuster could most likely learn more by investigating beyond internal data. If they could obtain data about movie streaming in countries of interest, they could create a more refined strategy.
What are the top-earning genres and titles by country and region? Localized marketing typically generates more effective results. Digging deeper into film popularity could help Rockbuster promote its new streaming option.