Cross-border operators can also complete customer price distribution analysis through order reports. Unlike regional distribution analysis, customer price distribution analysis has two dimensional analysis methods: price time distribution and price regional distribution. Price time distribution analysis studies how the average customer price changes over time. Price regional distribution analysis studies the average customer price in different regions, which region has the highest average customer price, and which region has the lowest average customer price.
1. Price time distribution analysis
First, open the “User Price Distribution Analysis” worksheet. There are three types of data in the label, namely “purchase-date” (purchase time), “item-price” (commodity price), and “ship-state” (shipping state). When performing price time distribution analysis, the operator needs the data “purchase-date’ and “item-price”, so the “ship-state” column can be temporarily deleted.
For “purchase-date”, its data needs to be cleaned to filter out the unnecessary parts of the data.
Then you need to calculate the average customer price at each time point. There are many ways to calculate it in Excel.
Select the first two data in the first row, that is, the data in cells “A1” and “B1”, then select “Start” in the toolbar above Excel, then click “Sort and Filter”, and select the “Filter” option.
After the operation is completed, drop-down filter arrows will appear on the right side of the “purchase-date” and “item-price” cells.
Click the drop-down filter arrow of “purchase-date” and select “00”.
After selecting “00”, the “item-price” information of all orders generated at the time point “00” will be displayed in the figure. Then you can calculate the average customer price at the time point “00”.
After calculating the average customer price at each time point.
Use the line chart drawing function in Excel to make a line chart of the average customer price data at each time point.
In addition to calculating the average customer price per customer for a single day within 24 hours through the order report, you can also get the order volume at each time point based on the order report data. Based on the order volume data and average customer price data at each time point, you can use Excel to create a combination chart to visualize the data.
(1) Select all the data in the “Order Volume” and “Average Customer Price” columns.
(2) Select “Insert” in the toolbar above Excel, and then click “Recommended Charts”.
(3) Select “All Charts” at the top of the “Insert Chart” dialog box that pops up, then select “Combination Chart”, then click “Clustered Column Chart” at the top, set “Order Volume” to “Clustered Column Chart”, “Average Customer Price” to “Line Chart”, and set the secondary axis to “Average Customer Price”.
Finally, you can get a combination chart of the changes in order volume and average customer price within 24 hours of a day, as shown in Figure 6-22. Operators can change the color, text, design style, etc. of the chart according to their own needs.
2. Regional price distribution analysis
After completing the price time distribution analysis, you need to conduct regional price distribution analysis, open the “User Price Distribution Analysis” worksheet.
There are 3 types of data in the table, namely “purchase-date” (purchase time), “item-price” (commodity price) and “ship-state” (shipping state). When conducting regional price distribution analysis, the operator needs the data “item-price” and “ship-state”, so the “purchase-date” column can be temporarily deleted.
The next operation is to “filter” the “ship-tate”, and then select different regions to calculate their average customer unit price. After completing the “filter” operation and calculating the average customer unit price of each region. Use the data in the table to make a bar chart.
The bar chart can be used to compare the average customer unit price of each region, but because the data difference of the chart itself is not large, in order to facilitate comparison, the operator needs to define a “comparison number”, and its numerical calculation rules are as follows.
Comparison number = average customer unit price – lowest average customer unit price From Figure 6-25, we can see that the lowest average customer unit price is 19, so we can derive a new data table based on this value.
According to the “comparison number” value shown in Figure 6-27, the operator can get a more intuitive bar chart.
At the same time, the operator can change the color, text, design style, etc. of the above chart according to their own needs.