Análisis de sesiones y productos (simulación de e-commerce)

En esta sección simulamos datos similares a los que se pueden encontrar en plataformas de e-commerce y analítica web (como Google Analytics), pero utilizando una tabla de ejemplo creada manualmente para entrenamiento.

Crear tabla: `session_data`

sql

#standardSQL
CREATE TABLE my_dataset.session_data (
  fullVisitorId STRING,
  visitId INT64,
  date STRING,
  time INT64,
  v2ProductName STRING,
  productSKU STRING,
  type STRING,
  eCommerceAction_type STRING,
  eCommerceAction_step INT64,
  eCommerceAction_option STRING,
  transactionRevenue FLOAT64,
  transactionId STRING,
  channelGrouping STRING,
  productQuantity INT64
);

Insertar datos de ejemplo

sql

#standardSQL
INSERT INTO my_dataset.session_data VALUES
('1001', 501, '20230801', 123456, 'Zapatilla Pro', 'SKU-001', 'PAGE', 'add to cart', 1, 'Talla M', 59.99, 'TX001', 'Social', 1),
('1002', 502, '20230801', 123457, 'Zapatilla Pro', 'SKU-001', 'PAGE', 'checkout', 2, 'Talla M', 59.99, 'TX001', 'Organic', 2),
('1003', 503, '20230801', 123458, 'Polera Dryfit', 'SKU-002', 'PAGE', 'add to cart', 1, 'Color Azul', 0, NULL, 'Paid Search', NULL),
('1001', 501, '20230801', 123456, 'Zapatilla Pro', 'SKU-001', 'PAGE', 'add to cart', 1, 'Talla M', 59.99, 'TX001', 'Social', 1),
('1004', 504, '20230802', 123459, 'Polera Dryfit', 'SKU-002', 'PAGE', 'checkout', 2, 'Color Azul', 0, NULL, 'Email', 1),
('1005', 505, '20230802', 123460, 'Short Deportivo', 'SKU-003', 'PAGE', 'add to cart', 1, 'Talla S', 0, NULL, 'Referral', 1),
('1002', 502, '20230801', 123457, 'Zapatilla Pro', 'SKU-001', 'PAGE', 'checkout', 2, 'Talla M', 59.99, 'TX001', 'Organic', 2);

Ejemplo 1: encontrar filas duplicadas

🎯 Este query permite identificar combinaciones de columnas que se repiten más de una vez (es decir, filas duplicadas).
Usa GROUP BY para agrupar por todos los campos que definen una fila.
Usa COUNT(*) para contar cuántas veces aparece esa combinación.
El filtro HAVING row_count > 1 muestra solo los duplicados.

sql

#standardSQL
SELECT
  fullVisitorId,
  visitId,
  date,
  time,
  v2ProductName,
  productSKU,
  type,
  eCommerceAction_type,
  eCommerceAction_step,
  eCommerceAction_option,
  transactionRevenue,
  transactionId,
  COUNT(*) AS row_count
FROM my_dataset.session_data
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12
HAVING row_count > 1;

fullVisitorId	visitId	date	time	v2ProductName	productSKU	...	row_count
1001	501	20230801	123456	Zapatilla Pro	SKU-001	...	2
1002	502	20230801	123457	Zapatilla Pro	SKU-001	...	2

Ejemplo 2: vistas totales vs visitantes únicos

🎯 Compara la cantidad total de registros (product_views) con la cantidad de visitantes únicos (fullVisitorId).
COUNT(*) cuenta todas las filas (vistas de productos).
COUNT(DISTINCT fullVisitorId) cuenta cada visitante una sola vez.

sql

#standardSQL
SELECT
  COUNT(*) AS product_views,
  COUNT(DISTINCT fullVisitorId) AS unique_visitors
FROM my_dataset.session_data;

product_views	unique_visitors
7	5

Ejemplo 3: visitantes únicos por canal

🎯 Agrupa los visitantes únicos por el canal de adquisición (channelGrouping).
GROUP BY organiza los datos por canal.
COUNT(DISTINCT fullVisitorId) calcula cuántas personas distintas llegaron por cada canal.
ORDER BY ordena alfabéticamente por canal (descendente en este caso).

sql

#standardSQL
SELECT
  COUNT(DISTINCT fullVisitorId) AS unique_visitors,
  channelGrouping
FROM my_dataset.session_data
GROUP BY channelGrouping
ORDER BY channelGrouping DESC;

unique_visitors	channelGrouping
2	Social
2	Organic
1	Referral
1	Paid Search
1	Email

Ejemplo 4: nombres de productos únicos

🎯 Muestra una lista de productos distintos.
GROUP BY elimina duplicados de nombres de producto.
ORDER BY los organiza alfabéticamente.

sql

#standardSQL
SELECT
  v2ProductName AS ProductName
FROM my_dataset.session_data
GROUP BY ProductName
ORDER BY ProductName;

ProductName
Polera Dryfit
Short Deportivo
Zapatilla Pro

Ejemplo 5: top 5 productos más vistos

🎯 Identifica los productos que más veces fueron vistos.
Se filtran solo las vistas (type = 'PAGE').
COUNT(*) cuenta cuántas veces se vio cada producto.
ORDER BY ... DESC LIMIT 5 devuelve solo los 5 más vistos.

sql

#standardSQL
SELECT
  COUNT(*) AS product_views,
  v2ProductName AS ProductName
FROM my_dataset.session_data
WHERE type = 'PAGE'
GROUP BY v2ProductName
ORDER BY product_views DESC
LIMIT 5;

product_views	ProductName
4	Zapatilla Pro
2	Polera Dryfit
1	Short Deportivo

Ejemplo 6: top productos vistos por personas únicas

🎯 Calcula cuántos usuarios distintos vieron cada producto al menos una vez.
Una CTE (WITH) agrupa fullVisitorId y v2ProductName para asegurar una vista única por persona.
Luego se cuenta cuántos usuarios únicos vieron cada producto.

sql

#standardSQL
WITH unique_product_views_by_person AS (
  SELECT
    fullVisitorId,
    v2ProductName AS ProductName
  FROM my_dataset.session_data
  WHERE type = 'PAGE'
  GROUP BY fullVisitorId, v2ProductName
)

SELECT
  COUNT(*) AS unique_view_count,
  ProductName
FROM unique_product_views_by_person
GROUP BY ProductName
ORDER BY unique_view_count DESC
LIMIT 5;

unique_view_count	ProductName
3	Zapatilla Pro
2	Polera Dryfit
1	Short Deportivo

Ejemplo 7: vistas, pedidos y cantidad por producto

🎯 Resume el comportamiento de los productos:
COUNT(*): cuántas veces se vio el producto.
COUNT(productQuantity): cuántas veces se registró un pedido (no nulo).
SUM(productQuantity): cantidad total pedida de cada producto.

sql

#standardSQL
SELECT
  COUNT(*) AS product_views,
  COUNT(productQuantity) AS orders,
  SUM(productQuantity) AS quantity_product_ordered,
  v2ProductName
FROM my_dataset.session_data
WHERE type = 'PAGE'
GROUP BY v2ProductName
ORDER BY product_views DESC
LIMIT 5;

product_views	orders	quantity_product_ordered	v2ProductName
4	4	6	Zapatilla Pro
2	2	1	Polera Dryfit
1	1	1	Short Deportivo

Ejemplo 8: promedio por pedido

🎯 Calcula la cantidad promedio pedida por orden para cada producto.
Usa SAFE_DIVIDE(SUM, COUNT) para evitar errores por división por cero.
Muestra el número promedio de unidades por pedido.

sql

#standardSQL
SELECT
  COUNT(*) AS product_views,
  COUNT(productQuantity) AS orders,
  SUM(productQuantity) AS quantity_product_ordered,
  SAFE_DIVIDE(SUM(productQuantity), COUNT(productQuantity)) AS avg_per_order,
  v2ProductName AS ProductName
FROM my_dataset.session_data
WHERE type = 'PAGE'
GROUP BY v2ProductName
ORDER BY product_views DESC
LIMIT 5;

product_views	orders	quantity_product_ordered	avg_per_order	ProductName
4	4	6	1.50	Zapatilla Pro
2	2	1	0.50	Polera Dryfit
1	1	1	1.00	Short Deportivo

🧠 Estos ejemplos simulan escenarios reales de e-commerce usando SQL, sin depender de datasets externos. Son útiles para practicar análisis con BigQuery, entender comportamiento de usuarios y métricas clave de productos.

Análisis de sesiones y productos (simulación de e-commerce) #

Crear tabla: session_data #

Insertar datos de ejemplo #

Ejemplo 1: encontrar filas duplicadas #

Ejemplo 2: vistas totales vs visitantes únicos #

Ejemplo 3: visitantes únicos por canal #

Ejemplo 4: nombres de productos únicos #

Ejemplo 5: top 5 productos más vistos #

Ejemplo 6: top productos vistos por personas únicas #

Ejemplo 7: vistas, pedidos y cantidad por producto #

Ejemplo 8: promedio por pedido #