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:
- Collect data from each website
- Categorize websites data according to specific internal business criteria
- 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)
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.
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) }
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:
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.
ReplyDeleteVery creative post, truly this is a very good job. Your explanation is very superb and I appreciate your great efforts. I like a more valuable post from your blog...
Tableau Training in Chennai
Tableau Course in Chennai
Pega Training in Chennai
Excel Training in Chennai
Power BI Training in Chennai
Primavera Training in Chennai
Unix Training in Chennai
Tableau Training in Chennai
Tableau Course in Chennai
Great Article
DeleteIEEE final year projects on machine learning
JavaScript Training in Chennai
Final Year Project Centers in Chennai
JavaScript Training in Chennai
I am learning this languages.
DeleteWhoa! 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
ReplyDeleteThanks for sharing this useful blog, easy to understanding the concept.
ReplyDeleteData Science Course in Chennai
Data Science Courses in Bangalore
Data Science Training in BTM
Data Science Training in Marathahalli
Data Science Course in Marathahalli
Best Data Science Training in Marathahalli
Data Science Institute in Marathahalli
PHP Training in Bangalore
DOT NET Training in Bangalore
Spoken English Classes in Bangalore
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/
ReplyDeleteI 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
ReplyDeleteWe 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.
ReplyDeleteJordan 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..
There are many Coach Outlet Online in the market.
ReplyDeleteThe 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.
Excellent article, good concepts are delivered nice to read your article.
ReplyDeletehow to declare array in python
python object oriented
best framework for python
goto in python
selenium automation framework interview questions and answers
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
ReplyDeleteI'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
ReplyDeleteHey 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
ReplyDeleteNice post and I am very happy to visit your blog. Keep doing...!
ReplyDeleteOracle Training in Chennai
Oracle Training in Bangalore
Oracle Training in Coimbatore
Tableau Training in Chennai
Tableau Training in Bangalore
Thanks for sharing such nice info. I hope you will share more information like this. please keep on sharing!
ReplyDeletePython Training In Bangalore | Python Online Training
Artificial Intelligence Training In Bangalore | Artificial Intelligence Online Training
Data Science Training In Bangalore | Data Science Online Training
Machine Learning Training In Bangalore | Machine Learning Online Training
AWS Training In Bangalore | AWS Online Training
IoT Training In Bangalore | IoT Online Training
Adobe Experience Manager (AEM) Training In Bangalore | Adobe Experience Manager (AEM) Online Training
Oracle Apex Training In Bangalore | Oracle Apex Online Training
ReplyDeleteAmazing Post. keep update more information.
Selenium Training in Bangalore
Selenium Training in Pune
Selenium Taining in Hyderabad
Selenium Training in Gurgaon
Selenium Training in Delhi
Great post!! Thanks for sharing..keep updated
ReplyDeletePython Classes in Chennai
Python Training Institute in Bangalore
Great post. keep sharing such a worthy information
ReplyDeleteswift training in bangalore
swift developer training in chennai
Great post. keep sharing such a worthy information
ReplyDeleteCloud Computing Training in Chennai
Cloud Training in Chennai
Great post. keep sharing such a worthy information
ReplyDeletecyber security course in bangalore
cyber security training in chennai
Fabulous post... Keep sharing
ReplyDeleteSpoken English Classes in Chennai
Best Spoken English Classes in Chennai
Great post. keep sharing such a worthy information
ReplyDeleteDevOps Training in Chennai
DevOps Training in Bangalore
ReplyDeleteThanks for sharing this.,
Leanpitch provides crash course in Brain hacks using NLP everyone can use it wisely.
Brain hacks with NLP
NLP training
NLP training
ReplyDeleteNLP crash course
Amazing blog...keep sharing
ReplyDeleteIELTS Training in Chennai
IELTS Classes in Chennai
IELTS Coaching in Chennai
IELTS Coaching centre in Chennai
Wonderful Blog.. keep updating
ReplyDeleteAWS Training in Chennai
Amazon web services Training in Chennai
AWS Training Institutes in Bangalore
AWS Certification Training in Bangalore
Nice.
ReplyDeleteIf 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.
ReplyDeleteThis post is so interactive and informative.keep update more information...
ReplyDeleteios training in bangalore
ios training institute in bangalore
This is very interesting post,Thanks for sharing this information.
ReplyDeleteCorporate Classes in Delhi
Great post. keep sharing such a worthy information
ReplyDeleteswift training in bangalore
swift developer training in chennai
카지노사이트 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..
ReplyDeleteGreetings! 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스포츠토토 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
ReplyDeleteGreetings! 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. 스포츠토토탑
ReplyDeleteNice post. I learn something totally new and challenging
ReplyDeleteon sites I stumbleupon every day. It will always be useful to read content from other writers and use something from their websites.경마사이트
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;
ReplyDelete온라인경마
magosucowep
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야설
Wow effortless, you are so creative. I am so grateful for your post. Much thanks again. Really Great.
ReplyDelete휴게텔
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횟수 무제한 출장
“He likes to take a traditional and risk-averse approach to things over a creative one.”
ReplyDelete타이마사지
Happy to read the informative blog. Thanks for sharing
ReplyDeletebest digital marketing course in chennai
best digital marketing training in chennai
Great post. keep sharing such a worthy information.
ReplyDeleteGoogle Analytics Training In Chennai
Google Analytics Online Course
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 !!
ReplyDeleteOne 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스포츠토토
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
ReplyDeleteExcellent 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. 먹튀검증
ReplyDeleteI 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
ReplyDeleteHey 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
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. 먹튀검증
ReplyDeleteThis post is so helpfull and informative.keep updating with more information...
ReplyDeleteFuture Scope Of Android
Android Applications
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.
ReplyDeleteFirst 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 ^^
ReplyDeleteI’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. 메이저토토추천
ReplyDeleteThis post is so interactive and informative.keep update more information...
ReplyDeletedot net training in anna nagar
Dot net training in Chennai
Great post. keep sharing such a worthy information.
ReplyDeletecontent writing course in chennai
online content writing course
you will need support or suggestions, write me privately.
ReplyDeleteI interested in your implementation/use case.
the best situs slot terbaik
Togel2win
daftar bo bonanza
Slot gampang menang
Great post. Thanks for sharing such a useful blog.
ReplyDeleteTally Course in Anna Nagar
Tally Course in Anna Nagar
We absolutely love your blog and find almost all of your post’s to be just what I’m looking for.
ReplyDeleteSimple 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토토사이트
Great post. keep sharing such a worthy information.
ReplyDeleteDevOps Training in Chennai
Devops Online Training
DevOps Training in Bangalore
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.
ReplyDeleteHello, 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.
ReplyDeleteWhen 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?
ReplyDeleteYour 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?
ReplyDeleteThis 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