How to Audit Amazon PPC Campaigns Using Bulk Files and Pivot Tables

How to Audit Amazon PPC Campaigns Using Bulk Files and Pivot Tables Speaker 1: What's going on, Badger Nation? Welcome to The PPC Den podcast, the world's first and longest running show all about how to make your Amazon advertising life a little bit easier and a little bit more profitable. Let's jump in. We got camera one, we got camera two, and we got a slide deck for you. My friends, if you are optimizing Amazon PPC in any capacity, learning how to audit them is key. So today on the show, we're going to be auditing our PPC with bulk files. And the goal here is to get a springboard so that you can actually manage better, so that you can understand your account much better. And you ever could before. And if you think about the way that most people interact with their PPC accounts, they log onto Amazon. Okay, great. They click on Amazon Advertising. Okay, cool. Amazon does that annoying thing where it asks you to sign in when you switch from Seller Central to Amazon Advertising sometimes. Very frustrating. You click. Save my login. It doesn't. It lies to you. Today we're going to cut through all that stuff and we're going to learn how to audit our account so that we can get keener, clearer insight. So often we're wondering what the heck is going on in my campaign. This is going to help answer that. So it's really about building a springboard. So, the TLDR here, that stands for too long, didn't read. An audit is just a quick way to find out what to go optimize. It gives you a clearer sense of what actions to go and take. That is the goal behind any audit. You cannot simply audit an account and be done with it. You observe something in your audit, you think of your recommended fix, and then you do that action and you watch it change over time. If I were to share the truth about auditing, maybe this isn't a truth, maybe everyone knows this. It's really less about being good or bad. Yes, you discover some obvious areas you'd want to optimize, but in almost every metric, And dimension that we look at, it's more about asking the question, is my PPC doing what I want it to do? Is my Amazon advertising helping me get closer to my goals or further from my goals? So for example, when you look at this data, you're going to look at cross sections of it. A ranking campaign with a high type of search is obviously going to be very different than a profit driven strategy, very different than a launch phase strategy. So it's unique for every business, every product, And when you interpret the data, that's where the real power comes in. So what is an Amazon PPC audit? It's really about taking a monolithic account and then Slicing it up, crossing it by a bunch of dimensions, crossing it up by different dimensions like a day of the week performance, match type performance, placement performance, so that you can get clued in into performance at the highest level, tie it to your business goals and profitability, tie it closer to your goals. And then for every piece of data we look at, we ask ourselves, is this the intention that I had when I started this campaign? Is this doing what I want it to do? Are my metrics in alignment with my goals? And that's where sort of the beauty and science comes in of Amazon Advertising. This excites me a lot. Because this is the primary way that I interact with PPC accounts. People come to me, like the good people out there in Badger Nation might come to me and they might say, Hey, Mike, how you doing? I listen to the show. Thanks so much. Thanks for listening. They say I'm struggling with PPC campaigns in some way. I do a lot of what we're about to see here. I needed to be able to develop a way to audit things, learn as much as I can, as fast as I can. And then I prepare a presentation with recommended actions. And then I check back in with them. And I get to see so many accounts. I get to learn so much. I get to study so much. So if I see something that has an out of whack ACOS, I look at it from various dimensions, come with a game plan, that person goes off and does that game plan, and then I get to follow along and check back in. So it's awesome. So, this is one of my rapid ways, which is a bulk file download. Now, prepare for your audit. Go ahead and pause the video after what I'm about to say. Go ahead and pause the video and go and download a bulk file from Amazon. If you don't know where it is, it's just right there on the side. Click on advertising and then like bulk operations is what they call it. Commonly known as, colloquially known as, bulk files. We're going to be using a bulk file because it contains all of your sponsor product search terms, as well as all of your sponsor product entities. So your campaigns, your ad groups, your keywords, your product targets, so on and so forth, your placement modifiers. So when you go ahead and set this up, date range, Depending on the size of the account, a bulk file can take a while. I think it's gotten slower over time. So if you have a really, really large account, you might do a 14-day audit. Play with the settings to only include enabled things. Don't include things with no impressions. There's some settings that you can look at to make the file smaller if you're struggling with big size. It is helpful to have a powerful computer if you're going to get into some of this stuff because sometimes the files are actually quite large. But for this point, you're just going to want to select Sponsored Products Data and Sponsored Products Search Term Data and Placements Data. So you'll get that info. You'll hit Generate My Bulk File, and then you'll wait. You can go get some coffee. So go do this now. Maybe you can come back and do this exercise together. Hey, what's up, friends? If you want some help with your bulk files, please be sure that you are on our newsletter mailing list. Just go to adbadger.com and find the spot to give your email. We have a big checklist talking about all of our episodes. They're categorized. But if you need help with a bulk file, we have a template for you and it's as simple as a drag and drop where you can get some really good analysis on your account. So it's something that I use regularly. It's something that I built for myself and I hope to share it with you. Basically getting lots of insight really fast in a simple drag and drop. Have a good one. Let's get back to the show. And we're back. It's a couple of minutes later. Hopefully your file is done. I was just talking to Sean Stone, friend of the show. It takes him about 24 hours to download a bulk file on some very large accounts. I definitely see that. So it's also helpful if you have a team. To maybe assign someone with just going and generating the bulk file once a month, once a week for you. Number one, it always gives you a save state. So if you ever wanted to reset your account, you can always do that. That's a topic for another day. But in addition, it just makes it so that when you want to go and get tabs on your account, it's sitting there waiting for you. Let's jump in here. Let's jump in. So after you get your bulk file, The goal of what we're going to be doing is analyzing at a high level, get a lay of the land and summarize it. So what we see here is the badger looking out Indiana Jones style, getting the lay of the land so we can begin to summarize it. We're going to be doing a lot of our auditing from a top level view, looking down into things. And in addition, when we do things like campaign structure analysis, that's really what you look at a lot. It's like the composition of the account, the way that the auto-targeting and the manual targeting are all configured, the way that your match types and your placements and your auto-targets are all configured and performing. That kind of stuff can be really, really insightful, really, really fast. So we're going to be looking at some of these, and I'll probably make more of these bulk file auditing and maybe auditing with some other spreadsheets just because it's so useful and so valuable to get so much insight so, so, so rapidly. I was talking to him about this with a friend of the show, Brent from CellarPlex, and he said a comment that I absolutely loved, like, wow, in the day and age of powerful software, powerful AI, The spreadsheet based analysis gave such intense, intensely good information so fast. And that's the power. I mean, that's why spreadsheets were invented. And like getting all of your data in a spreadsheet is actually really powerful. So even in a day and age where tools are getting more and more advanced, AdVenture is a bit advanced. But even with all these tools, coming back to a bulk file can be a really nice, refreshing take. Now, When we do our bulk file analysis, we're going to be using the humble pivot table, the pivot table. If you don't know what a pivot table is, don't worry, we'll break it down together. So basically, a pivot table is a way to summarize data quickly that lives in a table. So we're going to have raw data here. So I just want to look at the screen and let's break this down before we begin to analyze PPC data. If we have a list of dates, order numbers, item ordered, revenue from each item orders, and the state, and you wanted to run some analysis like blank by blank, meaning Sales by item, or sales by item by state, or amount of orders by item, or amount of orders by state. Do you see all of this blank by blank type information? We're getting queued up here because it's like Performance by match type, you know, sales by match type, clicks by match type. That is the power of a pivot table when it comes to Amazon PPC optimization. So in this case, in this little example that was pulled up by ExcelJet, thank you ExcelJet for making this image, basically what we wanted to see from this was revenue by item as well as order count of item. So what that did was it grabbed every single cell where the word hat was To summarize the hats, and then it counted the count of them. So it's like, oh, it appeared 46 times. We have 46 orders with a hat. And then it summed up the revenue anywhere where hat was. So it's blank by blank. We had order count by item. We had revenue by item. That's what a pivot table allows you to do. And that's what we will see here. So for this example, I'm going to be using Google Sheets. And in one area, I've got a bulk file downloaded. That's what a bulk file looks like. And then I will go and I'll begin to pivot this data. So it's really simple. You just simply select the sheet that you're in, insert pivot table. You can use Google Sheets. It's awesome. And when you do that, you get met with this. And you can do really simple. I'll start this fresh. So when you first get in here, you'll see something like this. And again, starting off with a really simple analysis. Match type would be like the row you want to look at. You want to look at broad exact phrase. And then you can just drag and drop metrics. So like spend, ah, how much it's spent. Cool. I can do orders. Cool. I can do sales. Cool. So what it's doing is it's summing up every single row that matched with broad. It's pretty neat, right? So I can see like spend, sales, orders, so on and so forth. One thing you want to know about a pivot table if you're new to this is that sum is the default. And for various metrics, it makes sense to do the sum because I want to know all the orders For match type broad, I don't know all the sales, I don't know all the spend, but you could not do sum of ACOS for that. Why? Because the sum of ACOS, like if one keyword, one broad match keyword had a 10% ACOS, the next one had a 10% ACOS, next one had a 10% ACOS, the sum ...would be 30%. It doesn't make any sense. So any calculated ratio-based metric, you typically have to calculate yourself. There's calculated fields that you can add here to improve your bulk files. So I can add a calculated field and do something like spend over sales. And then get ACOS there. So that is how that kind of analysis works. Make sense? So for any straight up number that you sum, you can just sum it. There's also like averages, you can do percentage of total. There's a lot of cool stuff that you can do. So like, Check this out. Percentage of grand total sum of orders. I can see my order breakdown there as a percent. So typically I'm going to be looking at things by the sum of, but any ratio based metrics, you do need to create a formula for it. I was in a PPC mastermind. I was talking about this and shout out to Sean Stone for pointing out a feature of pivot tables that I didn't know about. And I've been doing this for 15 years. I would normally like run the calculated fields outside. And he's like, yo, dude, you know about calculated fields in your pivot table. So shout out to Sean Stone for pointing that out to me, friend of the show, in the Badger Den, fantastic marketer. This is an example of a pivot table breakout. And you can set this up in a variety of various different ways that you would want. I mean, if I can get rid of match type, I can also do another kind of Another kind of row-based analysis, I can add in placements. And the reason why I don't see it is because I've got some filters on. So I'll get rid of the filter and I would see this. And I would filter by placement to just sort of see how there's an empty row. That means there's rows that have metrics but don't have a placement. So I would just come in here and I would just get rid of the blank. Boom. Now I've got this beautiful little sheet of data, right? So this is the power of a Audit. Now, when I do an audit, I'll generally take a screen cap, put it somewhere else, take a screen cap, put it somewhere else. If you are listening to the show, And you want a fancy drag-and-drop bulk file auditor, stay tuned. Well, actually, yeah, stay tuned and join our newsletter list. I've been working on this template, and I'll share it with the newsletter list when it's ready. And I just wanted to talk about, like, the benefit of that is that it'll just produce all the different kinds of pivots that you want, so you can really get a good pulse check. So what I want to do for the rest of this episode, I think, is just run through some things that I commonly pivot on. I do this pivot table analysis and then I will talk about what actions I would take. And we can go relatively quickly. This one I think is really interesting. It's a match type summary. The thing that jumps out, of course, in this account, there's no phrase match. This is actually more common than you think where people have out of balance, broad exact phrase. Maybe you try to set it up perfectly where you have broad exact phrase duplicated in three campaigns all the time, and then maybe you forget sometimes, or maybe some are just super high volume, broad runs away with ad spend. Seeing the ratios here, incredibly valuable. So spend, sales, ACOS, my recommended action here would be to go And duplicate some really great keywords in phrases. Let's get some more related terms for them. So I'd be duplicating terms for phrases. Would I worry about duplicated search terms, like the same thing triggering multiple ad groups? Not really, because probably once a month, twice a month. Actually, that's not true. I usually do this weekly. I would look at duplicated search terms, and we have a tool to do duplicated search terms, so it makes it really simple. But I'd be looking at duplicated search terms To be sure that I'm not like in one place is doing well and another place is doing poorly. So I would just try to have a good balance and be sure everything is within my similar level of goals. Now, if I had a really intentional strategy about broads or exact phrase, I would probably look at this and see like, okay, it's fine that maybe one is higher than the other. But really what you're looking for is like accordance to your goals. So every single A cost value should be good. So that's a little thing that I would do. So if I was sort of auditing this account, I would Duplicate some of my best terms for phrase. And if I just did my best terms, I would expect phrase to come in really good with a good ACoS in like the next sort of interval that I check this out in. Let's talk about account-wide placement summary. So you can also do account-wide placement summary. So in a bulk file, you now get Amazon B2B placements or Amazon business, Amazon product page, rest of search, and they just call it a top. They just call it a top. Typing up search, it's a lot, guys. So they just wrote top. But what you have here are four different placements. And then what do I observe here? Well, the first thing I observe is, of course, product page is 29% ACoS compared to everything else. That's quite high. It's almost double the one underneath it. And that would lead me to believe that there are going to be campaigns where I can lower the bid and then increase top of search, increase rest of search. So that's how I would sort of fix product page. There'd be some investigation. But again, different layers of pivot that we're going to see later. Would it allow me to find exactly what campaign to do that in? Very quickly. Later in this presentation, we're going to run a campaign by placement performance breakdown as we talk about double pivots later. So not only does it clue you in at the top level, but you can zoom in on a lot of bulk file audits. So that's really neat. Top of Search doing exactly what I want it to. I would love to see Top of Search maybe at a bigger percentage. I'm almost positive that if I were to go and audit this account and look at a list of placement percentages, there's going to be a lot of campaigns without a placement percentage, so I'd want to push those and be sure that I'm putting my best foot forward on my best terms. I'd also look at Amazon Business. We're underutilizing Amazon Business. It's only about 2% of the ad spend here, so I'd love to push that a little higher. Another breakout you can do, drag bidding strategy, drag performance there. You might realize that, hey, you're only using one bidding strategy. My recommended action, if I were to see this, would probably be to take the highest order campaign. And switch it from down only to up and down. Up and down I like to test in high order campaigns. I'm talking like 60 orders a month for those high volume keywords because you can generally get some more juice out of it because it just gets a little bit more aggressive. And because it gets a little bit more aggressive, your very best term is a prime candidate for it. So it's likely something that you convert better than average on the search query performance there as well. Awesome takeaway there. Auto targeting gut check. This is another thing you can do. So you drag product targeting, you drag whatever metrics you want to look at, and you get stuff like this. So right away I see compliments are not performing well. So I would sort of like go do a double check on all my compliments, maybe pause some, down bid, be sure that's doing what I want to do. The next thing I would do is sort of, everything looks fairly stable, that's fine. So that would probably be my only action there. The next thing you can do is like a SKU roll up. So basically, you drag the property of SKU, spend, sales, conversion rate, ACOS, so on and so forth. What do I see here? Well, I see all my products. Broke summed by their spend. So if they're appearing in 10 different campaigns, I'll see one line item for each one, which is really helpful analysis. So what I would see here is I have a product that was at 6% ACoS. I'd want to be sure that that's a variant. I would want to be sure that that variant gets the most visibility because it's just converting so well. I might look at other variations and see maybe, you know, hey, this has an 85%. Maybe not every product needs to be advertised. And I might sort of pause that so that the spend there can go towards higher converting products, and I ultimately end up more profitable. So there's some observations there, and it just allows you to pinpoint what product is performing well, which one is not. I wanted to introduce the double pivot. So as you look at this list of SKUs by spend and sales, each one of these products might be in five different campaigns. And it's helpful to know what they're doing at the top level, but in addition, it's helpful to know what campaign they're performing well in and which ones they're not. So then you can drag a secondary metric in there, like campaign name. So you first drag SKU, and I'll just show you what this looks like in case you never did it before. So you would drag SKU in there. So where's my SKUs at? So SKU and placement doesn't exist, but I would drag SKU and then I would drag, I get rid of my filter. I would drag SKU. So I see my list of SKUs, AB100. And then I would drag some other entity there. And I'm looking at SKU and campaign name, SKU and campaign name, show me campaign name. And it just allows you to pinpoint any issues that you might have. So this is a double pivot table, double pivot table. I hope we did the booming voice every time I say pivot table. So it just allows you to zoom in. Okay. I know my total for the product, but then I can zoom in and see, oh, wow, this product has an 8% right here. 55% over here. Is there anything I can do to get more of this 8% traffic and maybe fix the 55%, 53% ACoS traffic? Double pivoting. Awesome. You can also do really interesting things like a placement breakout. Basically what we're looking at here is each placement and then the sum of performance per Bidding percentage modifier. I just want to break this down again because it's actually really, really interesting. So I can see placement top. I can see that for campaigns that have no placement top modifier. So they have a 0% modifier. I have a 27% ACoS on them. I can see placement rest of search. I've only got a couple different modifiers active. Same with product page. So I've got all my campaigns that have a zero and that looks like the majority of spend. So I have the majority of spend on zero rest of search. I have a dollar in spend on 30% rest of search and I've got 16% in spend on 100% top of search. So this clues me in like, hey, I'm almost certain there's going to be areas of performance where REST-A-Search is going to be behaving well that I have not accounted for, so I should give it a bid boost. So how do you know where? Well, you do another double pivot. I drop my campaign name, and then I split out the placement positions on every single one, and then I'll see a campaign. I'll campaign REST-A-Search. Ooh, here, it's 82%. I don't want to do anything there. But I'll then find other areas where the rest of search is performing well, and I would lean into that, right? So you can do a triple pivot. So if you are here and you're looking at campaign by placement, you can add what percentage each thing is. So I've got campaign name, the placement, and the percentages. So I can see a couple different things. Like I've got a placement top at a whopping, beautiful 13% ACOS. I might want to ramp that up to maybe 40%. Top of search. I've got, I'm looking for an area where, yeah, rest of search is a tough one for this account. But I would go back and look at this pivot. Where I see, hey, there's some areas of rest of search where I likely have good ACoS, I just want to be sure for that campaign and that targeting, it gets a little visibility. Let's talk about the cousin of sponsored products and jump over to sponsored brand. There's no way to get this outside of a bulk file, which is creative headline pooled analysis. Let's say you're doing sponsored brands, you have a bunch of creative headlines, you throw them all over the place, and you then want to sum the data and analyze it. Pivot table, my friends. Let's say you're advertising on sponsored brands and you want to know what's behaving best. Here's a common question. Detail pages, product lists or store pages. Guess what? Low volume on the store page, but converting at 10% ACoS compared to sending it directly to a detail page at 47%. I would use that information to try to assess what to do. Oh, what store page? It gives you a pivot of store page performance. How about multi ASIN creatives? Hard to keep track of, hard to measure the best one. I've got one multi ASIN creative, 9% ACoS. I've got another one, 192, friends. Pivot table clues you in. I know exactly what to do and go and optimize. Lets you have the same video across various places. Guess what? You can pivot across your media IDs. So you can see that one video gets a 34% cost and another one gets 67%. My friends, this is the workflow. If you have an intern, if you have somebody who works with you, if not, it's you, friends. This is vital work. Do this at least once a month. Do this sort of like bulk, audit, pivot. It's brilliant. And I think that hopefully you know, you can pivot across any kind of downloadable report, my friends. If you want this one, be sure you're on our newsletter list. We've got performance by campaign type, and this is just sort of drag and drop. Performance by match type, performance by placement, And bidding strategy. I'll probably have some updates on this in the coming weeks. I love building spreadsheet-based tools. Clicks by click, spend, order, sales, conversion rate, ACoS, buy, auto, targeting type, search term, spend by order amount. So I can see for this one, I got $14,000 in total spend, only $3,600 in non-converting spend. So $3,600 over $14,500 is like 24%. That's actually quite good. I see some accounts with like 70% non-converting spend, having a nice little audit template that you just fill out. Invaluable. ASIN by performance. So I got on my ASIN and I can see what the level of performance is. If you don't have a way to bulk file, audit, my friends, I hope by now you do. It was an absolute pleasure. I love doing this. Thanks so much for listening. I'll be back here next week talking about how to make your Amazon PPC life a little bit easier and a little bit more profitable. My name is Michael Erickson Facchin. You're tuned in to The PPC Den Podcast. Have a prosperous week and I'll see you next time here on PPC Den Podcast. Unknown Speaker: And picked keywords. I've got my bits. Some placements too. And bad mistakes. I've made a few. I've had my share of brunt. Oh my... The PPC Den, my friend. You too are the PPC. Time for medicals, cause we fixed the game.