How to extract Google Analytics data in R and Excel

You will need RGoogleAnalytics package to extract Google Analytics data in R. The package was developed by Michael Pearmain, and it provides functions for accessing and retrieving data from the Google Analytics API. This article is based on the package’s supporting documentation. To download the documentation use this link: RGoogleAnalytics documentation.

First, install the package RgoogleAnalytics. It requires the packages “lubridate” and “httr” to be installed as well.

install.packages("RGoogleAnalytics")
install.packages("lubridate")
install.packages("httr")

library(RGoogleAnalytics)
library(lubridate)
library(httr)

If you have problems with downloading the packages, check your R version. RGoogleAnalytics requires R version 3.0.2 or newer.

Then, you will use the Auth function to authorize the RGoogleAnalytics package to your Google Analytics Account using Oauth2.0.
The function Auth expects a Client ID and Client Secret. To get these, you will have to register an application with the Google Analytics API:
1. Go to the Google Developers Console
2. Create a New Project and enable the Google Analytics API
3. On the Credentials screen, create a new Client ID for Application Type “Installed Application”
4. Copy the Client ID and Client Secret to your R Script

1_AnalyticsAPI

Enable Google Analytics API

2_Credentials

Create a new Client ID

 

 

 

 

 

Now you can authorize the RGoogleAnalytics package to your Google Analytics Account.

client.id <- "your_client_ID_here"
client.secret <- "your_client_secret_here"
token <- Auth(client.id, client.secret)

Save the token into the file

save(token,file="./token")

Next, we will use the GetProfiles function. The function returns a dataframe with profile ID and Profile Name. The output of the function is supposed to look something like this:

GetProfiles(token)

If you get Profile Names in a weird formatting, make sure that in the View Settings of your Google Analytics Admin Panel you have a properly filled out View Name box.

Make sure the View Name box is properly filled out

View ID from this step is the same as the table.id parameter from the following step.

The next step is to initialize the Google Analytics query parameters with the Init function. This function takes all the query parameters and combines them into a single list that is to be passed as an argument to QueryBuilder later. You can read more about the function’s parameters in the RGoogleAnalytics documentation.

query.params <- Init(start.date = "2015-07-07",
                           end.date = "2015-08-07",
                           dimensions = "ga:sourceMedium,
                           metrics = "ga:sessions”,
                           sort = "-ga:sessions”,
                           max.results = 10000,
                           table.id = "ga:your_view_ID_here”)

To explore all the dimensions and metrics and their valid combinations use this link: Dimensions & Metrics Explorer.

Initialize a QueryBuilder object with the given parameters and perform validation.

init.params <- QueryBuilder(query.params)

Query the Google Analytics API

ga.data <- GetReportData(init.params, token)
7_R_result

Six first row of ga.data

 

 

 

 

 

 

If you want to export the ga.data data frame into an Excel file, use the following command:

write.table(ga.data, file = "ga_data", row.names = FALSE)

The good news is that you don’t have to generate the Access Token every time you want to run a query, you just need to validate and refresh the token using the ValidateToken function. This function checks whether the Access Token is expired. If yes, it generates a new Access Token and updates the Token object.

Load("./token”)
ValidateToken(token)

This is it. I hope the article was useful for you.

Leave a Reply

Your email address will not be published. Required fields are marked *