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 ("" 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) <- "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.secret)
oauth_token <- save(token,file="./oauth_token")
} else {

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.

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.


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 "". 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){ <- "2015-04-01" <- "2015-04-30" <- paste("ga:",v,sep="") #the View ID parameter need to have "ga:" in front of the ID 
     query.list <- Init( =, =, dimensions = "ga:date,    ga:deviceCategory, ga:channelGrouping,", metrics = "ga:sessions, ga:users, ga:bounceRate, ga:goalCompletions1", =
     ga.query <- QueryBuilder(query.list) <- GetReportData(ga.query, token, paginate_query = F)

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:


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($date) # dates come as character
newDate<-as.Date($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.  


  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

  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!

  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

  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..

  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.

  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

  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

  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