12
$\begingroup$

Using Mathematica, is it possible to pull data directly from Google Analytics? Unfortunately, GA exports messy csv files, and I was wondering if there was a direct way of receiving this data.

A quick Google search has shown that at one point Patrick Collison had devised a way, though perhaps due to copyright or other legal infringements, that knowledge has disappeared off the web.

I'm very new to using Mathematica, so any tips would be greatly appreciated.

EDIT:

As suggested, here is an example of the messy csv files:

All Web Site Data
Browser & OS
20130421-20130521


Browser,Visits,Pages / Visit,Avg. Visit Duration,% New Visits,Bounce Rate
Safari,534,4.57,00:03:31,59.36%,44.38%
Safari (in-app),426,5.21,00:02:54,97.65%,34.04%
Chrome,347,5.16,00:05:54,63.11%,28.24%
Internet Explorer,214,6.02,00:08:21,68.69%,20.56%
Android Browser,170,4.84,00:03:49,68.82%,37.65%
Firefox,156,5.44,00:05:02,67.95%,30.77%
IE with Chrome Frame,11,4.36,00:04:07,18.18%,18.18%
Maxthon,3,3.33,00:00:51,66.67%,0.00%
Opera Mini,2,1.00,00:00:00,100.00%,100.00%
BlackBerry,1,1.00,00:00:00,100.00%,100.00%
,"1,865",5.08,00:04:31,71.31%,34.37%

Day,Visits
4/21/13,41
4/22/13,44
4/23/13,16
4/24/13,92
4/25/13,293
4/26/13,142
4/27/13,25
4/28/13,40
4/29/13,40
4/30/13,67
5/1/13,129
5/2/13,114
5/3/13,32
5/4/13,11
5/5/13,27
5/6/13,136
5/7/13,68
5/8/13,59
5/9/13,123
5/10/13,49
5/11/13,20
5/12/13,40
5/13/13,35
5/14/13,28
5/15/13,36
5/16/13,42
5/17/13,32
5/18/13,31
5/19/13,10
5/20/13,22
5/21/13,21
,"1,865"
$\endgroup$
9
$\begingroup$

Since Mathematica 10 it's possible to pull data from Google Analytics using ServiceConnect and ServiceExecute. I refer you to the documentation for all the details, but here is a small sample.

Establish a connection:

ga = ServiceConnect["GoogleAnalytics"]

Get the number of visitors and the number of sessions over the last ten days:

req1 = ServiceExecute[ga, "ReportData", {
   "ProfileID" -> "108086866",
   "StartDate" -> Now - Quantity[10, "Days"],
   "EndDate" -> Now,
   "Metrics" -> {"Users", "Sessions"}
   }]

Mathematica graphics

Note the parameter that's called ProfileID. In order to retrieve this parameter you have to log into the web interface and select the website that you want to retrieve data from. Currently the end of the URL is formatted like this:

a<string of numbers>w<string of numbers>p<string of numbers>

The string of numbers that comes after p is the ProfileID.

The previous query returned the total number of visitors and the total number of users, what if we want to get a time series? We can use the parameter Dimensions to select the resolution that we want:

req2 = ServiceExecute[ga, "ReportData", {
   "ProfileID" -> "108086866",
   "StartDate" -> Now - Quantity[10, "Days"],
   "EndDate" -> Now,
   "Metrics" -> {"Users", "Sessions"},
   "Dimensions" -> "Day"
   }]

Mathematica graphics

Plotting it:

ListLinePlot[
 ToExpression@Normal@req2[All, {#Day, #Users} &]
 ]

Mathematica graphics

$\endgroup$
  • $\begingroup$ running Mathematica ver. 11.0.1 Trying to get a sense of what is available via ServiceConnect, I find that executing: ga = ServiceConnect["GoogleAnalytics"] and rather than a connection I get the error "...Import: Cannot import data as JSON format." Service for arxiv works just fine. Is there something missing here that is required but not getting done? $\endgroup$ – Stuart Poss Jan 31 '17 at 5:57
  • $\begingroup$ @StuartPoss I just tried it in Mathematica 11 and it still works. $\endgroup$ – C. E. Jan 31 '17 at 6:03
  • $\begingroup$ Perhaps then there is a system setting that differs between our machines? $\endgroup$ – Stuart Poss Jan 31 '17 at 20:59
  • $\begingroup$ @StuartPoss I know how the connection works and I can't think of anything that would cause any trouble. Consider contacting Wolfram's support about this, they may be able to help. $\endgroup$ – C. E. Jan 31 '17 at 21:29
7
$\begingroup$

I just logged into Google Analytics and had a look at the export formats they offer. They offer many different formats that can be read by Mathematica: CSV, TSV and Excel are those that stand out as very easy to work with.

Here is an example where I chart the number of visitors over a certain period of time:

data = ImportString["Day Index,Visits
    12/11/13,374
    12/12/13,359
    12/13/13,301
    12/14/13,259
    12/15/13,325
    12/16/13,365
    12/17/13,302
    12/18/13,334
    12/19/13,284
    12/20/13,256
    12/21/13,279
    12/22/13,283
    12/23/13,219
    12/24/13,158
    12/25/13,308
    12/26/13,330
    12/27/13,327
    12/28/13,293
    12/29/13,354
    12/30/13,339
    12/31/13,210
    1/1/14,409
    1/2/14,415
    1/3/14,357
    1/4/14,351
    1/5/14,414
    1/6/14,471
    1/7/14,485
    1/8/14,492
    1/9/14,420
    1/10/14,368", "CSV"][[2 ;;]];

values = Transpose[data][[2]];
labels = Riffle[Rotate[#, Pi/4] & /@ Transpose[data][[1, 1 ;; ;; 2]], ""];

BarChart[values, ChartLabels -> labels]

enter image description here

In your case you had several different stats in the same CSV file. But the procedure is still simple: load the data, inspect the list that you get, use Part to select the data you're interested in, and so on. If you're very new the list manipulation may be cumbersome for you, but at the same time it will be great practice.

$\endgroup$
2
$\begingroup$

Have not checked this but it seems to cover your question as well. http://code.google.com/p/ragfield-packages/source/browse/GoogleAnalytics.m

regards Patrick

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.