En Otogami estamos montando un CARAJAL para poder medir (bien) que hacen nuestros usuarios antes de lanzarnos a conquistar el mundo gastándonos zillones de rublos en publicidad.

No es que seamos ese tipo de empresas que prefieren desarrollar durante MESES una solución peor -y, evidentemente, mucho más cara- que la que podrían conseguir pagando 300 dólares al mes. El problema es que, cuando te bates el cobre en las oscuros trincheras del marketing de afiliación en vez de en los salones alfombrados de las herramientas SaaS, soluciones como Kissmetrics o Mixpanel empiezan a chirriar un poco.

El objetivo, en teoría sencillo, es conseguir un informe como este:

Pero, como siempre, detrás de cada una de las cifras de un informe se puede esconder UN MUNDO. En concreto, el concepto de visita es muy interesante.

¿Qué carallo es una visita?

Antes de pensar si quiera como calcular un valor tiene que definir que representa. Para nosotros, una visita son todos los eventos (visionados de páginas web, clics en botones, etc) generados desde un mismo dispositivo con una diferencia temporal de 30 minutos o más respecto al anterior y posterior conjunto de eventos.

Y, con esa información en la mano ¿Cómo extraer el número de visitas por usuario?

Si lo pensáis dos veces, es un dato que se podría llegar a extraer del fichero de log de un servidor web como Apache, pero en nuestro caso, registramos todo este tipo de interacciones en base de datos para poder cruzarlas con el origen del usuario.

La Query de la Muerte

Básicamente, necesitas comparar cada tupla de tu tabla de eventos -ordenados por fecha y cualificados por ID de usuario/dispositivo/cookieID- con el anterior para saber si la diferencia es de más de 30 minutos y, en ese caso, registrar una nueva visita ¿Y eso cómo se hace?

Podrías hacerlo consultando todos los datos de la tabla y después resolviendo el problema mediante programación, pero cuando hablamos de tablas con millones de registros, puedes llegar a freír tu maquina y, además, seamos sinceros: resolver con tu código algo que podría devolverte una consulta a base de datos es de perdedores.

Lo que pasa es que el concepto de tupla anterior puede provocar mareos entre desarrolladores que no posean un conocimiento avanzado de SQL. Afortunadamente, nuestro amigo Alvaro Hernandez Tortosa -uno de los mayores expertos de PostgreSQL de España- nos dio la idea que nos llevó a encontrar una solución sencilla y elegante: las funciones de ventana.

Las Funciones de Ventana o Window Functions te permiten realizar cálculos con valores de conjuntos de tuplas relacionadas con la que se está procesando en ese momento. Por ejemplo, para saber la diferencia en minutos entre la fecha de un evento y la del evento anterior.

Así, para una tabla de prueba como esta:

se puede obtener el número de visitas con una query como esta:

SELECT "ID", COUNT(*) -- ID es el ID de usuario, de cookie, etc.
FROM (
 SELECT "ID", "FECHA", 
 extract(epoch from ("FECHA"- lag("FECHA") OVER (PARTITION BY "ID" ORDER BY "FECHA")))/60 as "MIN_ANT"
 FROM "PRUEBA" -- Tabla con tus logs
 ORDER BY "FECHA") AS NACKLE 
WHERE "MIN_ANT" IS NULL OR "MIN_ANT" > 30 -- Aquí se indica el numero de minutos entre eventos que define una nueva "visita"
GROUP BY "ID" ORDER BY "ID"

En realidad, Álvaro creó una query Hernandez-Tortosa más sofisticada para evitar la subselect y mejorar la velocidad de consulta, pero el SQL que utilizó sólo es comprensible para chamanes de base de datos de nivel 32 o superior.

De momento, nosotros nos quedamos con nuestra humilde solución para seguir cumpliendo una de nuestra máximas: no utilizar nada que no comprendamos. Por eso de usar herramientas en vez de dejar que ellas te usen a ti…