Crie um pipeline ELT para dados de estatísticas de marketing

Este tutorial mostra como configurar um fluxo de trabalho ELT que extrai, carrega e transforma dados de estatísticas de marketing no BigQuery.

Um fluxo de trabalho de ELT típico extrai periodicamente novos dados de clientes da sua origem de dados e carrega-os no BigQuery. Os dados não estruturados são, em seguida, processados em métricas significativas. Neste tutorial, vai criar um fluxo de trabalho de ELT configurando uma transferência de dados de estatísticas de marketing através do Serviço de transferência de dados do BigQuery. Em seguida, agenda o Dataform para executar transformações periódicas nos dados.

Neste tutorial, usa o Google Ads como origem de dados, mas pode usar qualquer uma das origens de dados suportadas pelo Serviço de transferência de dados do BigQuery.

Antes de começar

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

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

  3. Verify that billing is enabled for your Google Cloud project.

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

  5. Verify that billing is enabled for your Google Cloud project.

    6. Funções necessárias

    Para receber as autorizações de que precisa para concluir este tutorial, peça ao seu administrador para lhe conceder as seguintes funções de IAM no projeto:

    Para mais informações sobre a atribuição de funções, consulte o artigo Faça a gestão do acesso a projetos, pastas e organizações.

    Também pode conseguir as autorizações necessárias através de funções personalizadas ou outras funções predefinidas.

    Agende transferências de dados recorrentes

    Para manter o BigQuery atualizado com os dados de marketing mais recentes da sua origem de dados, configure transferências de dados recorrentes através do Serviço de transferência de dados do BigQuery para extrair e carregar dados com base num horário.

    Neste tutorial, usa o Google Ads como exemplo de origem de dados. Para ver uma lista completa das origens de dados suportadas pelo Serviço de transferência de dados do BigQuery, consulte o artigo Origens de dados compatíveis.

    1. Aceda à página Transferências de dados na Google Cloud consola.

      Aceda a Transferências de dados

    2. Clique em Criar transferência.

    3. Na secção Tipo de origem, para Origem, escolha Google Ads.

    4. Na secção Detalhes da origem de dados:

      1. Para o ID de cliente, introduza o seu ID de cliente do Google Ads.
      2. Em Tipo de relatório, selecione Padrão. O relatório padrão inclui o conjunto padrão de relatórios e campos, conforme detalhado na transformação do relatório do Google Ads.
        • Para Atualizar janela, introduza 5.

    5. Na secção Definições de destino, para Conjunto de dados, selecione o conjunto de dados que criou para armazenar os seus dados.

    6. Na secção Nome da configuração da transferência, para Nome a apresentar, introduza Marketing tutorial.

    7. Na secção Opções de programação:

      • Para Frequência de repetição, selecione Dias.
      • Para At, introduza 08:00.

    8. Clique em Guardar.

    Depois de guardar a configuração, o Serviço de transferência de dados do BigQuery inicia a transferência de dados. Com base nas definições na configuração de transferência, a transferência de dados é executada uma vez por dia às 08:00 UTC e extrai dados do Google Ads dos últimos cinco dias.

    Pode monitorizar tarefas de transferência em curso para verificar o estado de cada transferência de dados.

    Consultar dados de tabelas

    Quando os dados são transferidos para o BigQuery, são gravados em tabelas particionadas por tempo de ingestão. Para mais informações, consulte o artigo Introdução às tabelas particionadas.

    Se consultar as tabelas diretamente em vez de usar as vistas geradas automaticamente, tem de usar a pseudocoluna _PARTITIONTIME na consulta. Para mais informações, consulte o artigo Consultar tabelas particionadas.

    As secções seguintes mostram exemplos de consultas que pode usar para examinar os seus dados transferidos.

    Desempenho da campanha

    A consulta de exemplo seguinte analisa o desempenho da campanha do Google Ads nos últimos 30 dias.

    Consola 

    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'

    Substitua o seguinte:

    • DATASET: o nome do conjunto de dados que criou para armazenar a tabela transferida
    • CUSTOMER_ID: o seu ID de cliente do Google Ads.

    Contagem de palavras-chave

    A consulta de exemplo seguinte analisa as palavras-chave por campanha, grupo de anúncios e estado da palavra-chave. Esta consulta usa a função KeywordMatchType. Os tipos de correspondência de palavras-chave ajudam a controlar as pesquisas que podem acionar o seu anúncio. Para mais informações sobre as opções de correspondência de palavras-chave, consulte o artigo Acerca das opções de correspondência de palavras-chave.

    Consola 

      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'

    Substitua o seguinte:

    • DATASET: o nome do conjunto de dados que criou para armazenar a tabela transferida
    • CUSTOMER_ID: o seu ID de cliente do Google Ads.

    Crie um repositório do Dataform

    Depois de criar a configuração de transferência de dados para transferir os dados mais recentes do Google Ads, configure o Dataform para transformar regularmente os dados de estatísticas de marketing. O Dataform permite-lhe agendar transformações de dados regulares e definir estas transformações com SQL enquanto colabora com outros analistas de dados.

    Crie um repositório do Dataform para armazenar as consultas SQLX que compõem o seu código de transformação.

    1. Na Google Cloud consola, aceda à página Dataform.

      Aceder ao Dataform

    2. Clique em Criar repositório.

    3. Na página Criar repositório, faça o seguinte:

      1. No campo ID do repositório, introduza marketing-tutorial-repository.
      2. Na lista Região, selecione uma região.
      3. Clique em Criar.

    O repositório marketing-tutorial-repository aparece agora na lista de repositórios do Dataform.

    Para mais informações sobre repositórios do Dataform, consulte o artigo Acerca dos repositórios do Dataform.

    Crie e inicialize um espaço de trabalho de desenvolvimento do Dataform

    Crie um espaço de trabalho de desenvolvimento do Dataform para poder trabalhar no código de transformação no seu repositório antes de confirmar e enviar as alterações para o repositório.

    1. Na Google Cloud consola, aceda à página Dataform.

      Aceder ao Dataform

    2. Clique em marketing-tutorial-repository.

    3. Clique em Criar espaço de trabalho de desenvolvimento.

    4. Na janela Criar espaço de trabalho de desenvolvimento, faça o seguinte:

      1. No campo ID do espaço de trabalho, introduza marketing-tutorial-workspace.
      2. Clique em Criar.

      É apresentada a página do espaço de trabalho de desenvolvimento.

    5. Clique em Inicializar espaço de trabalho.

    O espaço de trabalho de marketing-tutorial-workspacedesenvolvimento aparece agora no seu repositório marketing-tutorial-repository no separador Espaços de trabalho de desenvolvimento, juntamente com dois ficheiros de exemplo no diretório definitions denominado *first_view.sqlx e *second_view.sqlx.

    Para mais informações sobre os espaços de trabalho de desenvolvimento do Dataform, consulte o artigo Vista geral dos espaços de trabalho de desenvolvimento.

    Declare a sua tabela do Google Ads como origem da tabela

    Associe a tabela do Google Ads recém-transferida ao Dataform declarando-a como uma origem de dados através dos seguintes passos:

    Crie um ficheiro SQLX para a declaração da origem de dados

    No Dataform, declara um destino da origem de dados criando um ficheiro SQLX no diretório definitions/:

    1. Na Google Cloud consola, aceda à página Dataform.

      Aceda à página Dataform

    2. Selecione marketing-tutorial-repository.

    3. Selecione marketing-tutorial-workspace.

    4. No painel Ficheiros, junto a definitions/, clique no menu Mais.

    5. Clique em Criar ficheiro.

    6. No painel Criar novo ficheiro, faça o seguinte:

      1. No campo Adicionar um caminho do ficheiro, após definitions/, introduza o nome definitions/googleads-declaration.sqlx.
      2. Clique em Criar ficheiro.

    Declare uma origem de dados

    Edite o definitions/googleads-declaration.sqlx para declarar uma tabela do Google Ads transferida como uma origem de dados. Este exemplo declara a tabela ads_Campaign como uma origem de dados:

    1. No espaço de trabalho de desenvolvimento, no painel Ficheiros, clique no ficheiro SQLX para a declaração da origem de dados.

    2. No ficheiro, introduza o seguinte fragmento do código:

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

    Defina a transformação

    Defina as transformações de dados criando um ficheiro SQLX no diretório definitions/ Neste tutorial, cria uma transformação diária que agrega métricas como cliques, impressões, custos e conversões através de um ficheiro denominado daily_performance.sqlx.

    Crie o ficheiro SQLX de transformação

    1. No painel Ficheiros, junto a definitions/, clique no menu Mais e, de seguida, selecione Criar ficheiro.
    2. No campo Adicionar um caminho de ficheiro, introduza definitions/daily_performance.sqlx.
    3. Clique em Criar ficheiro.

    Defina o ficheiro SQLX de transformação

    1. No painel Ficheiros, expanda o diretório definitions/.

    2. Selecione daily_performance.sqlx e, de seguida, introduza a seguinte consulta:

          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

    Confirme e envie as alterações

    Depois de fazer as alterações no espaço de trabalho de desenvolvimento, pode confirmar e enviar estas alterações para o repositório seguindo estes passos:

    1. No espaço de trabalho marketing-tutorial-workspace, clique em Confirmar 1 alteração.
    2. No painel Novo commit, introduza uma descrição do commit no campo Adicionar uma mensagem de commit.
    3. Clique em Confirmar todas as alterações.
    4. No espaço de trabalho marketing-tutorial-workspace, clique em Enviar para ramo predefinido.

    Depois de as alterações serem enviadas com êxito para o repositório, é apresentada a mensagem O Workspace está atualizado.

    Agende a transformação de dados

    Depois de definir o ficheiro de transformação de dados, agende as transformações de dados.

    Crie um lançamento de produção

    Um lançamento de produção no Dataform garante que o seu ambiente é atualizado de forma consistente com os resultados das suas transformações de dados. Os passos seguintes mostram como especificar a ramificação main do repositório marketing-tutorial-repository para armazenar as transformações de dados:

    1. Na Google Cloud consola, aceda à página Dataform.

      Aceda à página Dataform

    2. Selecione marketing-tutorial-repository.

    3. Clique no separador Lançamentos e agendamento.

    4. Clique em Criar versão de produção.

    5. No painel Criar configuração de lançamento, configure as seguintes definições:

      1. No campo ID da versão, introduza transformations.
      2. No campo Git commitish, mantenha o valor predefinido main .
      3. Na secção Frequência da programação, selecione A pedido.

    6. Clique em Criar.

    Crie uma configuração do fluxo de trabalho

    Depois de criar um lançamento de produção, pode criar uma configuração de fluxo de trabalho que execute as transformações de dados num horário especificado no seu repositório. Os passos seguintes mostram como agendar transformações diárias a partir do ficheiro transformations:

    1. Na Google Cloud consola, aceda à página Dataform.

      Aceda à página Dataform

    2. Selecione marketing-tutorial-repository.

    3. Clique no separador Lançamentos e agendamento.

    4. Na secção Configurações do fluxo de trabalho, clique em Criar.

    5. No painel Criar configuração do fluxo de trabalho, no campo ID da configuração, introduza transformations.

    6. No menu Configuração de lançamento, selecione transformations.

    7. Em Autenticação, selecione Executar com credenciais do utilizador

    8. Na secção Frequência da programação, faça o seguinte:

      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. Clique em Seleção de etiquetas.

    10. No campo Selecionar etiquetas a executar, selecione Diariamente.

    11. Clique em Criar.

    A configuração do fluxo de trabalho que criou executa o resultado da compilação mais recente criado pela configuração de lançamento transformations.

    Limpar

    Para evitar incorrer em cobranças na sua Google Cloud conta pelos recursos usados nesta página, siga estes passos.

    Elimine o conjunto de dados criado no BigQuery

    Para evitar incorrer em custos por recursos do BigQuery, elimine o conjunto de dados denominado dataform.

    1. Na Google Cloud consola, aceda à página BigQuery.

      Aceda ao BigQuery

    2. No painel Explorador, expanda o projeto e selecione dataform.

    3. Clique no menu Ações e, de seguida, selecione Eliminar.

    4. Na caixa de diálogo Eliminar conjunto de dados, introduza delete no campo e, de seguida, clique em Eliminar.

    Elimine o espaço de trabalho de desenvolvimento e as configurações do Dataform

    A criação do espaço de trabalho de desenvolvimento do Dataform não acarreta custos, mas para eliminar o espaço de trabalho de desenvolvimento, pode seguir estes passos:

    1. Na Google Cloud consola, aceda à página Dataform.

      Aceder ao Dataform

    2. Clique em quickstart-repository.

    3. Clique no separador Lançamento e agendamento.

    4. Na secção Configurações de lançamento, clique no menu Mais junto à configuração production e, de seguida, clique em Eliminar.

    5. Na secção Configurações do fluxo de trabalho, clique no menu Mais junto à configuração transformations e, de seguida, clique em Eliminar.

    6. No separador Espaços de trabalho de desenvolvimento, clique no menu Mais junto a quickstart-workspace e, de seguida, selecione Eliminar.

    7. Para confirmar, clique em Eliminar.

    Elimine o repositório do Dataform

    A criação de um repositório do Dataform não incorre em custos, mas pode seguir estes passos para eliminar o repositório:

    1. Na Google Cloud consola, aceda à página Dataform.

      Aceder ao Dataform

    2. Em quickstart-repository, clique no menu Mais e, de seguida, selecione Eliminar.

    3. Na janela Eliminar repositório, introduza o nome do repositório para confirmar a eliminação.

    4. Para confirmar, clique em Eliminar.