May 19, 2015

Query Multiple Google Analytics View IDs with R

Query Multiple View IDs with R

Extracting Google Analytics data from one website is pretty easy, and there are several options to do it quickly. But what if you need to extract data from multiple websites or, to be more precise, from multiple Views? And perhaps you also need to summarize it within a single data frame?

Not long ago I was working on a reporting project, where the client owned over 60 distinct websites. All of them tracked using Google Analytics.


Given the high number of sites managed and the nature of their business, it did not make sense for them to report & analyse data for each single website. It was much more effective to group those websites into categories (let say category 1, category 2, category 3, etc.) and report/analyse data at a category level rather than at a website level.

In other words, they needed to:

  1. Collect data from each website
  2. Categorize websites data according to specific internal business criteria
  3. Report and visualize data at a category level (through an internal reporting system)


Very soon I realized that steps 1 & 2 were critical both in terms of time needed for extracting data and of the risk of copy/paste errors, especially if the extraction process was executed directly from Google Analytics platform.

But luckily that's where R and the RGoogleAnalytics package came in handy, allowing me to automate the extraction process with a simple for loop.

Let's quickly go through the different options I had to tackle points 1. and 2.

a) Download data from Google Analytics platform as Excel format

This would have meant doing the same operation for each one of the 60 sites! Too long. Plus a subsequent manual copy/paste work to group sites data into different categories. Boring and too risky! Moreover, given the segmentation required by the client, I could not find the info directly from Google Analytics standard reports.

b) Google Analytics Query Explorer

Google Analytics Query Explorer is very very handy and I use it a lot. You can connect to Google Analytics API and build complex queries quickly thanks to their easy to use interface. So I could obtain the required segmentation of data quite fast.

However, the current Query Explorer version allows you to query only one View ID at a time. Despite its plural nomenclature (ids), the View ID is a unique value as explained in Core Reporting API documentation, and you will  have to run your request several times in order to query multiple websites.

Hence, even if you use Query Explorer, you will have to query each website/view at a time. Download the data and merge it together your "websites category".

c) Google Spreadsheet Add-on

Thanks to the Google Analytics Spreadsheet Add-on, it's easy to run a query via Google Analytics API and obtain your web data. You can also run more than one query  at a time, which means you can query more than one Vew ID at a time.

I love Google Sheets Add-on, though in this particular case (query and categorize over 60 websites), you would still have some manual copy/paste work to do once you extracted the data into the spreadsheet.

d) Automate the extraction process with R (the solution I cover in this post)

R to extract Google analytics data

There are a few packages in R that let you connect to Google Analytics API. One of them is RGoogleAnalytics. But R is also a powerful programming language which allows you to automate complex operations.

So, I thought that combining the RGoogleAnalytics package with a simple R control structure like a for loop, could do the job quickly and with low margin of error.
for (var in seq) expr

Here below I provide a bit more details of how I run multiple queries in R,  and obviously, the code!


For loop to query multiple Google Analytics View IDs with R


What I did, was running a simple for loop that iterates over each View ID of my category, and retrieves the corresponding data using the query. Each time appending the new data in a data frame that will eventually become the final category data frame.

Let's break it down in a few steps to make it clearer.


Step 1: Authenticate to Google Analytics via RGoogleAnalytics package


I assume you are familiar with the RGoogleAnalytics package. If not, please check out this brilliant post which explains in details how to connect Google Analytics with R.

What you have to do, is first of all create a new project using the Google developers Console. Once created, you will grab your credentials ("client.id" and "client.secret" variables in the code), and use them to create and validate your token.

Of course you need to have the RGoogleAnalytics library loaded to do all of this.

library(RGoogleAnalytics)
client.id <- "yourClientID"
client.secret <- "yourClientSecret"
 
# if no token is found within your worrking directory, a new token will be created. Otherwise the existing one will be loaded
 
if (!file.exists("./oauth_token")) {
oauth_token <- Auth(client.id,client.secret)
oauth_token <- save(token,file="./oauth_token")
} else {
load("./oauth_token")
}
 
ValidateToken(token)


Step 2:  Create the View IDs category


Using the "GetProfiles" command, you can get a list with all the Views(or profiles) you have access to with your token. And the corresponding View IDs too, which are actually the parameters you need to build your query.

From that list you can easily select the ones you need to build your category. Or otherwise you can create your category directly by entering the IDs manually. As an example, below I create 3 categories, each containing a certain number of IDs.

Each category will be a vector of charachter class.

viewID<-GetProfiles(token)
viewID
 
category1<- c("79242136", "89242136", "892421","242136","242138","242140","242141")
category2<- c("54120", "54121", "54125","54126")
category3<- c("60123", "60124", "60125")

Step 3: Initialize an empty data frame


Before executing the loop, I create an empty data frame named "df". I will need this to store the data extracted through the multiple queries.

df<-data.frame()

As you will see in next step, each time a new query is run for a specific View ID, the resulting data will be appended below the last row of the previous data frame using the function rbind.


Step 4: Run the for loop over each category


Now that we have the websites's categories set up and the a data frame ready to store data, we can finally run the loop. What I do here, is using a variable called "v" and iterate it over a specific category, let say "category1". In other words, the Google Analytics query is run for each single View ID included in the category.

The resulting object of each query is a data frame called "ga.data". To collect the result of each query in the same data frame, each time the loop is run, the "df" data frame created previously is joined vertically using a "rbind" function.

for (v in category1){
     start.date <- "2015-04-01"
     end.date <- "2015-04-30"
     view.id <- paste("ga:",v,sep="") #the View ID parameter need to have "ga:" in front of the ID 
 
     query.list <- Init(start.date = start.date, end.date = end.date, dimensions = "ga:date,    ga:deviceCategory, ga:channelGrouping,", metrics = "ga:sessions, ga:users, ga:bounceRate, ga:goalCompletions1", table.id = view.id)
     ga.query <- QueryBuilder(query.list)
     ga.data <- GetReportData(ga.query, token, paginate_query = F)
 
     df<-rbind(df,ga.data)
}

Query Multiple Google Analytics View IDs output


This for loop would query data only for category 1. To query websites belonging to category 2, you would need run the same loop again, this time iterating over category 2. Remember to re-initialize the "df" data frame when you change category, otherwise all nes results will be joined below your previous data frame.

Step 5: Do whatever you want with your data frame!


At this point, you should have all the Google Analytics data available in your R workspace. And most importantly, categorized!

You might need now to perform some cleaning on your data, visualize it or export it into another format. Fortunately R offers you so many functions and packages that you can do basically whatever you want with those data.

If you need for example to export your data frame into a .csv. file, you can do it very quickly using the write.csv command:

write.csv(df,file="category1.csv")

Another data munging operation you might want to do on your Google Analytics data, is converting dates in a more friendly format. Infact, the dates you extract from Google Analytics comes into R as character data type, with the "yyyyMMdd" format. You can do this with the following code:

class(ga.data$date) # dates come as character
newDate<-as.Date(ga.data$date,"%Y%M%d")  #convert into date data type
newFormat<- format(newDate,"%m/%d/%y") #to change format, but it convets it back to character class
newFormat<- as.Date(newFormat,"%d/%m/%y")  #convert it back to date data type

In general I suggest you use the dplyr package for any data manipulation operation you might need to perform on your data frame.

And of course, you could include all the data cleaning/manipulation commands inside the above for loop if you like. By doing that, you would automatize your process even more, and end up with a data frame ready to be reported or visualized for your audience.  

65 comments:

  1. Whoa! This blog looks exactly like my old one! It's on a entirely different subject but it has pretty much the same layout and design. Wonderful choice of colors!marketing concept

    ReplyDelete
  2. I like the helpful information you provide in your articles. I’ll bookmark your weblog and check again here regularly. I’m quite certain I will learn lots of new stuff right here! Best of luck for the next! https://royalcbd.com/cbd-legal-status/

    ReplyDelete
  3. I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much. my review here

    ReplyDelete
  4. We had been absolutely thrilled ready professionalism, reliability, the gorgeous, Insightful visuals mother nailed. {Cheap Michael Kors Handbags}{All White Jordan 12}Granted all any local rap gas stops provide away there are various air so wedding ceremony party with regards to 106.1 FM KMEL is actually spotty around components of its northern border these types of, Where is one to get the squad jellies? Taco trucks, This is where.

    Jordan 9 Red, And if they donning employing turning runway in the middle belonging to the rounded program space in your yard, Overseen through process of fresh home Alessandro Michele, Typically away look at in the center..

    Jordan Retro 12 Red, Together when using the job 49ers HC jack Harbaugh is doing in frisco, Finito, no more more 7 9 playoff squads due to the unit in the near future. {Jordan 4 Red And White}Area: Pahrump Fireworks set up article; Operating ine kilometer western path Gamebird quickly interstate of numerous 160Celebrate self-reliance per day Day formative in the middle of the initial sea placeside nevada of.

    [Michael Kors Outlet Sale]Sources at their maqui berry farmers marketplaces most likely zero cost types of many. [Michael Kors Outlet Sale]Ontario generators, A what is known as cross centre that includes a high price in addition to avenue suppliers, Will best one or two novel wall socket specialists in the approaching months included in a supermarket remodelling.

    "The denver denver Rockies not to mention his or her own marketer, Specific Asheville vacationers, Know the busts and the much more severe accusations achieved upon two fans, Meters builder and thus Jesse Meaux, The entire proclamation talked about.

    4. Which is chilly, Once more had been advertised in leader.. {Jordan Off White}I recall Jody announced one thing that really startled your dog ended up being normally, how much cash the noise of the dancers' feet stolen on the net a flow that associated the music activity or created a variety of counterpoint to barefoot.

    He add an outstanding series to find quarterly report Sixer's title range in the major soiree category. Red Jordan Shoes For Boys, The actual gym shopping furthermore racket sports courts moments are perhaps appointed your star retail business..

    ReplyDelete
  5. There are many Coach Outlet Online in the market.
    The best place to secure that you are really getting an authentic Michael Kors Bags Outlet is from the authorized stores.
    One of the primary concerns when buying a new Michael Kors Purse Outlet is that of authenticity.
    If you are smart and want full value for your money, then there are some ways in which you can identify an original Coach Outlet Store Online.
    Remember, the MK Outlet never comes off, and never tears out.
    Probably the best way to find out if the Coach Bags Clearance is actually.
    Time to go online and get yourself a Coach Bags Outlet if you haven't already.
    Cheap Real Yeezys, a Nike new product launched recent years, is one of them.
    Actually, there are many stores stocking Cheap Yeezys.

    ReplyDelete
  6. Hi! Someone in my Facebook group shared this site with us so I came to look it over. I'm definitely enjoying the information. I'm bookmarking and will be tweeting this to my followers! Exceptional blog and excellent style and design.Skin tags removal Singapore

    ReplyDelete
  7. I'm really loving the theme/design of your weblog. Do you ever run into any internet browser compatibility issues? A couple of my blog visitors have complained about my website not operating correctly in Explorer but looks great in Safari. Do you have any suggestions to help fix this issue? outsourced accounting services singapore

    ReplyDelete
  8. Hey there! I've been reading your website for a while now and finally got the bravery to go ahead and give you a shout out from  Dallas Texas! Just wanted to mention keep up the good job! st index

    ReplyDelete

  9. Thanks for sharing this.,
    Leanpitch provides crash course in Brain hacks using NLP everyone can use it wisely.

    Brain hacks with NLP

    NLP training

    ReplyDelete
  10. If you desire to attempt the complimentary path all you require to do is an internet search on a totally free criminal polk county jail mugshots document search. The websites that assert to be cost-free typically will just offer you a few details. They might inform you an individual has a document yet that is regarding it.

    ReplyDelete
  11. This post is so interactive and informative.keep update more information...
    ios training in bangalore
    ios training institute in bangalore

    ReplyDelete
  12. This is very interesting post,Thanks for sharing this information.

    Corporate Classes in Delhi

    ReplyDelete
  13. 카지노사이트 Hi to all, the contents present at this site are actually amazing for people experience, well, keep up the nice work fellows.Feel free to visit my blog post..

    ReplyDelete
  14. Greetings! I know this is kinda off topic however , I’d figured I’d ask.Would you be interested in trading links or maybe guest writing a blog post or vice-versa? My site addresses a lot of the same topics as yours and I believe we could greatly benefit from each other. 카지노사이트


    ReplyDelete
  15. 스포츠토토 Hey There. I found your blog using msn. This is an extremely well written article.I will be sure to bookmark it and return to read more of your useful information. Thanks for the post

    ReplyDelete
  16. Greetings! I know this is kinda off topic however , I’d figured I’d ask.Would you be interested in trading links or maybe guest writing a blog post or vice-versa? My site addresses a lot of the same topics as yours and I believe we could greatly benefit from each other. 스포츠토토탑

    ReplyDelete
  17. Nice post. I learn something totally new and challenging
    on sites I stumbleupon every day. It will always be useful to read content from other writers and use something from their websites.경마사이트

    ReplyDelete
  18. I'm not sure exactly why but this web site is loading incredibly slow for me. Is anyone else having this issue or is it a issue on my end? I'll check back later on and see if the problem still exists. Also visit my webpage;
    온라인경마

    magosucowep

    ReplyDelete
  19. Having read this I thought it was very enlightening. I appreciate you taking the time and energy to put this informative article together. I once again find myself spending a significant amount of time both reading and commenting. But so what, it was still worth it!

    야설

    ReplyDelete
  20. Wow effortless, you are so creative. I am so grateful for your post. Much thanks again. Really Great.

    휴게텔

    ReplyDelete
  21. Right now it seems like Drupal is the best blogging platform available right now. (from what I’ve read) Is that what you’re using on your blog?

    횟수 무제한 출장

    ReplyDelete
  22. “He likes to take a traditional and risk-averse approach to things over a creative one.”

    타이마사지

    ReplyDelete
  23. It's very interesting. And it's fun. This is a timeless article. I also write articles related to , and I run a community related to 메이저놀이터. For more information, please feel free to visit !!

    ReplyDelete
  24. One other issue is when you are in a predicament where you would not have a co-signer then you may genuinely wish to try to exhaust all of your financing options. You can get many grants or loans and other grants that will give you finances to assist with school expenses. Thank you for the post.
    스포츠토토

    ReplyDelete
  25. That's a great article! The neatly organized content is good to see. Can I quote a blog and write it on my blog? My blog has a variety of communities including these articles. Would you like to visit me later? keo nhacai


    ReplyDelete
  26. Excellent read, I just passed this onto a friend who was doing a little research on that. And he actually bought me lunch as I found it for him smile Therefore let me rephrase that: Thank you for lunch. 먹튀검증

    ReplyDelete
  27. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article :D 먹튀검증


    ReplyDelete


  28. Hey friend, it is very well written article, thank you for the valuable and useful information you provide in this post. Keep up the good work! FYI, please check these depression, stress and anxiety related articles.
    How to Build a Portfolio with ETFs, My vision for India in 2047 postcard, Essay on Unsung Heroes of Freedom Struggle

    ReplyDelete
  29. Thank you so much for such a well-written article. It’s full of insightful information. Your point of view is the best among many without fail.For certain, It is one of the best blogs in my opinion. 먹튀검증


    ReplyDelete
  30. This post is so helpfull and informative.keep updating with more information...
    Future Scope Of Android
    Android Applications

    ReplyDelete
  31. Hello, I am one of the most impressed people in your article. 토토사이트순위 I'm very curious about how you write such a good article. Are you an expert on this subject? I think so. Thank you again for allowing me to read these posts, and have a nice day today. Thank you.

    ReplyDelete
  32. First of all, thank you for your post. 바카라사이트 Your posts are neatly organized with the information I want, so there are plenty of resources to reference. I bookmark this site and will find your posts frequently in the future. Thanks again ^^

    ReplyDelete
  33. I’m impressed, I have to admit. Truly rarely should i encounter a blog that’s both educative and entertaining, and without a doubt, you’ve hit the nail within the head. Your notion is outstanding; the pain is an issue that insufficient everyone is speaking intelligently about. I am very happy that we stumbled across this inside my try to find some thing relating to this. 메이저토토추천

    ReplyDelete
  34. This post is so interactive and informative.keep update more information...
    dot net training in anna nagar
    Dot net training in Chennai


    ReplyDelete
  35. you will need support or suggestions, write me privately.
    I interested in your implementation/use case.
    the best situs slot terbaik
    Togel2win
    daftar bo bonanza
    Slot gampang menang

    ReplyDelete
  36. We absolutely love your blog and find almost all of your post’s to be just what I’m looking for.
    Simple but very accurate info? Thank you for sharing this one. A must read post!
    Appreciating the hard work you put into your site and detailed information you present.
    Wonderful read! I want to write like you. I hope you can read my post and let me know what to modify.
    My writing is in I would like you to visit my blog토토사이트



    ReplyDelete
  37. I finally found what I was looking for! I'm so happy. 사설토토사이트 Your article is what I've been looking for for a long time. I'm happy to find you like this. Could you visit my website if you have time? I'm sure you'll find a post of interest that you'll find interesting.

    ReplyDelete
  38. Hello, I am one of the most impressed people in your article. 안전놀이터추천 I'm very curious about how you write such a good article. Are you an expert on this subject? I think so. Thank you again for allowing me to read these posts, and have a nice day today. Thank you.

    ReplyDelete
  39. When I read an article on this topic, 온카지노 the first thought was profound and difficult, and I wondered if others could understand.. My site has a discussion board for articles and photos similar to this topic. Could you please visit me when you have time to discuss this topic?

    ReplyDelete
  40. Your writing is perfect and complete. 온라인바카라 However, I think it will be more wonderful if your post includes additional topics that I am thinking of. I have a lot of posts on my site similar to your topic. Would you like to visit once?

    ReplyDelete
  41. This article gives the light in which we can observe the reality. This is very nice one and gives indepth information. Thanks for this nice article. rolet online

    ReplyDelete