Excel for PPC: Analyze PPC performance better and faster with these powerful tips

Episode Description

Analyzing performance is PIVOTal in PPC. And Anthony Excels at that.

Sorry about those bad puns, but jokes apart, you’ll go a long way if you regularly lean into finding ways to become more efficient and methodical in analyzing your PPC account performance.

Most of us try to limit our Excel exposure to the bare minimum. But, learn a trick here and a formula there, and you’ll see how it can make your life easier.

In this episode of PPC Town Hall, I spoke to Anthony Tedesco. He showed me some really powerful formulas and tricks to analyze PPC performance better and faster, from calculating incremental impact to using VLOOKUP to find results faster to handling complex datasets.

Tune in to learn:

- Powerful formulas for better reporting

- How to use VLOOKUP and Pivot tables for PPC

- Tips to handle large-scale datasets

and much more

Episode Takeaways

Powerful Formulas for Better Reporting

• Utilizing Delta calculations can reveal significant changes in performance metrics by comparing current periods to previous ones, highlighting shifts in volumes and efficiencies.
• Incremental impact calculations help quantify the actual effect of changes in metrics like click-through rates or conversion rates, providing tangible figures to stakeholders.

Using VLOOKUP and Pivot Tables for PPC

• VLOOKUP is crucial for merging data from disparate sets based on a common key, such as campaign names, improving data organization and analysis.
• Pivot tables enhance data analysis in PPC by allowing quick summaries and deeper insights into campaign structures and outcomes, such as verifying campaign setup and auditing ad groups and keywords.

Tips for Handling Large-Scale Datasets

• Employing formulas like IF statements and VLOOKUP in Excel can streamline handling and categorizing large datasets, facilitating easier management and more robust reporting.
• Creating pivot tables from extensive datasets enables efficient summarization and quality checks, ensuring accuracy and consistency across PPC campaigns.

Additional Takeaways

• Excel tools like conditional formatting and advanced formulas (e.g., IF, VLOOKUP) are indispensable for precise PPC management, allowing for detailed manipulation and analysis of campaign data.
• Regularly using pivot tables and VLOOKUP not only for basic reporting but also for complex tasks like quality assurance can significantly enhance the efficiency and accuracy of PPC campaign management.

Episode Transcript

ANTHONY TEDESCO: But this, if is number search, you know, this is something that I would strongly encourage the search marketing professionals that are listening here to really kind of get a deep understanding of. I’m coming over here and doing some QA. And I wanted to start by kind of calling this out because if you just look at the counts, you have to consider how a pivot table is being filled.

Right. The pivot table is being built based off of kind of the most granular number of rows. And so it doesn’t necessarily kind of by default have the ability to show you unique campaigns or unique ad groups that are included in the pivot table. It’s looking at. The number of keywords, because that’s our most granular segmentation in this particular example.

FREDERICK VALLAEYS: Hello and welcome to another episode of PPC Townhall. My name is Fred Vallaeys. I’m your host. I’m also one of the co founders and the CEO at Optmyzr. So in this week’s episode, we’ve brought in a great guest, Anthony Tedesco, and he has written I believe three of the top 10 articles on search engine land on the PPC topic in 2022.

So we definitely noticed that. And we said, Hey, what is Anthony talking about? And you as my audience will probably want to hear a little bit about what he’s been doing. So he talks about brand keywords, but even more than that, he talks about spreadsheets and how to use spreadsheets in PPC. Spreadsheets are one of the most foundational things that PPC managers use.

So obviously a great topic to talk about. Right. And Anthony’s got great expertise. So with that, let’s have another great episode of PPC town.

All right, Anthony, welcome to the show. Tell us a little bit about who you are and what you do.

ANTHONY TEDESCO: Thanks for having me, Fred. Anthony Tedesco here. I am the global search engine marketing lead at Cisco systems. And you know, I kind of coined myself as a bit of an Excel nerd, you know you know, ever since kind of, I started in PPC back in 2015, 2016, really kind of leaned into using that tool in order to help me do my job better.

And so yeah, a bit of a, bit of an Excel nerd, but an SEM professional as well.

FREDERICK VALLAEYS: Nice. Well, welcome to the show. And many of us are nerds or geeks or whatever the nice term is, but yeah, we love the data, right? And that’s sort of how we grew up in search marketing. But before we dive into a lot of the very specific examples that I know you’ll share with us, talk a little bit about.

What’s the point of spreadsheets? Because at some level, there’s like this feeling that Google’s automating everything. So our spreadsheets, just a nice way to put some reports together that management will like, or does it actually give us back some level of control that we may have lost along the way with PPC management?

ANTHONY TEDESCO: Absolutely. You know, I think when it comes to Excel, you know, first and foremost, let’s just put it out there. Excel is a tool with infinite possibilities in terms of how it can be used, right? I mean, it’s essentially a program of sorts and, you know, what we’re doing is scraping the surface today in terms of how Excel can, you know, be utilized.

But, you know, to the point you’re making, Fred, I Couldn’t agree more. It’s a very interesting time in search engine marketing where we are seeing a lot of updates on the platforms that really lean into automation. You know, taking away kind of some of that granular control we’ve had historically in terms of our campaign management.

I mean, when I first started in search engine My internship was really changing keyword bids on a daily basis, updating Excel tracker and just making those updates in the platform. Obviously in the past seven, eight years, we’ve come a long way. And you know, where I kind of see the spreadsheets, you know, still having a critical role in an SEM professionals you know, day to day is.

It does help you kind of maintain some of that control, because if you know how to use Excel properly, you can really dig into the why and you can do it at scale. And I think that’s a really important thing, too, is that, you know, a lot of what we’re going to be walking through today, if it doesn’t matter if it’s a data set that has 10, 000 rows, 100, 000 rows, 250, 000 rows, because you’re taking the same techniques, you’re standardizing, and you’re going to be kind of scaling those out across, you know, a data set of any size.

FREDERICK VALLAEYS: Yeah, that makes a ton of sense. Okay, good. So there is hope for people who still want to have some control over PPC spreadsheets are the answer. I know you’re going to talk quite a bit about Excel, right? So Excel is your go to. Obviously anyone who wants to use Google Sheets, a lot of the formulas you’re going to cover today, they are either the same or very slight variations, but they will also work in Google Sheets.

So with that, why don’t we start maybe with some of the fundamentals of you know, how What can you do in Excel? And then for folks who are maybe more PPC and Excel experts where we have bookmarks that we’re going to put in on the show on YouTube. So you can find the section that’s most relevant to you, but let’s start with the fundamentals.

So Anthony, what’s the first thing you want to show us?

ANTHONY TEDESCO: I think we should start with the funnel, right? You know, the, the SCN funnel is it. You know, an absolute essential when it comes to kind of evaluating the performance of our campaign. So I want to show a couple techniques that I leveraged to not only kind of help understand what the biggest drivers of performance are, but also contextualize for those stakeholders who, you Might not kind of have that deep understanding of how SEM campaigns work.

And so, you know, sometimes it helps to, you know, put it in terms of in simple terms, such as clicks or conversions. And, you know, I’ve got a couple of little tricks that we can walk through to help show how you can do that.

FREDERICK VALLAEYS: And hopefully you’ve convinced them to care about conversions more than impressions.

That’s right. It’s not that crazy, right, how much people want to know, but what’s my CPCS? Like, ah, who cares? We care about profits. Yep. But yeah, so what’s on the screen here? Walk us through it.

ANTHONY TEDESCO: Yeah, so what we’ve got here is just kind of a dummy data set, right, that I put together to help convey kind of, You know, a couple of the techniques you can use.

So the first one I’m going to cover is the Delta, right? And you might remember from your chemistry classes back in the day, or perhaps a physics class, a Delta is essentially a rate of change. And what I love about a Delta is that it helps to kind of convey that story. across both volume metrics as well as efficiency metrics.

So, you know, cost efficiencies like a CPC or CPA as well as conversion rates, like a click through rate or CVR or click conversion, you know, whatever kind of term you use for your company. But to kind of, you know, just review it very simply. All right. A change is is taking kind of the current period versus the previous period minus one, right?

And you can, you know, as you can see here, we do this across both the volume metrics as well as those efficiency metrics. And what this does is it helps you kind of understand. What the biggest changes were week over week, right? So, you know, here we have a 50 percent lift in clicks week over week. That’s a pretty substantial number.

And someone who is not leveraging a Delta might look at this and say, okay, well, could the rate only jumped from 4 percent to 5%. However, Impressions, we were up 200. So, you know, the, the, the impressions must be the main driver of the click lift there, right? In actuality, when we look at it from a Delta perspective, you know, that rate of change week over week is much higher on the click through rate front than it actually is on the impressions.

You know, you can see that played out in the 25 versus the 20%. So if a stakeholder was asking you, Hey, what was the cause of the click through, or, you know, the What was the cause of the click increase week over week? The correct answer would actually be that the click through rate improvement had a better impact than the impression lift, even though those numbers might be different magnitude.

So it’s a really useful tool in that sense. And then, you know, what I’ve also gotten here is our incremental calculation, the incremental impact calculation. You know, I want to start by saying it comes with caveats, right? All us SEM marketers know that. We do not operate in a vacuum, meaning that, you know, this funnel kind of all the different metrics that are within a funnel as they change, that will have an impact on other metrics.

However, this can be a really useful tool to help stakeholders understand roughly the impact that changes are having because saying to a stakeholder, Hey, a 25 percent improvement in click through rate drives a 50 percent lift in clicks. You know, they might be like, so what? What does that mean? Like, how many clicks are we talking about here?

So what we can do is we can use the incremental impact calculation to help contextualize that. So I’ll go through an example and then we’ll actually calculate one out for the CVR impact. So as you can see here, the calculation that I’m going to be using is saying, Take the impressions, multiply that by the difference between the click through rate in week two versus the click through rate in week one.

And you can see, ultimately, when I do that, that’s 12 percent, or sorry, 12 incremental clicks, week over week, driven by that 25 percent lift in click through rate. If we wanted to do this for convergence, the way we would go about this, Knowing this is a click conversion, so our calculation is going to be our conversions divided by our clicks, we’re going to say, okay, sorry, go ahead, Fred.

FREDERICK VALLAEYS: So yeah, conversion rate, I guess, which you don’t have. Exactly.

ANTHONY TEDESCO: Click conversion here, conversion rate. I’m, I’m kind of, I’m using yeah, that’s a good call. I should probably update that. But if we were taking the clicks and we multiply that by the difference in quick conversion week over week. What we’re going to see is that there’s a negative impact.

Right? And that makes sense because the click conversion dropped 40%. So if a stakeholder, you know, was asking you, Hey, I see click conversions down 40 percent week over week. What’s going on there? Is that a big deal? Is that something that’s really going to hurt our bottom line? Because negative 40 percent sounds like a pretty big number.

The answer to that is, you know, in reality, because of the volume in the funnel, what we’re talking about is the difference of about one conversion week over week. So in that sense, it can be a really useful tool to help contextualize conversations with stakeholders.

FREDERICK VALLAEYS: Right. And that’s where we get into the problem with small numbers sometimes.

Right. So, A decline of one conversion may not seem that significant, but on a percentage basis, if we were looking at what two or three conversions in the past now, it’s like, oh, that’s kind of like half, right. That’s right. Do you have any tricks for sort of like well, one thing we always struggle with is like, if we’re doing a sort on like the biggest changes in an account and you just go off of a percent basis, then obviously, you know, something that went from one impression to two impressions, that’s a doubling of impressions, which is a massive improvement, but kind of like you’re alluding to here.

That’s one extra impression. That’s not going to do anything right at a conversion rate of even 20%. It’s barely going to register. Do you have any tricks for sort of like combining volume and impact metrics when you sort these things out?

ANTHONY TEDESCO: Yeah, I think I don’t know if it’s necessarily a tip per se, but just kind of my general philosophy is You know, I like to use these metrics in tandem.

You know, I always put the highest priority on the delta. Because I do want to understand the rates of change, because the rate of change does have a significant role in terms of understanding the aggregate changes in performance. But when you use the incremental calculation in conjunction with.

delta, it helps to kind of bring that story together and it helps to kind of normalize to your point, Fred of, you know, Hey, if we’re talking small numbers here, deltas can be very misleading. You know, the one other thing I’ll just kind of add onto here is you can use the incremental impact calculation with a lot of metrics.

What you’re doing is you’re essentially saying, okay, What was the rate of change in an efficiency metric, whether that’s a cost efficiency or just kind of a funnel efficiency, like click through rate or conversion, as well as long as you’re multiplying that by the volume metric, that’s one step up in the funnel.

So, you know, just to do another example here real quickly, let’s do it based off of costs. So if we go incremental costs

due to CPC,

what we can do is we can say, Oh, well, actually our CPCs are a flat here. So that’s going to show zero. The

FREDERICK VALLAEYS: cool thing is you can actually model this, right? So now you can start plugging in some numbers and say, like, imagine what if the CPC had gone up 20%? Yeah. Why don’t we change that number? We’ll fudge with the data a bit.

ANTHONY TEDESCO: Yeah, let’s just fudge with the data. That’s a good call. So let’s, let’s just change this to a 2. 5 for the sake of argument. Right. What we can do here is we can say, okay, if we drove 60 clicks at that 2. 50 CPC, what was the incremental kind of costs on a week over week basis? That’s 30, right? So what that’s going to do is help you kind of just convey to the stakeholders what’s going on.

And the other thing I would say is when it comes to this stuff, just Make sure it kind of passes the smell test. Right? You know, you don’t want to just kind of blindly take these numbers and share them out because of what we’re talking about with the interrelation between all these metrics.

FREDERICK VALLAEYS: So one of my favorite ways to use incrementality is with the bit simulator from Google.

It’ll tell you if you change your bid from X to Y. What is your new cost going to be and what is your new conversions going to be? But it doesn’t talk about the difference the deltas And so it’s quite nice because now if you start bringing in the deltas like you’re calculating them You can actually do an incremental cost per conversion and you can start to find out when You sort of hit that inflection point and those additional conversions, even though they still average out to being within your goal, each additional conversion you buy is basically more expensive than the profit that that sale could generate.

So it’s a really good way to find a cutoff point where you maybe should not take that step to the next bit increment.

Awesome. But Hey, thanks incrementality here. So. I think let’s go to another basic one that hopefully most people know about VLOOKUPS. How do those work? Absolutely.

ANTHONY TEDESCO: Let’s do it. VLOOKUPS, definitely one of my you know, favorite Excel features. And the way I kind of like to describe a VLOOKUP simply is, it’s a join between two datasets.

So we can walk through a couple of different examples, one that’s a little more basic, one that’s a bit more complex. But essentially what it allows you to do is take disparate data sets and combine them together based off of a common value. And I would say one of the most common ways that VLOOKUPs are used is going to be kind of, you know, from an SEM perspective, or at least kind of for the sake of my company, it’s the campaign and using that campaign as kind of the source that’s In all of the different data sets that we want to combine together.

So using a hypothetical example here, right? Let’s say I’m running a shoe company. I have these different campaigns that are in the platform. And while we might have Google ads, conversion tags that are set up, there’s some sort of kind of offline conversions that we want to incorporate as well. Right?

You know. Different companies kind of might have different reasons for having these offline conversions. Let’s say it’s brick and mortar store visits, whatever it might be right. Ultimately, what we’re going to do is just use a simple V look up in the way to kind of set these up is. The first feature of the VLOOKUP, the first input is going to be what am I looking up in the second data set?

Then you define what that data set is. And then finally, this is an important one, what column you want to reference in that second data set? The true false feature. My best practice is always to use false, especially with campaign names, because a lot of our campaign names look very similar to each other with maybe one small change in the field or one of the fields in the campaign name.

So best practice always to use false. And then that

FREDERICK VALLAEYS: refers to whether your lookup set is sorted correctly. And so I agree with you. It’s, it’s you, even if it is sorted, it’s better to set it to false. It’s you lose some efficiency, But you usually get more correct results.

ANTHONY TEDESCO: Exactly. It increases your accuracy.

And I guess just maybe to take a step back in terms of what the true false is, false is exact match, like very, very kind of explicitly kind of what is what you are referencing, whereas true can bring in approximate matches as well. So that’s, that’s kind of the big difference there, but ultimately that this is a really useful tool just to kind of combine data sets, right?

And so what we’re, you know, as you can see here, this formula you know, one thing I’ll call out is blocking your data range. that you’re referencing from. I think this is a really important one that is often a mistake that people make when they’re first kind of getting going with feed lookups. If you don’t lock your data set, and let me show, I’ll just show a quick example here.

What I’m hitting, by the way, is F4. On PCs for those who are kind of interested in like little shortcuts and whatnot, that’s going to kind of, you know, allow you to walk different rows, different columns. You can play around with where those dollar signs are in the data set, but just making that one small change.

Watch what happens.

What we’re going to do.

FREDERICK VALLAEYS: So you’re copy and pasting that down now, where you no longer lock the data set. So the top result there was 18. But if you change it,

ANTHONY TEDESCO: I get lucky because they’re all here. Let’s do this.

Right. All I did was just kind of sorted them. So they weren’t in the same order. That’s why that was working there before. But you can see now we’re getting errors. And the reason why we’re getting errors is because this shoe competitor. It’s no longer in the data set we are referencing. So if I just come back up here, even though I’ve changed Because by shifting

FREDERICK VALLAEYS: the formulas down by one cell, it’s basically sent the The lookup space is also shifted down by one cell and eventually just every time you do that, like the top line of that data set you were looking in kind of disappears.

Exactly. The further down the list you go, the fewer matches you’re going to get.

ANTHONY TEDESCO: And so what I just did was I, I just changed us back to being locked on the cell. So even though we’ve sorted with the order of that second data table to make it a little different, what we should see here is that we now kind of are returning all those correct values because.

Down on the bottom here, we’re still referencing that full table, as opposed to it moving down one by one as you were describing, Fred. So locking cells with VLOOKUP is a very important tool.

FREDERICK VALLAEYS: And then another nice feature I don’t know, you might be going there, but this doesn’t have to be on the same sheet.

Yes. You can reference a different sheet. Can you reference a different spreadsheet altogether, or does it have to be in the same sheet?

ANTHONY TEDESCO: You can you can reference different spreadsheets. The important thing is just making sure that kind of access between the spreadsheets works fine. And especially if you’re sharing it out with other users, making sure they have access to both documents, that’s going to be really important.

But it is possible to reference from other different separate workbooks as well as separate sheets.

FREDERICK VALLAEYS: Nice. And then I see you have a multi segment VLOOKUP example. So let’s take a look at that.

ANTHONY TEDESCO: Yeah, I’ll go real quick because we kind of went through the fundamentals of VLOOKUP here. But one way that I really like to use VLOOKUP in my day to day is when you’re pulling data that’s segmented, right?

This can be kind of hard to combine things and one of the most common segmentations we look at in search marketing is going to be time, right? Whether that’s day, whether that’s week, month, whatever it might be, we want to understand what’s happening over time. Well, as opposed to kind of going into the platform and pulling a, you know, one data poll for each week you want, you can pull the segment into the, into the data set and what that allows you to do is one of these multi step B lookups where I’m using a simple concatenation of the campaign in the week.

And I’m doing that in my offline conversion data the same way, campaign and week, and that will create that tie together. So when I go to my VLOOKUP column here. I’m now pulling in the correct conversions for that campaign for a particular week. And this is one of those examples that’s really, really helpful with large data sets.

You know, if you’re running 150, 200 campaigns and you want to segment and pull out for, you know let’s use, I don’t know, 52 weeks, right? Because that’s the course of a year that data sets going to start growing on you very quickly. It multiplies out. And so that’s going to, you know, having a technique like this that you can consistently apply across a large data set, that it’ll really expedite your analysis capabilities.

FREDERICK VALLAEYS: Right. And for those less technical viewers, I mean, basically, the key is the two things that connect between data sets. And in this example, you’ve said campaign name plus week, but that’s just an example, right? So long as you get a unique value of some sort that matches between the two data sets, that’s all we’re looking for in this case.

ANTHONY TEDESCO: And, you know, you can you can use any segment really right. You know, I do keyword by device. Sometimes that’s a really useful one. But as long as to your point, Fred, it’s a unique value. You want to make sure like sometimes what I end up having to do is, you know, day campaign ad group as well as keyword.

If that keyword is showing up in a different campaign where we might be targeting a different audience. So you can kind of extend out that concatenation to different segments. As long as you’re getting to a true unique value in this reference that you’re using for the VLOOKUP.

FREDERICK VALLAEYS: Great. So let’s look at the final fundamental, and I believe that’s categorization.

So how do you categorize things?

ANTHONY TEDESCO: Absolutely. So I’m gonna use a couple different examples here, but categorization, you know, really, what we’re talking about here is a lot of if statements and if statements in Excel. Very, very powerful tools. You, you can do all kinds of crazy stuff with ifs. And if anybody watched the Excel world championships, like myself, you may have seen, like, there was a lot of if statements that were being used as these guys were flying around their Excel, trying to build out whatever kind of a lottery.

FREDERICK VALLAEYS: Anthony, I think you just proved to us that when you said you were a nerd, you meant

ANTHONY TEDESCO: it. That’s correct. Yeah, I just kind of exposed myself, but hey, it was on ESPN too. That’s pretty cool. I don’t know about

FREDERICK VALLAEYS: that. I think I will check it out.

ANTHONY TEDESCO: So, if statements, really, really valuable. And an if statement is simply just kind of logic, right?

You know, if something meets a criteria, then this If it doesn’t meet the criteria, then what? And the beauty of the if statement is that you can nest them together. Because a lot of the times in our world, it’s not a simple binary, you know, zero or one evaluation. There might be 20 different options that something could be categorized underneath.

And so what I’ve done is I’ve put together a couple of different examples. One that’s more kind of on the numeric end of the spectrum and one that’s more kind of using you know, text you know, characters in order to kind of evaluate. So the first one is going to be, you know, let’s say you work for a company that for whatever reason has Tuesday as the start of their week.

What this tool will allow you to do is. pull your data on a daily basis and create a column in your data set for this funky week. You know, if, you know, in Google ads, there isn’t a super easy way to pull week starting Tuesday data, unless you want to go Tuesday to Monday, go to the next week, Tuesday to Monday and pull them all down and kind of manually create the filters as you go.

So what this, what this calculation does is it shows you, you know, if the weekday Is equal to three, which in this is Tuesday, right? Sunday is one, Monday, two, Tuesday, three, et cetera. Then I want you to return that date because that is the week starting. But if it is not, I want you to pull the most recent Tuesday prior.

That’s what this kind of next part is here. This is the date minus the weekday minus three. That’s going to pull you to the previous Tuesday. And what you can see after I dragged that down. is we have 81, which is a Monday that’s pulling to the week prior 7 26. But then once we get to Tuesday, let’s refer to that date because that’s our actual week start and it plays out for the next six days.

And then we jumped to 89. You know, this also can be really, really valuable. For more common you know, calendars as well, like a Sunday start, you know, yes, can we pull that data into or from the platform rather easily? Absolutely. But a lot of times I like to pull at the most granular level at that date level and then just make that transformation in my Excel so that when I’m looking at my weekly performance, if we see a spike, I can quickly jump to, okay, that’s that’s when Well, what was happening kind of on the different days of that made that week?

Was there a day that spiked in particular that might have been the driver of that week? That, you know, saw the significant change in performance. So again, this formula, it’s the exact same thing. The only difference is switching out which day we’re starting our week on. So here one being Sunday and the other example, three being Tuesday.

FREDERICK VALLAEYS: Very nice. And that’s a little more sophisticated there. And then I guess you could also combine this now with the VLOOKUPS to. Do the correct mapping. So that’s right.

ANTHONY TEDESCO: You’re picking up what I’m putting down. This all kind of comes together, right? Like you pull the data set down, you start adding columns to your data set using these types of manipulations and you can be look up right once you kind of have these like different columns built up and then you’ve got this big old huge data set that you can throw in a pivot table and have all kinds of fun with that’s ultimately.

Where this all drives to, I

FREDERICK VALLAEYS: have a feeling we’re going to have to pivot tables next year, but I think you have one more categorization example where it’s text based instead of dates.

ANTHONY TEDESCO: I do this is 1 that, you know, when it comes to categorizing ad copy, that can be kind of tricky. Right. You know, especially if you’re working for a company that has a whole bunch of different messages that you’re putting out there.

So what I did was just kind of built out a simple example. And what you can see here is there’s only two options, right? This if is number search, you know, this is something that I would strongly encourage the search marketing professionals that are listening here to really kind of get a deep understanding of.

If it’s number search, what that is really kind of saying is look at a, look at a cell for a particular set of text. And if it’s in there, return something, if it’s not, return something different. So what we have here, right, this is the basic example. If is number search, we want to look for percent off because we know if the ad copy is talking about something that’s percent off, it’s some sort of discount, right?

So if it contains percent off return discount, if it doesn’t, Let’s return value prop, right? Because that’s, you know, some other kind of value prop. And we could get more specific with this, which we will in a second, but for the sake of argument, let’s just do discount versus value prop. And what you can see here is each cell is kind of just being you know, searched for that percent off.

And if it does contain it, It will return the discount filter. And this is often like a label that I might add to ads in the, in the account. And then in that way, I have labels that are built out based off of the type of messaging that we have. And I can aggregate that much more quickly.

FREDERICK VALLAEYS: Right. And so eventually this sort of methodology probably goes into a bulk sheet that’s compatible with uploading back into Google ads so that you can set these labels, post them back into the system and then use the Google ads native interface to do some basic reporting on what you just explained.

Exactly. Hey, so one thing I’m curious about, so you just showed a formula that was getting a bit more complex, right? So sometimes we can nest the if statements. In this case, you were doing. A search and that then was being used in the if statement i’ve seen i’ve built myself like 50 levels of if How do you not get lost when all of that is in one line?

Like is there some sort of an editor tool? Like what’s how do you go about not getting lost in the code?

ANTHONY TEDESCO: That is a great question and one thing that I would say is Using copy and paste when you’re building nested ifs can really help you out. Because it helps you to kind of keep control of what’s going on in that consistent taxonomy so that you’re referencing the same things.

FREDERICK VALLAEYS: Correct. And I think what you’re saying is what I’ve been doing as well. And so you basically write the one if condition where there’s a certain piece of text you’re looking for. And then that is the thing you copy and paste because the if it’s not matched, it’s something else. And that’s something else.

That’s the thing you then paste over with your consistent nice formula. And that way I’ve also achieved to not break some of these formulas, but the moment they get broken, I’m like, I don’t know what to do. I basically feel like I have to restart from scratch. And so if any viewers have a great tool or a great technique for not getting lost in your code, put it in the comments.

We’d love to hear more about that.

ANTHONY TEDESCO: Yeah,

FREDERICK VALLAEYS: let’s let’s switch over to pivot tables, right? So now that we got this basic data in a nice shape, what do we do with it?

ANTHONY TEDESCO: Let’s do it. So I’m going to pull up. I’m gonna start with a pivot table example that I think might be a little less commonly used but I find it to be incredibly impactful.

You know, ultimately pivot tables are most commonly going to be used for reporting and looking at funnels and things like that. Right. And they are awesome, awesome tools. You know, it’s right up there with the look up for me in terms of stuff that I’m using most commonly in Excel. But this is a technique that I learned from one of my kind of early in career mentors, and it’s really expedited my ability to create campaigns at scale.

You know, a lot of people just think pivot tables equals numbers, right? Manipulating numbers, all that. Well, this is an example that, you know, we’re not really looking at numbers at all. We’re looking at kind of the different you know, aspects of our bulk sheet and using it to QA very, very quickly.

So. I’m going to start just kind of what’s level set in terms of what we’re trying to do here. We’re trying to create five campaigns. Each campaign has three ad groups in each of those ad groups is going to have five keywords. And then I’ve added just like a nice little wrinkle here from a QA perspective as well.

Ad group one uses landing page a ad group to landing page B three C. So. I’ve gone ahead and taken a stab at that, right? And I want to, you know, you can look here and there’s different ways to go about this, right? You could sit here and filter through each one and try to, you know, just spot check it in that way.

But this is where pivot tables can really help you out. What I’ve done here is just built my summary table. And if you checked out my search engine land article where I talk about this use case, you know, one of the steps I talk about is creating that kind of initial just summary view of, hey, Here is what I’m hoping to achieve.

So I’m looking for five campaigns, 15 ad groups, 75 keywords, and three unique final URLs. But, you know, how those kind of all get layered out is, you know, reflected here. So the pivot table PUA, all I did here, and you know, for the sake of time, all I’m doing is highlighting the whole pivot table and then creating the pivot table.

Right.

FREDERICK VALLAEYS: Highlighting the data sets and then creating the pivot table from there. Yes.

ANTHONY TEDESCO: Highlighting the data, creating the pivot table. And then I’m coming over here and doing some QA. And I wanted to start by kind of calling this out because if you just look at the counts, you have to consider how a pivot table is being built.

Right. The pivot table is being built based off of kind of the most granular number of rows. And so it doesn’t necessarily kind of by default have the ability to show you unique campaigns or unique ad groups that are included in the pivot table. It’s looking at the number of keywords because that’s our most granular segmentation in this particular example.

So. That’s why we’re seeing here 75 campaigns, 75 ad groups, 75 keywords, and 75 landing pages. When you start kind of thinking about how you can use these to your benefit, where that’s going to come in is starting to kind of understand, okay, our first criteria was we wanted to make sure that we had five campaign or five keywords, five campaigns, and three ad groups in each campaign.

So looking at the default pivot table format, You can see campaign one has ad groups one, two, and three, there are five keywords in each. And we see that kind of throughout, which is a sign that, okay, we’re looking good on that front. An alternative view, just, you know, just to get stylistic difference ultimately is going to be looking at the reformatted pivot table.

And all I’m doing here is I’m making a change in terms of the layout. So I’m switching our layout and saying, I want to show the items in tabular form and repeating the item labels. And you can see the difference isn’t that significant. It’s really just that indentation, but it’s another way to look at it.

The other thing we want to look at is our landing page QA, right? We want to understand. What is going on kind of from a landing page perspective? Do we have that association of ad group one to landing page A, two to B and three to C? There’s a couple different ways to go about that. This is probably what I refer to as the top down view, where it’s a really kind of simplistic, just looking, okay, spot checking.

1, A, 2, B, 3, C, etc. But you can also flip it on its head, right? And you can also say, okay, I want to rearrange in my pivot table, instead of, and maybe this is a good thing for me to kind of highlight here, Over here, down in the bottom, all I’m doing between the option number one and option number two is changing the order that I have those rows in the pivot table.

So option number one, you can see it’s campaign ad group landing page. What I refer to as the top down. Option number two is going to be landing page campaign ad group. And as you can see, what this does is it allows us to quickly organize and say, okay, landing page a yep. It’s only being used for ad group ones.

B at group twos, C at group threes. So you can probably think of all different kinds of use cases for how you could use a pivot table with bulk sheets ads. It comes into play a lot to help you catch misspellings. Because if you know, you’re only supposed to have, for example, Three responsive search ads per ad group.

If all of a sudden you’re seeing a number that’s greater than that, that’s a, hey, maybe there’s a misspelling in there somewhere. There’s a reason kind of, we we’re not getting the exact number of ads we, we were anticipating. So it’s a really useful tool in that regard.

FREDERICK VALLAEYS: Nice. Okay. So you’ve taken a bulk sheet of your ads.

You’ve run some pivot tables on it to basically do a quality assurance audit to make sure that structurally the numbers line up and you’re getting As many keywords and attacks as you expect very interesting. So is there a way to, I’m sure there is, but like automate that final step then, right? Like, can you run an if condition on one of these outputs to say you know, we, we expect the output of this landing page to always be ad group number one.

ANTHONY TEDESCO: Yeah, you could definitely add a, you know, that’s another way to go about it, right? Is just adding a column back over here per se. It could be an if statement that is if at, if ad group is at group number one. So like basically let’s see, we can do this real quick

equals. If

this number, I’m going to use that from the previous example, search, and this contains number one in this text

and.

If and is number search

a sorry, it’s hard to talk and talking at the same time.

FREDERICK VALLAEYS: Yeah, so Anthony is writing a formula here to check if the ad group name contains the number one. And also if the landing page then contains a equals a then he wants to say that that’s an accurate combination. If it’s not, he’s going to probably put something like inaccurate.

And then you can run a filter on the final result to just show inaccurate ones. And kind of speed up finding problems in your account. What’s nice, of course, about Excel and Google sheets is that they will give you little warnings along the way. If your formula is missing some input parameters or some other things.

But yeah, these things are sometimes a bit hard to build on the fly when the pressure’s on, the camera’s rolling. So but Anthony, I mean, this has been fascinating. So let’s wrap it up here today. You’ve showed us a lot of amazing things. There’s so much more. Where can people follow you if they want to talk about nerd out about spreadsheets?

ANTHONY TEDESCO: Absolutely. I would say, you know, feel free to reach out to me on LinkedIn. You know, that’s probably the best place to find me. I’ve been trying to kind of post some of the content I’ve been putting together on this topic of Excel and search engine marketing and how the two kind of work. You can also find some of my content on search engine land.

You know, that, that was what got Fred’s attention. Some of those articles there.

FREDERICK VALLAEYS: Exactly.

ANTHONY TEDESCO: So, you know, I’m going to continue to kind of try and build out some content, keep my name up on that list. And, you know, when it comes to sharing out templates that I’ve walked through today, or, you know, discussing.

Specific use cases. I’m all here. I’m all ears. You know, I love to kind of solve these problems. I tend to try and help my family members like when it comes to things that are not related to search engine marketing at all. One thing in particular that’s become you know, maybe kind of in interest of mine is, you know, Creating wedding invite lists and using categorization, categorization and pivot tables and all those types of things.

There’s a million different use cases for Excel and, you know,

FREDERICK VALLAEYS: Anthony, are you getting married or family members getting married?

ANTHONY TEDESCO: What we’re doing is we are looking at the distribution of, you know, the different kind of people in our life. And like how those kind of invite numbers line up it,

FREDERICK VALLAEYS: you know, Are you suggesting there’s an audit for whether you’re compatible to get married to basically Based on the distribution of the,

ANTHONY TEDESCO: that’s still a work in progress.

That algorithm, you know, we’re still building that one.

FREDERICK VALLAEYS: Look for Anthony’s next startup, some sort of you know, matchmaking app, all based on Excel spreadsheets. Okay. This is fantastic. Hey, Anthony, will you come back and show us some more stuff? I’d

ANTHONY TEDESCO: love to. And I’ll you know, I’ll make sure I clean up my Excel examples a little bit more.

So thank you everybody. Really appreciate it.

FREDERICK VALLAEYS: Yeah, thanks for watching. Hey, and if anyone needs more help with PPC management, of course, there’s Excel, but you also have tools out there like Optmyzr, the company that I run, do a two week free trial. So check it out, give it a look. Our team is amazing, giving you a demo, showing you exactly how we can solve your business needs.

And if you’ve enjoyed this episode of PPC Town Hall and hearing from Anthony. Press the subscribe button at the bottom. We’ve got new episodes all the time and talking about PPC and how that’s evolving and how you can do it better. Thanks for watching. Thanks for joining us, Anthony, and we’ll see you for the next one.