Блог

Я настроил поток передачи данных Google Ads из Google Ads в свой проект GoogleBigQuery. Он работает, данные текут, все в порядке. Но когда я решил создать запрос, который возвращает сумму потраченных денег в контексте определенной комбинации utm_marks (источник, среда, кампания), я столкнулся с проблемой «дублированных» данных.

Итак, запрос сначала переходит в таблицу Adstat и получает статистику каждого идентификатора creativeId (я полагаю, что идентификатор creativeId означает объявление) в каждой кампании. Затем он берет все utm_marks из шаблона adtrackingurl для каждого идентификатора креатива из каждой кампании. Наконец, он объединяет две таблицы в одну, и на выходе у меня есть полная информация о статистике для каждой utm_mark.

Запрос выглядит так:

 with   Adstat as (  select *   from `myproject.GoogleAds.AdStats_7394379271`  ),   Ad as (  select   CampaignId,   CreativeId,   REGEXP_EXTRACT(CreativeTrackingUrlTemplate, r"[?amp;]utm_source=([^amp;] )") as source,  REGEXP_EXTRACT(CreativeTrackingUrlTemplate, r"[?amp;]utm_medium=([^amp;] )") as medium,  REGEXP_EXTRACT(CreativeTrackingUrlTemplate, r"[?amp;]utm_campaign=([^amp;] )") 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  

выход:

И тут возникает проблема. Если идентификатор creativeId в течение своего срока службы имеет несколько utm_marks в табличке AdTrakingTemplate, все они перейдут в результат, и все они получат статистику из таблицы AdStats (вы можете увидеть в выводе: та же дата, тот же заголовок, та же статистика, но разные utms). Таким образом,у нас есть двойные (тройные, четырехкратные) показы, клики, потраченная сумма и т.д. Это довольно распространенный случай, потому что менеджеру проще изменить шаблон отслеживания, чем создавать новое объявление или кампанию в Google Ads.

И, к сожалению, я не знаю, как это выяснить, потому что нет способа определить, какие именно utm_marks были в createiveIdTrakingTemplate, когда выполнялись некоторые действия (показы, щелчок и т. Д.).

Кто-нибудь знает, как с этим бороться? Спасибо за помощь!