If you model your Google Analytics data in Microsoft Excel, you may be using Excellent Analytics. Over the last several years, Excellent Analytics has been a great free tool for analysts who need basic Excel integration capabilities, but as of April 20, 2015, it will stop working.
Excellent Analytics uses an old method of authenticating with Google, one that was deprecated in 2012. Per Google’s deprecation policy, this means that several old methods of authentication will stop working on April 20 of this year, and only tools using the current standard method, OAuth2, will be allowed to connect to Google.
Excellent Analytics is one of the affected tools, so if you’re using it, you’ll need to move your Excel reporting to use another tool. As a free, enterprise-grade add-in for Microsoft Excel, many users are already migrating to Reporting Manager. To make the move to Reporting Manager easier, we’ve written a migration guide for Excellent Analytics.
Google Analytics recently tweeted about Reporting Manager, and the reception has been great. If you don’t have Reporting Manager yet, you can sign up for free, here.
Why Reporting Manager?
- Reporting Manager is a native Excel add-in that’s easy to install and use.
- Metrics and dimensions are automatically updated when Google Analytics adds new ones or migrates old ones.
- Reporting Manager connects to an enterprise-grade platform for Google Analytics; we actively maintain it and we’ll always use the latest reliable method to connect to Google.
- Reporting Manager has a growing list of post-processing options that offer data formatting and aggregation above and beyond what’s available in Google Analytics by itself.
- You can avoid sampling with Reporting Manager.
- Just like Excellent Analytics, it’s free!
If you’re new to Reporting Manager, there’s one key difference between it and Excellent Analytics. Where Excellent Analytics connects to Google Analytics directly, Reporting Manager works through a cloud platform called Analysis Engine. This allows us to do some useful post-processing of data if requested, and it also allows our enterprise users to collaborate on reporting, data imports and other Google Analytics tasks.
So, the first thing you need to do once you’ve installed Reporting Manager is connect to Google Analytics within the cloud platform. A quick tutorial on how to do that is below:
Replacing EA queries with Reporting Events
Excellent Analytics stores query data as a block of text in a cell.
Reporting Manager has two stages to a query. The first is a data export, the definition of what data to pull. That lives in the cloud, and if you’re an Enterprise Edition customer, can be shared with other users.
The second is a Reporting Event. This is the connection between the data export and Excel. We store this in hidden metadata within Excel.
We’ll start by clicking on the first cell of the Excellent Analytics output. This will be the top-left cell directly below the query details cell, which shows the view (profile) and date range.
Next, click the Reporting Events icon in the Reporting Manager tab of Excel:
Click the Create Event… button in the dialog that opens. (If you haven’t signed in yet, you’ll be asked to sign in. Remember that, here, you’re signing in with your credentials for Analysis Engine, not Google.)
The Reporting Event editor will open. We haven’t created a data export for this reporting event yet, so in the data export section, we’ll click Create….
The next window is where we’ll define the details for your export. Give the export a name, and then choose the connection, web property and view that we set up already.
Next, choose your date range. If your Excellent Analytics report used a rolling date range, all of the same options are there in the date range drop-down. If not, choose “Custom” and select the specific date range using the date widgets.
Next, if you used a segment in your Excellent Analytics query, you can choose the same segment in the ‘Segment’ section.
When you’ve configured these options, you’ll see something like this:
Next, click on the Advanced tab at the top of this window. This is where you can configure any sorting, filtering or limits.
Sorts work the same way as they do in Excellent Analytics. If you were sorting your results, click the Add… button in the Sorts section, choose the metric or dimension you want to sort by, click OK and then choose Ascending or Descending sort.
Filters are also similar. Click Add Met… or Add Dim… to add a filter on a metric or dimension, respectively. When you do, you’ll see the same options available for the filters as you see in Excellent Analytics.
The Post Processors section is unique to Reporting Manager — Excellent Analytics doesn’t have anything like it, so we’ll leave it alone for now.
Finally, on this tab, you can choose a Limit. This is the same as the Max Results setting in Excellent Analytics.
When you’re done in this tab, you should see something like this:
Feel free to look at the Format tab if you want to make changes to the way the data is laid out, but since the default settings are identical to Excellent Analytics, we’ll leave that alone.
Click OK to save your new data export and return to the Reporting Event editor.
Your Reporting Event will now automatically have a name, and the data export details will be filled in. Next, we need to look at the Spawn Details section here. If you started off by selecting the cell we told you to above, the Cell and Worksheet here will already be set. Verify that it’s the top-left cell below the query details cell from Excellent Analytics.
Next, choose the Direction. Excellent Analytics doesn’t have anything like this; the idea behind spawn directions is that your data can be pulled in without replacing old data, meaning that you can build tables that grow every time you refresh them. Because Excellent Analytics doesn’t do this, you can replicate your existing report by choosing REPLACE as your direction.
Next, in the Smart Interval section, click the ‘Enable’ checkbox. This just makes sure that we store some extra metadata about your exported data, and that every time we refresh the report, we replace the right data if it varies in size. So, if your data comes back with 50 rows on one refresh, and 25 the next time, Smart Interval will make sure that the entire 50 rows are erased on the refresh.
Have a look at the Formatting section as well. The only change you’ll need to make here is to check Suppress Sample Data Warnings. This means you won’t be alerted if your report contains sampled data, but it will keep things in the same cells the way Excellent Analytics does.
You should now see something like this:
Click OK to exit your reporting event, and Save to save your changes.
The next thing to do is to delete the Excellent Analytics data that’s in your report. You don’t need it anymore, so just remove it so that Reporting Manager can put new data there.
Note that you’ll see a Comment marker in the cell where your new data will appear. This is just an indicator to you from Reporting Manager that a Reporting Event spawns there.
Refreshing with One Click
Excellent Analytics has an ‘Update Query’ button, which opens up the editor window. In order to update an Excellent Analytics report, you need to click on every single query in your report (don’t miss one!) and then click ‘Execute’ for each one.
To update a Reporting Manager report, you need to click the ‘Run Events’ button. Click it, and Reporting Manager will fill the new data.
That’s it… one step, one click, and Reporting Manager will update everything.
Excellent Analytics does something a little annoying with metrics that are percentages — for example, Bounce Rate. It formats percent values so that they are already multiplied by 100. Meaning that 86% if 86.0, not 0.86. This is useful if you do not format your data, but if you want to apply the Excel number formatting and make this a proper percentage field, you’ll end up with 8600% from Excellent Analytics. That’s not a possible bounce rate.
So, in the switch to Reporting Manager, you just need to be aware that, for example, 86% will come in as 0.86, and any extra division you make in your presentation layer to get rid of that can be axed.
Rinse and Repeat
Now that you’ve updated one query to use Reporting Manager, you’ll need to update any others in your report. Follow the steps above for each query to create a reporting event for each one. Once you’re done, you’ll have a report that will keep working after Excellent Analytics stops on April 20 — and one that’s easier to refresh.
Making It Better
That’s it for the basics on how to migrate from Excellent Analytics to Reporting Manager with an existing report. But that’s just maintaining the status quo; if you want to take advantage of Reporting Manager’s more powerful features, such as post-processing, sampling evasion and Smart Interval, have a look at the following tutorial that shows how a report can be built with Reporting Manager from scratch: