Good article that walks through step by step data mining with bulk spreadsheets: 1. Download the Bulk Downloads Spreadsheet
After logging in to Amazon Seller Central, hover over Advertising and click Campaign Manager.
Select Bulk Operations.
Use a 60 days Date range, and Exclude Terminated Campaigns and Campaign items with zero impressions. Then click Create spreadsheet for download.
Once the spreadsheet is ready to download, click download.
2. Upload to Google Sheets
Open a new Google Sheet. Head to File and Import.
Click Upload and then Select the excel spreadsheet that you downloaded from Amazon Seller Central.
Insert a new sheet(s) and hit Import Data
3. Organize and Consolidate Your Bulk Downloads Report
Next, we are going to delete all the columns and rows that we will not be using. Remember the goal of a bulk download report is to figure out your keyword bids and Max CPC. First we are going to delete all columns that are not needed.
Delete the following columns
Record ID
Campaign ID
Campaign Daily Budget
Portfolio ID
Campaign Start Date
Campaign End Date
Targeting Type
SKU
Product Targeting ID
Total Units
Bidding Strategy
Placement Type
Increase bids by placement
Next, we are going to separate each product into its own sheet. To do this, highlight the entire sheet, and then click Data → Sort range
Then select Data has a header row, Sort by Campaign A→ Z, and finally Sort
Then simply copy and paste each product into its own sheet.
After that we want to eliminate all rows that are not keywords. This can be done by sorting by Record Type (column A) and deleting everything that is not a keyword.
Delete all the non-keyword rows.
Finally, sort by Match Type and delete all the negative keywords
Delete all the campaign negative phrase and campaign negative exact keywords
Now we have our final list of keywords that we want to figure out the maximum cost-per-click for.
4. Add Three New Columns And Calculate Max Cost-Per-Click
Add three new columns to the right to help calculate your Maximum Cost-Per-Click (Max CPC).
Label these three columns Clicks/Orders, Max CPA, Max CPC.
For Clicks/Orders simply make a formula to take clicks divided by orders. Highlight the cell that you want the formula to be in and then click the ∑ button. Then click the cell under Clicks, the / sign, and the Orders button.
Maximum Cost Per Acquisition (Max CPA) is the breakeven cost of the product that you are advertising for. For the example that we have been using, our Max CPA is $7.09
Calculate this by going to your listing with the Helium 10 Chrome Extension (it’s free) and put in your cost of the product you sell and use that number.
Maximum Cost Per Click is simply the Max CPA / Clicks/Orders. Use the same formula button that you did above and take the Max CPA divided by Click/Order
Use the little box to drag the two formulas you just created down all your search terms. WAIT to put Max CPA in at this point.
5. Sort Your Bulk Downloads Into 3 Sections
We are going to again highlight all and sort range again. We want to see which Ad Groups are still enabled or turned on, then what keywords are turned on, and finally the higher ACoS will be sorted to the top. To do this you will need to sort range by Ad Group Statues A → Z, then by Status A → Z, then by ACoS Z → A, which will look like this.
6. Create a Pause and Move Section for Keywords Above Target ACoS With More Than 5 Orders
For keywords that are above our target ACoS, but still make a lot of sales, they need to have a maximum bid (Max CPC) put on them in order to make sales but to turn them into profitable sales. We are going to divide these keywords out and title them Pause/Move because we will be pausing them in their current Ad group (and in the next step moving them into our BUY Ad group with a keyword cap bid (the calculated Max CPC)).
So we will highlight all the keywords that are above target ACoS and sort the range by Orders Z→ A, which in my example is Column M
7. Copy the PAUSE AND MOVE to BUY Ad Group Section and Create an Add to BUY Ad Group Section for the SAME Keywords that are Above Target ACoS With More Than 5 Orders
We will copy the EXACT same keywords that we calculated in the previous step and title this section Add, which means that we will be adding them to the BUY Ad Group. These keywords, like stated before, will have a bid cap to continue to make sales, but we are trying to make them profitable sales.
8. Create a Pause Section for Keywords Above Target ACoS With Less Than 5 Orders
Get Amazon Consulting at
0 Comments