E-Commerce Sales Transaction
Adrian Djaya Prathama
2024-01-26
E-Commerce
E-commerce (electronic commerce) is the activity of electronically buying or selling products on online services or over the Internet. E-commerce draws on technologies such as mobile commerce, electronic funds transfer, supply chain management, Internet marketing, online transaction processing, electronic data interchange (EDI), inventory management systems, and automated data collection systems. E-commerce is the largest sector of the electronics industry and is in turn driven by the technological advances of the semiconductor industry.
1 Data Preparation
1.1 Reading and Inspecting Data
We will be reading and inspecting e-commerce sales transaction for 2023 period. We will name our table ecom
# Data Input
ecom <- read.csv("data_input/larger_sales_dataset.csv")
head(ecom)# Checking data row
nrow(ecom)## [1] 10000# Checking data columns
ncol(ecom)## [1] 10names(ecom)## [1] "Order.ID" "Product.ID" "Product.Category" "Quantity"
## [5] "Unit.Price" "Total.Price" "Order.Date" "Customer.ID"
## [9] "Payment.Type" "Order.Status"From inspecting data above we knew:
- There is 10 columns and 10000 rows.
- All the columns are:
- Order.ID
- Product.ID
- Product.Category
- Quantity
- Unit.Price
- Total.Price
- Order.Date
- Customer.ID
- Payment.Type
- Order.Status
1.2 Data Cleansing
We will create a new dataframe called ecom_clean for us to use as a cleaned dataframe
#Creating clean dataframe
ecom_clean <- ecom[]# Checking Data Type
str(ecom_clean)## 'data.frame': 10000 obs. of 10 variables:
## $ Order.ID : chr "873ff350-854d-4bd8-aebf-6cc5e1a6d3b7" "7cb2641e-ea6b-4faf-a7ab-24992d5573fa" "94e0de8a-419d-46e1-9e7c-65c730b89c65" "88cc8ccf-f018-46ac-ba3b-f36b5cf4e429" ...
## $ Product.ID : chr "26ab320e-62f2-4203-be68-f8740085d796" "8c0d7ba9-1e6b-412a-8359-97d964ad19e5" "5d65b6c7-1894-4207-b7e3-b53cc2d5e5e3" "b3f6c6a5-d3cb-49a4-b53b-3996be815c28" ...
## $ Product.Category: chr "Sports & Outdoors" "Home & Kitchen" "Beauty & Health" "Books" ...
## $ Quantity : int 4 5 3 2 3 2 2 2 5 4 ...
## $ Unit.Price : num 154.1 275.5 56.4 176.4 409.9 ...
## $ Total.Price : num 616 1378 169 353 1230 ...
## $ Order.Date : chr "2023-08-02" "2023-09-03" "2023-12-06" "2023-07-22" ...
## $ Customer.ID : chr "292fe8cf-4d60-437b-8488-4c225e84d48d" "a026178d-e3ed-4d45-a49c-8336479b2114" "808e8010-1552-42dd-a9c5-eacd4fde3283" "6184896b-4fb2-4ae0-9851-65033dae1319" ...
## $ Payment.Type : chr "Credit Card" "PayPal" "Debit Card" "PayPal" ...
## $ Order.Status : chr "Completed" "Refunded" "Cancelled" "Pending" ...We will change a few columns type into the correct type
# Changing Data Type
ecom_clean$Product.Category <- as.factor(ecom_clean$Product.Category)
ecom_clean$Order.Date <- as.Date(ecom_clean$Order.Date, "%Y-%m-%d")
ecom_clean$Payment.Type <- as.factor(ecom_clean$Payment.Type)
ecom_clean$Order.Status <- as.factor(ecom_clean$Order.Status)We will check is there any missing value in the dataframe
# Check for Missing Value
colSums(is.na(ecom_clean))## Order.ID Product.ID Product.Category Quantity
## 0 0 0 0
## Unit.Price Total.Price Order.Date Customer.ID
## 0 0 0 0
## Payment.Type Order.Status
## 0 0anyNA(ecom_clean)## [1] FALSEThere is no missing value in the dataframe.
We will remove unused columns
#Removing unused columns
ecom_clean <- ecom_clean[ , -c(1, 2, 8)]#Rechecking Data Type After the Change
str(ecom_clean)## 'data.frame': 10000 obs. of 7 variables:
## $ Product.Category: Factor w/ 6 levels "Beauty & Health",..: 6 5 1 2 4 5 3 3 6 6 ...
## $ Quantity : int 4 5 3 2 3 2 2 2 5 4 ...
## $ Unit.Price : num 154.1 275.5 56.4 176.4 409.9 ...
## $ Total.Price : num 616 1378 169 353 1230 ...
## $ Order.Date : Date, format: "2023-08-02" "2023-09-03" ...
## $ Payment.Type : Factor w/ 4 levels "Credit Card",..: 1 4 2 4 1 4 1 1 4 4 ...
## $ Order.Status : Factor w/ 4 levels "Cancelled","Completed",..: 2 4 1 3 1 1 3 4 4 4 ...Data type is changed and no missing value. Data is set and ready to be analyzed.
2 Data Explanation
2.1 Data Insight
summary(ecom_clean)## Product.Category Quantity Unit.Price Total.Price
## Beauty & Health :1614 Min. :1.00 Min. : 10.07 Min. : 10.09
## Books :1634 1st Qu.:2.00 1st Qu.:129.21 1st Qu.: 284.39
## Clothing :1704 Median :3.00 Median :251.67 Median : 602.82
## Electronics :1665 Mean :3.01 Mean :253.28 Mean : 762.72
## Home & Kitchen :1664 3rd Qu.:4.00 3rd Qu.:378.26 3rd Qu.:1129.88
## Sports & Outdoors:1719 Max. :5.00 Max. :499.96 Max. :2499.78
## Order.Date Payment.Type Order.Status
## Min. :2023-01-01 Credit Card:2453 Cancelled:2537
## 1st Qu.:2023-03-31 Debit Card :2529 Completed:2457
## Median :2023-07-01 Gift Card :2445 Pending :2575
## Mean :2023-06-30 PayPal :2573 Refunded :2431
## 3rd Qu.:2023-09-30
## Max. :2023-12-31Insight that we can get from summary above are:
- First order was placed on 1 January 2023
- The most popular Product Category was from ‘Sports & Outdoors’
- The least popular Product Category was from ‘Beauty & Health’
- The minimum quantity placed in a single order was 1 unit and the maximum quantity placed in a single order is 5
- There is a single order that cost 2499.78 total price
- From 10000 order placed, 2457 order was completed, 2537 are cancelled, 2575 are pending, 2431 are refunded
- The most popular payment type is by using PayPal followed by Debit Card
2.2 Checking data outlier
Checking is there any outlier in the total price
#Checking any outliers in the Total Price data
boxplot(ecom_clean$Total.Price, horizontal = T)
Based on boxplot above, we knew that there is some outliers in ‘Total.Price’. We knew also that the data distribution is Right Skewed distribution.
3 Business Case
- What is the average total price that was made during the period of time?
#we use median instead of mean because of the outlier
median(ecom_clean$Total.Price)## [1] 602.8234602.8234 is the average total price that was made during the period of time
- What is the correlation between Quantity and Total Price?
cor(ecom_clean$Quantity, ecom_clean$Total.Price)## [1] 0.5980247The correlation between Quantity and Total Price is positive moderate.
- How much revenue does the shop get at day1 of their selling?
day1 <- ecom_clean[ecom_clean$Order.Date == "2023-01-01" & ecom_clean$Order.Status == "Completed", ]
summary(day1)## Product.Category Quantity Unit.Price Total.Price
## Beauty & Health :0 Min. :2.0 Min. :201.5 Min. : 403.1
## Books :1 1st Qu.:2.5 1st Qu.:242.0 1st Qu.: 665.8
## Clothing :0 Median :3.0 Median :282.5 Median : 928.6
## Electronics :0 Mean :3.0 Mean :282.5 Mean : 928.6
## Home & Kitchen :0 3rd Qu.:3.5 3rd Qu.:323.0 3rd Qu.:1191.3
## Sports & Outdoors:1 Max. :4.0 Max. :363.5 Max. :1454.0
## Order.Date Payment.Type Order.Status
## Min. :2023-01-01 Credit Card:0 Cancelled:0
## 1st Qu.:2023-01-01 Debit Card :0 Completed:2
## Median :2023-01-01 Gift Card :1 Pending :0
## Mean :2023-01-01 PayPal :1 Refunded :0
## 3rd Qu.:2023-01-01
## Max. :2023-01-01sum(day1$Total.Price)## [1] 1857.105sum(day1$Quantity)## [1] 6Total revenue that the shop get from day 1 of selling is 1857.105 from ‘Books’ and ‘Sports & Outdoors’ category with total quantity of 6 items payed with Gift Card and Paypal
- Which category that gives the most total price in a single transaction?
ecom_clean[ecom_clean$Total.Price == max(ecom$Total.Price), ]The order was placed for ‘Home & Kitchen’ category with total quantity of 5 items .Payed using Credit Card but unfortunately the order was asked for refund.
- Which category that gives the most total price in a single completed transaction?
complete <- ecom_clean[ecom_clean$Order.Status == "Completed", ]
complete[complete$Total.Price == max(complete$Total.Price), ]The order was placed for ‘Electronics’ category with total quantity of 5 items. 498.89 each with total price of 2494.45 payed using Gift Card