GA Join Excel Add-In

   

GA Join Excel Add-In ...

Google Analytics has always had the functionality to use regular expressions to help users create more powerful advanced /wp-content/uploads/filters. The problem is that for beginners they can be difficult to learn. On the plus side learning regular expressions can save you a lot of time.

The first character we teach people is the pipe character. The pipe character "|" acts as the logical OR and you can use it in an advanced /wp-content/uploads/filter to match multiple criteria.

For example if we were looking at creating an advanced /wp-content/uploads/filter that matched the following fruits:

apple

pear

banana

You would have end up with a regular expression that looks like the following:

apple|pear|banana

This regular expression would then be input into the advanced search field in GA and in the example below we would get a list of referring keywords that match the three different types of fruit.

Google Analytics advanced /wp-content/uploads/filter

Joining texts is relatively straightforward because all you have to do is delimit each text string with a pipe character. URLs on the other hand are a lot trickier because you have to escape reserved characters such as the dot, question mark and ampersand with a backslash. For example the following URLs:

/contact.html

/about-us.html

/category?name=apple

Would be joined using this regular expression:

/contact\.html|/about-us\.html|/category\?name=apple

For a beginner new to GA and regular expressions escaping reserved characters is hard enough but what if you want to do an exact match on each URL?

In these situations you would use caret and dollar characters to denote the start and end of strings. In this scenario our original example:

/contact.html

/about-us.html

/category?name=apple

Now becomes

^/contact\.html$|^/about-us\.html$|^/category\?name=apple$

As you can see things can very quickly get quite complicated! And what do you do if you have to join ten strings?

Well to get around the issue we’ve developed a quick Excel Add-in that does the hard work so you don’t have to. The add-in itself is very simple and it contains a new custom function called GAJOIN. This new function will automatically escape reserved characters for you and concatenate strings using the pipe character. There are also options to set anchors using the caret and dollar characters.

GAJOIN accepts two function arguments. The first is a single cell or a range of cells you wish to join. The second is one of four matching options.

  1. Exact Match
  2. Head Match
  3. End Match
  4. No Match

Syntax

GAJOIN(range,match_type)

Example 1

=GAJOIN(A1:A3,1)

GA Join example 1

Example 2

=GAJOIN(A1:A3,2)

GA join example 2

Example 3

=GAJOIN(A1:A3,3)

GA join example 3

Example 4

=GAJOIN(A1:A3,4)

GA join example 4

The function also automatically escapes the dot, ampersand and query string characters. Again taking the URL slug /category?name=apple&colour=red which has been input into cell A7 if we input =GAJOIN(A7,1) into cell C7 the following escaped exact match URL will be output:

GA join example 5

Install and Download

To install the Add-in you will need to download the /wp-content/uploads/file, unzip it and save it to somewhere convenient. When you need to run it you will simply need to open the /wp-content/uploads/file.

Excel Join Excel Add-in Download

GA join download

Once you’ve opened the /wp-content/uploads/file you will be prompted with a Microsoft Excel Security Notice. At this point you will need to select the Enable Macros button.

excel security notice

You should now be taken into a new excel workbook where you will be able to use the new function.

Hopefully our little Add-in will help you save some time and help you get to grips with regex. If you’d like to learn more or need help with Google Analytics please feel free to get in touch.

   

Contact Us

Do you have a challenge for us to solve?

get in touch