Google Analytics in Google Data Studio: Extracting Dashboard Title & Page Info for better breakdowns
I use Google Data Studio to create dashboards, and like many others, I want to know which dashboards are being used, by who, from where, on which devices. You know….usual Google Analytics website stuff.
It’s a good thing that Google provides the two main things to solve this.
- Add Google Analytics tracking codes to your Data Studio Dashboard
- Connect to Google Analytics data from Data studio with the prebuilt data connector
All that stuff is easy. It’s point and click. It takes literally 2 minutes to set up. After you get it set up though, you’ll notice that there’s only one dimension/field that includes the Dashboard Name & Dashboard Page, called “Page Title.”
So if your dashboard name is “My Analytics Dashboard” and you have only 1 page, then your “Page Title” value will be “My Analytics Dashboard”
However, if your dashboard name is “My Analytics Dashboard” and you have more than 1 page, then your “Page Title” values will be something like:
My Analytics Dashboard › Page 1
My Analytics Dashboard › Page 2
Important to note that the “›” character is NOT a > (greater-than) character.(This will matter later when we talk about regex)
It’s actually this character: https://www.toptal.com/designers/htmlarrows/punctuation/single-right-pointing-angle-quotation-mark/
Let’s get extractin’
So…if we want to filter or show visualizations that do things like show the breakdown of page use over time for an individual dashboard, or show distribution of page views per dashboard page (grouped by its parent dashboard)…then we need to split out the single “Page Title” field into 2 fields…one for the Dashboard Name/Title only, and one for the Page Name only.
Still with me? Now for the regex.
To do this, we need to create a couple of calculated fields. In basic terms, we will look at the “Page Title” value, and extract the parts before and after the “›” character. Here’s the code:
Calc Field for Dashboard Name:
TRIM(REGEXP_EXTRACT(Page Title,’(.*)›’))
Calc Field for Dashboard Page:
TRIM(REGEXP_EXTRACT(Page Title,’›(.*)$’))
Example Screenshot:
Now we’re done! Right?
WRONG
When you look at your newly minted calculated fields, you’ll notice for NULL values for all your Dashboards that only have 1 page. That’s because the REGEXP_EXTRACT code doesn’t account for the case where the character doesn’t exist.
No big deal, we can fix that with a CASE statement, right? WRONG
You can’t use a function in the THEN or ELSE portions of a CASE statement, which means we CANNOT do something like this:
CASE
WHEN REGEXP_EXTRACT(Page Title,’›(.*)$’) IS NULL THEN Page Title
ELSE TRIM(REGEXP_EXTRACT(Page Title,’›(.*)$’))
END
(I haven’t checked the syntax, but trust me, you can’t do this in one step)
What we CAN do however, is create another calculated field, that looks at our first calculated field.
CASE
WHEN calculated_dashboard_page IS NULL THEN “Default Page”
ELSE calculated_dashboard_page
END
This says….when there is a multi-page dashboard, which will have a value in our original calculated field, use that. If this is a single-page dashboard, then call that page “Default Page” so we can report on it in data studio.
and
CASE
WHEN calculated_dashboard_name IS NULL THEN Page Title
ELSE calculated_dashboard_name
END
This says….if the first calculated field for the dashboard name is NULL, use the Google Analytics field called “Page Title” as the Dashboard Name. But if our first calculated field is not NULL, then use what we extracted.
Still with me? Okay, so…
…we did the extraction and CASE statements in 2 steps instead of one.
You should be able to take any of those nice Google Analytics community templates, use this little process to create 4 new calculated fields, and then just update the appropriate visualizations to use them instead of the defaults.
Now, you can use Google Analytics to capture usage data about your Google Data Studio dashboards. Then use Google Data Studio to display useful Google Analytics data in a dashboard.
Just keep in mind if you put an analytics tracking code on your tracking dashboard, you might create an infinite loop! Analytics Inception!
Used for this demo….
Analytics Dashboard Link: https://datastudio.google.com/reporting/83e4d040-64f3-44ce-86c0-1411dfba3932
Other Dashboard Links that have the analytics code on them to flow into the Analytics Dashboard:
https://datastudio.google.com/embed/reporting/0f18cadb-b4df-4d80-89df-0c926bc5119d/page/RZmbB
https://datastudio.google.com/embed/reporting/83e4d040-64f3-44ce-86c0-1411dfba3932/page/4VDGB