Google ads connector not loading data for yesterday

  • Is this your first time deploying Airbyte?: No
  • OS Version / Instance: Ubuntu 20
  • Memory / Disk: 16Gb / 100 Gb
  • Deployment: Docker
  • Airbyte Version: 0.35.27-alpha
  • Source name/version: google ads 0.1.36
  • Destination name/version: Postgres
  • Step: during sync
  • Description: The connector does not load data for today or yesterday. Everything including May 3rd is there.

Hi everyone,My google ads connector does not load any data for yesterday and today. Airbyte does not show an error, it just pretends there is no data, although in the google ads UI I see data for the past two days.

  • does anybody experience the same problem?
  • any ideas how I could resolve the issue?

Grateful for any hints.P.S.: I already reset the connection and did a fresh full load, but still no data for yesterday

Hello!
Could you please upload the complete sync logs?
Could you please also share which streams are affected by this?
What happens if you create a new source + connection with this source connector with yesterday’s start date?

Hi,

the stream affected is the account_performance_report. I don’t load other reports at the moment. If i create a new source and set yesterday as start date I get I do get data for yesterday, but not today. When I set the start date back to another date I do not get data for yesterday anymore. In no setting I get data for the current day anymore…

Here are my logs:

2022-05-05 21:48:55 e[32mINFOe[m i.a.w.w.WorkerRun(call):49 - Executing worker wrapper. Airbyte version: 0.35.27-alpha
2022-05-05 21:48:55 e[32mINFOe[m i.a.w.t.TemporalAttemptExecution(get):105 - Docker volume job log path: /tmp/workspace/5205/0/logs.log
2022-05-05 21:48:55 e[32mINFOe[m i.a.w.t.TemporalAttemptExecution(get):110 - Executing worker wrapper. Airbyte version: 0.35.27-alpha
2022-05-05 21:48:55 e[32mINFOe[m i.a.w.DefaultReplicationWorker(run):103 - start sync worker. job id: 5205 attempt id: 0
2022-05-05 21:48:55 e[32mINFOe[m i.a.w.DefaultReplicationWorker(run):115 - configured sync modes: {null.account_performance_report=full_refresh - overwrite}
2022-05-05 21:48:55 e[32mINFOe[m i.a.w.p.a.DefaultAirbyteDestination(start):69 - Running destination...
2022-05-05 21:48:55 e[32mINFOe[m i.a.c.i.LineGobbler(voidCall):82 - Checking if airbyte/destination-postgres:0.3.19 exists...
2022-05-05 21:48:55 e[32mINFOe[m i.a.c.i.LineGobbler(voidCall):82 - airbyte/destination-postgres:0.3.19 was found locally.
2022-05-05 21:48:55 e[32mINFOe[m i.a.w.p.DockerProcessFactory(create):157 - Preparing command: docker run --rm --init -i -w /data/5205/0 --log-driver none --network host -v airbyte_workspace:/data -v /tmp/airbyte_local:/local airbyte/destination-postgres:0.3.19 write --config destination_config.json --catalog destination_catalog.json
2022-05-05 21:48:55 e[32mINFOe[m i.a.c.i.LineGobbler(voidCall):82 - Checking if airbyte/source-google-ads:0.1.36 exists...
2022-05-05 21:48:55 e[32mINFOe[m i.a.c.i.LineGobbler(voidCall):82 - airbyte/source-google-ads:0.1.36 was found locally.
2022-05-05 21:48:55 e[32mINFOe[m i.a.w.p.DockerProcessFactory(create):157 - Preparing command: docker run --rm --init -i -w /data/5205/0 --log-driver none --network host -v airbyte_workspace:/data -v /tmp/airbyte_local:/local airbyte/source-google-ads:0.1.36 read --config source_config.json --catalog source_catalog.json
2022-05-05 21:48:55 e[32mINFOe[m i.a.w.DefaultReplicationWorker(lambda$getDestinationOutputRunnable$6):337 - Destination output thread started.
2022-05-05 21:48:55 e[32mINFOe[m i.a.w.DefaultReplicationWorker(run):157 - Waiting for source and destination threads to complete.
2022-05-05 21:48:55 e[32mINFOe[m i.a.w.DefaultReplicationWorker(lambda$getReplicationRunnable$5):278 - Replication thread started.
2022-05-05 21:48:56 e[43mdestinatione[0m > SLF4J: Class path contains multiple SLF4J bindings.
2022-05-05 21:48:56 e[43mdestinatione[0m > SLF4J: Found binding in [jar:file:/airbyte/lib/log4j-slf4j-impl-2.17.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
2022-05-05 21:48:56 e[43mdestinatione[0m > SLF4J: Found binding in [jar:file:/airbyte/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
2022-05-05 21:48:56 e[43mdestinatione[0m > SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
2022-05-05 21:48:56 e[43mdestinatione[0m > SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
2022-05-05 21:48:58 e[43mdestinatione[0m > 2022-05-05 21:48:58 e[32mINFOe[m i.a.i.d.p.PostgresDestination(main):89 - starting destination: class io.airbyte.integrations.destination.postgres.PostgresDestination
2022-05-05 21:48:59 e[43mdestinatione[0m > 2022-05-05 21:48:58 e[32mINFOe[m i.a.i.b.IntegrationCliParser(parseOptions):118 - integration args: {catalog=destination_catalog.json, write=null, config=destination_config.json}
2022-05-05 21:48:59 e[43mdestinatione[0m > 2022-05-05 21:48:59 e[32mINFOe[m i.a.i.b.IntegrationRunner(runInternal):121 - Running integration: io.airbyte.integrations.base.ssh.SshWrappedDestination
2022-05-05 21:48:59 e[43mdestinatione[0m > 2022-05-05 21:48:59 e[32mINFOe[m i.a.i.b.IntegrationRunner(runInternal):122 - Command: WRITE
2022-05-05 21:48:59 e[43mdestinatione[0m > 2022-05-05 21:48:59 e[32mINFOe[m i.a.i.b.IntegrationRunner(runInternal):123 - Integration config: IntegrationConfig{command=WRITE, configPath='destination_config.json', catalogPath='destination_catalog.json', statePath='null'}
2022-05-05 21:48:59 e[43mdestinatione[0m > 2022-05-05 21:48:59 e[33mWARNe[m c.n.s.JsonMetaSchema(newValidator):338 - Unknown keyword order - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2022-05-05 21:48:59 e[43mdestinatione[0m > 2022-05-05 21:48:59 e[33mWARNe[m c.n.s.JsonMetaSchema(newValidator):338 - Unknown keyword examples - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2022-05-05 21:48:59 e[43mdestinatione[0m > 2022-05-05 21:48:59 e[33mWARNe[m c.n.s.JsonMetaSchema(newValidator):338 - Unknown keyword airbyte_secret - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2022-05-05 21:48:59 e[43mdestinatione[0m > 2022-05-05 21:48:59 e[33mWARNe[m c.n.s.JsonMetaSchema(newValidator):338 - Unknown keyword multiline - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2022-05-05 21:48:59 e[44msourcee[0m > Starting syncing SourceGoogleAds
2022-05-05 21:49:00 e[43mdestinatione[0m > 2022-05-05 21:49:00 e[32mINFOe[m i.a.i.b.s.SshTunnel(getInstance):170 - Starting connection with method: NO_TUNNEL
2022-05-05 21:49:00 e[43mdestinatione[0m > 2022-05-05 21:49:00 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$toWriteConfig$0):98 - Write config: WriteConfig{streamName=google_ads_de_account_performance_report, namespace=null, outputSchemaName=airbyte, tmpTableName=_airbyte_tmp_bej_google_ads_de_account_performance_report, outputTableName=_airbyte_raw_google_ads_de_account_performance_report, syncMode=overwrite}
2022-05-05 21:49:00 e[43mdestinatione[0m > 2022-05-05 21:49:00 e[32mINFOe[m i.a.i.d.b.BufferedStreamConsumer(startTracked):116 - class io.airbyte.integrations.destination.buffered_stream_consumer.BufferedStreamConsumer started.
2022-05-05 21:49:00 e[43mdestinatione[0m > 2022-05-05 21:49:00 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onStartFunction$1):123 - Preparing tmp tables in destination started for 1 streams
2022-05-05 21:49:00 e[43mdestinatione[0m > 2022-05-05 21:49:00 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onStartFunction$1):127 - Preparing tmp table in destination started for stream google_ads_de_account_performance_report. schema: airbyte, tmp table name: _airbyte_tmp_bej_google_ads_de_account_performance_report
2022-05-05 21:49:01 e[43mdestinatione[0m > 2022-05-05 21:49:01 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onStartFunction$1):133 - Preparing tables in destination completed.
2022-05-05 21:49:01 e[44msourcee[0m > Syncing stream: account_performance_report 
2022-05-05 21:49:17 e[32mINFOe[m i.a.w.DefaultReplicationWorker(lambda$getReplicationRunnable$5):300 - Records read: 1000
2022-05-05 21:49:19 e[44msourcee[0m > Read 1188 records from account_performance_report stream
2022-05-05 21:49:19 e[44msourcee[0m > Finished syncing account_performance_report
2022-05-05 21:49:19 e[44msourcee[0m > SourceGoogleAds runtimes:
Syncing stream account_performance_report 0:00:17.196239
2022-05-05 21:49:19 e[44msourcee[0m > Finished syncing SourceGoogleAds
2022-05-05 21:49:19 e[32mINFOe[m i.a.w.DefaultReplicationWorker(run):162 - One of source or destination thread complete. Waiting on the other.
2022-05-05 21:49:19 e[43mdestinatione[0m > 2022-05-05 21:49:19 e[32mINFOe[m i.a.i.b.FailureTrackingAirbyteMessageConsumer(close):65 - Airbyte message consumer: succeeded.
2022-05-05 21:49:19 e[43mdestinatione[0m > 2022-05-05 21:49:19 e[32mINFOe[m i.a.i.d.b.BufferedStreamConsumer(close):170 - executing on success close procedure.
2022-05-05 21:49:19 e[43mdestinatione[0m > 2022-05-05 21:49:19 e[32mINFOe[m i.a.i.d.r.InMemoryRecordBufferingStrategy(lambda$flushAll$1):84 - Flushing google_ads_de_account_performance_report: 1188 records (9 MB)
2022-05-05 21:49:20 e[43mdestinatione[0m > 2022-05-05 21:49:20 e[32mINFOe[m i.a.i.d.j.SqlOperations(onDestinationCloseOperations):136 - No onDestinationCloseOperations required for this destination.
2022-05-05 21:49:20 e[43mdestinatione[0m > 2022-05-05 21:49:20 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):163 - Finalizing tables in destination started for 1 streams
2022-05-05 21:49:20 e[43mdestinatione[0m > 2022-05-05 21:49:20 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):168 - Finalizing stream google_ads_de_account_performance_report. schema airbyte, tmp table _airbyte_tmp_bej_google_ads_de_account_performance_report, final table _airbyte_raw_google_ads_de_account_performance_report
2022-05-05 21:49:20 e[43mdestinatione[0m > 2022-05-05 21:49:20 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):181 - Executing finalization of tables.
2022-05-05 21:49:20 e[43mdestinatione[0m > 2022-05-05 21:49:20 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):183 - Finalizing tables in destination completed.
2022-05-05 21:49:20 e[43mdestinatione[0m > 2022-05-05 21:49:20 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):186 - Cleaning tmp tables in destination started for 1 streams
2022-05-05 21:49:20 e[43mdestinatione[0m > 2022-05-05 21:49:20 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):190 - Cleaning tmp table in destination started for stream google_ads_de_account_performance_report. schema airbyte, tmp table name: _airbyte_tmp_bej_google_ads_de_account_performance_report
2022-05-05 21:49:20 e[43mdestinatione[0m > 2022-05-05 21:49:20 e[32mINFOe[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):195 - Cleaning tmp tables in destination completed.
2022-05-05 21:49:20 e[43mdestinatione[0m > 2022-05-05 21:49:20 e[32mINFOe[m i.a.i.b.IntegrationRunner(runInternal):169 - Completed integration: io.airbyte.integrations.base.ssh.SshWrappedDestination
2022-05-05 21:49:20 e[43mdestinatione[0m > 2022-05-05 21:49:20 e[32mINFOe[m i.a.i.d.p.PostgresDestination(main):91 - completed destination: class io.airbyte.integrations.destination.postgres.PostgresDestination
2022-05-05 21:49:20 e[32mINFOe[m i.a.w.DefaultReplicationWorker(run):164 - Source and destination threads complete.
2022-05-05 21:49:20 e[32mINFOe[m i.a.w.DefaultReplicationWorker(run):227 - sync summary: io.airbyte.config.ReplicationAttemptSummary@5292f6b9[status=completed,recordsSynced=1188,bytesSynced=2487837,startTime=1651787335235,endTime=1651787360410,totalStats=io.airbyte.config.SyncStats@18f2d89d[recordsEmitted=1188,bytesEmitted=2487837,stateMessagesEmitted=0,recordsCommitted=1188],streamStats=[io.airbyte.config.StreamSyncStats@7ce5e24f[streamName=google_ads_de_account_performance_report,stats=io.airbyte.config.SyncStats@59bcade5[recordsEmitted=1188,bytesEmitted=2487837,stateMessagesEmitted=<null>,recordsCommitted=1188]]]]
2022-05-05 21:49:20 e[32mINFOe[m i.a.w.DefaultReplicationWorker(run):249 - Source did not output any state messages
2022-05-05 21:49:20 e[33mWARNe[m i.a.w.DefaultReplicationWorker(run):260 - State capture: No state retained.
2022-05-05 21:49:20 e[32mINFOe[m i.a.w.t.TemporalAttemptExecution(get):131 - Stopping cancellation check scheduling...
2022-05-05 21:49:20 e[32mINFOe[m i.a.w.t.s.ReplicationActivityImpl(lambda$replicate$1):144 - sync summary: io.airbyte.config.StandardSyncOutput@1cb79f68[standardSyncSummary=io.airbyte.config.StandardSyncSummary@40ab0109[status=completed,recordsSynced=1188,bytesSynced=2487837,startTime=1651787335235,endTime=1651787360410,totalStats=io.airbyte.config.SyncStats@18f2d89d[recordsEmitted=1188,bytesEmitted=2487837,stateMessagesEmitted=0,recordsCommitted=1188],streamStats=[io.airbyte.config.StreamSyncStats@7ce5e24f[streamName=google_ads_de_account_performance_report,stats=io.airbyte.config.SyncStats@59bcade5[recordsEmitted=1188,bytesEmitted=2487837,stateMessagesEmitted=<null>,recordsCommitted=1188]]]],state=<null>,outputCatalog=io.airbyte.protocol.models.ConfiguredAirbyteCatalog@64e6574c[streams=[io.airbyte.protocol.models.ConfiguredAirbyteStream@113f68ce[stream=io.airbyte.protocol.models.AirbyteStream@3153f95e[name=google_ads_de_account_performance_report,jsonSchema={"type":"object","$schema":"http://json-schema.org/draft-07/schema#","properties":{"customer.id":{"type":["null","integer"]},"metrics.ctr":{"type":["null","number"]},"segments.date":{"type":["null","string"],"format":"date"},"segments.week":{"type":["null","string"]},"segments.year":{"type":["null","integer"]},"metrics.clicks":{"type":["null","integer"]},"segments.month":{"type":["null","string"]},"segments.device":{"type":["null","string"]},"customer.manager":{"type":["null","boolean"]},"segments.quarter":{"type":["null","string"]},"customer.time_zone":{"type":["null","string"]},"metrics.average_cpc":{"type":["null","number"]},"metrics.average_cpe":{"type":["null","number"]},"metrics.average_cpm":{"type":["null","number"]},"metrics.average_cpv":{"type":["null","number"]},"metrics.conversions":{"type":["null","number"]},"metrics.cost_micros":{"type":["null","integer"]},"metrics.engagements":{"type":["null","integer"]},"metrics.impressions":{"type":["null","integer"]},"metrics.video_views":{"type":["null","integer"]},"metrics.average_cost":{"type":["null","number"]},"metrics.interactions":{"type":["null","integer"]},"segments.day_of_week":{"type":["null","string"]},"customer.test_account":{"type":["null","boolean"]},"customer.currency_code":{"type":["null","string"]},"metrics.active_view_cpm":{"type":["null","number"]},"metrics.active_view_ctr":{"type":["null","number"]},"metrics.all_conversions":{"type":["null","number"]},"metrics.engagement_rate":{"type":["null","number"]},"metrics.video_view_rate":{"type":["null","number"]},"metrics.interaction_rate":{"type":["null","number"]},"segments.ad_network_type":{"type":["null","string"]},"customer.descriptive_name":{"type":["null","string"]},"metrics.conversions_value":{"type":["null","number"]},"metrics.cost_per_conversion":{"type":["null","number"]},"metrics.value_per_conversion":{"type":["null","number"]},"customer.auto_tagging_enabled":{"type":["null","boolean"]},"metrics.all_conversions_value":{"type":["null","number"]},"metrics.active_view_impressions":{"type":["null","integer"]},"metrics.active_view_viewability":{"type":["null","number"]},"metrics.interaction_event_types":{"type":["null","array"],"items":{"type":"string"}},"metrics.search_impression_share":{"type":["null","number"]},"metrics.content_impression_share":{"type":["null","number"]},"metrics.cost_per_all_conversions":{"type":["null","number"]},"metrics.cross_device_conversions":{"type":["null","number"]},"metrics.view_through_conversions":{"type":["null","integer"]},"metrics.active_view_measurability":{"type":["null","number"]},"metrics.value_per_all_conversions":{"type":["null","number"]},"metrics.search_rank_lost_impression_share":{"type":["null","number"]},"metrics.active_view_measurable_cost_micros":{"type":["null","integer"]},"metrics.active_view_measurable_impressions":{"type":["null","integer"]},"metrics.content_rank_lost_impression_share":{"type":["null","number"]},"metrics.conversions_from_interactions_rate":{"type":["null","number"]},"metrics.search_budget_lost_impression_share":{"type":["null","number"]},"metrics.search_exact_match_impression_share":{"type":["null","number"]},"metrics.content_budget_lost_impression_share":{"type":["null","number"]},"metrics.all_conversions_from_interactions_rate":{"type":["null","number"]}}},supportedSyncModes=[full_refresh, incremental],sourceDefinedCursor=true,defaultCursorField=[segments.date],sourceDefinedPrimaryKey=[],namespace=<null>,additionalProperties={}],syncMode=full_refresh,cursorField=[segments.date],destinationSyncMode=overwrite,primaryKey=[],additionalProperties={}]],additionalProperties={}],failures=[]]
2022-05-05 21:49:20 e[32mINFOe[m i.a.w.t.TemporalUtils(withBackgroundHeartbeat):234 - Stopping temporal heartbeating...
2022-05-05 21:49:20 e[33mWARNe[m i.a.s.p.JobNotifier(notifyJob):104 - Failed to successfully notify success: io.airbyte.config.Notification@3670552a[notificationType=slack,sendOnSuccess=false,sendOnFailure=true,slackConfiguration=io.airbyte.config.SlackNotificationConfiguration@4085188f[webhook=https://hooks.slack.com/services/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],additionalProperties={}]

Thanks a lot for any help!

P.S.: My setup is:
Airbyte Version: 0.35.27-alpha

Source: Google Ads (0.1.36)

Destination: Postgres (0.3.19)

Could it have to do with the airbyte version?

I can see from your logs of the full refresh - overwrite that 1188 records are read. Is it different from the number of records you expect?
By checking this connector a bit I found that there are some complex logics around date chunking that might cause this problem.
I’d like to request additional attempts from your side:

  • Could you please set the end_date to tomorrow or today?
  • Could you please tweak the conversation_window
  • Is this problem only occurring yesterday or on a specific date?
  • Could you share the logs of an incremental sync attempt?

I might eventually ask you to open a GitHub issue, but these elements will allow our developer to reproduce and quickly find the root cause.

Hello,

Thanks for your reply.

Strangely, the issue was gone this morning. So I suddenly had data for 2022-05-04 & 2022-05-05. But still no data for today, which is the expected behaviour.

  • changing end_date does not change the behaviour, I tried a couple of things
  • changing conversion_window also did not change anything
  • so the problem was only occurring yesterday (2022-05-05) for ‘yesterday’ (2022-05-04)
  • the source/stream does not allow for incremental loading

I am sorry I cannot reproduce the error / weird behaviour now. Is there anything else I can check/do? It still does not load data for the current day.

AFAIK if you download the data from the Google Ads dashboard the data from yesterday (from certain period of time during the current day) will not be available.

Very interesting. Do you have a reference for that, i could not find it in the docs.

It would also still not fully exactly explain the behaviour I observed, but it might be a contributing factor.

@oliver, as Marcos suggested there might be a delay between what’s available in the google ads dashboard and what you can access through their API.
Marcos Marx did you observe this using Google Ads or you found it in their docs?
@oliver, what is your current sync interval? I would suggest avoiding intra-days syncs and using daily syncs. If the data is not ready on Google Ads’ side, the incremental sync will consider no data exists for this period and the subsequent syncs will miss this eventually available data.

Thank you for that tip, I will switch the sync interval then. The problem has not occurred since last Thursday, so it might have just been a one-off glitch.

Since I can’t reproduce the issue right now I would consider the issue as closed.

Thank you very much for your help, much appreciated.