Hello,

It is Nhi,

Thank you for being here.

As a data analyst, I have been working on different projects. My goal is to bring a different perspective to your business.

Sometimes Data can be very confusing and complex, so it is important to understand it. And I am here to help you with that. I will help you present complex, meaningless data in an easily digestible format that could be used to launch a strategy for your new service.

My Story

As a data analyst, I received data from a movie rental company. The business intelligence (BI) department needs help to launch a strategy for their new movie service to stay competitive and idealize their inventory.

In this project, I will use SQL to analyze the data and answer any ad-hoc business questions the stakeholders may have. Read more to see how I do it.

Part 1: Understand data structure and relationship.

First, as a data analyst, I need to understand my data and identify the primary and foreign keys in each table. I create an Entity Relationship Diagram (ERD) using DbVisualizer. Some people like to skip this step. However, it will save you so much time and energy later on by creating an ERD diagram, especially, if you have a complex data set.

Part 2: Exploratory Data Analysis (EDA)

It is necessary to profile your data by calculating basic descriptive statistics like the mean, maximum, and minimum values or the count of each column. It helps to find valuable, actionable insights within the data. Sometimes, it is not what the stakeholders are looking for. But this step helps to find missing values and patterns.

Download the data set below to follow my work if you want to.

Download

If you ask me about SQL, I can tell you how much I love SQL; it is an easy and straightforward language.

One thing I don't like about SQL is that SQL doesn't have visualization features.

I could show you my work, from how I write basic to more complex subqueries or joining tables. However, it means nothing and it is hard to understand. Again, as a data analyst, I am also a storyteller. I will translate a tricky language into beautiful and meaningful graphs and bars that help you understand your data.

I usually use Tableau and Power PI to present my finding. In the last project, " Project 1: Preparing for Flu seasons", I used Tableau as my visualization software. In this project, I will use Power PI. They are very similar if you ask me.

Let's get start it

Click on the picture to see what I wrote in SQL to get the data

1000 movies

Screen Shot 2023-03-19 at 2.53.37 AM.png

Average movie length
115 mins

Movie language
mainly English

With 17 different genres

Screen Shot 2023-03-21 at 4.34.03 AM.png

Screen Shot 2023-03-15 at 12.48.44 PM.png

The company has total of 599 users across the world

Most of the customers/users are located in India, China, the United States, Japan, Mexico, Brazil, and Russia.

The top 10 countries with the most users are also the top 10 countries that bring the most revenue to the company. They account for more than half of the total revenue of the company (Figure 2.).
It suggests there is a need to focus on these countries. It is important to know what people in these countries like so we can create a strategic business plan.

Top 10 countries with the most number of users

Top 10 countries have the most number of users.png

Figure 1.

Screen Shot 2023-03-16 at 12.04.46 AM.png

Figure 2.

Figure 3A.

Screen Shot 2023-03-16 at 1.45.35 AM.png

Figure 3B

Screen Shot 2023-03-19 at 1.46.48 AM.png

Figure 3A shows the revenues by rating the top 10 countries with the highest revenue.

PG-13 movies have the highest revenue in these countries, and G movies have the lowest revenue (Summary in Figure 3B)

Note: Depending on the needs, Figure 3A and 3B, could be used for different purposes.
For example, suppose the company decides to focus on specific sectors or open a location in these 10 countries. In that case, it is essential to identify the niches using Figure 3A.

Otherwise, they want to know what type of movie rating is the most common and will bring the most benefit, then Figure 3B is enough to give information

Let's confirm the result by looking at the top 10 movies with the most revenue. The top 10 movies have brought the most revenue and accounted for half of the company's total revenue. 5 in 10 of these movies are PG-13 movies. Now we do see the trend, PG-13 seems like the type of movie that bring the most revenue to the company.

Figure 4A. The top 10 movies have bring the most revenue to the company

Screen Shot 2023-03-15 at 2.56.39 PM.png

Figure 4B. The top 10 movies have bring the least revenue to the company

Screen Shot 2023-03-15 at 3.17.16 PM.png

I also look at the top 10 movies that bring the least revenue to the company. There are a total of 3 of 10 movies that are R-rated movies. It is hard to conclude from this. It could be due to R-rated movies being more restricted in some countries and specific age groups.

So far, we know PG-13 is one of the types of movie ratings that brings the most revenue to the company. When I look at the company inventory, I notice the company has a total of 223 PG-13 movies which accounted for 22% of total revenue. To bring more revenue to the company, we need to devise a plan such as stocking more interesting PG-13 movies.

Genres

Let's figure out what genres the people in the top 10 countries like.

The data shows that Sports, Animation, Drama, Sci-fi, and New are the top 5 genes that bring the most revenue to the company in the Top 10 countries with the most users.

It is important to look at the inventory to see what the company has and whether we can revise it.

For the top 5 most stocked genres, the company has 74 movies in Sports, 73 in Foreign, 68 in Documents, 68 in Family, and 66 in Animation.

Now we know what is needed to be changed. The company needs to stock more Animation, Drama, Sci-fi, New.

Screen Shot 2023-03-21 at 4.31.30 AM.png

Movie length

Let's figure out how long the people in the top 10 countries like to spend watching movies.

The average movie length is around 114 minutes, similar to the company movie inventory about 115 minutes.

Let me break down the most popular movie length.

Even though the average movie length is 114 minutes in the top 10 countries with the most users, the most common movie length these people pick is 85 minutes.

Let me count how many movies have around 85 - 114 minutes lengths.

Movie inventory
Average movie length
115 mins

Average movie length
in the top 10 countries
114 minutes

Ideal movie length
85 minutes

Number of movies have
length between
85-114 minutes
222

I found that people in the top 10 countries have the most users who like to spend around 114 minutes and ideally about 85 minutes. However, the company only has 222 movies with a movie-length between 85 -114 minutes, accounting for 22.2% of the inventory.
It is important to stock more movies with lengths between 85 and 114 minutes.

Language

I noticed the company only stocks movies that language is English.

When we look at the top 3 countries with the most users: India with 60 users, China with 53 users, and the United States with 36 users.

It is important to have more movies that are in different languages. Since the company does not have the resources and it would be particular to foreign countries,
we could build a platform that creates subtitles for movies in other languages, such as Hindi or Mandarin.
It would help to use the resources we have a while and can target different groups of people.

Conclusion

So now we know PG-13 is one of the rating types that bring the most revenue to the company. We also see that the company has a total of 223 movies. We must stock more PG-13 that we think the customers might like. In contrast, R- rating movies are not user-friendly; they are at the top of the film, bringing the least revenue to the company due to their characteristics. It would be risky if we stock more R-rating movies.

Further, there is a need to change movie genres and movie lengths. The company needs to stock more Animation, Drama, Sci-fi, and New with an average time between 85 and 114 minutes.

One more important thing, most of the movies are in English. There is a need to create software that can create subtitles for movies. It would help to promote and target more customers across the world.