crribs.com

I know its ugly, but it does the job.  Whats that you say?  Youd like to programmatically pull your list of Profile Numbers?  Stay tuned and we’ll get er done.  What this script does, is it pulls down yesterdays number of visitors for whatever site you entire the “profilenum” of for that variable.  Dont forget to put your email and password in there too.  I know there are some scandanavian or Dutch variables in there.  I borrowed a decent chunk of this code (and changed a whole lot) from Mikael Thunberg’s VBA flavoring found at:  http://mikaelspage.blogspot.com/2009/08/excel-functions-for-fetching-data.html

There may be objects that sit unused or variables too.  I had to de-workify the code and was in a hurry.  Also, the previous version spit back all visits because it pulled all your profile numbers.  Because that remains fairly static, I pulled that function out of the code.  I may have left some remnants of it in there, though.

‘*******************************************************************************
‘*
‘*  Good Script to pull one time-frames worth of visits from google analytics
‘*  for a website with a known profilenum
‘*
‘*
‘*******************************************************************************

Set DataList = CreateObject(“System.Collections.ArrayList”)

Call main()

Sub main()

email = “emailaddress@gmail.com”
password = “password”
profilenum = “191919191″

token = getGAauthenticationToken(email, password)

‘arr = getGAdata(token, profilenum, ”visits”, Date - 7, Date - 1) ’visits for last week
arr = getGAdata(token, profilenum, “visits”, (Date – 1), (Date – 1), “”, “”, “”, “”, “”) ‘visits for previous day

WScript.Echo arr(1,1)

For Each strItem in DataList
Wscript.Echo strItem
Next

If Err.Number <> 0 Then WScript.Echo “ERROR - Main(): ” & Err.Description
End Sub

Function getGAauthenticationToken(email, password)
If email = “” Or password = “” Then
getGAauthenticationToken = “”
Exit Function
End If

CurChr = 1

Set objhttp = CreateObject(“MSXML2.ServerXMLHTTP”)
URL = “https://www.google.com/accounts/ClientLogin”
objhttp.Open “POST”, URL, False
objhttp.setRequestHeader “Content-type”, “application/x-www-form-urlencoded”
objhttp.send (“accountType=GOOGLE&Email=” & email & “&Passwd=” & password & “&service=analytics&Source=CrribsDotCom”)

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

WScript.Echo “ERROR - getGAauthenticationToken()1: ” & Err.Description

End If

If Err.Number <> 0 Then
WScript.Echo “ERROR - getGAauthenticationToken()2: ” & Err.Description
End If
End Function

Function getGAdata(authToken, profileNumber, metrics, startDate, endDate, filters, dimensions, sort, includeHeaders, showArraySize)

If authToken = “Authentication failed” Then
ReDim TempArray(1 ,1)
TempArray(1, 1) = “Authentication failed”
getGAdata = TempArray
Exit Function
End If

If authToken = “” Then
ReDim TempArray(1, 1)
TempArray(1, 1) = “Authentication token missing”
getGAdata = TempArray
Exit Function
End If

If startDate > endDate Then
ReDim TempArray(1, 1)
TempArray(1, 1) = “Start date should be before end date”
getGAdata = TempArray
Exit Function
End If

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)

URL = “https://www.google.com/analytics/feeds/data?ids=ga:” & profileNumber & “&start-date=” & startDateString & “&end-date=” & endDateString & “&max-results=10000″

If metrics <> “” Then
If Left(metrics, 3) <> “ga:” Then metrics = “ga:” & metrics
metrics = Replace(metrics, “&”, “&ga:”)
metrics = Replace(metrics, “&ga:ga:”, “&ga:”)

tempAns = “”

CurChr = 1

metrics = Replace(metrics, “ga%00″, “ga:”)
metrics = Replace(metrics, “%26″, “%2C”)

URL = URL & “&metrics=” & metrics
End If

If filters <> “” Then
If Left(filters, 3) <> “ga:” Then filters = “ga:” & filters
filters = Replace(filters, “;”, “;ga:”)
filters = Replace(filters, “;ga:ga:”, “;ga:”)
filters = Replace(filters, “,”, “,ga:”)
filters = Replace(filters, “,ga:ga:”, “,ga:”)

tempAns = “”
CurChr = 1

Do Until CurChr – 1 = Len(filters)
Select Case Asc(Mid(filters, CurChr, 1))
Case 37, 42, 44, 46, 48 > 57, 59, 65 > 90, 97 > 122, 126
tempAns = tempAns & Mid(filters, CurChr, 1)
Case 32
tempAns = tempAns & “%” & Hex(32)
Case Else
tempAns = tempAns & “%” & _
Format(Hex(Asc(Mid(filters, _
CurChr, 1))), “00″)
End Select

CurChr = CurChr + 1
Loop

filters = tempAns
filters = Replace(filters, “ga%00″, “ga:”)
filters = Replace(filters, “%26″, “%2C”)
URL = URL & “&filters=” & filters
End If

If dimensions <> “” Then
If Left(dimensions, 3) <> “ga:” Then dimensions = “ga:” & dimensions
dimensions = Replace(dimensions, “&”, “&ga:”)
dimensions = Replace(dimensions, “&ga:ga:”, “&ga:”)
tempAns = “”
CurChr = 1

Do Until CurChr – 1 = Len(dimensions)
Select Case Asc(Mid(dimensions, CurChr, 1))
Case 37, 48 > 57, 65 > 90, 97 > 122
tempAns = tempAns & Mid(dimensions, CurChr, 1)
Case 32
tempAns = tempAns & “%” & Hex(32)
Case Else
tempAns = tempAns & “%” & _
Format(Hex(Asc(Mid(dimensions, _
CurChr, 1))), “00″)
End Select
CurChr = CurChr + 1
Loop

dimensions = tempAns
dimensions = Replace(dimensions, “ga%00″, “ga:”)
dimensions = Replace(dimensions, “%26″, “%2C”)
URL = URL & “&dimensions=” & dimensions
End If

If sort = True Then URL = URL & “&sort=-” & metrics

Set objhttp = CreateObject(“MSXML2.ServerXMLHTTP”)

objhttp.Open “GET”, URL, False
objhttp.setRequestHeader “Content-type”, “application/x-www-form-urlencoded”
objhttp.setRequestHeader “Authorization”, “GoogleLogin Auth=” & authToken
objhttp.send (“”)

gaResponse = objhttp.responseText

If InStr(1, gaResponse, “Token invalid”) > 0 Or InStr(1, gaResponse, “Authorization required”) > 0 Then
ReDim TempArray(1, 1)
TempArray(1, 1) = “Authentication failed”
getGAdata = TempArray
Exit Function
End If

Set XMLdoc = CreateObject(“MSXML2.DOMDocument”)

XMLdoc.LoadXML (objhttp.responseText)

Set juuri = XMLdoc.DocumentElement
Set lapset = juuri.ChildNodes

For Each lapsi In lapset
If lapsi.nodeName = “openSearch:totalResults” Then
varRows = CDbl(lapsi.Text)
End If

If lapsi.nodeName = “entry” Then
Set lapset2 = lapsi.ChildNodes
For Each lapsi2 In lapset2
If lapsi2.nodeName = “dxp:dimension” Or lapsi2.nodeName = “dxp:metric” Then
varColumns = varColumns + 1
End If
Next
End If
Next

If varRows > 10000 Then varRows = 10000

If varRows = 0 And varColumns = 0 Then
ReDim TempArray(1, 1)
TempArray(1, 1) = “No data found”
getGAdata = TempArray
Exit Function
End If

ReDim TempArray(varRows, varColumns)
If includeHeaders = True Then ReDim TempArray(varRows, varColumns)

If showArraySize = True Then
ReDim TempArray(1, 1)
TempArray(1, 1) = varColumns & “ columns * ” & varRows & “ rows”
If includeHeaders = True Then TempArray(1, 1) = TempArray(1, 1) & “ + header row”
getGAdata = TempArray
Exit Function
End If

varrow = 1

For Each lapsi In lapset
If lapsi.nodeName = “entry” Then
varcol = 1

Set lapset2 = lapsi.ChildNodes
For Each lapsi2 In lapset2
If lapsi2.nodeName = “dxp:dimension” Then
Set attribuutit = lapsi2.Attributes
TempArray(varrow, varcol) = Left(attribuutit.getNamedItem(“value”).Text, 255)
If varrow = 1 And includeHeaders = True Then TempArray(0, varcol) = attribuutit.getNamedItem(“name”).Text
varcol = varcol + 1
End If

If lapsi2.nodeName = “dxp:metric” Then
Set attribuutit = lapsi2.Attributes
If Not IsNumeric(attribuutit.getNamedItem(“value”).Text) Then
TempArray(varrow, varcol) = CDbl(Replace(attribuutit.getNamedItem(“value”).Text, “.”, “,”))
Else
TempArray(varrow, varcol) = CDbl(attribuutit.getNamedItem(“value”).Text)
End If

If varrow = 1 And includeHeaders = True Then TempArray(0, varcol) = attribuutit.getNamedItem(“name”).Text
varcol = varcol + 1
End If
Next
varrow = varrow + 1
End If
Next

getGAdata = TempArray

If Err.Number <> 0 Then
WScript.Echo “ERROR - getGAdata(): ” & Err.Description
End If
End Function