Custom reports

Report to understand users specific timing behaviors inside Google Data Studio for an international audience

User local time, part of user experience

Dating of collected data are based on one time zone defined inside Digital Analytics softwares like Google Analytics. For most of cases, it is fine to get a GA view configured with a unique time zone (on the property settings for Google Analytics 4, the new version of GA), common to the advertiser and the related audience. But when you address international audience with more than 20 time zones or a country with several time zones, the story is different.

You need to understand the users experience, so in which context they interact with your brand, materialized with your website or application. How can you do it, without taking care of their own timing. Even more with reports made with our favorite data viz tool, Google Data Studio, providing the features to change the dimensions used as date and time repository.


Google Data Studio - Reporting with user time instead of application time dimension

Data collection & storage, the easier way

We could imagine to combine user location and time dimensions from our data to calculate user time. But to do it, we would need a data computing layer and a service converting time zones. But it can be considered too complicated for analytics devices without data warehousing, with a simple data storage summed up to the analytics collection software (Google Analytics), and a data visualisation software directly plugged on it (Google Data Studio).

The easier solution is tracking directly the date and time of the end user inside a dedicated Google Analytics custom dimension, considering it as a global core variable inside your measurement plan, like the page language or the full page URL.



So, let’s create the full solution to use the audience date and time inside our analytics report, cooked with Google Data Studio. Because you could need to switch between brand time zone to relative user time, let’s include a control for that (…yes, you know parameter feature ๐Ÿ˜‰…like my previous posts…)


The result

You will find below an example of report with a time series chart and a week heatmap, data are changing following the selected time repository.



Ingredients to create the mechanism to switch from application to user date and time

For our report, we will need the following Data Studio components:

  • Google Analytics hit-scoped custom dimension
    To track and store the date and time of the user
  • Data source parameter
    To switch between application and user time repository
  • Calculated fields
    To wrap the parameter as a calculated field and to map the selected option of the parameter with the related time dimensions to display
  • Dropdown control
    for the dynamic selection of time repository corresponding the the parameter : Application or user
  • Time series and pivot table charts
    Finally, to display our data, we will create a time series and pivot table charts with time dimensions

Google Analytics hit-scoped custom dimension

First, we create a new hit-scoped custom dimension “User date and time” inside Google Analytics administration and change our tracking to provide the value to the right dimension index, using the old-fashioned script below. In my case, I’ve chosen the following date format “YYYY-MM-DD HH:mm” as end user date and time ; and I’ve created a custom script variable inside Google Tag Manager to collect the value through GA settings, shared by all GA tags.

NB: In the future, your will be able to do exactly the same thing for the new version of GA (Google Analytics 4). But for the moment, the Google Data Studio connector of GA4 doesn’t load custom fields corresponding to GA custom dimensions, so I will update this post when it will be possible. You could do now the same using related BigQuery export.


Users specific timing behaviors inside Google Data Studio for an international audience - Custom dimension
function()
{
  var currDate = new Date();
  function pad(number) {
    if ( number < 10 ) {return '0' + number;}
    return number;
  }
  return currDate.getFullYear()
         + "-" + pad(currDate.getMonth()+1)
         + "-" + pad(currDate.getDate())
         + " " + pad(currDate.getHours())
         + ":" + pad(currDate.getMinutes())  
}

Data source parameter

Let’s create a parameter inside our data source to store the options we wish to propose through the time repository selector (dropdown control). In this example, I use Date and Hour dimensions from Google Analytics data, as the referral dimensions for application time repository (Vs User repository represented by our new custom dimension that we will split the get mirror dimensions).

“Time repository” parameter is a list of text values:

  • Application time (with a display name providing the time zone configured inside GA, and defined as default value)
  • User time

Users specific timing behaviors inside Google Data Studio for an international audience - Parameter


Calculated fields

Now, we need the following calculated fields. I use “UTD” as prefix for “User time dimension” and “ATD” as prefix for “Application time dimension”.
Let’s begin with our 4 levels of time dimensions cast as text:

  • First, we parse our GA custom dimension string, as a Date & Time type:
    UTD – Date and time
    PARSE_DATETIME(“%Y-%m-%d %H:%M”, User date and time)
  • We built a user local day of week on which we can apply the wished order, as a Text type:
    UTD – Day of week
    REPLACE(FORMAT_DATETIME(“%w-%A”, UTD – Date and time),”0-“,”7-“)
  • We do the same for built-in date from GA, as a Text type:
    ATD – Day of week
    REPLACE(FORMAT_DATETIME(“%w-%A”, Date),”0-“,”7-“)
  • We create a field isolating the user local hour, as an Hour type:
    UTD – Hour
    CAST (FORMAT_DATETIME(“%H”, UTD – Date and time) AS NUMBER)

Then, let’s create a wrapper inside a calculated dimension just to be able to use the parameter easily in the next steps (current technical limitations, already identified by Data Studio team) inside other calculated dimensions without constraints:

  • UTD – Time repository โ€“ dim wrp
    Time repository

At last, we create the 2 dimensions that we will use inside the charts.

  • The displayed day of week, as a Text type:
    UTD – Selected day of week
CASE 
WHEN UTD - Time repository โ€“ dim wrp = "User time" THEN UTD - Day of week
WHEN UTD - Time repository โ€“ dim wrp = "Application time" THEN ATD - Day of week
END
  • The displayed hour, as an Hour type:
    UTD – Selected hour
CASE 
WHEN UTD - Time repository โ€“ dim wrp = "User time" THEN UTD - Hour
WHEN UTD - Time repository โ€“ dim wrp = "Application time" THEN Hour
END

Dropdown control

Now, the configuration of the data source is finished, we add the dropdown menu and we select the parameter Time repository as control field (Data tab) and adapt styles.


Charts

Our last action is the creation of our 2 charts:

  • Time series chart
    • Use UTD – Selected hour as dimension
    • Add your metric (Sessions as example)
    • Customize styles
  • Pivot table chart
    • Use UTD – Selected hour as row dimension and UTD – Selected day of week as column dimension
    • Add your metric
    • Sort by UTD – Selected hour ascending for rows and UTD – Selected day of week ascending for columns
    • Apply an heatmap on style tab

Let’s make a quick review of the chart with the video below.

Finito



Mets toi ร  leur place…