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 e-commerce com milhões de registros do Google Analytics da Google Merchandise Store que está armazenado em uma tabela do BigQuery. Você usará uma cópia desse conjunto de dados neste 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.

Objetivos

Neste laboratório, você usará o BigQuery nas 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 Começar o Laboratório

Leia estas instruções. Os laboratórios são cronometrados e não podem ser pausados. O timer é ativado quando você clica em Iniciar 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, e 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 (recomendado) ou uma janela anônima do navegador. Isso evita conflitos entre sua conta pessoal e de estudante, o que poderia causar cobranças extras na sua conta pessoal.

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

Observação: use apenas a conta de estudante neste laboratório. Se usar outra conta do Google Cloud, você poderá receber cobranças nela.

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 por ele, uma caixa de diálogo vai aparecer para você selecionar a forma de pagamento. No painel Detalhes do Laboratório, à esquerda, você vai encontrar o seguinte:

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

2. Se você estiver usando o navegador Chrome, clique em Abrir console do Google Cloud ou clique com o botão direito do mouse e selecione Abrir link em uma janela anônima.

   O laboratório ativa os recursos e depois abre a página Fazer Login em outra guia.

   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. Se necessário, copie o Nome de usuário abaixo e cole na caixa de diálogo Fazer login.

   {{{user_0.username | "Username"}}}

   Você também encontra o nome de usuário no painel Detalhes do Laboratório.

4. Clique em Próxima.

5. Copie a Senha abaixo e cole na caixa de diálogo de Olá.

   {{{user_0.password | "Password"}}}

   Você também encontra a senha no painel Detalhes do Laboratório.

6. Clique em Próxima.

   Importante: você precisa usar as credenciais fornecidas no laboratório, e não as da sua conta do Google Cloud. Observação: se você usar sua própria conta do Google Cloud neste laboratório, é possível que receba cobranças adicionais.

7. 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 Google Cloud será aberto nesta guia.

Observação: para acessar os produtos e serviços do Google Cloud, clique no Menu de navegação ou digite o nome do serviço ou produto no campo Pesquisar.

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.

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

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

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

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

Parabéns! Neste laboratório, você usou o BigQuery para visualizar e consultar os dados em busca de insights significativos sobre vários aspectos do marketing dos produtos. Você aprendeu a acessar um conjunto de dados de e-commerce, observar os metadados do conjunto de dados, remover entradas duplicadas, além de escrever e executar consultas.

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 2 de abril de 2024

Laboratório testado em 2 de abril de 2024

Copyright 2025 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.