Descubriendo Viaje del cliente por SQL

El artículo anterior se crearon múltiples consultas para crear información valiosa para su organización; estas estaban orientadas a descubrir porqué sus clientes se retiran de su empresa. Usted ya investigó que tan asiduos eran estos en usar su plataforma. Pero aún usted quiere saber más de ellos. En particular quiere saber cuáles son los viajes (o secuencia de acciones) que están realizando en su plataforma. O dicho de otra forma, quiere saber:

• Qué ven sus usuarios y en que orden.
• Cuánto tiempo permaneces en cada página

Con esto, podría indagar más fino y preguntarse lo siguiente:

• ¿Están navegando por las páginas que les debería generar más valor?.
• ¿Hay tiempos de permanencia inusualmente altos en páginas que son de transición (como un home o una página de búsqueda de contenido). Mientras que hay tiempos bajos en páginas cargadas de información (que se esperaría el usuario/a lea)?
• Imagínese siendo un usuario, ¿Qué opina de la experiencia completa de su navegación?

Partiendo por los antecedentes. Imagínese tiene las siguientes tablas.

Tabla cliente: Aquí están todos los clientes registrados en su plataforma.

id_cliente	nombre	email	fecha_de_creación
1	Pedro ko	pko@algo.cl	10/11/2019 11:20:32
2	Juan Ta	jta@algo.cl	11/11/2019 11:22:42
3	jushoue Yte	jyte@algo.com	10/03/2020 11:20:00
4	Johan Re	jre@@algo.com	11/11/2020 11:20:32

Tabla acciones_plataforma: Tabla donde cada fila o registro indica una posible acción en su plataforma.

id_accion	nombre_accion
1	log-in
2	ver producto

Tabla registro_acciones: Tabla que muestra efectivamente cada acción realizada por sus usuarios.

id_registro	id_cliente	id_accion	Fecha_acción
100	1	1	10/03/2020 11:20:01
101	2	1	10/03/2020 11:20:02
102	1	2	10/03/2020 11:20:03

Suponga que esta tabla muestra todas las acciones realizadas en su sitio web. En particular muestra la navegación por las distintas páginas de sus sitio (cada acción lleva a una página distinta).

La última tabla ennegrecida es la que más le importa para su query.

Secuencia de pasos para conseguir el viaje del cliente

Para lograr el viaje del cliente se seguirá el siguiente orden de pasos:

1. Se creará una nueva variable log-in: esto para poder separar una sesión de otra. Es necesario saber y distinguir cada sesión.
2. Se creará una variable sesión: La variable sesión nos permitirá ir agrupando a los usuario junto con cada una de sus visitas a la plataforma. Y así, se podrá ver el viaje completo de un usuario en una sesión.
3. (Opcional) Extraer el tiempo en permanencia en cada página: Esta variable no es necesaria para crear el viaje del cliente. Sin embargo, aporta mucho valor tener una noción del tiempo promedio que pasan los usuarios en cada página antes de pasar a la siguiente.
4. Crear la variable flujo: la variable flujo es la que cuenta el número de usuarios que hacen de la acción “A” a la “B”. O de la Acción “A” a “C”. Siendo más especifico, de una vista o página que tiene muchos acciones disponibles, usted puede ver cuantos usuarios eligen una opción sobre la otra. Y con el punto 3 puede ver cuanto se demoran en tomar esa decisión o navegar a ella.

Note que en el punto 1 y 2 la variable más importante es el usuario. Mientras que, en la 3 y 4 la variable más importante es la página en especifico que se revisa (en particular, en el punto 4). Es decir, aquí se mide el rendimiento y comportamiento general del sitio web. Dado lo anterior, le podría interesar guardar separadamente la query del punto 2 de la 4 ya que aportan distinto nivel de detalle.

Navegación Individual por Plataforma

Las primeras 2 query se crearon en el artículo anterior. Esta es la consulta:

SELECT *, SUM(Log_in) OVER(PARTITION BY id_cliente ORDER BY Fecha_acción ASC) 
AS sesion_actual
FROM
(SELECT *, CASE WHEN id_accion=1 THEN 1 ELSE 0 
END AS log_in
FROM registro_acciones) 
AS tabla_log_in

El resultado es:

id_registro	id_cliente	id_accion	Fecha_acción	log_in	sesion_actual
100	1	1	10/03/2020 11:20:01	1	1
101	2	1	10/03/2020 11:20:02	1	1
102	1	2	10/03/2020 11:20:03	0	1
103	1	1	11/03/2020 14:21:03	1	2

Note que si está query la ordena primero por usuario y luego por fecha tiene el viaje de cada cliente, desde el cliente de id=1 hasta el último.

Para tener el detalle de cuánto tiempo pasaron por cada página hay que aprender dos nuevas funciones.

TIMESTAMPDIFF: Esta función es para calcular la diferencia en tiempo entre un registro y otro. Permite poner dentro de sus argumentos la unidad (segundos, minutos, años, etc) en este caso se usará segundos.

LEAD: Permite acceder al dato de la fila siguiente de una tabla. Es decir, con esto se puede restar el valor del tiempo de una columna con la misma columna de un registro que aparece abajo de este (el siguiente registro).

Consulta en SQL:

SELECT id_cliente,id_accion, Fecha_acción, log_in, sesion_actual,
TIMESTAMPDIFF(
SECOND, Fecha_acción, LEAD(Fecha_acción) OVER (
PARTITION BY id_cliente, sesion_actual ORDER BY Fecha_acción) 
) 
AS timediff
FROM
(
SELECT *, 
SUM(Log_in) OVER(PARTITION BY id_cliente, sesion_actual ORDER BY Fecha_acción ASC) 
AS sesion_actual
FROM
(
SELECT *, CASE WHEN id_accion=1 THEN 1 ELSE 0 
END AS log_in
FROM registro_acciones
) 
AS tabla_log_in
) 
AS Navegacion_paginas

id_cliente	id_accion	Fecha_acción	log_in	sesion_actual	timediff
1	1	10/03/2020 11:20:01	1	1	2
1	2	10/03/2020 11:20:03	0	1	null
1	1	11/03/2020 14:21:03	1	2	null
2	1	10/03/2020 11:20:02	1	1	null

Nota: desde ahora está tabla se le hará referencia por su nombre (Navegacion_paginas) para no tener que escribir todo el código nuevamente.

Los null quiere decir que no se calculo el timediff, ya sea porque es el mismo usuario pero en una sesión distinta o bien es otro usuario. Con esto note que tiene lista las navegaciones y tiempo de navegación de todos sus usuarios/as. Sin embargo, qué sucede a nivel macro, es decir, ¿Qué pasa con el comportamiento general en su plataforma?

Navegación Grupal por Plataforma

Ahora lo que usted debe querer es agrupar de alguna forma los elementos de la tabla anterior para extraer el viaje del cliente. Esto queda bastante sencillo teniendo la tabla Navegacion_paginas (buscar comando WITH en SQL para aprender a hacer esto, o bien crear una VIEW de esta tabla). Para ello se escribirá la siguiente consulta:

SELECT id_accion,
accion_sig,
AVG(timediff) as promedio_permanencia_sec,
COUNT(*) as flujo_total
FROM
(
SELECT *, 
LEAD(modulo_id) OVER(PARTITION BY user_id,sesion_actual ORDER BY Fecha_acción ASC) AS accion_sig 
FROM Navegacion_paginas
) 
AS tabla_con_sig_accion
GROUP BY id_accion, accion_sigaccion_sig

Note que la tabla tiene una subquery. La subquery la única función es crear una tabla intermedia que tiene la función de poner la acción siguiente que realiza un usuario en una determinada sesión. Así:

id_cliente	id_accion	Fecha_acción	log_in	sesion_actual	timediff	accion_sig
1	1	10/03/2020 11:20:01	1	1	2	2
1	2	10/03/2020 11:20:03	0	1	null	null
1	1	11/03/2020 14:21:03	1	2	null	null
2	1	10/03/2020 11:20:02	1	1	null	null

Y finalmente la siguiente query lo único que hace es agrupar las filas que tienen igual id_accion y accion_sig y realizar dos operaciones. La primera es calcular el tiempo promedio que pasan los usuarios en un página en particular. La segunda operación es contar el número de usuarios que paso de una página a otra.

Note que la tabla al tener un group by es una tabla mucha más pequeña y su unidad de análisis será el movimiento de una página a otra, y no el usuario como en todas las otras tablas. La tabla quedaría así (se agregaron más datos para dar una idea del resultado final):

id_accion	accion_sig	promedio_permanencia_sec	flujo_total
1	2	60	5000
1	3	60	4500
2	3	2	2000
2	5	2	1000
3	6	150	6000

¡Finalmente entiende el problema de la fuga de los usuarios! Al analizar está tabla en detalle se da cuenta que:

• Los usuarios tienen una mala experiencia para hacer log-in. Permanecen mucho tiempo en está página. Usted debe indagar donde se haya el problema con su área de información.
• La acción 2 lleva a una página poco deseada, ya que el tiempo de permanencia promedio es muy bajo. Es una página de transición a otra. Pero es una mala página de transición ya que solo salen 3 mil de los 5 mil que entran en ella. Pero ¡esto es aún peor! ya que esta está es la primera página que se van todos sus usuarios una vez que hacen log-in.
• De la página que se llega con la acción 3 usted considera que el flujo está bien. Puesto que está acción lleva a una página con gráficos y lecturas muy interesantes que gusta que sus clientes vean en detalle. Por lo que el tiempo de permanencia está dentro de lo esperable.
• El viaje desde el log-in, pasando por la acción 3 a la 6 al parecer tiene muy buena aceptación por lo que sería bueno potenciar ese viaje.

Conclusión: Desde la vista Individual a la Panorámica

Tiene dos tablas interesantes. Una separada por cada usuario. Y otra que muestra los comportamientos generales por su sitio web. cada una de estás tablas tienes distintos usos como ya se mencionó. Con esto usted puede encontrar sus propias aplicaciones de que hacer con estas tablas, sin embargo, acá les dejo algunas ideas:

La tabla que muestra cada usuario junto con sus datos de navegación le puede ser útil para:

• Descubrir patrones de los clientes que se van: Puede crear un modelo de Machine Learning para entender y predecir que clientes son potenciales de irse de su negocio. Separando en dos grupos, los clientes que se van vs los que se quedan. Puede leer más aquí.
• Sementar sus clientes: Puede segmentar para ver cuántos perfiles distintos en base a comportamiento tiene en su plataforma, ejemplo de como hacerlo: aquí .
• Ver casos de clientes particulares que le interesé entender.

La tabla que muestra los datos por comportamiento genérico puede servirle para:

• Entender comportamiento usual de sus usuarios. Crear un “Sankey Diagram” le puede añadir mucho valor a usted para así entender como se mueve el flujo de “acciones normalmente” en su sitio.
• En caso de realizar experimentos o cambios en el sitio, puede ver separado por distintos bloques de tiempo como varía el comportamiento en su página. En concreto, si realizó un cambio hace dos meses en su plataforma, puede ver como se compara el comportamiento previo al cambio (antes de los dos meses) y post cambio (entre hoy y el día del cambio).

Si tienes una duda, o solo quieres comentar ¡siéntase libre de hacerlo en la sección de comentarios!