CODEWITHSUNDEEP
google-bigquerygoogle-ads-api
jgeeda
jgeeda

Reputation: 33

BigQuery Google Ads Transfer - Duplicate data

We are using the Google Ads transfer in BigQuery to ingest our Google Ads data. One thing I have noticed when querying the results is that all of the metrics are exactly 156x of the values we would expect in the Google Ads UI (cost, clicks, etc.)

We have tested multiple transfers and each time we have this same issue. The transfer process seems pretty straight forward, but am I missing something? Has anyone else noticed a similar issue or have any ideas of what to look at to adjust in the data transfer?

Upvotes: 2

Views: 763

Answers (1)

Sander van den Oord
Sander van den Oord

Reputation: 12808

For which tables do you notice this behavior?

The dimension tables such as Customer, Campaign, AdGroup are exported every day and so are partitioned by day.
This could cause your duplication?! You only need the latest partition/day.

So this is for example how I get the latest account / customer data:

SELECT
    -- main reason I cast all the id's to string is because BI reporting tool will not see it as a metric but as a dimension field
    CAST(customer_id AS STRING) AS account_id,  --globally unique, see also: https://developers.google.com/google-ads/api/docs/concepts/api-structure

    customer_descriptive_name,
    customer_auto_tagging_enabled,
    customer_currency_code,
    customer_manager,
    customer_test_account,
    customer_time_zone,

    _DATA_DATE AS date,  --source table is paritioned on date
    _LATEST_DATE,
    CASE WHEN _DATA_DATE = _LATEST_DATE THEN TRUE ELSE FALSE END is_most_recent_record
FROM 
    `YOURPROJECTID.google_ads.ads_Customer_YOURID`
WHERE 
    _DATA_DATE = _LATEST_DATE

Upvotes: 1

Related Questions