Comparing the same week (weekday for weekday) last year in Google Data Studio with Analytics 360 and BigQuery

Google Data Studio offers some powerful reporting features, but one piece we’re still waiting on is more control over comparisons — right now you can compare your data against the “previous period” or “previous year”, the latter of which gives you a same-date comparison year-over-year. However, a common requirement is being able to report on the same weekday year-over-year.

Over on the Napkyn blog, I wrote up my detailed (and consequently long) overview of how to accomplish day-of-week comparisons year-over-year with Data Studio and Google Analytics. The solution provides Google Analytics 360 customers with a way to accomplish this by using the GA integration with Google BigQuery.

Have a look, and let me know if you have any feedback in the comments!

Google Analytics default channel groupings in the BigQuery export

One of Google Analytics 360’s nicest features is the ability to export its raw dataset to Google BigQuery, giving you access to run your own queries on the data set without the constraints of the user interface. However, that data set does not include various dimensions and metrics that are derivative of the collected data. Often you need to align the data you’re acquiring from Google, so sometimes it’s necessary to recreate these data points in the queries you run in BigQuery.

Over on the Napkyn blog, I wrote up a detailed post on how to recreate Google Analytics channel groupings in BigQuery. The result? An 80-line Standard SQL query that generates all the default groupings that Google offers according to their definitions (which can, of course, be customized to get custom groupings).

That has some use in terms of tinkering with those definitions, but it’s also a valuable learning tool as it can help you understand not only how those channel groupings are defined in the first place, but also the structure of the BigQuery schema that is exported from Google Analytics.

After Google last week announced that they’re offering up a sample of the Google Analytics BigQuery data set it’s a good time for analysts to brush up their SQL skills and get to understand that data!

Prepare your data for Google Analytics 360 Suite

It’s been a few months now since Google announced its Analytics 360 Suite, and we’ve been seeing the gradual roll-out of what’s been promised over the time since. One of the goals of the suite is something we’ve been advocating for a while: better support for enterprise requirements of Google Analytics. While our Analysis Engine helps close the gaps for large enterprises using Google Analytics, we’ve been thrilled to see Google take the enterprise more seriously.

Some of the things I’ve been particularly eager to see are a couple of the products currently in beta: Audience Center 360 and Data Studio 360.  Audience Center gives some insights into how different audience segments interact with your business when it comes to Google Analytics, advertising and other data sources, and lets you use those audiences in your campaigns. Data Studio is a visualization tool that allows you to create beautiful reports and dashboards that can draw data from Google Analytics and some other data sources.

For these tools in particular and the Analytics 360 Suite in general, there are some things you can do to get your data ready to take better advantage of what they offer. Whether you’re planning to use the free version of Data Studio, or buy into these tools eventually, adding context to the data you already have in Google Analytics will let you hit the ground running when you start to explore the rest of the suite.
Read more

Migrate from Excellent Analytics to Reporting Manager

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.
Read more

Introducing Reporting Manager: Google Analytics data in Microsoft Excel

Today, we’re launching Reporting Manager, a free add-on for Microsoft Excel that allows you to build powerful refresh-able reporting using Google Analytics data.

We built Reporting Manager to leverage our Analysis Engine cloud platform from within Excel, and today we’re releasing it for free.


Since Reporting Manager pulls data through Analysis Engine, we have a number of features that take advantage of data processing in the cloud. Using Reporting Manager, you can:

  • Post-process data online before it’s output into Excel.
  • Avoid sampling by merging multiple smaller pulls together.
  • Pull data according to your company’s fiscal calendar.

Reporting Manager comes bundled with Analysis Engine Reporting Edition, which includes a few features of the cloud platform that empower reporting. With Reporting Edition, you can:

  • Create and manage your connections to Google Analytics
  • Configure and run data exports in the cloud
  • Easily upload cost data to Google Analytics
  • Automatically synchronize your Bing Ads cost data with Google Analytics

All of these features come bundled with Reporting Manager for free. You can get started right now by signing up for a free account.

Understanding refunds in Google Analytics

Google Analytics introduced a cool new feature with its Enhanced Ecommerce upgrade for ecommerce tracking a while back, but it hasn’t gotten the traction it deserves. That feature is the ability to track your refunds in Google Analytics.

This is a powerful thing because it helps Google Analytics provide data that’s more complete and more accurate. If the numbers that you’re working with as an analyst, or a marketer, are the same as the ones that finance is working with, then your insights will not only be more powerful, but the high-ranking members of your organization might actually listen to them. A CFO or merchandising executive skeptical in your claims about the value of marketing activities.
Read more

Cyber Monday and Google Analytics: 3 things you should be doing right now

Black Friday and Cyber Monday aren’t just the biggest days of the year for online retail, they’re also the biggest days of the year for people to log in to Google Analytics (we’re totally guessing). From large company CEOs to digital media interns, everyone is logging into Analytics to see how many visitors are coming and and how many sales are closing.

For most retailers, digital analytics is like Christmas music. During the holidays you get excited about it for a day, then run it in the background for a few weeks, and don’t think about it again until next year.

You would think that, with all this focus on Cyber Monday analytics, more people would be converted into year-round digital data advocates. In truth, though, most people in an organization view web analytics data as interesting rather than valuable. It’s not perceived to be particularly accurate, it doesn’t contain a lot of the data that decision-makers need to do their jobs, and budget and forecast numbers live somewhere else.

Black Friday and Cyber Monday are great times to not just get wins with measurement, but set up your entire organization to make it a year-round focus. Here are 3 things every retailer should be doing right now:
Read more