First, download and open the “User Price Distribution Analysis” Excel table.
There are three types of data in this table, 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 “shipstate” column can be temporarily deleted.
The initial order data is in the format of “2019-03-29T16:34:49-07:00”, and if you want to do user portrait analysis, the operator needs the order time in this data, that is, “16” in “16:34:49”, so you need to clean these data. The operator can use the “RIGHT” function in the Excel software to remove the “2019-03-29T” in the “2019-03-29T16:34:49-07:00” data.
Then use the “LEFT” function in Excel software to remove the “-07:00” in “16:34:49-07:00”.
Finally, use the “LEFT” function once to simplify the data of “16:34:49” to the hourly data of “16”.
After performing the above operations on the data in the “User Price Distribution Analysis” Excel table, delete the data that has no analysis value.
Then calculate the average customer unit price for each time period. There are many statistical methods in Excel tables, and the most commonly used statistical method is the “filter method”.
1 Select the first two columns of data in the first row, that is, the data in cells “A1” and “B1”, then select the “Home” tab in the toolbar, then click the “Sort and Filter” drop-down button, and select the “Filter” option.
2 After the operation is completed, two drop-down filter arrows will appear on the right side of the “purchase-date” and “item-price” cells respectively. Click the arrows to see the relevant pages.
3 Click the filter arrow of “purchase-date” and select “00”.
4 After selecting “00”, the chart page will display the “item-price” information of all orders generated in the “00” time period, and then the average customer unit price in the “00” time period can be counted.
After counting the average customer unit price in each time period from 0 to 23 o’clock, the information obtained uses the line chart drawing function in Excel software.
In addition to being able to calculate the changes in the average customer unit price of a single day for 24 hours through the order report, you can also get the order volume per hour based on the order report data (when calculating the average customer unit price, the number of customer unit price data in each time period is the number of orders in that time period).
Based on the order volume data and the average customer unit price data of each time period, use Excel to make a combined chart to realize data visualization. The operation steps are as follows.
The first step is to select all the data of “order volume” and “average customer unit price”.
The second step is to select the “Insert” tab in the toolbar above Excel and click “Recommended Charts”.
Step 3. In the pop-up dialog box, select “All Charts”, then select “Combined 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 will get a combined chart of the changes in order volume and average customer price within 24 hours.