This analysis used Excel, a database accessed with pg Admin 4 and PostgreSQL, and Microsoft PowerPoint for the final presentation. To follow along in greater detail please click the links below.
Problem and Background
A video rental company is looking to expand their services to provide online streaming services in 2020. As a launch point they will begin by using existing movie licenses and targeting established regions.
Needs
-
Develop a business strategy for the business intelligence department.
- Understand how sales vary by region.
- Identify movies and genres that perform well within existing licenses.
Database Exploration and Statistical Analysis
Company Database
The data for this analysis comes from an already established company database. It is accessed and navigated using pg Admin 4 and PostgreSQL. I was instructed to not change anything in the database, but if any error or inconsistency is found to note it in my reports.
​
Entity Relationship Diagram and Data Dictionary
​
The first step in the process was getting acquainted with the structure of the database itself, and in order to visualize the setup I created an entity relationship diagram (ERD) seen on the right. This database has a snowflake schema with two basic groupings of data - film information and the store and customer data.
​
Building on this I created a data dictionary and documented each of the tables and their relation to each other. This can be updated as the company grows and adds to their database. To view the dictionary please see the link above.
Click Image to Enlarge
Descriptive Statistics
Both an overview and descriptive statistical information was recorded to complete the base of understanding of the company's current situation. There is a total of 1000 films, encompassing 20 unique genres, being rented across 108 countries. Rental rates run from 0.99 to 4.99 USD. This company has a total of 599 customers, however 15 of them are labeled as inactive.
Analysis Highlights
Setting the Parameters
In order to determine where the business intelligence department should focus their efforts I needed to decide on parameters. It is unclear what would be considered important to the company, and I wanted to create several groupings that could progress with the company as they grow. These groupings were determined based on three factors.
​
-
Total revenue per country
-
Total customers per country
-
Customer purchasing power
​
Purchasing power for each country was calculated as revenue divided by customer count. This is the potential power each customer holds.
Group A
​
-
Top countries based on total revenue and customer count.
Group B
​
-
Countries above the median in all three factors.
Group C
​
-
Countries above the median based on revenue and customer count.
Group A: Top Countries
14 countries had over $1000 USD revenue and a higher than average customer count. There was a large enough drop in revenue after Taiwain to show a natural delineation.
Click Image to Enlarge
Group B: Countries with Potential
Click Image to Enlarge
This grouping of 29 countries is where the purchasing power comes into play, and identifies countries with the potential for high profits as the customer base grows.
​
If we compare the top earning country in Group A to the top country in terms of purchasing power we can see the power a single customer holds.
​
-
India:
-
Customer Count: 60
-
Total Revenue: $6,034
-
Purchasing Power: $101
-
-
Belarus:
-
Customer Count: 2
-
Total Revenue: $201
-
Purchasing Power: $136
-
If Belarus had the same number of customers as India the earnings could potentially be over $8,000. The potential earnings of any of these countries just by increasing the customer base makes this group one not to be overlooked.
Group C and Remaining Countries
Group C is made up of 15 countries that have both revenue and customer count over the median, but did not meet the additional factor of significant purchasing power. 55 countries did not meet any of the three factors, but can be reassessed as the business develops.
Conclusion and Next Steps
Breaking into online video rental is the next step for this company, and creating the right plan of action means following after the established areas of business. By dividing up the many countries into groups based on their potential growth the marketing and business intelligence departments can focus their efforts and maximize revenue.
​
Countries with high revenue and a large customer base ensures the most stable transition into online video rental. After this, the company should focus on countries that have high earning potential if their customer base increases. Finally, the company can expand efforts to countries that have revenue and a customer base over the median. After these steps have been completed the remaining 55 countries can be reassessed to see how they have developed.
This analysis used a company database that was accessed using pg Admin 4 and navigated with PostgreSQL. Charts were made on Tableau and transferred into a PowerPoint presentation and sent with relevant documentation and reports to stakeholders.
​