Ever wonder who your stiffest or most consistent competitors are when it comes to your pay-per-click advertising campaigns? Want to know who might be bidding on your branded terms? There are some great premium online marketing tools to monitor your competition, but there is also data right in your AdWords account that can deliver similar insights.

By downloading a few Auction Insight reports from the AdWords user-interface and massaging the data in Excel with pivot tables we can produce a report that answers questions about your competitor’s impression share, average position, and how those metrics fluctuate on a monthly basis.

As Google states: the Auction Insights report lets you compare your performance with other advertisers who are participating in the same auctions that you are.

So how do we actually do this? Follow along to find out.

 

Step 1: Filter your Campaigns view to show search campaigns only.

search-filter

 

Step 2: Click the Keywords tab to view the account’s search campaign keywords

Step 3: Apply a filter to only view keywords that have auction insight data available

auction filtered

 

Step 4: Choose the date range

Extending the date range analysis will provide the ability to segment insight data by period of time. For example, I like to create a report that covers the previous 12-month period.

When downloading the report from the AdWords UI, I choose to segment the data by month. This is an important step if you want to see how the competition changed over the course of the year.

month segment

 

Step 5: Select the keywords you want to research

This is an important step because I have found that the results differ depending upon whether or not you select multiple keywords. For example, when creating an Auction Insights report for “All” keywords, the report only shows two competitors, but when I run another report for a single keyword, the report lists ten competitors. This makes sense because individual keyword auctions could have different sets of competitors based upon the assumed subject matter of a users Google search.

AdWords Auction Insight reports can be generated at the account, campaign, ad group, or keyword level. I have always found that creating reports for a single keyword produces more competitor data. This also means that more time is spent running different reports and downloading multiple csv files to compile in Excel. If you are attempting to provide a client report that touches on multiple parts of the PPC account than this additional time is necessary.

Typically, I like to download reports for the top 2 to 3 spending keywords, and also one branded keyword (to see who is bidding on our company name).

 

Step 6: Create a Pivot-Table in Excel from the downloaded report

Open your csv file and then insert a pivot table in your Excel workbook. By adding the competitor’s domain name to the “Rows” quadrant, adding the months to the “Columns” and Sum of Average Position to the “Values quadrant, we get a clean overview of all the competitors and their average position for the month for that particular keyword.

 

pivot table

avg-position

 

Duplicate your pivot table and then swap out Average Position for Impression Share in the “values” quadrant to display how the respective impression share for each competitor compares.

 

imp-share

 

Take it a step further and display the pivot table data as a “percentage difference from previous” to see if any particular competitors made a big push or drop off from a month-to-month perspective. Add conditional formatting to create rich visualization to present to clients so they quickly grasp the report data.

 

conditional-formatting

 

Recent Posts

Leave a Comment