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!