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: https://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 – https://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:

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?