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

Fiscal Calendars for Google Analytics

Today, Analysis Engine has an exciting new feature: fiscal calendars for Google Analytics. Analysis Engine has several useful features that allow you to pull ad hoc and routinely refreshable data exports from Google Analytics based on rolling date ranges. For example, using our Reporting Manager add-on for Microsoft Excel, you can build a dashboard that refreshes data for “last week”, and, hence, always pulls data up to the previous week when you refresh it.

But enterprises need alignment throughout the organization, and if you want your web analytics data to align with what the rest of the organization is reporting, you may not be able to do that with Google Analytics out of the box. Most organizations operate with their own fiscal calendar, meaning that years, quarters and sometimes even weeks won’t line up with what Google Analytics counts as those date ranges.
Read more

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