Recently I came across a project requirement which interested me quite a lot; the project actually did analytics on top of Google analytics data (
though Google Analytics does indeed provide a lot of interesting analytics, via its feature rich platform). Interestingly I don’t understand where the human quest for analytics will end, but it keeps me on my toes to build some exciting tools.
So the challenge presented to me was to download the Google analytics data to excel on demand by the user. This presented me with the following technical challenges:
1. User Authentication for access to Google API
2. Google analytics API mechanism for data download
Thus the major key to the excel model is in the authentication subroutine which retrieves the authentication key for the future request authentication based on user credentials as follows:
Private Function getGAauthenticationToken(ByVal email As String, ByVal password As String)
Dim authResponse As String
Dim authTokenStart As Integer
Dim URL As String
Dim authtoken As String
If email = "" Then
getGAauthenticationToken = ""
Exit Function
End If
If password = "" Then
getGAauthenticationToken = "Input password"
Exit Function
End If
password = modCommonFunctions.uriEncode(password)
On Error GoTo errhandler
Dim objhttp As Object
Set objhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
URL = "https://www.google.com/accounts/ClientLogin"
objhttp.Open "POST", URL, False
objhttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
objhttp.setTimeouts 1000000, 1000000, 1000000, 1000000
objhttp.send ("accountType=GOOGLE&Email=" & email & "&Passwd=" & password & "&service=analytics&Source=Excel based analytics. (c) Amol Pandey 2012")
authResponse = objhttp.responseText
If InStr(1, authResponse, "BadAuthentication") = 0 Then
authTokenStart = InStr(1, authResponse, "Auth=") + 4
authtoken = Right(authResponse, Len(authResponse) - authTokenStart)
getGAauthenticationToken = authtoken
Else
getGAauthenticationToken = "Authentication failed"
End If
Exit Function
errhandler:
getGAauthenticationToken = "Authentication failed"
End Function
Rest of the request are appended by the authentication id obtained from the previous module for request authentication for the data download from API, leaving us with the XML formatted data to lay out as desired on to excel worksheet.
Private Sub getDetailedGoogleAnalyticsData(authtoken As String, profileNumber As Long, startDate As Date, endDate As Date, metrics() As String, Optional dimensions As Variant)
Dim httpUrl As String
Dim request As MSXML2.ServerXMLHTTP60
Dim requstTimeOut As Long
Dim requestReponseText As String
Dim startDateString As String, endDateString As String
Dim item As Variant, subItem As Variant, responseXml As MSXML2.DOMDocument
Dim rowCount As Integer
'clear Range
RawData.Range("F5:F6").ClearContents
RawData.Range("H2").Resize(MAX_ROWS, 4).ClearContents
rowCount = 0
startDateString = Year(startDate) & "-" & Right("0" & Month(startDate), 2) & "-" & Right("0" & Day(startDate), 2)
endDateString = Year(endDate) & "-" & Right("0" & Month(endDate), 2) & "-" & Right("0" & Day(endDate), 2)
httpUrl = "https://www.google.com/analytics/feeds/data?ids=ga:" & profileNumber & "&start-date=" & startDateString & "&end-date=" & endDateString & "&max-results=10000&metrics="
For Each item In metrics
httpUrl = httpUrl & "ga:" & item & ","
Next item
httpUrl = Left(httpUrl, Len(httpUrl) - 1)
'Aggreagted values
Set request = New MSXML2.ServerXMLHTTP60
requstTimeOut = 1000000
request.Open "GET", httpUrl, False
request.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
request.setRequestHeader "Authorization", "GoogleLogin Auth=" & authtoken
request.setRequestHeader "GData-Version", "2"
request.setTimeouts requstTimeOut, requstTimeOut, requstTimeOut, requstTimeOut
request.send ("")
Set responseXml = request.responseXml
For Each item In responseXml.ChildNodes(1).ChildNodes
If item.nodeName = "dxp:aggregates" Then
For Each subItem In item.ChildNodes
If subItem.Attributes.Length > 0 Then
RawData.Range("F5").Offset(rowCount, 0).Value = subItem.Attributes(3).Value
rowCount = rowCount + 1
End If
Next subItem
End If
Next item
If Not (IsError(dimensions)) Then
httpUrl = httpUrl + "&dimensions="
For Each item In dimensions
httpUrl = httpUrl & "ga:" & item & ","
Next item
httpUrl = Left(httpUrl, Len(httpUrl) - 1)
Set request = New MSXML2.ServerXMLHTTP60
requstTimeOut = 1000000
request.Open "GET", httpUrl, False
request.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
request.setRequestHeader "Authorization", "GoogleLogin Auth=" & authtoken
request.setRequestHeader "GData-Version", "2"
request.setTimeouts requstTimeOut, requstTimeOut, requstTimeOut, requstTimeOut
request.send ("")
Set responseXml = request.responseXml
rowCount = 0
For Each item In responseXml.ChildNodes(1).ChildNodes
If item.nodeName = "entry" Then
RawData.Range("H2").Offset(rowCount, 0).Value = item.ChildNodes(4).Attributes(1).Value 'Country
RawData.Range("H2").Offset(rowCount, 1).Value = item.ChildNodes(5).Attributes(1).Value 'region
RawData.Range("H2").Offset(rowCount, 2).Value = item.ChildNodes(6).Attributes(3).Value 'visits
RawData.Range("H2").Offset(rowCount, 3).Value = item.ChildNodes(7).Attributes(3).Value 'pageviews
rowCount = rowCount + 1
End If
Next item
End If
End Sub
The attached excel model takes in the user details, then fetches the list of web sites monitored by the user account, and then on second step downloads the country wise detailed data of page visit and views for the selected site via drop down.
Happy analytics…
Download:
Download solution
Refrences:
Link1: http://www.automateanalytics.com/2009/08/excel-functions-for-fetching-data.html
Link2: https://developers.google.com/analytics/
Link3: https://developers.google.com/analytics/devguides/reporting/core/dimsmets
