Guía de BigQuery

Esta función solo está disponible en Analytics 360, que forma parte de Google Marketing Platform.
Más información sobre Google Marketing Platform

En este artículo se incluyen ejemplos de cómo crear consultas de los datos de Analytics que se exportan a BigQuery. Disponemos de un conjunto de datos de muestra para que pueda practicar con las consultas de este artículo.

Secciones de este artículo:

Optimización de consultas

Cada consulta que ejecuta contribuye a la cuota mensual de procesamiento de datos. Si selecciona campos irrelevantes, se aumenta la cantidad de datos que se deben procesar y, por lo tanto, usa más de su cuota mensual de la que es necesaria. Las consultas optimizadas hacen un uso eficaz de la cuota mensual de procesamiento de datos.

Más información sobre los precios

Seleccionar solo lo necesario

Al formular una consulta, seleccione los campos relevantes en la instrucción SELECT. Si no llama a campos irrelevantes, se reducirá el volumen de datos y el tiempo necesario para procesar la consulta.

Ejemplo: evite el uso del operador comodín

Método incorrecto: usar el operador comodín
SELECT *
FROM [nombre de tabla];

 

Método adecuado: usar nombres de campo para evitar el procesamiento innecesario
SELECT field1, field2
FROM [nombre de tabla];

Permitir el almacenamiento en la memoria caché

Siempre que sea posible, evite el uso de funciones como campos. Las funciones (como NOW() o TODAY()) devuelven resultados variables, lo que impide que las consultas se almacenen en la memoria caché y, por lo tanto, se devuelvan más rápidamente. En su lugar, utilice horas y fechas específicas.

Usar tablas intermedias para subconsultas usadas habitualmente

Si utiliza de forma continua una determinada consulta como una subconsulta, puede guardar la consulta como una tabla intermedia haciendo clic en Guardar como tabla encima de los resultados de la consulta. Posteriormente puede hacer referencia a esa tabla en la sección FROM de la consulta, lo que reducirá tanto la cantidad de datos que se deben procesar como el tiempo necesario para el procesamiento.

uso de una tabla intermedia
SELECT field1, field2
FROM [nombre de conjunto de datos.nombre de tabla];

Depuración de consultas

BigQuery depura el código a medida que se crea. En la ventana de creación, la depuración se indica debajo de la consulta. La depuración también está disponible mediante la API con la marca dryRun.

Las consultas válidas tienen un indicador de color verde en el que puede hacer clic para ver la cantidad de datos que han procesado. Gracias a esta función puede optimizar los datos antes de ejecutar la consulta para evitar el procesamiento de datos innecesarios.

Query Debugging - Success

 

Las consultas no válidas tienen un indicador de color rojo en el que puede hacer clic para ver la información del error y encontrar la línea y la columna donde se produce. En el ejemplo siguiente, la instrucción GROUP BY está en blanco y se ha destacado el error.

Query Debugging - Error

 

Consejos y prácticas recomendadas

Utilizar el conjunto de datos de muestra

En los ejemplos que se muestran a continuación se utiliza el conjunto de datos de muestra de Google Analytics.

Para usar las consultas con sus datos, cambie los nombres de proyectos y conjuntos de datos de los ejemplos por los suyos.

Utilizar SQL estándar o SQL antiguo

BigQuery admite dos dialectos SQL:

En este artículo sobre cómo migrar a SQL estándar se explican las diferencias entre los dos dialectos.

SQL estándar se ha convertido en el dialecto SQL preferido para consultar datos almacenados en BigQuery.

Consulte más información en este artículo sobre cómo habilitar SQL estándar en la IU, CLI, API o cualquier otra interfaz de BigQuery que utilice.

Para empezar, solo tiene que incluir el comentario "standardSQL" en la parte superior de sus consultas SQL estándar, como se muestra en los ejemplos de abajo.

Con SQL antiguo, los datos de Google Analytics 360 se transfieren a una nueva tabla cada día. Para consultar varias tablas a la vez, separe sus nombres con comas o utilice la función de comodín de tabla TABLE_DATE_RANGE o varias funciones TABLE_DATE_RANGE separadas por comas, como en los ejemplos de abajo.

Consultar varias tablas

En los siguientes ejemplos se muestran consultas de los mismos datos con SQL estándar y SQL antiguo.

3 días

SQL estándar
Datos de 3 días con la función 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 antiguo
Datos de 3 días con nombres de tablas separados por comas
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 días

SQL estándar
Datos de los últimos 1095 días con la función _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 antiguo
Datos de los últimos 1095 días con la función 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 estándar
Datos de los últimos 36 meses con la función _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 antiguo
Datos de los últimos 36 meses con la función 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 años

SQL estándar
Datos de los últimos 3 años con la función _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 antiguo
Datos de los últimos 3 años con la función 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

 

Periodo concreto

SQL estándar
Datos de un periodo concreto con la función _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 antiguo
Datos de un periodo concreto con la función 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 años y hoy (a lo largo del día)

SQL estándar
Datos de los últimos 3 años y de hoy (a lo largo del día) con las funciones UNION ALL y _TABLE_SUFFIX
Nota: Esta consulta de ejemplo no funciona con el conjunto de datos público de Google Analytics porque actualmente no hay ninguna tabla intradiaria.
#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 antiguo

Datos de los últimos 3 años y de hoy (a lo largo del día) con varias funciones TABLE_DATE_RANGE
Nota: Esta consulta de ejemplo no funciona con el conjunto de datos público de Google Analytics porque actualmente no hay ninguna tabla intradiaria.
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

 

 

Ejemplos de consultas básicas

En esta sección se explica cómo crear consultas básicas con métricas y dimensiones de datos de ejemplo de Analytics.

¿Total de [métrica] por [dimensión]?

A continuación se muestran las secuencias de comandos de ejemplo para la pregunta: ¿Cuál fue el número total de transacciones generadas por navegador de dispositivo en julio del 2017?

SQL estándar

Total de transacciones por navegador de dispositivo en julio del 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 antiguo

Total de transacciones por navegador de dispositivo en julio del 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

 

 

¿Porcentaje de rebote medio por [dimensión]?

El porcentaje de rebote real se define como el porcentaje de visitas con una sola página vista. A continuación se muestran las secuencias de comandos de ejemplo para la pregunta: ¿Cuál fue el porcentaje de rebote real por fuente de tráfico?

SQL estándar

Porcentaje de rebote por fuente de tráfico en julio del 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 antiguo

Porcentaje de rebote por fuente de tráfico en julio del 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 medio de páginas vistas de producto por tipo de comprador (compradores frente a no compradores)

A continuación se muestran las secuencias de comandos de ejemplo para la pregunta: ¿Cuál fue el número medio de páginas vistas de producto de los usuarios que realizaron una compra en julio del 2017?

SQL estándar

Número medio de páginas vistas de producto de los usuarios que realizaron una compra en julio del 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 antiguo

Número medio de páginas vistas de producto de los usuarios que realizaron una compra en julio del 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 )

 

 

A continuación se muestran las secuencias de comandos de ejemplo para la pregunta: ¿Cuál fue el número medio de páginas vistas de producto de los usuarios que no realizaron una compra en julio del 2017?

SQL estándar

Número medio de páginas vistas de producto de los usuarios que no hicieron una compra en julio del 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 antiguo

Número medio de páginas vistas de producto de los usuarios que no hicieron una compra en julio del 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 medio de transacciones por comprador

A continuación se muestran las secuencias de comandos de ejemplo para la pregunta: ¿Cuál fue la media total de transacciones por usuario que hizo una compra en julio del 2017?

SQL estándar

Número medio de transacciones por usuario que hizo una compra en julio del 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 antiguo

Número medio de transacciones por usuario que hizo una compra en julio del 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 )

 

 

Importe medio de gasto por sesión

A continuación se muestran las secuencias de comandos de ejemplo para la pregunta: ¿Cuál fue el importe medio de gasto por sesión en julio del 2017?

SQL estándar

Importe medio de gasto por sesión en julio del 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 antiguo

Importe medio de gasto por sesión en julio del 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 )

 

 

Secuencia de hits

A continuación se muestran las secuencias de comandos de ejemplo para la pregunta: ¿Cuál fue la secuencia de páginas vistas?

SQL estándar

Secuencia de páginas vistas por los usuarios en julio del 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 antiguo

Secuencia de páginas vistas por los usuarios en julio del 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

En esta consulta se limitan los tipos de hits a PAGES para evitar que se muestren interacciones de eventos o de transacciones. Cada línea del resultado representa una página vista y se muestra en el orden predeterminado de los campos de la instrucción SELECT.

 

 

Varias dimensiones personalizadas de hit o sesión

dimensión personalizada de 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

 

dimensión personalizada de sesión
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

En cada consulta:

La instrucción SELECT consulta los campos de dimensiones y métricas relevantes.

La función MAX:

  • Devuelve la dimensión personalizada como una columna nueva. Puede repetir la función para que devuelva varias dimensiones personalizadas como columnas nuevas.
  • Con WITHIN hits y WITHIN RECORD se evalúa la condición de los campos repetidos en BigQuery.
  • La condición de MAX se evalúa para cada dimensión personalizada, pero para las que no son index=1 (hits) o index=2 (sessions) devuelve NULL.
  • Devuelve el valor máximo, que es el valor de la dimensión personalizada 1 para los hits o la dimensión personalizada 2 para las sesiones, ya que todos los demás valores son NULL.

Ejemplos de consultas avanzadas

Ahora que ya conoce las consultas básicas, puede construir consultas con las funciones avanzadas disponibles en BigQuery.

Productos adquiridos por los clientes que han comprado el producto A (comercio electrónico clásico)

A continuación, se muestra el esquema de la secuencia de comandos de la pregunta ¿Qué otros productos han adquirido los clientes que han comprado el producto A?

productos adquiridos por un cliente que ha comprado el producto A (comercio electrónico clásico)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [‘nombre del conjunto de datos’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘nombre de conjunto de datos’ ]
  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. En la primera línea se seleccionan todos los demás productos que ha comprado un usuario y la función de agregado COUNT() se utiliza para calcular la cantidad de dichos productos. El resultado se muestra en el campo quantity, con su producto asociado en el campo de producto other_purchased_products.
  2. En la subconsulta de color gris solo se seleccionan los usuarios únicos (fullVisitorId) que han realizado transacciones (totals.transactions>=1) y durante una transacción han comprado el producto A (WHERE hits.item.productName CONTAINS 'Product Item Name A').

Las reglas (instrucciones WHERE y AND) de la consulta de nivel superior (de color verde) descartan los valores de hits.item.productName que son nulos o contienen el producto A.

A continuación se ofrece un ejemplo de la consulta Si un cliente compra Bolígrafos metálicos (juego de 4), ¿qué otros productos compra?

productos adquiridos por un cliente que compra "Bolígrafos metálicos (juego de 4)" el 24 de junio 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 'Bolígrafos metálicos (juego de 4)'
   AND totals.transactions>=1
  GROUP BY fullVisitorId
)
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Bolígrafos metálicos - Juego de 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

En Dremel/BigQuery, usar WHERE expr IN activa una instrucción JOIN, y se aplican las restricciones de tamaño, en concreto, el tamaño de la parte derecha de JOIN (en este caso, el número de visitantes) debe ser menor de 8 MB. En Dremel, se denomina JOIN de difusión. Cuando el tamaño supera los 8 MB, debe activar una instrucción JOIN aleatoria, que se puede realizar con la sintaxis JOIN EACH syntax. Lamentablemente, no se puede realizar con IN, pero la misma consulta se puede rescribir con una instrucción JOIN.

Productos adquiridos por los clientes que han comprado el producto A (comercio electrónico mejorado)

Este esquema es similar al de la consulta anterior, pero se puede utilizar para el comercio electrónico mejorado. También utiliza la función TABLE_DATE_RANGE para consultar datos de varios días.

productos adquiridos por un cliente que ha comprado el producto A (comercio electrónico mejorado)
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 medio de interacciones de usuario antes de la compra

Esto es un ejemplo de una consulta con un comando JOIN() [...] ON, que solo depende de los datos de Analytics.

A continuación, se muestra el esquema de la secuencia de comandos correspondiente a la pregunta ¿Cuál es el número medio de interacciones de usuario antes de una compra?

número de interacciones de usuario antes de la 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 [‘nombre de conjunto de datos de Google Analytics’ ]
 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 [‘nombre de conjunto de datos de Google Analytics’ ]
 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. En la primera línea se realiza la operación matemática principal para encontrar la media de interacciones de usuario por producto y esta consulta crea una unión entre las dos subconsultas "Alias_Name_1" y "Alias_Name_2".
  2. "Alias_Name_1" se utiliza para crear un campo que usa la función de agregado SUM() para sumar todas las cantidades de hits registradas para un producto.
  3. "Alias_Name_2" se utiliza para encontrar el número de hits que han realizado los usuarios por producto mediante la función COUNT().
  4. La última línea muestra el campo común (hits.item.productSku) compartido entre los dos conjuntos de datos de la unión.

A continuación se ofrece un ejemplo de la consulta El 10 de septiembre de 2013, ¿cuál fue el número medio de interacciones de usuario antes de una compra?

número de interacciones de usuario el 10 de septiembre de 2013 antes de la 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;

Porcentaje de existencias vendido por producto

A continuación se muestra un ejemplo de una consulta que no solo depende de los datos de Analytics, sino también de otros datos. Con la combinación de ambos conjuntos de datos se puede empezar a entender el comportamiento de los usuarios de un modo más segmentado. Puede importar datos que no sean de Analytics en BigQuery, pero recuerde que repercutirá en su cargo mensual de almacenamiento de datos.

A continuación se muestra el esquema de la secuencia de comandos correspondiente a la pregunta ¿Cuál es el porcentaje de existencias que se ha vendido por producto?

porcentaje de existencias vendido por producto
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold
FROM [ ‘Conjunto de datos importado’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘Conjunto de datos de Google Analytics’ ]
  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. La primera línea genera dos campos: uno que contiene todos los ID de producto y otro que es la operación matemática que muestra el porcentaje de las existencias vendidas de ese ID de producto.
  2. Como esta consulta depende de dos conjuntos de datos, se debe usar la función JOIN() ... ON. Este comando une las filas de dos conjuntos de datos basándose en el campo común entre ellos. En este caso, los dos conjuntos de datos son [ ‘Imported_DataSet’ ] y ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] corresponde a los datos que no son de Analytics. Se trata del conjunto de datos que contiene el campo de métrica con las existencias que quedan (Imported_DataSet.’stock_left_field’) y el campo de dimensión de ID de producto (Imported_DataSet.’productId_field’).
  4. ‘Alias_Name’ es el nombre asignado a los datos que devuelve la subconsulta de color gris. Esta subconsulta usa los datos de Analytics para averiguar la cantidad total de los artículos vendidos por producto.
  5. En la última línea se usa la instrucción ON para mostrar el campo común entre los dos conjuntos de datos y dónde se unen los dos conjuntos de datos.

Muchas de las variables de esta consulta tienen el nombre de conjunto de datos adjuntado como prefijos (por ejemplo, Conjunto de datos importado.’productId_field’, Alias_Nam.quantity_sold). La finalidad es dejar claro el campo que se selecciona y para que resulte evidente al conjunto de datos al que pertenece.

A continuación se ofrece un ejemplo de la consulta ¿Cuál es el porcentaje de existencias vendido por producto el 28 de julio de 2013?

porcentaje de existencias vendido por producto el 28 de julio 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;

Rentabilidad de cada producto

A continuación se muestra el esquema de la secuencia de comandos de la pregunta ¿Cuál es la rentabilidad de cada producto?

beneficio por producto
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 [ ‘Conjunto de datos importado’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘Nombre de conjunto de datos de Google Analytics’ ]
    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. En la primera línea se incluye la operación matemática que calcula el beneficio total obtenido de cada producto.
  2. En la subconsulta de color gris. que utiliza datos que no son de Analytics, se recopilan datos sobre el beneficio que se obtiene cuando se vende un producto.
  3. La subconsulta de color rojo es la subconsulta de los datos de Analytics, que se combinarán con los datos que no son de Analytics. Calcula la cantidad de elementos vendidos por producto.
  4. En la última línea se usa la instrucción ON para indicar el campo que comparten los dos conjuntos de datos. En este caso, es el número de ID de producto.

A continuación se ofrece un ejemplo de la consulta ¿Cuál fue la rentabilidad de cada producto el 28 de julio de 2013?

beneficio por producto el 28 de julio 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
);

El beneficio se calcula encontrando la diferencia entre el precio al que se ha vendido el producto y el coste de su fabricación. Esta información se almacena en el conjunto de datos que no es de Google Analytics.

Rentabilidad real de cada producto (teniendo en cuenta los reembolsos)

A continuación se muestra el esquema de la secuencia de comandos de la pregunta ¿Cuál es la rentabilidad real de cada producto?

beneficio real por producto
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 [ ‘Nombre de conjunto de datos importado’ ] AS 'Imported_DataSet'
    JOIN (
      
SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘nombre de conjunto de datos de Google Analytics’ ]
      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 es muy similar a ¿Cuál es la rentabilidad de cada producto? Las únicas diferencias se encuentran en el conjunto de datos que no es de Analytics en la subconsulta de color gris y en la operación matemática que calcula el beneficio real en la primera línea.
  2. En el conjunto de datos que no son de Analytics, también se calcula el importe total del gasto en reembolsos (en la instrucción SELECT de la subconsulta de color rojo).
  3. Después, se realiza una operación matemática en la línea 1 para encontrar el beneficio real restando los ingresos gastados en reembolsos al beneficio bruto.

En la sección sobre la rentabilidad de cada producto encontrará más información sobre la consulta.

A continuación se ofrece un ejemplo de la consulta ¿Cuál fue la rentabilidad real de cada producto el 28 de julio de 2013?

beneficio real por producto el 28 de julio 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 )
);

En el beneficio real se tiene en cuenta la rentabilidad de un producto después de considerar los productos reembolsados. Para calcular el total de ingresos reembolsados de un producto:

total de ingresos reembolsados de un producto = ( precio del producto + precio de envío de reembolso del producto ) * cantidad de productos reembolsados

¿Te ha resultado útil esta información?
¿Cómo podemos mejorar esta página?