Using Spreadsheets to Optimize Your Amazon Auto Campaigns

October 19, 2020
By The Badger

We get it. Spreadsheets aren’t the most interesting things in the world, but they are incredibly powerful tools in the Amazon PPC world. Not only can you track your performance with a variety of reports, but you can use this data to optimize hundreds of campaigns in seconds.

The Wonderful World of Pivot Tables

Pivot tables in Excel and Google Sheets are widely underutilized. Using pivot tables can make your life so much easier because they allow you to filter and manipulate data without creating new sheets.

If you haven’t used pivot tables before, let’s go through a brief overview of how they work.

How to Use a Pivot Table

Let’s paint a very simple picture. You’re selling fruits and vegetables at a farmer’s market, and you want to see how your daily revenue is affected based on a wide variety of factors. Do you want to input that data for five or six sheets to see which factor has the most effect? Of course not.

That’s where pivot tables come in. Pivot tables allow you to filter your data in a wide variety of ways and get answers to the important questions.

Here’s an example of your day-to-day data at this farmer’s market:

Very simple data sheet, but easy to use a pivot table to find some valuable information inside of it.

How can you find which days contributed the most to your monthly sales? Was a certain day of the week more popular than others? It’s very easy to make quick assumptions about these questions based on your day-to-day experience, but seeing these assumptions fleshed out in a pivot table makes it easier to act on them.

Let’s say you want to see how each item sells on a certain day of the week. If you don’t use a pivot table, you’ll have to extract the data with a filter and then create a new sheet to understand how a certain day of the week corresponds with total sales. With a pivot table, you can easily find the answer.

In just a few clicks, your data can be used to make important decisions about which product is in higher demand on a certain day of the week.

Pivot tables allow you to put data into four sections:

Rows
Columns
Filters
Values

For the above example, Fruit is used as a row. Day of the Week is in the column section, and Quantity Sold is in the value section.

The great thing about a pivot table is that you can make changes quickly. If you want to find the answer to another question, you can easily swap around rows, columns, and values. With a separate spreadsheet, this is much harder to accomplish.

Using Spreadsheets to Optimize Auto Campaigns

For automatic campaigns, using pivot tables is the best way to optimize your campaigns. Especially when you have campaigns that are just getting started, the combination of single product ad groups and pivot tables can give you the best glimpse into what is happening.

To illustrate the value of this strategy, let’s look at an example.

Let’s say you have 10 automatic campaigns. You have 10 single product ad groups (SPAGs) within each campaign, totalling 100 ad groups. For each ad group, you have 10 clicks.

If you look at each ad group, you’ll see four targeting options:

Close match
Loose match
Compliments
Substitutes

Clearly, once the ten clicks per ad group is spread across these four targets, it can seem impossible to make any decisions about these campaigns. There’s just not enough data, right?

Wrong! With pivot tables, you can aggregate this data and get the insights you need to optimize these campaigns.

Aggregating Your Data with Pivot Tables

In our scenario that we just laid out, everything is very simple. In the real world, things will be a little more complicated, but the fundamentals stay the same.

Let’s take a look at how a pivot table can give insights into your campaign performance across each targeting option.

Even with just two campaigns showing, you can still see how a pivot table can show a targeting option that isn’t performing well.

As you can see, even just looking at clicks can reveal an opportunity to optimize your campaigns. Of course, clicks don’t tell the entire story. You can use match type as a row and insert any of the other metrics on a targeting report as a value.

Using pivot tables to analyze single product ad groups is the best way to find out exactly what is happening inside your campaigns. Sure, the data aggregation that comes with only a couple of ad groups may seem great, but, if you want to diagnose a specific problem and optimize each product, single product ad groups are a necessity.

The amazing thing about this strategy is that you can drop in data from hundreds of auto campaigns and get your data into a pivot table with just a few clicks.

Key Takeaways

There’s two big takeaways from this blog. First, spreadsheets are extremely powerful tools. Every Amazon PPC-er should know their way around either Excel or Google Sheets. Creating pivot tables, while super effective, is just the tip of the iceberg.

Second, we cannot stress the importance of single product ad groups enough! Being able to optimize on a product by product basis is incredible, and by aggregating your data with pivot tables you won’t have to worry about your data being spread too thin.