Project 2 : Sales Analysis

Hawa Mayang Andini
Feb 6, 2022
3 min read

Updated: Feb 14, 2022

Company X which is engaged in online sales needs help of a BI Analyst to analyze the company sales data in the last few years and create a dashboard that will be used to analyze the condition of the company sales performance and get insights from the dashboard.

Some information that company needs :

Sales performance from time to time
Which city that must to increasing sales
The best time to advertise for increasing sales
The best selling product and why the reason
The best combination of product to sell in one package

To completed this challenge the tools that I used are Google BigQuery and Google Data Studio.

Data Preview

Based on the preview data, there are some columns where the data type is not match with their content, such as :

Quantity_Ordered (changed to integer)
Price_Each (changed to float)
order_date (changed to timestamp).

So, the problem is : BI analyst must transform the data first, and extract the data from Purchase_Address column to get information about city.

Cleansing Data

First step is cleansing the data to fix the existing data. The query for cleansing the data is:

The result after cleansing

This is the result after cleansing, we have the new column that is city and the data type in each columns have been appropriate.

After that we save into the new table and named as ‘sales_data_trx’

Sales Performance By Month

We want to know sales performance trend in every month. So the query used is :

The chart result:

From line chart above, the company faces increased in sales from February 2019 until April 2019, then sales decreased slowly in May 2019 until September 2019. The highest sales occur in December 2019 and dramatically decreased in January 2020.

Sales Performance by City

We want to know the number of sales from each city. So the query used is :

The chart result :

From bar chart above, San Francisco is the country with the highest sales compared to other country. And Austin is the country with the least sales. So, Austin must increasing the number of order to boost their sales. And also, San Francisco can add more quantity of the products in order to further increasing the number of sales.

Total Transactions by Time

We want to know the best time to do advertising for increasing sales. Before that, we have to know what time that has the highest traffic of transaction. The query to answer this problem is :

The chart result:

From bar chart above, we can see that top 3 the highest transactions occur at 12pm, 7pm, and 6pm. Which means, many people doing transaction in the lunch time and after work. In order to increasing sales, we can post ads around 12 pm or 7pm, because at that time there is a chance for people seeing the ads and after that make some order right away.

The Best Selling Product

We want to know what is the most sold product and also we want to know the reason why that product were so demanding. The query to answer the problem is :

The result:

From combo chart above, we can see that AAA Batteries is the most sold product among the others product. The reason because AAA Batteries is the cheapest product among the other product. It can be proved by the pattern of the chart above.

The Best Combination of the Product

We want to know which the best combination product to sell as a bundling. To answer that, first we have to know which product are purchased together in one transaction (in one order id). The query is :

The result is:

From the table above, we can see that the most purchased product in one transaction are iPhone and Lightning Charging Cable. It is because when people buy iPhone they need Charger for the additional item. So, we can sell iPhone and Lightning Charging Cable as a bundle to make people interest to buy.