BigQuery Data Transfer Service for Google Ads - timezone issues?

[ajdncnsn]

Data transfer service thinks that yesterday is in the future. We try to download data for the last n days and it refuses to include yesterday's data. Sounds like a timezone mismatch (project on US servers, data timezone definition in Sydney) but nothing we have tried has been able to correct it.

Anyone know how to fix this?

[caryna]

Hi @ajdncnsn,

Welcome to the Google Cloud Community!

You are correct that this issue is likely due to a timezone mismatch. During the data transfer, the US server perceives Sydney's 'yesterday' as still a few hours in the future. Consequently, the service might overlook data from 'yesterday,' as the US server's clock has not yet reached that timeframe.

Here are the possible solution how to fix it:

• Check your Data Transfer configuration to ensure the backfill is triggered correctly. This will help you to fill the gaps that might be missing in your tables.
• This may also cause conversion delay, try to increase the refresh window.
• If you are utilizing custom queries, leverage SQL's date and time functions to precisely target the desired data range. For example, use FORMAT_TIMESTAMP() to display timestamp data in a specific time zone, or manually adjust the timestamp with DATETIME_SUB to fine-tune your data range as needed.
• You can also review the Data Transfer Logs for any errors or warnings about timezone issues.

If these steps don't resolve the issue, please reach out to Google Cloud Support. Their team has specialized expertise in diagnosing underlying problems. When contacting them, provide comprehensive details and include screenshots from both the Google Ads UI and any discrepancy reports from BigQuery. This will help them better understand and address your issue.

I hope the above information is helpful.

[Snoshone07]

Hi @ajdncnsn it sounds like this issue could indeed be related to a timezone mismatch, which is a common challenge when working with systems that operate across different regions. Here are some steps you can take to address it:

1. Check Timezone Settings

Make sure the following configurations are aligned:

• Project Location Settings: Confirm that your Google Cloud project is set to the correct timezone.
• Source Data Timezone: Verify the timezone of the data source. For instance, if the data originates from Sydney but is processed on US servers, adjustments might be needed for data exports.
• BigQuery Queries: When querying data in BigQuery, use functions like TIMESTAMP() or DATETIME() to standardize timestamps and avoid inconsistencies.

2. Use UTC for Simplicity

Storing and processing timestamps in UTC is often the best practice. This ensures consistency, and you can convert to your desired timezone when running queries or creating reports.

3. Review Data Transfer Schedules

Some data transfer services operate based on server time, which may not match your local timezone. Check the transfer schedule to see if there’s an option to adjust for timezones.

4. Fix "Yesterday in the Future" Issues

If yesterday's data isn't being included due to timezone mismatches, consider these solutions:

• Use a custom query that accounts for timezone differences. For example:
  WHERE timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY) AND TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
• Alternatively, create a buffer by querying data from the last 48 hours. This helps capture data that might overlap due to timezone differences.

5. Consider a Centralized Data Pipeline

If timezone mismatches are a recurring issue, using a data pipeline solution like Windsor.ai can be helpful. These tools integrate data from multiple sources and automatically handle timezone adjustments, simplifying the reporting process.

Final Thoughts

Timezones can be tricky, especially in global projects, but aligning the settings across your source, transfer, and destination systems is key to resolving these challenges. Hope all the explained might be helpful!