I have configured a Google Ads DataTransfer stream from Google Ads to my GoogleBigQuery project. It runs, data flows, everything is fine. But when I decided to build a query that return an amount of money spend in the context of distinct combination of utm_marks (source, medium, campaign) I've faced a trouble with 'doublicated' data.

So, the query firstly goes to Adstat Table and takes the stats of every creativeId (I suppose creativeId means Ad) in every campaignId. Then it takes an every utm_marks from AdTrackingUrlTemplate of every creativeId from every campaign. Finally it merges two tables in one and in the output I have a full info about stats for every utm_mark.

Query looks like this:

 with   Adstat as (             select *              from `myproject.GoogleAds.AdStats_7394379271`         ),  Ad as (         select              CampaignId,              CreativeId,              REGEXP_EXTRACT(CreativeTrackingUrlTemplate, r"[?&]utm_source=([^&]+)") as source,             REGEXP_EXTRACT(CreativeTrackingUrlTemplate, r"[?&]utm_medium=([^&]+)") as medium,             REGEXP_EXTRACT(CreativeTrackingUrlTemplate, r"[?&]utm_campaign=([^&]+)") as campaign         from              `myproject.GoogleAds.p_Ad_7394379271`         where              CreativeTrackingUrlTemplate is not null          and              CreativeTrackingUrlTemplate!="{lpurl}"         group by              CampaignId, CreativeId, source, medium, campaign        ) select     date, CampaignId, CreativeId, impressions,      Clicks, Cost, Cost * 1.2/1000000 as adCost, source, medium, campaign from      Adstat  left join       Ad using (CampaignId, CreativeId) where      date = '2021-11-26' and      CampaignId = 1688777252 and      CreativeId = 328994634699

output:

date CampaignId CreativeId impressions Clicks adCost source medium campaign
2021-11-26 1688777252 328994634699 1 1 10 google cpc _cntr_sale_15
2021-11-26 1688777252 328994634699 1 1 10 google cpc cntr_sale_16
2021-11-26 1688777252 328994634699 1 1 10 google cpc cntr_sale_17

And there is a trouble. If a creativeId during its lifetime has a several utm_marks in AdTrakingTemplate, all of them will go to result and all of them will receive a stats from AdStats Table (you can see at in output: same date, same CreativeAd, same stats, but different utms). So we have a double (triple,quadriple) impressions, clicks, amount spent etc. It's a pretty common case, because it's easier from manager to change a tracking template, than create a new Ad or Campaign in Google Ads.

And, unfortunatly, I don`t know, how to figure it out, cause there no way to determ which exactly utm_marks were in createiveIdTrakingTemplate when some stat actcions (impressions, click, etc) were performed.

Does anyone know, how to deal with it? Thanks for help!

Tag:google-ads-api, sql, google-bigquery, google-cloud-data-transfer

2 comments.

  1. Zweitze

    You assume CreativeId is unique. That is not the case. CreativeId is only unique within the ad group - in other words, a campaign can have multiple ad groups, and these groups can have an ad with the same CreativeId.

    To deal with this, make sure you mention the ad group in the result, that should help you understand the result.

    For information on uniqueness of objects in Google Ads: https://developers.google.com/adwords/api/docs/guides/objects-methods#object_id_uniqueness

    Reply
    1. Simas Joneliunas

      While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review

      Reply

Add a new comment.