Advanced Pay Per Click (PPC) Certification Program

Certification Training
2380 Learners
View Course Now!
42 Chapters +

Pivot Tables and Quality Score Tutorial

4.2 Introduction

Hello, I'm Brad Geddes. The author of advanced Google AdWords, the founder of Certified Knowledge, and the PPC Faculty Chair for Market Motive. In this module, we're going to look at a live walk through of Excel and ways to analyze. Your ad groups and your key words to find good places where you can optimize for quality score.

4.3 Creating Pivot Tables

Now, the way you're going to start, is you're going to go into your account. And you're going to run a keyword report. And the items you need in your report. The keyword column will be automatic because you're running a keyword report. You want to include the ad group that's a column you definitely need. You can also include campaign which is used if you have several campaigns. You will want impression based data. You want cost data and then finally you want quality score. Now, it's okay if we have additional items in our report. We're just not going to use them. We're going to focus on just manipulating these few numbers to determine where our best places for improvement are. So, looking at thousands of rows of data is not useful. Instead what we want to find is where we have ad groups. We have places of opportunity. So, we want to create a pivot table. A pivot table, it's simple to create, and I'll walk through it in a moment here. But, essentially, a pivot table gives you a higher level look at aggregate data. It's a good tool to use for when you have too much data to simply analyze. So, in Excel, go to the insert tab, click on pivot table. Now, you can instead just select your entire sheet, which I've done here, click in the corner selects your entire sheet, hit pivot table. Now, Excel's just going to ask us to confirm our data. Since we chose our entire sheet, we'll just hit okay. So, now we have a PivotTable. It starts blank. We need to choose what we want to view. So, instead of looking at every keyword, we want to look at our Ad group, we want to look at our cost, and we want to look at our Quality score. Now, with you can drag and drop stuff around, so let's make it easier to see. Now count of cost will be how many times throw exists on table. We don't want to see count to cost, and we don't want to see count of quality score, that's how many keywords there are. Instead we want to see average of quality score. And let's format this and make it a little easier to read. So, now what we want to find is places we have high spends and low quality scores. So, in a case where we've spent $44,000 in this ad group in the past month We have a fantastic quality score. So, there's probably not a lot of organization to have there. Where if we start looking through our quality score columns to see places where we have lower quality scores. See here's a 5.5 with $98,000 in spend. There's a nice place of opportunity. So, essentially you just want to find your ad groups where you have high cost and low quality scores. Then go back into that ad group inside your account, hover over the conversation icons and see what the problem is. If there's a problem listed then work on that problem, if there's not one, then your steps really are could you have a better ad copy for the keyword. If yes, move the low keywords to a new add group, write a better ad copy. If no, then you're really doing ad copy testing for CTR purposes. So, this is a simple way of using a pivot table. But, there's one downfall to looking at the data in this way. This, essentially, assumes that your quality scores are similar by cost and impression information. I cross your ad group. For instance, in this particular case, we spent $44,000, we have a 7.68 quality score. Now, not every keyword is 7.68. Some are 2, some are 5, some are 8. The average is a 7.68. So what happens, we had twenty keywords that had ten quality scores, but they only spent ten dollars. And we had one keyword at a quality score of three that spent fourteen $44,000, our average looks really good, 7.68 because it's an average regardless of a number. However, there's a lot of opportunity in that particular ad group. So, the way to combat this is you can do another very simple formula in your data source to determine a normalized quality score on an impression basis. So, let's go back to the same spreadsheet we just had. This is all the information that we downloaded from AdWords. Let's make one more column. We're just going to call this QS times Impressions. Now, all we really want to know in this instance is what is our quality score times impressions? That's our first set here. So, we'll do equals sum and multiply our quality score times our impressions. Let's drag that down for the entire sheet. Let's go back to our pivot table sheet. And pivot tables. Let's refresh the data. And then lets use quality of score times impressions. So, in a sense what we want to do is just a simple formula of quality score times impressions divided by impressions. That will give us a more normalized quality score. So for quality score, let's first change this to sum, so we're just adding up all the numbers. On our impressions, let's do the same thing pull our impressions over and sum this. So, now we just want to divide these two columns. Now, you can't divide out of a pivot table very easily. Copy this, and and then just past in our data. So, now we can easily do a simple formula from lists to see normalized quality score. So we're going to call this Real QS, real quality score. And we're just going to do a simple formula of Sum of quality score times impressions divided by impressions. So, now we can now see the actual quality score of these items. So, let's hide this data for a second so we can see what we've just uncovered. Now, when we looked at our average of quality score. In our $44,000 cost ad group, it look like going to average over 7.68 it was actually 6.67. Still not too bad. But here's when it was hiding. This ad group has spent $20,000 it appeared to be a 7.74 average quality score. In reality, it's a 4.33. This is one of the first places we should start inside the account. The other place we want to start in the account is still the aggregate that I spent 98 thousand dollars, doesn't actually have a 5.5, it has a slight lower 5.01. So, those are our two best ad groups to start with. So, if you look at your account and in general their quality scores are really similar by keyword per ad group, then you don't need to this next level data. If they, you do see varieties though of some of your keyword quality scores are sevens, others are fives, others are threes then this is a better way of determining where you have pockets of opportunity. So, it's just quality score times impressions divided by impressions. At the ad group level. So, that's an easy way to find that information. Now, what this doesn't show us though, are places where we don't we're not spending money because our quality score's so low the ads aren't being displayed.

4.4 Managing Your Low Quality Scores

So let's go back to our sheet for a second. What we want to do is create a filter first. We want to just see low quality score information. So if you select your top data row, go to Data, click on Filter. This makes it so you have these dropdown boxes. So, a pivot table can only create data from a source. It won't filter within a source. So, another place you want to look at is places where we have low quality score counts by ad group. So, let's not select all quality scores, let's just select twos, threes and fours. Now we have filtered data, so let's just Ctrl+C, let's copy this and make a new sheet. And now we have a set of data that only includes low quality score keywords. So let's make another PivotTable. Let's just look at ad groups and quality score. And cost information. So now we want to look at sum of cost. We want to leave it as count of quality score, because that's how many keywords are in the ad group. because every time there is a quality score, which exists by keyword level, you would count it up. So when we look at our places where we have areas we're not doing well or potentially not even having any ads show because our quality scores are so low. There's one ad group that definitely stands out. It has spent zero dollars, and it has 48 keywords of quality scores four or lower, so there's probably something going on in this particular case. So we have another one, 16 keywords, zero spent. So it may not be there's zero spend because there's not impressions, maybe there's zero spend because our quality scores are so low. So we do the same exercise. We go back and look at these two ad groups and do a diagnosis of quality score and those same ad groups. So that's another way of finding areas of zero exposure.

4.5 See How You're Doing Overall

Another way of looking at this pivot table to see how we're doing overall is choose your quality score data, so this will show us all the different quality scores in our account. Next, let's choose our keywords. We just want to see the count of keywords at each quality score level, so on this account, they have 6,296 keywords in this particular report, 4,399 of them are QS7. 5,593, so the vast majority are quality scores 7 or higher, so they do have areas where they can improve quality score, but it's not a huge number. This is a simple way of also seeing what's stopping your account visibility because when we look at accounts visibility, it's based upon ad rank, ad rank is either bids or quality score. In this case, we can see that the majority of keywords have high quality scores, so to increase visibility would be increasing bids. If we saw, instead, most of these keywords were in the 2, 3, and 4 range and not at the higher range, then to increase visibility, we need to be increasing quality score first before bids. So, pivot tables, really simple to use, and they have a lot of usage. Essentially, whenever you have too much data to analyze, you're not sure of a starting place, see if a pivot table can help you out. Because it's very useful to use a pivot table with quality scores and ad groups to find areas of improvement, whether it's low quality scores and high spends, or ad groups that are comprised of mostly low quality score keywords. That gives you a starting place. Go into your ad groups, hover over the icons, diagnose the problem, and then go forward from there. Quality scores, not a hard number to work with. There's only a few steps you can do, but by just spending a couple minutes manipulating the data in Excel, you can find good starting places, so you are spending your time as wisely as possible and trying to increase quality scores.

4.5 See How You're Doing Overall

Another way of looking at this pivot table to see how we're doing overall is choose your quality score data, so this will show us all the different quality scores in our account. Next, let's choose our keywords. We just want to see the count of keywords at each quality score level, so on this account, they have 6,296 keywords in this particular report, 4,399 of them are QS7. 5,593, so the vast majority are quality scores 7 or higher, so they do have areas where they can improve quality score, but it's not a huge number. This is a simple way of also seeing what's stopping your account visibility because when we look at accounts visibility, it's based upon ad rank, ad rank is either bids or quality score. In this case, we can see that the majority of keywords have high quality scores, so to increase visibility would be increasing bids. If we saw, instead, most of these keywords were in the 2, 3, and 4 range and not at the higher range, then to increase visibility, we need to be increasing quality score first before bids. So, pivot tables, really simple to use, and they have a lot of usage. Essentially, whenever you have too much data to analyze, you're not sure of a starting place, see if a pivot table can help you out. Because it's very useful to use a pivot table with quality scores and ad groups to find areas of improvement, whether it's low quality scores and high spends, or ad groups that are comprised of mostly low quality score keywords. That gives you a starting place. Go into your ad groups, hover over the icons, diagnose the problem, and then go forward from there. Quality scores, not a hard number to work with. There's only a few steps you can do, but by just spending a couple minutes manipulating the data in Excel, you can find good starting places, so you are spending your time as wisely as possible and trying to increase quality scores.

  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

We use cookies on this site for functional and analytical purposes. By using the site, you agree to be cookied and to our Terms of Use. Find out more

Request more information

For individuals
For business
Name*
Email*
Phone Number*
Your Message (Optional)

By proceeding, you agree to our Terms of Use and Privacy Policy

We are looking into your query.
Our consultants will get in touch with you soon.

A Simplilearn representative will get back to you in one business day.

First Name*
Last Name*
Email*
Phone Number*
Company*
Job Title*

By proceeding, you agree to our Terms of Use and Privacy Policy