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
- Consejos y prácticas recomendadas
- Ejemplos de consultas básicas
- Total de [métrica] por [dimensión]
- Porcentaje de rebote medio por [dimensión]
- Número medio de vistas de páginas de producto por tipo de comprador
- Número medio de transacciones por comprador
- Importe medio de gasto por sesión
- Secuencia de hits (análisis de rutas)
- Varias dimensiones personalizadas a nivel de hit o de sesión
- Ejemplos de consultas avanzadas
- Productos adquiridos por los clientes que han comprado el producto A (comercio electrónico clásico)
- Productos adquiridos por los clientes que han comprado el producto A (comercio electrónico mejorado)
- Número medio de interacciones de usuario antes de la compra
- Porcentaje de existencias vendido por producto
- Rentabilidad de cada producto
- Rentabilidad real de cada producto
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 * |
Método adecuado: usar nombres de campo para evitar el procesamiento innecesario |
---|
SELECT field1, field2 |
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.
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 |
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.
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.
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 |
SQL antiguo
Datos de 3 días con nombres de tablas separados por comas |
---|
SELECT |
Últimos 1095 días
SQL estándar
Datos de los últimos 1095 días con la función _TABLE_SUFFIX |
---|
#standardSQL |
SQL antiguo
Datos de los últimos 1095 días con la función TABLE_DATE_RANGE |
---|
SELECT |
Últimos 36 meses
SQL estándar
Datos de los últimos 36 meses con la función _TABLE_SUFFIX |
---|
#standardSQL |
SQL antiguo
Datos de los últimos 36 meses con la función TABLE_DATE_RANGE |
---|
SELECT |
Últimos 3 años
SQL estándar
Datos de los últimos 3 años con la función _TABLE_SUFFIX |
---|
#standardSQL |
SQL antiguo
Datos de los últimos 3 años con la función TABLE_DATE_RANGE |
---|
SELECT |
Periodo concreto
SQL estándar
Datos de un periodo concreto con la función _TABLE_SUFFIX |
---|
#standardSQL |
SQL antiguo
Datos de un periodo concreto con la función TABLE_DATE_RANGE |
---|
SELECT |
Ú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 |
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 |
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 |
SQL antiguo
Total de transacciones por navegador de dispositivo en julio del 2017 |
---|
SELECT |
¿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 |
SQL antiguo
Porcentaje de rebote por fuente de tráfico en julio del 2017 |
---|
SELECT |
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 |
SQL antiguo
Número medio de vistas de páginas de producto por cada usuario que hizo una compra en julio del 2017 |
---|
SELECT |
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 |
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 |
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 |
SQL antiguo
Número medio de transacciones por cada usuario que hizo una compra en julio del 2017 |
---|
SELECT |
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 |
SQL antiguo
Importe medio de gasto por sesión en julio del 2017 |
---|
SELECT |
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 |
SQL antiguo
Secuencia de vistas de página por los usuarios en julio del 2017 |
---|
SELECT |
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, |
Dimensión personalizada de sesión |
---|
SELECT fullVisitorId, visitId, |
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
yWITHIN 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 sonindex=1 (hits)
niindex=2 (sessions)
, devuelveNULL
. - 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 |
- 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 campoquantity
, con su producto asociado en el campo de productoother_purchased_products
. - 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 |
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 |
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 |
- 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".
- "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. - "Alias_Name_2" se utiliza para encontrar el número de hits de los usuarios por producto mediante la función
COUNT()
. - 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 |
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 |
- 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.
- 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’
. [ ‘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’
).‘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.- 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 |
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 |
- En la primera línea se incluye la operación matemática para calcular el beneficio total obtenido de cada producto.
- 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.
- 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.
- 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 |
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 |
- 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.
- 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). - 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 |
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