Cette page a été traduite par l'API Cloud Translation.

Créer un pipeline ELT pour les données d'analyse marketing

Ce tutoriel vous explique comment configurer un workflow ELT qui extrait, charge et transforme les données d'analyse marketing dans BigQuery.

Un workflow ELT typique extrait périodiquement les nouvelles données client de votre source de données et les charge dans BigQuery. Les données non structurées sont ensuite traitées pour générer des métriques pertinentes. Dans ce tutoriel, vous allez créer un workflow ELT en configurant un transfert de données d'analyse marketing à l'aide du service de transfert de données BigQuery. Ensuite, vous planifiez Dataform pour qu'il exécute des transformations périodiques sur les données.

Dans ce tutoriel, vous utilisez Google Ads comme source de données, mais vous pouvez utiliser n'importe quelle source de données compatible avec le service de transfert de données BigQuery.

Avant de commencer

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Rôles requis

Pour obtenir les autorisations nécessaires pour suivre ce tutoriel, demandez à votre administrateur de vous accorder les rôles IAM suivants sur le projet :

Administrateur BigQuery (roles/bigquery.admin)
Administrateur Dataform (roles/dataform.admin)

Pour en savoir plus sur l'attribution de rôles, consultez Gérer l'accès aux projets, aux dossiers et aux organisations.

Vous pouvez également obtenir les autorisations requises avec des rôles personnalisés ou d'autres rôles prédéfinis.

Planifier des transferts de données récurrents

Pour que BigQuery soit toujours à jour avec les dernières données marketing de votre source de données, configurez des transferts de données récurrents à l'aide du service de transfert de données BigQuery. Vous pourrez ainsi extraire et charger les données selon une programmation.

Dans ce tutoriel, vous allez utiliser Google Ads comme exemple de source de données. Pour obtenir la liste complète des sources de données compatibles avec le service de transfert de données BigQuery, consultez Sources de données compatibles.

Accédez à la page "Transferts de données" dans la console Google Cloud .

Accéder à la page Transferts de données
Cliquez sur Créer un transfert.
Dans section Type de source, accédez à la liste déroulante Source, puis sélectionnez Google Ads.
Dans la section Data source details (Détails de la source de données) :
1. Dans le champ ID client, saisissez votre numéro client Google Ads.
2. Sous Type de rapport, sélectionnez Standard. Le rapport standard inclut l'ensemble standard de rapports et de champs, comme indiqué dans Transformation de rapports Google Ads.
  - Pour Intervalle d'actualisation, saisissez 5.
Dans la section Paramètres de destination, pour le champ Ensemble de données, sélectionnez l'ensemble de données que vous avez créé pour stocker vos données.
Dans la section Nom de la configuration de transfert, sous Nom à afficher, saisissez Marketing tutorial.
Dans la section Schedule options (Options de programmation) :
- Pour Fréquence de répétition, sélectionnez Jours.
- Pour À, saisissez 08:00.
Cliquez sur Enregistrer.

Une fois la configuration enregistrée, le service de transfert de données BigQuery commence le transfert des données. En fonction des paramètres de la configuration du transfert, le transfert de données s'exécute une fois par jour à 8h00 UTC et extrait les données Google Ads des cinq derniers jours.

Vous pouvez surveiller les tâches de transfert en cours pour vérifier l'état de chaque transfert de données.

Interroger les données de la table

Lorsque les données sont transférées vers BigQuery, elles sont écrites dans des tables partitionnées avec date d'ingestion. Pour plus d'informations, consultez la page Présentation des tables partitionnées.

Si vous interrogez directement les tables au lieu d'utiliser les vues générées automatiquement, vous devez utiliser la pseudo-colonne _PARTITIONTIME dans votre requête. Pour en savoir plus, consultez la page Interroger des tables partitionnées.

Les sections suivantes présentent des exemples de requêtes que vous pouvez utiliser pour examiner vos données transférées.

Performances des campagnes

L'exemple de requête ci-dessous permet d'analyser les performances des campagnes Google Ads au cours des 30 derniers jours.

Console

SELECT
  c.customer_id,
  c.campaign_name,
  c.campaign_status,
  SUM(cs.metrics_impressions) AS Impressions,
  SUM(cs.metrics_interactions) AS Interactions,
  (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
FROM
  `DATASET.ads_Campaign_CUSTOMER_ID` c
LEFT JOIN
  `DATASET.ads_CampaignBasicStats_CUSTOMER_ID` cs
ON
  (c.campaign_id = cs.campaign_id
  AND cs._DATA_DATE BETWEEN
  DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
WHERE
  c._DATA_DATE = c._LATEST_DATE
GROUP BY
  1, 2, 3
ORDER BY
  Impressions DESC

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.customer_id,
    c.campaign_name,
    c.campaign_status,
    SUM(cs.metrics_impressions) AS Impressions,
    SUM(cs.metrics_interactions) AS Interactions,
    (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
  FROM
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  LEFT JOIN
    `DATASET.ads_CampaignBasicStats_CUSTOMER_ID` cs
  ON
    (c.campaign_id = cs.campaign_id
    AND cs._DATA_DATE BETWEEN
    DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
  WHERE
    c._DATA_DATE = c._LATEST_DATE
  GROUP BY
    1, 2, 3
  ORDER BY
    Impressions DESC'

Remplacez les éléments suivants :

DATASET : nom de l'ensemble de données que vous avez créé pour stocker la table transférée
CUSTOMER_ID : votre numéro client Google Ads.

Nombre de mots clés

L'exemple de requête suivant analyse les mots clés par campagne, par groupe d'annonces et par état de mots clé. Cette requête utilise la fonction KeywordMatchType. Grâce aux différents types de correspondance des mots clés, vous pouvez mieux contrôler quelles recherches déclencheront la diffusion de votre annonce. Pour en savoir plus sur les options de ciblage par mots-clés, consultez la page À propos des options de correspondance des mots-clés.

Console

  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.ads_Keyword_CUSTOMER_ID` k
    JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 2, 3, 4

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.ads_Keyword_CUSTOMER_ID` k
  JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 2, 3, 4'

Remplacez les éléments suivants :

DATASET : nom de l'ensemble de données que vous avez créé pour stocker la table transférée
CUSTOMER_ID : votre numéro client Google Ads.

Créer un dépôt Dataform

Après avoir créé la configuration de transfert de données pour transférer les dernières données depuis Google Ads, configurez Dataform pour transformer régulièrement vos données d'analyse marketing. Dataform vous permet de planifier des transformations de données régulières et de les définir avec SQL tout en collaborant avec d'autres analystes de données.

Créez un dépôt Dataform pour stocker les requêtes SQLX qui composent votre code de transformation.

Dans la console Google Cloud , accédez à la page Dataform.

Accéder à Dataform
Cliquez sur Créer un dépôt.
Sur la page CRÉER UN DÉPÔT, procédez comme suit :
1. Dans le champ ID du dépôt, saisissez marketing-tutorial-repository.
2. Dans la liste Région, sélectionnez une région.
3. Cliquez sur Créer.

Le dépôt marketing-tutorial-repository apparaît désormais dans la liste de vos dépôts Dataform.

Pour en savoir plus sur les dépôts Dataform, consultez À propos des dépôts Dataform.

Créer et initialiser un espace de travail de développement Dataform

Créez un espace de travail de développement Dataform pour pouvoir travailler sur le code de transformation dans votre dépôt avant de valider et d'envoyer vos modifications.

Dans la console Google Cloud , accédez à la page Dataform.

Accéder à Dataform
Cliquez sur marketing-tutorial-repository.
Cliquez sur Créer un espace de travail de développement.
Dans la fenêtre Créer un espace de travail de développement, procédez comme suit :
1. Dans le champ ID de l'espace de travail, saisissez marketing-tutorial-workspace.
2. Cliquez sur Créer.
La page de l'espace de travail de développement s'affiche.
Cliquez sur Initialiser l'espace de travail.

L'espace de travail de développement marketing-tutorial-workspace apparaît désormais dans votre dépôt marketing-tutorial-repository sous l'onglet Espaces de travail de développement, ainsi que deux exemples de fichiers dans le répertoire definitions appelés *first_view.sqlx et *second_view.sqlx.

Pour en savoir plus sur les espaces de travail de développement Dataform, consultez Présentation des espaces de travail de développement.

Déclarer votre tableau Google Ads comme source de tableau

Connectez votre table Google Ads nouvellement transférée à Dataform en la déclarant comme source de données. Pour ce faire, procédez comme suit :

Créer un fichier SQLX pour déclarer une source de données

Dans Dataform, vous déclarez une destination de source de données en créant un fichier SQLX dans le répertoire definitions/ :

Dans la console Google Cloud , accédez à la page Dataform.

Accéder à la page Dataform
Sélectionnez marketing-tutorial-repository.
Sélectionnez marketing-tutorial-workspace.
Dans le volet Fichiers, à côté de definitions/, cliquez sur le menu Plus.
Cliquez sur Créer un fichier.
Dans le volet Créer un fichier, procédez comme suit :
1. Dans le champ Ajouter un chemin d'accès au fichier, après definitions/, saisissez le nom definitions/googleads-declaration.sqlx.
2. Cliquez sur Créer un fichier.

Déclarer une source de données

Modifiez definitions/googleads-declaration.sqlx pour déclarer une table Google Ads transférée comme source de données. Cet exemple déclare la table ads_Campaign comme source de données :

Dans votre espace de travail de développement, dans le volet Fichiers, cliquez sur votre fichier SQLX pour la déclaration de source de données.

Dans le fichier, saisissez l'extrait de code suivant :

    config {
        type: "declaration",
        database: "PROJECT_ID",
        schema: "DATASET",
        name: "ads_Campaign_CUSTOMER_ID",
    }

Définir votre transformation

Définissez vos transformations de données en créant un fichier SQLX dans le répertoire definitions/. Dans ce tutoriel, vous allez créer une transformation quotidienne qui agrège des métriques telles que les clics, les impressions, les coûts et les conversions à l'aide d'un fichier nommé daily_performance.sqlx.

Créer le fichier SQLX de transformation

Dans le volet Fichiers, à côté de definitions/, cliquez sur le menu Plus, puis sélectionnez Créer un fichier.
Dans le champ Ajouter un chemin d'accès au fichier, saisissez definitions/daily_performance.sqlx.
Cliquez sur Créer un fichier.

Définir le fichier SQLX de transformation

Dans le volet Fichiers, développez le répertoire definitions/.

Sélectionnez daily_performance.sqlx, puis saisissez la requête suivante :

    config {
        type: "table",
        schema: "reporting",
        tags: ["daily", "google_ads"]
    }
    SELECT
        date,
        campaign_id,
        campaign_name,
    SUM(clicks) AS total_clicks
    FROM
        `ads_Campaign_CUSTOMER_ID`
    GROUP BY
        date,
        campaign_id,
        campaign_name
        ORDER BY
        date DESC

Valider et transférer vos modifications

Une fois que vous avez apporté des modifications à votre espace de travail de développement, vous pouvez les appliquer et les transférer à votre dépôt en procédant comme suit :

Dans l'espace de travail marketing-tutorial-workspace, cliquez sur Valider 1 modification.
Dans le volet Nouveau commit, saisissez une description du commit dans le champ Ajouter un message de commit.
Cliquez sur Valider toutes les modifications.
Dans l'espace de travail marketing-tutorial-workspace, cliquez sur Envoyer à la branche par défaut.

Une fois vos modifications déployées dans votre dépôt, le message L'espace de travail est à jour s'affiche.

Planifier la transformation de vos données

Une fois que vous avez défini votre fichier de transformation de données, planifiez les transformations de données.

Créer une version de production

Dans Dataform, une version de production permet de s'assurer que votre environnement est constamment mis à jour avec les résultats de vos transformations de données. Les étapes suivantes vous montrent comment spécifier la branche main du dépôt marketing-tutorial-repository pour stocker vos transformations de données :

Dans la console Google Cloud , accédez à la page Dataform.

Accéder à la page Dataform
Sélectionnez marketing-tutorial-repository.
Cliquez sur l'onglet Versions et programmation.
Cliquez sur Créer une version de production.
Dans le volet Créer une configuration de version, configurez les paramètres suivants :
1. Dans le champ ID de version, saisissez transformations.
2. Dans le champ Commitish Git, conservez la valeur par défaut main .
3. Dans la section Fréquence de programmation, sélectionnez À la demande.
Cliquez sur Créer.

Créer une configuration de workflow

Une fois que vous avez créé une version de production, vous pouvez créer une configuration de workflow qui exécute vos transformations de données selon une planification spécifiée dans votre dépôt. Les étapes suivantes vous indiquent comment planifier des transformations quotidiennes à partir du fichier transformations :

Dans la console Google Cloud , accédez à la page Dataform.

Accéder à la page Dataform
Sélectionnez marketing-tutorial-repository.
Cliquez sur l'onglet Versions et programmation.
Dans la section Configurations de workflow, cliquez sur Créer.
Dans le volet Create workflow configuration (Créer une configuration de workflow), saisissez transformations dans le champ Configuration ID (ID de configuration).
Dans le menu Configuration de version, sélectionnez transformations.
Sous Authentification, sélectionnez Exécuter avec les identifiants utilisateur.

Dans la section Fréquence de programmation, procédez comme suit :

1. Select **Repeat**.
1. For **Repeats**, select `Daily`.
1. For **At time**, enter `10:00 AM`.
1. For **Timezone**, select `Coordinated Universal Time (UTC)`.

Cliquez sur Sélection de tags.
Dans le champ Sélectionner les tags à exécuter, sélectionnez Tous les jours.
Cliquez sur Créer.

La configuration de workflow que vous avez créée exécute l'intégralité du résultat de compilation le plus récent créé par la configuration de version transformations.

Effectuer un nettoyage

Pour éviter que les ressources utilisées sur cette page soient facturées sur votre compte Google Cloud , procédez comme suit :

Supprimer l'ensemble de données créé dans BigQuery

Pour éviter que des frais ne vous soient facturés pour les éléments BigQuery, supprimez l'ensemble de données dataform.

Dans la console Google Cloud , accédez à la page BigQuery.

Accéder à BigQuery
Dans le panneau Explorateur, développez votre projet et sélectionnez dataform.
Cliquez sur le menu Actions, puis sélectionnez Supprimer.
Dans la boîte de dialogue Supprimer l'ensemble de données, saisissez delete dans le champ, puis cliquez sur Supprimer.

Supprimer l'espace de travail de développement et les configurations Dataform

La création d'un espace de travail de développement Dataform n'entraîne aucuns frais. Toutefois, pour le supprimer, procédez comme suit :

Dans la console Google Cloud , accédez à la page Dataform.

Accéder à Dataform
Cliquez sur quickstart-repository.
Cliquez sur l'onglet Publication et programmation.
Dans la section Configurations des versions, cliquez sur le menu Plus à côté de la configuration production, puis sur Supprimer.
Dans la section Configurations de workflow, cliquez sur le menu Plus à côté de la configuration transformations, puis sur Supprimer.
Dans l'onglet Espaces de travail de développement, cliquez sur le menu Plus de quickstart-workspace, puis sélectionnez Supprimer.
Pour confirmer l'opération, cliquez sur Supprimer.

Supprimer le dépôt Dataform

La création d'un dépôt Dataform n'entraîne aucuns frais. Pour le supprimer, procédez comme suit :

Dans la console Google Cloud , accédez à la page Dataform.

Accéder à Dataform
À côté de quickstart-repository, cliquez sur le menu Plus, puis sélectionnez Supprimer.
Dans la fenêtre Supprimer le dépôt, saisissez le nom du dépôt pour confirmer la suppression.
Pour confirmer l'opération, cliquez sur Supprimer.