Advertising SOP: Find Overoptimized, Underoptimized, or Wasted Spend Targets Using Bulk

Finding OverOptimized, UnderOptimized, and Non-Converting Targets with High Ad Spent Using Bulk File

Procedure

Step 1:

Download a bulk file for a date range that you choose - preferably 30 days of data.

Step 2: 

Open and apply the Macro filter ( CTRL + SHIFT + Q) If you haven't utilized the macro yet, please refer to the Advertising SOP for Excel Macro Usage: Streamlining Bulk File Filtering and Bulk Optimizations.

Step 3: 

Targets are now sorted by ACOS and wasted ad spend. We also have new columns here: Min, Max, and Bidding Strategy. For Min and Max: These columns calculate the minimum/lowest and maximum/highest values of the two data points, CPC and Current Bid.

Step 4: 

Looking for High/Low ACOS targets. 

• Arrange it based on the bidding strategy (in our example above, I opted for down only bidding first)

For Down Only: If looking for high ACOS targets, check if your MIN matches your current bid. That means your Max indicates your CPC, helping you decide if the bids are acceptable or if a further reduction is necessary. If your MIN is your CPC, then you have to pull the ACOS down.

If looking for low ACOS targets, confirm if your MAX aligns with your current bid. That means your Min signifies your CPC, assisting you in assessing whether the bids are satisfactory or if additional bid enhancement is required. If your MAX is your CPC, your current bid is low so you have to bid higher than your MAX.

For Up and Down: Seeking unoptimized Up and Down targets differs, as the Up and Down bidding strategy enables Amazon to increase your bid by up to 300%. Your adjustment is limited to the current bid, as the bid is multiplied, potentially raising your CPC beyond the current bid amount.

For Fixed bids: Bids and average CPC should align, unless you've recently optimized your bids. Adjustments to bids here should be kept minimal, typically around 3 to 5 cents. If there's a significant difference between CPC and current bids, it indicates that bid adjustments have been made.

Note: Please double check those campaigns with placement bid adjustment either manually or in bulk(after your bulk optimizations). Placement bid adjustments can increase your bids by up to 900%.

Step 5: 

Let's now move on to examining potential wasted ad spend. Filter out targets with zero orders and review the bids to ensure they are lower than the average CPC.

Establish criteria for identifying wasted ad spend, commonly done by taking the lowest product price and dividing it by 60%. For instance, if the product price is $19.99, the calculation would be $19.99 * 0.60 = $11.99. This becomes the initial benchmark for bid reduction. Alternatively, you can begin with a straightforward approach, such as starting with $5 or $10 for wasted ad spend. If the wasted ad spend is pretty huge, you can consider pausing it.

In the screenshot above you will see that I have already adjusted my Current Bid lower than my CPC in order to reduce the ad spending on these targets that are overspending and not getting any sales.

Step 6: 

Once you’re done with your bid adjustments,  remove the blank cells on your “new bid” column. Press Ctrl + shift + N so your filtered workbook will get transferred to a new workbook. This macro will also automatically put “update” under the operation column.

Step 7: 

Now, all you have to do is copy the bids from the “new bid” column and paste that to the bid column.

Step 8:

You can now save the file and upload it.