Exploratory Data Analysis using Tableau (Study Case: Ecommerce Dataset)
Background for Business Questions 1
Reviewing the distribution of data from the customer side. So, we can find out:
· What are the most frequently used payment methods
· What product categories are often purchased and make a big profit
· Which provinces are quite consumptive
Purpose for Business Questions 1
Knowing the initial description of the data so we can dig deeper about the things that need to be asked in data.
Hypothesis for Business Questions 1
1. Payment methods done in each region have different trends
2. Consumption needs are the most selling product categories in every region
3. Java Island is the region with the most buying of goods online
How are payment types, product categories, and sales used in each region?
Hypothesis 1: Payment methods done in each region have different trends
What is visualized is the overall data, 6 provinces from different islands (Banten, North Sumatra, East Kalimantan, South Sulawesi, West Nusa Tenggara, and Papua), and 29 of 34 provinces throughout Indonesia.
1. The most widely used payment method is a credit card with a total transaction of 73.77% from all transactions.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
2. The most widely used payment method in Banten Province is credit card with a total of 75,24% transactions.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
3. The most widely used payment method in North Sumatera Province is credit card with a total of 71,67% transactions.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
4. The most widely used payment method in East Kalimantan Province is credit card with a total of 70,82% transactions.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
5. The most widely used payment method in South Sulawesi Province is credit card with a total of 75,95% transactions
Excludes orders that have seller state unknown, order status unavailable, and canceled.
6. The most widely used payment method in West Nusa Tenggara Province is credit card with a total of 72,62% transactions
Excludes orders that have seller state unknown, order status unavailable, and canceled.
7. The most widely used payment method in Papua Province is credit card with a total of 72,62% transactions
Excludes orders that have seller state unknown, order status unavailable, and canceled.
8. The most widely used payment method in 29 of 34 provinces is credit card with a total of 73,55% transactions
Excludes orders that have seller state unknown, order status unavailable, and canceled.
Conclusion for Hypothesis 1:
From all provinces, 6 regions, and 29 of 34 provinces, it was found that the most frequently used payment method was credit cards.
Next step:
It will be explored more deeply why this phenomenen can occur in business questions no 2.
Hypothesis 2: Consumption needs are the most selling product categories in every region
What is visualized is the overall data and to test the hypothesis, I took the data as a whole, 6 provinces from various islands (Banten, North Sumatra, East Kalimantan, South Sulawesi, West Nusa Tenggara, and Papua), and 29 of 34 provinces in Indonesia.
1. Product categories that are frequently purchased and have the highest overall sales include bed bath tables, health beauty, computers accessories, and sports leisure.
Excludes orders that have order status unavailable and canceled.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
2. Product categories that are frequently purchased and have the highest sales in Banten Province include bed bath tables, health beauty, and sports leisure.
Excludes orders that have order status unavailable and canceled.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
3. Product categories that are frequently purchased and have the highest sales in North Sumatera Province include bed bath tables, health beauty, housewares, and sports leisure.
Excludes orders that have order status unavailable and canceled.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
4. Product categories that are frequently purchased and have the highest sales in East Kalimantan Province include bed bath tables, health beauty, furniture decor, and sports leisure.
Excludes orders that have order status unavailable and canceled.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
5. Product categories that are frequently purchased and have the highest sales in South Sulawesi Province include bed bath tables, health beauty, computer accessories, and sports leisure.
Excludes orders that have order status unavailable and canceled.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
6. Product categories that are frequently purchased and have the highest sales in West Nusa Tenggara Province include bed bath tables and health beauty.
Excludes orders that have order status unavailable and canceled.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
7. Product categories that are frequently purchased and have the highest sales in Papua Province include bed bath tables, health beauty, computer accessories, and sports leisure.
Excludes orders that have order status unavailable and canceled.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
8. In 29 out of 34 provinces, it was found that the product categories that were frequently purchased and have the highest sales were bed bath tables, health beauty, sports leisure, and computers accessories.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
Excludes orders that have order status unavailable and canceled.
Conclusion for Hypothesis 2:
The product categories with the highest sales and most purchased are bed bath tables and health beauty. So there are categories of goods that are often purchased apart from consumables such as sports leisure and computer accessories.
Next step:
It will be explored more deeply why this phenomenon can occur in business question no 3.
Hypothesis 3: Java Island is the region with the most buying of goods online
Overall, almost all provinces on the island of Java have a fairly high tendency to purchase goods and have high sales compared to other islands in Indonesia (the darker the color of the province, the higher the sales/purchase of goods in that province).
Excludes orders that have seller state unknown, order status unavailable, and canceled.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
Excludes orders that have order status unavailable and canceled.
Conclusion for Hypothesis 3:
5 of the 6 provinces on the Java Island are included in the top 10 provinces that have lot of total purchases of goods and all provinces on the Java Island are included in the top 10 provinces with the highest sales in Indonesia.
Next step:
I will find out more about why this phenomenon can occur in business question 2.
Background for Business Questions 2
In the first business questions, it was found that the most common payment method was using a credit card.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
In addition, the first business question shows that the Java Island dominates online shopping transactions compared to other islands.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
Excludes orders that have order status unavailable and canceled.
I want to explore the phenomena that occur in business question 1 (Most sales are only on Java Island and the use of credit cards is very high compared to the other payment methods).
Purpose for Business Questions 2
Knowing the distribution of payment methods in each region whether it is evenly distributed or not.
Is the distribution of payment methods evenly distributes between Java and other islands?
Hypothesis: Payment methods are very different in every region
In the previous visualization, it was found that Java dominates in purchasing goods. Here I want to see how the payment methods are quite diverse in trend in each region.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
From the visualization obtained, the facts obtained include:
1. The area with the most transactions using Blipay is the Banten Province with a total of 19.07% of the total Blipay transactions in Indonesia. The area with the blue graph is Java Island and the red graph is outside Java Island. Here it can be seen that the Java Island dominates in terms of transactions using Blipay.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
2. The area with the most transactions using credit cards is the Banten Province with a total of 21.67% of the total credit card transactions in Indonesia. The area with the blue graph is Java Island and the red graph is outside Java Island. Here it can be seen that Java is quite dominating (in fact all provinces in Java are in the top 10) in terms of transactions using credit cards.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
3. The area with the most transactions using debit cards is the Banten Province with a total of 25.40% of the total debit card transactions in Indonesia. The area with the blue graph is Java Island and the red graph is outside Java Island. Here it can be seen that Java Island dominates in terms of transactions using debit cards.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
4. The area with the most transactions using vouchers is the Banten Province with a total of 20.72% of the total voucher transactions in Indonesia. The area with the blue graph is Java Island and the red graph is outside Java Island. Here it can be seen that Java is quite dominating (in fact all provinces in Java are in the top 10) in terms of transactions using vouchers.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
In the visualization in business question 1, it is known that the island of Java dominates in terms of total sales. From here I want to see how the transactions in each region. Do areas that frequently make transactions also have high total transactions?
Excludes orders that have seller state unknown, order status unavailable, and canceled.
In the visualization of transactions for each payment method, several things are obtained, such as the following:
1. The area with the largest total nominal transaction using Blipay is Banten province with a total of Rp 507,09M transactions, the area with the blue graph is Java Island and the red graph is outside Java. Here it can be seen that Java Island dominates in terms of total transactions using Blipay.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
2. The area with the largest total nominal transaction using a credit card is the Banten Province with a total of Rp 2.419,46M transactions, the area with the blue graph is Java Island and the red graph is outside Java. Here it can be seen that Java is quite dominating (in fact all provinces in Java are in the top 10) in terms of total transactions using credit cards.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
3. The area that has the largest total nominal transaction using a debit card is the Banten Province with a total of Rp 41,56M transactions. The area with the blue graph is Java Island and the red graph is outside Java Island. Here it can be seen that Java Island dominates in terms of total transactions using debit cards.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
4. The region that has the largest total nominal transaction using vouchers is Banten Province with a total of Rp 69,67M transactions. The area with the blue graph is Java Island and the red graph is outside Java Island. Here it can be seen that Java is quite dominating (in fact all provinces in Java are in the top 10) in terms of total transactions using vouchers.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
Here we will find out what payment methods are often used in addition to the payment methods already provided.
What are the most used payment method to buy online?
In a survey conducted by APJII, the top 6 most frequently used online payment methods to purchase online was COD as much as 27.5%¹.
What are the most frequently used payment methods in each region in Indonesia?
In a survey from lokadata, 73.04% of e-commerce payments were made by buyers via COD. The area that did the most COD are from outside of Java Island, one of them was Gorontalo at 93.48%².
Based on the 2 surveys, most people use payment methods such as COD. Also, the use of COD is concentrated in areas outside Java, even most of the people of Gorontalo prefer the COD system in making payments².
While in the data, there are only 4 payment methods, namely blipay, credit card, debit card, and voucher.
Average Order Value
Here are some interesting things that I found. One of them is the payment method using a credit card. Here I see that there are more transactions between West Java and DKI Jakarta in West Java. However, for total transactions, it turns out that DKI Jakarta is superior to West Java in the payment method using a credit card. Here I want to explore more about the average order value where the purpose of the Average Order Value method is to see the average number of rupiah spent every time a customer places an order in each area.
In the following visualization, it turns out that there are quite surprising facts where the regions that tend to have the most Average Order Value are areas outside Java where West Nusa Tenggara ranks first wherein one order they spend a budget of around Rp. 172,11K.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
Excludes orders that have seller state unknown, order status unavailable, and canceled.
This means that even outside of Java, they spend quite a lot of money shopping online even though there are not many items ordered. The addition of payment methods is expected to reach markets outside Java to increase sales.
Conclusion:
1. Customers on the Java Island dominate transactions on each payment method provided.
2. Banten Province always ranks first in the most total transactions.
3. The most frequently used payment methods outside Java are COD.
4. Althought areas outside Java tend to have fewer orders, in one order they spend quite a lot of money.
Suggestions:
1. Based on surveys from external sources, I recommend adding a payment method in the form of COD. Users are expected to have many choices of payment methods to increase user flexibility in making payments.
2. . By implementing payment methods that are widely used outside Java, it is expected to be able to embrace markets outside Java.
Background for Business Questions 3
In business question 1, it is known that the product categories that are most frequently purchased and have the highest sales are bed bath tables, health beauty, sports leisure, and computers accessories. Here I want to find out how the trend of the existing product categories? Is the trend up or down?
Excludes orders that have seller state unknown, order status unavailable, and canceled.
Excludes orders that have order status unavailable and canceled.
Purpose for Business Questions 3
Knowing what the trend of product categories in data is so that product categories that have an increasing trend can keep in stock.
What is the trend of the 5 best-selling product categories in each year and how are the sales of these product categories?
Note: the data displayed is data from 2017 to 2018 in the second quarter because the assumption is that the data in 2016 and 2018 in the third quarter have not yet been entered.
1. The 5 most frequently purchased product categories during the 2017Q1–2018Q2 were bed bath tables, health beauty, sports leisure, furniture decor, and computers accessories.
Excludes orders that have order status unavailable and canceled.
2. The 5 product categories with the highest sales during the 2017Q1–2018Q2 were watches gifts, health beauty, bed bath tables, sports leisure, and computers accessories.
Excludes orders that have order status unavailable and canceled.
3. Products in the bed bath table and health beauty categories experienced an increase in the number of products purchased, although sales of bed bath tables decreased slightly in the second quarter.
Excludes orders that have order status unavailable and canceled.
4. Products in the watch gifts category have always experienced an increase in sales throughout 2017Q1 to 2018Q2.
Excludes orders that have order status unavailable and canceled.
Suggestions:
• Bed bath tables and health beauty have quite high sales and are necessities that quickly run out. So doing a fairly massive sale like giving some promo can increase sales.
• Watches gifts are a category product whose sales are quite high, but because watch gifts are items that can last for a long time, it is necessary to review the trend in the future, whether it will continue to rise.
• The following data can be used to perform predictive analysis to predict how the trend will be in the following years.
Predictive Analysis
Furthermore, predictive analysis was performed using Tableau. Products in the Bed bath Table and Health Beauty categories are predicted to increase in sales until the end of 2018.
Predictive analysis on Watches Gifts category products using Tableau is also predicted to increase sales until the end of 2018.
Conclusion:
- Bed bath table and health beauty are the most frequently purchased category products
- Watches gifts are the products that have the highest sales
- Bed bath tables, health beauty, and watches gifts have pretty good trend predictions so they need to be kept in stock
Thank you for reading!
References:
[1] Asosiasi Penyelenggara Jasa Internet Indonesia, 2019–2020. Laporan Survei Internet APJII, s.l.: Indonesia Survey Center.
[2] Lita, 2020. Lokadata. [Online]
Available at: https://lokadata.id/artikel/orang-indonesia-pilih-cod-saat-belanja-online [Accessed 24 May 2021].
