{"id":2358,"date":"2016-04-13T14:40:40","date_gmt":"2016-04-13T09:10:40","guid":{"rendered":"http:\/\/www.karooya.com\/blog\/?p=2358"},"modified":"2020-06-22T18:47:08","modified_gmt":"2020-06-22T13:17:08","slug":"excel-for-ppc-marketers-bing-ads-webinar","status":"publish","type":"post","link":"https:\/\/www.karooya.com\/blog\/excel-for-ppc-marketers-bing-ads-webinar\/","title":{"rendered":"[Transcript] The Science of Excel For PPC Marketers With Bing Ads"},"content":{"rendered":"

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<\/a> hosts this session along with Eric Couch<\/a> 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.<\/p>\n

<\/p>\n

You can view the video of this webinar here<\/a>.<\/p>\n

 <\/p>\n

Here’s the transcript of the video:<\/p>\n

 <\/p>\n

MJ:<\/strong> Welcome everyone, to the Bing Ads webcast on The Science of Excel for PPC Marketers<\/strong>. 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\u2019s 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.<\/p>\n

You can expand your slide area by clicking on the \u2018Maximize\u2019 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 \u2018Help\u2019 widget. It has a \u2018?\u2019 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.<\/p>\n

However, feel free to tweet your questions to Bing Ads and Eric Couch, if they go unanswered using the #AskBingAds<\/strong>, 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 \u2018Resource\u2019 widget in the upper right hand corner we\u2019ll refer to during the webcast. Go ahead now, and download the Excel Spreadsheet you see, so you\u2019re ready to follow along later with Eric. Also we found out that the PDF of the actual presentation was not downloadable, and that\u2019s probably because the presentation is so large.<\/p>\n

So, we\u2019ll fix that on the \u2018On Demand\u2019 portion, we\u2019ll figure that out \u2013 it might be a PDF of two parts, so be on the lookout for that. Also there\u2019s a twitter widget that\u2019ll enable you to tweet right from the webcast with the #BingAds and the #bingadswebinar# – that\u2019s kind of redundant, but that\u2019s what it is, which appears to your upper right under \u2018Resources\u2019. We\u2019ll also provide \u201cAll put your hand out\u201d to the speakers in the next slide. Let me introduce\u2026 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\u2019s our Client Development and Training Manager<\/strong>, and as you can see, he\u2019s also an Excel Superhero, in that picture.<\/p>\n

Eric:<\/strong> More of an Excel dork, but I appreciate the enthusiasm, or I guess the\u2026 yeah, the super-hero designation. MJ is actually the person who took that photo of me.<\/p>\n

MJ:<\/strong> That\u2019s right.<\/p>\n

Eric:<\/strong> It is, yeah. I felt compelled to kind of like rip that open, and just let, you know, let my Excel flag fly!<\/p>\n

MJ:<\/strong> You\u2019ve gotten a lot of mileage out of that here.<\/p>\n

Eric:<\/strong> I really have. A lot of arresting comments, but you know, all good things. And I\u2019m not ashamed.<\/p>\n

MJ:<\/strong> Well, you truly love Excel.<\/p>\n

Eric:<\/strong> It\u2019s true, it\u2019s true.<\/p>\n

MJ:<\/strong> It suits you really well. Also, just to mention the \u2018On Demand\u2019 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\u2019ve registered at, which is aka.ms\/excelforppc. If your colleagues or friends couldn\u2019t make it this time, be sure to share that link with them, and you\u2019ll 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\u2019s get started. What\u2019s on the agenda?
\nOverview – Power of Conditional Formatting and Pivot Tables<\/strong><\/span>
\nEric:<\/strong> Oh well, I was going to ask you what\u2019s on the agenda, but I guess I\u2019m the one who put all this together, so I should probably know that, right? So yeah, what we\u2019re going to talk about today\u2026 We have a lot of ground to cover, so here\u2019s what we\u2019re going to cover. The power of conditional formatting and Pivot Tables<\/strong>, we\u2019re going to talk about some ways you can use Excel to turbo charge<\/strong>, some insights you get from the BingAds interface. We\u2019re also going to talk about how to combine Excel formulas like a mad PPC scientist, and refer some new formulas at the audience<\/strong> that you may not have heard before, especially because we only just released them. I\u2019m 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<\/strong>. So, that sounds good?<\/p>\n

MJ:<\/strong> That sounds awesome, Eric.<\/p>\n

Eric:<\/strong> Alright.<\/p>\n

MJ:<\/strong> I do want to touch on that, you know, we\u2019ve been doing a lot of client trainings, and a lot of events, and from those events, we\u2019ve heard over and over again \u2013this is where this webcast and this content actually came from, is that our customers really wanted to know, \u201cHow 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?\u201d And so being at Microsoft and having Excel, it\u2018s like such an amazing magical win-win.<\/p>\n

Eric:<\/strong> 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.<\/p>\n

MJ:<\/strong> That\u2019s right.<\/p>\n

Eric:<\/strong> So, let\u2019s get started, right? So yeah, the two MJ mentioned, so the ones we\u2019re going to be talking about today, but three in particular: Excel, so hopefully everybody here has that. I know I do. It\u2019s going to be kind of critical, so I\u2019ll walk through this, but we\u2019re also going to talk about Auction Insights, the Campaign Planner. And the one you\u2019ll see here, it\u2019s grayed out, it\u2019s Bing Ads Intelligence \u2013 it\u2019s another plug-in for Excel. We\u2019re not going to talk about it today, but\u2026<\/p>\n

MJ:<\/strong> We will be doing another webcast coming soon that\u2019ll be dedicated to Bing Ads Intelligence.<\/p>\n

Eric:<\/strong> Absolutely. It\u2019s 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\u2019s the \u2018I Simply Excel Demo Workbook.\u2019 There is pretty much everything we\u2019re going to talk about in there with some pretty detailed instructions. So f you miss what we\u2019re talking about, or if you want to, like, revisit it any time, just download that and it\u2019ll be there at your fingertips, ready to go.<\/p>\n

MJ<\/strong>: Awesome.<\/p>\n

Eric<\/strong>: Alright. We\u2019re ready?<\/p>\n

MJ<\/strong>: We\u2019re ready.<\/p>\n

Eric<\/strong>: We\u2019ve a lot of ground to cover, so we\u2019re going to fly. So, our first thing to talk about is, \u201cLet\u2019s avoid spreadsheet blindness with the power of conditional formatting.\u201d So, what is spreadsheet blindness? Well, spreadsheet blindness is an affliction that unfortunately affects about 90% of search engine marketers. Basically, it\u2019s when you\u2019re 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\u2019ll get set on fire, like it is just, you\u2019re not enjoying looking at, you\u2019re not able to figure out what kind of insights you\u2019re 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.<\/p>\n

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\u2019s not just limited to\u2026 well, really, it\u2019s not limited at all! So you can change text color and format, you can also change cell color \u2013 so that\u2019s going to be one we\u2019ll talk about at length. You can also add some bar graph overlays to your data, and then insert some very pretty graphics. So I\u2019m going to warn you now, like that\u2019s about as fancy as my design knowhow gets, so if you\u2019re expecting anything prettier, you\u2019re probably going to be disappointed.<\/p>\n

But how do you find it? So, it\u2019s not really hiding anywhere, it\u2019s one of the default functions in Excel in the \u2018Home\u2019 ribbon. You can find it kind of on the right hand side, kind of squished between the \u2018General Setting,\u2019 and also, like the \u2018Format of Table\u2019 stuff. Now, if you\u2019re 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 \u2018Greater than,\u2019 \u2018Less than,\u2019 \u2018Between,\u2019 and \u2018Equal to,\u2019 or you can also get fancy and look at like, certain text things, like look at things with like \u2018Text that contains,\u2019 certain characters or certain things that you may be want to highlight, like may be an ad copy.<\/p>\n

You can also look at things with pre-defined rules. So if you don\u2019t want to do things by, you know, \u2018Custom\u2019 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 \u2013 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\u2019t 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.<\/p>\n

So if you want to\u2026, 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\u2019re 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 \u2018Icon Sets\u2019 in line with your data. You know, if you\u2019re not a big fan of, like, just some of the color things, like instead you\u2019re more visual, you want to do, like, some arrows or you know, some other color-coded icons, you can do that instead.<\/p>\n

And even below that, you\u2019re not just limited to the ones we\u2019ve highlighted here; you\u2019re 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\u2019re 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 \u2013 you just highlight \u2018New Rule.\u2019 So, how can we actually use this? So what\u2019s an easy way that you can get started using this function in kind of a scientific way?<\/p>\n

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\u2019ll 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 \u2018Hour of Day,\u2019 and then you download that report to Excel. And if you want to, you can add a \u2018Campaign,\u2019 like make it a \u2018Campaign Report\u2019, not just an \u2018Account Report\u2019, 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?<\/p>\n

MJ<\/strong>: Well, you\u2019re supposed to do conditional formatting.<\/p>\n

Eric<\/strong>: Yeah, exactly. Because you really can\u2019t tell. Like, unless you want to stare at these numbers and try and, like, figure out what it is you\u2019re supposed to do, like you\u2026
\nMJ<\/strong>: I have no idea where to look here.<\/p>\n

Eric<\/strong>: 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\u2026 like, I\u2019ll 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\u2019t actually tell us anything. Like it\u2019s\u2026all it\u2019s really telling me here is that \u201cNo kidding!\u201d or \u201cNo doubt, impressions are the highest value on the spreadsheet.\u201d That\u2019s not actually helping us at all.<\/p>\n

MJ: No.<\/p>\n

Eric: Instead, if we highlight each column individually, and apply the appropriate color scale\u2026 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\u2019s actually, we get a more accurate picture of account performance, by time of day, visualized in heat map form.<\/p>\n

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 \u2013 so like, the red versus the green, like what are some of the opportunities I have here? Like, so maybe you like bid more?<\/p>\n

MJ<\/strong>: So, the green where it says line number seven, eight\u2026<\/p>\n

Eric<\/strong>: Yeah, so we\u2019re doing really well there.<\/p>\n

MJ<\/strong>: Eeven\u2026<\/p>\n

Eric<\/strong>: Exactly.<\/p>\n

MJ<\/strong>: \u2026where it\u2019s not as green, you can probably gain more there.<\/p>\n

Eric<\/strong>: Yeah, exactly. And so when you look at something like average position, like, where I\u2019m doing the worst in the day, so I have that one, like, midnight hour \u2013 that\u2019s pretty bad. But I also have those hours from about, it\u2019s 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\u2019s actually pretty good, right?<\/p>\n

MJ<\/strong>: Oh yeah.<\/p>\n

Eric<\/strong>: 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\u2019s the big thing when working with heat maps, is to, like look at the contrasts, if you\u2019re looking at like this sort of dayparting thing, but that\u2019s 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 \u2018Days\u2019 in addition to \u2018Time of Day\u2019, so we\u2019re 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.<\/p>\n

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\u2019m supposed to do here. But it\u2019s not even just limited to dayparting, like, that\u2019s not the only use case for this. So this is a super slick ad copy, ad performance heat map \u2013 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\u2019ll 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?<\/p>\n

MJ: No.<\/p>\n

Eric<\/strong>: Exactly. Like, they\u2019ll just be, yeah, it\u2019ll 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\u2019re 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\u2019ve done here is, we\u2019ve looked at, like, what we\u2019re using in the ad title, and then also, like the \u2018Call to Action\u2019 or the \u2018Features\u2019 or the \u2018Benefits\u2019 in the ad description line, and pit those against one another to figure out what\u2019s the highest click-through rate. And we found that mentioning \u2018Free,\u2019 and \u2018Safe,\u2019 [don\u2019t] figure for Auto Insurance, but that\u2019s the most popular combination.<\/p>\n

MJ<\/strong>: As well as, I see, \u2018Fast\u2019 and \u2018Safe,\u2019 and\u2026<\/p>\n

Eric<\/strong>: And also , like, \u2018Quote,\u2019 and \u2018Safe.\u2019<\/p>\n

MJ<\/strong>; \u2018Safe\u2019 is pretty\u2026<\/p>\n

Eric<\/strong>: Yeah, \u2018Safe\u2019 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 \u2018Safe,\u2019 particularly in that ad title, like, that\u2019s the key. Now, MJ, there is one common theme with these two heatmaps \u2013 they had one thing in common. Do you have any idea what it might be?<\/p>\n

MJ<\/strong>: Tally.<\/p>\n

Eric<\/strong>: Tally? Okay, I guess you did read ahead on the presentation.<\/p>\n

MJ<\/strong>: [laughter]<\/p>\n

Eric<\/strong>: So the one common theme that they had is that they both came from Pivot Tables.<\/p>\n

MJ<\/strong>: Awesome.<\/p>\n

Eric<\/strong>: So yeah, like the ad performance heatmap, if you know how to make a pivot table already\u2026I mean, I\u2019m going to go into it just now if you don\u2019t, 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.<\/p>\n

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

So let\u2019s go through a hypothetical situation here. So let\u2019s say you needed to pull the following report. So we need to look at a Keyword Performance Report, a six-month\u2019s 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.<\/p>\n

And so, it\u2019s too much data, you have the same metrics that are repeated over and over again. So if you look at, like, we have the \u2018Ad Group,\u2019 and the \u2018Account Name,\u2019 and all that stuff \u2013 like, it\u2019s really hard to simplify this or, like you figure out, like, \u201cWhat this report is actually supposed to tell me?\u201d Like I\u2019m looking at a specific thing here, and it\u2019s not giving me anything, right? So the issue here is that this report on its own isn\u2019t really actionable, or I mean it\u2019s actionable but you have to take some time to really dive into it, right?<\/p>\n

Now, enter the Pivot Table. So I\u2019m probably preaching to the choir here, so let\u2019s 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\u2019s a reporting tool that you do a bunch of crazy stuff with, although the short answer is that it\u2019s awesome; like it\u2019s my favorite tool in Excel pretty much. So, let\u2019s kind of give the comparison here.<\/p>\n

So, the standard Excel table \u2013 so, it\u2019s a raw output, it\u2019s not really actionable or obvious, and l requires some adjustments to actually use this as a report. Like, you wouldn\u2019t 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\u2019re 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.<\/p>\n

So if you look at, you know, our original problem here\u2026 so, considering that question we had, so which keywords get the most clicks, broken up by match type on computers? We\u2019ll be focused on these four elements at the pivot table \u2013 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\u2019re manipulating these Pivot Tables, so there are four elements \u2013 Filters, Columns, Rows and Values \u2013 those build and define your pivot table.<\/p>\n

And so, by dragging and dropping those, like these elements into these different fields, you can create a pretty robust report, though\u2026 I want to say this \u2013 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\u2019s 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 \u2018Insert\u2019 in pivot table, you choose a location for your table and create it, and then drag and drop \u2018Field List\u2019 elements into place.<\/p>\n

And just for those of you still on the line, we\u2019re going to go into some more advanced applications of this. We just need to kind of set the foundation. Now, let\u2019s 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\u2019s looking at clicks- per- keyword, so pretty simple.<\/p>\n

Then we layer on another element to that. So, match-type, so we put match-type in Columns, and now we\u2019re 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\u2019re looking at \u2018Computers\u2019 specifically. So now we\u2019re looking at exact clicks by device, like, on PC \u2013 \u2018Phrase\u2019 clicks on PC, like, on a keyword level. So, once we do all that\u2026 So, how to actually create it? Selecting the data \u2013 well, make sure you select it \u2013 so, select all of the data because you never know what you might want to add in your pivot table. So, yeah, that\u2019s just kind of like common best practice and there are some quick keyboard shortcuts \u2013 I\u2019ll show it in a second that will actually kind of basically teach you how to do it much faster.<\/p>\n

Go to Navigate \u2018Insert\u2019 > 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\u2019t\u2019 create it in the actual sheet that contains all the data, because that\u2019s going to make things kind of messy. And then, drag and drop your fields into place. So you\u2019ve just physically moved them into the proper quadrants. So make sure \u2018Device\u2019 \u2013 you drag and drop it over in \u2018Filters,\u2019 and do the same thing for the rest of those \u2013 like in the order that we talked about.<\/p>\n

And so, what that does is, once you kind of got that all arranged is, it tells you pretty specifically, like, so you\u2019re 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\u2019ve manipulated, we can find out that it\u2019s this modified, broad, \u2018Scrubs Discount\u2019 keyword that\u2019s getting the most clicks. Now, some of you might get\u2026 that\u2019s fairly basic, right? So, fine, fine, I hear you, so I\u2019ll show you my very favorite application for Pivot Tables, which is Normalized Quality Score.<\/p>\n

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\u2019t already follow this guy in twitter, he\u2019s at @bgtheory, he\u2019s somewhat of a PPC genius, a good friend of ours Todd. He\u2019s an ad copy, like a guru, so if you want to know about, like, to not just to sell stuff, but ad copy, he\u2019s got a forum, and he taught me how to do this. So, this is Normalized Quality Score. So, why is this? What\u2019s 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.<\/p>\n

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\u2019s got an average quality score of 8.6. Now, I don\u2019t know about you, but clearly there\u2019s a problem here, right? Like, I\u2019m just highlighting it for everybody to see, it\u2019s that big \u20183,\u2019 like this is standing out, it\u2019s kind of like a sore thumb. And it\u2019s 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.<\/p>\n

Now, if you\u2019re just looking at the average quality score of this ad group, like you\u2019re not going to able to see that, like, there is this one out liar here dragging the whole thing down, like it\u2019s kind of masked by that average, right? So how do we actually account for this? Well, I\u2019ll 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?<\/p>\n

Well, it goes a little bit something like this. So if you want to create this yourselves, go to \u2018Keyword Report\u2019 from the Report Center, make sure it includes \u2018Spend,\u2019 \u2018Impressions,\u2019 and \u2018Quality Score,\u2019 and then make the date range for the \u2018Last 30 Days.\u2019 Now, one thing – I\u2019m 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\u2019s not, so you\u2019re more than welcome and free to change it, or add to it \u2013 like add some columns to it \u2013 it\u2019s okay, which is exactly what we\u2019re going to do here. So, for this particular spreadsheet, add a column to the end of it, and title it \u2018Quality Score x Impressions\u2019. 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.<\/p>\n

And then, just another quick Pro tip here \u2013 Control + Shift and the arrow keys make selecting your pivot table data way easier. Like, if you\u2019re working with a lot of rows and columns of data, don\u2019t 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 \u2018Row\u2019 field. So you\u2019ve created pivot table in a new spreadsheet, put your ad groups in the \u2018Row\u2019 field, put average of \u2018Quality Score\u2019, sum of \u2018Impressions\u2019 and sum of \u2018Spend\u2019 in the \u2018Values\u2019 field.<\/p>\n

And then it\u2019s going to look something like this. Now you use calculated fields when you\u2019re 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\u2019s not going to calculate correctly, or especially not the averages. You want to make sure you\u2019re creating a calculated field for these, so it calculates the actual, like, honest CPC. Otherwise it\u2019s 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.<\/p>\n

So, how to actually make use of calculated fields? So, when you\u2019re in your pivot table, you can find out under \u2018Fields, Items & Sets\u2019 in the Pivot Table Tools \u2018Analyze\u2019 tab. And so, for this calculated field, name it \u2018Normalized Quality Score\u2019, and for that formula, make it the \u2018Quality Score x Impressions column\/Impressions.\u2019 So don\u2019t make it actually, like, make sure the quotations are there, like that\u2019s the important part, make sure you\u2019re selecting that column and not just trying to make it, like multiply your Quality Score x Impressions here. That\u2019s not what we\u2019re trying to measure.<\/p>\n

So, the reason this works is because we\u2019re looking at this at the ad group level, not just the keyword level. So what we\u2019re going to do here is that by weighting for these Impressions, we\u2019re 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 \u2013 a weighting quality score of 4.3, which, you know, if you know your quality scores, that\u2019s bad.<\/p>\n

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.
\nNow, another Pivot Table Pro tip – you can actually\u2026., 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 \u2018Sort and Filter\u2019 on the cell right next to the top row on your pivot table to create \u2018Filter\u2019 buttons next to, like, every single or at the top of every single row in your Pivot Table.<\/p>\n

So in this case, like, you want to have it at that cell, hit \u2018Sort and Filter,\u2019 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\u2019s when you know, like you have really big issues; sort descending by \u2018Spend\u2019, because you can do that with that \u2018Sort and Filter\u2019 in place now, even though it will again start to make your pivot table get a little bit funky, like don\u2019t do this too often, but it definitely helps.<\/p>\n

And then look for a large difference between your Average Quality Score and then your Normalized Quality Score, because that\u2019s when you know, like, that\u2019s where the biggest structural issues are. So if you\u2019re not, like, at all impressed by that or satisfied\u2026 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?<\/p>\n

MJ<\/strong>: Sounds awesome, Eric.<\/p>\n

Eric<\/strong>: Alright. So that\u2019s 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 \u2018Ad Titles,\u2019 and one for \u2018Ad Descriptions,\u2019 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?<\/p>\n

MJ<\/strong>: It does.<\/p>\n

Eric<\/strong>: Kind of? Kind of?<\/p>\n

MJ<\/strong>: Hopefully.<\/p>\n

Eric<\/strong>: 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.<\/p>\n

MJ<\/strong>: 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\u2019s presentation. It was way too big, I didn\u2019t 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.<\/p>\n

Eric<\/strong>: All right. So let\u2019s jump on to\u2026. Again, we got a lot of ground to cover, so let\u2019s talk about how to turbocharge the Bing Ads Interface with Excel. Now we\u2019ve 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 \u2018Resources List\u2019, but it\u2019s 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.
\nThis 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.<\/p>\n

But the big reason that we\u2019re 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\u2019re not even advertising for yet, we give you competitive insights about, like, the domain, the types of advertisers you\u2019re going to be going up against. So if you want to look at, like, something like \u2018Running Shoes\u2019, or you know, \u2018Video Game Consoles\u2019, you see, like you\u2019ll be going up against \u201cXbox.com\u201d and \u201cMicrosoft.com\u201d and also would start to get an idea of their ad coverage, like, how often are they showing for this term, what\u2019s their position coverage, where they are at, like, on the page, average position \u2013 sideball, mainline, we give you all those insights.<\/p>\n

And then you can also \u2018Pin\u2019 and \u2018Favorite\u2019 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 \u2018Summary tab\u2019. So you\u2019re 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 \u2018Competition\u2019 tab. So, you know, as an example here, we set up this \u2018Macbook Air\u2019 term, so this is usually a video that we show where it\u2019s kind of like paint a picture for you.<\/p>\n

So, we saw a really cool use case here where within this \u2018Macbook Air\u2019 term, like we saw all these trends, so we saw \u2018Spike\u2019\u2026. Oh, it\u2019s actually going! So we are to look at it, look the trends here, and so for this \u2018Macbook Air\u2019 term, like, we\u2019re looking at basically, like, what\u2019s the traffic like over the course of a year. And so, no surprise here – it really spikes going into those winter months, so it\u2019s, 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.<\/p>\n

And then we can also take a look at, like, just device performance in aggregate too, because may be, like, we\u2019re 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\u2019m going to kind of move on, but the big thing that we\u2019re going to see here is that when we go to this \u2018Competition\u2019 tab, you\u2019ll see that we\u2019re advertising\u2026 Like, let\u2019s say for the sake of argument, don\u2019t tell anybody, M.J, that I\u2019m Apple, and I\u2019m seeing that on the \u2018Results\u2019 page, this Microsoft surface pro 3 or pro 4 ad is hung up above us on like our core term.<\/p>\n

This might be a problem, right? Especially if I\u2019m Apple\u2019s SEM manager, like, may be my boss sees this, and they start blowing up my phone, asking, like,\u201dWhat\u2019s going on?\u201d Like, \u201cAre you actually doing your job?\u201d And well, this report can help you circumvent that; it can you help you say, like, \u201cOkay. Yes, this is happening, but it\u2019s only happening, like, maybe one in five searches, because Microsoft\u2019s ad coverage is only about 20%, their \u2018top of page\u2019 rate is only about, like, 8% of the time.\u201d 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\u2019re not just limited to looking at the UI.<\/p>\n

But we also have some more insights here. So we can look at this \u2018Seahawks Jersey\u2019 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, \u201cOh, they call themselves the top man because none of them had fans until 2012.\u201d<\/p>\n

MJ<\/strong>: [Laughter]<\/p>\n

Eric<\/strong>: That\u2019s how I stepped out of line a little bit, but that was because the breaking point is, \u201cOkay, you can\u2019t prove this,\u201d and I was like, \u201cCan\u2019t I, Tony? Can\u2019t I?\u201d And so, using this tool, you could actually see that there was a pretty significant spike in \u2018Seahawks Jersey\u2019 traffic in Washington over, like, when they went to the SuperBowl. This was in January 2015. But this isn\u2019t just used for winning dumb office place arguments; you can also use this for campaign planning.<\/p>\n

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, \u201cHow does this compare to the average traffic volume in this industry?\u201d<\/p>\n

So this is for \u2018Education and Training,\u2019 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\u2019re planning out your monthly budget, like, month by month by month, and you know, the person who\u2019s making that decision had the expectation that you\u2019re 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.<\/p>\n

So you can kind of get ahead of that conversation by saying, \u201cLook, traffic volume is going to dip by about 40%, so we should probably account for that.\u201d 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, \u201cOkay, may be your budget should also increase if you want to, you know, capture all that traffic.\u201d<\/p>\n

So that\u2019s, you know, one particular use case, so this is all conditional formatting. But there\u2019s another tool that I\u2019m also a big fan of, and it\u2019s \u2018Auction Insights.\u2019 So \u2018Auction Insights\u2019 \u2013 we used to have this in Bing Ads Intelligence but it\u2019s not there anymore, but you can still find it in the user interface. We actually just released this, so we\u2019ve released a Bing Ads Academy Cheatsheet about this \u2013 it\u2019s on a YouTube channel now. I just tweeted about it so you can probably just find it in my history, I\u2019ll 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.<\/p>\n

So \u2018Auction Insights\u2019 is not a static snapshot of your account, like say, it changes whatever date range you\u2019re 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 \u2013 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\u2019s 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.<\/p>\n

It\u2019s also kind of a backdoor insight into budget strategy or keyword strategy because if you\u2019re seeing, you know, an increase in overlap rate, it means they\u2019ve either specifically added this keyword, and it\u2019s no longer just, you know, matching in a broad sense or a phrase match sense like they are targeting this specifically or they\u2019ve increased the budget in which that keyword was, you know\u2026.to the campaign in which that keyword was located.<\/p>\n

We can also have \u2018Position Above Rate,\u2019 which is how often did other advertisers show up above you, and then \u2018Top of Page Rate.\u2019 Now \u2018Position Above Rate\u2019 is also a kind of a backdoor insight into their bid strategy relative to yours, because if you\u2019re seeing an increase in \u2018Position Above Rate\u2019, you know, in some advertiser relative to you – that means they\u2019ve really increased their bids on that term too. So if you\u2019re seeing an increase in a \u2018Position Above Rate\u2019 and then also \u2018Overlap Rate\u2019, that means, like on a brand term, that means they\u2019re gunning for you specifically.<\/p>\n

So let\u2019s talk about, like a theoretically used case and actually used case, like to troubleshoot this. So let\u2019s 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.<\/p>\n

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\u2019ll 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\u2019s about to change.<\/p>\n

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\u2019s what accounts for, like the big difference in their performance here. Now because this is an Excel webinar, I\u2019m going to tie it back into the auctions like the Excel Insights here. So you\u2019re going to run a pivot table, like download these \u2018Auctions Insights Reports\u201d 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.<\/p>\n

You can actually see how your competitive landscape changes over time. So what we\u2019re looking at here is pivot table of Overlap Rate over about a six months\u2019 span and we\u2019re seeing how seasonality influences our advertisers\u2019 campaign strategy. And so this is for a bridal keyword, like, this is a \u2018Wedding Dress\u2019 keyword, and so we\u2019re seeing an advertiser there in grey; I\u2019m not sure if I can say their name out aloud, but they were\u2026 I can\u2019t 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\u2019re seeing a drastic decrease in their Overlap Rate because they\u2019re no longer spending as much. Make sense?<\/p>\n

MJ<\/strong>: Make sense.<\/p>\n

Eric<\/strong>: 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\u2019 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\u2026 Yeah, this came from a Pivot Table. And this is just datas you can get from the Bing Ads Interface. But let\u2019s really note down here, so we talked about, like, all these other interface stuff, so let\u2019s really get to the nuts and bolts of Excel, let\u2019s talk about how you can use and combine some formulas like a mad scientist. Sound good?<\/p>\n

MJ<\/strong>: Sounds great.<\/p>\n

Eric<\/strong>: All right. So we already know Excel is an incredibly versatile tool, and almost every formula can have unique PPC applications. So I\u2019m going to run through some of my favorites here and then may be throw some curveballs at you too. So we have \u2018If,\u2019 which is for making conditional \u2018if\/then\u2019 statements like, \u201cIf my conversions are greater than 1, then increase my bid by 10%, otherwise make it 15%.\u201d And that\u2019s kind of what it will look like in practice. This is the really basic bid formula. But you can also make use of nested \u201cif\/then\u201d statements for more power like, \u201cIf my conversions are greater than 1, and it\u2019s below position 3, and it\u2019s exact, then\u2026\u201d – do all that stuff I just said.<\/p>\n

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 \u2018IFS\u2019. So if you\u2019re not comfortable with multiple \u2018IF\u2019 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\u2019re not wanting to make multiple \u2018IFS\u2019, like in a row because sometimes, like there\u2019s some logic to it that isn\u2019t intuitive to some people.<\/p>\n

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 \u2018IF\u2019, with a regular \u2018IF\u2019 formula, you can make a value for \u2018If it\u2019s true,\u2019 or \u2018If it\u2019s false\u2019; you can\u2019t do that here. It\u2019s only for \u2018If it\u2019s true.\u2019 So you\u2019re not going to be able to make super complicated ones, the way you could with a really complex \u2018if\/then\u2019 statement, but it is a way to make something a little bit more advanced. And that\u2019s brand spanking new, so if you have Excel 2016, give that a shot because there are some variant format too, just like \u2018MAX IFS,\u2019 \u2018MIN IFS,\u2019 and so you can kind of play around with them, but we are not doing yet.<\/p>\n

So we also have \u2018LEN\u2019 – I\u2019m pretty sure I couldn\u2019t do this job without \u2018LEN,\u2019 or really post a twitter for that matter, because it\u2019s for counting the number of characters used in a cell which is really useful for things like ad copy, when you\u2019re working with a limited number of characters. And so the nice thing about \u2018LEN\u2019 is that it also works with formulas. And so if you\u2019re 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\u2019d definitely recommend you use this, if you haven\u2019t already.<\/p>\n

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\u2019s metrics against one another using CORRELATE to figure out how they\u2019re 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.<\/p>\n

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\u2019s where your bids are a little bit more unstable.
\nNow, we also have \u2018LINEST,\u2019 which calculates the statistics for a line by using the \u201cleast squares\u201d method. So you can use this to project future performance based on past metrics.<\/p>\n

Now I\u2019ll show you an example of this a little bit later. Then we also have \u2018CONCATENATE\u2019 which is for combining the contents of one cell with another, like we\u2019re using this for ad copy, creating some Bing-friendly ads from an adWords ad, right? There are two description lines there, we\u2019re 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\u2019s concatenating a \u2018+\u2019 symbol to the beginning of, like the cell we\u2019re targeting.<\/p>\n

Now, we have another new formula here which is probably going to blow your minds a little bit. Again, Excel 2016 or later – it\u2019s the formula \u2018TEXTJOIN,\u2019 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 \u2018TEXTJOIN\u2019, to say, \u201cIgnore 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.\u201d<\/p>\n

So you have multiple cells to join, you can use \u2018TEXTJOIN\u2019 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\u2019ve a couple more to go through, but \u2018SUBSTITUTE\u2019 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 \u2018SUBSTITUTE\u2019 now, it\u2019s 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.<\/p>\n

In this previous example, you can combine \u2018CONCATENATE\u2019 and \u2018SUBSTITUTE\u2019 to create modified broad match keywords in one formula, or you can also combine \u2018IF\u2019, \u2018CONCATENATE\u2019 and \u2019SUBSTITUTE\u2019 to say \u201cIF this is my modified broad match ad group, then do the CONCATENATE and SUBSTITUTE.\u201d So you can get really in-depth with the kind of stuff you\u2019re targeting. Now let\u2019s go through a theoretical problem here. Let\u2019s say you need to generate about 350,000 keyword-level destination URLs, and let\u2019s say if they have unique locations, ad group and product identifiers \u2013 do you think you could that manually? Or, would you really even want to, MJ?<\/p>\n

MJ<\/strong>: No, not at all.<\/p>\n

Eric<\/strong>: Not at all, not at all.<\/p>\n

MJ<\/strong>: I have too many other things that I would like to do.<\/p>\n

Eric<\/strong>: Exactly. So the solution here is something like \u2018VLOOKUP,\u2019 so \u2018VLOOKUP\u2019 is one of my favorite formulas. It stands for \u2018Vertical Lookup\u2019. If you need to cross-reference a lot of data, this is really how you do it. Or, you know, you could also use \u2018INDEX MATCH\u2019 too, but this is probably a controversial opinion to voice on an Excel webinar, but \u2018INDEX MATCH\u2019 has always kind of struck me as the hipster version of \u2018VLOOKUP\u2019 in that it\u2019s like, you know, it\u2019s like vital, \u201cOh, you know, it\u2019s just as good,\u201d or \u201cIt sounds better, just way more complicated,\u201d like not many people know about it. That\u2019s probably a harsh version of it. It\u2019s actually, like a little bit more flexible and it\u2019s not quite as taxing on your Excel spreadsheets, but for most people \u2018VLOOKUP\u2019 is going to get you what you need, but if you know how to use \u2018INDEX MATCH,\u2019 I\u2019m not trying to forsake the \u2018INDEX MATCH\u2019 \u2013 great function, but we\u2019re not going to go into it today.<\/p>\n

Now, this is the \u2018VLOOKUP\u2019 formula, so, looks a little bit intimidating, so I\u2019m just going to break it down for the newbie\u2019s in the group. So, \u2018LOOKUP VALUE\u2019 is what you\u2019re trying to look up, the \u2018TABLE ARRAY\u2019 is the array of cells you\u2019re targeting for this search, so it contains what you\u2019re trying to look up as well as any corresponding values you might want to pull in. The \u2018COLUMN INDEX NUMBER\u2019 is the specific column you want to pulled in on this search, and then, \u2018RANGE LOOKUP\u2019, you just ignore.<\/p>\n

And so, some small reminders here is \u2018CONCATENATE\u2019 or \u2018TEXTJOIN\u2019 combined cells, so if you want to combine multiple cells, then here\u2019s a hint – cells you might have populated with \u2018VLOOKUP\u2019 because remember, we had this overall problem we want to solve \u2026 Well, this is how you do it. And then, that \u2018CONCATENATE\u2019 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 \u2018VLOOKUP\u2019 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.<\/p>\n

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 \u2018SUBSTITUTE\u2019 formula to compare, to prepare our campaign and keyword columns for use of our tracking tags. And then also \u2018CONCATENATE\u2019 all that together in order to create six new destination URLs. \u201cBut 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?\u201d<\/p>\n

Well, in that case, don\u2019t! Just do it in one formula, because everything I\u2019ve 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\u2019re concatenating our \u2018VLOOKUP\u2019 with \u2018TEXT,\u2019 with \u2018SUBSTITUTE,\u2019 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.<\/p>\n

By the way, that\u2019s 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\u2019m 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 \u2018VLOOKUP,\u2019 \u2018SUBSTITUTE\u2019 and \u2018CONCATENATE.\u2019 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?<\/p>\n

MJ<\/strong>: Believe it or not, yes!<\/p>\n

Eric<\/strong>: Yes?<\/p>\n

MJ<\/strong>: Yes, I\u2019ve seen you do it.<\/p>\n

Eric<\/strong>: Yes, you\u2019ve seen me do this, so\u2026<\/p>\n

MJ<\/strong>: And it\u2019s a magical moment when you see it for the first time [Laughter].<\/p>\n

Eric<\/strong>: Exactly. So you actually can do this, just not with regular Excel. So how you do this is with a plug-in called \u2018Excel Solver\u2019. So \u2018Excel Solver\u2019 is a plug-in, it solves things, and you know, but it will blast, like through the sarcastic [inaudible 52:32] within the\u2026around here, but the real answer to this is that it solves equations that you give it according to parameters that you set. Equations like, \u201cWhat is my most efficient budget allocation to maximize conversion volume?\u201d So I found, like this is really useful especially for you agency guys out there; like, if you\u2019re 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.<\/p>\n

Like, you\u2019re making budget tweaks, like may be you\u2019re spending on some display campaigns, like because you have to make budget for the month, and then once you\u2019re no longer in that position, like you forgot about that. Like, you didn\u2019t forget to like tweak it, so you\u2019re still spending that same amount on those less efficient campaigns. Like this is a [inaudible 53:11] free zone, so I\u2019m not calling you out for it, that\u2019s 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?<\/p>\n

Well, first things first, it\u2019s not included by default. So you can find it by going to your \u2018Excel Options\u2019 menu, and then, going to \u2018Add-Ins\u2019. And so you find it right here. And so once you have it enabled, it\u2019s actually in the \u2018Data\u2019 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\u2019s @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.
\nOoh!<\/p>\n

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\u2019re not just, like asking, \u201cHow much can I spend?\u201d but \u201cHow much can I spend given the fact that I still need to worry about conversion rate, cost per conversion?\u201d 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 \u2018Last 30 Days,\u2019 and make sure it includes the following columns: Average CPC, Cost, Conversions and Conversion Rates. And then also make sure it includes \u2018Impression Share lost to Budget\u2019. And then that\u2019s just kind of what that report looks like.<\/p>\n

You can also include the same metrics from other accounts for this cross-account optimization. Just make sure you include you \u201cImpression share lost to budget\u201d 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 \u201cLost IS (budget),\u201d there\u2019s 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 \u2018Display lost Impression Share to budget.\u2019 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.<\/p>\n

So what I want to do here is find our \u2018Average Daily Spend\u2019 for each campaign. And so again, remember the date range you looked at here, so we looked at \u2018Last 30 Days,\u2019 so our formula is going to be Cost\/30 days. And then we find the \u2018Maximum Possible Daily Spend\u2019 for each campaign, and that\u2019s when we pull in the \u2018Lost Impression Share\u2019 numbers. So, that formula is you\u2019re \u2018Average Daily Spend\u2019 that you just made divided by 1 minus your \u2018Lost Impression Share\u2019. And so that\u2019s going to tell us, like not just what we spend on average, but what we could have spent on average, given our \u2018Lost Impression Share\u2019 numbers. So again, we\u2019re just kind of figuring out how much we could have theoretically spent.<\/p>\n

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, \u201cHey, these accounts are limited by budget\u201d and they always sounded like, \u201cNo doubt.\u201d 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: \u2018Solved Budget\u2019, \u2018Solved Clicks\u2019 and \u2018Solved Conversions\u2019.<\/p>\n

And so what goes into each of those? We\u2019ll tell you. So, \u2018Solved Budget\u2019 is left entirely to Solver itself, so we leave it blank. \u2018Solved Clicks\u2019 is where we take our average CPC, and what our \u2018Solved Budget\u2019 will be, and basically kind of reverse engineer what our new Click total would be. And you\u2019re probably seeing where this is going. The \u2018Solved conversions\u2019 column takes our conversion rate and multiplies it by that \u2018Solved Clicks\u2019 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.<\/p>\n

Basically what we\u2019re asking Solver to do is, say, \u201cGive me as many conversions as possible.\u201d Like, we\u2019re highlighting one cell to say, \u201cMake this as large as you can,\u201d then tell your Solver to do this by changing your budget, so you\u2019re highlighting the \u2018Solved Budget\u2019 column. And then you would say, \u201cMake sure my campaign can actually spend that,\u201d but putting in some constraints by saying, \u201cThis can\u2019t exceed my Maximum Possible Spend,\u201d or putting in some constraints on it, because otherwise it\u2019s 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.<\/p>\n

And so, what it\u2019ll do is it\u2019ll actually give you some stuff to do. So the highlighted campaigns on the screen, they\u2019re actually getting paused, because from an efficiency standpoint, like we\u2019re losing money. Based on my budget constraints, like I shouldn\u2019t be spending money here; there are better places to spend that money because I\u2019m looking for as many conversions as possible. So the highlighted campaigns are getting paused, and then it\u2019s 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.<\/p>\n

And so the one thing here is that you\u2019re 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\u2019s on the way that it gives you, so I tweaked it to say, like, \u201cI\u2019m spending 3000 a month, 9000 a month, and 18,000 a month, and even beyond that, and it\u2019s giving me a conversion volume for that.\u201d<\/p>\n

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 \u2013 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\u2019re trying to let you, like, \u201cI\u2019ll take that any day\u201d, right?<\/p>\n

MJ<\/strong>: I would.<\/p>\n

Eric<\/strong>: All right.<\/p>\n

MJ<\/strong>: I\u2019m sure everyone on the call would do.<\/p>\n

Eric<\/strong>: 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\u2019t find any other way \u2013 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 \u2018VLOOKUP,\u2019 \u2018TEXTJOIN,\u2019 and \u2018IFS\u2019 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.<\/p>\n

MJ<\/strong>: Fantastic Eric! Ooh, that was a whole lot of information.<\/p>\n

Eric<\/strong>: Yeah, it was a marathon. So this will be recorded, so you can come back to this at any time.<\/p>\n

MJ<\/strong>: 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\u2019ll be sending out an e-mail to everyone who joined with the \u2018Replay\u2019 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\u2019d 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\u2019ll see you soon.<\/p>\n

Eric<\/strong>: Thanks guys.<\/p>\n

 <\/p>\n

Related Articles:<\/strong><\/span><\/p>\n