The user ID feature in Google Analytics is a valuable one that all websites and mobile apps should use when tracking cross-device behavior, but what if you want to understand how specific users are behaving? For example, imagine that you have an internal mobile app where your user needs to login to access the app, and you want to monitor how each of them are using the app? This could give you invaluable information because you’ll then be able to personally reach out to them to tie your qualitative and quantitative analysis together.

Due to the Google’s terms of service, you cannot pass personally identifiable information (PII) into Google Analytics, but there are ways around that by matching user IDs to the names outside Google Analytics.

In this post, I’ll show you how to take user IDs from Google Analytics, pass them into Google Sheets, match them with user names and then pull them into Google Data Studio to create an automated report broken down by your specific users.

Note: All data you see in this post has been anonymized and is for demonstration purposes only. No users were injured in the making of this blog post.

Pass the User ID into a custom dimension

I won’t go into detail on how to set up user ID for your website as there are a couple different methods depending on if you’re using Google Tag Manager or not.

Assuming you are already passing the user ID into GA, you’ll now want to make sure it’s passed into a custom dimension. We want to do this because we’ll want to pull this value from the reporting API and we cannot pull it directly from the userId field in GA.

Create a custom dimension with a hit-level scope and pass the user ID value into this dimension on all hits. This means passing it into all of your events in addition to your page views. Simo Ahava has a solid post explaining how to pass the user ID into a custom dimension with GTM.

Once you’re successfully passing the user ID into a custom dimension, you should be able to see it in your reports. An easy way to look at this is by creating a custom report with the User ID dimension and Sessions metric. Below is a screenshot example of what that’ll look like.

Google Analytics User ID

Pull the custom dimension into Google Sheets

Now we’ll want to pull the user ID and any other associated dimensions and metrics into Google Sheets. I love using Google Sheets because it allows you to automate reports using the Reporting API without knowing how to code.

If you haven’t worked with Google Analytics in Google Sheets, check out this helpful page from Google. Once you get the hang of it, you may never go back to Excel again!

Create a new report titled ‘User IDs’ using the following fields:
Metrics: ga:sessions
Dimensions: ga:dimensionXX (note: XX should match the dimension ID in GA)
Sort: -ga:sessions

Google Analytics Sheets

Run your report and it should look something like this:

Screen Shot 2016-08-19 at 4.03.53 PM

Match the User ID with the user name

Alrighty, we have the User ID in our reports and now it’s time to match it up with the user name. We’ll first want to create a new sheet with 2 columns for our User ID and Username. Typically, you’ll get this data directly from your user database.

google sheets user names

Next, we’ll want to create another sheet titled ‘Sessions’ where we’ll pull in the User ID, Username and Sessions.

The User ID column should pull from your User IDs sheet/report that was generated from Google Analytics.

Screen Shot 2016-08-19 at 4.30.47 PM

The Username column should use a Vlookup to match the User ID value with the username from your Usernames sheet.

Screen Shot 2016-08-19 at 4.34.59 PM

The Sessions column should pull from the Sessions metric in your User IDs sheet/report that was generated from Google Analytics.

Screen Shot 2016-08-19 at 4.35.56 PM

Automate the report

Now that we have the information flowing in and structured cleanly, we’ll want to automate it. This will allow us to build a report in Google Data Studio that’s always up to date. Automate the report by selecting Add-ons > Google Analytics > Schedule Reports from the top menu. Set the reports to run automatically every hour.

Screen Shot 2016-08-19 at 4.44.12 PM

Pull this information into Google Data Studio

Now that we have all of the information flowing nicely into Google Sheets, let’s pull it into Google Data Studio for a nice report that you can share with your boss! I’m going to copy the Acme Sample report that Google provides to you and apply some simple edits.

Create a new table and add the Sessions table from Google Sheets as your data source. Make your dimension equal Username and metric equal Sessions like shown below.



Now you have a beautiful, automated report that integrates some of the great analytics tools Google has to offer. You can also slice and dice this data any way you like. This is just a taste of what you can do when you learn how these powerful tools work together.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.