Criar um pipeline ELT para dados de análise de marketing

Neste tutorial, você vai aprender a configurar um fluxo de trabalho de ELT que extrai, carrega e transforma dados de análise de marketing no BigQuery.

Um fluxo de trabalho de ELT típico extrai periodicamente novos dados de clientes da sua fonte de dados e os carrega no BigQuery. Os dados não estruturados são processados em métricas significativas. Neste tutorial, você vai criar um fluxo de trabalho de ELT configurando uma transferência de dados de análise de marketing usando o serviço de transferência de dados do BigQuery. Em seguida, programe o Dataform para executar transformações periódicas nos dados.

Neste tutorial, você usa o Google Ads como fonte de dados, mas pode usar qualquer uma das fontes de dados compatíveis com o serviço de transferência de dados do BigQuery.

Antes de começar

  1. Faça login na sua conta do Google Cloud . Se você começou a usar o Google Cloud, crie uma conta para avaliar o desempenho de nossos produtos em situações reais. Clientes novos também recebem US$ 300 em créditos para executar, testar e implantar cargas de trabalho.

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

Funções exigidas

Para conseguir as permissões necessárias a fim de concluir este tutorial, peça ao administrador para conceder a você os seguintes papéis do IAM no projeto:

Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

Também é possível conseguir as permissões necessárias usando papéis personalizados ou outros papéis predefinidos.

Programar transferências de dados recorrentes

Para manter o BigQuery atualizado com os dados de marketing mais recentes da sua fonte, configure transferências recorrentes usando o serviço de transferência de dados do BigQuery para extrair e carregar dados de acordo com uma programação.

Neste tutorial, usamos o Google Ads como exemplo de fonte de dados. Para conferir uma lista completa das fontes de dados compatíveis com o serviço de transferência de dados do BigQuery, consulte Fontes de dados compatíveis.

  1. Acesse a página "Transferências de dados" no console Google Cloud .

    Acesse Transferências de dados

  2. Clique em Criar transferência.

  3. Na seção Tipo de origem, em Origem, escolha Google Ads.

  4. Na seção Detalhes da fonte de dados, faça o seguinte:

    1. Em ID de cliente, insira 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 em Transformação de relatórios do Google Ads.
      • Em Janela de atualização, insira 5.

  5. Na seção Configurações de destino, em Conjunto de dados, selecione o conjunto de dados que você criou para armazenar seus dados.

  6. No campo Nome de exibição da seção Nome da configuração de transferência, insira Marketing tutorial.

  7. Na seção Opções de programação, faça o seguinte:

    • Em Frequência de repetição, selecione Dias.
    • Em Em, insira 08:00.

  8. Clique em Salvar.

Depois de salvar a configuração, o serviço de transferência de dados do BigQuery inicia a transferência de dados. Com base nas configurações da transferência de dados, a transferência de dados é executada uma vez por dia às 8h UTC e extrai dados do Google Ads dos últimos cinco dias.

É possível monitorar os jobs de transferência em andamento para verificar o status de cada transferência de dados.

Consultar os dados da tabela

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

Use a pseudocoluna _PARTITIONTIME para consultar suas tabelas diretamente em vez de usar visualizações geradas automaticamente. Para mais informações, veja Como consultar tabelas particionadas.

As seções a seguir mostram exemplos de consultas que podem ser usadas para examinar os dados transferidos.

Desempenho da campanha

A consulta de amostra a seguir analisa o desempenho da campanha do Google Ads nos últimos 30 dias.

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'

Substitua:

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

Contagem de palavras-chave

Na consulta de amostra a seguir, analisamos as palavras-chave por campanha, grupo de anúncios e status da palavra-chave. Nesta consulta, a função KeywordMatchType é usada. Com os tipos de correspondência de palavras-chave, você controla quais pesquisas podem acionar o anúncio. Para mais informações sobre as opções de correspondência de palavras-chave, consulte Sobre opções de correspondência de palavras-chave.

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'

Substitua:

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

Criar 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 seus dados de análise de marketing. Com o Dataform, é possível programar transformações regulares de dados e definir essas 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 seu código de transformação.

  1. No Google Cloud console, acesse a página Dataform.

    Acesse o Dataform

  2. Clique em Criar repositório.

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

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

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

Para mais informações sobre repositórios do Dataform, consulte Sobre os repositórios do Dataform.

criar e inicializar um espaço de trabalho de desenvolvimento do Dataform

Crie um espaço de trabalho de desenvolvimento do Dataform para trabalhar no código de transformação no repositório antes de fazer commit e enviar as mudanças para o repositório.

  1. No Google Cloud console, acesse a página Dataform.

    Acesse o 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 Código do espaço de trabalho, digite marketing-tutorial-workspace.
    2. Clique em Criar.

    A página do espaço de trabalho de desenvolvimento é exibida.

  5. Clique em Inicializar espaço de trabalho.

O espaço de trabalho de desenvolvimento marketing-tutorial-workspace agora aparece no repositório marketing-tutorial-repository na guia Espaços de trabalho de desenvolvimento, junto com dois arquivos de exemplo no diretório definitions chamados *first_view.sqlx e *second_view.sqlx.

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

Declarar sua tabela do Google Ads como origem da tabela

Conecte a tabela do Google Ads recém-transferida ao Dataform declarando-a como uma fonte de dados seguindo estas etapas:

Criar um arquivo SQLX para declaração de fonte de dados

No Dataform, você declara um destino de fonte de dados criando um arquivo SQLX no diretório definitions/:

  1. No Google Cloud console, acesse a página Dataform.

    Acessar a página do Dataform

  2. Selecione marketing-tutorial-repository.

  3. Selecione marketing-tutorial-workspace.

  4. No painel Arquivos, ao lado de definitions/, clique no menu Mais.

  5. Selecione Criar arquivo.

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

    1. No campo Adicionar um caminho de arquivo, depois de definitions/, insira o nome definitions/googleads-declaration.sqlx.
    2. Selecione Criar arquivo.

Declarar uma fonte de dados

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

  1. No espaço de trabalho de desenvolvimento, no painel Arquivos, clique no arquivo SQLX para declaração da fonte de dados.

  2. No arquivo, digite este snippet de código:

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

Definir sua transformação

Defina suas transformações de dados criando um arquivo SQLX no diretório definitions/. Neste tutorial, você vai criar uma transformação diária que agrega métricas como cliques, impressões, custos e conversões usando um arquivo chamado daily_performance.sqlx.

Criar o arquivo SQLX de transformação

  1. No painel Arquivos, ao lado de definitions/, clique no menu Mais e selecione Criar arquivo.
  2. No campo Adicionar um caminho de arquivo, insira definitions/daily_performance.sqlx.
  3. Selecione Criar arquivo.

Definir o arquivo SQLX de transformação

  1. No painel Arquivos, abra o diretório definitions/.

  2. Selecione daily_performance.sqlx e insira 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

Confirmar e enviar suas mudanças

Depois de fazer as mudanças no espaço de trabalho de desenvolvimento, siga estas etapas para confirmar e enviar as mudanças ao repositório:

  1. No espaço de trabalho marketing-tutorial-workspace, clique em Confirmar uma mudança.
  2. No painel Novo commit, insira uma descrição no campo Adicionar uma mensagem de commit.
  3. Clique em Confirmar todas as mudanças.
  4. No espaço de trabalho marketing-tutorial-workspace, clique em Enviar para a ramificação padrão.

Depois que as mudanças forem enviadas ao repositório, a mensagem O espaço de trabalho está atualizado vai aparecer.

Programar a transformação de dados

Depois de definir o arquivo de transformação de dados, programe as transformações.

Criar uma versão de produção

Uma versão de produção no Dataform garante que seu ambiente seja atualizado de forma consistente com os resultados das transformações de dados. As etapas a seguir mostram como especificar a ramificação main do repositório marketing-tutorial-repository para armazenar suas transformações de dados:

  1. No Google Cloud console, acesse a página Dataform.

    Acessar a página do Dataform

  2. Selecione marketing-tutorial-repository.

  3. Clique na guia Versões e programação.

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

  5. No painel Criar configuração de versão, defina as seguintes configurações:

    1. No campo ID da versão, digite transformations.
    2. No campo Commitish do Git, deixe o valor padrão main .
    3. Na seção Frequência de programação, selecione Sob demanda.

  6. Clique em Criar.

Criar uma configuração de fluxo de trabalho

Depois de criar uma versão de produção, é possível criar uma configuração de fluxo de trabalho que executa as transformações de dados em uma programação especificada no seu repositório. As etapas a seguir mostram como programar transformações diárias do arquivo transformations:

  1. No Google Cloud console, acesse a página Dataform.

    Acessar a página do Dataform

  2. Selecione marketing-tutorial-repository.

  3. Clique na guia Versões e programação.

  4. Na seçã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, digite transformations.

  6. No menu Configuração da versão, selecione transformations.

  7. Em Autenticação, selecione Executar com as credenciais do usuário.

  8. Na seção Frequência de programação, realize estas ações:

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

  10. No campo Selecionar tags a serem executadas, selecione Diário.

  11. Clique em Criar.

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

Limpar

Para evitar cobranças na conta do Google Cloud pelos recursos usados nesta página, siga as etapas abaixo.

Excluir o conjunto de dados criado no BigQuery

Para evitar cobranças por recursos do BigQuery, exclua o conjunto de dados chamado dataform.

  1. No console do Google Cloud , acesse a página BigQuery.

    Acessar o BigQuery

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

  3. Clique no menu Ações e selecione Excluir.

  4. Na caixa de diálogo Excluir conjunto de dados, digite delete no campo e clique em Excluir.

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

A criação de espaços de trabalho de desenvolvimento do Dataform não gera custos, mas para excluir o espaço de trabalho de desenvolvimento, siga estas etapas:

  1. No Google Cloud console, acesse a página Dataform.

    Acesse o Dataform

  2. Clique em quickstart-repository.

  3. Clique na guia Lançamento e programação.

  4. Na seção Configurações da versão, clique no menu Mais ao lado da configuração production e clique em Excluir.

  5. Na seção Configurações do fluxo de trabalho, clique no menu Mais ao lado da configuração transformations e clique em Excluir.

  6. Na guia Espaços de trabalho de desenvolvimento, clique no menu Mais ao lado de quickstart-workspace e selecione Excluir.

  7. Para confirmar, clique em Excluir.

Excluir o repositório do Dataform

A criação de repositórios do Dataform não gera custos, mas para excluir o repositório, siga estas etapas:

  1. No Google Cloud console, acesse a página Dataform.

    Acesse o Dataform

  2. Em quickstart-repository, clique no menu Mais e selecione Excluir.

  3. Na janela Excluir repositório, insira o nome do repositório para confirmar a exclusão.

  4. Para confirmar, clique em Excluir.