The aim of this post is to give you the tools to enhance your Google Analytics data with R and present it on-line using Shiny. By following the steps below, you should have your own on-line GA dashboard, with these features:
Interactive trend graphs.
Auto-updating Google Analytics data.
Zoomable day-of-week heatmaps.
Top Level Trends via Year on Year, Month on Month and Last Month vs Month Last Year data modules.
A MySQL connection for data blending your own data with GA data.
An easy upload option to update a MySQL database.
Analysis of the impact of marketing events via Google's CausalImpact.
Detection of unusual time-points using Twitter's Anomaly Detection.
A lot of these features are either unavailable in the normal GA reports, or only possible in Google Analytics Premium. Under the hood, the dashboard is exporting the data via the Google Analytics Reporting API, transforming it with various R statistical packages and then publishing it on-line via Shiny.
A live demo of the dashboard template is available on my Shinyapps.io account with dummy GA data, and all the code used is on Github here.
Here are some details on what modules are within the dashboard. A quick start guide on how to get the dashboard running with your own data is at the bottom.
Additionally, the events you upload via the MySQL upload also appear here, as well as any unusual time points detected as anomalies. You can go into greater detail on these in the Analyse section.
Heatmaps use colour intensity to show metrics between categories. The heatmap here is split into weeks and day per week, so you can quickly scan to see if a particular day of the week is popular - in the below plot, Monday/Tuesday look like they are best days for traffic.
The data window is set by what you select in the trend graph, and you can zoom for more detail using the mouse.
Top Level Trends
Quite often headlines just need a number to quickly check. These data modules give you a quick glance into how you are doing, comparing last week to the week before, last month to the month before and last month to the same month the year before. Between them, you should see how your data is trending, accounting for seasonal variation.
The code provides functions to connect to a MySQL database, which you can use to blend your data with Google Analytics, provided you have a key to link them on.
In the demo dashboard the key used is simply the date, but this can be expanded to include linking on a userID from say a CRM database to the Google Analytics CID, Transaction IDs to off-line sales data, or extra campaign information to your campaign IDs. An interface is also provided to let end users update the database by uploading a text file.
In the demo dashboard, the MySQL connection is used to upload Event data, which is then used to compare with the Google Analytics data to see if the event had a statistically significant impact on your traffic. This replicates a lot of the functionality of the GA Effect dashboard
Headline impact of the event is shown in the summary dashboard tab. If its statistically significant, the impact is shown in blue.
Twitter has released this R package to help detect unusual time points for use within their data streams, which is also handy for Google Analytics trend data.
The annotations on the main trend plot are indicated using this package, and you can go into more detail and tweak the results in the Analyse section.
Making the dashboard multi-user
In this demo I’ve taken the usual use case of an internal department just looking to report on one Google Analytics property, but if you would like end users to authenticate with their own Google Analytics property, it can be combined with my shinyga() package
, which provides functions which enable self authentication, similar to my GA Effect/Rollup/Meta apps.
In production, you can publish the dashboard behind a Shinyapps authentication login (needs a paid plan), or deploy your own Shiny Server to publish the dashboard on your company intranet.
Now you have seen the features, the below goes through the process for getting this dashboard for yourself. This guide assumes you know of R and Shiny - if you don’t then start there: http://shiny.rstudio.com/
You don’t need to have the MySQL details ready to see the app in action, it will just lack persistent storage.
Setup the files
Clone/copy-paste the scripts in the github repository to your own RStudio project.
Find your GA View ID you want to pull data from. The quickest way to find it is to login to your Google Analytics account, go to the View then look at the URL: the number after “p” is the ID.
[Optional] Get your MySQL setup with a user and IP address. See next section on how this is done using Google Cloud SQL. You will also need to white-list the IP of where your app will sit, which will be your own Shiny Server or shinyapps.io. Add your local IP for testing too. If using shinyapps.io their IPs are: 220.127.116.11; 18.104.22.168; 22.214.171.124; 126.96.36.199.
Create a file called secrets.R file in the same directory as the app with the below content filled in with your details.
1. Make sure you can install and run all the libraries needed by the app:
2. Run the below command locally first, to store the auth token in the same folder. You will be prompted to login with the Google account that has access to the GA View ID you put into step 3, and get a code to paste into the R console. This will then be uploaded with app and handle the authentication with Google Analytics when in production:
3. Test the app by hitting the “Run App” button at the top right of the server.ui script in RStudio, or by running:
Using the dashboard
The app should now be running locally in a browser window with your own GA data. It can take up to 30 seconds for all the data to load first time.
Deploy the instance on-line to Shinyapps.io with a free account there, or to your own Shiny Server instance.
Customise your instance. If for any reason you don’t want certain features, then remove the feature in the ui.R script - the data is only called when the needed plot is viewed.
Getting a MySQL setup through Google Cloud SQL
If you want a MySQL database to use with the app, I use Google Cloud SQL. Setup is simple:
Go to the Google API console and create a project if you need to.
Make sure you have billing turned on with your billing accounts menu top right.
Go to Storage > Cloud SQL in the left hand menu.
Create a New Instance.
Create a new Database called “onlinegashiny”
Under “Access Control” you need to put in the IP of yourself where you test it, as well as the IPs of the Shiny Server/shinyapps.io. If you are using shinyapps.io the IPs are: 188.8.131.52; 184.108.40.206; 220.127.116.11;18.104.22.168
Under “IP Address” create a static IP (Charged at $0.24 a day)
You now should have all the access info you need to put in the apps secrets.R for MySQL access. The port should be a default 3306
You can also limit the amount of data that is uploaded by the shiny.maxRequestSize option - default is 0.5 MB.
Hopefully the above could help inspire what can be done with your Google Analytics data. Focus has been on trying to give you the tools that allow action to be made on your data.
There is a lot more you can do via the thousands of R packages available, but hopefully this gives a framework you can build upon.
I’d love to see what you build with it, so do please feel free to get in touch. :)