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:
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.
Pivot tables allow you to put data into four sections:
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
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.
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.
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.
Discover Us on our PPC Den Podcast
If you enjoy supplementing your long reads with audio, we cover this topic on our podcast as well.
Listen to it in the episode below or find us on your favorite streaming platform, like Apple, Google, Spotify, and more!
- 1:10 Intro
- 4:30 Pivot tables
- 12:15 Optimizing automatic campaigns with pivot tables
- 16:00 Benefit of single product ad groups (SPAGs)
- 27:00 How to download all of your auto targets
- 27:50 Why consolidating your data is effective
- 29:20 How Ad Badger can automate this process
- 30:10 Closing thoughts
- Prime Day Preparation: Capitalizing on the Most Important Days of the Year
- Ad Badger & Bobsled Marketing: Staying on the Cutting Edge of Amazon PPC
- What Do I Do When a Keyword Doesn’t Convert?
- Keyword Cannibalization: Good or Bad?
- How One Keyword Can Apply to Multiple Products
- Ad Badger & TurnKey: Amazon Product Listing Optimization
- Seven Habits of Highly Effective Amazon PPC-ers
- Ad Badger and ZonGuru: Boost Your SEO with High Value Keywords
- Answered Prayers: Negative ASIN Targeting Comes to Auto Campaigns
- Much Ado About Budgets
- How to Get Over 100% Conversion Rates on Sponsored Display Ads
- New Launch Strategy for New Products: Skip the Auto?
- Amazon PPC Data Trends to Shape Your 2020 Strategy
- How Does Amazon PPC Work With Misspellings?
- Following Amazon’s Suggestions? The Mysterious “Suggested Bid”
- Negative ASIN in Amazon PPC Auto Campaigns: Is it Possible (Part 2)
- Negative ASIN in Amazon PPC Auto Campaigns: Is it Possible (Part 1)
- When You Should DIY Your PPC (and When You Shouldn’t)
- An Introduction to Targeting Options on the Amazon DSP
- Optimizing on a Hunch
- 5 Things You Don’t Know About Product Targeting
- Amazon PPC Stats During COVID-19| April 2020
- Sponsored Brand Ads Update: Now You Can Edit Creatives
- Amazon PPC Stats During COVID-19
- Seller Central Campaign Manager is Moving to Amazon Ad Console in 2020
- Taking Your Amazon Products & Campaigns Global: A Primer
- Best Practices for Selling on Amazon During COVID-19
- Principles and Mindset for Amazon Marketers During Coronavirus
- Coronavirus (Covid-19) and Your Amazon PPC Campaigns
- Incorporate “Adjust Bids by Placement” into Your Keyword Bids (Part 2): Multi-Keyword Campaigns
- Incorporate “Adjust Bids by Placement” into Your Keyword Bids (Part 1): Single Keyword Campaigns
- Grow Your Amazon Campaigns with Demographics Data
- What Consumer Behavior Says About You: Amazon Brand Analytics
- How to Raise Lifetime Value with Market Basket Report
- Search Frequency Rank: How to Identify Most Important Keywords
- Long Tail Search Terms: Amazon PPC Silent Killer
- 7 Tips to Get More Clicks on Amazon Ads
- LIVE Amazon PPC Campaign Audit
- Amazon PPC Keyword Research in 2020: RPSB Revisited
- Our Top Predictions for Amazon PPC in 2020
- 7 Bad PPC Habits to Kick in 2020 (And 3 Good Ones to Start)
- The PPC Tasks We Put on Our Project Management Tools
- A Data- Oriented Approach to Advertising in December
- The Wait is Over… Search Term Reports for Sponsored Brand Ads are Here
- What to Do When It’s Time to Sell Your Amazon Business with Coran Woodmass
- Ramping Up for Cyber Monday and Black Friday with Data-Driven PPC Strategies
- Amazon PPC Campaign Structure: 6 Layers of Complexity
- Amazon PPC Today vs. Yesterday: What’s Changed, What Hasn’t
- Make Your Best Keywords Better with Single Keyword Campaigns
- 6 Levels of Amazon PPC Mastery
- How to Advertise Commodity and Unique Products with Lukas Matthews
- Amazon SEO – Bridging the Gap with PPC
- Amazon’s New Sponsored Display Ads (Beta)
- Listener Q&A: Optimizing Placement Settings
- The Problem With Optimizing Low-Converting Products (And How to Solve It)
- 4 Reasons Why Lowering Your Bids Won’t Always Lower your ACOS
- What to Expect From 30 Days of Running Amazon Ads
- Should You Segment Your Branded Keywords in Amazon Ads?
- Promoting your Products with Amazon Coupons
- Three Effective Ways to Optimize Amazon PPC Bids
- 4 Things Amazon Does Better than Google and Facebook
- Amazon DSP with Kiri Masters
- Increase Conversion Rates for Sponsored Brand Ads
- Clickfraud, Who to Hire, and More Common Amazon PPC Questions
- A Data-Driven Approach to Prime Day PPC
- Improving Your Account With Amazon Reports
- The Complete Guide to Self-Auditing Your Campaigns
- A Round-Table Discussion About Placement Settings
- My 5 Predictions for the Future of Amazon PPC
- The Importance of Indexation for Amazon PPC
- Click Through Rate (CTR) Rundown
- An Introduction To Bulk File Operations
- The Latest Sponsored Brand Ad Updates
- Defining Your PPC Goals & Setting ACOS Targets
- Making Sense of New to Brand Metrics
- Should You Bid on Competitors’ Branded Keywords
- Our Gripes About Amazon Sponsored Brand Ads
- Amazon Advertising Launch Strategy for New Products
- The Ultimate Amazon PPC Roadmap
- How to Scale Using PPC – A Case Study
- The Star-Crossed Lovers (Organic & Paid Traffic)
- Cranking Up Conversion Rates
- All Things Negative Keywords
- The Dreaded Amazon Data Reporting Delay
- What We Love About Amazon PPC
- First Look on New Bid Options in Amazon
- Dissenting Thoughts on PPC Budgets
- The Strangest, Most Popular PPC Strategy: The Keyword Dump
- Product Targeting – Into the Great Unknown
- Campaign Naming Systems
- Hitchhiker’s Guide to Sponsored Products (Part 2)
- Hitchhiker’s Guide to Sponsored Products (Part 1)
- Amazon PPC Advertising Stats
- The Advanced Basics of Amazon PPC
- Amazon’s New Product Targeting Features
- The Bid+ Conundrum
- Why We’re Living In The Golden Age of Amazon PPC
Watch Mike & Stephen on YouTube
If you enjoy supplementing your long reads with video, well, hot diggity dog, you’re in luck! We cover this topic on our YouTube channel too.
Watch it below and please don’t forget to ‘like’ and subscribe.