CREATING A GOOGLE ANALYTICS TRACKING CROSS IN DATA STUDIO
Recently in Google Analytics on the Audiences tab there was a new report – “Different devices”.
This report includes information about all devices with which users interacted with your property before the conversion. In the future, this information can be used at least when creating remarketing campaigns for the most profitable devices.
But if you create reports in Data Studio, then you will not see changes for cross-devices there, since this report is under finalization.
Important! If you have Google Analytics 360, it’s better to use Google BigQuery for cross-device tracking.
Today we’ll talk about how to get started with cross-device tracking reports using Google Sheets and Apps scripts so that everyone who works in standard Google Analytics can track changes.
Initially, you should focus on the DevicePath report, as the Device Overlap and Acquisition Device reports require several changes. First you need to set up a User ID, create a view of the user ID in Google Analytics. Then you should configure Google Sheets and Apps Script for the received data. Only after that, the report will start dragging into Data Studio.
Step one: set up User ID and enable user ID view
We will need to set up the User ID in the custom view. Please note that there can be no more than 20 views. For those who do not want to delve into and remember how to properly configure this function, we attach an article here.
After defining your users and setting up your User ID view, you can find cross-device tracking reports in Google Analytics.
Step two: setting up Google Sheets (Simple version)
To make things easier, we attach a custom Google Sheets sheet that does all the work for you. Click here to get it.
Important! You do not need to request access to this document, just select the File menu item and then Create a Copy.
After you open the file, you will need to change several items in it. Change the view ID to yours that you configured in the first step. It can be found in the view settings inside the Google Analytics interface.
You can also make this setting in Google Analytics Sheets using the Add-On menu. Now you can run the report with your information.
Step two: setting up Google Sheets (hard version)
If you want to set up data transfer yourself, follow the instructions below. Create a new report in Google Sheets by going to Add-ons> Google Analytics> Create New Report.
You will see a pop-up report configuration on the right side of our table. Write a title for your report and select your account, properties and view. For this example, we will use session metrics and session counters, and then select a view for the User ID that you set earlier and the device category.
You can also apply the segment before generating the report. We will use a custom User ID in conjunction with a device category to determine which device the user visited the resource through. And the definition of “Number of sessions” will help us determine the order of interaction.
Selecting the “Create Report” command in the pop-up window, you will see a new tab for customizing the report. We’ll add a few more details here. In the “Order” line, enter the following:
“[{
«»fieldName»»: «»ga:sessionCount»»,
«»sortOrder»»: «»ASCENDING»»,
«»orderType»»: «»DIMENSION_AS_INTEGER»»
}]”
You can apply any filters to your data. The following filter should be used to rule out any cases where the custom User ID is (not set):
ga: dimension1! = (not set)
You can also choose a traffic source or geolocation to target a specific audience. Here is a list of filters and operators for URL reference.
Then you need to add the parameter to the line of the selection level. Without parameter, sampling levels will be the default for GA. You can also use FASTER to get faster results at a higher sampling rate. It is better to use HIGHER_PRECISION for a lower sampling rate, although it will take longer to run the report.
Once you’re done setting up, you can run the report by going to Add-ons> Google Analytics> Run reports. You can also schedule reports to be automatically generated every hour, day, week, or month by going to Add-on> Google Analytics> Schedule reports.
Step three: configure the application script
Google Apps Scripts lets you run JavaScript in multiple G Suite apps. We are going to use Apps Script to run the Google Analytics data we retrieved into Google Sheets and rewrite the data in the device path. You will find the Script Editor under Tools on the menu bar.
Copy and paste the following function into your code file to add the getDevicePaths function to your table:
function getDevicePaths (input) {
// Get Count of Session
var userDict = {};
for (var i = 0; i
// Check to see if the user exists in the user dict
var userId = val[1];
var device = val[2];
var numSessions = val[3];
if (! device) {
continue;
}
if (userDict[userId]) {
// d is the same structure as the thing we create below in the else, it’s {path: (array of devices), numSession: (int)}
var d = userDict[userId];
// rip the path out
var path = d.path;
// If the last place they visited from is the same as the current device we are looping through, don’t add it
if (d.path[d.path.length — 1] ! == device) {
d.path.push (device);
}
// Always increment the number of sessions the user took
d.numSession = d.numSession + numSessions;
} else {
userDict[userId] = {
path: [device],
numSession: 1
}
}
}
// We want to create a new representation of path dictionaries
var pathDict = {};
for (var key in userDict) {
var d = userDict[key];
var numSessions = d.numSession;
var path = d.path.join (“->”);
if (pathDict[path]) {
var p = pathDict[path];
p.numUsers = p.numUsers + 1;
p.numSessions = p.numSessions + numSessions;
} else {
pathDict[path] = {
numUsers: 1,
numSessions: numSessions
}
}
}
var arr = [];
for (var key in pathDict) {
var p = pathDict[key];
var numSessions = p.numSessions;
var numUsers = p.numUsers;
arr.push ([key, numUsers, numSessions]);
};
return arr;
}
Important. Be sure to save the script!
Copy and paste the following function into your code file to add the getDeviceOverlap function to your sheet:
function getDeviceOverlap (input) {
// Get Count of Users
var userDict = {};
for (var i = 0; i
// Check to see if the user exists in the user dict
var userId = val[1];
var device = val[2];
var sessionNum = val[0];
var numSessions = val[3];
if (! device) {
continue;
}
if (userDict[userId]) {
// d is the same structure as the thing we create below in the else, it’s {path: (array of devices)}
var d = userDict[userId];
// rip the path out
var path = d.path;
// If the device already exists, don’t add it
if (d.path.indexOf (device) == -1) {
d.path.push (device);
d.path.sort ();
}
} else {
userDict[userId] = {
path: [device]
}
}
}
// We want to create a new representation of path dictionaries
var pathDict = {};
for (var key in userDict) {
var d = userDict[key];
var path = d.path.join (“|”);
if (pathDict[path]) {
var p = pathDict[path];
p.numUsers = p.numUsers + 1;
} else {
pathDict[path] = {
numUsers: 1
}
}
}
var arr = [];
for (var key in pathDict) {
var p = pathDict[key];
var numUsers = p.numUsers;
arr.push ([key, numUsers]);
};
return arr;
}
Now go back to your Google Sheets, add three new tabs. Here we will recreate the Device Paths, Device Overlap and Acquisition Device tables. In cell A2 we are going to call a function from our script, enter:
= sort (getDevicePaths (filter (‘Device Paths’! A16: D, ‘Device Paths’! A16: A> 0)), 2,0)
Here we link to a tab with all our Google Analytics data and run the data through our app scripts function. Add headings to the table on line 1 to denote metrics and all dimensions.
Do the same for the Device Overlap tab. To do this, you need the following formula:
= sort (getDeviceOverlap (filter (‘Device Paths’! A16: D, ‘Device Paths’! A16: A> 0)), 2,0)
These calculations can take some time, especially if you have a large dataset. Also, if your numbers don’t match exactly, it’s okay, the point is that you might have some inconsistencies in how your User ID field is configured versus your Custom Dimension field.
Step four: connect to Data Studio
Now, we just need to add Google Sheet as data source in Data Studio. Open a new Data Studio report or add your table as a new data source to an existing report. Add a spreadsheet to your report using Google Sheet as your data source, and then select dimension: steps along the path, and users and sessions as metrics. Done!
Use this as a starting point for your cross-device reporting in Data Studio. Our Google Sheet provides basic metrics for users and sessions, but you can continue to use them for other metric calculations. You also have the option to try different filters or segments in the Google Sheets report configuration.
