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

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

Un workflow ELT typique extrait régulièrement de 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 significatives. 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 allez planifier l'exécution de transformations périodiques des données par Dataform.

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

1. In the Cloud de Confiance console, on the project selector page, select or create a Cloud de Confiance 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 role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

   Go to project selector

2. Verify that billing is enabled for your Cloud de Confiance 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 afin d'extraire et de charger les données selon une planification.

Dans ce tutoriel, vous utilisez 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.

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

   Accéder à la page Transferts de données

2. Cliquez sur add Créer un transfert.

3. Dans section Type de source, accédez à la liste déroulante Source, puis sélectionnez Google Ads.

4. 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 le paramètre Intervalle d'actualisation, saisissez 5.

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.

6. Dans la section Transfer config name (Nom de la configuration de transfert), sous Display name (Nom à afficher), saisissez Marketing tutorial.

7. Dans la section Schedule options (Options de programmation) :

   • Pour Repeat frequency (Fréquence de répétition), sélectionnez Days (Jours).
   • Pour At (À), saisissez 08:00.

8. Cliquez sur Enregistrer.

Une fois la configuration enregistrée, le service de transfert de données BigQuery lance le transfert de données. En fonction des paramètres de la configuration de transfert, le transfert de données s'exécute une fois par jour à 8h 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 les 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.

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 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.

  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 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 de Google Ads, configurez Dataform pour transformer régulièrement vos données d'analyse marketing. Dataform vous permet de planifier des transformations régulières des données et de définir ces transformations 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.

1. Dans la Cloud de Confiance console, accédez à la page Dataform.

   Accéder à Dataform

2. Cliquez sur add Créer un dépôt.

3. 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 maintenant dans la liste des 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 de déployer vos modifications dans votre dépôt.

1. Dans la Cloud de Confiance console, accédez à la page Dataform.

   Accéder à Dataform

2. Cliquez sur marketing-tutorial-repository.

3. Cliquez sur add Créer un espace de travail de développement.

4. 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.

5. Cliquez sur Initialiser l'espace de travail.

L'espace de travail de développement marketing-tutorial-workspace apparaît maintenant 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 table Google Ads comme source de table

Connectez votre nouvelle table Google Ads transférée à Dataform en la déclarant comme source de données en procédant comme suit :

Créer un fichier SQLX pour la déclaration de la 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/ :

1. Dans la Cloud de Confiance console, accédez à la page Dataform.

   Accéder à la page Dataform

2. Sélectionnez marketing-tutorial-repository.

3. Sélectionnez marketing-tutorial-workspace.

4. Dans le volet Fichiers, à côté de definitions/, cliquez sur le menu Plus.

5. Cliquez sur Créer un fichier.

6. 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 le fichier 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 :

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

2. 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

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

Définir le fichier SQLX de transformation

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

2. 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 déployer vos modifications

Une fois que vous avez apporté vos modifications dans votre espace de travail de développement, vous pouvez les valider et les déployer dans votre dépôt en procédant comme suit :

1. Dans l'espace de travail marketing-tutorial-workspace, cliquez sur Valider 1 modification.
2. Dans le volet Nouveau commit, saisissez une description du commit dans le champ Ajouter un message de commit.
3. Cliquez sur Valider toutes les modifications.
4. Dans l'espace de travail marketing-tutorial-workspace, cliquez sur Déployer dans 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 votre transformation de 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

Une version de production dans Dataform garantit 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 :

1. Dans la Cloud de Confiance console, accédez à la page Dataform.

   Accéder à la page Dataform

2. Sélectionnez marketing-tutorial-repository.

3. Cliquez sur l'onglet Versions et planification.

4. Cliquez sur Créer une version de production.

5. 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 Git commitish, laissez la valeur par défaut main .
   3. Dans la section Fréquence de programmation, sélectionnez À la demande.

6. 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 montrent comment planifier des transformations quotidiennes à partir du fichier transformations :

1. Dans la Cloud de Confiance console, accédez à la page Dataform.

   Accéder à la page Dataform

2. Sélectionnez marketing-tutorial-repository.

3. Cliquez sur l'onglet Versions et planification.

4. Dans la section Configurations de workflow, cliquez sur Créer.

5. Dans le volet Créer une configuration de workflow , saisissez transformations dans le champ ID de configuration.

6. Dans le menu Configuration de version, sélectionnez transformations.

7. Sous Authentification, sélectionnez Exécuter avec les identifiants utilisateur.

8. 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)`.
   

9. Cliquez sur Sélection de tags.

10. Dans le champ Sélectionner les tags à exécuter, sélectionnez Tous les jours.

11. 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 dans cette démonstration soient facturées sur votre Cloud de Confiance compte, 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.

1. Dans la Cloud de Confiance console, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans le panneau Explorateur, développez votre projet et sélectionnez dataform.

3. Cliquez sur le menu more_vert Actions, puis sélectionnez Supprimer.

4. 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, mais vous pouvez le supprimer en procédant comme suit :

1. Dans la Cloud de Confiance console, accédez à la page Dataform.

   Accéder à Dataform

2. Cliquez sur quickstart-repository.

3. Cliquez sur l'onglet Versions et planification.

4. Dans la section Configurations de version, cliquez sur le more_vert Plus menu à côté de la production configuration, puis cliquez sur Supprimer.

5. Dans la section Configurations de workflow, cliquez sur le more_vert Plus menu à côté de la transformations configuration, puis cliquez sur Supprimer.

6. Dans l'onglet Espaces de travail de développement, cliquez sur le menu more_vert Plus de quickstart-workspace, puis sélectionnez Supprimer.

7. 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, mais vous pouvez le supprimer en procédant comme suit :

1. Dans la Cloud de Confiance console, accédez à la page Dataform.

   Accéder à Dataform

2. À côté de quickstart-repository, cliquez sur le menu more_vert Plus , puis sélectionnez Supprimer.

3. Dans la fenêtre Supprimer le dépôt, saisissez le nom du dépôt pour confirmer la suppression.

4. Pour confirmer l'opération, cliquez sur Supprimer.