EXCELlent Series: Making Excel Subtotals Work For You

This week, we have been discussing a few of the ways in which Excel, that terrible and wonderful entity which tends to find its way into every search marketer’s daily life, can be harnessed to help you boost PPC performance.

On Tuesday, Jessica C expanded on her list of Excel Tips for PPC Managers. Yesterday Bethany described how to use Excel to project PPC account performance. Today we’ll discuss one of the more useful functions which can be used across multiple data types- “subtotal”, which is simple, but much more powerful than its simplicity suggests.

With slight process modifications, you can apply the subtotal feature to analyze many types of data, including:

  • Overall performance of a given landing page- from keyword or ad data, depending on the level from which you deliver destination URLs – example below (note: of course this doesn’t really account for the ad text-landing page relationship so be cautious when applying it, and if a parameter in your destination URL changes the landing page in some way, you can’t remove it, and if you are doing multivariate testing in which the URL does not change based on page content, you can’t use this method at all)
  • Performance of all keywords in any given position or range of positions
  • Full-account performance on mobile or tablets
  • Performance across all display network appearances for a particular domain – example below
  • Total spend of all search queries containing the word “cows” (note: requires filtering and find-replace before subtotaling)
  • Although these examples all involve AdWords data, the same principles can be applied to any spreadsheetable data from Analytics

Destination URL parameter removal and subtotaling:

Placement report subtotaling:

AdWords and Google Analytics by themselves offer a huge number of very useful reporting functions, but using their internal capabilities in conjunction with Excel can increase your ability to analyze relevant data. The list of possible applications is really only limited by your creativity in Excel data manipulation- if you have any fun uses, let us know so that others can benefit!