Manual do BigQuery

Esse recurso está disponível apenas no Google Analytics 360, que faz parte da Google Marketing Platform.
Saiba mais sobre essa plataforma.

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, aumentará a quantidade de dados que precisam ser 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: evite o uso do operador de caractere curinga

Forma incorreta: uso do operador de caractere curinga
SELECT *
FROM [table name];

 

Forma correta: uso dos 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 rapidamente. Em vez disso, use horas e datas específicas.

Use 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. Então, você poderá consultar essa tabela na seção FROM da sua consulta, o que reduzir a quantidade de dados que tem de ser processada e o tempo necessário para o processamento.

Como 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. A depuração também está disponível por meio da 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 oferece a oportunidade de otimizar seus dados antes de executar a consulta para evitar o processamento de dados desnecessário.

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

Como 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 Migração 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 Como 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 superior 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 tabelas de uma só vez, você pode separar os nomes delas por vírgulas. Para isso, use a função de caractere curinga TABLE_DATE_RANGE ou as funções separadas por vírgulas TABLE_DATE_RANGE, conforme os exemplos a seguir.

Consulta de várias tabelas

Os exemplos a seguir mostram consultas com SQL padrão e SQL legado para os mesmos dados.

Três dias

SQL padrão
Três 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
Três 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 Days

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 três anos

SQL padrão
Últimos três 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 três 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 três anos até hoje (intradiário)

SQL padrão
Dados dos três últimos anos até hoje (intradiário) usando UNION ALL & _TABLE_SUFFIX
Observação: esta consulta de exemplo não 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 três últimos dias até hoje (intradiários) usando várias funções TABLE_DATE_RANGE
Observação: esta consulta de exemplo não 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 um único page view. Veja 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 page views de produtos por tipo de comprador (compradores x não compradores)

Veja abaixo exemplos de scripts para a pergunta: qual foi o número médio de page views de produtos para usuários que fizeram uma compra em julho de 2017?

SQL padrão

Número médio de page views de produtos para usuários que fizeram 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 page views de produtos para usuários que fizeram 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 )

 

 

Veja abaixo exemplos de scripts para a pergunta: qual foi o número médio de page views de produtos para usuários que não fizeram uma compra em julho de 2017?

SQL padrão

Número médio de page views de produtos para usuários que não fizeram 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 page views de produtos para usuários que não fizeram 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

Veja 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

Veja 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

Veja 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 exibiçã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 ou dimensão personalizada 2 para sessões, já que todos os outros valores são NULL.

Exemplos de consultas avançadas

Agora que você está familiarizado com consultas simples, já pode criar 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)

Veja abaixo o esqueleto de um script para a pergunta: que outros produtos são adquiridos por clientes que compram o produto A?

Produtos adquiridos por um cliente que comprou o produto A (Comércio eletrônico 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 é então exibido em um campo, chamado de 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.

Veja um exemplo da consulta Se um cliente comprou "Canetas metálicas Brighton - conjunto com 4", que outros produtos ele comprou?

Produtos comprados por um cliente 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 instrução JOIN e restrições de tamanho são aplicáveis, especificamente o tamanho do lado direito da instrução JOIN (neste caso, o número de visitantes) precisa ser inferior a 8 MB. No Dremel, isso é chamado de broadcast JOIN. Quando o tamanho excede 8 MB, é necessário acionar uma instrução shuffled JOIN, por meio da 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 (Comércio eletrônico 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 um cliente que compra o produto A (Comércio eletrônico 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 da consulta de um comando JOIN() [...] ON que depende apenas dos dados do Google Analytics.

Veja abaixo o esqueleto de um 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.

Veja 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

Veja abaixo o esqueleto de um 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.

Veja 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 do produto e o custo de fabricação dele. 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)

Veja abaixo o esqueleto de um 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 declaração SELECT 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.

Veja 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 da entrega do reembolso do produto) * a quantidade de produtos reembolsados

Isso foi útil?
Como podemos melhorá-lo?