Data Analysis using MySQL

8 min readMar 28, 2023

Exploratory data analysis in E-commerce data

E-commerce has a significant impact on our daily lives in the current digital era. Businesses are gathering a ton of information on consumer behavior, sales patterns, and other topics as online shopping becomes more popular. Businesses must conduct exploratory data analysis (EDA) to make sense of this data and find insights and trends that can guide their decision-making. MySQL, a well-known open-source relational database management system, is a potent tool for EDA. This guide will cover how to connect to a database, query data, and do fundamental statistical analysis when using MySQL for data analysis in e-commerce. So let’s get started and learn how powerful MySQL is for analyzing e-commerce data!

We will use an E-commerce business transaction from online store. The data set comes from kaggle.com, below are the definition of each column in the dataset:

TransactionNo (categorical): a six-digit unique number that defines each transaction. The letter “C” in the code indicates a cancellation.
Date (numeric): the date when each transaction was generated.
ProductNo (categorical): a five or six-digit unique character used to identify a specific product.
Product (categorical): product/item name.
Price (numeric): the price of each product per unit in pound sterling (£).
Quantity (numeric): the quantity of each product per transaction.
Negative values related to cancelled transactions.
CustomerNo (categorical): a five-digit unique number that defines each customer.
Country (categorical): name of the country where the customer resides.

Here, I will not show anymore the data cleaning part, but you can see it in the code later.

Get Jhon Paul G. Kionesala’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Let’s check the dataset

SELECT *
FROM paul_portfolio.transactions_sales2
LIMIT 10;

Top 10 most highest transactions base on total sale

SELECT TransactionNo,
 ProductNo,
    ProductName,
    ROUND((Price * Quantity), 2) AS total_sale
FROM transactions_sales2
WHERE TransactionNo NOT LIKE '%C_%'
ORDER BY total_sale DESC
LIMIT 10;

This query can provide valuable insights into the top-selling products and transactions in our e-commerce business. We can see the product no. 23843 has the highest total sale. The result is below

2. Total product that are canceled in each country

SELECT Country,
 SUM(Quantity) AS Total_cancels
FROM transactions_sales2
WHERE TransactionNo LIKE '%C_%'
GROUP BY Country
ORDER BY Total_cancels;

The query shows the country that has many canceled transactions. By analyzing the output of the query, we can identify which countries have the highest number of canceled products. This information can be used to investigate the reasons for the cancellations and take corrective actions to reduce the number of cancellations in the future. The table below shows the result of query.

3. Top 10 most sold product and its total sale

SELECT ProductName,
 COUNT(ProductName) AS Total_Sold,
    ROUND(SUM((Price * Quantity)), 2) AS Total_sale
FROM transactions_sales2
GROUP BY ProductName
ORDER BY Total_sale DESC
LIMIT 10;

We can determine which products are the most well-liked and profitable for our e-commerce business by examining the output of the query. Our inventory control, pricing decisions, and marketing initiatives may all be made more effective with the use of this information. The result shows below

4. Top 10 countries that has most sales

SELECT Country,
 COUNT(*) AS total_sold,
    ROUND(SUM(Price * Quantity), 2) AS total_sale
FROM transactions_sales2
WHERE TransactionNo NOT LIKE '%C_%'
GROUP BY Country
ORDER BY total_sale DESC
LIMIT 10;

We may identify which countries are the most important markets for our e-commerce business by studying the query’s output. This data may be utilized to improve our marketing efforts, pricing tactics, and inventory management across the board. For example, if a specific country has a high number of successful transactions and total revenues, we might strengthen our marketing efforts there and modify our product offers to fit the needs of clients in that market. Result below is shown.

5. Average and total sale per year

SELECT YEAR(Date) AS year,
 COUNT(YEAR(Date)) AS transactions_per_year,
    ROUND(SUM(Price * Quantity), 2) AS total_sale_per_year,
    ROUND(AVG(Price * QUantity), 2) AS average_sale_per_year
FROM transactions_sales2
WHERE TransactionNo NOT LIKE '%C_%'
GROUP BY year;

The average sale in 2019 is 120.39, in 2018 is 106.61, that is 12.92% increase. We can say that this is the effect of pandemic. People choose to buy online for their own safety, Table shown below.

6. Sales retention per month

SELECT 'Febuary-January' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 1 THEN CustomerNo END) AS Previous_Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 2 THEN CustomerNo END) AS Current_Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 2 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 1) THEN CustomerNo END) AS Repeated_customers,
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 2 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 1) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 1 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2
UNION
SELECT 'March-February' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 2 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 3 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 3 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 2) THEN CustomerNo END),
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 3 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 2) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 2 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2
UNION
SELECT 'April-March' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 3 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 4 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 4 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 3) THEN CustomerNo END),
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 4 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 3) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 3 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2
UNION
SELECT 'May-April' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 4 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 5 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 5 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 4) THEN CustomerNo END),
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 5 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 4) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 4 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2
UNION
SELECT 'June-May' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 5 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 6 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 6 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 5) THEN CustomerNo END),
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 6 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 5) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 5 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2
UNION
SELECT 'July-June' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 6 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 7 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 7 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 6) THEN CustomerNo END),
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 7 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 6) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 6 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2
UNION
SELECT 'August-July' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 7 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 8 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 8 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 7) THEN CustomerNo END),
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 8 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 7) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 7 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2
UNION
SELECT 'September-August' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 8 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 9 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 9 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 8) THEN CustomerNo END),
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 9 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 8) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 8 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2
UNION
SELECT 'October-September' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 9 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 10 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 10 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 9) THEN CustomerNo END),
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 10 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 9) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 9 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2
UNION
SELECT 'November-October' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 10 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 11 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 11 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 10) THEN CustomerNo END),
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 11 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 10) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 10 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2
UNION
SELECT 'December-November' AS Month,
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 11 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 12 THEN CustomerNo END),
       COUNT(DISTINCT CASE WHEN MONTH(Date) = 12 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 11) THEN CustomerNo END),
       ROUND(COUNT(DISTINCT CASE WHEN MONTH(Date) = 12 AND TransactionNo NOT LIKE '%C_%' AND CustomerNo IN 
              (SELECT DISTINCT CustomerNo FROM transactions_sales2 WHERE MONTH(Date) = 11) THEN CustomerNo END) /
             COUNT(DISTINCT CASE WHEN MONTH(Date) = 11 THEN CustomerNo END) * 100, 2) AS RetentionRate
FROM transactions_sales2;

The query above shows the retention rate of the costumers.
Retention rate in customers is a metric that measures the percentage of customers who continue to do business with a company over a certain period of time.

A high retention rate indicates that a company is doing a good job of keeping its customers satisfied and engaged, which can lead to increased revenue and profitability. we can see a 45.69% in the month of November and October. but else is low indicating a low retention rate meaning there is a problem with the service or products of the store.

7. The rate of cancelation in each country

SELECT Country,
    COUNT(TransactionNo),
    COUNT(CASE WHEN LEFT(TransactionNo, 1) = 'C' THEN 1 END) AS cancels_per_Country,
    COUNT(CASE WHEN LEFT(TransactionNo, 1) = 'C' THEN 1 END) / COUNT(*) * 100 AS Cancel_percentage
FROM transactions_sales2
GROUP BY Country
ORDER BY Cancel_percentage DESC;

As we can see, the cancelation rate differs from every country. Like in USA, there is 29% of cancelation rate meaning there are not satisfied with the product. It is important for the company to investigate the reasons behind the high cancellation rates in certain countries and take appropriate actions to reduce them.

Conclusion

We can tell that the sales are really good for the certain products and the average sales in 2019 is really good compare to 2018 also there are many products that are cancel lastly some customers are not buying again based on the sales retention.

Recommendation

To boost sales and lower cancellations, it is advised to concentrate on enhancing customer happiness and addressing any problems with product quality or customer service in certain nations. Also, determining popular products and studying sales trends can assist in stocking and growing the company.

Paul-portfolio-in-MySQL/Portfolio_sales_transactions.sql at main · linux3/Paul-portfolio-in-MySQL

You can't perform that action at this time. You signed in with another tab or window. You signed out in another tab or…

github.com

The full code with data cleaning.

Jhon Paul G. Kionesala

Data Analysis using MySQL

Get Jhon Paul G. Kionesala’s stories in your inbox

Paul-portfolio-in-MySQL/Portfolio_sales_transactions.sql at main · linux3/Paul-portfolio-in-MySQL

You can't perform that action at this time. You signed in with another tab or window. You signed out in another tab or…

Written by Jhon Paul G. Kionesala

Responses (1)

More from Jhon Paul G. Kionesala

Power Bi Dashboard

HR Analytics / Employee Dashboard in Power BI

Click here too see the full dashboard

Basic relational data base for employees sales

For portfolio purposes

Recommended from Medium

The 12 Data Architecture Patterns Every Data Engineer Should Master in 2026

From Lakehouse and Medallion to Data Mesh and Event-Driven systems — understand the architectures behind modern data platforms.

The Medallion Architecture Reconsidered: What It Solved, and Where It’s Cracking

Rethinking Bronze, Silver, and Gold for the AI era

8 New AI Terms You Should Know to Be Ahead of 90% of People

A few more AI terms to know beyond tokens, prompts, and hallucinations.

9 technologies that changed data engineering forever

Without them, we would live in a very different world

10 AI Tools Everyone Will Be Using in Six Months, But Almost Nobody’s Found Yet

You’re going to see these in someone’s toolkit next year and wonder why you didn’t hear about them first. Here’s your head start.

Andrej Karpathy’s New suggestion- Ask AI output in ASD-STE100 format and not in Text format

A few days ago, Andrej Karpathy shared a perspective that immediately clicked with how my own daily workflow has been evolving. His premise…