Checkpoints
Identify duplicate rows
/ 30
Write basic SQL on ecommerce data
/ 70
Como analisar seu conjunto de dados de comércio eletrônico com SQL no Google BigQuery
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).
- Tempo para concluir o laboratório---não se esqueça: depois de começar, não será possível pausar o laboratório.
Como iniciar seu laboratório e fazer login no console do Google Cloud
-
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
-
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. -
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.
-
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. -
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.
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.
- Clique em Menu de navegação > BigQuery.
A caixa de mensagem "Olá! Este é o BigQuery no console do Cloud" vai aparecer.
- 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".
- 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
:
- Clique no ícone Abrir nó perto de data-to-insights para expandir o projeto.
- Abra e-commerce.
- 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
- 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.
-
Para visualizar as linhas de exemplo da tabela sem usar o SQL, clique na guia Visualização.
-
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.
-
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.
- 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.
Clique em Verificar meu progresso para conferir o objetivo.
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
:
A consulta não retorna registros.
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
.
- Clique no ícone + (Criar nova consulta).
- Escreva esta consulta no editor:
- 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.
- Clique em Executar. Leia os resultados para conferir o número de visitantes únicos.
Resultados:
- Desta vez, escreva uma consulta que mostre o total de visitantes únicos (
fullVisitorID
) em um site (channelGrouping
):
Resultados:
- Escreva uma consulta para listar todos os nomes de produtos únicos (
v2ProductName
) em ordem alfabética:
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).
- 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'.
Resultados:
- 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.
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:
- Agora amplie a consulta anterior para incluir a quantidade de produtos distintos comprados e o número total de unidades dos pedidos (
productQuantity
):
Resultados:
Perguntas:
- 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)
):
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.
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:
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.