Time-of-Day reporting in GA4 & Looker Studio

universal analytics time of day

 

Users by time of day in Universal Analytics

Google’s Universal Analytics gave us the ability to report on traffic and other metrics by time of day and day of week, useful dimensions for understanding the behavior of a website’s audience. The visualization to the right is from the Universal Analytics home screen, and in addition to that you could create pivot tables and other report types in UA with these and other dimensions.

GA4 does not have these capabilities, but it is possible to create a visualization that is similar to this in Looker Studio, using one of two approaches. The first approach uses the integration between GA4 and Google BigQuery, plus a little Looker Studio magic. The second approach uses a custom JavaScript variable in Google Tag Manager and a custom dimension in GA4. Both approaches are detailed below.

The pivot table we will be creating, showing GA4 Sessions by time of day and day of week in Looker Studio:

image 3

How to Report by Time of Day in GA4

How to Access Time-of-Day Dimensions in GA4 Reports

There are two standard dimensions in GA4 that incorporate hour-of-day: Date + Hour and Hour. There are no standard reports or visualizations that use these dimensions, but you can add them as secondary dimensions to any report. To add a secondary dimension, click the plus sign to the right of the primary dimension in a report:

image 4

There’s not much else you can do with hour data in regular GA4 reports. Unlike Universal Analytics, you have very little control over chart types and configurations. The only thing I find useful about this is that you can export the data and do proper analysis and visualization in Excel or Google Sheets. To do this, click the ‘Share’ icon at the top of the report.

image 5

Google Sheets Formulas to Extract Hour or Day from GA4’s Date + Hour dimension

If you are using the Date + Hour dimension, it is formatted like this: 2023051007. The following formula will extract the hour in Google Sheets:

=RIGHT(A1,2)

Replace ‘A1’ with the Date + Hour cell reference. This hairy beast will extract the day of week:

=TEXT(WEEKDAY(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))),"ddd")

How to Create an Hour-of-Day exploration in GA4

GA4 Explorations offer slightly more utility regarding time-of-day reporting. For example, you can add more than two dimensions to a free form exploration, and you can apply column dimensions to create a rudimentary pivot table. Unfortunately, there is not a day-of-week dimension and charts and graphs in Explorations have very limited options for formatting and presentation, so it is not possible to create a visualization that incorporates both time of day and day of week.

What you can do is to create a line chart that shows traffic and other metrics by hour:

image 7

Here are the steps to create this visualization:

     

      1. Create a Free form Exploration

      1. Add Sessions as a metric

      1. Switch the visualization to Line chart

      1. Change the granularity to hour

      1. Add Sessions to Values

      1. Voilá! From here you can add filters, segments and breakdown dimensions to explore more specific questions.

    Time-of-day Visualization in Looker Studio
    Using BigQuery

    Read on or watch the following video for a detailed walkthrough of how to set up the BigQuery data source in Looker Studio and build the pivot table. Further down in this post, I include a second video that shows the steps to add a custom Hour dimension for importing into Looker Studio using JavaScript and Google Tag Manager.

    If you are new to GA4 data in BigQuery…

    You’ll need to start by setting up BigQuery and enabling the BigQuery export in GA4. For the purposes of what I am going to show you, you don’t need to do anything in BigQuery – an advantage of the BigQuery data source Custom Query option is that it is self-contained and runs on demand. For the same reason it is also a lot slower than pre-building your reporting data in BigQuery. Custom Queries are an easy way to get started, but if you are going to be working with GA4 data a lot, you’ll want to start getting your head around BigQuery and the structure of GA4 data.

    For that, I highly recommend that you check out ga4bigquery.com. The SQL I share below is pieced together from code samples on that site – a big thank you to Johan van de Werken for sharing so much valuable information! If you are new to SQL altogether, or want to dig deeper into GA4 data in BigQuery, I also recommend the video course Johan offers in collaboration with Simo Ahava.

    Creating the BigQuery Custom Query connector

    • In Looker Studio, start by selecting ‘Add data’, then choose the BigQuery connector type.
    • Then select ‘CUSTOM QUERY’ on the left-hand side and select your BigQuery billing project. You’ll need to be logged in with an account that has access to BigQuery. Note that if ‘billing project’ sounds scary, don’t worry too much. There’s a pretty good chance your BigQuery usage will fall in the free tier, and if you do end up paying, it won’t cost much. We have four GA4 BigQuery datasets, two for fairly busy sites, and in total pay a few dollars a month. No promises though, usage may vary depending on your circumstance.
    • Copy and paste the code below.
    select
        -- day of week, starting with Sunday (dimension)
        format_date('%w',parse_date("%Y%m%d",event_date)) as day_of_week,
        -- hour (dimension)
        format("%02d",extract(hour from timestamp_micros(event_timestamp))) as hour,
        count(event_name) as sessions
    from
        -- change this to your google analytics 4 export location in bigquery
        `yourbigqueryproject.analytics_12345678.events_*`
    where
        -- uses dynamic start and end date parameters supported by Looker Studio
        _table_suffix between @DS_START_DATE and @DS_END_DATE
        and event_name = "session_start"
    group by
        day_of_week, hour
    • You’ll need to change the table reference in the ‘from’ clause to point to your table. In the video, I show a trick for copying it from your BigQuery project. I’ve learned the hard way that it can be easy to get it wrong if you try to type it in manually. You’ll also need to replace the table’s date suffix with an asterisk. I.e., change:
      `yourbigqueryproject.analytics_12345678.events_20230515`
      to
      `yourbigqueryproject.analytics_12345678.events_*`
      The reason for this is that BigQuery automatically adds a date partition to the GA4 data. As a result, each partition has a table reference that ends in the value of the event_date field.
    • Check the ‘Enable date range parameters’ box. This enables dynamic substitution of start and end date values that come from the date range of your dashboard. For example, if you add a date-picker to your dashboard, when you update the date range, it will update the query!
    • Click ‘Add’ to add your connector.

    Configuring the pivot table

    Now you have a data source with day of week, hour of day and sessions. To visualize this data in a pivot table:

    • Insert a pivot table into your dashboard and make sure it’s connected to the data source you just created
    • Set the row dimension to time of day
    • Set the column dimension to day of week
    • Set the metric to Sessions
    • Sort row by hour ascending
    • Sort column by day of week ascending
    • Change to a heatmap

    I also like to get rid of the header and just use a text box to describe the chart. I do this by setting the header background to transparent and making the text match the background of my dashboard.

    That’s it! Now you have an easy-to-read view of how traffic varies to your website across days of the week and hours of the day.

    image 3
    Sessions by time of day and day of week in Looker Studio

    Time-of-day Visualization in Looker Studio
    Using JavaScript

    After figuring out the method above and posting the first video on YouTube, a commenter asked if there was a way to create the same visualization without needing BigQuery. At first I thought “no”, but I thought about it a bit more and a lightbulb went off. It occurred to me that I could create a custom JavaScript variable in Google Tag Manager that returns the current hour, and add that as a parameter to the GA4 configuration tag. From there, I could capture the parameter as a custom dimension in GA4. While the native GA4 Hour dimension is not available in Tag Manager, custom dimensions and metrics are.

    The following video (and the continuation of the blog below) describes this second approach.

    https://youtu.be/IbtWt5Mv3S4

    Creating an ‘hour’ variable in GTM with help from ChatGPT

    Following are the steps to add the hour parameter to the GA4 configuration tag in GTM:

    • In GTM, add a User-Defined Variable
      • Set the Variable Type to Custom JavaScript
      • Copy in the code below
      • Change the time zone in the code to the correct time zone for your GA4 property – this wikipedia page has a complete list of timezones in the correct format
      • In the video, I test the code using jsbin.com – you shouldn’t need to test it again, but jsbin is pretty handy to know about
      • Save your variable
    • Then add an ‘hour’ parameter to your GA4 configuration tag
      • In the Configuration tag, you add a parameter by selecting ‘Add Row’ under Fields to Set
      • Name the parameter “hour” and set the value to the variable you just created
    • Test your parameter in GTM Preview mode
      • Click on your GA4 configuration tag in Tag Assistant to see which message (trigger) fired the tag
      • Click on that message on the left bar
      • Then click on your GA4 configuration tag again – if you view it with the correct message selected on the left, you get to see the value of the hour parameter
      • Once you’ve confirmed that it shows the correct value, you can Submit (publish) your Tag Manager container

    Following is the JavaScript code for the GTM variable – check out the video to see how I got ChatGPT to write it for me 🙂

    function getHourInTimeZone() {
      var timeZone = 'America/Denver';
      // change timeZone value to the desired time zone
      // list of time zones: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
      var date = new Date();
      var options = {
        timeZone: timeZone,
        hour: 'numeric',
        hour12: false
      };
      return date.toLocaleTimeString('en-US', options);
    }

    Adding a custom Hour dimension in GA4

    In GA4:

    • Go to Admin > Custom definitions and click ‘Create custom dimensions’
    • Name your dimension “Hour” – since this dimension will be redundant with the native Hour dimension, you could also name it “Hour – custom”, but I just named mine Hour.
    • I added this to the Description, to make it clear to other GA4 users why it was added as a custom dimension: “Redundant with built-in dimension, but necessary to make visible in Looker Studio”
    • Specify the event parameter you just created in GTM and Save

    Using the GTM GA4 connector instead of BigQuery GA4 data in Looker Studio

    In Looker Studio:

    • Refresh fields in your GA4 data source to see your custom dimension
      • Go to Resource > Manage added data sources
      • Click EDIT to the right of your GA4 data source
      • Click REFRESH FIELDS in the lower left corner, then Apply, then click DONE
    • Follow the steps outlined above to create the pivot table, with the following changes
      • Use your GA4 data source instead of BigQuery
      • Select ‘Day of week’ as your column dimension – for whatever reason, the Looker Studio GA4 connector does include this dimension without having to do any customization
    gold lines expanding outward

    Be sure to check out our other tutorials for GA4 and GA4 in Looker Studio. And reach out if you’re interested in customized one-on-one training to improve the marketing analytics practice at your business.

    Share This Post

    Share This Post

    master GA4

    with personalized training

    A training program like no other. Work with expert marketers for 1:1 training to maximize learning & impact.

    have you registered?

    Our next free digital marketing seminar is coming soon!

    [MEC id="946"]