Solving the ‘Day of Week’ discrepancy between GA4 and Looker Studio.

The problem: Day of Week mismatch.

When pulling the Day of Week data from GA4 into Looker Studio, you might notice that the days don't align as expected. This is because GA4 uses integers 1 through 7 to represent the days of the week (1 for Monday, 2 for Tuesday, and so on until 7 for Sunday). 

Looker Studio, however, uses integers 0 through 6 (0 for Sunday, 1 for Monday, and so on until 6 for Saturday).

This mismatch causes two main issues:

1. Misaligned time series charts: The data appears shifted by one day.
2. Blank sections in reports: There is no data for what Looker Studio considers the last day of the week (Sunday), as GA4's mapping doesn't include a 0.

Details of the discrepancy.

In Looker Studio, several built-in dimensions are available to display the day of the week, but they exhibit different behaviours that contribute to the problem:

1. Day of Week:
   • Intended to return the integer day of the week, with values ranging from 0 to 6, where Sunday is 0.
   • However, GA4 provides values from 1 to 7, causing misalignment.
2. Day of Week - Type Day of Week (DoW):
   • Converts integers to day names but expects a 0-6 range.
   • With GA4's 1-7 values, it results in incorrect mappings, and the value 7 returns a number instead of a day name.
3. Day of Week name:
   • Returns the day of the week in text format (e.g., Sunday, Monday).
   • Correctly displays day names but cannot be used in time series charts due to its text data type.
4. Day of Week Name – Type DoW:
   • Changing its type to Day of Week results in null values due to misalignment with the expected format.

Example of the discrepancy.

Below, is a table displaying the returned values of each dimension:

With a time series chart, the dimension must be of type Date and Time. This can be clearly identified with a calendar logo next to the dimension, such as:

Below is a visualisation of how our time series charts will appear when utilising the Day of Week and Day of Week Name dimensions:

In this example, Day of Week - Type DoW fails to align properly, particularly when the day of the week is Sunday (7). Additionally, time series charts using Day of Week Name - Type DoW will not return a visualisation, as the values are null.

Importance of the Day of Week dimension.

When analysing data over time, simply using the date doesn’t allow for easy aggregation of data by days of the week over longer periods. 

For instance, identifying trends or patterns specific to certain days (like higher traffic on Mondays) becomes cumbersome. The Day of Week dimension helps to quickly aggregate and visualise these patterns, provisioning more actionable insights than looking at dates individually.    

The solution: Custom dimension with WEEKDAY formula.

To rectify this discrepancy, we can create a custom dimension in Looker Studio using the WEEKDAY formula to align the day-of-the-week integers correctly. Here’s how to set it up:

1. Open your Looker Studio report and navigate to your data source
2. Add a new field by clicking on "Add a Field"
3. Create a calculated field using the following formula:
   WEEKDAY(Date)
4. This formula returns the day of the week for a given date, using the 0-6 integer system that Looker Studio expects. Change the data type to ‘Day of week’
5. Name your new field appropriately, such as "Corrected Day of Week"

1. Use this custom dimension in your reports and visualisations instead of the default day of the week field from GA4.

Why it works.

The WEEKDAY(Date) function in Looker Studio returns the day of the week for a given date in the 0-6 integer format. This aligns perfectly with Looker Studio's expectations, ensuring your data is displayed correctly and eliminating the misalignment and blank sections.

The underlying issue.

It appears the root cause of this discrepancy lies with the GA4 connector to Looker Studio. The connector pulls the day of the week data as integers 1-7, which doesn't align with Looker Studio’s 0-6 system. While we wait for an official fix, this custom dimension workaround ensures our data remains accurate and actionable.

Conclusion.

Discrepancies like these remind us of the importance of understanding the tools we use and being prepared to implement creative solutions. By creating a custom dimension in Looker Studio, we can bridge the gap between GA4's and Looker Studio's day-of-the-week representations, ensuring our reports are both accurate and insightful.

Learn more about our specialist analytics team today to learn how we can help you achieve measurement clarity.