How to conduct customer price distribution analysis: dual perspectives of time and region

In cross-border e-commerce operations, through detailed analysis of order reports, we can gain an in-depth understanding of customer purchasing behavior in different time periods and regions. This article will introduce how to analyze customer price distribution from two dimensions: time and region.

1. Price time distribution analysis

Data preparation

First, download and open the “User Price Distribution Analysis” Excel sheet. The table contains three types of data: “purchase-date” (purchase time), “item-price” (item price), and “ship-state” (shipping state). For time distribution analysis, we mainly focus on “purchase-date” and “item-price”, so the “ship-state” column can be deleted.

Data cleaning

For “purchase-date”, the following cleaning steps need to be performed to extract useful information:

1. Use the “RIGHT” function in Excel to remove the “2019-03-29T” part in the “2019-03-29T16:34:49-07:00” data;
2. Use the “LEFT” function again to remove “-07:00” from “16:34:49-07:00”;
3. Finally apply the “LEFT” function again to reduce “16:34:49” to only keep the number of hours “16”.

Statistics and Visualization

After completing the data cleaning, follow the following steps to calculate the average customer price in each time period and draw a chart:

1. Select the first row of the “purchase-date” and “item-price” columns;
2. Click “Start”->”Sort & Filter”->”Filter” on the toolbar;
3. Filter out a specific time period (such as “00”) and calculate the average customer price during that period;
4. Repeat the above process for each hour, and finally use the line chart function of Excel to draw a trend chart of price changes per customer within 24 hours a day.

In addition, you can also obtain hourly order volume data through the same method, and combine it with the average customer price to create a combination chart to achieve more comprehensive data visualization.

2. Price distribution analysis by region

Data preparation and screening

Similar to the preparation stage of time distribution analysis, open the same Excel file and delete the unnecessary “purchase-date” column.

Calculate average customer price

Next, filter the “ship-state” column to calculate the average price per customer for each state. The specific steps are similar to the screening process in time distribution analysis and will not be described here.

Data visualization

Use the calculated results to create a histogram to visually display the differences in average price per customer by region.

In order to make the comparison clearer, the concept of “comparison number” can be further introduced: comparison number = average customer price – lowest average customer price. Assuming that the lowest average customer price is 19, the data is reorganized based on this formula and a new histogram is generated accordingly.

Through the above steps, cross-border e-commerce operators can deeply understand customer needs from both time and geographical perspectives, thereby formulating more effective market strategies.