How to Create Custom Reports Using the Google Analytics API

Recently, I tried to create a particular Google Analytics custom report for e-commerce. Unfortunately, the Google Analytics interface couldn't handle our request.

Because of that lack of success, I had to go back to the drawing board. There was promise in the Google Analytics API explorer that I might be able to create this particular report using the GA API. But I wasn't going to build the report if you weren't interested in the subject matter. So, I offered to journey into the Google Analytics API if you left enough comments.

The votes came in, and here we are with the second part of our Google Analytics exploration!

Here's where we started with our with custom report conundrum:

We were trying to solve a reporting problem for one of our Analytics Course students, Helgi.

Helgi needs a custom e-commerce report that includes the quantity of product, revenue, the product purchased, and e-commerce conversion rate all in one report.

Unfortunately, creating this custom report in the standard Google Analytics interface did not work. Google Analytics couldn't put all these metrics together in the custom report builder because these metrics and dimensions had different scopes.

We used the Metric and Dimension Explorer to see if this report was even possible.

The conclusion was, we might be able to use the Google Analytics API to build this report.

Should we try this again in the Google Analytics API?

At the end of our last tutorial, I asked you “Do you want to learn how to use the Google Analytics API?” And you answered with a resounding “YES!”

Because of your enthusiasm and support.

Today we are going inside of the Google Analytics API to learn how to build custom reports!

Building custom reports in the Google Analytics API

The easiest way to build reports in the API is with the Google Sheets Add-on.

Using the Google Sheets Add-on to work in the API

You can navigate to the Google Analytics Spreadsheet Add-on here –https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on.

Once you click through to install the Add-on, you'll get a blank spreadsheet.

This spreadsheet is where we ‘ll build our custom report.

In the Add-ons menu of Google Sheets, select “Create a new report.”

Building custom reports in the Google Sheets Add-on

Creating a new report will bring up a menu on the left-hand side of your screen. In this menu, you can choose the property where you want to work.

The Google Merchandise Store (GMS) contains the product and sales data we need. We'll start by trying to build our report in the GMS account.

The report menu is also where you select the metrics, dimensions, and segments you want in your report.

The metrics we want in our report include the quantity of product, revenue and conversion rate.

We also need the dimension: product.

Once you define your metrics and dimensions, you can select “Create Report.”

Configure your custom report

Creating the report will add a configuration tab to your spreadsheet. This tab shows you the parameters of your report. It does not contain your data.

Run your report

To see our data, we need to select “Run reports” from the Google Sheets Add-ons menu.

Permission levels for the Google Analytics API

Unfortunately, attempting to run this report returned an error message. We don't have high enough permission to work in the GMS account using the API.

To create this report I need to use an account where I have the right permission level. (To learn all about Google Analytics permission and access level see our Analytics Course permissions guide.)

So we'll have to go back to square one. I'll switch over to an account that I have full access in and try to create our report again. Jumping from one property to another in the API is pretty quick and painless.

Error messages and problem solving

When I run our report from an account where I have full access in, I get shut down again. This time the error message says:

“Selected dimensions and metrics cannot be queried together.”

Now, I could have edited our failed attempts to run this report out of this post and just said – “this isn't possible.” But there's a valuable lesson here that I wanted to share with you.

Sometimes you don't know what you can and can't do until you try.

AND.

Just because you can dream it, doesn't mean Google Analytics can do it

Not everything you can think up is something you can do in Google Analytics. In our previous post on custom reports, we used the dimensions and metrics explorer to see if this report was even possible. Google told us it was possible, so we moved to the next stage.

Not all metrics and dimension work together, even in the API

In this tutorial, we configured our report using the Google Analytics API. Google will let us build everything, but the report won't run properly. Of course, creating a report that won't run is frustrating, but I like to think of it as another learning opportunity.

As mentioned earlier in this post, the problems we're having relate to the scope of the data we're trying to query.

Conversion rate, represented by transactions per session, is calculated at the session level. But our other metrics and dimension – product, product quantity, and revenue – are product related. These two scopes can't be combined in a report, so these metrics and dimensions will not work in harmony.

Adjusting your report configuration in the Google Sheets Add-on

But, I believe that If we remove transactions per session, we should be able to create this report.

Instead of starting over, I can adjust this report in the configuration tab. Deleting transactions per session from the metrics cell in the configuration tab will remove it from our report. Then, I can execute the report again.

Google Analytics API custom report success

This time when I run the report… Success! Removing transactions per session allowed us to get our custom ecommerce report to work.

The result is, we now have a report created in the API, that displays products, product quantity, and product revenue.

Why use the API for custom reports?

Running reports in the API is a great way to see side by side comparisons of your data. The API isn't necessary for every custom report you want to run. But it does allow you to adjust the configurations of your reports quickly. And it's an excellent option if you're running a lot of repetitive reports.

Let's try building one more report. For this example, we'll create a traffic source report for Jeffalytics.

Creating a traffic report in the API

I'll run the metric sessions against the dimension source/medium.

Creating the report will populate another column in my configuration tab.

Once again, when I run the report, I get a new tab displaying the data.

This report accounts for all 18,586 sessions on Jefflaytics over the last 30 days. (Traffic is up! Way to go 90 Day challenge!) The data in our report is very raw. It's not organized or filtered. We need to go back to our configuration tab and clean up this report so we can read it.

Editing custom reports in the Google Sheets Add-on

We want to organize this data by the number of sessions generated from each source/medium. We can do this by using the command “ga:sessions” in the Order cell of the configuration column.

The order command sorted our data by sessions, but it sorted them in ascending order. (Doh!). So, I need to revise the syntax in the command.

Finding the right Syntax for configuration in the API

It's absolutely cool to look up the Google Analytics API syntax. A quick search in the API documentation shows us the syntax we need. To sort in descending order, I need to place “-” prefix in front of “ga:sessions.”

With our syntax fixed, our report displays in ascending order.

Filter your data

We need to fix one more thing to make this report useful. I am not interested in seeing any traffic sources that resulted in under ten sessions. So, let's go into our configuration and tell Google to cut that data from our report.

In the filters cell of our configuration tab, we can use the command “ga:session>10” to adjust our report. With that command in place, we can execute the report again, and bam! Just like that Google eliminates all the source/mediums that sent us less than ten sessions.

Building reports in the API is easier than it sounds

As you can see, once you learn your way around the Google Sheets Add-on it's pretty easy to create reports in the API.

And we just covered the basics. There's a lot more useful stuff you can do with your data in the API. If you need to build charts or graphs of your data, you create them with one click after you've run your report.

Trial and error

Now, I am going to admit I was a bit apprehensive about this second part of the tutorial. I had a feeling our custom ecommerce report wasn't going to work out. And it didn't!

When things don't work, you need to get creative. You need to find an alternate solution. Using the API and developing new reports can help you to test the limitations of Google Analytics, so you can figure out what will and won't work in the future.

Recapping how to build custom reports in the Google Analytics API

Let's do a quick recap of the steps we used to build our custom reports in the Google Analytics API

Step #1 – Navigate to the Google Sheets Add-on
Step #2 – Install the API Add-on plugin into Google Sheets
Step #3 – Create your report definition
Step #4 – Run your report

Building reports in the Google Analytics API sounds fancy and complicated. But, following these steps makes it pretty simple to get started.

What did you think of this Google Analytics tutorial?

Was this helpful? Do you enjoy watching me work through this process? Please leave a comment below. If you like these web analytics problem-solving tutorials, we'll create more of them.

And if you want to hyperpower you Google Analytics skills?