How to create a pivot table for Amazon CPC advertising

Advertising is something that every seller will encounter during the operation process. Many practical articles of big sellers always say that advertising words need to be tried and optimized continuously, but how to analyze advertising data and where to start optimization? I believe that some novice sellers still have doubts about this. Even when the boss asks for specific data analysis and optimization plans, they don’t even know which data to check!

Today, we have organized some dimensions related to CPC advertising analysis, including how to create a pivot table, so that sellers can understand the data more clearly!

As an important basis for data analysis, let me first introduce to you how to make a pivot table.

First, we download the CPC advertising search term report in the background. If you don’t know where to download it, you can directly enter the advertising campaign management - click on the advertising report, select the required category in order, and download it. The operation is shown in the figure below:

Now that the advertising report has been downloaded, we can officially enter the stage of making a pivot table.

1. Create a PivotTable

1. Download the CPC advertising search term report, open it using Excel, select all data (ctrl+A), click "Insert-Pivot Table", "OK-Generate Pivot Table", and the operation is as shown in the figure below:

Drag the Campaign and Ad Group fields to the Pivot Area Filters;

Drag the Keywords field to Rows;

Drag the Impression/Click/Spend/Order Count/Sales Volume field to the Value field and select the Sum item, as shown in the following figure.

2. Key points about setting the sum item in the value field:

Click the inverted triangle on the right, select Value Field Settings, and set the value field summary method to Sum. The steps are as follows:

After setting the value field, insert a calculated field in the top toolbar:

Find "Field Item" in the table header and select "Calculated Field";

In the formula bar, click Insert Field or click the corresponding field, click OK, and set it as follows:

PS: The field cell format is percentage, with 2 decimal places, and the settings are as follows:

2. Pull data reference dimensions

1. Filter the campaigns where the products are located

2. Pull the summary analysis dimensions and sum them. Generally, "exposure/click/call fee/order volume/sales/conversion/click-through rate" are all common analysis dimensions.

3. Locate the campaigns you want to analyze

Add customer search term to the row label;

Sort by click volume (click any cell, right-click to sort - descending)

4. Common methods for mining keywords valuable to Amazon

1. Sort by conversion rate in descending order: Keywords with conversions can be focused on and expanded

2. Sort by click rate in descending order: select words with high exposure and clicks and those with strong relevance, and exclude those with occasional clicks and 1 click.

3. For keywords with high exposure, you can manually set a low price

5. Manual Ad Optimization

6. Automatic Ad Optimization

Industry Standard Ad Performance:

CTR0.5%

The CR conversion rate is 10%, and words with more than 2 times the natural conversion rate can be directly targeted.

acos: Different advertising cycles and products are different, and the normal standard is 15%.

Reference anker: (This is a perspective made from the CPC data of the anker backend I bought)