Internet Marketing Services

Often it is difficult to tell whether or not there are fundamental issues within your PPC account by just looking at metrics such as Conversions or Cost per Conversions. If you are in a managerial position and not working hands on with the accounts every day, it gets even more difficult.

One way to quickly gauge the health of your account is by looking at the Quality Score of the keywords you are targeting. Low quality scores can sometimes point out certain issues within your account such as a bad landing page, bad ad copies, bad keyword choices, etc. A quick and easy way to overview the keyword quality score within your account is to create a pivot chart in Excel, like this:

Quality Score Distribution Chart

This Quality Score distribution chart can provide a quick overview of the health of your account. Ideally, you want to have all of your keywords in the 7+ range. If you see a lot of 1, 2 and 3s in relation to the rest of the keywords, that usually means there is room for improvement in your account. Realistically, a Quality Score of 6 and above is decent. A Quality Score of 4 can be considered  a warning sign. Scores of  3 and below need to be taken seriously.

Here are the steps to create a Quality Score distribution chart:

1. Download a keyword performance report (in Excel) for the whole account. Make sure the Quality Score column is included in the report. (It can be any date range, since AdWords only shows the most recent quality score.)
2. Delete all keywords from the report that don’t have an Active status.
3. Delete all columns from the report, keeping only the Keyword and Quality Score columns.
4. Select the entire  A and B columns, then go to Insert > Pivot Table > Pivot Chart, then click OK.
5. When the pivot chart is inserted In the new sheet, this is what you will see:
   

   Chart Before Formatting

6. Check both Keyword and Quality Score boxes in the PivotTable Field List on the top right corner.
7. Drag Quality Score and Keywordtabs into the desired fields:
   

   This is the initial state

   

   This is what you want

8. Now you have a Quality Score Distribution Chart that you can use to analyze the health of your account.

To make things easier, you can download a sample Excel template here (2.1Mb). Just paste in your data into the Data Input sheet following the column names, and a chart will automatically be populated in the Chart sheet. Note that the file contains a macro to automatically update the chart, and you have to enable macros to make it work properly. Enjoy.

Pitstop Media offers ROI-based internet marketing services. If you need help with PPC management or PPC optimization please contact us for a free, no obligation quote. We’ve helped companies reduce their paid advertising cost by as much as 48% and increase their AdWords conversion rates by as much as 410%. See our internet marketing case studies.