La Query Hernandez-Tortosa

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:

Otogami-LTVreport

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:

tabla-prueba-bonillaware-query-hernandez-tortosa

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…

  • Que post más interesante..

    • Supongo que sólo para la gente que juegue con visitas y a la que Analytics y similares se les quede corto 🙂

  • Jose Maria Alvarez

    A nosotros nos pillas con lo mismo y la verdad es que aún no sabemos como lo vamos a solucionar. Trasteando un poco, he llegado a esta librería….

    https://github.com/keenlabs/scribe-analytics

    No tiene mala pinta pero a ver que tal resulta 😉

  • Seguramente por solo preguntar esto deje claro que soy un chapuzas que apenas conoce conceptos como la tercera forma normal, pero teniendo en cuenta que los datos (al menos los que veo en la imagen, ganancia media / número de visitas / dias_entre / tiempo_medio / ventas_directas / ventas_indirectas / cpc) solo cambian en un evento compra / visita… ¿ por qué no almacenar esos datos en el ID de la visita, precalculados en otra tabla (al ser de un solo usuario sería extremadamente rápido), para que luego la consulta/informe sea mucho más ágil ? Optimization is human, Denormalization is divine !

    • En nuestro caso al menos, porque no tenemos acceso a esos datos y… no sabemos cuando los tendremos. Me explico: cada plataforma de afiliación te da los datos cuando quiere. Algunas al día siguiente, otras a los 15 días y… pueden cambiar (por ejemplo, si hay devoluciones).

  • No es por sonar pretencioso, pero he aquí la query de ventanas. ¡Parece casi más fácil de comprender que la “vuestra”! ;))

    WITH presessions (id, t, s) AS (

    SELECT id, t, CASE WHEN t – lag(t, 1) OVER (PARTITION BY id
    ORDER BY id, t) > ’30 minutes’::interval THEN row_number() OVER
    (PARTITION BY id ORDER BY id, t) ELSE 0 END

    FROM otologs

    ),

    sessions (id, t, s) AS (

    SELECT id, t, max(s) OVER (PARTITION BY id ORDER BY id, t)

    FROM presessions

    )

    SELECT DISTINCT id, s, count(s) OVER (PARTITION BY id, s ORDER BY
    id, s)

    FROM sessions ORDER BY id ASC, s ASC;

  • Me encanta la imagen del encabezado del post 🙂

  • La
    solución definitiva:

    * Añadido event_id a la tabla de datos (es más realista, los
    tendréis seguro)

    * Añadidos más datos de utilidad a la consulta, como
    conservar todos los ids de los eventos (los guarda en un
    array), calcular la duración de la sesión completa
    (evidentemente, puede ser más de 30 minutos) y dar el
    comienzo de la sesión

    * Renombrado de columnas y optimizada la query para mayor
    legibilidad

    * Wrapeado de la query en una función. Al igual que creas
    tablas, puedes crear funciones. La función permite recibir
    por argumento el número de minutos de la sesión. Así, si
    mañana no son 30 sino 120 ó cualquier otro, basta con llamar
    a la función con otro argumento.

    * Para máxima facilidad, creada una vista que llama a la
    función pero con 30 minutos (y así se puede considerar como
    una tabla para hibernate, y no tener que llamar a una
    función).

    * Si os podéis permitir bloquear la tabla de eventos
    mientras se recalcula la vista, podéis usar vistas
    materializadas. Esto permite que se cacheen todos los datos
    y cálculos y sucesivas consultas a la vista sean casi
    instantáneas. Esto tiene mucho sentido en un procesado de
    logs. Lo único: hay que forzar REFRESH manualmente cuando se
    quiera que se vuelva a recalcular. Esta operación bloquea
    exclusivamente la tabla (en 9.4 se soportará refrescado
    concurrente, aunque con algunos matices).

    Demo:

    SELECT * FROM user_event;

    event_id | user_id | event_time

    ———-+———+—————————-

    1 | 1 | 2014-04-08 16:55:01.883807

    2 | 1 | 2014-04-08 17:15:01.883807

    3 | 1 | 2014-04-08 19:05:01.883807

    4 | 1 | 2014-04-08 21:55:01.883807

    5 | 1 | 2014-04-08 21:56:01.883807

    6 | 1 | 2014-04-08 23:59:01.883807

    7 | 2 | 2014-04-07 16:55:01.883807

    8 | 2 | 2014-04-07 16:54:01.883807

    9 | 3 | 2014-04-08 17:07:28.322587

    10 | 3 | 2014-04-08 17:37:28

    11 | 3 | 2014-04-08 18:00:00

    (11 rows)

    SELECT * FROM user_event_session_30;

    user_id | session_id | hits_per_session | event_ids |
    session_start | session_span

    ———+————+——————+———–+—————————-+—————–

    1 | 0 | 2 | {1,2} |
    2014-04-08 16:55:01.883807 | 00:20:00

    1 | 3 | 1 | {3} |
    2014-04-08 19:05:01.883807 |

    1 | 4 | 2 | {4,5} |
    2014-04-08 21:55:01.883807 | 00:01:00

    1 | 6 | 1 | {6} |
    2014-04-08 23:59:01.883807 |

    2 | 0 | 2 | {8,7} |
    2014-04-07 16:54:01.883807 | 00:01:00

    3 | 0 | 3 | {9,10,11} |
    2014-04-08 17:07:28.322587 | 00:52:31.677413

    (6 rows)

    SELECT * FROM user_event_session(120);

    user_id | session_id | hits_per_session | event_ids |
    session_start | session_span

    ———+————+——————+———–+—————————-+—————–

    1 | 0 | 3 | {1,2,3} |
    2014-04-08 16:55:01.883807 | 02:10:00

    1 | 4 | 2 | {4,5} |
    2014-04-08 21:55:01.883807 | 00:01:00

    1 | 6 | 1 | {6} |
    2014-04-08 23:59:01.883807 |

    2 | 0 | 2 | {8,7} |
    2014-04-07 16:54:01.883807 | 00:01:00

    3 | 0 | 3 | {9,10,11} |
    2014-04-08 17:07:28.322587 | 00:52:31.677413

    (5 rows)

    Código:

    CREATE FUNCTION user_event_session(
    IN session_duration_minutes integer,
    OUT user_id integer, OUT session_id integer, OUT hits_per_session integer, OUT event_ids int[], OUT session_start timestamp, OUT session_span interval
    ) RETURNS SETOF RECORD AS $$
    WITH
    session_beginnings (event_id, user_id, event_time, start_of_session_id) AS (
    SELECT event_id, user_id, event_time,
    CASE WHEN (event_time – lag(event_time, 1) OVER w) > ($1 || ‘ minutes’)::interval THEN (row_number() OVER w) ELSE 0 END
    FROM user_event WINDOW w AS (PARTITION BY user_id ORDER BY user_id ASC, event_time ASC)
    ),
    event_session (event_id, user_id, session_id, event_time) AS (
    SELECT event_id, user_id, (max(start_of_session_id) OVER w)::integer, event_time
    FROM session_beginnings WINDOW w AS (PARTITION BY user_id ORDER BY user_id ASC, event_time ASC)
    )
    SELECT DISTINCT user_id, session_id, (count(*) OVER w)::integer, array_agg(event_id) OVER w,
    min(event_time) OVER w, nullif(max(event_time) OVER w – min(event_time) OVER w, ‘0’::interval)
    FROM event_session WINDOW w AS (PARTITION BY user_id, session_id ORDER BY user_id ASC, session_id ASC)
    ORDER BY user_id ASC, session_id ASC
    $$ LANGUAGE SQL STRICT;

    CREATE MATERIALIZED VIEW user_event_session_30 AS SELECT * FROM user_event_session(30);

    REFRESH MATERIALIZED VIEW user_event_session_30;

  • Adrian R

    Yo añadiría también los usuarios nuevos, por otro lado que consideráis por usuario perdido? te permitiría medir la tasa de crecimiento de clientes,

    TOTALES (t1) = TOTALES (t0) * tasa de nuevos clientes + TOTALES (t0) * tasa de recurrencia – TOTALES (t0) * tasa de abandonos,perdidos

    tasa de nuevos clientes = NUEVOS / TOTALES
    tasa de recurrencia = TOTALES (t3)/TOTALES(t2)
    tasa de abandono-perdidos =? (1-tasa de recurrencia)

    yo los nuevos los calculaba de esta manera,

    (la forma mas sencilla, quizás mas pesada)

    WITH new_ids AS
    (
    SELECT id_usuario , min(date) AS [firstDate]
    FROM prueba
    GROUP BY id_usuario
    )

    SELECT year(date), month(date) –whatever, etc…
    , count(id_usuario)
    FROM new_ids
    WHERE date >= ‘DateReportBegin’
    AND date <= 'DateReportEnd'

    aunque simplemente por tener un conteo,

    INSERT INTO temp_table VALUES ( id_usuario )
    SELECT DISTINCT id_usuario
    FROM prueba
    WHERE date < 'DateReportBegin'

    SELECT id_usuario
    FROM prueba
    WHERE id_usuario NOT IN (SELECT id_usuario FROM temp_table )
    AND date <= 'DateReportBegin'

    los usuarios perdidos los podriamos sacar asi, seria tener la ultima fecha de sesion de un usuario para saber cuando fue la ultima vez que visito,

    /***Esta primera parte lo que implica siempre es tener que sacar todo el historico si se quiere sacar el dato fino****/

    INSERT INTO temp_table VALUES (id_usuario, date, Rank)
    SELECT id_usuario , date, ROW_NUMBER() OVER (ORDER BY date DESC) AS Rank
    FROM prueba

    /*o
    SELECT id_usuario, max(date) AS [LastDate]
    FROM prueba
    GROUP BY id_usuario
    */

    SELECT id_usuario , LastDate
    FROM temp_table
    WHERE LastDate < 'Tiempo desde que hace tiempo no vuelve'

    o la otra vertiente para los perdidos,

    INSERT INTO temp_table VALUES (id_usuario)
    SELECT DISTINCT id_usuario
    FROM prueba
    WHERE date = ‘DateReportBegin’)

    otras cosas que tambien se podrian obtener de los usuarios , es cual es la distribucion de la frecuencia de las visitas, es decir cuantos hacen una, cuantos hacen dos, etc… aunque aqui habria que tener en cuenta un posible ajuste, por que los que visitan mucho te aumentan la media, y estos son importantes, dependiendo del caso imaginate, el 20% de tus usuarios te hacen el 50% de las visitas, o un 10-60, por lo que para ir muy muy fino, si empiezas a contar desde Enero y terminas en Diciembre, los usuarios nuevos muy fieles que te empezaron a visitar en Mayo, esto te aplana la distribución.

    INSERT INTO temp_table VALUES (id_usuario, TotalVisitas)
    SELECT id_usuario, count(visitas) AS [TotalVisitas]
    FROM v_visitas

    SELECT TotalVisitas, Count(id_usuario) Usuarios
    FROM temp_table
    GROUP BY TotalVisitas

    yo construiria la tabla visitas de la siguiente manera, aunque parezca chorra,

    INSERT INTO temp_table VALUES (…)
    SELECT id_usuario, date, ROW_NUMBER() OVER (PARTITION id_usuario ORDER BY date ASC) AS Rank
    FROM prueba

    INSERT INTO temp_table2
    SELECT id_usuario, date, (Rank-1) AS Rank2
    FROM temp_table

    ALTER TABLE temp_table
    ADD LapsoDeTiempo AS Int

    UPDATE t1.LapsoDeTiempo = DateDiff(minutos,t2.date – t1.date) AS [LapsoDeTiempo]
    FROM temp_table AS t1
    LEFT JOIN temp_table2 AS t2 ON t1.id_usuario = t2.id_usuario
    WHERE t1.Rank = t2.Rank2

    SELECT *
    FROM temp_table
    WHERE LapsoDeTiempo >= 30

    creo que puedo decir que son solo a modo orientativo, como veis estan en SQL Server que es con el que he desarrollado estas queries por que justo era el 80% de mi tiempo en mi trabajo anterior, aunque hace meses que no las he vuelto a pensar por lo que perdonar por cualquier errorcillo. Me encanta el tema de como medir y luego tener metricas que ayuden a valorar el negocio, me ha gustado mucho este post. Yo por mi parte en mi proyecto actual que trato de llevar a cabo trato de obtener por cada consulta la geoposicion solicitada para luego saber de donde vienen la mayoria de mis usuarios, o en que puntos de venta mas proximos se interesan, o ver a que distancia maxima esta una persona dispuesta a desplazarse para comprar, etc…

    Bueno un saludo espero que ayude en lo que sea…

    Adrian

  • Adrian R

    sorry vi que se me olvido algo en la query de los clientes perdidos,

    INSERT INTO temp_table VALUES (id_usuario, date, Rank)
    SELECT id_usuario , date, ROW_NUMBER() OVER (ORDER BY date DESC) AS Rank
    FROM prueba

    SELECT id_usuario , LastDate
    FROM temp_table
    WHERE LastDate < 'Tiempo desde que hace tiempo no vuelve'

    AND Rank = 1 <<– esta parte es la que se me olvido