GSP408

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 nem precisar de um administrador de banco de dados. O BigQuery usa SQL e está disponível no modelo de pagamento por utilização. Assim, você pode se concentrar na análise dos dados para encontrar informações relevantes.

Agora o BigQuery inclui um novo conjunto de dados de e-commerce com milhões de registros do Google Analytics referentes à Google Merchandise Store. Você tem uma cópia do conjunto de dados usado neste laboratório e analisará os campos e linhas disponíveis para extrair insights.

Este laboratório orientará você pela lógica da solução de problemas em consultas. Ele inclui atividades no contexto de uma situação real. Durante o laboratório, imagine que está trabalhando com uma nova analista de dados na sua equipe e que você recebeu dela as consultas abaixo para responder a algumas perguntas sobre o conjunto de dados de comércio eletrônico. Use as respostas para corrigir as consultas e chegar a um resultado concreto.

Conteúdo

Neste laboratório, você vai aprender a fazer o seguinte:

• Fixar projetos na árvore de recursos do BigQuery
• Usar o editor e o validador de consultas do BigQuery para identificar e corrigir erros de lógica e sintaxe do SQL

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 um projeto na árvore de recursos do BigQuery

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

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

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. Para abrir o projeto contendo esse tipo de dados, copie data-to-insights.

4. Clique em + Adicionar > Marcar um projeto com estrela por nome e copie o nome "data-to-insights".

5. Clique em Marcar com estrela.

O projeto data-to-insights é listado na seção "Explorer".

Editor e validador de consultas do BigQuery

As atividades das próximas seções apresentam consultas com erros comuns para você resolver. Este laboratório indicará o que você precisa observar e vai oferecer sugestões de como corrigir a sintaxe e retornar resultados significativos.

Para acompanhar a solução de problemas e as sugestões, copie e cole a consulta no editor de consultas do BigQuery. Se houver algum erro, um ponto de exclamação vermelho vai aparecer na linha que contém o erro e no validador de consultas (canto inferior).

Se você executar a consulta com erro, ela falhará. O erro será especificado nas informações do job.

Se nenhum erro for detectado, o validador de consulta mostrará uma marca de seleção verde. Quando aparecer a marca de seleção verde, clique em Executar a consulta para acessar o resultado.

Observação: acesse mais informações sobre a sintaxe em Sintaxe de consultas SQL padrão.

Tarefa 2: encontrar o número total de clientes que concluíram uma compra

Nesta seção, você criará uma consulta para encontrar o número de visitantes únicos que concluíram uma compra no seu site. Os dados estão na tabela rev_transactions que sua equipe de analistas de dados enviou. A equipe também enviou consultas de exemplo para ajudar na sua análise, mas você não tem certeza se elas estão escritas corretamente.

Resolva os problemas de alias, de vírgulas e apontados pelo validador de consultas

• Confira a consulta abaixo e responda a pergunta a seguir:

#standardSQL SELECT FROM `data-to-inghts.ecommerce.rev_transactions` LIMIT 1000

• E esta consulta atualizada?

#standardSQL SELECT * FROM [data-to-insights:ecommerce.rev_transactions] LIMIT 1000

• E esta consulta que usa SQL padrão?

#standardSQL SELECT FROM `data-to-insights.ecommerce.rev_transactions`

• E agora? Esta consulta tem uma coluna:

#standardSQL SELECT fullVisitorId FROM `data-to-insights.ecommerce.rev_transactions`

• E agora? A próxima consulta tem um título de página:

#standardSQL SELECT fullVisitorId hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• E agora? A vírgula que faltava foi corrigida.

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

Resposta: a consulta retorna resultados, mas você tem certeza de que os visitantes não foram contabilizados duas vezes? Além disso, retornar apenas uma linha não responde à pergunta sobre quantos visitantes únicos concluíram uma compra. Na próxima seção, você verá uma maneira de agregar os resultados.

Resolva problemas de consultas com erros de lógica, instruções GROUP BY e filtros curinga

• Agregue a próxima consulta para saber quantos visitantes únicos concluíram uma compra.

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• E isso? A função de agregação COUNT() foi adicionada:

#standardSQL SELECT COUNT(fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions`

• Na próxima consulta, foram adicionadas as funções GROUP BY e DISTINCT:

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY hits_page_pageTitle

Resultados

Ótimo. Os resultados são bons, mas parece que há algo errado.

• Filtre os resultados para acessar apenas aqueles com "Checkout Confirmation":

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_page_pageTitle = "Checkout Confirmation" GROUP BY hits_page_pageTitle

Clique em Verificar meu progresso para conferir o objetivo. Encontrar o número total de clientes que concluíram uma compra

Tarefa 3: listar as cidades com mais transações no site de e-commerce

Resolver erros de pedidos, campos calculados e filtragem após agregação

1. Complete a consulta parcialmente escrita:

SELECT geoNetwork_city, totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY

Possível solução:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city

2. Atualize sua consulta anterior para listar as cidades com o maior número de transações primeiro.

Possível solução:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY distinct_visitors DESC

3. Atualize sua consulta e crie um novo campo calculado para retornar o número médio de produtos por pedido em cada cidade.

Possível solução:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

Resultados

Filtre seus resultados agregados para retornar somente cidades com o campo avg_products_ordered superior a 20.

• Onde está o erro na consulta abaixo?

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` WHERE avg_products_ordered > 20 GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

Possível solução:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city HAVING avg_products_ordered > 20 ORDER BY avg_products_ordered DESC

Clique em Verificar meu progresso para conferir o objetivo. Listar as cidades com mais transações no site de e-commerce .

Tarefa 4: encontrar o número total de produtos em cada categoria

Encontrar os produtos mais vendidos filtrando por valores NULL

1. Onde está o erro na consulta abaixo? Como você pode corrigi-lo?

#standardSQL SELECT hits_product_v2ProductName, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY 1,2

2. Onde está o erro na consulta abaixo?

#standardSQL SELECT COUNT(hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC

3. Atualize a consulta anterior para contabilizar apenas produtos diferentes de cada categoria de produto.

Possível solução:

#standardSQL SELECT COUNT(DISTINCT hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC LIMIT 5

Observação:

(not set) pode indicar que o produto não tem uma categoria.

${productitem.product.origCatName} é um código de front-end para renderizar a categoria, o que pode indicar que o script de rastreamento do Google Analytics está sendo disparado antes que a página seja totalmente renderizada.

Clique em Verificar meu progresso para conferir o objetivo. Encontre o número total de produtos em cada categoria.

Parabéns!

Você solucionou problemas e corrigiu erros de consultas usando o SQL padrão no BigQuery. Use o validador de consultas para corrigir sintaxes e conferir os resultados da consulta, mesmo que ela não apresente erros.

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 aprender mais sobre o BigQuery:
  • Como analisar seu conjunto de dados de e-commerce com SQL no BigQuery
  • Dados meteorológicos no BigQuery
  • Como criar tabelas particionadas por data 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 19 de janeiro de 2024

Laboratório testado em 28 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.