[Transcript] The Science of Excel For PPC Marketers With Bing Ads

Bing brings the third webcast in their ‘Advertiser Science Series’, the agenda for which was to discuss the tips, and tricks for harnessing the power of Excel. MJ De Palma hosts this session along with Eric Couch from Bing’s team. Master the art of excelling in Excel with this webcast and learn how to turbo charge your PPC analysis with the help of Excel formulas and other plugins.

You can view the video of this webinar here.

Here’s the transcript of the video:

MJ: Welcome everyone, to the Bing Ads webcast on The Science of Excel for PPC Marketers. My name is MJ DePalma and I will be your host today. This is the third webcast in our Advertiser Science Series, and we are excited to share concrete methodologies of harnessing the power of Excel for your marketing. Before we begin, let’s review some housekeeping items to enhance your experience with us today. The webcast console that you see is customizable on your site where you can click the content widgets that you see on your screen and move them around or size them for your screen size.

You can expand your slide area by clicking on the ‘Maximize’ icon on the top right of your slide area, or by dragging the bottom right corner of the slide area. If you have any technical difficulty, please click on the ‘Help’ widget. It has a ‘?’ mark icon and covers common technical issues. We recommend, if you can, to leave all the widgets as they are, so you have the ability to see all the great resources at the same time. This webcast is jam packed with great information, and because of that, we most likely will not have time for Q & A at the end.

However, feel free to tweet your questions to Bing Ads and Eric Couch, if they go unanswered using the #AskBingAds, and Eric will do his best to answer them for you. We will also be following up with a blog post on any unanswered questions, so be on the lookout for that in the follow-up e-mail after the webcast. Also, you will see a ‘Resource’ widget in the upper right hand corner we’ll refer to during the webcast. Go ahead now, and download the Excel Spreadsheet you see, so you’re ready to follow along later with Eric. Also we found out that the PDF of the actual presentation was not downloadable, and that’s probably because the presentation is so large.

So, we’ll fix that on the ‘On Demand’ portion, we’ll figure that out – it might be a PDF of two parts, so be on the lookout for that. Also there’s a twitter widget that’ll enable you to tweet right from the webcast with the #BingAds and the #bingadswebinar# – that’s kind of redundant, but that’s what it is, which appears to your upper right under ‘Resources’. We’ll also provide “All put your hand out” to the speakers in the next slide. Let me introduce… As I said, my name is MJ DePalma, and I will be your host in monitoring all Q & A. And we also have Eric Couch, who’s our Client Development and Training Manager, and as you can see, he’s also an Excel Superhero, in that picture.

Eric: More of an Excel dork, but I appreciate the enthusiasm, or I guess the… yeah, the super-hero designation. MJ is actually the person who took that photo of me.

MJ: That’s right.

Eric: It is, yeah. I felt compelled to kind of like rip that open, and just let, you know, let my Excel flag fly!

MJ: You’ve gotten a lot of mileage out of that here.

Eric: I really have. A lot of arresting comments, but you know, all good things. And I’m not ashamed.

MJ: Well, you truly love Excel.

Eric: It’s true, it’s true.

MJ: It suits you really well. Also, just to mention the ‘On Demand’ version, just so you know, for you and your colleagues, will be available approximately two days after this webcast, and can be accessed using the same link you’ve registered at, which is aka.ms/excelforppc. If your colleagues or friends couldn’t make it this time, be sure to share that link with them, and you’ll get it in the follow-up e-mail that I mentioned earlier. Last housekeeping item, we would love your participation in our survey at the end of the webcast, so that we can improve in any way possible. So, let’s get started. What’s on the agenda?
Overview – Power of Conditional Formatting and Pivot Tables
Eric: Oh well, I was going to ask you what’s on the agenda, but I guess I’m the one who put all this together, so I should probably know that, right? So yeah, what we’re going to talk about today… We have a lot of ground to cover, so here’s what we’re going to cover. The power of conditional formatting and Pivot Tables, we’re going to talk about some ways you can use Excel to turbo charge, some insights you get from the BingAds interface. We’re also going to talk about how to combine Excel formulas like a mad PPC scientist, and refer some new formulas at the audience that you may not have heard before, especially because we only just released them. I’m also going to talk about how you can use some plug-ins, like Solver, to re-arrange and incrementally add budget within your ROI goals. So, that sounds good?

MJ: That sounds awesome, Eric.

Eric: Alright.

MJ: I do want to touch on that, you know, we’ve been doing a lot of client trainings, and a lot of events, and from those events, we’ve heard over and over again –this is where this webcast and this content actually came from, is that our customers really wanted to know, “How can it be more data-driven simply by using the tools that I have? How can I be more powerful? How can I be more insightful?” And so being at Microsoft and having Excel, it‘s like such an amazing magical win-win.

Eric: Yeah, I mean if you want to work with spreadsheets, you might as well work on the program that has the best spreadsheets on the universe.

MJ: That’s right.

Eric: So, let’s get started, right? So yeah, the two MJ mentioned, so the ones we’re going to be talking about today, but three in particular: Excel, so hopefully everybody here has that. I know I do. It’s going to be kind of critical, so I’ll walk through this, but we’re also going to talk about Auction Insights, the Campaign Planner. And the one you’ll see here, it’s grayed out, it’s Bing Ads Intelligence – it’s another plug-in for Excel. We’re not going to talk about it today, but…

MJ: We will be doing another webcast coming soon that’ll be dedicated to Bing Ads Intelligence.

Eric: Absolutely. It’s kind of like the hidden gem of Search Marketing, so just kind of there to whet your appetite. So, if you want to follow along, at any point, you can go to the upper right corner of the webcast console and download the file, it’s the ‘I Simply Excel Demo Workbook.’ There is pretty much everything we’re going to talk about in there with some pretty detailed instructions. So f you miss what we’re talking about, or if you want to, like, revisit it any time, just download that and it’ll be there at your fingertips, ready to go.

MJ: Awesome.

Eric: Alright. We’re ready?

MJ: We’re ready.

Eric: We’ve a lot of ground to cover, so we’re going to fly. So, our first thing to talk about is, “Let’s avoid spreadsheet blindness with the power of conditional formatting.” So, what is spreadsheet blindness? Well, spreadsheet blindness is an affliction that unfortunately affects about 90% of search engine marketers. Basically, it’s when you’re staring at a spreadsheet full of rows and columns that go beyond counting, and you kind of go blind, like your eyes feel like they’ll get set on fire, like it is just, you’re not enjoying looking at, you’re not able to figure out what kind of insights you’re able to derive from these spreadsheets. And we want to help you out. There are some tools and some tactics you can employ to help you.

So, what is conditional formatting? Well, conditional formatting is an Excel function that allows you to format things conditionally. Well, the non-sarcastic answer for that is, conditional formatting is a function that allows you to automatically change the format of a cell based on values and parameters that you dictate. And you can actually change a lot of different things with it. Like, it’s not just limited to… well, really, it’s not limited at all! So you can change text color and format, you can also change cell color – so that’s going to be one we’ll talk about at length. You can also add some bar graph overlays to your data, and then insert some very pretty graphics. So I’m going to warn you now, like that’s about as fancy as my design knowhow gets, so if you’re expecting anything prettier, you’re probably going to be disappointed.

But how do you find it? So, it’s not really hiding anywhere, it’s one of the default functions in Excel in the ‘Home’ ribbon. You can find it kind of on the right hand side, kind of squished between the ‘General Setting,’ and also, like the ‘Format of Table’ stuff. Now, if you’re not seeing it, may be your Excel window is not quite long enough. But once you do have it there, and you click on it, and you get dropdown menu, you get a lot of different options. So you can highlight cells according to values that you set, so things like ‘Greater than,’ ‘Less than,’ ‘Between,’ and ‘Equal to,’ or you can also get fancy and look at like, certain text things, like look at things with like ‘Text that contains,’ certain characters or certain things that you may be want to highlight, like may be an ad copy.

You can also look at things with pre-defined rules. So if you don’t want to do things by, you know, ‘Custom’ nature, do just the top 10 or top 10%, or bottom 10% of things, or like above and below average items, so make sure you look at your key words and conditionally format the ones that are like above or below certain, like, thresholds for conversion lines, right? Or like, the top 10 – just isolate certain themes there. You can also layer data bars over your spreadsheet. So this can be really useful for quantitative metrics. Things aren’t necessarily good or bad, just need to know how many of them that you have – things like impressions, whereas this other one is really great for qualitative metrics, which are color gradients.

So if you want to…, for further learning, if you want to create things like a heat map, this is where you want to go. Like if you want to look at things, like whether they’re good or bad, based on from how high or low they are in a scale, you can use this to figure that out. And then you can also insert some ‘Icon Sets’ in line with your data. You know, if you’re not a big fan of, like, just some of the color things, like instead you’re more visual, you want to do, like, some arrows or you know, some other color-coded icons, you can do that instead.

And even below that, you’re not just limited to the ones we’ve highlighted here; you’re going to also create custom rules. And so, believe me, creating custom conditional formatting rules is probably a webinar unto itself, because you can get very, very, very in-depth with the color gradients, and like, all the different types of options in there. So we’re not going to dive into that today, but you have the option to customize any of these rules in a way you see fit with those custom rule there – you just highlight ‘New Rule.’ So, how can we actually use this? So what’s an easy way that you can get started using this function in kind of a scientific way?

And the easiest way is creating a dayparting heat map, doing it by campaign, or even in the account level. So to use conditional formatting properly, so you want to download it, and so we’ll talk little bit out over here, so what you want to do is you actually create an account from the Report Center, making sure the unit of time is ‘Hour of Day,’ and then you download that report to Excel. And if you want to, you can add a ‘Campaign,’ like make it a ‘Campaign Report’, not just an ‘Account Report’, and so what you want to do is, this is like the raw output that you get. So, this is like, spreadsheet blindness personified. So, MJ, what am I supposed to do here?

MJ: Well, you’re supposed to do conditional formatting.

Eric: Yeah, exactly. Because you really can’t tell. Like, unless you want to stare at these numbers and try and, like, figure out what it is you’re supposed to do, like you…
MJ: I have no idea where to look here.

Eric: Exactly. So, to use conditional formatting properly, all you have to do is highlight each column individually and select the scale you want to use. So in this case… like, I’ll show you the wrong way of doing it. So, if you highlight every single column and just do, like, one scale for all of it, it actually gets kind of thrown off, and it look sort of like a mess, like this. So, this doesn’t actually tell us anything. Like it’s…all it’s really telling me here is that “No kidding!” or “No doubt, impressions are the highest value on the spreadsheet.” That’s not actually helping us at all.

MJ: No.

Eric: Instead, if we highlight each column individually, and apply the appropriate color scale… So, you know, for things like cost-per-conversion, making sure that lower numbers are better, or for click-through rate, making sure higher numbers are better, now if we do that correctly, then it’s actually, we get a more accurate picture of account performance, by time of day, visualized in heat map form.

And then, if we layer some gradient bars, you know, to these quantitative metrics, it actually provides even more insight, like, we know what the ebb and flow traffic is over the course of this day in terms of both impressions, clicks, spend and conversion volume. And then we see our qualitative metrics with, like, this red and green contrast immediately highlighting some opportunities here. So MJ, just looking at, like, the contrast – so like, the red versus the green, like what are some of the opportunities I have here? Like, so maybe you like bid more?

MJ: So, the green where it says line number seven, eight…

Eric: Yeah, so we’re doing really well there.

MJ: Eeven…

Eric: Exactly.

MJ: …where it’s not as green, you can probably gain more there.

Eric: Yeah, exactly. And so when you look at something like average position, like, where I’m doing the worst in the day, so I have that one, like, midnight hour – that’s pretty bad. But I also have those hours from about, it’s about like from 4 p.m. to 6 p.m. in the afternoon for this particular account, like my average position is pretty bad, and my cost-per-conversion number’s actually pretty good, right?

MJ: Oh yeah.

Eric: Exactly. So, like, looking at those contrasts, I can pretty much immediately see an opportunity to implement a dayparting bid modifier, to be a little more competitive there. So, that’s the big thing when working with heat maps, is to, like look at the contrasts, if you’re looking at like this sort of dayparting thing, but that’s not the only use for it. And so, this is a dayparting heat map, just looking at, like, time of day. We can get a little more advanced. So, this is the same kind of heat map here, only now, with added in ‘Days’ in addition to ‘Time of Day’, so we’re seeing the entire week, and we can see, you know, some pretty clear, like general themes; like, so early morning hours, pretty much regardless of day, probably not good for us.

But Sundays, pretty terrible for this account, as is, you know, some of the evening hours, you know, those of the dinner hours from about, like, 5 to 7 p.m. So like, we can get some pretty clear directional insights to what I’m supposed to do here. But it’s not even just limited to dayparting, like, that’s not the only use case for this. So this is a super slick ad copy, ad performance heat map – this is an older version of it. So, you can actually get this, like, if you have Bing Ads account managers, they can run this for your own accounts, or I can teach you how to do this which, you know, we’ll go into this in just a second. But can you imagine trying to figure out what the best combination was, of like your ad title and ad description, if these were all just raw CTR numbers?

MJ: No.

Eric: Exactly. Like, they’ll just be, yeah, it’ll just be like a bunch of numbers on a big, wide spreadsheet, totally incapable of figuring anything out. But if you use this conditional formatting and look for, like the greenest cells, you’re able to figure out pretty quickly, like, what your best combinations are. And for this particular industry, this is for Auto Insurance, and what we’ve done here is, we’ve looked at, like, what we’re using in the ad title, and then also, like the ‘Call to Action’ or the ‘Features’ or the ‘Benefits’ in the ad description line, and pit those against one another to figure out what’s the highest click-through rate. And we found that mentioning ‘Free,’ and ‘Safe,’ [don’t] figure for Auto Insurance, but that’s the most popular combination.

MJ: As well as, I see, ‘Fast’ and ‘Safe,’ and…

Eric: And also , like, ‘Quote,’ and ‘Safe.’

MJ; ‘Safe’ is pretty…

Eric: Yeah, ‘Safe’ seems to be like the universal best performer there. And you know, we add in a couple of other extra elements there for flavor, but ‘Safe,’ particularly in that ad title, like, that’s the key. Now, MJ, there is one common theme with these two heatmaps – they had one thing in common. Do you have any idea what it might be?

MJ: Tally.

Eric: Tally? Okay, I guess you did read ahead on the presentation.

MJ: [laughter]

Eric: So the one common theme that they had is that they both came from Pivot Tables.

MJ: Awesome.

Eric: So yeah, like the ad performance heatmap, if you know how to make a pivot table already…I mean, I’m going to go into it just now if you don’t, but if you already do know, like that ad copy performance heatmap came from a pivot table. So, when you use something like conditional formatting on a pivot table, it kind of amplifies the power that a pivot table already has, and like to isolate, and look at specific segments of your data. Like, conditional formatting makes it that much easier to read.

So, I guess it’s time to kind of dive in to talking about your new best friend, or may be rediscovering your old flame, the pivot table. I’ve put this picture here, which is the man’s best friend; well, PivotTable’s really, like, the Excel user’s best friend, especially when it comes to PPC. Like, the Pivot Table’s one of the very first things that I ever looked at when it came to learning Excel.

So let’s go through a hypothetical situation here. So let’s say you needed to pull the following report. So we need to look at a Keyword Performance Report, a six-month’s performance broken up by Month, Match Type, Devices and Conversions. So if I go to the Bing Ads Report Center, I can download a Keyword Report with all that stuff in there, but the problem is, it looks like this.

And so, it’s too much data, you have the same metrics that are repeated over and over again. So if you look at, like, we have the ‘Ad Group,’ and the ‘Account Name,’ and all that stuff – like, it’s really hard to simplify this or, like you figure out, like, “What this report is actually supposed to tell me?” Like I’m looking at a specific thing here, and it’s not giving me anything, right? So the issue here is that this report on its own isn’t really actionable, or I mean it’s actionable but you have to take some time to really dive into it, right?

Now, enter the Pivot Table. So I’m probably preaching to the choir here, so let’s all kind of like have a mutual love fest with the pivot table. But just for those of you not in the know, well now you are, a pivot table is a program tool that allows you to aggregate and compare data from selected columns and rows. And then you can manipulate that to obtain a desired report. But in English, it’s a reporting tool that you do a bunch of crazy stuff with, although the short answer is that it’s awesome; like it’s my favorite tool in Excel pretty much. So, let’s kind of give the comparison here.

So, the standard Excel table – so, it’s a raw output, it’s not really actionable or obvious, and l requires some adjustments to actually use this as a report. Like, you wouldn’t want to send that giant wide Excel spreadsheet to a client, or to your boss or to anybody who cares about what it is you’re doing, right? Whereas the pivot table, you can summarize that data in easy-to-digest formats. You can also quickly compare those subsets of data together, and then reveal patterns and relations in that data, and really, it just allows for faster analysis.

So if you look at, you know, our original problem here… so, considering that question we had, so which keywords get the most clicks, broken up by match type on computers? We’ll be focused on these four elements at the pivot table – Rows, Values, Columns and Filters. Well, we can actually see that. So if you want to look at what it actually looks like when you’re manipulating these Pivot Tables, so there are four elements – Filters, Columns, Rows and Values – those build and define your pivot table.

And so, by dragging and dropping those, like these elements into these different fields, you can create a pretty robust report, though… I want to say this – you can get there in many different ways with a pivot table, like either by rearranging stuff on the Rows or the Values or Columns or Filters, like, there are millions of ways to get this data there, so it’s really flexible. Now there are a few steps to performing a perfect pivot. So, one is determining what the table should display, selecting all the data you want to pivot, navigate ‘Insert’ in pivot table, you choose a location for your table and create it, and then drag and drop ‘Field List’ elements into place.

And just for those of you still on the line, we’re going to go into some more advanced applications of this. We just need to kind of set the foundation. Now, let’s determine what the table displays. So, at the most basic level, so the basic form of this question is: Which keywords get the most clicks? Now to figure that out we put keywords in our Rows field and values in our Clicks field, right? So now it’s looking at clicks- per- keyword, so pretty simple.

Then we layer on another element to that. So, match-type, so we put match-type in Columns, and now we’re looking at clicks-by-keyword, but also broken up by match type, so again, getting a little more advanced, and then adding in that device filter, and now we’re looking at ‘Computers’ specifically. So now we’re looking at exact clicks by device, like, on PC – ‘Phrase’ clicks on PC, like, on a keyword level. So, once we do all that… So, how to actually create it? Selecting the data – well, make sure you select it – so, select all of the data because you never know what you might want to add in your pivot table. So, yeah, that’s just kind of like common best practice and there are some quick keyboard shortcuts – I’ll show it in a second that will actually kind of basically teach you how to do it much faster.

Go to Navigate ‘Insert’ > Pivot Table; now this will prompt you to choose a location for the new table, and so the best practice here, once you like get that option to Insert pivot table somewhere, just create it in a new spreadsheet, like, or in a new worksheet. Don’t’ create it in the actual sheet that contains all the data, because that’s going to make things kind of messy. And then, drag and drop your fields into place. So you’ve just physically moved them into the proper quadrants. So make sure ‘Device’ – you drag and drop it over in ‘Filters,’ and do the same thing for the rest of those – like in the order that we talked about.

And so, what that does is, once you kind of got that all arranged is, it tells you pretty specifically, like, so you’re looking at which keywords get the most clicks broken up my match-type on computers, and right now, because of this pivot table that we’ve manipulated, we can find out that it’s this modified, broad, ‘Scrubs Discount’ keyword that’s getting the most clicks. Now, some of you might get… that’s fairly basic, right? So, fine, fine, I hear you, so I’ll show you my very favorite application for Pivot Tables, which is Normalized Quality Score.

And so this came to me like, I learnt this a couple of years ago from a really smart guy by the name of Brad Geddes, so if you don’t already follow this guy in twitter, he’s at @bgtheory, he’s somewhat of a PPC genius, a good friend of ours Todd. He’s an ad copy, like a guru, so if you want to know about, like, to not just to sell stuff, but ad copy, he’s got a forum, and he taught me how to do this. So, this is Normalized Quality Score. So, why is this? What’s the big deal about this? So, the problem with the normal Pivot Table is, when you look at something like quality score, is that it uses averages. And averages lie.

So if you think about, like, we have five keywords in this ad group. Well, if you think about the average quality score of this, well, technically it’s got an average quality score of 8.6. Now, I don’t know about you, but clearly there’s a problem here, right? Like, I’m just highlighting it for everybody to see, it’s that big ‘3,’ like this is standing out, it’s kind of like a sore thumb. And it’s especially problematic if we have a situation like this, where that quality score coming keyword is actually driving the majority of the impression volume in this ad group.

Now, if you’re just looking at the average quality score of this ad group, like you’re not going to able to see that, like, there is this one out liar here dragging the whole thing down, like it’s kind of masked by that average, right? So how do we actually account for this? Well, I’ll show you how. So, luckily we can tweak Pivot Tables to identify this issue in our account. So seem to add some columns to our data sheet, use a calculated field and then sort and filter the results. How do you do that?

Well, it goes a little bit something like this. So if you want to create this yourselves, go to ‘Keyword Report’ from the Report Center, make sure it includes ‘Spend,’ ‘Impressions,’ and ‘Quality Score,’ and then make the date range for the ‘Last 30 Days.’ Now, one thing – I’m going to share some of my favorite Pro tips with you. So your Excel spreadsheet was not carved in stone. Now, you may download it and you may seem to think so, but it’s not, so you’re more than welcome and free to change it, or add to it – like add some columns to it – it’s okay, which is exactly what we’re going to do here. So, for this particular spreadsheet, add a column to the end of it, and title it ‘Quality Score x Impressions’. And so what you want to do is, for each of your keywords, multiply your impressions by your quality score in that column, and then pivot table it.

And then, just another quick Pro tip here – Control + Shift and the arrow keys make selecting your pivot table data way easier. Like, if you’re working with a lot of rows and columns of data, don’t drag and drop it, like, just use the Control + Shift + arrow keys to select just those populated cells, and it makes your life much simpler. Now put your ad groups in the ‘Row’ field. So you’ve created pivot table in a new spreadsheet, put your ad groups in the ‘Row’ field, put average of ‘Quality Score’, sum of ‘Impressions’ and sum of ‘Spend’ in the ‘Values’ field.

And then it’s going to look something like this. Now you use calculated fields when you’re working with aggregate derived metrics in a pivot table. Because again, like, if you try and take something like average of Quality Score or like, sum of CPC for, like your ad groups, like, it’s not going to calculate correctly, or especially not the averages. You want to make sure you’re creating a calculated field for these, so it calculates the actual, like, honest CPC. Otherwise it’s going to take the average CPC, of like all the elements inside that ad group. So make sure you use a calculated field for that kind of stuff or when you want to calculate something very interesting.

So, how to actually make use of calculated fields? So, when you’re in your pivot table, you can find out under ‘Fields, Items & Sets’ in the Pivot Table Tools ‘Analyze’ tab. And so, for this calculated field, name it ‘Normalized Quality Score’, and for that formula, make it the ‘Quality Score x Impressions column/Impressions.’ So don’t make it actually, like, make sure the quotations are there, like that’s the important part, make sure you’re selecting that column and not just trying to make it, like multiply your Quality Score x Impressions here. That’s not what we’re trying to measure.

So, the reason this works is because we’re looking at this at the ad group level, not just the keyword level. So what we’re going to do here is that by weighting for these Impressions, we’re able to quickly identify ad groups that have poor quality score keywords driving all this traffic. So, if you remember this issue here, so, this ad group had an average quality score of 8.6. Well, if we go through this map, this extra map here, it actually shows us that if you weight for Impressions, it then had a Quality Score of 4.3 – a weighting quality score of 4.3, which, you know, if you know your quality scores, that’s bad.

So, what is dealt here is, you can now analyze your ad groups by their true quality score and quickly determine where you should break out your keywords. So this is a really great way to figure out where structural changes need to happen in your account, because you have, like, some keywords in, like, a specific ad group dragging things down. So break those keywords out into their own ad groups to try and improve their quality score.
Now, another Pivot Table Pro tip – you can actually…., and this is not one many people know. You can actually sort and filter a pivot table by more than just the row value. Now this might actually be some kind of bigot or something not in tandem because sometimes it will break things, but you can actually use ‘Sort and Filter’ on the cell right next to the top row on your pivot table to create ‘Filter’ buttons next to, like, every single or at the top of every single row in your Pivot Table.

So in this case, like, you want to have it at that cell, hit ‘Sort and Filter,’ and then you can start sorting and filtering by the actual, like, values and, like, the sum of Normalized Quality Score. And so if you do this, you can actually start looking for the following things, which are, like, the big red flags. So look for a Normalized Quality Score of 4 or below or even 5 or below, that’s when you know, like you have really big issues; sort descending by ‘Spend’, because you can do that with that ‘Sort and Filter’ in place now, even though it will again start to make your pivot table get a little bit funky, like don’t do this too often, but it definitely helps.

And then look for a large difference between your Average Quality Score and then your Normalized Quality Score, because that’s when you know, like, that’s where the biggest structural issues are. So if you’re not, like, at all impressed by that or satisfied… Well, I know, I hope you are, but if not, I will come back, and this is either a threat or a promise, depending on how much you like Pivot Tables. I will come back and talk another hour about how to pivot table a 100,000 lines of SalesForce lead data with monthly campaign performance reports. That sound good?

MJ: Sounds awesome, Eric.

Eric: Alright. So that’s kind of Pivot Tabling, and again, so if you wanted to do something with your Ad Copy Performance Heatmap, you have to create a Pivot Table based off of an ad report, just create an extra couple of lines, like one for ‘Ad Titles,’ and one for ‘Ad Descriptions,’ and then manually enter in, like, the elements that you wanted to Pivot Table. And so, then just use, like, a calculated field for CTR and then layer conditional formatting on top of it to create that heatmap. Sounds fairly simple and straight?

MJ: It does.

Eric: Kind of? Kind of?

MJ: Hopefully.

Eric: Hopefully. Well, the instructions are all there, so again, once you guys get access to something on this presentation, might come in two parts, but you should be able to reverse engineer the process from that.

MJ: Yeah, and then next couple of days, this deck will be available in a couple of parts for downloads at the same URL that you used to register and log in from today’s presentation. It was way too big, I didn’t realize that until a few minutes before we started unfortunately. I apologize for that. But it will be available, everybody was asking for it, so thanks very much.

Eric: All right. So let’s jump on to…. Again, we got a lot of ground to cover, so let’s talk about how to turbocharge the Bing Ads Interface with Excel. Now we’ve a lot of really cool tools that not all people know about, especially ones that are fairly unique to our platform. So the first one I want to talk about is the Campaign Planner. So if you want to go, find the Campaign Planner, so we have it in the ‘Resources List’, but it’s also at just: bingads.com/campaignplanner. And the reason we like this so much, the reason it gets really cool, is that this is a way for you to plan campaigns with better insights.
This is really a great way to plan, like overall campaign strategy at an industry or a vertical level, because you can monitor marketplace changes, traffic trends across your industry, and then also discover new campaign ad group and keyword ideas.

But the big reason that we’re so excited about it, meaning I get performance and traffic data, and vertical on product insights, but the thing that really sets us apart here is, we give you competitive insights by keyword in industry. So if you want to look at specific keywords you’re not even advertising for yet, we give you competitive insights about, like, the domain, the types of advertisers you’re going to be going up against. So if you want to look at, like, something like ‘Running Shoes’, or you know, ‘Video Game Consoles’, you see, like you’ll be going up against “Xbox.com” and “Microsoft.com” and also would start to get an idea of their ad coverage, like, how often are they showing for this term, what’s their position coverage, where they are at, like, on the page, average position – sideball, mainline, we give you all those insights.

And then you can also ‘Pin’ and ‘Favorite’ these terms for later use. You can also see these trends by keyword and over time, so you get keyword vertical trends and performance data by looking at this ‘Summary tab’. So you’re going to have to look at it per month or also a year over here. And then again, you can see these competitive market places before you even start advertising by using the ‘Competition’ tab. So, you know, as an example here, we set up this ‘Macbook Air’ term, so this is usually a video that we show where it’s kind of like paint a picture for you.

So, we saw a really cool use case here where within this ‘Macbook Air’ term, like we saw all these trends, so we saw ‘Spike’…. Oh, it’s actually going! So we are to look at it, look the trends here, and so for this ‘Macbook Air’ term, like, we’re looking at basically, like, what’s the traffic like over the course of a year. And so, no surprise here – it really spikes going into those winter months, so it’s, like, Black Friday, Christmas, Thanksgiving shopping. And so, we can also really see, so when it happens, but also on which devices. So, we have this device breakdown, and so we see that this spike is really happening on desktops.

And then we can also take a look at, like, just device performance in aggregate too, because may be, like, we’re getting, like this is a really good way to to help you pitch, like, a mobile strategy. We need more time on this, so I’m going to kind of move on, but the big thing that we’re going to see here is that when we go to this ‘Competition’ tab, you’ll see that we’re advertising… Like, let’s say for the sake of argument, don’t tell anybody, M.J, that I’m Apple, and I’m seeing that on the ‘Results’ page, this Microsoft surface pro 3 or pro 4 ad is hung up above us on like our core term.

This might be a problem, right? Especially if I’m Apple’s SEM manager, like, may be my boss sees this, and they start blowing up my phone, asking, like,”What’s going on?” Like, “Are you actually doing your job?” And well, this report can help you circumvent that; it can you help you say, like, “Okay. Yes, this is happening, but it’s only happening, like, maybe one in five searches, because Microsoft’s ad coverage is only about 20%, their ‘top of page’ rate is only about, like, 8% of the time.” So this happened to be, like, the once in a lifetime showing on the surf that causes to be a problem. And you can export all of this to Excel at any time too, so you’re not just limited to looking at the UI.

But we also have some more insights here. So we can look at this ‘Seahawks Jersey’ term, so once this actually advances here, we also get location insights too. And so, I used to, like, sit in front of the guy, whose name was Tony Austin, a really good friend, also a rabid Seahawks fan, and I have a working theory that may be Seahawk fans, you know, some are Nevada [inaudible 34:25] fandom, like maybe they, who turned more of a bandwagon fandom, and I kept on giving a hard time about this, you know, I said, like, “Oh, they call themselves the top man because none of them had fans until 2012.”

MJ: [Laughter]

Eric: That’s how I stepped out of line a little bit, but that was because the breaking point is, “Okay, you can’t prove this,” and I was like, “Can’t I, Tony? Can’t I?” And so, using this tool, you could actually see that there was a pretty significant spike in ‘Seahawks Jersey’ traffic in Washington over, like, when they went to the SuperBowl. This was in January 2015. But this isn’t just used for winning dumb office place arguments; you can also use this for campaign planning.

So you can use this for budget forecasting because we give you the impression volume month over month, and with a little of Excel knowhow, you can actually plot this out and figure out what those numbers actually mean. So this is what it looks like when you hover over each one of those data points and record the impression volume, and then put this is Excel, and then with a little bit of conditional formatting you can then, like figure out, “How does this compare to the average traffic volume in this industry?”

So this is for ‘Education and Training,’ and what we found is that numbers-wise, there was actually about a 40% dip in the summer months and about a 20% rebound in the fall months. And so how this could help you, is that this can actually influence budget discussions. So if you’re planning out your monthly budget, like, month by month by month, and you know, the person who’s making that decision had the expectation that you’re going to be able to spend as much in the summer months that you were in the early winter months, then they might be disappointed.

So you can kind of get ahead of that conversation by saying, “Look, traffic volume is going to dip by about 40%, so we should probably account for that.” And then you can also take the same tactic with overall budget increases year over year because we provide you with those year over year numbers, and so the mean traffic volume, like, on a monthly basis is about 20% higher year over year, so you can also translate that to say, “Okay, may be your budget should also increase if you want to, you know, capture all that traffic.”

So that’s, you know, one particular use case, so this is all conditional formatting. But there’s another tool that I’m also a big fan of, and it’s ‘Auction Insights.’ So ‘Auction Insights’ – we used to have this in Bing Ads Intelligence but it’s not there anymore, but you can still find it in the user interface. We actually just released this, so we’ve released a Bing Ads Academy Cheatsheet about this – it’s on a YouTube channel now. I just tweeted about it so you can probably just find it in my history, I’ll re-tweet it after this webinar because we talk about this there too. And so this helps you to get some actual insights about your competition, so you get, you know, comparisons to other advertisers on the auctions in which you took part; you can also monitor the competition over time.

So ‘Auction Insights’ is not a static snapshot of your account, like say, it changes whatever date range you’re looking at, and you can discover opportunities and also issues by looking at five key metrics. So the key metrics you look at are Impression Share, Average Position and Overlap Rate – those are the first three. Overlap Rate is actually a really good one to focus on because this is how often did you compete against other advertisers. So if you see specific changes in this overlap rate, like, this is how you know somebody is gunning for you, like, specifically, you know, especially if it’s on a brand term. Like, if you see a really big spike in your overlap rate on a brand term, you know somebody is gunning for you.

It’s also kind of a backdoor insight into budget strategy or keyword strategy because if you’re seeing, you know, an increase in overlap rate, it means they’ve either specifically added this keyword, and it’s no longer just, you know, matching in a broad sense or a phrase match sense like they are targeting this specifically or they’ve increased the budget in which that keyword was, you know….to the campaign in which that keyword was located.

We can also have ‘Position Above Rate,’ which is how often did other advertisers show up above you, and then ‘Top of Page Rate.’ Now ‘Position Above Rate’ is also a kind of a backdoor insight into their bid strategy relative to yours, because if you’re seeing an increase in ‘Position Above Rate’, you know, in some advertiser relative to you – that means they’ve really increased their bids on that term too. So if you’re seeing an increase in a ‘Position Above Rate’ and then also ‘Overlap Rate’, that means, like on a brand term, that means they’re gunning for you specifically.

So let’s talk about, like a theoretically used case and actually used case, like to troubleshoot this. So let’s troubleshoot this account versus our competitor. So this is for a real life example here for a very popular diet keyword for an advertiser. They were dominating on this term, like running pretty much unopposed for the first half of January, you know, close to a year, diet terms very popular in the New Year; not a big surprise there, but we saw some performance issues pop up with this guy.

Like, their average CPC started, like it spiked, their CPA spiked too, and looking at the change history, there was nothing that this advertiser did to account for that. But if we look at their Auction Insights Report, we might find some more. So this is like the before and after snapshot. So before, this advertiser was pretty much running unopposed, so no use in animation there, but I’ll just give you the close-notes version of it. They had an average position of 1.5 and the closest Overlap Rate was about 46% and the Position Above Rate, the closest one was about 38%. Now that’s about to change.

So this is the after snapshot. So they had five new competitors enter this space, and then the Overlap Rate increasing from 46% to 70%, and then Position Above rate going from 38% to, like 81% and 91%, meaning that they are no longer running unopposed, and that’s what accounts for, like the big difference in their performance here. Now because this is an Excel webinar, I’m going to tie it back into the auctions like the Excel Insights here. So you’re going to run a pivot table, like download these ‘Auctions Insights Reports” over time, like with a monthly segment in place from some other platforms, or just you know, look at it from a monthly, like, month by month by month in Bing Ads and download that report, and then pivot table it.

You can actually see how your competitive landscape changes over time. So what we’re looking at here is pivot table of Overlap Rate over about a six months’ span and we’re seeing how seasonality influences our advertisers’ campaign strategy. And so this is for a bridal keyword, like, this is a ‘Wedding Dress’ keyword, and so we’re seeing an advertiser there in grey; I’m not sure if I can say their name out aloud, but they were… I can’t say it aloud, but they were a big, broader retailer and we saw that as they exited the wedding season, which is allegedly like peaks in July and tapers off after that, well, I guess, right? It was tapering off, and we’re seeing a drastic decrease in their Overlap Rate because they’re no longer spending as much. Make sense?

MJ: Make sense.

Eric: All right. Well, we also have some other things to go through, so you can also go through, like, Position Above rate which is again, closer analysis of our bid strategy, seeing how competitors’ bids change relative to yours. And in this case that orange line may or may not belong to a giant Seattle-based eCommerce retailer who really decreased their bids going in December. And so this is how something he can do with it. So these are the… Yeah, this came from a Pivot Table. And this is just datas you can get from the Bing Ads Interface. But let’s really note down here, so we talked about, like, all these other interface stuff, so let’s really get to the nuts and bolts of Excel, let’s talk about how you can use and combine some formulas like a mad scientist. Sound good?

MJ: Sounds great.

Eric: All right. So we already know Excel is an incredibly versatile tool, and almost every formula can have unique PPC applications. So I’m going to run through some of my favorites here and then may be throw some curveballs at you too. So we have ‘If,’ which is for making conditional ‘if/then’ statements like, “If my conversions are greater than 1, then increase my bid by 10%, otherwise make it 15%.” And that’s kind of what it will look like in practice. This is the really basic bid formula. But you can also make use of nested “if/then” statements for more power like, “If my conversions are greater than 1, and it’s below position 3, and it’s exact, then…” – do all that stuff I just said.

And this is kind of what that formula will look like. Again, so you can also see this on the demo sheet if you download that. Now I want to throw a curveball at you because we have some new formulas. We just released a couple of new formulas that have some actually pretty relevant PPC applications in the latest release of Excel 2016, and so one of those is ‘IFS’. So if you’re not comfortable with multiple ‘IF’ statements in a row, use this formula. So if you want to increase your bids by 10% for multiple conversions, but only 5% for poor average position, so this is what that looks like. And so this is really great for if you’re not wanting to make multiple ‘IFS’, like in a row because sometimes, like there’s some logic to it that isn’t intuitive to some people.

So this can be a really great way to make use of those nested formulas without having to actually know how to do it. Now the weakness here is that this is only for true statements, so with ‘IF’, with a regular ‘IF’ formula, you can make a value for ‘If it’s true,’ or ‘If it’s false’; you can’t do that here. It’s only for ‘If it’s true.’ So you’re not going to be able to make super complicated ones, the way you could with a really complex ‘if/then’ statement, but it is a way to make something a little bit more advanced. And that’s brand spanking new, so if you have Excel 2016, give that a shot because there are some variant format too, just like ‘MAX IFS,’ ‘MIN IFS,’ and so you can kind of play around with them, but we are not doing yet.

So we also have ‘LEN’ – I’m pretty sure I couldn’t do this job without ‘LEN,’ or really post a twitter for that matter, because it’s for counting the number of characters used in a cell which is really useful for things like ad copy, when you’re working with a limited number of characters. And so the nice thing about ‘LEN’ is that it also works with formulas. And so if you’re concatenating something together, like it actually counts the results of the concatenate, not just, like the number of characters used in that concatenate formula. So it behaves how you would expect it to, so I’d definitely recommend you use this, if you haven’t already.

But I also have a few others. So, CORRELATE or PEARSON, so this is really useful for analyzing the positive or negative correlation in that data set. So you can use this to determine how fluctuating campaign spend is impacting your overall business. Like, take a look at the impact, like, the correlation of non-branded spend on brand spend, or non-branded impression volume and brand conversions. Try and pit somebody’s metrics against one another using CORRELATE to figure out how they’re related. We also have Standard Deviation (STDEV) which provides the standard deviation For variance in the data set. This is also found in Pivot Tables, which can be really useful in comparing your bids versus the competition.

So if you really want to have your mind on at some point, run an average day report on your keywords, and take a look at the difference between your average CPC and your max CPC, and then also look at the standard deviation there, because I guarantee you, the larger the standard deviation, like the bigger the gulf is between your max CPC and your average CPC, and in that difference is where your advertisers was bidding you up and down over the course of the day, and, you know, it’s where your bids are a little bit more unstable.
Now, we also have ‘LINEST,’ which calculates the statistics for a line by using the “least squares” method. So you can use this to project future performance based on past metrics.

Now I’ll show you an example of this a little bit later. Then we also have ‘CONCATENATE’ which is for combining the contents of one cell with another, like we’re using this for ad copy, creating some Bing-friendly ads from an adWords ad, right? There are two description lines there, we’re combining them together to create one Bing Ad. It also can combine the contents of any cell with a text string contained in quotation marks. So if you want to create some modified broad match keywords, you would concatenate that together, or it’s concatenating a ‘+’ symbol to the beginning of, like the cell we’re targeting.

Now, we have another new formula here which is probably going to blow your minds a little bit. Again, Excel 2016 or later – it’s the formula ‘TEXTJOIN,’ which is a more powerful form of concatenate. And so , why is this so cool? Well, it allows you to automatically add delimiters. It also ignores empty cells, and you can join arrays of cells together. So if we have that same example from before, like we have an empty cell in the middle, and then we also have this, like delimiter in place, so using ‘TEXTJOIN’, to say, “Ignore this empty cell. Put a period or put a space in between the cells on concatenating, and also do this across an entire array of cells.”

So you have multiple cells to join, you can use ‘TEXTJOIN’ to do it way faster than concatenate, and in a much simpler formula. So give that one a shot. Now we also have one more to talk about or we’ve a couple more to go through, but ‘SUBSTITUTE’ is useful for substituting one character for another, which again is kind of also useful for creating modified broad match keywords. So if you remember that concatenate formula I showed you, this is the other half of that. So if you look at ‘SUBSTITUTE’ now, it’s substituting any instance of space with space bars to finish off the formula. But while each formula is useful on its own, if you combine them in nested formulas, you can achieve more, faster.

In this previous example, you can combine ‘CONCATENATE’ and ‘SUBSTITUTE’ to create modified broad match keywords in one formula, or you can also combine ‘IF’, ‘CONCATENATE’ and ’SUBSTITUTE’ to say “IF this is my modified broad match ad group, then do the CONCATENATE and SUBSTITUTE.” So you can get really in-depth with the kind of stuff you’re targeting. Now let’s go through a theoretical problem here. Let’s say you need to generate about 350,000 keyword-level destination URLs, and let’s say if they have unique locations, ad group and product identifiers – do you think you could that manually? Or, would you really even want to, MJ?

MJ: No, not at all.

Eric: Not at all, not at all.

MJ: I have too many other things that I would like to do.

Eric: Exactly. So the solution here is something like ‘VLOOKUP,’ so ‘VLOOKUP’ is one of my favorite formulas. It stands for ‘Vertical Lookup’. If you need to cross-reference a lot of data, this is really how you do it. Or, you know, you could also use ‘INDEX MATCH’ too, but this is probably a controversial opinion to voice on an Excel webinar, but ‘INDEX MATCH’ has always kind of struck me as the hipster version of ‘VLOOKUP’ in that it’s like, you know, it’s like vital, “Oh, you know, it’s just as good,” or “It sounds better, just way more complicated,” like not many people know about it. That’s probably a harsh version of it. It’s actually, like a little bit more flexible and it’s not quite as taxing on your Excel spreadsheets, but for most people ‘VLOOKUP’ is going to get you what you need, but if you know how to use ‘INDEX MATCH,’ I’m not trying to forsake the ‘INDEX MATCH’ – great function, but we’re not going to go into it today.

Now, this is the ‘VLOOKUP’ formula, so, looks a little bit intimidating, so I’m just going to break it down for the newbie’s in the group. So, ‘LOOKUP VALUE’ is what you’re trying to look up, the ‘TABLE ARRAY’ is the array of cells you’re targeting for this search, so it contains what you’re trying to look up as well as any corresponding values you might want to pull in. The ‘COLUMN INDEX NUMBER’ is the specific column you want to pulled in on this search, and then, ‘RANGE LOOKUP’, you just ignore.

And so, some small reminders here is ‘CONCATENATE’ or ‘TEXTJOIN’ combined cells, so if you want to combine multiple cells, then here’s a hint – cells you might have populated with ‘VLOOKUP’ because remember, we had this overall problem we want to solve … Well, this is how you do it. And then, that ‘CONCATENATE’ formula, so it can refer to our cells or strings of text. So, easy enough, right? So this is a quick walk through of just how you might use ‘VLOOKUP’ in a real use case to solve that one problem of mine. So we have six ad groups, six keywords with a need for six different destination URLs.

And so first off, you want to prepare a master list of URLs to cross-reference with your ad groups, right? Then you can also pull those in, so cross-reference it with that first spreadsheet; you get the URLs into our new sheet. And then you do also the stuff with ‘SUBSTITUTE’ formula to compare, to prepare our campaign and keyword columns for use of our tracking tags. And then also ‘CONCATENATE’ all that together in order to create six new destination URLs. “But wait! Hold on, hold on, hold on here. I thought that you just said combining, so do you not want to take five steps to accomplish one task?”

Well, in that case, don’t! Just do it in one formula, because everything I’ve just talked about can all be done in one single formula; concatenate these formulas together doing this entire process in one step rather than five. So we’re concatenating our ‘VLOOKUP’ with ‘TEXT,’ with ‘SUBSTITUTE,’ and all of that in just one formula to do this in one, like single step. And so this sounds, you know, super-complicated. Has anybody in the history of, like Planet Earth or PPC ever actually used something like this? Well, yeah, actually, like I used this back in my agency days.

By the way, that’s 18,388, so we were generating a unique keyword-level destination URLs for an automatically generated report from a tool provider. And so, it was for a Canadian client, and so if you do the map there, we had to do it for 8 provinces and 11 cities. So you multiply, like, 18,0000 keywords by each of those modifiers, equals 350,000 unique keyword URLs generated in about a half hour, and if I’m being honest, most of that was spent waiting for the formula to compute and also, like, brushing the smoke away from my poor laptop at that time, because it was really struggling with that amount of data! But all of that was just done through ‘VLOOKUP,’ ‘SUBSTITUTE’ and ‘CONCATENATE.’ Now, I have one more question for you, so, MJ, is there a way to use Excel to intelligently determine how to best spend our advertising budgets, and can you do it across multiple accounts?

MJ: Believe it or not, yes!

Eric: Yes?

MJ: Yes, I’ve seen you do it.

Eric: Yes, you’ve seen me do this, so…

MJ: And it’s a magical moment when you see it for the first time [Laughter].

Eric: Exactly. So you actually can do this, just not with regular Excel. So how you do this is with a plug-in called ‘Excel Solver’. So ‘Excel Solver’ is a plug-in, it solves things, and you know, but it will blast, like through the sarcastic [inaudible 52:32] within the…around here, but the real answer to this is that it solves equations that you give it according to parameters that you set. Equations like, “What is my most efficient budget allocation to maximize conversion volume?” So I found, like this is really useful especially for you agency guys out there; like, if you’re working with a lot of accounts across a lot of different platforms, like I know how working in those things can go on a day-to-day basis.

Like, you’re making budget tweaks, like may be you’re spending on some display campaigns, like because you have to make budget for the month, and then once you’re no longer in that position, like you forgot about that. Like, you didn’t forget to like tweak it, so you’re still spending that same amount on those less efficient campaigns. Like this is a [inaudible 53:11] free zone, so I’m not calling you out for it, that’s just life. So I mean, especially for you guys out there, who work at a lot of accounts, this might be able to help you out. And so, how can you do it? Well, so how do you find it?

Well, first things first, it’s not included by default. So you can find it by going to your ‘Excel Options’ menu, and then, going to ‘Add-Ins’. And so you find it right here. And so once you have it enabled, it’s actually in the ‘Data’ tab, found, like right there in the data ribbon in Excel. Now once you have it enabled, you can do super cool stuff like this. So this came, like, the genesis of this idea started with a good buddy of mine, his name is Sam Owen, and he works at Netflix now, on twitter he’s @SamOwenPPC. Super smart guy, he first showed me this Solver thing, and then I figured out you could do this across multiple accounts. And so what we have here is a mix of Bing and Google campaigns.
Ooh!

And we found the average day we spend across all these campaigns and then calculated the optimal budget as found by Excel Solver, because we’re not just, like asking, “How much can I spend?” but “How much can I spend given the fact that I still need to worry about conversion rate, cost per conversion?” Makes sense, right? So how do we actually do that? And so again, if you download the Excel demo sheet, all of these instructions are in there, so you can do this in your own accounts. But, you know, for the very, very short version of how to do this basically, you download a campaign report from the Report Center, make the date range for the ‘Last 30 Days,’ and make sure it includes the following columns: Average CPC, Cost, Conversions and Conversion Rates. And then also make sure it includes ‘Impression Share lost to Budget’. And then that’s just kind of what that report looks like.

You can also include the same metrics from other accounts for this cross-account optimization. Just make sure you include you “Impression share lost to budget” metrics all in the same column. So if you want to try this with your adwords data, download that same report from adwords and include “Lost IS (budget),” there’s probably like some percentage in there too, and then you can also do the same thing for your adwords display campaign. And just use the column ‘Display lost Impression Share to budget.’ Make sure all of those columns are in the same spot. Makes sense so far? Kind of? Well again, the instructions are all on that demo sheet.

So what I want to do here is find our ‘Average Daily Spend’ for each campaign. And so again, remember the date range you looked at here, so we looked at ‘Last 30 Days,’ so our formula is going to be Cost/30 days. And then we find the ‘Maximum Possible Daily Spend’ for each campaign, and that’s when we pull in the ‘Lost Impression Share’ numbers. So, that formula is you’re ‘Average Daily Spend’ that you just made divided by 1 minus your ‘Lost Impression Share’. And so that’s going to tell us, like not just what we spend on average, but what we could have spent on average, given our ‘Lost Impression Share’ numbers. So again, we’re just kind of figuring out how much we could have theoretically spent.

Now we saw several campaigns here that are limited by budget. But I know, like one of the biggest pain points I always had when I worked with an agency is, I would get like reports, especially with my adwords account reps, like sending me something like, “Hey, these accounts are limited by budget” and they always sounded like, “No doubt.” So the question here is, like we know we can grow our budget here, but is it a good idea to do it? And so this is where Excel Solver comes in. So we add in three columns: ‘Solved Budget’, ‘Solved Clicks’ and ‘Solved Conversions’.

And so what goes into each of those? We’ll tell you. So, ‘Solved Budget’ is left entirely to Solver itself, so we leave it blank. ‘Solved Clicks’ is where we take our average CPC, and what our ‘Solved Budget’ will be, and basically kind of reverse engineer what our new Click total would be. And you’re probably seeing where this is going. The ‘Solved conversions’ column takes our conversion rate and multiplies it by that ‘Solved Clicks’ amount and figures out what our new conversion volume would be. And then we use Solver to the highlighted cell there, so again, follow the instructions and you figure out, like, kind of, like, figure out what to do there.

Basically what we’re asking Solver to do is, say, “Give me as many conversions as possible.” Like, we’re highlighting one cell to say, “Make this as large as you can,” then tell your Solver to do this by changing your budget, so you’re highlighting the ‘Solved Budget’ column. And then you would say, “Make sure my campaign can actually spend that,” but putting in some constraints by saying, “This can’t exceed my Maximum Possible Spend,” or putting in some constraints on it, because otherwise it’s just going to funnel all your spend towards, like some brand campaigns, even though that brand campaign might not be able to actually spend it.

And so, what it’ll do is it’ll actually give you some stuff to do. So the highlighted campaigns on the screen, they’re actually getting paused, because from an efficiency standpoint, like we’re losing money. Based on my budget constraints, like I shouldn’t be spending money here; there are better places to spend that money because I’m looking for as many conversions as possible. So the highlighted campaigns are getting paused, and then it’s also providing us with some opportunities to boost budget, to reallocate that spend somewhere else, like there, you know, might be a brand campaign in Bing Ads. It might be like a generic campaign in adwords; like just giving you a more intelligent way to spend this money.

And so the one thing here is that you’re not limited to calculating your existing budget either. So using those Solver and LINEST, you can churn out the potential gains from increased spend and even see the point of diminishing returns because the directions will show you how to do this. But you can tweak the values in one of the constraint fields to play around with values and see the incremental conversion gains as you go, like spending in this account, and so that’s on the way that it gives you, so I tweaked it to say, like, “I’m spending 3000 a month, 9000 a month, and 18,000 a month, and even beyond that, and it’s giving me a conversion volume for that.”

And using LINEST just to target that array, it gives me basically the lie or like the equation for what I could be spending going forth. But the upshot here is that just by reallocating that budget and doing nothing else – no bids, no budgets, like no keyword additions, like I can get 1.2 more conversions per day, which was 36 more conversions a month, which for this advertiser was a 17.6% increase with no change except budgets. We’re trying to let you, like, “I’ll take that any day”, right?

MJ: I would.

Eric: All right.

MJ: I’m sure everyone on the call would do.

Eric: So you Excel, therefore you rock because today you learned about how conditional formatting can make your data easy to read. You also learned how you can use Pivot Tables to get insights you can’t find any other way – insights like Normalized Quality Score, and an Ad Copy Performance Heatmap. And you also learned how to use features like the Campaign Planner and the Auction Insights with Excel. You also learned about how to use some formulas like ‘VLOOKUP,’ ‘TEXTJOIN,’ and ‘IFS’ to get things done. And then learned about plug-ins like Excel Solver to do things a little more intelligently, spend your budget more intelligently.

MJ: Fantastic Eric! Ooh, that was a whole lot of information.

Eric: Yeah, it was a marathon. So this will be recorded, so you can come back to this at any time.

MJ: Absolutely. I definitely want to thank everyone who joined us today. I apologize that we did not get a chance to answer your questions but we will follow-up with a blog post on any unanswered questions. There are some great questions that we do want to answer, so we’ll be sending out an e-mail to everyone who joined with the ‘Replay’ link, please if you could take a lit bit of time and give us your feedback on this webinar, we would love to improve, hearing your feedback. Please also if you’d like, you can tweet #AskBingAds, also if you want your questions answered that way as well. Eric is happy to get your tweets at @ecouch11 and you see my handle as well there. So thank you everyone. Look forward to having you on our next webcast and until then, I’ll see you soon.

Eric: Thanks guys.

Related Articles:

• [Transcript] Bing Ads Advertiser Science Series – The Kevin Bacon Approach to Keyword Attribution
• Bing Ads Advertiser Science Series: The Science of Brand Bidding Webcast
• Bing Ads API Quarterly Call – Feb 2016