Crea una pipeline ELT per i dati di analisi di marketing

Questo tutorial mostra come configurare un flusso di lavoro ELT che estrae, carica e trasforma i dati di analisi di marketing in BigQuery.

Un tipico workflow ELT estrae periodicamente i nuovi dati dei clienti dall'origine dati e li carica in BigQuery. I dati non strutturati vengono quindi elaborati in metriche significative. In questo tutorial, creerai un flusso di lavoro ELT configurando un trasferimento di dati di analisi di marketing utilizzando BigQuery Data Transfer Service. Poi, pianificherai l'esecuzione periodica delle trasformazioni dei dati in Dataform.

In questo tutorial, utilizzerai Google Ads come origine dati, ma puoi utilizzare una qualsiasi delle origini dati supportate da BigQuery Data Transfer Service.

Prima di iniziare

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.

Ruoli obbligatori

Per ottenere le autorizzazioni necessarie per completare questo tutorial, chiedi all'amministratore di concederti i seguenti ruoli IAM nel progetto:

• Amministratore BigQuery (roles/bigquery.admin)
• Amministratore Dataform (roles/dataform.admin)

Per saperne di più sulla concessione dei ruoli, consulta Gestisci l'accesso a progetti, cartelle e organizzazioni.

Potresti anche riuscire a ottenere le autorizzazioni richieste tramite i ruoli personalizzati o altri ruoli predefiniti.

Pianifica trasferimenti di dati ricorrenti

Per mantenere BigQuery aggiornato con i dati di marketing più recenti dell'origine dati, configura trasferimenti di dati ricorrenti utilizzando BigQuery Data Transfer Service per estrarre e caricare i dati in base a una pianificazione.

In questo tutorial, utilizzerai Google Ads come origine dati di esempio. Per un elenco completo delle origini dati supportate da BigQuery Data Transfer Service, consulta Origini dati supportate.

1. Vai alla pagina Trasferimenti di dati nella Cloud de Confiance console.

   Vai a Trasferimenti di dati

2. Fai clic su add Crea trasferimento.

3. Nella sezione Tipo di origine , scegli Google Ads per Origine.

4. Nella sezione Dettagli origine dati:

   1. In ID cliente, inserisci il tuo ID cliente Google Ads.
   2. In Tipo di report, seleziona Standard. Il report standard include il set standard di report e campi descritto in Trasformazione dei report di Google Ads.
      • In Finestra di aggiornamento, inserisci 5.

5. Nella sezione Impostazioni destinazione, per Set di dati, seleziona il set di dati che hai creato per archiviare i dati.

6. Nella sezione Nome configurazione di trasferimento , inserisci Marketing tutorial in Nome visualizzato.

7. Nella sezione Opzioni di pianificazione:

   • In Frequenza di ripetizione, seleziona Giorni.
   • In Alle, inserisci 08:00.

8. Fai clic su Salva.

Dopo aver salvato la configurazione, BigQuery Data Transfer Service avvia il trasferimento dei dati. In base alle impostazioni della configurazione di trasferimento, il trasferimento dei dati viene eseguito una volta al giorno alle 8:00 UTC ed estrae i dati di Google Ads degli ultimi cinque giorni.

Puoi monitorare i job di trasferimento in corso per controllare lo stato di ogni trasferimento di dati.

Esegui query sui dati della tabella

Quando vengono trasferiti a BigQuery, i tuoi dati vengono scritti in tabelle partizionate per data di importazione. Per ulteriori informazioni, consulta Introduzione alle tabelle partizionate.

Se esegui query direttamente sulle tabelle anziché utilizzare le visualizzazioni generate automaticamente, devi utilizzare la pseudocolonna _PARTITIONTIME nella query. Per ulteriori informazioni, consulta Esecuzione di query sulle tabelle partizionate.

Le sezioni riportate di seguito mostrano query di esempio che puoi utilizzare per esaminare i dati trasferiti.

Rendimento della campagna

La seguente query di esempio analizza il rendimento delle campagne Google Ads degli ultimi 30 giorni.

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'

Sostituisci quanto segue:

• DATASET: il nome del set di dati che hai creato per archiviare la tabella trasferita
• CUSTOMER_ID: il tuo ID cliente Google Ads.

Conteggio delle parole chiave

La seguente query di esempio analizza le parole chiave in base a campagna, gruppo di annunci e stato della parola chiave. Questa query utilizza la funzione KeywordMatchType. I tipi di corrispondenza delle parole chiave consentono di controllare le ricerche che possono attivare la pubblicazione dell'annuncio. Per saperne di più sulle opzioni di corrispondenza delle parole chiave, consulta Informazioni sulle opzioni di corrispondenza delle parole chiave.

  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'

Sostituisci quanto segue:

• DATASET: il nome del set di dati che hai creato per archiviare la tabella trasferita
• CUSTOMER_ID: il tuo ID cliente Google Ads.

Crea un repository Dataform

Dopo aver creato la configurazione di trasferimento dei dati per trasferire i dati più recenti da Google Ads, configura Dataform per trasformare regolarmente i dati di analisi di marketing. Dataform ti consente di pianificare trasformazioni di dati regolari e di definirle con SQL mentre collabori con altri analisti di dati.

Crea un repository Dataform per archiviare le query SQLX che compongono il codice di trasformazione.

1. Nella Cloud de Confiance console, vai alla pagina Dataform.

   Vai a Dataform

2. Fai clic su add Crea repository.

3. Nella pagina Crea repository:

   1. Nel campo ID repository, inserisci marketing-tutorial-repository.
   2. Nell'elenco Regione, seleziona una regione.
   3. Fai clic su Crea.

Il repository marketing-tutorial-repository viene visualizzato nell'elenco dei repository Dataform.

Per saperne di più sui repository Dataform, consulta Informazioni sui repository Dataform.

Crea e inizializza un'area di lavoro di sviluppo Dataform

Crea un'area di lavoro di sviluppo Dataform in modo da poter lavorare sul codice di trasformazione all'interno del repository prima di eseguire il commit e il push delle modifiche nel repository.

1. Nella Cloud de Confiance console, vai alla pagina Dataform.

   Vai a Dataform

2. Fai clic su marketing-tutorial-repository.

3. Fai clic su add Crea area di lavoro di sviluppo.

4. Nella finestra Crea area di lavoro di sviluppo:

   1. Nel campo ID area di lavoro, inserisci marketing-tutorial-workspace.
   2. Fai clic su Crea.

   Viene visualizzata la pagina dell'area di lavoro di sviluppo.

5. Fai clic su Inizializza area di lavoro.

L'area di lavoro di sviluppo marketing-tutorial-workspace viene visualizzata nel repository marketing-tutorial-repository nella scheda Aree di lavoro di sviluppo, insieme a due file di esempio nella directory definitions denominati *first_view.sqlx e *second_view.sqlx.

Per saperne di più sulle aree di lavoro di sviluppo Dataform, consulta Panoramica delle aree di lavoro di sviluppo.

Dichiara la tabella Google Ads come origine della tabella

Collega la tabella Google Ads appena trasferita a Dataform dichiarandola come origine dati seguendo questi passaggi:

Crea un file SQLX per la dichiarazione dell'origine dati

In Dataform, dichiari una destinazione dell'origine dati creando un file SQLX nella directory definitions/:

1. Nella Cloud de Confiance console, vai alla pagina Dataform.

   Vai alla pagina Dataform

2. Seleziona marketing-tutorial-repository.

3. Seleziona marketing-tutorial-workspace.

4. Nel riquadro File, accanto a definitions/, fai clic sul menu Altro.

5. Fai clic su Crea file.

6. Nel riquadro Crea nuovo file:

   1. Nel campo Aggiungi un percorso del file, dopo definitions/, inserisci il nome definitions/googleads-declaration.sqlx.
   2. Fai clic su Crea file.

Dichiara un'origine dati

Modifica il file definitions/googleads-declaration.sqlx per dichiarare una tabella Google Ads trasferita come origine dati. Questo esempio dichiara la tabella ads_Campaign come origine dati:

1. Nell'area di lavoro di sviluppo, nel riquadro File, fai clic sul file SQLX per la dichiarazione dell'origine dati.

2. Nel file, inserisci il seguente snippet di codice:

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

Definisci la trasformazione

Definisci le trasformazioni dei dati creando un file SQLX nella directory definitions/. In questo tutorial, creerai una trasformazione giornaliera che aggrega metriche come clic, impressioni, costi e conversioni utilizzando un file denominato daily_performance.sqlx.

Crea il file SQLX di trasformazione

1. Nel riquadro File, accanto a definitions/, fai clic sul menu more_vert Altro e poi seleziona Crea file.
2. Nel campo Aggiungi un percorso del file, inserisci definitions/daily_performance.sqlx.
3. Fai clic su Crea file.

Definisci il file SQLX di trasformazione

1. Nel riquadro File, espandi la directory definitions/.

2. Seleziona daily_performance.sqlx, quindi inserisci la seguente query:

       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

Esegui il commit e il push delle modifiche

Dopo aver apportato le modifiche nell'area di lavoro di sviluppo, puoi eseguire il commit e il push di queste modifiche nel repository seguendo questi passaggi:

1. Nell'area di lavoro marketing-tutorial-workspace, fai clic su Esegui il commit di 1 modifica.
2. Nel riquadro Nuovo commit, inserisci una descrizione del commit nel campo Aggiungi un messaggio di commit.
3. Fai clic su Esegui il commit di tutte le modifiche.
4. Nell'area di lavoro marketing-tutorial-workspace, fai clic su Esegui il push nel ramo predefinito.

Una volta eseguito il push delle modifiche nel repository, viene visualizzato il messaggio L'area di lavoro è aggiornata.

Pianifica la trasformazione dei dati

Dopo aver definito il file di trasformazione dei dati, pianifica le trasformazioni dei dati.

Crea una release di produzione

Una release di produzione in Dataform garantisce che l'ambiente venga aggiornato in modo coerente con i risultati delle trasformazioni dei dati. I seguenti passaggi mostrano come specificare il branch main del repository marketing-tutorial-repository per archiviare le trasformazioni dei dati:

1. Nella Cloud de Confiance console, vai alla pagina Dataform.

   Vai alla pagina Dataform

2. Seleziona marketing-tutorial-repository.

3. Fai clic sulla scheda Release e pianificazione.

4. Fai clic su Crea release di produzione.

5. Nel riquadro Crea configurazione della release, configura le seguenti impostazioni:

   1. Nel campo ID release, inserisci transformations.
   2. Nel campo Commit Git, lascia il valore predefinito main .
   3. Nella sezione Frequenza di pianificazione, seleziona On demand.

6. Fai clic su Crea.

Crea una configurazione di flusso di lavoro

Dopo aver creato una release di produzione, puoi creare una configurazione del flusso di lavoro che esegue le trasformazioni dei dati in base a una pianificazione specificata nel repository. I seguenti passaggi mostrano come pianificare le trasformazioni giornaliere dal file transformations:

1. Nella Cloud de Confiance console, vai alla pagina Dataform.

   Vai alla pagina Dataform

2. Seleziona marketing-tutorial-repository.

3. Fai clic sulla scheda Release e pianificazione.

4. Nella sezione Configurazioni dei flussi di lavoro, fai clic su Crea.

5. Nel riquadro Crea configurazione del flusso di lavoro , inserisci transformations nel campo ID configurazione.

6. Nel menu Configurazione della release, seleziona transformations.

7. In Autenticazione, seleziona Esegui con le credenziali utente.

8. Nella sezione Frequenza di pianificazione:

   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. Fai clic su Selezione dei tag.

10. Nel campo Seleziona i tag da eseguire, seleziona Giornaliera.

11. Fai clic su Crea.

La configurazione del flusso di lavoro che hai creato esegue l'intero risultato della compilazione più recente creato dalla configurazione della release transformations.

Libera spazio

Per evitare che al tuo Cloud de Confiance account vengano addebitati costi relativi alle risorse utilizzate in questa pagina, segui questi passaggi.

Elimina il set di dati creato in BigQuery

Per evitare che ti vengano addebitati costi per gli asset BigQuery, elimina il set di dati denominato dataform.

1. Nella Cloud de Confiance console, vai alla pagina BigQuery.

   Vai a BigQuery

2. Nel riquadro Explorer, espandi il progetto e seleziona dataform.

3. Fai clic sul menu more_vert Azioni e seleziona Elimina.

4. Nella finestra di dialogo Elimina set di dati , inserisci delete nel campo e fai clic su Elimina.

Elimina l'area di lavoro di sviluppo e le configurazioni di Dataform

La creazione dell'area di lavoro di sviluppo Dataform non comporta costi, ma per eliminarla puoi seguire questi passaggi:

1. Nella Cloud de Confiance console, vai alla pagina Dataform.

   Vai a Dataform

2. Fai clic su quickstart-repository.

3. Fai clic sulla scheda Release e pianificazione.

4. Nella sezione Configurazioni della release, fai clic sul menu more_vert Altro accanto alla production configurazione e poi su Elimina.

5. Nella sezione Configurazioni dei flussi di lavoro, fai clic sul menu more_vert Altro accanto alla transformations configurazione e poi su Elimina.

6. Nella scheda Aree di lavoro di sviluppo , fai clic sul menu more_vert Altro accanto a quickstart-workspace e poi seleziona Elimina.

7. Per confermare, fai clic su Elimina.

Elimina il repository Dataform

La creazione del repository Dataform non comporta costi, ma per eliminarlo puoi seguire questi passaggi:

1. Nella Cloud de Confiance console, vai alla pagina Dataform.

   Vai a Dataform

2. Accanto a quickstart-repository, fai clic sul menu more_vert Altro, e poi seleziona Elimina.

3. Nella finestra Elimina repository, inserisci il nome del repository per confermare l'eliminazione.

4. Per confermare, fai clic su Elimina.