https://linen.dev logo
Join Slack
Powered by
Hi all! as the google ads and bing ads source conn...
# replication-troubleshooting
l
Hi all! as the google ads and bing ads source connectors have predetermined cursor fields for the incremental + history deduped syncs, I think it doesn´t update the field. For example, keyword with id x cost may change retroactively, but as the cursor is the timestamp/ segments.date we won´t detect the change. How do people handle this problem? I thought of creating a process that once a month I full refresh those tables and rewrite existing rows and add the new ones, but I don´t see the possibility to set the source to refresh the last 30 days, as an expression. Any ideas?
e
Hi. We use google ads and bing connectors (and created our own custom ones for our needs) it does update the data and dedup it
What exactly is the issue you have?
l
Hi, I use the standard connectors and the cursors are the segments.date for google ads and timeperiod for bing ads. If a row is updated in google ads/ bing ads, it won´t be updated by the source as the segments.date /timeperiod didn't change for that row. So my question is which fields do you use as cursor for those connectors for them to be able to capture an update of existing fields
e
The dedup mechanism is insert overwrite which mean that if the row exists it will update that rows data based on the composite keys and if it is a new row it will insert it this means that regardless if the data changed or not it will update to the newest data from the API. the standard schemas available in the connectors already have those correct composite keys so no worries there. if you use custom query in gg ads then you need to specify it correctly and this is mainly dependent on the data
l
First, thanks for answering! But you are saying it will update although the key id (already exists) and the cursor (segments.date) didn't change for that row?
e
date is a primary key so if you have 2022-11-11 and 2022-11-12 and each day has it`s own adgroup, campaigns and ad ids etc... it will updated those rows based on the date by default
for example if you have a conversion windows of 90 days - then you need to pull every day 90 days back so that you can update those dates retroactively
the mechanism will know based on the date and other primary keys how to update
l
but data of a past segments.date that google ads updates now, for that past date wont be updated. You are saying that you pull every 90 days the data back, which was what I thought needs to be done, as I don´t see how the connector UPDATES data (the primary keys nor the cursor changed for that past row that google ads updated the cost).
e
are you asking how does the date in the source is dynamically updated ?>
l
I don´t see how it updates in the standard google ads connector.. I think it doesn´t
But I´m not an expert
e
it does not update in the source dynamically - you need to use airbyte api for that
and update the connection
POST /v1/connections/update you need to use that endpoint and update the connection in order to make the source dynamic
i.e. if you want every day to pull the LATESTS 90 days of data
l
If I had in the connector a cursor of updated_at, It would update the rows if the update_at changes.. But as the cursor is fixed I thought of the option you mentioned, to pull back data.
So I guess you pull back data because the incremental+ dedupped history doesn´t update the rows?
e
it is not fixed- when you create a source you specify the date from - when you update the connection you are updating that date from
you can also update directly the source via airbyte api though this is not the best option if you have CI|CD setup since this could build the image again
For example:
Copy code
import requests
import json

url = "<http://localhost:8000/api/v1/sources/update>"

payload = json.dumps({
  "sourceId": "4becec06-90cd-48fd-b49c-8632f94b8ef4",
  "connectionConfiguration": {
    "start_date": "2022-09-01",
    "credentials": {
      "developer_token": "111111111111111111",
      "client_id": "1111111111111111111111",
      "client_secret": "111111111111111111111111",
      "refresh_token": "111111111111111111111111111"
    },
    "customer_id": "111111111111111111",
    "custom_queries": [
      {
        "query": "SELECT ad_group_ad.ad.id, ad_group_ad.ad.name, segments.device, ad_group.id, ad_group.name, campaign.id, campaign.name, customer.id, customer.descriptive_name, ad_group_ad.labels, ad_group.labels, ad_group_ad.ad.resource_name, customer.currency_code, metrics.clicks, metrics.cost_micros, metrics.impressions FROM ad_group_ad",
        "table_name": "gg_ad_data_1"
      },
      {
        "query": "SELECT ad_group_ad.ad.id, ad_group_ad.ad.name, segments.device, ad_group.id, ad_group.name, campaign.id, campaign.name, customer.id, customer.descriptive_name, ad_group_ad.labels, ad_group.labels, ad_group_ad.ad.resource_name, customer.currency_code, segments.conversion_action_name, metrics.all_conversions_value, metrics.all_conversions FROM ad_group_ad",
        "table_name": "gg_ad_data_2"
      },
      {
        "query": "SELECT ad_group.id, ad_group.name, segments.device, metrics.cost_micros, metrics.impressions, metrics.clicks, campaign.id, campaign.name, customer.id, customer.descriptive_name, customer.currency_code, geographic_view.location_type, geographic_view.country_criterion_id, segments.geo_target_state FROM geographic_view",
        "table_name": "gg_geo_data_1"
      },
      {
        "query": "SELECT ad_group.id, ad_group.name, segments.device, campaign.id, campaign.name, customer.id, customer.descriptive_name, customer.currency_code, geographic_view.location_type, geographic_view.country_criterion_id, metrics.all_conversions, metrics.all_conversions_value, segments.conversion_action_name, segments.geo_target_state FROM geographic_view",
        "table_name": "gg_geo_data_2"
      }
    ],
    "login_customer_id": "111111111111",
    "conversion_window_days": 14
  },
  "name": "pearl_gg_ad_data"
})
headers = {
  'Content-Type': 'application/json'
}

response = requests.request("POST", url, headers=headers, data=payload, timeout=360)

print(response.text)
this will update the source date from
l
I am asking 2 things. Are the google ads and Bing ads source connectors UPDATING existing rows? they have pre determined cursor and primary keys as in the picture. I think they don't update the data, just adding new rows.
e
if you use dedup - yes they are updating
l
So may I ask, if they are updating, why do you need to pull data back? I wanted it because I don´t see how it updates..
e
you would want to pull data based on the conversion window so it changes retroactively
l
Don´t the deduuped change it retroactively?
e
no those are 2 different things
you specify the API from when to pull the data and specify in airbyte in the source that you want dedup+history so that it will update the destination TABLE
hope this makes sense
l
For now yes, but google ads updates costs retroactively
e
correct
since it is based on impressions which are based on views which are based on the conversion windows
n
@laila ribke let me know if you need more help on this!
l
Hi @Nataly Merezhuk (Airbyte), maybe Im focusing the issue incorrectly, but I simulated it and I think the google ads and Bing ads connectors dony perform the incremental+ dedupped history when an existing row needs to be updated because one field of it was updated. The cursor for google ads, for example is segments.date. as this specific field didnt change (another field in that row was updated, such as the costs) the row wont update. Am I wrong?
Also I need badly help with two pull requests that are stuck #20175 and #20300
u
I don't think you're wrong! I'm looking into this but we'll probably need to escalate this to GitHub. I see the PRs - looks like you need to wait for a review. Our team isn't big and with the holidays coming up it's been super busy! Thanks for your patience, I hope you understand!
3 Views
Open in Slack
PreviousNext
Powered by