GSP407

Visão geral

O BigQuery é um banco de dados de análise NoOps, totalmente gerenciado e de baixo custo desenvolvido pelo Google. Com ele, você pode consultar muitos terabytes de dados sem ter que gerenciar uma infraestrutura ou precisar de um administrador de banco de dados. O BigQuery usa SQL e está disponível no modelo de pagamento por uso. Assim, você pode se concentrar na análise dos dados para encontrar insights relevantes.

Agora temos um novo conjunto de dados de comércio eletrônico com milhões de registros do Google Analytics da Google Merchandise Store. Os dados estão armazenados em uma tabela do BigQuery. Você usará uma cópia desse conjunto de dados para realizar este laboratório. Alguns exemplos de situações serão apresentados para você analisar os dados e aprender a remover informações duplicadas. O laboratório ainda explica como fazer análises mais detalhadas dos dados.

Se você quiser testar e saber mais sobre as consultas do BigQuery fornecidas para analisar os dados, acesse Referência de sintaxe de consulta do BigQuery.

O que você vai aprender

Neste laboratório, você usará o BigQuery para as seguintes tarefas:

• Acessar um conjunto de dados de e-commerce
• Analisar os metadados do conjunto de dados
• Remover entradas duplicadas
• Escrever e executar consultas

Configuração e requisitos

Antes de clicar no botão Start Lab

Leia estas instruções. Os laboratórios são cronometrados e não podem ser pausados. O timer é iniciado quando você clica em Começar o laboratório e mostra por quanto tempo os recursos do Google Cloud vão ficar disponíveis.

Este laboratório prático permite que você realize as atividades em um ambiente real de nuvem, não em uma simulação ou demonstração. Você vai receber novas credenciais temporárias para fazer login e acessar o Google Cloud durante o laboratório.

Confira os requisitos para concluir o laboratório:

• Acesso a um navegador de Internet padrão (recomendamos o Chrome).

Observação: para executar este laboratório, use o modo de navegação anônima ou uma janela anônima do navegador. Isso evita conflitos entre sua conta pessoal e a conta de estudante, o que poderia causar cobranças extras na sua conta pessoal.

• Tempo para concluir o laboratório---não se esqueça: depois de começar, não será possível pausar o laboratório.

Observação: não use seu projeto ou conta do Google Cloud neste laboratório para evitar cobranças extras na sua conta.

Como iniciar seu laboratório e fazer login no console do Google Cloud

1. Clique no botão Começar o laboratório. Se for preciso pagar, você verá um pop-up para selecionar a forma de pagamento. No painel Detalhes do laboratório à esquerda, você verá o seguinte:

   • O botão Abrir Console do Cloud
   • Tempo restante
   • As credenciais temporárias que você vai usar neste laboratório
   • Outras informações se forem necessárias

2. Clique em Abrir Console do Google. O laboratório ativa recursos e depois abre outra guia com a página Fazer login.

   Dica: coloque as guias em janelas separadas lado a lado.

   Observação: se aparecer a caixa de diálogo Escolher uma conta, clique em Usar outra conta.

3. Caso seja preciso, copie o Nome de usuário no painel Detalhes do laboratório e cole esse nome na caixa de diálogo Fazer login. Clique em Avançar.

4. Copie a Senha no painel Detalhes do laboratório e a cole na caixa de diálogo Olá. Clique em Avançar.

   Importante: você precisa usar as credenciais do painel à esquerda. Não use suas credenciais do Google Cloud Ensina. Observação: se você usar sua própria conta do Google Cloud neste laboratório, é possível que receba cobranças adicionais.

5. Acesse as próximas páginas:

   • Aceite os Termos e Condições.
   • Não adicione opções de recuperação nem autenticação de dois fatores (porque essa é uma conta temporária).
   • Não se inscreva em testes gratuitos.

Depois de alguns instantes, o console do GCP vai ser aberto nesta guia.

Observação: para ver uma lista dos produtos e serviços do Google Cloud, clique no Menu de navegação no canto superior esquerdo.

Tarefa 1: fixar o projeto do laboratório no BigQuery

Nesta seção, você vai adicionar o projeto data-to-insights aos recursos do seu ambiente.

1. Clique em Menu de navegação > BigQuery.

A caixa de mensagem "Olá! Este é o BigQuery no console do Cloud" vai aparecer.

Observação: a caixa de mensagem "Este é o BigQuery" no console do Cloud tem um link para o guia de início rápido e as atualizações da interface.

2. Clique em Concluído.
3. Por padrão, o BigQuery não mostra conjuntos de dados públicos na interface Web. Para abrir o projeto de conjuntos de dados público, copie "data-to-insights".
4. Clique em + Adicionar > Marcar um projeto com estrela por nome e depois defina o nome como data-to-insights. Clique em MARCAR COM ESTRELA.

A seção do Explorer agora mostra o projeto data-to-insights.

Tarefa 2: examinar os dados de e-commerce e identificar registros duplicados

Situação: sua equipe de analistas de dados exportou para o BigQuery os registros do Google Analytics referentes a um site de e-commerce e criou uma nova tabela com todos os dados brutos de sessão de visitante.

Examine os dados da tabela all_sessions_raw:

1. Clique no ícone Abrir nó perto de data-to-insights para expandir o projeto.
2. Abra e-commerce.
3. Clique em all_sessions_raw.

No painel direito, você verá uma seção com três visualizações dos dados da tabela:

• Guia "Esquema": inclui as colunas "Nome do campo", "Tipo", "Modo" e "Descrição" (que são as restrições lógicas usadas para organizar os dados)
• Guia "Detalhes": mostra os metadados da tabela
• Guia "Visualização": mostra as linhas e as colunas da tabela

4. Clique na guia Detalhes para ver os metadados da tabela.

Perguntas:

Identifique linhas duplicadas

Ver os dados de exemplo pode dar uma ideia melhor do que está incluído no conjunto de dados.

1. Para visualizar as linhas de exemplo da tabela sem usar o SQL, clique na guia Visualização.

2. Navegue pelas linhas. Não há nenhum campo que identifique uma linha de forma única, por isso você precisa de uma lógica avançada para identificar linhas duplicadas.

3. A consulta que você vai usar (abaixo) utiliza a função GROUP BY da SQL em todos os campos e conta (COUNT) onde há linhas com os mesmos valores em todos os campos:

• Quando o campo é único, COUNT retorna 1 porque não existe outro agrupamento de linhas com o mesmo valor para todos os campos.
• Quando existem várias linhas com o mesmo valor para todos os campos, elas são agrupadas e COUNT será maior que 1.

A última parte da consulta é um filtro de agregação que usa HAVING para mostrar os resultados maiores do que 1 para a contagem COUNT de registros duplicados. Portanto, o número de registros que têm duplicatas será o mesmo que o número de linhas na tabela resultante.

4. Copie e cole a consulta a seguir no Editor de consulta e depois execute a consulta para descobrir quais registros estão duplicados entre as colunas.

#standardSQL SELECT COUNT(*) as num_duplicate_rows, * FROM `data-to-insights.ecommerce.all_sessions_raw` GROUP BY fullVisitorId, channelGrouping, time, country, city, totalTransactionRevenue, transactions, timeOnSite, pageviews, sessionQualityDim, date, visitId, type, productRefundAmount, productQuantity, productPrice, productRevenue, productSKU, v2ProductName, v2ProductCategory, productVariant, currencyCode, itemQuantity, itemRevenue, transactionRevenue, transactionId, pageTitle, searchKeyword, pagePathLevel1, eCommerceAction_type, eCommerceAction_step, eCommerceAction_option HAVING num_duplicate_rows > 1;

Observação: nos seus conjuntos de dados, mesmo que você tenha uma chave exclusiva, ainda é interessante confirmar se as linhas são realmente exclusivas com COUNT, GROUP BY e HAVING antes de iniciar a análise.

Clique em Verificar meu progresso para conferir o objetivo. Identifique linhas duplicadas

Analise a nova tabela all_sessions

Nesta seção, você usará a tabela chamada all_sessions que teve registros duplicados eliminados.

Situação: a equipe de analistas de dados enviou essa consulta, e os especialistas em esquema identificaram os campos-chave que precisam ser exclusivos para cada registro segundo o esquema.

• Execute a consulta para confirmar que não há registros duplicados, desta vez na tabela all_sessions:

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=pt-BR SELECT fullVisitorId, # o ID do visitante único visitId, # um visitante pode ter múltiplas visitas date, # data da sessão, no formato de string YYYYMMDD time, # horário da visita ao site (pode ser 0 para vários, de acordo com a sessão do visitante) v2ProductName, # não exclusivo, já que um produto pode ter variações, como Color productSKU, # exclusivo para cada produto type, # um visitante pode visitar Páginas e/ou acionar Eventos (inclusive simultaneamente) eCommerceAction_type, # é mapeado para ‘add to cart', ‘completed checkout' eCommerceAction_step, eCommerceAction_option, transactionRevenue, # lucro do pedido transactionId, # identificador exclusivo para transação que incorre lucro COUNT(*) as row_count FROM `data-to-insights.ecommerce.all_sessions` GROUP BY 1,2,3 ,4, 5, 6, 7, 8, 9, 10,11,12 HAVING row_count > 1 # encontra duplicatas

A consulta não retorna registros.

Observação: na SQL, use GROUP BY ou ORDER BY para exibir o índice da coluna. Por exemplo, use GROUP BY 1 em vez de GROUP BY fullVisitorId.

Tarefa 3: escrever SQL básico com dados de e-commerce

Nesta seção, você vai realizar uma consulta para encontrar insights no conjunto de dados de e-commerce.

Escreva uma consulta que mostre o número total de visitantes únicos

Sua consulta determina o total de visualizações contando product_views e o total de visitantes únicos contando fullVisitorID.

1. Clique no ícone + (Criar nova consulta).
2. Escreva esta consulta no editor:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(DISTINCT fullVisitorId) AS unique_visitors FROM `data-to-insights.ecommerce.all_sessions`;

3. Para confirmar que sua sintaxe está correta, verifique se o validador de consulta em tempo real mostra o ícone de sinal de verificação verde.
4. Clique em Executar. Leia os resultados para conferir o número de visitantes únicos.

Resultados:

5. Desta vez, escreva uma consulta que mostre o total de visitantes únicos (fullVisitorID) em um site (channelGrouping):

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS unique_visitors, channelGrouping FROM `data-to-insights.ecommerce.all_sessions` GROUP BY channelGrouping ORDER BY channelGrouping DESC;

Resultados:

6. Escreva uma consulta para listar todos os nomes de produtos únicos (v2ProductName) em ordem alfabética:

#standardSQL SELECT (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` GROUP BY ProductName ORDER BY ProductName

Dica: na SQL, as cláusulas ORDER BY aparecem em ordem ascendente (ASC) de A até Z. Para inverter, use ORDER BY field_name DESC.

Resultados:

Esta consulta retorna o total de 633 produtos (linhas).

7. Escreva uma consulta para listar os cinco produtos mais visualizados (product_views) pelos visitantes, incluindo as pessoas que viram o mesmo produto mais de uma vez. A consulta conta o número de vezes que um produto (v2ProductName) foi visualizado (product_views) e lista as cinco primeiras entradas em ordem decrescente:

Dica: no Google Analytics, os visitantes podem exibir um produto durante os seguintes tipos de interação: “page”, “screenview”, “event”, “transaction”, “item”, “social”, “exception” e “timing”. No nosso caso, vamos filtrar somente por type = 'PAGE'.

#standardSQL SELECT COUNT(*) AS product_views, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Resultados:

8. Bônus: refine a consulta para não contar todas as vezes que um produto foi visualizado pelo mesmo visitante. Apenas uma visualização de produto por visitante vai ser considerada.

WITH unique_product_views_by_person AS ( -- encontra todos os produtos visualizados por cada visitante SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- agrega e ordena os produtos mais visualizados SELECT COUNT(*) AS unique_view_count, ProductName FROM unique_product_views_by_person GROUP BY ProductName ORDER BY unique_view_count DESC LIMIT 5

Dica: use a cláusula WITH da SQL para separar uma consulta complexa em várias etapas. Começamos criando uma consulta que conta a primeira vez que um visitante visualiza um produto. Depois criamos uma consulta que agrega visitantes e produtos.

Resultados:

9. Agora amplie a consulta anterior para incluir a quantidade de produtos distintos comprados e o número total de unidades dos pedidos (productQuantity):

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Resultados:

Perguntas:

10. Expanda a consulta para incluir a quantidade média de produtos por pedido (número total de unidades pedidas/número total de pedidos ou SUM(productQuantity)/COUNT(productQuantity)):

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, SUM(productQuantity) / COUNT(productQuantity) AS avg_per_order, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Resultados

Pergunta:

O produto "22 oz YouTube Bottle Infuser" apresentou o maior valor para avg_per_order, com 9,38 unidades por pedido.

Clique em Verificar meu progresso para conferir o objetivo. Escreva SQL básico com dados de e-commerce

Parabéns!

Você usou o BigQuery para visualizar e consultar os dados em busca de insights significativos sobre vários aspectos do marketing dos produtos.

Próximas etapas/Saiba mais

• Confira Conjuntos de dados públicos do BigQuery.
• Você tem uma conta do Google Analytics e quer consultar seus próprios conjuntos de dados no BigQuery? Siga este guia de exportação.
• Conheça 15 fatos interessantes que você provavelmente não sabia sobre o BigQuery.
• Confira outros laboratórios para saber mais sobre o BigQuery:
  • Como solucionar problemas comuns de SQL com o BigQuery
  • Dados meteorológicos no BigQuery

Treinamento e certificação do Google Cloud

Esses treinamentos ajudam você a aproveitar as tecnologias do Google Cloud ao máximo. Nossas aulas incluem habilidades técnicas e práticas recomendadas para ajudar você a alcançar rapidamente o nível esperado e continuar sua jornada de aprendizado. Oferecemos treinamentos que vão do nível básico ao avançado, com opções de aulas virtuais, sob demanda e por meio de transmissões ao vivo para que você possa encaixá-las na correria do seu dia a dia. As certificações validam sua experiência e comprovam suas habilidades com as tecnologias do Google Cloud.

Manual atualizado em 26 de janeiro de 2024

Laboratório testado no dia 24 de agosto de 2023

Copyright 2024 Google LLC. Todos os direitos reservados. Google e o logotipo do Google são marcas registradas da Google LLC. Todos os outros nomes de produtos e empresas podem ser marcas registradas das respectivas empresas a que estão associados.