Convertir gclid en nom campagne

Prérequis - Data Transfers

Pour commencer, il faut que les données Google Ads soient disponibles au sein d'une table Big Query.

Pour cela, il faut configurer un transfert de données via Big Query > Data Transfers.

🌟 Pour avoir un maximum de donnée à exploiter, je vous conseille d'activer ce transfert le plus tôt possible, même si vous n'en avez pas encore besoin.

Cette remarque est aussi vraie pour l'export de Google Analytics vers BigQuery.

Modélisation SQL

Parmi les nombreuses tables rendues disponibles et pour répondre à notre objectif, seulement deux tables sont utiles :

• table: p_ads_ClickStats_XXX
  • colonne: click_view_gclid
  • colonne: campaign_id
• table: ads_Campaign_XXX
  • colonne: campaign_id
  • colonne: campaign_name

Note : XXX est un identifiant propre à votre compte.

Pour améliorer la correspondance entre vos données et celles de Google Ads, vous pouvez normaliser le gclid sur l'ensemble de vos modèles en suivant la même méthode. De mon côté j'ai décidé de mettre ces valeurs en minuscles.

Il est à noter que la table ads_Campaign contient l'évolution des valeurs des campagnes, une même campagne est donc présente plusieurs fois, avec des noms potentiellements différents.
L'utilisation du mot clé distinct et l'usage d'une window function au sein de la requête SQL ci-dessous, permet d'éviter la redondances des résultats.

Vous pouvez adapter le modèle intermédiaire suivant sur vos projets [DBT ou SQLMesh]({{< ref "bootstrap-project-sqlmesh-dbt.md" >}}).

-- Modélisation SQL qui associe la valeur gclid au nom de campagne Google Ads.

with
    click_stats as (
        select 
            distinct click_view_gclid, 
            campaign_id
        from p_ads_ClickStats_XXX
    ),

    ads_campaign as (
        select 
            distinct campaign_id,
            last_value(campaign_name) over (
                partition by campaign_id
                order by _data_date desc
                range between unbounded preceding and unbounded following
            ) as campaign_name
        from ads_Campaign_XXX
    )

select 
    lower(click_view_gclid) as gclid, 
    campaign_name
from click_stats
left join ads_campaign using (campaign_id)