Comparing 100+ Websites with Google Analytics
You’re a titan of industry with websites up the whazoo. You want to check in. How’s each one doing? How do they match up against their peers? How can you improve the laggards and tout the winners?
Google Data Studio only lets you blend a maximum of 5 sources. What a bummer. Thankfully, with some API calls and simple scripting, you can automate a process for pulling basic data from any number of GA accounts you own and operate.
Heres what you’ll need:
- More than 5 Google Analytics Accounts (duh)
- Google Sheets
- Some Knowledge of Javascript (notice you don’t need to be an expert– that’s because Google already has resources for making calls to the Google Analytics Core Reporting API– we’re just going to modify these for our purposes.)
- An elegant work playlist to jam out to while you casually automate a part of your data processing
Step 1: Setting Up Your Scripting Interface
Open Google Sheets, name the sheet something sexy, and head to the script editor–this is where we will live for the majority of the time.
Head to “Resources”, and hit “Advanced Google Services”. Title your script and if prompted to accept any Cloud Platform permissions from Google follow the link and accept. Head back to the script editor and once again click “Resources” > “Advanced Google Services”
In the pop-up window scroll down and enable the Google Analytics API:
Hit “OK”. Once we run the script for the first time you will have to authenticate once more with your Google account credentials.
Step 2: Looping through all the profiles contained in your many accounts
You have a lot of websites hooked up through your Google account. Each of these sites has a Web Property Id that is nested within an Account Id for its corresponding Google Analytics account. Each Web Property has a View the default being “All Website Visitor Data”– which is what we’ll be using.
As you can start to see, the hierarchy of Account > WebProperty > View gets a bit confusing and messy. Thankfully, we can use a “for” loop to isolate each site’s data and print it out to our Google sheet one at a time.
Clear your script editor and paste the following:
function dataPull() {
try {
//get number of accounts
var accounts = Analytics.Management.Accounts.list();
if (accounts.getItems()) {
var numberOfAccounts=accounts.getItems().length;
}
//clear spreadsheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.clear();
}catch(error) {
Browser.msgBox(error.message);
}
}NOTICE: the “try-catch” statement is not closed. It will remain open until the end of the function.
Here, we’re clearing the current spreadsheet, calling the Google Analytics Core Reporting API, and grabbing the total number of analytics accounts that we’ll need to loop through. Likewise, we can do the same for the number of Web Properties contained in each account.
Paste the following right under “sheet.clear();”
//start row for importing data (row 1 contains our headers)
var numRows=2;for(var i=0; i<numberOfAccounts;i++){
var AccountId = accounts.getItems()[i].getId();var webProperties = Analytics.Management.Webproperties.list(AccountId);
if (webProperties.getItems()) {
var numberOfProperties = webProperties.getItems().length;
}
NOTICE: the “for” loop is NOT closed with an ending bracket. Leave that open for now, we’re going to nest another “for” loop inside there.
Like before, we’re cycling through the total number of accounts we have, stopping at each one, and grabbing the number of Web Properties contained in that specific account. Because each Web Property corresponds to a site and contains data for that site, we’ll then grab the data from it and paste it to our spreadsheet with a few functions.
That’s where that “numRows” variable will come in handy. By specifying where we want to place the data, and incrementing that variable, we can keep an accurate count of the number of rows of data in our spreadsheet and ensure we only print data to a new and empty line.
Paste the following under the previous “if” statement:
for(var n=0; n<numberOfProperties; n++){
var propertyName= webProperties.getItems()[n].getName()
var targetProfile = getTargetProfile(i,n);
var results = getReportDataForProfile(targetProfile);
try{
if(results.getRows()){
outputToSpreadsheet(sheet,results,numRows,propertyName);
numRows+=results.getRows().length;
}else{
throw new Error('No views (profiles) found');
}
} catch(error){
sheet.appendRow([propertyName])
numRows+=1;
}
}}
}catch(error) {
Browser.msgBox(error.message);
}
}
NOTICE: both “for” loops and the overarching “try-catch” statement should now be CLOSED.
With our “numberOfProperties” variable we can loop through each Web Property, pass in key data from that property, and print it to the spreadsheet with our “ouputToSpreadsheet()” function.
In the event that the property doesn’t exist, we’ll get an error. In the event, the property has no data, we’ll simply append a row with the property name and move on to the next property incrementing our “numRows” value by 1.
With that, we’re all set up to loop through all of our profiles as we’ve passed in both “i” and “n” to our “getTargetProfile()” function which will pinpoint each site based on its exact Account Id and Web Property Id.
Step 3: Passing in Data and Outputting it to the Spreadsheet
We’ll need to set up the various functions we’ve called throughout “dataPull()”. Those include a “getTargetProfile()” function that returns a variable containing all recorded website data for a profile, a “getReportDataForProfile()” function that specifies what data we want to isolate over what time period, and an “outputToSpreadsheet” function that actually prints the data we’ve isolated to our spreadsheet in the correct fashion.
The following relies heavily upon an existing implementation guide used to automate access to Google Analytics Data in Google Sheets.
Lets start with “getTargetProfile()”:
function getTargetProfile(i,n) {
var accounts = Analytics.Management.Accounts.list();
if (accounts.getItems()) {
var targetAccountId = accounts.getItems()[i].getId();var webProperties = Analytics.Management.Webproperties.list(targetAccountId);
if (webProperties.getItems()) {var targetWebPropertyId = webProperties.getItems()[n].getId();
var profiles = Analytics.Management.Profiles.list(targetAccountId, targetWebPropertyId);if (profiles.getItems()) {
var targetProfile = profiles.getItems()[0];
return targetProfile;} else {
throw new Error('No views (profiles) found.');
}
}else {
throw new Error('No webproperties found.');
}
} else {
throw new Error('No accounts found.');
}
}
Because we’re only interested in the first “view” of a Web Property or that “All Website Data” view, we only need to grab the profile view at index “0” hence the
var targetProfile = profiles.getItems()[0];assignment instead of another loop.
Next, we can create “getReportDataForProfile()” passing in our “targetProfile” returned from the previous function:
function getReportDataForProfile(targetProfile) {var profileId = targetProfile.getId();
var tableId = 'ga:' + profileId;
var startDate = getLastNdays(30); // 30 days ago.
var endDate = getLastNdays(0); // Today.var optArgs = {
'sort': 'ga:pageviews', // Sort by pageviews
'start-index': '1',
'max-results': '250' // Display the first 250 results
};// Make a request to the API.
var results = Analytics.Data.Ga.get(
tableId, // Table id (format ga:xxxxxx).
startDate, // Start-date (format yyyy-MM-dd).
endDate, // End-date (format yyyy-MM-dd).
'ga:sessions,ga:pageviews,ga:avgSessionDuration,ga:organicSearches,ga:users,ga:newUsers,ga:bounceRate',
optArgs);if (results.getRows()) {
return results;
} else {
return null;
}
}
Followed by “getLastNdays()” to set the range of dates for the data we’d like to pull:
function getLastNdays(nDaysAgo) {
var today = new Date();
var before = new Date();
before.setDate(today.getDate() - nDaysAgo);
return Utilities.formatDate(before, 'GMT', 'yyyy-MM-dd');
}If you noticed above, the API request pulls a lot of information including “ga: organicSearches, ga;newUsers” etc.
If you want a detailed list of all metrics and dimensions and how you could include them, head HERE.
Lastly let’s include our “outputToSpreadsheet()” function:
function outputToSpreadsheet(sheet,results,numRows,propertyName) {// Print the headers.
var headerNames = [];
for (var i = 0, header; header = results.getColumnHeaders()[i]; ++i) {
headerNames.push(header.getName());
}
sheet.getRange(1, 2, 1, headerNames.length)
.setValues([headerNames]);// Print the rows of data.
sheet.getRange(numRows, 2, results.getRows().length, headerNames.length)
.setValues(results.getRows());
sheet.getRange(1,1).setValue("Site Name");
sheet.getRange(numRows,1).setValue(propertyName);
}
Here we pass in all the data we’ve pulled including our “numRows” variable to dictate which row we place our data in, our “propertyName” variable which details the title of the site, and our sheet itself.
With that, we can hit the “Run” > “Run Function” button, input our credentials and watch as data auto-populates to our spreadsheet!
Within Google Sheets you can organize your sites by pageviews, sessions, new users, bounce rates etc. using filtering for accurate benchmarking and comparison. You can even import this new sheet into Google Data Studio if you want to add it to a complex dashboard!
We’ve worked with data collected from the past 30 days, but you can customize that date range to pull in data from specific quarters, weeks, months, or years.
Step 4: AUTOMATION TIME!
This is actually the easiest part believe it or not. Instead of opening this script and running it tediously every time we want data we can set up a trigger to run that “dataPull()” function every month, week, or even day.
Head to “Edit” > “Current Project’s Triggers” and in the page that opens head to the bottom right and hit “+ Add Trigger”
In the window that opens change the “event source” to “Time-driven” and you can set the trigger to run monthly, weekly, or even daily. Scroll down and hit “Save” and THAT’s THAT.
Congratulations you Titan of Industry! You now have the building blocks in place to conduct robust data analysis, set up complex dashboards, and load in the most current data automatically as you scale.
Want to learn more? Check out some resources below:
