23 Nov 2018
Cómo hacer un funnel cerrado en Firebase
Lectura: 19 mins.
|
Dificultad:

Cómo hacer un funnel cerrado con Firebase

Firebase es una plataforma de desarrollo y analítica de aplicaciones móviles que utiliza la infraestructura de Google. Aunque es una de las mejores herramientas gratuitas que podemos encontrar en el mercado, tiene algunas limitaciones, como es el caso de la definición y visualización de funnels o embudos. En este post os damos una solución a este problema.

El problema

A primera vista sí parece que Firebase tiene contemplada la analítica de los funnels. En cambio, si entramos a la sección correspondiente y observamos bien, vemos que no es el caso.

Sí. Firebase da la opción de establecer un funnel y ponerle un nombre. El problema es que trata cada evento individualmente y, simplemente, te devuelve el número de usuarios que han realizado el evento, sin importar qué eventos han ocurrido antes.

La buena noticia es que, al parecer, Google es consciente de las limitaciones de este software en cuanto a la flexibilidad de visualizar y tratar con los datos. Y nos da la opción de volcar toda la información en bruto a BigQuery (una base de datos de la plataforma Google Cloud). Además, nos lo ponen muy fácil, solo hay que entrar en configuración de nuestro proyecto de Firebase, ir a BigQuery en la sección de integraciones y activar la opción de vincular Firebase con BigQuery. Esto nos sirve para poder consumir la información de la manera que más nos guste. Eso sí, es recomendable tener algún conocimiento sobre consultas SQL.

Datos en BigQuery

Cómo hemos dicho, al activar la integración de Firebase con BigQuery, los datos se empezarán a volcar en BigQuery. Si os interesa aprender sobre cómo consultar esta información y obtener ideas de cómo hacerlo, os sugiero ver esta conferencia. Pero de momento, en este post, nos limitaremos a recoger información de funnels.

A pesar de que BigQuery es una base de datos relacional y de lenguaje SQL, también admite información de forma jerárquica, y es que BigQuery vuelca la información de esta forma. Cada dato unitario corresponde a un usuario realizando un evento en la aplicación, junto con toda la información que esto supone (parámetros del evento, información sobre el usuario y el dispositivo, etc). Para nuestro caso, solo necesitamos el id del usuario, el timestamp y el nombre del evento.

SELECT
  user_pseudo_id,
  event_name,
  event_timestamp
FROM
  `youday_app.app_events_*`
user_pseudo_idevent_nameevent_timestamp
0031a30efbd6411006d5a8e5d0c7da89event11542240220401369
0031a30efbd6411006d5a8e5d0c7da89event21542240220401411
00E6C6FB3A264FD5AE564DCDD3D976C6event11542327929339298

Como podréis comprobar, ejecutando la query nos devuelve la información en una tabla plana, ya que todos los campos que hemos pedido se encuentran en el primer nivel de la jerarquía.

Funnels

Bien, ya tenemos la información necesaria, pero ahora hay que darle forma. ¿Cómo es el resultado final que queremos obtener?

Yo sugiero algo así:

user_idstagetsfunnel_start_tsfunnel_end_ts
102018-10-22T20:32:292018-10-22T20:32:362018-10-22T20:37:11
112018-10-22T20:32:362018-10-22T20:32:362018-10-22T20:37:11
122018-10-22T20:37:112018-10-22T20:32:362018-10-22T20:37:11
202018-11-14T16:21:552018-11-14T16:21:552018-11-14T16:21:55
302018-11-16T09:49:352018-11-16T09:51:042018-11-16T10:02:01
312018-11-16T09:51:042018-11-16T09:51:042018-11-16T10:02:01
322018-11-16T09:55:132018-11-16T09:51:042018-11-16T10:02:01
332018-11-16T10:02:012018-11-16T09:51:042018-11-16T10:02:01

Cada fila de la tabla corresponde con un usuario alcanzando un nuevo nivel en el funnel. Se han incluido también los usuarios que solo han alcanzado la stage 0 (en el ejemplo es el caso del usuario con user_id=2), para que podamos tener en cuenta también qué cantidad de usuarios han estado activos, pero ni siquiera han empezado el funnel.

Así de primeras, todos coincidimos en que la interpretación de los datos no es muy visual. Sí, es cierto. Pero si conectamos esta tabla con alguna herramienta de visualización como Google Data Studio o Tableau, la cosa cambia bastante.

Nota:
Hay otras soluciones por Internet, pero, en mi opinión, tienen muchos inconvenientes. Las vemos al final del post.

Advierto que no vamos a obtener los datos con una query SQL sencilla y que, probablemente, sea más apropiado resolverlo mediante un lenguaje de programación R o Python. Aún así, para herramientas de visualización como Google Data Studio, que no admiten estos lenguajes, tener una query que extraiga la información que te interesa es lo más cómodo.

Estrategia a seguir

Antes de ponernos a construir la query, parémonos a pensar un momento la estrategia que queremos seguir.

Cada registro de evento que nos devuelva la query tiene que corresponder a un único usuario y a una fase completada del funnel, y tiene que cumplir las siguientes condiciones:

  • Cada registro debe ser único por fase y usuario, para no contar al mismo usuario como más de uno por muchas veces que haya realizado el evento.
  • Normalmente, para que el usuario alcance una fase, tiene que haber alcanzado todas las anteriores.
  • Las fases alcanzadas por un usuario tienen que estar en orden cronológico. Aunque un usuario haya realizado todos los eventos, no significa que necesariamente haya completado nuestro funnel.
  • Realizar de nuevo un evento correspondiente a una fase del funnel inferior a la que se encuentra el usuario no tiene efecto.

Obviamente, un usuario no tiene porque realizar los eventos de los funnels sin volver a fases anteriores o pasando por otros eventos no contemplados en el funnel. Para poder detectar también a estos usuarios, hay que hacer previamente un poco de limpieza en los datos.

Nos interesa conocer cuándo y qué evento se ha realizado si este está contemplado en el funnel. Pero, ¿y los demás eventos? Podríamos dejarlos fuera y ya, al final esto es una decisión de cada uno y de lo que más se ajuste a su caso. Yo aquí voy a renombrar todos esos eventos que no contemplamos a “0”, como si fuera algo así como la “fase prefunnel”, que nos servirá para hallar el porcentaje de usuarios que entran a la primera fase respecto a todos los que han estado activos en la app.

Pongamos por ejemplo que nuestro funnel se compone de cuatro eventos con estos nombres y orden: event1, event2, event3 y event4.

Una vez hecho el cambio de nombre, pongamos unos usuarios de ejemplo que, en orden cronológico, han realizado los siguientes eventos:

user1user2user3user4user5
000event1event2
event10event200
00event100
event2event1000
event2event30event1event2
event20event3event2event3
event30event2event30
event20000
event40event3event40

Recordamos que el evento “0” corresponde a cualquier evento no registrado como parte del funnel. Lo más fácil es quedarnos con solo uno de ellos por usuario y forzarlo a que sea el primero realizado, ya que solo vamos a usarlo como indicador de la “fase pre-funnel”.

user1user2user3user4user5
00000
event1event1event2event1event2
event2event3event1event1event2
event2event3event2event3
event2event2event3
event3event3event4
event2
event4

Para simplificar más los datos sin perder información valiosa, vamos a eliminar aquellos eventos que se repiten de forma consecutiva.

user1user2user3user4user5
00000
event1event1event2event1event2
event2event3event1event2event3
event3event3event3
event2event2event4
event4event3

Ahora solo queda averiguar, para cada registro de evento de usuario, hasta qué etapa del funnel ha llegado. Para eso vamos a tener otra variable que contenga los eventos pasados. Si estuviéramos procesando la información en un lenguaje de programación como R o Python, no haría falta crear esta variable temporal, pero en lenguaje SQL no nos queda otro remedio.

user1user1_tmpuser2user2_tmpuser3user3_tmpuser4user4_tmpuser5user5_tmp
0null0null0null0null0null
event10event10event20event10event20
event20-1event30-1event10-2event20-1event30-2
event30-1-2event30-2-1event30-1-2
event20-1-2-3event20-2-1-3event40-1-2-3
event40-1-2-3-2-4event30-2-1-3-2

Esta variable temporal va a ser muy útil porque vamos a poder obtener, por cada registro de evento, un resumen de lo que ha ocurrido en el pasado. Nos servirá para poder determinar mediante condiciones si el evento realizado hace pasar al usuario a la siguiente fase o no.

Por ejemplo, un event3 que como variable temporal tenga “0-1-2” va a hacer que el usuario pase a la fase 3, porque el orden de los eventos ha sido 0, event1, event2 y ahora event3. En cambio, si la variable temporal es “0-2-1” no pasaría a la tercera etapa, ya que aún ni siquiera ha llegado a la segunda (ha realizado el evento event2, pero ha sido antes del evento event1). Vamos a marcar en rojo los eventos que no causen avanzar en el funnel debido a su historial, para posteriormente ignorarlos.

También vamos a marcar, esta vez de naranja, aquellos eventos que vuelven a surgir estando en un nivel más alto del funnel, para también, posteriormente, ignorarlos.

user1user1_tmpuser2user2_tmpuser3user3_tmpuser4user4_tmpuser5user5_tmp
0null0null0null0null0null
event10event10event20event10event20
event20-1event30-1event10-2event20-1event30-2
event30-1-2event30-2-1event30-1-2
event20-1-2-3event20-2-1-3event40-1-2-3
event40-1-2-3-2-4event30-2-1-3-2

Si tal como hemos dicho eliminamos los registros de evento marcados y nos quedamos solo con los eventos que han hecho avanzar al usuario en el funnel, ya tendríamos la información que buscábamos.

user1user2user3user4user5
00000
event1event1event1event1
event2event2event2
event3event3event3
event4event4
Etapa del funnelN.º Usuarios
05
14
23
33
42

Hemos visto la idea del procedimiento. Ahora tenemos que traducirla a lenguaje SQL standard.

Construyendo la query

Lo primero de todo vamos a definir las constantes “funnel_events”, “from_date” y “to_date”. La primera de ellas será una lista de los eventos ordenados de nuestro funnel en la que el primer elemento será “0”. La segunda y tercera serán las fechas que acoten el período que queramos analizar.

WITH
  vars AS (
  SELECT
    ["0",
    "event1",
    "event2",
    "event3",
    "event4"] AS funnel_events,
    DATE '2018-11-01' AS from_date,
    DATE '2018-11-30' AS to_date
)

Bien, con estas constantes definidas, ya podemos pasar a la query de consulta. En vez de daros la query para hacer copy-paste, voy a hacer que me odiéis un poquito y la vamos a ir construyendo poco a poco para entenderla.

Os vais a dar cuenta enseguida de que se trata de muchas queries pequeñitas anidadas una dentro de otra. La forma de proceder es empezando con la más interna y terminando con la que envuelve a todas.

Dicho esto, lo primero que hay que hacer es aislar la información que necesitamos. Es decir, el id de usuario, el nombre del evento que ha realizado, y la fecha y hora de la acción.

Para el id de usuario, vamos a usar la variable que nos da Firebase, “user_pseudo_id”, ya que, si os fijáis, la variable “user_id” original siempre está vacía. Esto es debido a que lo borran para proteger la identidad del usuario. La variable “user_pseudo_id” es suficiente para identificar al usuario, ya que lo único que la diferencia de “user_id” es que un usuario físico obtiene un “user_pseudo_id” diferente cada vez que se instala la app.

En cuanto a la fecha y hora, solo tenemos que incluir la variable “event_timestamp” en la petición.

Sobre los eventos, solo nos interesan si aparecen en la lista del funnel que hemos definido previamente. Al resto los llamaremos “0”.

SELECT
  user_pseudo_id AS user_id,
  datetime(TIMESTAMP_MILLIS(CAST(event_timestamp/1000 AS int64)), 'Europe/Madrid') AS event_timestamp,
  (
  SELECT
    CASE
      WHEN event_name NOT IN UNNEST(x) THEN '0'
      ELSE event_name
    END
  FROM (
    SELECT
      funnel_events AS x
    FROM
      vars)) AS event_name
  
FROM
   `youday_app.app_events_*`,
  vars
WHERE
   DATE(TIMESTAMP_MILLIS(CAST(event_timestamp/1000 AS int64)), 'Europe/Madrid') between CAST(from_date AS DATE) and  CAST(to_date AS DATE)
ORDER BY
  user_pseudo_id,
  event_timestamp ASC
user_idevent_timestampevent_name
0031a30efbd6411006d5a8e5d0c7da892018-11-20T15:30:360
0031a30efbd6411006d5a8e5d0c7da892018-11-20T15:30:38event1
0031a30efbd6411006d5a8e5d0c7da892018-11-20T15:30:39event2
00E6C6FB3A264FD5AE564DCDD3D976C62018-11-20T19:15:420
00E6C6FB3A264FD5AE564DCDD3D976C62018-11-20T19:58:01event2
00E6C6FB3A264FD5AE564DCDD3D976C62018-11-20T20:02:540

A continuación, vamos a aplicar una query a la anterior. Esta query, aparte de poner al mínimo el timestamp de los eventos “0”, también va a asignar, basándose en la variable definida anteriormente, la etapa de cada funnel correspondiente a cada evento.

SELECT
  user_id,
  CASE
     WHEN event_name = '0' THEN FIRST_VALUE (event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
     ELSE event_timestamp
  END AS event_timestamp,
  event_name,
  event_funnel_stage

FROM (
       
       #############################
       # Aquí va la query anterior #
       #############################

        )
CROSS JOIN
UNNEST((
SELECT
          funnel_events
  FROM vars)) AS funnel_event
  WITH
   OFFSET
            AS event_funnel_stage
   WHERE
    event_name = funnel_event
user_idevent_timestampevent_nameevent_funnel_stage
0031a30efbd6411…2018-11-20T15:30:3600
0031a30efbd6411…2018-11-20T15:30:38event11
0031a30efbd6411…2018-11-20T15:30:39event22
00E6C6FB3A264F…2018-11-20T19:15:4200
00E6C6FB3A264F…2018-11-20T19:58:01event22
00E6C6FB3A264F…2018-11-20T19:15:4200

Para evitar tener varios eventos “0”, vamos a dejar solo uno por usuario. Esto se consigue agrupando por todas las variables, ya que para los eventos “0” todas tienen el mismo valor.

Además, añadimos la variable “pre_event”, que contiene el evento anterior de cada usuario. Nos servirá para posteriormente deshacernos de aquellos eventos que se repiten consecutivamente.

SELECT
  user_id,
  event_timestamp,
  event_name,
  event_funnel_stage,
  LAG(event_name, 1) OVER (PARTITION BY user_id ORDER BY event_timestamp ASC) AS pre_event
FROM (

       #############################
       # Aquí va la query anterior #
       #############################

)
GROUP BY
  user_id,
  event_name,
  event_funnel_stage,
  event_timestamp
ORDER BY
  user_id,
  event_timestamp
user_idevent_timestampevent_nameevent_funnel_stagepre_event
0031a30efbd6411…2018-11-20T15:30:3600null
0031a30efbd6411…2018-11-20T15:30:38event110
0031a30efbd6411…2018-11-20T15:30:39event221
00E6C6FB3A264F…2018-11-20T19:15:4200null
00E6C6FB3A264F…2018-11-20T19:58:01event220

Posteriormente, nos deshacemos de los registros que, como evento previo, tienen el mismo que el propio, a excepción de los que no tienen evento previo, que son los “0”. Y es que si no lo indicamos se borrarán, ya que null y “0” se considera lo mismo.
Adicionalmente, vamos a crear la variable “user_path”, que será la que contendrá el historial de los eventos pasados. Esta variable será inicialmente una lista.

SELECT
  user_id,
  event_timestamp,
  event_name,
  event_funnel_stage,
  ARRAY_AGG(CAST(event_funnel_stage AS string)) OVER (PARTITION BY user_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS user_path
FROM (

       #############################
       # Aquí va la query anterior #
       #############################

)
WHERE
  event_name != pre_event OR pre_event IS NULL
user_idevent_timestampevent_nameevent_funnel_stageuser_path
0031a30efbd6411…2018-11-20T15:30:3600[ ]
0031a30efbd6411…2018-11-20T15:30:38event11[0]
0031a30efbd6411…2018-11-20T15:30:39event22[0, 1]
00E6C6FB3A264F…2018-11-20T19:15:4200[ ]
00E6C6FB3A264F…2018-11-20T19:58:01event22[0]

La idea es usar expresiones regulares para analizar y validar los históricos de cada evento, por lo que nos interesa que la variable “user_path” sea de tipo string. Vamos a hacer esta transformación en este paso.

SELECT
  user_id,
  event_timestamp,
  event_name,
  event_funnel_stage,
  ARRAY_TO_STRING(user_path, "-") AS user_path_string
FROM (

       #############################
       # Aquí va la query anterior #
       #############################

)
user_idevent_timestampevent_nameevent_funnel_stageuser_path_string
0031a30efbd6411…2018-11-20T15:30:3600
0031a30efbd6411…2018-11-20T15:30:38event110
0031a30efbd6411…2018-11-20T15:30:39event220-1
00E6C6FB3A264F…2018-11-20T19:15:4200
00E6C6FB3A264F…2018-11-20T19:58:01event220-2

Como ya tenemos el histórico de eventos pasados en forma de texto en la variable “user_path_string”, podemos aplicar las expresiones regulares.

Las condiciones son distintas según la fase del evento a la que se pretende entrar. Para la primera fase no hay condición más que se realice el evento event1. Para la segunda fase no solo es suficiente con realizar el evento event2, sino que también tenemos que tener el evento event1 en el historial. Y para la tercera fase, no solo tendríamos que tener los eventos event1 y event2 en el historial, sino que además tienen que haberse realizado en el orden correcto.

Cada uno tendrá que considerar si para su caso se necesitan condiciones de otro tipo. Igual hay algún evento opcional en el funnel o igual es necesario que dos eventos ocurran consecutivamente sin otro de por medio.

Vamos a crear la variable “is_valid” que estará determinada por si el registro cumple alguna de las condiciones o no. Contendrá un 1 cuando se den las suficientes condiciones para avanzar en el funnel y un 0 cuando no.

SELECT
  user_id,
  event_timestamp,
  event_name,
  event_funnel_stage,
  CASE
      WHEN event_funnel_stage = 0 THEN 1
      WHEN event_funnel_stage = 1 THEN 1
      WHEN event_funnel_stage = 2 AND REGEXP_CONTAINS( user_path_string, r'.*1.*') THEN 1
      WHEN event_funnel_stage = 3 AND REGEXP_CONTAINS( user_path_string, r'.*1.*2.*') THEN 1
      WHEN event_funnel_stage = 4 AND REGEXP_CONTAINS( user_path_string, r'.*1.*2.*3.*') THEN 1
      ELSE 0
    END AS is_valid
FROM (


       #############################
       # Aquí va la query anterior #
       #############################

)
user_idevent_timestampevent_nameevent_funnel_stageis_valid
0031a30efbd6411…2018-11-20T15:30:36001
0031a30efbd6411…2018-11-20T15:30:38event111
0031a30efbd6411…2018-11-20T15:30:39event221
00E6C6FB3A264F…2018-11-20T19:15:42001
00E6C6FB3A264F…2018-11-20T19:58:01event220

¡Ya estamos en la recta final!

Vamos a filtrar por la variable “is_valid” y así quedarnos únicamente con aquellos que representen una etapa nueva en el funnel.

Por último, en el caso de que se repita el mismo evento en un mismo usuario, vamos a quedarnos con el más antiguo.

SELECT
  user_id,
  min(event_timestamp) as event_timestamp,
  event_name,
  event_funnel_stage,
  NTH_VALUE (MIN(event_timestamp), 2) OVER (PARTITION BY user_id ORDER BY MIN(event_timestamp) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS funnel_start_ts,
  LAST_VALUE (MIN(event_timestamp)) OVER (PARTITION BY user_id ORDER BY MIN(event_timestamp) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_ts

FROM (

       #############################
       # Aquí va la query anterior #
       #############################

)
WHERE
  is_valid = 1
GROUP BY
  user_id,
  event_name,
  event_funnel_stage
ORDER BY
  user_id,
  event_funnel_stage
user_idevent_timestampevent_nameevent_funnel_stagefunnel_start_tsmax_ts
0031a3…2018-11-20T15:30:36002018-11-20T15:30:382018-11-20T15:30:39
0031a3…2018-11-20T15:30:38event112018-11-20T15:30:382018-11-20T15:30:39
0031a3…2018-11-20T15:30:39event222018-11-20T15:30:382018-11-20T15:30:39
00E6C6…2018-11-20T19:15:4200null2018-11-20T19:15:42

¡Y ya lo tenemos!

Podemos visualizarlo en Data Studio muy fácilmente:

Queries alternativas

Cuando me encontré por primera vez con el problema de Firebase y los funnels obviamente busqué si alguien ya había construido una query para solucionarlo. Me encontré con unas pocas soluciones, pero ninguna me encajaba del todo.

En todas ocurría que no eran adecuadas para funnels de más de tres o cuatro eventos y es que, por cada evento añadido, solía aumentar exponencialmente el tiempo de consulta. Además, no era tan fácil añadir, quitar y modificar eventos, ya que había que modificar el código en diferentes sitios. Y por si fuera poco, solo te devolvían un resumen con el número de usuarios que habían alcanzado las distintas fases del funnel, por lo que se perdía mucha información, como quiénes son estos usuarios o cuánto tiempo tiempo de media tardan los usuarios en avanzar de una fase a otra.

De todas formas me sirvieron para inspirarme y aprender. Os las dejo aquí por si os interesa echar un vistazo:

Personaliza tu query

Insisto en la importancia que tiene entender el procedimiento y la query para poder adaptarla a las necesidades de cada caso y por eso he ido paso por paso explicándola.

De todos modos, por haber llegado hasta el final os dejo de premio la query en un archivo descargable. A cambio, os pedimos vuestro e-mail; no vamos a abrasaros con spam, solo os enviaremos nuestra newsletter, con contenido fresco sobre analítica y estrategia digital, una vez al mes. ¡Prometido!