Correcting Week Dimension Data in Dataslayer with Google Ads Connector

Overview

When utilizing the Week Dimension (starting on Sunday) with the Google Ads connector in Dataslayer, you may encounter a situation where the returned date appears to reference the previous week, rather than the current one. This document provides a guide to address and correct this discrepancy.

Understanding the Issue

The root of this issue lies in the difference between date formats used by Google. Specifically, Google differentiates between the ISO format (YYYY-MM-DD) and the American format (MM/DD/YY). When the Google API returns dates in the American format, it may lead to inconsistencies with the week dimension data.

This behavior is related to the interaction with Google Ads and is not fault of Dataslayer itself.

Solution: Correcting the Date

Should you encounter this discrepancy, the following steps will guide you in correcting the week dimension data:

To understand the example, first let's clarify with the attached calendar how Google understands weeks. Google associates the week from 12/31 to 1/6 as another week in the year 2023 and that is why we get a week 53 when it should be week 1 of 2024

Upon loading your query, you might notice that the Year-Week dimension (starting on Sunday) displays incorrect dates. This is illustrated in the accompanying screenshot:



To adjust and obtain the correct week data, apply the following formula within your spreadsheet and drag down to cover all weeks:

=IF(IF(VALUE(RIGHT(A:A;2))=53;1;VALUE(RIGHT(A:A;2)))=D1;IF(VALUE(RIGHT(A:A;2))=53;1;VALUE(RIGHT(A:A;2)))+1;IF(VALUE(RIGHT(A:A;2))=53;1;VALUE(RIGHT(A:A;2))))

The range to select (A:A in the formula above) depends on the specific column where your week dimension data is located. Adjust accordingly to match your data setup.

By applying this formula, you can ensure that your Year-Week dimension data accurately reflects the intended time period, thereby enhancing the reliability of your data analysis within Dataslayer.

You can also obtain this issue with the Week (starting on Sunday) dimension, although we recommend using the Year-Week dimension proposed above.

As always, please contact us via our live chat on our website or via email if you still have doubts or questions. We are happy to help!

Updated on: 09/04/2024