Guia prático do BigQuery

Esta funcionalidade só está disponível no Analytics 360, que faz parte da Google Marketing Platform.
Saiba mais acerca da Google Marketing Platform.

Este artigo contém exemplos de como construir consultas dos dados do Analytics exportados para o BigQuery. Disponibilizámos um conjunto de dados de exemplo para poder praticar com algumas das consultas neste artigo.

Neste artigo:

Otimização de consultas

Cada consulta executada contribui para a sua quota mensal de processamento de dados. Se selecionar campos externos, aumenta a quantidade de dados que é necessário processar. Consequentemente, utiliza ainda mais da sua quota mensal do que o necessário. As consultas otimizadas usam de forma eficiente a quota mensal de processamento de dados.

Saiba mais acerca dos preços.

Selecionar apenas o necessário

Quando formula uma consulta, seleciona apenas os campos relevantes na instrução SELECT. Ao não chamar campos externos, está a reduzir a quantidade de dados e o tempo necessário para processar a consulta.

Exemplo: evite a utilização do operador caráter universal

Forma incorreta: com o operador caráter universal
SELECT *
FROM [table name];

 

Forma mais correta: com nomes de campos para evitar processamento desnecessário
SELECT field1, field2
FROM [table name];

Permita a colocação em cache

Sempre que possível, evite utilizar funções como campos. As funções (como NOW() ou TODAY()) devolvem resultados variáveis, o que impede que as consultas sejam colocadas em cache e, por isso, que terminem mais rapidamente. Em alternativa, use horas e datas específicas.

Neste momento, não é possível colocar em cache os resultados das consultas de várias tabelas que usem um caráter universal, mesmo que a opção "Usar resultados em cache" esteja selecionada. Se executar várias vezes a mesma consulta com um caráter universal, terá de pagar cada consulta. Saiba mais

Use tabelas intermédias para subconsultas usadas frequentemente

Se perceber que utiliza repetidamente uma consulta específica como subconsulta, pode guardá-la como uma tabela intermédia ao clicar em Guardar como tabela acima dos resultados da consulta. Em seguida, pode referenciar esta tabela na secção FROM da sua consulta, o que reduzirá a quantidade de dados a processar e o tempo necessário para o processamento.

usar uma tabela intermédia
SELECT field1, field2
FROM [Dataset name.table name];

Depuração de consultas

O BigQuery depura o seu código à medida que o constrói. Na janela de composição, a depuração é indicada logo abaixo da consulta. A depuração está também disponível através da API com o sinalizador dryRun.

As consultas válidas têm um indicador verde que, quando clicado, apresentará a quantidade de dados processada pela consulta. Esta funcionalidade permite considerar a otimização de dados antes de executar a consulta, para evitar processamento de dados desnecessário.

Query Debugging - Success

 

As consultas inválidas têm um indicador vermelho em que pode clicar para ver informações acerca do erro e que localiza a linha e a coluna em que o erro está a ocorrer. No exemplo abaixo, a instrução GROUP BY está em branco e o erro é identificado.

Query Debugging - Error

 

Sugestões e práticas recomendadas

Usar o conjunto de dados de exemplo

Os seguintes exemplos usam o conjunto de dados de exemplo do Google Analytics.

Para utilizar as consultas nos seus próprios dados, basta substituir os nomes do projeto e do conjunto de dados nos exemplos pelos seus nomes do projeto e do conjunto de dados.

Usar o SQL padrão vs. o SQL antigo

O BigQuery suporta dois dialetos de SQL:

O artigo Migrar para o SQL padrão explica as diferenças entre os dois dialetos.

O SQL padrão agora é o dialeto de SQL preferido para consultar dados armazenados no BigQuery.

Consulte o artigo Ativar o SQL padrão para obter informações acerca de como ativar o SQL padrão na IU, CLI, API do BigQuery ou em qualquer interface que use.

A forma mais fácil de começar é incluir o comentário "standardSQL" na parte superior das consultas SQL padrão, conforme apresentado nos exemplos a seguir.

Com o SQL antigo, os dados do Google Analytics 360 são transferidos para uma nova tabela todos os dias. Para consultar várias tabelas de uma vez, pode separar os nomes das tabelas por vírgulas, usar a função de caráter universal da tabela TABLE_DATE_RANGE ou usar várias funções TABLE_DATE_RANGE separadas por vírgulas, como nos exemplos seguintes.

Consultar várias tabelas

Os seguintes exemplos mostram consultas de SQL padrão e SQL antigo para os mesmos dados.

3 dias

SQL padrão
3 dias com 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 antigo
3 dias com nomes de tabelas separados por vírgulas
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 1095 dias

SQL padrão
Últimos 1095 dias com _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 antigo
Últimos 1095 dias com 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 com _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 antigo
Últimos 36 meses com 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 com _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 antigo
Últimos 3 anos com 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

 

Intervalo de datas específico

SQL padrão
Intervalo de datas específico com _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 antigo
Intervalo de datas específico com 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

Últimos 3 anos juntamente com os dados de hoje (intradiários)

SQL padrão
Últimos 3 anos juntamente com os dados de hoje (intradiários) com UNION ALL e _TABLE_SUFFIX
Nota: esta consulta de exemplo não funciona com o conjunto de dados público do Google Analytics porque não há atualmente uma tabela de dados intradiários.
#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 antigo

Últimos 3 anos juntamente com os dados de hoje (intradiários) com várias funções TABLE_DATE_RANGE
Nota: esta consulta de exemplo não funciona com o conjunto de dados público do Google Analytics porque não há atualmente uma tabela de dados intradiários.
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 secção explica como construir consultas básicas com métricas e dimensões de dados de exemplo do Analytics.

Total de [métrica] por [dimensão]?

Veja abaixo exemplos de scripts para a pergunta: Qual é o número total de transações geradas por navegador de dispositivo em julho de 2017?

SQL padrão

Total de transações por navegador de dispositivo 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 antigo

Total de transações por navegador de dispositivo 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 de rejeição média por [dimensão]?

A taxa de rejeição real é definida como a percentagem de visitas com uma única visualização de página. Veja abaixo exemplos de scripts para a pergunta: Qual foi a taxa de rejeição real por origem do tráfego?

SQL padrão

Taxa de rejeição por origem do 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 antigo

Taxa de rejeição por origem do 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 de produtos por tipo de comprador (compradores vs. não compradores)

Veja abaixo exemplos de scripts para a pergunta: Qual foi o número médio de visualizações de página de produtos de utilizadores que fizeram uma compra em julho de 2017?

SQL padrão

Número médio de visualizações de página de produtos de utilizadores 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 antigo

Número médio de visualizações de página de produtos de utilizadores 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 visualizações de página de produtos de utilizadores que não fizeram uma compra em julho de 2017?

SQL padrão

Número médio de visualizações de página de produtos de utilizadores 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 antigo

Número médio de visualizações de página de produtos de utilizadores 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 de transações, em média, por utilizador que efetuou uma compra em julho de 2017?

SQL padrão

Número médio de transações por utilizador 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 antigo

Número médio de transações por utilizador 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 )

 

 

Montante médio gasto por sessão

Veja abaixo exemplos de scripts para a pergunta: Qual é o montante médio gasto por sessão em julho de 2017?

SQL padrão

Montante 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 antigo

Montante 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 resultados

Veja abaixo exemplos de scripts para a pergunta: Qual é a sequência de páginas visualizadas?

SQL padrão

Sequência de páginas visualizadas por utilizadores 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 antigo

Sequência de páginas visualizadas por utilizadores 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

Nesta consulta, limita os tipos de resultados a PAGES para evitar ver interações de eventos ou transações. Cada linha do resultado representa uma visualização de página e é apresentada pela ordem predefinida dos campos na declaração SELECT.

 

 

Múltiplas dimensões personalizadas ao nível do resultado ou da sessão

dimensão personalizada ao nível do resultado
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 ao 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 declaração SELECT consulta os campos de dimensão e de métrica relevantes.

A função MAX:

  • Devolve a dimensão personalizada como uma nova coluna. Pode repetir a função para devolver várias dimensões personalizadas como novas colunas.
  • WITHIN hits e WITHIN RECORD avaliam a condição dentro de campos repetidos no BigQuery.
  • A condição em MAX é avaliada para cada dimensão personalizada, mas para aquelas em que não se verificar index=1 (hits) ou index=2 (sessions) é devolvido NULL.
  • Devolve o valor máximo, que é o valor da dimensão personalizada 1 para resultados ou da dimensão personalizada 2 para sessões, uma vez que todos os outros valores são NULL.

Exemplos de consultas avançadas

Agora que já conhece as consultas simples, pode construir consultas com as funções e as funcionalidades avançadas disponíveis no BigQuery.

Produtos comprados por clientes que compraram o produto A (Comércio eletrónico clássico)

Segue-se o esboço de um script para a pergunta: Que outros produtos são comprados por clientes que compram o produto A?

produtos comprados por um cliente que compra 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, seleciona todos os outros artigos comprados por um utilizador e a função de agregação COUNT() é usada para calcular a quantidade de cada um dos restantes artigos comprados. O resultado é apresentado num campo denominado quantity, com o respetivo artigo associado no campo de produto denominado other_purchased_products.
  2. Na subconsulta a cinzento, apenas seleciona os utilizadores únicos (fullVisitorId) que realizaram transações (totals.transactions>=1) e que, durante uma transação, compraram o produto A (WHERE hits.item.productName CONTAINS 'Product Item Name A').

As regras (declarações WHERE e AND) na consulta de nível superior (verde) ignoram valores em hits.item.productName que sejam nulos e contenham o produto A.

Segue-se um exemplo da consulta: Se um cliente compra Brighton Metallic Pens - Set of 4, que outro(s) produto(s) comprou?

produtos comprados por um cliente que compra "Brighton Metallic Pens (Set of 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, a utilização de WHERE expr IN aciona um comando JOIN e aplicam-se restrições de tamanho; especificamente, o tamanho do lado direito de JOIN (neste caso, o número de visitantes) tem de ser inferior a 8 MB. No Dremel, isto tem a designação de broadcast JOIN. Quando o tamanho ultrapassa os 8 MB, tem de acionar um comando shuffled JOIN, o que pode ser feito com a sintaxe JOIN EACH. Infelizmente, não é possível fazê-lo com IN, mas a mesma consulta pode ser reescrita com JOIN.

Produtos comprados por clientes que compraram o produto A (Comércio eletrónico otimizado)

É semelhante ao esboço da consulta anterior, mas funciona para o Comércio eletrónico otimizado. Também usa TABLE_DATE_RANGE para consultar dados em vários dias.

produtos comprados por um cliente que compra o produto A (Comércio eletrónico otimizado)
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 utilizador antes da compra

Este é um exemplo de uma consulta de um comando JOIN() [...] ON que só depende de dados do Analytics.

Segue-se o esboço de um script para a pergunta: Qual é o número médio de interações do utilizador antes de uma compra?

número de interações do utilizador 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 operação matemática principal para encontrar a média de interações do utilizador por produto e esta consulta cria uma associação entre duas subconsultas denominadas "Alias_Name_1" e "Alias_Name_2".
  2. "Alias_Name_1" é utilizada para produzir um campo que utiliza a função de agregação SUM() para somar todos os números de resultados registados para um produto.
  3. "Alias_Name_2" é utilizada para encontrar o número de resultados de utilizadores por produto com a função COUNT().
  4. A última linha mostra o campo comum (hits.item.productSku) partilhado entre os dois conjuntos de dados na associação.

Segue-se um exemplo da consulta: Em 10 de setembro de 2013, qual é o número médio de interações do utilizador antes de uma compra?

número de interações do utilizador 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;

Percentagem de stock vendida por produto

Este é um exemplo de uma consulta que depende, não apenas de dados do Analytics, mas também de dados que não são do Analytics. A combinação de ambos os conjuntos de dados permite começar a compreender o comportamento do utilizador a um nível mais segmentado. Pode importar dados que não são do Analytics para o BigQuery, mas tenha em atenção que tal vai contribuir para a sua quota de armazenamento de dados mensal.

Segue-se o esboço de um script para a pergunta: Que percentagem de stock foi vendida por produto?

percentagem de stock vendida 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 tem como resultado dois campos. Um campo contém todos os IDs de produtos e o outro é uma operação matemática que mostra a percentagem de stock vendida para esse ID do produto.
  2. Uma vez que esta consulta depende de dois conjuntos de dados, tem de usar a função JOIN() ... ON. Este comando associa as linhas dos dois conjuntos de dados com base no campo comum entre eles. Neste caso, os dois conjuntos de dados são [ ‘Imported_DataSet’ ] e ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] são os dados que não pertencem ao Analytics. Este é o conjunto de dados que contém o campo de métrica relativo ao stock restante (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 devolvidos pela subconsulta a cinzento. Esta subconsulta utiliza dados do Google Analytics para calcular a quantidade total de artigos vendidos por produto.
  5. A última linha usa a declaração ON para mostrar qual é o campo comum entre os dois conjuntos de dados e onde são associados os dois conjuntos de dados.

Muitas das variáveis existentes nesta consulta têm o nome do respetivo conjunto de dados anexado como prefixo (por exemplo, Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Isto destina-se a clarificar o campo que está a selecionar e a explicitar o conjunto de dados a que pertence.

Segue-se um exemplo da consulta: Que percentagem de stock foi vendida por produto em 28 de julho de 2013?

percentagem de stock 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;

Rentabilidade de cada produto

Segue-se o esboço de um script para a pergunta: Qual é a rentabilidade 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 de cálculo do lucro total realizado em cada produto.
  2. A subconsulta a cinzento utiliza dados que não são do Analytics, que recolhe os dados sobre o montante do lucro realizado quando um produto é vendido.
  3. A subconsulta a vermelho é a subconsulta de dados do Analytics, que serão associados aos dados que não são do Analytics. Calcula a quantidade de artigos vendidos por produto.
  4. A última linha usa a declaração ON para clarificar o campo que os dois conjuntos de dados partilham. Neste caso, trata-se do número de ID do produto.

Segue-se um exemplo da consulta: Qual foi a rentabilidade 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 através da diferença entre o preço a que o produto é vendido e o respetivo custo de produção. Estas informações são armazenadas no conjunto de dados que não é do Google Analytics.

Rentabilidade real de cada produto (tendo em conta os reembolsos)

Segue-se o esboço de um script para a pergunta: Qual é a rentabilidade 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. Esta consulta é muito semelhante a Qual é a rentabilidade de cada produto? As únicas diferenças estão no conjunto de dados que não são do Analytics, na subconsulta a cinzento, e na operação matemática para o cálculo do lucro real, na primeira linha.
  2. No conjunto de dados que não são do Analytics, também está a calcular o montante total gasto em reembolsos (na declaração SELECT da subconsulta a vermelho).
  3. Em seguida, realiza uma operação matemática na linha 1 para encontrar o lucro real, ao subtrair a receita gasta em reembolsos do lucro bruto.

Para mais informações sobre a consulta, vá para a secção sobre a rentabilidade de cada produto.

Veja um exemplo da seguinte consulta: Qual foi a rentabilidade 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 tem em conta a rentabilidade de um produto após a consideração dos produtos reembolsados. Para calcular o total de receita reembolsada de um produto:

total de receita reembolsada de um produto = (o preço do produto + o preço de reembolso do produto) * a quantidade de produtos reembolsados

A informação foi útil?

Como podemos melhorá-la?

Precisa de mais ajuda?

Experimente estes passos seguintes:

Postar na Comunidade de Ajuda Receber respostas dos membros da comunidade
Contacte-nos Forneça-nos mais informações e iremos ajudá-lo a encontrar o que procura.
true
learning path
Escolha o seu percurso de aprendizagem

Visite google.com/analytics/learn, um novo recurso para ajudar a tirar o máximo partido do Google Analytics 4. O novo Website inclui vídeos, artigos e fluxos guiados, além de fornecer links para o Discord, o blogue, o canal do YouTube e o repositório do GitHub do Google Analytics.

Comece a aprender hoje!

Pesquisa
Limpar pesquisa
Fechar pesquisa
Menu principal
1219770183916622330
true
Pesquisar no Centro de ajuda
true
true
true
true
true
69256
false
false