Manage your Google Analytics Filters in Excel

Spam referrals are a problem for everyone.  You use hundreds of filters across hundreds of views to give you a better view of your business. Changing each one of your hundreds of filters to remove spam could take you all day to accomplish!  With a little bit of setup, NEXT Analytics can help you manage all this filters in ONE SINGLE CLICK.

Look how easy it is going to be:

  1. Create a Template Filter
  2. Assign filters to all your views
  3. Set up NEXT Analytics to manage all your filters with one click
  4. Update your filters with one click!

Creating a Template Filter

Let’s create a Template Filter.  Use the Google Analytics Web Interface to edit it.  Once you’re done, NEXT Analytics can use this edited filter as a template to update all your other filters at once.  This feature makes it easy to manage

1) Under the Admin Menu, find your Account, and click Filters

2) Add a new Filter

3) Create a Custom Filter called NEXT Analytics Spam Template Filter, and exclude your list of spam referrals. We are excluding and  Click Save when you are done.

Create filters that NEXT Analytics will manage across all your views

This is where we apply the filters to the specific views across our account.  We will create many filters for each Account, and apply them to all the views that you want filtered.

To create your filters, ensure they start with NEXT Analytics Managed Spam Filter.  This is what we will use later on when we modify your filters in bulk.

Here is an example of a filter I created:

Filter Name: NEXT Analytics Managed Spam Filter Account3

Exclude Traffic from the ISP Domain that contain:

The fact that we are using does not matter, as we will be replacing it further down.

When you are done, you can see your Filters you created

Download all your filters into Excel

1) Download this Excel Worksheet, and open it.

Download Google Analytics Filters

2) Click Refresh Data to download all of your current filters into Excel. If you have not purchased a subscription, or authorized Google Analytics yet, click here for more details.

3) Look in the All Filters worksheet search for the template filter you just created. Keep this row in mind for the next step.

You are done in the Google Analytics Web Interface.  Let’s download all of our existing filters into Excel

Set up your Filter Management

Now that we have our template, and all of the filters that we want to edit named with a common starting string, we are ready to start saving you time.

1) Download this Excel Worksheet, and open it.

Manage Google Analytics Filters

2) On the update_filters_actions worksheet, the section in Dark Blue is where you put the row that we marked in the previous step. This is where your filters will be updated.  Paste in your template filter here like so:

Next we will modify the name of the filter that we want to edit, so we can edit all the filters we just applied to our views at once!

Set the filter name to update multiple filters at once

Let’s change the name of the filter we want to edit to “^NEXT Analytics Managed Spam Filter.*“.  You can see that we have created a regular expression to modify all the filters starting with NEXT Analytics Managed Spam Filter, and set them to an Exclude we got from our Template.  The first character ^ means Starts With.  The .* at the end means that we do not care what the filter ends with, as long as it starts with NEXT Analytics Managed Spam Filter.

Update your filters

Click Refresh Data on the Excel ribbon bar to update your filters!

You are done!  You have just updated all of the filters that you created to exclude spam referrals.  Check out the section below titled Log the changes and Mark any Errors to see how you can handle your errors, or undo the update you just did easily.

See how easy it was to manage hundreds or thousands of filters using Excel as your workbench?  There’s no need to learn anything new, since you already know Excel!

Further Reading

About your Filter Management Workbook

1) At the top of this workbook, you can see a section marked in Green. This section first gets all the filters in your account, and writes them out to the All Filters Before Changes worksheet

2) Next a section in light blue, will delete the previous log files from the last time you updated all of your filters.

3) The section in Dark Blue is where you put the row that we saved earlier. This is where your filters will be updated.  Paste in your template filter here like so:

4) The section in orange will write out the Logs to the Logs worksheet. Check out the section titled Log the changes for more information on what this sheet looks like

5) Finally, the section at the bottom in Light Green will write out any errors. Check out the section titled Mark any Errors for more information on what this sheet looks like.

Log the changes

Your Logs will show you all the changes that you made.  First it will tell you the Status (Usually Success).  Then it will show you the Filter ID that was modified, as well as the new filter expression after the filter has been changed.  In orange is the set of commands that could be used to re-do this change, and in blue is the set of commands you can use to Undo the change.  Note that Filter Expression New and Updated Filter Expression should be the same because the updated filter expression should match the command that was used to change it.

Mark any Errors

In your Errors sheet, it will show you the Filter ID that was attempted to be modified, as well as the error message.  It will also show you the command you could execute to re-try this attempt after you have solved the error message.  You can copy Columns C through F into your update_filters_actions if you need to re-run them.

Make helpful comments to remember key information

Want to make notes in your document?  Easy!  Start any row with the semicolon (;) character to write a note to yourself in the future!