Analysis on the month-on-month and year-on-month growth in annual transaction volume of the cross-border e-commerce market

Data source and import

In order to analyze the summary data of monthly sales of 15 secondary categories under the first-level category “Sportswear/casual wear” in the cross-border e-commerce market from January 2018 to December 2020, we first need to Open the corresponding data source file. For market capacity analysis, “Project – Task – Market Capacity Analysis” should be opened; for market trend analysis, “Project – Task – Market Trend Analysis” should be opened.

Next, import the pivot table into Excel. The specific steps are: After selecting the table data, click the “Pivot Table” button in the “Insert” tab. In the pop-up “Create PivotTable” dialog box, select “Select a table or range” and “New Worksheet”, and then drag relevant fields such as “Year”, “Second Type”, “Trading Volume”, etc. into the appropriate labels box. When analyzing market trends, you also need to drag fields such as “first Type”, “quarter”, and “month” into the corresponding label boxes.

Pivot table settings

For market volume analysis, you need to set the “Value Display Mode” to “Sum Item” and set the “Percentage of Column Summary” and “Percentage of Difference” for the “trading volume” column in different years. Market trend analysis requires calculating the year-on-year and month-on-month changes in transaction amount, using the formulas “=(G15-G3)/G3” and “=(G4-G3)/G3” respectively, and ensuring that the results are displayed in percentage form.

Chart production and optimization

After completing the PivotTable setup, the next step is to insert the chart. Market capacity analysis recommends using “XY scatter chart”, that is, Boston matrix chart; while market trend analysis is more suitable to use “combination chart”, which includes “line chart”. After inserting the chart, you need to perform optimization operations, including adding necessary chart elements (such as axes, chart titles, and data labels), adjusting the display range of the axes, and specifying label options through the “Format Data Labels” task pane.

Data analysis conclusion

The Boston matrix chart helps identify secondary categories with a high proportion of transaction value and significant month-on-month growth, such as “Gym clothes” and “Sport hoodie/jumper”. At the same time, by comparing the year-on-year and month-on-month changes in transaction amounts at different time points, it can be found that there are obvious seasonal fluctuations in the market, especially in March and November each year, the transaction amounts increase significantly. In addition, the transaction value from 2019 to 2020 showed positive growth year-on-year.