Google Ads Data Transfer Service - Sporadic errors starting May 4th

[SociumMedia]

Hi All. 

We've had mostly positive experiences with the new Google Ads Transfer service to pull in client data to BigQuery to use for reporting. However starting yesterday (May 4, 2023), we've been getting errors of two types (examples below) for some scheduled transfers. What's rather peculiar is that we may have 3 days of data process just fine, get an error for the next day, and then have a subsequent day be successful. Completely unpredictable.

The error messages seem to come in two varieties:

Error while reading data, error message: CSV table references column position 26, but line starting at position:544 contains only 24 columns. File: gs://513030992809-us/2479980046/20230503/64632d5a-0000-2020-bb91-001a1143f424/20230504111506_1683198906241973581/LocationsUserLocationsStats_tmp_0; JobID: 1040889383827:google_ads_64634cbe-0000-2b29-bac7-94eb2c09abf2

and

Error while reading data, error message: Too many values in row starting at position: 586. Found 27 column(s) while expected 24. File: gs://513030992809-us/2479980046/20230502/64632d5c-0000-2020-bb91-001a1143f424/20230504114502_1683200702036460405/LocationsUserLocationsStats_tmp_0; JobID: 1040889383827:google_ads_64638f98-0000-2b29-bac7-94eb2c09abf2

I haven't updated any settings in the transfers, so I'm really not sure why we'd suddenly see what seems like structural issues when importing data from Google to Google. It does seem like the common element is that it's the user locations stats data.

Has anyone else experienced this and possibly know of a remedy? Not much I've been able to figure out beyond retrying the transfer and crossing my fingers.

[kvandres]

Good day @SociumMedia,

Welcome to Google Cloud Community!

This error may be due to the source file being transferred, based on the errors it seems there is an inconsistency with the data structure between the data being transferred from Google Ads to Bigquery, you can verify this by manually uploading the CSV file to Bigquery. You can check this link for more information on how to load the data using a local file: https://cloud.google.com/bigquery/docs/loading-data-local#appending_to_or_overwriting_a_table_using_...
If these errors are encountered again while uploading the CSV file, it seems that there is a problem with the data structure being transferred to BQ, you can change the schema these data in order to match the schema in Bigquery. 

You can also reach out to google cloud support: https://cloud.google.com/support/

Hope this will help!

[OlegS]

Hi @SociumMedia 

As a workaround you may try to import your Google Ads data into windsor.ai account first and export Google Ads data into BigQuery.

[kerryshka]

I am having the same issue. I just rescheduled backfills for missing days but there must be a better solution?

[Cris_ch]

I have the same problem, it started on May 3, 2023 were you able to solve this issue?

[SociumMedia]

Nope. Seems to just pop up every now and then and be problematic for the better part of the day. Fortunately, it seems to primarily impact the geo and location tables, which we don't really rely on for mu what we're doing.

[Snoshone07]

Hi @SociumMedia It sounds like you’re encountering some pretty frustrating issues with the Google Ads Transfer service, especially when it comes to the User Locations Stats data. The errors you’re seeing are likely due to discrepancies between the expected data structure and what’s actually being imported. This can happen occasionally due to temporary issues with data formatting on Google’s end or due to changes in how certain fields are populated.

Here are a few steps you could try to troubleshoot this:

1. Retry the transfer manually: Since you’ve mentioned that retrying has worked intermittently, it might help to manually trigger the transfer again, especially during off-peak hours. Sometimes a fresh attempt can resolve the issue if it was a transient data anomaly.

2. Check for updates or changes: Even if you haven’t changed any settings, it might be worth double-checking if there have been any recent updates or changes to the Google Ads Transfer service. Google occasionally makes updates that could affect data structures.

3. Error-specific handling: Given the specific error messages (e.g., too few or too many columns), it might be useful to set up some error-specific handling or logging in your pipeline. This could help in identifying patterns or specific conditions under which the errors occur.

4. Contact Google Support: If the issue persists, it might be necessary to contact Google Support. Provide them with the job IDs and error messages; they might be able to identify a backend issue or suggest a fix.

As an alternative or complementary solution, you might want to explore Windsor.ai. They offer a Google Ads connector that could potentially streamline your data integration with BigQuery and handle some of the edge cases that might be causing these errors. This could be a good fallback if you’re finding that the native transfer service isn’t meeting your needs consistently.

Hope this helps!