Advanced Excel For PPC: Using Regular Expressions To Add Dimensions To Data

Regular expressions are a powerful computing tool, with near-mythical status in some programming circles (here is one of my favorite online comic series’ take on the topic: http://xkcd.com/208/).

This article demonstrates some useful ways to use regular expressions with PPC, by adding new dimensions to our typical Account/Campaign/AdGroup hierarchy of data. Another use for regular expressions would be manipulating URLs – perhaps in a future article…

First, a quick note: Getting up to speed with regular expressions in general can be a daunting adventure. There are a lot of resources online for getting started - http://www.regular-expressions.info is a good one.

While this article assumes a working knowledge of Regular expressions, the examples do work without your needing to know anything about them.

Enriching Data With Campaign & AdGroup Names

It is sometimes useful in PPC to group data together in more ways than the standard hierarchy of Account, Campaign, AdGroup, etc.

You might want to create a report that more naturally represents your business’ products, or that aggregates keyword data in unconventional ways, perhaps to help with bidding. If you are familiar with data warehousing concepts, and if you will permit me the rough comparison, we are enriching our data with dimensions.

As an example, consider a bike store that has 3 different Campaigns selling Mountain Bikes, a few selling Road Bikes, some Brand Campaigns, and some others. It could be useful to alter a standard Campaign Performance report that has one row per campaign, to instead have one row for each product, brand, and other.

Similarly, you could aggregate based on Account or AdGroup names, provided you plan ahead and use a naming convention that will prove useful.

Use A Strong Naming Convention

In order to use this technique, use a strong naming convention that is readily parsed by regular expressions. The examples above use my recommended naming convention, which basically looks like this:

{Dimension}({Value}), e.g.: Product(Road Bike)

That is: a {Dimension} name that we will use to name the new dimension in our data, followed by a {Value} for each of the different values in that dimension, wrapped in parenthesis. One implied rule is that {Value} should not contain any parenthesis.

In practice, avoiding any special characters is a good idea, regardless.

Considering our bicycle vendor, let’s look at those Campaign Names:

  • Product(Road Bike)
  • Product(Road Bike) Distribution(Content)
  • Product(Mountain Bike)
  • Product(Mountain Bike) Distribution(Content)
  • Brand(ACME Bikes)
  • Product(Other)

We have defined 3 new dimensions:

  • Product
  • Brand
  • Distribution

Create additional dimensions by adding them to your Campaign naming convention. Device(Mobile) might be a good one, or perhaps geography, as in State(CA), etc.

The “Product” dimension currently has 3 members:

  • Road Bike
  • Mountain Bike
  • Other

Likewise, each of the other dimensions has its members. Add members by simply filling in the new value in a campaign. In this example, there might also be: Product(Tire), Product(Helmet), etc.

You could of course use something other than parenthesis to delimit your {Member} values, and you could use something other than a space to separate {Dimension}({Member}) pairs.

Some examples come to mind:

  • product=road bike&distribution=content
  • product:road bike|distribution:content
  • etc.

Personally, I find the Product(Road Bike) convention to be easily human-readable, and suitably robust to meet our needs for machine-parsing with regular expressions.

Now that we have rigorously-named Campaigns and AdGroups, how do we make use of that in a robust and scalable way? The simple way is to use filter functionality.

Using Filter Functionality With Our New Naming Convention

With your new naming convention in place, you can immediately start using filters in your reporting. This works online in the web UI, as well as with Excel, and probably with whatever reporting tool you are already using.

By carefully using your delimiters, you can get exactly what you want. For example, to get all Campaigns that sell products, filter for “Product(” (without the quotes).

Notice I included the delimiter – the opening parenthesis. This will help avoid inadvertently including something unexpected, like Brand(ACME Products).

Using filters is nice, but what about robust reporting, or combining multiple dimensions in creative ways? We are going to use regular expressions, of course.

Extracting Dimensions With Regular Expressions

Here is a regular expression we can use to extract a specific dimension’s member-value from our recommended naming convention:

Product\(([^\)]+)\)

Or, more generally, replace {Dimension} with the name of the dimension in the following:

{Dimension}\(([^\)]+)\)

Note: These examples use the Microsoft regular expression implementation. Other conventions, as with PERL, POSIX, etc., will of course require adjustments to get the expected results.

Now let’s try to use that in Excel. Wait, Excel doesn’t do regular expressions…

Teach Excel How To Use Regular Expressions

The easiest way – use these macros can be downloaded in a working example spreadsheet here: PPC Tools – Regular Expressions Examples.

Excel does not ship with any regular expression functionality, but it is added easily enough through macros.

Here is one working version:

Option Explicit #Const LateBind = True‘COPYRIGHT: 2011 Stone Temple Consulting (http://www.stonetemple.com) ‘AUTHOR: Crosby Grant – cgrant@stonetemple.com ‘LICENSE: Provided under Creative Commons BY-NC license. ‘ Details here: http://creativecommons.org/licenses/by-nc/3.0/ ‘ Essentially: You may share or remix, but you must attribute the original author(e.g.: include this entire block of text) and you may not use this work for commercial purposes (e.g.: sell it or its output).’WARRANTY: This code is distributed as-is, with no warranty.

‘USAGE INFORMATION: http://searchengineland.com/ppc-shop-tools-the-permutator-99135

‘CONTRIBUTIONS: Code and design contributions graciously accepted. Please contact the author directly.

Function RegExReplace(ReplaceIn, _
        ReplaceWhat As String, ReplaceWith As String)
    #If Not LateBind Then
      Dim re As RegExp
      Set re = New RegExp
    #Else
      Dim re As Object
      Set re = CreateObject("vbscript.regexp")
    #End If
    re.Pattern = ReplaceWhat
    re.Global = True
    RegExReplace = re.Replace(ReplaceIn, ReplaceWith)
End Function

Function RegExFind(FindIn, FindWhat As String, Optional Match As
Integer = 0,
Optional SubMatch As Integer = 0, _ Optional IgnoreCase As
Boolean = False)
    Dim i As Long
    Dim rslt As String

    #If Not LateBind Then
    Dim re As RegExp, allMatches As MatchCollection, aMatch As Match
    Set re = New RegExp

    #Else
      Dim re As Object, allMatches As Object, aMatch As Object
      Set re = CreateObject("vbscript.regexp")
    #End If
    re.Pattern = FindWhat
    re.IgnoreCase = IgnoreCase
    re.Global = True
    Set allMatches = re.Execute(FindIn)
    Set aMatch = allMatches(Match)
    RegExFind = aMatch.SubMatches(SubMatch)
End Function

To add these to an Excel file, here are the quick steps:

  • Be sure you have a macro-enabled file. (hint: Save As Excel Macro-Enabled Workbook)
  • Alt-F11 (this opens the VBA coding window)
  • Paste the macros into a module (the default window should work).
  • Alt-F11 (this should bring you back to regular Excel

Save

You may also want to deal with setting Excel options to permit macros to run next time you open Excel. Here are good instructions from Microsoft for how to Enable or Disable macros in Office files.

Extract Dimensions & Members Using Regular Expressions

Now we can ask Excel to help us extract those member names.

 

Here is the magic formula (Assuming Campaign Name is in column A, and Dimension Name is the column B header, in row 10)

=IFERROR(regexfind($A11,B$10&"\(([^\)]+)\)"),"")

The formula looks in the column header to get the dimension name, then looks in the Campaign Name to get the member value. If it doesn’t find anything, it returns a blank. Simply add the dimension name to each successive column, and copy/paste the formula.

Pivot On Your New Dimensions

Here is the culmination of our efforts. We can now pull together a report that aggregates based on Product (rather than just Campaign Name, for example).

What tips do you have to utilize Excel to better manage your search marketing efforts?

Opinions expressed in the article are those of the guest author and not necessarily Search Engine Land.

Related Topics: Channel: Search Marketing | Search Marketing Toolbox

Sponsored


About The Author: is Director of Advertising Services at Stone Temple Consulting. Crosby has extensive experience in search engine marketing including growing small accounts to multi-million dollar success stories at companies such as QuinStreet.com and JustAnswer.com.

Connect with the author via: Email | Twitter | Google+ | LinkedIn



SearchCap:

Get all the top search stories emailed daily!  

Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • http://twitter.com/digideth digideth

    Have you considered adding the “Save to Evernote” button to your site?

    I tell you what I would use it… thanks for the tips!

  • Fabio Schenone

    you can simply use Adwords labes to achive that, can’t you ?

  • http://www.stanleyoppenheimer.com searchengineman

    I recently found a REGEX plugin for Excel which works with Excel 2007 (Find/Replace only)
    http://www.codedawn.com/excel-add-ins.php 
    .  I think its about time Excel 2010+  should have REGEX as an option – for its sort options..Hey Microsoft!

 

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!

 


 

Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide