Crea una pipeline ELT per i dati di analisi di marketing

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

Un tipico flusso di lavoro ELT estrae periodicamente 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, crei un flusso di lavoro ELT configurando un trasferimento di dati di analisi di marketing utilizzando BigQuery Data Transfer Service. Poi, pianifichi l'esecuzione periodica di Dataform per trasformare i dati.

In questo tutorial, utilizzi Google Ads come origine dati, ma puoi utilizzare 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:

• BigQuery Admin (roles/bigquery.admin)
• Dataform Admin (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.

Pianificare 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 console Cloud de Confiance .

   Vai a Trasferimenti 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.
      • Per 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, per Nome visualizzato, inserisci Marketing tutorial.

7. Nella sezione Opzioni di pianificazione:

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

8. Fai clic su Salva.

Dopo aver salvato la configurazione, BigQuery Data Transfer Service inizia il trasferimento dei dati. In base alle impostazioni nella configurazione del trasferimento, il trasferimento di dati viene eseguito una volta al giorno alle 08:00 UTC ed estrae i dati da 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 viste generate automaticamente, devi utilizzare la pseudocolonna _PARTITIONTIME nella query. Per maggiori informazioni, consulta la pagina Esecuzione di query sulle tabelle partizionate.

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

Rendimento della campagna

La seguente query di esempio analizza il rendimento della campagna Google Ads negli 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 attivano la pubblicazione dell'annuncio. Per saperne di più sulle opzioni di corrispondenza delle parole chiave, consulta l'articolo 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 del trasferimento dei dati per trasferire i dati più recenti da Google Ads, configura Dataform per trasformare regolarmente i dati di analisi del marketing. Dataform ti consente di pianificare trasformazioni regolari dei dati 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 console Cloud de Confiance , 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 ora visualizzato nell'elenco dei repository Dataform.

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

Crea e inizializza un'area di lavoro di sviluppo Dataform

Crea un workspace 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 console Cloud de Confiance , 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 del workspace di sviluppo.

5. Fai clic su Inizializza area di lavoro.

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

Per saperne di più sui workspace di sviluppo Dataform, consulta la panoramica dei workspace di sviluppo.

Dichiarare 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 console Cloud de Confiance , 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.

Dichiarare un'origine dati

Modifica 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 nello spazio di lavoro di sviluppo, puoi eseguirne il commit e il push nel repository seguendo questi passaggi:

1. Nello spazio di lavoro marketing-tutorial-workspace, fai clic su Esegui 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 Commit all changes (Esegui il commit di tutte le modifiche).
4. Nello spazio di lavoro marketing-tutorial-workspace, fai clic su Push to default branch (Push al branch predefinito).

Una volta eseguito il push delle modifiche nel repository, viene visualizzato il messaggio Workspace è aggiornato.

Pianificare 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 ramo main del repository marketing-tutorial-repository in cui archiviare le trasformazioni dei dati:

1. Nella console Cloud de Confiance , 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 Git commitish, 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 workflow 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 console Cloud de Confiance , 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, segui questi passaggi:

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

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

11. Fai clic su Crea.

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

Esegui la pulizia

Per evitare che al tuo account Cloud de Confiance 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 console Cloud de Confiance , 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 Dataform e le configurazioni

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

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

   Vai a Dataform

2. Fai clic su quickstart-repository.

3. Fai clic sulla scheda Pubblicazione e programmazione.

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

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

6. Nella scheda Aree di lavoro di sviluppo, fai clic sul menu more_vert Altro accanto a quickstart-workspace, quindi 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 console Cloud de Confiance , vai alla pagina Dataform.

   Vai a Dataform

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

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

4. Per confermare, fai clic su Elimina.