Esse recurso está disponível apenas no Google Analytics 360, que faz parte do Google Marketing Platform. Saiba mais sobre o Google Marketing Platform. |
Este artigo contém exemplos de como criar consultas dos dados do Google Analytics que você exporta para o BigQuery. Disponibilizamos um conjunto de dados de amostra para que você possa praticar com as consultas mostradas aqui.
Neste artigo:- Otimização de consultas
- Dicas e práticas recomendadas
- Exemplos de consultas básicas
- Total de [metric] por [dimension]
- Taxa média de rejeição por [dimension]
- Número médio de visualizações de página do produto por tipo de comprador
- Número médio de transações por comprador
- Valor médio gasto por sessão
- Sequência de hits (análise de caminho)
- Várias dimensões personalizadas no nível do hit ou da sessão
- Exemplos de consultas avançadas
- Produtos adquiridos por clientes que compraram o produto A (e-commerce clássico)
- Produtos adquiridos por clientes que compraram o produto A (e-commerce avançado)
- Número médio de interações do usuário antes da compra
- Porcentagem de estoque vendido por produto
- Lucratividade de cada produto
- Lucratividade real de cada produto
Otimização de consultas
Todas as consultas que você executa contribuem para sua cota mensal de processamento de dados. Se você selecionar campos irrelevantes, vai aumentar a quantidade de dados a serem processados e, como resultado, usará mais do que o necessário da sua cota mensal. Consultas otimizadas fazem uso eficiente da sua cota mensal de processamento de dados.
Saiba mais sobre preços.
Selecione apenas o que você precisa
Ao formular uma consulta, selecione os campos relevantes na instrução SELECT. O fato de não chamar campos irrelevantes reduz a quantidade de dados e o tempo necessários para processar a consulta.
Exemplo: não use o operador de caractere curinga
Forma incorreta: usar o operador de caractere curinga |
---|
SELECT * |
Forma correta: usar os nomes dos campos para evitar processamento desnecessário |
---|
SELECT field1, field2 |
Permissão de armazenamento em cache
Quando possível, evite o uso de funções como campos. Funções (como NOW()
ou TODAY()
) retornam resultados variáveis, o que impede que as consultas sejam armazenadas em cache e, portanto, retornadas mais rápido. Em vez disso, use horas e datas específicas.
Usar tabelas intermediárias para subconsultas frequentes
Se você perceber que usa repetidas vezes uma consulta específica como uma subconsulta, salve essa consulta como uma tabela intermediária. Para isso, clique em Salvar como tabela acima dos resultados da consulta. Você vai poder consultar essa tabela na seção FROM
da sua consulta, reduzindo a quantidade de dados a ser processada e o tempo necessário para isso.
Usar uma tabela intermediária |
---|
SELECT field1, field2 |
Depuração da consulta
O BigQuery depura seu código à medida que você o cria. Na janela de composição, a depuração é indicada logo abaixo da consulta. Esse processo também está disponível usando a API com a sinalização dryRun.
Consultas válidas têm um indicador verde em que você pode clicar para ver a quantidade de dados processados pela consulta. Esse recurso dá a você a chance de otimizar os dados antes de executar a consulta para evitar o processamento de dados desnecessários.
Consultas inválidas têm um indicador vermelho em que você pode clicar para ver informações sobre o erro e encontrar a linha e coluna onde o erro está ocorrendo. No exemplo abaixo, a instrução GROUP BY está em branco e o erro é indicado.
Dicas e práticas recomendadas
Usar o conjunto de dados de amostra
Os exemplos a seguir usam o conjunto de dados de amostra do Google Analytics.
Para usar as consultas nos seus próprios dados, basta substituir os nomes do projeto e do conjunto de dados nos exemplos pelos seus próprios nomes.
Como usar o SQL padrão e o SQL legado
O BigQuery é compatível com dois dialetos SQL:
O artigo Como migrar para o SQL padrão explica as diferenças entre os dois dialetos.
Agora o SQL padrão é o dialeto SQL preferido para consultar dados armazenados no BigQuery.
Consulte Ativar o SQL padrão para saber como ativar o SQL padrão na IU, na CLI, na API do BigQuery ou em qualquer interface que você esteja usando.
A maneira mais fácil de começar é incluindo o comentário "standardSQL" na parte de cima das suas consultas com o SQL padrão, conforme mostrado nos exemplos a seguir.
Com o SQL legado, os dados do Google Analytics 360 são transferidos diariamente para uma nova tabela. Para consultar várias de uma só vez, use vírgulas para separar os nomes das tabelas, a função de caractere curinga TABLE_DATE_RANGE
ou várias funções TABLE_DATE_RANGE
separadas por vírgulas, conforme os exemplos a seguir.
Como consultar várias tabelas
Os exemplos a seguir mostram consultas com SQL padrão e SQL legado para os mesmos dados.
3 dias
SQL padrão
3 dias usando UNION ALL |
---|
#standardSQL |
SQL legado
3 dias usando nomes de tabelas separados por vírgula |
---|
SELECT |
Últimos 1.095 dias
SQL padrão
Últimos 1.095 dias usando _TABLE_SUFFIX |
---|
#standardSQL |
SQL legado
Últimos 1.095 dias usando TABLE_DATE_RANGE |
---|
SELECT |
Últimos 36 meses
SQL padrão
Últimos 36 meses usando _TABLE_SUFFIX |
---|
#standardSQL |
SQL legado
Últimos 36 meses usando TABLE_DATE_RANGE |
---|
SELECT |
Últimos 3 anos
SQL padrão
Últimos 3 anos usando _TABLE_SUFFIX |
---|
#standardSQL |
SQL legado
Últimos 3 anos usando TABLE_DATE_RANGE |
---|
SELECT |
Período específico
SQL padrão
Período específico usando _TABLE_SUFFIX |
---|
#standardSQL |
SQL legado
Período específico usando TABLE_DATE_RANGE |
---|
SELECT |
Dados dos últimos 3 anos até hoje (intradiário)
SQL padrão
Dados dos últimos 3 anos até hoje (intradiário) usando UNION ALL & _TABLE_SUFFIX |
---|
Observação: esta consulta de exemplo não vai funcionar com o conjunto de dados público do Google Analytics porque atualmente não há tabela intradiária. |
#standardSQL |
SQL legado
Dados dos últimos 3 dias até hoje (intradiários) usando várias funções TABLE_DATE_RANGE |
---|
Observação: esta consulta de exemplo não vai funcionar com o conjunto de dados público do Google Analytics porque atualmente não há tabela intradiária. |
SELECT |
Exemplos de consultas básicas
Esta seção mostra como criar consultas básicas com métricas e dimensões de exemplos de dados do Google Analytics.
Total de metric] por [dimension]?
Veja abaixo exemplos de scripts para a pergunta: qual é o número total de transações geradas por navegador de dispositivos em julho de 2017?
SQL padrão
Total de transações por navegador de dispositivos em julho de 2017 |
---|
#standardSQL |
SQL legado
Total de transações por navegador de dispositivos em julho de 2017 |
---|
SELECT |
Taxa média de rejeição por [ dimension ]?
A taxa de rejeição real é definida como a porcentagem de visitas com uma única visualização de página. Confira a seguir exemplos de scripts para a pergunta: qual foi a taxa de rejeição real por origem de tráfego?
SQL padrão
Taxa de rejeição por origem de tráfego em julho de 2017 |
---|
#standardSQL |
SQL legado
Taxa de rejeição por origem de tráfego em julho de 2017 |
---|
SELECT |
Número médio de visualizações de página por tipo de comprador (compradores x não compradores)
Confira abaixo exemplos de scripts para a pergunta: qual foi o número médio de visualizações de página de produtos para quem fez uma compra em julho de 2017?
SQL padrão
Número médio de visualizações de página de produtos para quem fez uma compra em julho de 2017 |
---|
#standardSQL |
SQL legado
Número médio de visualizações de página de produtos para quem fez uma compra em julho de 2017 |
---|
SELECT |
Confira abaixo exemplos de scripts para a pergunta: qual foi o número médio de visualizações de página de produtos para quem não fez uma compra em julho de 2017?
SQL padrão
Número médio de visualizações de página de produtos para quem não fez uma compra em julho de 2017 |
---|
#standardSQL |
SQL legado
Número médio de visualizações de página de produtos para quem não fez uma compra em julho de 2017 |
---|
SELECT |
Número médio de transações por comprador
Confira abaixo exemplos de scripts para a pergunta: qual foi o total médio de transações por usuário que fez uma compra em julho de 2017?
SQL padrão
Número médio de transações por usuário que fez uma compra em julho de 2017 |
---|
#standardSQL |
SQL legado
Número médio de transações por usuário que fez uma compra em julho de 2017 |
---|
SELECT |
Valor médio gasto por sessão
Confira abaixo exemplos de scripts para a pergunta: qual é o valor médio gasto por sessão em julho de 2017?
SQL padrão
Valor médio gasto por sessão em julho de 2017 |
---|
#standardSQL |
SQL legado
Valor médio gasto por sessão em julho de 2017 |
---|
SELECT |
Sequência de hits
Confira abaixo exemplos de scripts para a pergunta: qual é a sequência das páginas visualizadas?
SQL padrão
Sequência de páginas visualizadas pelos usuários em julho de 2017 |
---|
#standardSQL |
SQL legado
Sequência de páginas visualizadas pelos usuários em julho de 2017 |
---|
SELECT |
Nessa consulta, você limita os tipos de hit a PAGES
para evitar a exibição de interações de evento ou transação. Cada linha da saída representa uma visualização de página e é mostrada pela ordem padrão dos campos na instrução SELECT
.
Várias dimensões personalizadas no nível do hit ou da sessão
Dimensão personalizada no nível do hit |
---|
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time, |
Dimensão personalizada no nível da sessão |
---|
SELECT fullVisitorId, visitId, |
Em cada consulta:
A instrução SELECT
consulta os campos de dimensões e métricas relevantes.
A função MAX
:
- Retorna a dimensão personalizada como uma nova coluna. Você pode repetir a função para retornar várias dimensões personalizadas como novas colunas.
WITHIN hits
eWITHIN RECORD
avaliam a condição em campos repetidos no BigQuery.- A condição em
MAX
é avaliada para cada dimensão personalizada. No entanto, quando a dimensão não éindex=1 (hits)
ouindex=2 (sessions)
, a condição retornaNULL
. - Retorna o valor máximo, que é o valor da dimensão personalizada 1 para hits e dimensão personalizada 2 para sessões, já que todos os outros valores são
NULL
.
Exemplos de consultas avançadas
Agora que você já conhece as consultas simples, crie consultas usando as funções e os recursos avançados disponíveis no BigQuery.
Produtos adquiridos por clientes que compraram o produto A (Comércio eletrônico clássico)
Confira abaixo o modelo de script para a pergunta: que outros produtos são adquiridos por quem compra o produto A?
Produtos adquiridos por um cliente que comprou o produto A (e-commerce clássico) |
---|
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity |
- Na primeira linha, você seleciona todos os outros itens comprados por um usuário, e a função de agregação
COUNT()
é usada para calcular a quantidade de cada item adicional comprado. O resultado aparece no campoquantity
, com o item associado no campo de produto chamadoother_purchased_products
. - Na subconsulta em cinza, você seleciona apenas os usuários únicos (
fullVisitorId
) que realizaram transações (totals.transactions>=1
) e, durante uma transação, compraram o produto A (WHERE hits.item.productName CONTAINS 'Product Item Name A')
.
As regras (instruções WHERE
e AND
) na consulta de nível superior (verde) ignoram valores em hits.item.productName
que são nulos e contêm o produto A.
Confira um exemplo da consulta Se uma pessoa comprou "Canetas metálicas Brighton - conjunto com 4", que outros produtos ela comprou?
Produtos comprados por uma pessoa que comprou "Canetas metálicas Brighton (conjunto com 4)" em 24 de junho de 2013 |
---|
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity |
No Dremel/BigQuery, usar WHERE expr IN
aciona uma JOIN, e restrições de tamanho se aplicam; especificamente, o tamanho do lado direito da instrução JOIN (neste caso, o número de visitantes) precisa ser menor que 8 MB. No Dremel, isso é chamado de broadcast JOIN. Quando o tamanho excede 8 MB, é necessário acionar uma instrução shuffled JOIN, o que pode ser feito usando a sintaxe JOIN EACH. Infelizmente, não é possível usar IN, mas a mesma consulta pode ser reescrita com a instrução JOIN.
Produtos adquiridos por clientes que compraram o produto A (e-commerce avançado)
Este exemplo é semelhante ao esqueleto da consulta anterior, mas também se aplica ao Comércio eletrônico avançado. Ele também utiliza TABLE_DATE_RANGE
para consultar dados de vários dias.
Produtos adquiridos por quem compra o produto A (e-commerce avançado) |
---|
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity |
Número médio de interações do usuário antes da compra
Este é um exemplo de uma consulta de um comando JOIN() [...] ON
, que depende apenas dos dados do Google Analytics.
Confira abaixo o modelo de script para a pergunta: qual é o número médio de interações do usuário antes de uma compra?
Número de interações do usuário antes de uma compra |
---|
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number |
- A primeira linha realiza a principal operação matemática para encontrar a média de interações do usuário por produto, e essa consulta cria uma integração entre duas subconsultas chamadas Alias_Name_1 e Alias_Name_2.
- Alias_Name_1 é usada para produzir um campo que usa a função de agregação
SUM()
para somar todos os números de hit registrados para um produto. - Alias_Name_2 é usada para encontrar o número de hits realizados por usuário e por produto, usando a função
COUNT()
. - A última linha exibe o campo comum (
hits.item.productSku
) compartilhado entre dois conjuntos de dados na integração.
Veja um exemplo da consulta Em 10 de setembro de 2013, qual é o número médio de interações do usuário antes de uma compra?
Número de interações do usuário em 10 de setembro de 2013 antes de uma compra |
---|
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number |
Porcentagem de estoque vendido por produto
Este é um exemplo de consulta que não depende apenas dos dados do Google Analytics, mas também dos dados que não são do Google Analytics. Ao combinar os dois conjuntos de dados, você começa a entender o comportamento do usuário em um nível mais segmentado. Você pode importar dados que não são do Google Analytics para o BigQuery, mas lembre-se de que isso é considerado na cobrança do armazenamento de dados mensal.
Veja abaixo o esqueleto de um script para a pergunta: qual foi a porcentagem de estoque vendido por produto?
Porcentagem de estoque vendido por produto |
---|
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold |
- A primeira linha gera dois campos: um contendo todos os IDs de produtos e outro correspondente a uma operação matemática que mostra a porcentagem de estoque vendido desse ID de produto.
- Como essa consulta depende de dois conjuntos de dados, você precisa usar a função
JOIN() ... ON
. Esse comando integra as linhas dos dois conjuntos de dados com base no campo em comum entre eles. Nesse caso, os dois conjuntos de dados são[ 'Imported_DataSet' ]
e'Alias_Name'
. [ ‘Imported_DataSet’ ]
são os dados que não são do Google Analytics. Esse é o conjunto de dados que contém o campo de métrica relacionado à quantidade de estoque que ainda está disponível (Imported DataSet.'stock_left_field'
) e o campo de dimensão do ID do produto (Imported_DataSet.'productId_field'
).Alias_Name
é o nome atribuído aos dados retornados pela subconsulta em cinza. Essa subconsulta usa os dados do Google Analytics para descobrir a quantidade total de itens vendidos por produto.- A última linha usa a instrução
ON
para mostrar o campo comum entre os dois conjuntos de dados e onde os dois conjuntos de dados são integrados.
Muitas das variáveis nessa consulta têm o nome do conjunto de dados anexado a elas como prefixo (por exemplo, Imported_DataSet.’productId_field’, Alias_Name.quantity_sold
). A finalidade é esclarecer o campo que você seleciona e tornar explícito o conjunto de dados ao qual ele pertence.
Confira um exemplo da consulta Qual porcentagem do estoque foi vendida por produto em 28 de julho de 2013?
Porcentagem de estoque vendida por produto em 28 de julho de 2013 |
---|
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold |
Lucratividade de cada produto
Confira abaixo o modelo de script para a pergunta: qual é a lucratividade de cada produto?
Lucro por produto |
---|
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit |
- A primeira linha contém a operação matemática para calcular o lucro total de cada produto.
- A subconsulta em cinza usa dados que não são do Google Analytics e coleta os dados sobre o lucro na venda do produto.
- A subconsulta em vermelho é a subconsulta de dados do Google Analytics, que será integrada aos dados não provenientes do Google Analytics. Ela calcula a quantidade de itens vendidos por produto.
- A última linha usa a instrução
ON
para deixar claro qual é o campo que os dois conjuntos de dados compartilham. Nesse caso, é o número do ID do produto.
Confira um exemplo da consulta Qual foi a lucratividade de cada produto em 28 de julho de 2013?
Lucro por produto em 28 de julho de 2013 |
---|
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit |
O lucro é calculado pela diferença entre o preço de venda e o custo de fabricação do produto. Essas informações são armazenadas no conjunto de dados que não pertence ao Google Analytics.
Lucratividade real de cada produto (considerando os reembolsos)
Confira abaixo o modelo de script para a pergunta: qual é a lucratividade real de cada produto?
Lucro real por produto |
---|
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit |
- Essa consulta é muito semelhante a Qual é a lucratividade de cada produto? As únicas diferenças estão no conjunto de dados que não é do Google Analytics, na subconsulta em cinza e na operação matemática do cálculo do lucro real, na primeira linha.
- No conjunto de dados que não é do Google Analytics, você também calcula o valor total gasto em reembolsos (na instrução
SELECT
da subconsulta em vermelho). - Em seguida, você realiza a operação matemática na linha 1 para descobrir o lucro real ao subtrair do seu lucro bruto a receita gasta com reembolsos.
Para mais informações sobre a consulta, acesse a seção sobre a lucratividade de cada produto.
Confira um exemplo da seguinte consulta: qual foi a lucratividade real de cada produto em 28 de julho de 2013?
Lucro real por produto em 28 de julho de 2013 |
---|
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit |
O lucro real leva em conta a lucratividade de um produto depois de considerar os produtos reembolsados. Para calcular a receita total de reembolso de um produto:
Receita total de reembolso de um produto = ( o preço do produto + o preço de entrega de reembolso do produto ) * a quantidade de produtos reembolsados