Manual do BigQuery

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

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 *
FROM [table name];

 

Forma correta: usar os nomes dos campos para evitar processamento desnecessário
SELECT field1, field2
FROM [table name];

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.

Atualmente, não é possível consultar dados armazenados em cache em várias tabelas usando um caractere curinga, mesmo que a opção "Usar resultados armazenados em cache" esteja marcada. Se você executar a mesma consulta de caractere curinga várias vezes, haverá cobrança para cada uma delas. Saiba mais.

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
FROM [Dataset name.table name];

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.

Query Debugging - Success

 

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.

Query Debugging - Error

 

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
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160802`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160803`
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
SQL legado
3 dias usando nomes de tabelas separados por vírgula
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
[bigquery-public-data.google_analytics_sample.ga_sessions_20160801],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160802],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160803]
GROUP BY
date
ORDER BY
date ASC

 

Últimos 1.095 dias

SQL padrão
Últimos 1.095 dias usando _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
SQL legado
Últimos 1.095 dias usando TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -1095, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Últimos 36 meses

SQL padrão
Últimos 36 meses usando _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
SQL legado
Últimos 36 meses usando TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -36, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Últimos 3 anos

SQL padrão
Últimos 3 anos usando _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
SQL legado
Últimos 3 anos usando TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Período específico

SQL padrão
Período específico usando _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
SQL legado
Período específico usando TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2016-08-01'), TIMESTAMP('2017-07-31')))
GROUP BY
date
ORDER BY
date ASC

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
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

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
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_intraday_],
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

 

 

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
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

SQL legado

Total de transações por navegador de dispositivos em julho de 2017
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
device.browser
ORDER BY
total_transactions DESC

 

 

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
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

SQL legado

Taxa de rejeição por origem de tráfego em julho de 2017
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
source )
ORDER BY
total_visits DESC

 

 

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
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

SQL legado

Número médio de visualizações de página de produtos para quem fez uma compra em julho de 2017
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions >=1
GROUP BY
users )

 

 

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
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

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
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

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
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

SQL legado

Número médio de transações por usuário que fez uma compra em julho de 2017
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

 

 

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
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

SQL legado

Valor médio gasto por sessão em julho de 2017
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

 

 

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
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

SQL legado

Sequência de páginas visualizadas pelos usuários em julho de 2017
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

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,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

Dimensão personalizada no nível da sessão
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

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 e WITHIN 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) ou index=2 (sessions), a condição retorna NULL.
  • 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
FROM [‘Dataset Name’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Dataset Name’ ]
  WHERE hits.item.productName CONTAINS 'Product Item Name A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Product Item Name A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. 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 campo quantity, com o item associado no campo de produto chamado other_purchased_products.
  2. 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
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

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
FROM SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14'))
WHERE hits.product.productSKU CONTAINS '10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY fullVisitorId )
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

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
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;
  1. 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.
  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.
  3. Alias_Name_2 é usada para encontrar o número de hits realizados por usuário e por produto, usando a função COUNT().
  4. 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
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku  ) AS one
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS two
ON one.hits.item.productSku = two.hits.item.productSku;

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
FROM [ ‘Imported_DataSet’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘GA Dataset’ ]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS ‘Alias_Name’
ON Imported_DataSet.’productId_field’ = Alias_Name.hits.item.productSku;
  1. 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.
  2. 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'.
  3. [ ‘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').
  4. 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.
  5. 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
FROM AnalyticsImport.product_data_20130728
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM GoogleStore.ga_sessions_20130728
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS one
ON AnalyticsImport.product_data_20130728.productId = one.hits.item.productSku
ORDER BY percentage_of_stock_sold DESC;

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
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘Imported Data Set’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘GA Dataset Name’ ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku );
  1. A primeira linha contém a operação matemática para calcular o lucro total de cada produto.
  2. 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.
  3. 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.
  4. 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
FROM (
  SELECT two.hits.item.productSku, AnalyticsImport.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice
  FROM AnalyticsImport.product_data_20130728
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM GoogleStore.ga_sessions_20130728
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS two
  ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku );

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
FROM (
  SELECT Alias_Name.hits.item.productSku, ( ( Imported_DataSet.productprice - Imported_DataSet.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Imported_DataSet.refunddeliveryprice + Imported_DataSet.productprice ) * Imported_DataSet.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT Alias_Name.hits.item.productSku, Imported_DataSet.productcost, Alias_Name.quantity, Imported_DataSet.productprice, Imported_DataSet.refunddeliveryprice, Imported_DataSet.refundquantity
    FROM [ ‘Imported DataSet Name’ ] AS 'Imported_DataSet'
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘GA Dataset Name’ ]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS 'Alias_Name'
    ON Imported_DataSet.productId = Alias_Name.hits.item.productSku ) );
  1. 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.
  2. No conjunto de dados que não é do Google Analytics, você também calcula o valor total gasto em reembolsos (na instruçãoSELECT da subconsulta em vermelho).
  3. 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
FROM (
  SELECT two.hits.item.productSku, ( ( AnalyticsImport.product_data_20130728.productprice - AnalyticsImport.product_data_20130728.productcost ) * two.quantity ) AS gross_profit, ( ( AnalyticsImport.product_data_20130728.refunddeliveryprice + AnalyticsImport.product_data_20130728.productprice ) * AnalyticsImport.product_data_20130728.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT two.hits.item.productSku, Analytics.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice, AnalyticsImport.product_data_20130728.refunddeliveryprice, AnalyticsImport.product_data_20130728.refundquantity
    FROM AnalyticsImport.product_data_20130728
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM GoogleStore.ga_sessions_20130728
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS two
    ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku ) );

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

Isso foi útil?

Como podemos melhorá-lo?

Precisa de mais ajuda?

Siga as próximas etapas:

Postar na Comunidade de Ajuda Receber respostas dos membros da comunidade
Fale conosco Conte mais sobre o problema para podermos ajudar você
true
learning path
Como escolher o caminho de aprendizado ideal para você

Confira google.com/analytics/learn, um novo recurso para aproveitar ao máximo o Google Analytics 4. O novo site inclui vídeos, artigos e fluxos guiados, além de outros links referentes ao Analytics, como Discord, blog, canal do YouTube e repositório do GitHub.

Comece a aprender hoje

Pesquisa
Limpar pesquisa
Fechar pesquisa
Menu principal
18134347723262370566
true
Pesquisar na Central de Ajuda
true
true
true
true
true
69256
false
false