Select Page

photo by Tama66

Here at OptimalBI we love data, and we get upset when it’s only piled and doesn’t produce value. Like many others who have a website, we use Google Analytics to collect its statistics. This is valuable marketing data, so we want to make use of it and help our customers with making this data work.

Jack has written two blogs about GA terminology, I would recommend starting from there to understand what information Google Analytics collects. GA console is very user-friendly as it has been designed for marketing specialists, rather than technical people. Default reports allow you seeing the website traffic data from all the possible angles, interface is quite intuitive, so anyone should be able to cope with changing the parameters to get the data they are after. However, I would agree with Jack that terminology could be frustrating. In this blog I use two more important terms, dimensions and metrics, these are building blocks of any GA report.

Dimensions are attributes of your data and Metrics are quantitative measurements.

I would recommend reading the documentation and fully understanding these terms before moving further.

GA Console has a lot of pre-built reports. Realtime reports are good for tracing the success of campaigns in real time. Audience reports allow you to drill down to understand website’s audience. Acquisition reports help you understand how people got to your website. Behavior reports show you what happened after users got into your website. Conversion reports are special reports for goals and ecommerce. Apart from the default reports, GA provides capability to create and share custom dashboards and reports; also it has very cool search string that can build a report from the search terms for you. In this blog I explore options for Google Analytics data integrations and making reports more accessible.

Google spreadsheet add-on

If you need to share the specific metrics with people without making them open the overwhelming GA console, the easiest way to start would be a GA add-on in Google spreadsheets. Add-on helps you with generating reports based on the chosen dimensions and metrics. It is possible to add multiple reports into one spreadsheet, they will be shown on different tabs. Once the report is generated, you can customise it and add charts. Data refresh could be scheduled. Like any other Google documents, spreadsheets with GA reports could be shared within the company or with anyone with the link. They will have to open the spreadsheet to see the report.

As reports are generated by add-on, data integration with another data sources is not easy to achieve.

Google Data Studio

Data Studio is a new cloud visualisation tool for the data stored in Google cloud services, like Big Query and Google Ads, and some other cloud services that collect your data, like Twitter, GitHub. Unlike the rest of the reporting tools, this one targets cross-platform marketing data. It is free to use.

You can choose visualisation type from the list of pre-set simple charts. The field picker is quite convenient. Combining data from multiple sources in one report is relatively easy to achieve. After the report is created, it is available in the repository very similar to Google docs. Reports could be shared within the company or made available to anyone with the link. Report also could be downloaded as PDF or scheduled to be emailed (PDF + link) regularly. Data is refreshed automatically every 12 hours.

Reporting Tools

If your organisation has a BI reporting tool, it can help with visualising the Google Analytics data and mixing it with other data you’ve got. It takes only few clicks to establish a connection to GA from Microsoft Power BI and build a visualisation. Qlik Sense’s connection solution is not that intuitive, but could be managed as well.

It takes only few clicks to visualise some data from Google Analytics in PowerBI

Reporting API

By default, Google Analytics stores your data for 26 months, but you can adjust this setting. However, you may want to extract your data from GA to store it in your database to integrate with other data you have or use it any other way. GA has a reporting API, well documented. All the above tools are using it to acquire the data. I can’t say it’s trivial to make it work, because I didn’t have previous Google Cloud experience, so I got stuck when it came to security settings. However, following steps described in this article made it easy enough to create a Node.JS app to extract the desired data. API returns a JSON of the specified structure, however, if your API call includes multiple dimensions and metrics, the result could have a complex structure, so it definitely makes more sense to store it in document-oriented database, like MongoDB, rather than the relational database.

Reporting API provides aggregated data by dimension and time period. For example, you can do three API calls to get the information about how many people visited a website yesterday, what browser they have used and what was the average session. However, this will not give you enough data to figure out the average session per browser: you will have to do another API call or request all dimensions and metrics at once. I am trying to say that data modelling and unfolding the aggregations for storing GA data in the database for future use could be a tricky job. The granularity which would make sense would be similar to what you can find under the “Audience” -> “User Explorer” report details. But for example, if you are running the campaign, you know what are the attributes you are interested in, so you can extract them separately or combined on day to day basis and store only this information, so you can compare it to the same metrics of other campaigns.

Kate
Data masseuse

 

Kate writes technical blogs about data warehouses, and is a Data Vault convert who works mostly with MS SQL Server.  Take a look at her blog on database testing automation, or connect with Kate on LinkedIn.

%d bloggers like this: