In this post I will go over two different ways to analyze your PPC data to identify and cut wasteful spend from your Amazon PPC campaigns.
We will be looking at keywords which have generated no sales within the time frame being covered.
To get started you will need to download a 2 - 4 week bulk file from Campaign Manager. The date range you select should be based on the amount of volume your PPC does. Accounts with higher PPC volume can select a shorter time frame like 2 weeks and accounts with less volume will want to select a longer time frame like 4 weeks.
I recommend starting with 2 weeks and if the filters we apply generate little to no data then you can select a longer time frame to analyze.
To find your bulk file go to Campaign Manager and select Bulk Operations. Select the date range you want to cover and then hit "Create spreadsheet for download".
Open your bulk file and lets get started.
Part 1:
Select the top row and apply filter.
The first filters we will be applying will be used to find keywords with spend above our target Spend Threshold. We will use results to cut back spend to these keywords as they are not converting at a rate high enough to achieve our goals.
To calculate your Spend Threshold you will need to calculate your average order value from PPC. To calculate AOV look at the last 30 days data in campaign manager. Take sales/orders and this will give you your average order value (AOV).
Average Order Value = Sales / Orders
Example:
Average Order Value (AOV) = $58,058.60 / 2,635 = $22.03
Now lets calculate your spend threshold:
Spend Threshold = Target ACoS * AOV
Example: 30% * $22.03 = $6.61
You should now have your AOV and Spend Threshold Calculated. These value will be used going forward within formulas and filters so keep them handy.
Apply these filters to the bulk file:
Filters:
Record Type: Keyword
Spend: ≥Spend Threshold
Orders: 0
Copy the remaining data and paste it into a new tab. Do this twice. This will allow you to have a "Before" and "After" picture of the data. Use the "Before" tab to leave the data unchanged. Use the "After" tab to make the changes.
Next we want to calculate our new bids. The goal is to reduce the CPC (Cost Per Click) for these keywords and not completely kill all traffic from these keywords. By reducing the CPC this allows the keywords to test in different ad placements that may perform better. Also by reducing the CPC we automatically reduce the amount of spend to these keywords which will save you money.
To reduce the CPC we want to calculate our current CPC for these keywords and use a fraction of the CPC to assure any future clicks will result in a lower CPC.
In the "After" tab insert two columns at the end of the spreadsheet to calculate the CPC and adjust bids.
Calculate CPC:
CPC = Spend/Clicks
Calculate New Bid:
New Bid = CPC * .75
When calculating your new bid the lower the ratio of the CPC the more aggressive the bid cut.
Once your new bid has been calculated you can copy the column and paste values over to the "Max Bid" column.
Copy all the data in the "After" tab and paste it into a new excel workbook. This will be the workbook you upload to bulk operations. The other workbook will be used to save the before and after data to refer back to in case you need to revert the changes made.
*Make sure to delete all columns we added manually to calculated our CPC and New Bids before uploading the file to Bulk Operations.
Part 2:
* Note: In part 2 we will be using many of the same steps we used in part 1 so we will not cover those again in detail. Refer back to part 1 if you are unsure.
This is a more aggressive approach to cut spend and should be used if you are still looking to further cut wasteful spend after implementing Part 1.
In part 2 we are looking to see if your ad spend ratio for keywords in a testing cycle is to high.
Keywords are considered to be in a testing cycle if they do not have any sales. They are yet to prove themselves.
We consider the spend ratio to be to high if the resulting keywords spend is greater than 25% of your overall ad spend. If this is the case we want to reduce the amount of spend being allocated to these keywords.
Filter:
Record Type: Keyword
Orders: 0
Clicks: ≥1
Spend: < Spend Threshold (This will eliminate keywords we already optimized in part 1)
Calculate the cumulative spend of the resulting set of keywords and divide it by your total spend. If the ratio is greater than 25% then we are likely allocating to much spend to these keywords and need to reduce the CPC for these keywords in order to reduce the spend.
Copy the resulting keywords into a before and after tab like we did in part 1.
The new bid will simply be a fraction of the current Max Bid. By reducing our Max Bids we will reduce our CPC's and overall spend. Again the smaller the ratio of the Max Bid the more aggressive the bid cut.
Personally I like to use .8 or 80% of the current Max Bid as this is enough to reduce spend and CPC while still allowing these keywords to generate traffic. Remember we don't want to lower the bid to much where all traffic comes to a halt.
Example:
New Bid = Max Bid * .8
Take the resulting bids and paste values over the Max Bid column. Copy the "After" tab and paste it into a new workbook to be uploaded to bulk operations.
Hopefully you have found these optimization steps to be helpful and they bring you closer to meeting your PPC goals!