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 Sintaxe de consulta SQL padrão.

Atividades deste laboratório

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

• 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

Para cada laboratório, você recebe um novo projeto do Google Cloud e um conjunto de recursos por um determinado período e sem custos financeiros.

1. Faça login no Qwiklabs em uma janela anônima.

2. Confira o tempo de acesso do laboratório (por exemplo, 1:15:00) e finalize todas as atividades nesse prazo.
   Não é possível pausar o laboratório. Você pode reiniciar o desafio, mas vai precisar refazer todas as etapas.

3. Quando tudo estiver pronto, clique em Começar o laboratório.

4. Anote as credenciais (Nome de usuário e Senha). É com elas que você vai fazer login no Console do Google Cloud.

5. Clique em Abrir Console do Google.

6. Clique em Usar outra conta, depois copie e cole as credenciais deste laboratório nos locais indicados.
   Se você usar outras credenciais, vai receber mensagens de erro ou cobranças.

7. Aceite os termos e pule a página de recursos de recuperação.

Tarefa 1: marque com estrela o projeto do laboratório no BigQuery

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

Abra o console do BigQuery

1. No Console do Google Cloud, selecione Menu de navegação > BigQuery. Você verá a caixa de mensagem Olá! Este é o BigQuery no Console do Cloud. Ela tem um link para o guia de início rápido e lista as atualizações da IU.
2. Clique em Concluído.

Por padrão, o BigQuery não mostra conjuntos de dados públicos na interface da Web. Portanto, você vai abrir o projeto contendo esse tipo de dados.

4. Clique em + Adicionar dados.

5. Selecione Marcar um projeto com estrela por nome.

6. Em Nome do projeto, insira data-to-insights.

7. Clique em Marcar com estrela.

8. No painel Explorer, o projeto data-to-insights estará marcado com estrela.

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 das sessões dos visitantes.

Volte à seção Explorer clássico e analise os dados da tabela all_sessions_raw:

1. Expanda o projeto data-to-insights.
2. Expanda 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

Consultar os dados de exemplo pode dar uma ideia melhor do que está incluído no conjunto de dados. Para mostrar 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 usa a função GROUP BY do SQL em cada campo e conta (COUNT) as linhas que têm campos com o mesmo valor.

• 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 linhas com o mesmo valor para todos os campos, elas são agrupadas e COUNT é 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.

• Copie e cole a consulta a seguir no campo EDITOR e depois selecione EXECUTAR para encontrar registros duplicados em todos os campos. Se a guia EDITOR não estiver visível, clique em + Consulta SQL.

#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 única, ainda é interessante confirmar se as linhas são realmente únicas com COUNT, GROUP BY e HAVING antes de iniciar a análise.

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 únicos para cada registro segundo seu 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: no 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 em + Consulta SQL.
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, clique no ícone do validador de consultas em tempo real.
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: no SQL, a cláusula ORDER BY aparece em ordem ascendente (ASC) de A até Z. Para inverter, escolha a opção ORDER BY field_name DESC.

Resultados

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

8. Escreva uma consulta para listar os cinco produtos mais visualizados (product_views) pelos visitantes. Inclua 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

9. 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 ( -- find each unique product viewed by each visitor SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- aggregate the top viewed products and sort them 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 no 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

10. Agora expanda 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:

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

Tarefa 4: pratique com o SQL

Já pode testar seus conhecimentos sobre o SQL? Responda a estas questões desafiadoras.

Desafio 1: calcule a taxa de conversão

1. Escreva uma consulta de taxa de conversão para produtos com estas características:

• Mais de 1.000 unidades foram adicionadas ao carrinho ou compradas.
• Essas unidades não são frisbees.

2. Responda a estas perguntas:

• Quantas vezes o produto foi incluído em um pedido completo ou incompleto?
• Quantas unidades do produto foram adicionadas ao pedido completo ou incompleto?
• Qual produto teve a maior taxa de conversão?

3. Conclua esta consulta parcial:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS potential_orders, SUM(productQuantity) AS quantity_product_added, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE v2ProductName NOT LIKE 'frisbee' GROUP BY v2ProductName HAVING quantity_product_added > ORDER BY conversion_rate LIMIT 10;

Possível solução:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS potential_orders, SUM(productQuantity) AS quantity_product_added, (COUNT(productQuantity) / COUNT(*)) AS conversion_rate, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE LOWER(v2ProductName) NOT LIKE '%frisbee%' GROUP BY v2ProductName HAVING quantity_product_added > 1000 ORDER BY conversion_rate DESC LIMIT 10;

Desafio 2: acompanhe o progresso da finalização de compra do visitante

• Escreva uma consulta que mostra eCommerceAction_type e a contagem fullVisitorId associada a cada tipo.

Possível solução:

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

Bônus

Você recebe o mapeamento abaixo para o tipo de ação: Unknown (Desconhecido) = 0 Click through of product lists (Clique nas listas de produtos) = 1 Product detail views (Visualizações detalhadas do produto) = 2 Add product(s) to cart (Adicionar produto(s) ao carrinho) = 3 Remove product(s) from cart (Remover produto(s) do carrinho) = 4 Check out (Check-out) = 5 Completed purchase (Compra finalizada) = 6 Refund of purchase (Reembolso da compra) = 7 Checkout options (Opções de check-out) = 8

• Use a instrução CASE para adicionar uma nova coluna à consulta anterior e mostrar o rótulo de eCommerceAction_type (por exemplo, "Completed purchase").

Possível solução:

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS number_of_unique_visitors, eCommerceAction_type, CASE eCommerceAction_type WHEN '0' THEN 'Unknown' WHEN '1' THEN 'Click through of product lists' WHEN '2' THEN 'Product detail views' WHEN '3' THEN 'Add product(s) to cart' WHEN '4' THEN 'Remove product(s) from cart' WHEN '5' THEN 'Check out' WHEN '6' THEN 'Completed purchase' WHEN '7' THEN 'Refund of purchase' WHEN '8' THEN 'Checkout options' ELSE 'ERROR' END AS eCommerceAction_type_label FROM `data-to-insights.ecommerce.all_sessions` GROUP BY eCommerceAction_type ORDER BY eCommerceAction_type;

Qual porcentagem de visitantes adicionou algo ao carrinho e concluiu a compra?

Resposta: 19.988 / 56.010 = 0.3568 ou 35,68%

Desafio 3: acompanhe carrinhos abandonados em sessões de alta qualidade

• Escreva uma consulta usando as funções de agregação que retornam os IDs de sessão única de visitantes que adicionaram um produto ao carrinho, mas não finalizaram a compra (abandonaram o carrinho).

Possível solução:

#standardSQL # high quality abandoned carts SELECT #unique_session_id CONCAT(fullVisitorId,CAST(visitId AS STRING)) AS unique_session_id, sessionQualityDim, SUM(productRevenue) AS transaction_revenue, MAX(eCommerceAction_type) AS checkout_progress FROM `data-to-insights.ecommerce.all_sessions` WHERE sessionQualityDim > 60 # high quality session GROUP BY unique_session_id, sessionQualityDim HAVING checkout_progress = '3' # 3 = added to cart AND (transaction_revenue = 0 OR transaction_revenue IS NULL)

Finalize o laboratório

Após terminar seu laboratório, clique em End Lab. O Qwiklabs removerá os recursos usados e limpará a conta para você.

Você poderá avaliar sua experiência neste laboratório. Basta selecionar o número de estrelas, digitar um comentário e clicar em Submit.

O número de estrelas indica o seguinte:

• 1 estrela = muito insatisfeito
• 2 estrelas = insatisfeito
• 3 estrelas = neutro
• 4 estrelas = satisfeito
• 5 estrelas = muito satisfeito

Feche a caixa de diálogo se não quiser enviar feedback.

Para enviar seu feedback, fazer sugestões ou correções, use a guia Support.

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