After downloading the search term report, you can get a table containing many elements such as customer search terms, display volume, exposure volume, and conversion volume.
Pivot table is a commonly used data analysis tool in Excel. It can quickly summarize detailed data tables in various categories. Click the upper left corner of the table, select all data, click the “Insert” tab in the toolbar, and click Pivot table to create it.
In the “Create Pivot Table” dialog box, you can change the data range, that is, which data needs to be used for summary, and you can also change the storage location of the Pivot Table. Keep the default options here to create a Pivot Table in a new worksheet.
After clicking the “OK” button, you can create a Pivot Table in a new worksheet. Through different settings of the Pivot Table fields, you can analyze advertising from different dimensions. First, add the name of the advertising combination in the row field, and add the display volume, click volume, and 7-day total sales volume in the value field to understand the advertising situation as a whole.
After the complete selection, the corresponding table will be automatically generated. At this time, select any value in the display volume, right-click, and select the descending button in the sort to get the relevant table.
At this step, you cannot intuitively see the exposure click rate, click conversion rate and ACoS. At this time, you can use the calculated field function of the pivot table to automatically calculate the above three values. Click the “Fields, Items and Sets” drop-down button in the “Analysis” tab, and select the “Calculated Field” option in the pop-up drop-down list to insert a custom field.
In the “Formula” text box, enter the click volume/impression volume by clicking, and set the field name to exposure click rate to add the exposure click rate field.
Similarly, enter the total sales volume/click volume in 7 days, and the cost/total sales volume in 7 days to add the click conversion rate and ACoS fields respectively.
After completing the settings, the exposure click rate, click conversion rate and ACoS will be matched in the value field using the formula calculation method. Click the value field setting and select the number format in the pop-up window to set these three values to percentage format.
Similarly, enter the total sales volume/click volume in 7 days, and the cost/total sales volume in 7 days to add the click conversion rate and ACoS fields respectively.
After completing the settings, the exposure click rate, click conversion rate and ACoS will be matched in the value field using the formula calculation method. Click the value field setting and select the number format in the pop-up window to set these three values to percentage format.
In order to avoid misunderstanding due to too much data, check the “Border Row” checkbox in the “Design” tab.
It is not difficult to see that the store’s skirt and top advertising combination has a good order performance, while the swimsuit, sweater, shoes and new product test advertising combination has not generated any conversions. Overall, the store’s exposure click rate and click conversion rate are relatively normal, but the ACoS reaches 84.03%. At this time, the core indicator of advertising optimization is ACoS. Except for new product test ads, all optimizations are aimed at saving money as the ultimate goal.
In addition to weekly analysis, you can also create a separate table to count daily advertising exposure click data. You can also combine advertising data with traffic data for easy reference at any time during daily operations.