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 | cpc | _cntr_sale_15 | |
| 2021-11-26 | 1688777252 | 328994634699 | 1 | 1 | 10 | cpc | cntr_sale_16 | |
| 2021-11-26 | 1688777252 | 328994634699 | 1 | 1 | 10 | 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!
