How do I lower my average cost over sale (ACoS)? This is the question I hear most often when working on Amazon Advertising accounts for clients. It can be a challenging question to answer as there are often several factors going into the ACoS. However, in my time managing Amazon accounts I’ve come up with a simple 4-step Excel tool that lowers overall cost and helps decrease the ACoS.
This tool is most impactful with manual, keyword-targeted campaigns. If you only using automatic campaigns, then my first step to lowering ACoS would be expanding to campaigns where you choose the keywords being targeted, pulling the best performing search terms from your automatic campaigns. If you are using manual campaigns then let’s dive in!
Step 1 – Downloading the Reports
Download a Targeting report for your Sponsored Product campaigns (or a Keyword Report for Sponsored Brand campaigns) going back the last 90 days of data. Once you have downloaded the report, you are going to want to clean it up a bit as you start with 22 columns of data and you only need eight of them for the tool. The columns you want to end up with are:
- Campaign
- Ad Group
- Targeting
- Match Type
- Spend
- Total Advertising Cost of Sales (ACoS)
- 7 Day Total Sales
- 7 Day Total Orders (#)
Step 2 – Filtering For Poor Performing Keywords
Once you have cleaned up your report, you should see something similar to this:
The next step is to put a filter on your columns and filter by the Total Advertising Cost of Sales column. You will want to filter for keywords that have an ACoS beyond your goal. In the case of this account, it is any keyword with an ACoS greater than 40%.
These are the keywords you want to pause as they are underperforming for the account based on the historical data. You can do so via the bulk upload tool on Amazon.
The following steps are how to then lower costs efficiently by finding the average daily spend of the paused, poor performing keywords and subtracting that spend from the campaign budgets.
Step 3 – Finding Your Wasted Spend
After you filter for your keywords performing under goal and paused them in the account, go back to your Excel file and create two new columns to the left of the Total Advertising Cost of Sales (ACoS) column. The two new columns will be the Avg. Monthly Spend and the Avg. Daily Spend.
In the Avg. Daily Spend column you are going to take the spend in the Spend Cell and divide by 3 to get the average monthly spend for the keyword. Since you used 90 days of data, this gives a rough average of a month of spend.
For the Avg. Daily Spend column you are going to take the calculated average monthly spend and divide it by 30.4, the average days in a month.
The Avg. Daily Spend column now shows your wasted spend per day on each poor performing keyword. The final step is to sum the wasted spend for each campaign to find how much you should decrease your budget per each campaign to take away the wasted spend from those campaigns for use in new campaigns or top performing campaigns.
Step 4 – Finding Your Savings
To find how much you should reduce your campaign budgets by, create a new sheet and add a table with all your campaigns listed out and a second column titled Daily Budget Reduction.
You will then enter a SUMIF formula into the Daily Budget Reduction Column of the table. Following the example, it will look as follows:
=SUMIF(RANGE!$A$1:$A$22, TABLE!$A2, RANGE!$G$2:$G$22)
The first RANGE is covering the Campaign name in our original sheet, matching the campaigns to the Table we created on our new sheet and summing all the data that matches the campaign name. Then drag down to capture the data for all of your campaigns.
To break it down further, for the first RANGE you will extend it to include all the report data. In my case, I only had 22 rows of data, but you might have 220. Make sure it goes all the way down the list. The Table I recommend keeping as is, but if you do move it ensure the Table begins on the first Campaign name in the table, otherwise it will not drag down correctly. The final RANGE is summing the Avg. Daily Spend data. Again here, check that you are capturing all the cells in the report with spend data or your numbers will not be accurate. A good way to check is seeing if the Ranges have the same number of cells, so RANGE one ends at $A22 and Range two ends at $G22. You know the data is all there and being summed.
Once you have completed this step you will end with a table that shows you how much you should reduce your campaign budgets. Depending on the size of your campaigns you can manually enter the data or use the Bulk upload tool from Amazon. In the example, we end up with $52 in savings that we can put toward our new or top performing campaigns.
Things To Watch Out For
When running this test, any automated campaigns will have the Targeting show an * instead of a keyword. You can filter these out of the results as this tool does not factor in the search terms coming from automatic campaigns and will not be able to process the decrease in the bulk upload.
Another issue that might occur is that the “saved” budget will completely wipe out the daily budget of a campaign. This isn’t a bad thing as it shows the entire campaign is inefficient. Depending on the product being targeted, it might do better to pause the campaign and put the budget elsewhere.
I would also recommend filtering out Branded campaigns from this tool as well, if possible. Brand protection is a key strategy on Amazon where there is an exponential amount of competition. I rarely recommend decreasing aggressive bids on Branded terms as that allows competitors to more easily enter your space and take away your sales.
Conclusion
While a simple tool, this process allows the keywords that are performing below the ACoS goal in the campaigns to continue spending while you can use the “saved” budget on new tests or expansion in the account. We tested this on two accounts and discovered ~$3K/month in previously wasted spend in one and ~$5.5K/month in wasted spend for the other. We put the saved budget toward new product launches and reported a 2-10% decrease in ACoS across the original campaigns. From my experience, it is worth testing in your account and seeing what kind of results you get.
Have you tried something like this before? What do you do to lower ACoS? Reach out to me on Twitter and let me know your thoughts!