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 una parte de su cuota mensual más grande de lo necesario. 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 [table name];

 

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

Permitir el almacenamiento en 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.

Actualmente, los resultados almacenados en caché no pueden utilizarse en las consultas de varias tablas con comodín aunque esté marcada la opción Utilizar los resultados almacenados en caché. Si realiza varias veces la misma consulta con comodín, se le facturará cada una de ellas. Más información

Utilizar 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. Después, 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 procesarlos.

Usar una tabla intermedia
SELECT field1, field2
FROM [Dataset name.table name];

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 a las que se aplica. 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 sobre cómo habilitar SQL estándar en la UI, 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 el dialecto 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 bien 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 que se indican a continuación.

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 vista de página. 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 vistas de páginas 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 la media de páginas de producto vistas por cada usuario que hizo una compra en julio del 2017?

SQL estándar

Número medio de vistas de páginas de producto por cada usuario que hizo 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 vistas de páginas de producto por cada usuario que hizo 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 la media de vistas de páginas de producto por cada usuario que no hizo ninguna compra en julio del 2017?

SQL estándar

Número medio de vistas de páginas de producto por cada usuario que no hizo ninguna 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 vistas de páginas de producto por cada usuario que no hizo ninguna 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 de transacciones totales por cada usuario que hizo una compra en julio del 2017?

SQL estándar

Número medio de transacciones por cada 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 cada 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 vistas de página 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 vistas de página 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 a nivel de hit o de 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 con cada dimensión personalizada, pero con las que no son index=1 (hits) ni 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 [‘Dataset Name’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Dataset Name’ ]
  WHERE hits.item.productName CONTAINS 'Nombre del producto 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 completado transacciones (totals.transactions>=1) y que durante una transacción han comprado el producto A (WHERE hits.item.productName CONTAINS 'Nombre del producto 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 del 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 la instrucción JOIN, y se aplican 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 inferior a 8 MB. En Dremel, se conoce como JOIN de difusión. Cuando el tamaño supera los 8 MB, se debe activar una instrucción JOIN aleatoria, algo que se puede hacer con la sintaxis JOIN EACH. Debes saber que no se puede hacer con IN, pero sí se puede rescribir la misma consulta con 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 usa 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 [‘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. 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 de 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 del 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 del 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 [ ‘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. 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 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 ambos.

Muchas de las variables de esta consulta tienen el nombre de conjunto de datos adjuntado como prefijos (por ejemplo, Imported_DataSet.’productId_field’, Alias_Name.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 del 2013?

Porcentaje de existencias vendido por producto el 28 de julio del 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?

Rentabilidad 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 [ ‘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. En la primera línea se incluye la operación matemática para calcular 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 artículos 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 del 2013?

Rentabilidad por producto el 28 de julio del 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?

Rentabilidad 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 [ ‘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 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 ON 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 del 2013?

Rentabilidad real por producto el 28 de julio del 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?

¿Necesitas más ayuda?

Prueba estos pasos:

Publicar en la comunidad de ayuda Obtener respuestas de los miembros de la comunidad
Ponte en contacto con nosotros Danos más información para que podamos ayudarte
true
learning path
Elija su propio plan de formación

Descubra google.com/analytics/learn, un recurso que le ayudará a sacar el máximo partido a Google Analytics 4. En el nuevo sitio web encontrará vídeos, artículos y procesos guiados, con enlaces al canal de Discord, al blog, al canal de YouTube y al repositorio de GitHub de Google Analytics.

Quiero empezar

Búsqueda
Borrar búsqueda
Cerrar búsqueda
Menú principal
5529872729770736272
true
Buscar en el Centro de ayuda
true
true
true
true
true
69256
false
false