Backgroud
The e-commerce sales data is stored in two tables in the database, one is the order data table, the other is the user data table, and the two tables are connected by the user name.
Overview
- Orderinfo is an order data table with 539,414 rows and 5 columns, mainly used to store information for each order.
-
Userinfo is the order data table with 101,535 rows and 3 columns. It is mainly used to store information about each user.
image.png
image.png
image.png
Business needed statistics are as follows:
- Monthly consumption
- Collect monthly repurchase rate and repurchase in another month rate of users
- Comparison of consumption times between male and female and top ten male and female users
- User life cycle
- Make statistics of different age groups and difference in the consumption amount of users
- Count the consumption amount contributed by the top20% users
1.Monthly consumption
Excluding incomplete data from May,2016, the number of consumers fell slightly in April, down nearly 20 percent from the previous month.
2.Collect monthly repurchase proportion and repurchase in another month proportion of users
-
Repurchase Rate
The repurchase proportion was 30.87% in March, 23.39% in April and 16.67% in May, showing a downward trend.
image.png -
repurchase in another month proportion
The repurchase in another month proportion in March was 23.94%, which means that those who bought their products in March bought them again in April. Due to incomplete data of May, the repurchase rate of April is low and has no reference.
image.png
3.Comparison of consumption times between male and female and top ten male and female users
The average number of individual consumption of females was 1.9459, slightly higher than that of males (1.9312).
Male users with the highest spending power is ID57282, spending 465,762.00 yuan in total.
4.User life cycle
- The total number of customers with purchase times greater than or equal to 2 is 32,465.
- According to the statistics, about 26% of customers did not buy the company's products for two months after buying many times on the first day.
-
The number of users whose life cycle is within 10 days accounts for the largest proportion of 46.43%, while 11-20 days accounts for 21.10%. Only 2% had a life span greater than 50 days.
image.png
5.Make statistics of different age groups and difference in the consumption amount of users
-
The middle-aged group (35-59 years old) is the main force of consumption, with the highest total consumption amount and consumption unit price, followed by the young and the elderly. Children consume less, but consumption unit price is higher than teenagers.
image.png
6.Count the consumption amount contributed by the top20% users
- According to statistics, the top 20% customers, accounting for 75.8% of the total consumption amount, are the company's important value customers, in addition, the top 38.58% customers, covering 80% of the company's sales.
