Cómo usar SELECT FROM UNNEST para analizar múltiples parámetros en BigQuery for Analytics

(Hombre, necesito títulos más cortos para mis publicaciones de blog)

¡Hola, hay desarrolladores de BigQuery-for-Google-Analytics-for-Firebase!

En nuestra publicación de blog anterior, le mostré cómo usar la función UNNEST en BigQuery para analizar parámetros de eventos en sus datos de Google Analytics para Firebase.

Debe usar esta función porque normalmente, los parámetros de eventos se almacenan como un registro repetido, que puede considerar como una matriz. Al DESNOSTIRLOS, puede "separar" esta matriz, colocar cada parámetro de evento individual en una nueva fila y luego duplicar la fila original para cada uno de estos parámetros individuales. Al igual que en esta animación:

Ahora, esto es realmente útil cuando desea analizar un parámetro de evento en sus datos analíticos. Pero, ¿qué sucede si quieres analizar dos a la vez?

Por ejemplo, echemos un vistazo a nuestro juego de muestra de Flood-it: aquí está el conjunto de datos públicos si desea probarlo usted mismo.

Ahora, si echaras un vistazo a nuestro evento level_complete_quickplay, hay dos parámetros que nos interesan: el parámetro de valor, que nos dice el puntaje final del usuario (es decir, cuántos movimientos les llevó a despejar el tablero), y el parámetro del tablero, que representa el tamaño del tablero.

Este jugador obtuvo un puntaje de 18 mientras jugaba en un tablero pequeño

Dado que su puntaje puede variar bastante según el tablero que esté jugando, probablemente tenga sentido agrupar nuestros eventos por el parámetro del tablero antes de comenzar a calcular estadísticas como el puntaje promedio de nuestros usuarios.

Si tuviéramos que DESNOMBRAR estos parámetros de evento de la siguiente manera:

SELECCIONAR parámetro
DE `firebase-public-project.analytics_153293282.events_20181003`,
UNNEST (event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND (param.key = "value" OR param.key = "tablero")

Puede ver que tenemos toda la información que necesitamos, pero abarcan filas separadas, lo que hace que analizarlos sea un poco difícil.

Entonces, una solución es simplemente reagrupar estos eventos observando qué filas tienen el mismo user_pseudo_id y event_timestamp. Si dos filas tienen los mismos valores en estas dos columnas, podemos estar bastante seguros de que pertenecen exactamente al mismo evento. Así que solo tenemos que hacer un poco de trabajo para consolidarlos juntos ...

SELECCIONAR
  MAX (if (param.key = "value", param.value.int_value, NULL)) AS score,
  MAX (if (param.key = "board", param.value.string_value, NULL)) AS board_type
DESDE (
  SELECT event_name, event_timestamp, user_pseudo_id, param
  DE `firebase-public-project.analytics_153293282.events_20181003`,
  UNNEST (event_params) AS param
  WHERE event_name = "level_complete_quickplay"
  AND (param.key = "value" OR param.key = "tablero")
)
GROUP BY user_pseudo_id, event_timestamp

... y luego continuamos y analizamos estos dos parámetros como quisiéramos.

Pero esto me parece innecesariamente complicado. ¿Realmente vamos a dividir todos nuestros parámetros en filas separadas solo para combinarlos nuevamente unos momentos más tarde? Me molesta un poco a nivel filosófico, y supongo que también es bastante ineficiente.

¡SELECCIONE DESDE UNNEST al rescate!

Afortunadamente, hay otra forma de solucionar esto usando SELECT FROM UNNEST. Cuando usas la función SELECCIONAR DESDE UNSEST, básicamente estás diciendo: "Oye, quiero DESNOMBRAR este registro repetido en su propia pequeña tabla temporal. Una vez que haya hecho eso, continúe y seleccione una fila de ella, y colóquela en nuestros resultados como si fuera cualquier otro valor ".

Sé que es un poco difícil de asimilar al principio, así que echemos un vistazo a nuestro ejemplo.

Lo primero que voy a hacer es eliminar nuestra declaración UNNEST original y simplificar nuestra consulta para ver todos nuestros eventos completos de nivel.

SELECT event_name, event_timestamp, user_pseudo_id
DE `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

A continuación, le pediré a BigQuery que SELECCIONE la columna value.int_value de nuestra matriz event_params UNNESTed, donde la clave del parámetro del evento es igual a "valor".

SELECT event_name, event_timestamp, user_pseudo_id,
  (SELECCIONAR value.int_value FROM UNNEST (event_params)
    WHERE key = "value") AS score
DE `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

Lo que termina sucediendo es algo parecido a esta animación, donde estamos expandiendo la matriz event_params en su propia pequeña tabla temporal, filtrando la entrada donde key = "value", y luego tomando el value.int_value desde allí.

Y obtienes algunos resultados que se parecen un poco a esto.

Una vez más, tenemos nuestros datos de nuestro evento original, y luego tenemos el valor de un único parámetro de evento extraído junto a cada fila.

Ahora, hay tres notas importantes a tener en cuenta al hacer llamadas SELECT FROM UNNEST como esta:

Primero, debe asegurarse de poner toda la llamada SELECCIONAR DESDE UNIDO entre paréntesis. Si no lo hace, BigQuery se asusta por el hecho de que tiene dos declaraciones SELECT y no ejecutará su consulta.

En segundo lugar, hasta donde puedo decir, esto solo funcionará si no devuelve más de un valor de cada llamada SELECCIONAR DESDE UNIDO.

Tercero, ¡puedes usar esta técnica varias veces en la misma instrucción SELECT! Así que ahora podemos tomar nuestra declaración select anterior y agregar una segunda llamada SELECT FROM UNNEST para obtener nuestro parámetro de placa junto con nuestro parámetro de valor ...

SELECT event_name, event_timestamp, user_pseudo_id,
  (SELECCIONAR value.int_value FROM UNNEST (event_params)
    WHERE key = "value") AS score,
  (SELECCIONAR value.string_value FROM UNNEST (event_params)
    WHERE key = "board") AS board_size
DE `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

... y aquí están todos muy bien organizados en la misma fila.

Luego podemos seguir adelante y hacer cualquier tipo de análisis que queramos, y es fácil dividir nuestros puntajes por el tamaño de nuestro tablero de juego. Aquí; sigamos adelante y calculemos el puntaje promedio y la desviación estándar para cada tipo de tablero de juego:

SELECCIONE AVG (puntaje) COMO promedio, STDDEV (puntaje) como std_dev, board_size
DESDE (
  SELECT event_name, event_timestamp, user_pseudo_id,
  (SELECCIONAR value.int_value FROM UNNEST (event_params)
    WHERE key = "value") AS score,
  (SELECCIONAR value.string_value FROM UNNEST (event_params)
    WHERE key = "board") AS board_size
  DE `firebase-public-project.analytics_153293282.events_20181003`
  WHERE event_name = "level_complete_quickplay"
)
GROUP BY board_size
No es sorprendente que se necesiten más movimientos para despejar un tablero más grande.

¡Analice los parámetros de eventos y las propiedades del usuario juntos también!

También puede usar esta técnica SELECCIONAR DESDE UNIDO para analizar parámetros de eventos y propiedades de usuario juntos.

Por ejemplo, la gente de Flood-it usa el evento pasar_virtual_currencia para rastrear cuando un usuario gasta "pasos adicionales" al final de una ronda. El parámetro de valor ayuda a rastrear cuántos pasos pasan por evento. También estamos rastreando con cuántos pasos adicionales se sembró un usuario inicialmente con la propiedad de usuario initial_extra_steps.

Entonces, digamos que queremos averiguar si existe alguna correlación entre la cantidad de pasos que se le dan inicialmente a un usuario y la cantidad de pasos que pasa durante un evento de "uso de pasos adicionales". Esto implica mirar el parámetro de valor y la propiedad de usuario initial_extra_steps juntos. Una vez más, ambos valores se agrupan en registros repetidos, entonces, ¿cómo podemos analizarlos?

En teoría, podría hacer esto encadenando dos llamadas UNNEST de esta manera.

SELECT event_name, event_timestamp, user_pseudo_id,
  param.value.int_value AS moves_used,
  userprop.value.string_value AS initial_extra_steps
DE `firebase-public-project.analytics_153293282.events_20181003`,
UNNEST (event_params) como param,
UNNEST (user_properties) como userprop
DONDE event_name = "Pass_virtual_currency"
Y param.key = "valor"
Y userprop.key = "initial_extra_steps"

Esto funcionaría, pero haría el trabajo DESNUESTANDO primero cada fila contra cada parámetro en su evento pasar_virtual_currencia, y luego DESCONECTANDO cada una de esas filas contra cada propiedad de usuario que estamos grabando. Esto significa que esencialmente estamos multiplicando cada fila en nuestro conjunto de datos de consulta grande por (número de parámetros * número de propiedades del usuario). ¡Esto puede hacerse bastante grande bastante rápido!

Pasé más tiempo en esta animación de lo que me gustaría admitir.

Ahora, tal vez BigQuery hace algo de magia bajo el capó para optimizarlo previamente, pero dado que he visto a los ingenieros de BigQuery hacer una mueca visible cada vez que menciono el uso de UNNEST varias veces, no estoy muy convencido aquí.

Afortunadamente, una vez más podemos recurrir a SELECCIONAR DESDE UNNEST para que nos ayuden aquí. Primero, obtendré el valor de nuestro parámetro de valor, como en nuestro primer ejemplo:

SELECT event_name, event_timestamp, user_pseudo_id,
  (SELECCIONAR value.int_value FROM UNNEST (event_params)
    WHERE key = "value") AS steps_used
DE `firebase-public-project.analytics_153293282.events_20181003`
DONDE event_name = "Pass_virtual_currency"

Y luego también puedo obtener el valor de nuestra propiedad de usuario. (Tenga en cuenta que este valor se almacena como una cadena, por lo que voy a seguir adelante y convertirlo como un entero)

SELECT event_name, event_timestamp, user_pseudo_id,
  (SELECCIONAR value.int_value FROM UNNEST (event_params)
    WHERE key = "value") AS steps_used,
  EMITIR(
    (SELECCIONAR value.string_value FROM UNNEST (user_properties)
     WHERE key = "initial_extra_steps")
   AS int64) AS initial_steps
DE `firebase-public-project.analytics_153293282.events_20181003`
DONDE event_name = "Pass_virtual_currency"

... y ahora tengo cada uno de estos valores uno al lado del otro en la misma fila. ¡Hurra!

No solo este código es más eficiente (creo), sino que es más fácil de entender.

Desde aquí, es bastante fácil analizar estos parámetros. Podemos ver cuál es nuestro valor promedio de pasos adicionales utilizados para cada tipo de propiedad de usuario ...

SELECT AVG (steps_used) AS average_steps_used, initial_steps
DESDE (
  SELECT event_name, event_timestamp, user_pseudo_id,
    (SELECCIONAR value.int_value FROM UNNEST (event_params)
      WHERE key = "value") AS steps_used,
    EMITIR(
      (SELECCIONAR value.string_value FROM UNNEST (user_properties)
       WHERE key = "initial_extra_steps")
     AS int64) AS initial_steps
  DE `firebase-public-project.analytics_153293282.events_20181003`
  DONDE event_name = "Pass_virtual_currency"
)
DONDE initial_steps NO ES NULO
GROUP BY initial_steps
ORDER BY initial_steps

... o podríamos ver si hay algún tipo de correlación entre estos dos valores.

SELECT CORR (steps_used, initial_steps) AS correlation
DESDE (
SELECT event_name, event_timestamp, user_pseudo_id,
  (SELECCIONAR value.int_value FROM UNNEST (event_params)
    WHERE key = "value") AS steps_used,
  EMITIR(
    (SELECCIONAR value.string_value FROM UNNEST (user_properties)
     WHERE key = "initial_extra_steps")
   AS int64) AS initial_steps
DE `firebase-public-project.analytics_153293282.events_20181003`
DONDE event_name = "Pass_virtual_currency"
)
DONDE initial_steps NO ES NULO

¡Ahí tienes, amigos! Entre el uso de UNNEST y SELECT FROM UNNEST, puede hacer un trabajo rápido de todos esos registros repetidos que Google Analytics para Firebase le gusta usar en su esquema BigQuery. Y, como resultado, estos son los mismos tipos de registros repetidos que también aparecen en los datos de Crashlytics y Cloud Messaging. Por lo tanto, recomiendo tomarse el tiempo para acostumbrarse a estas técnicas, porque definitivamente harán su vida mucho más fácil.

Entonces, ¿qué más quieres saber sobre el uso de BigQuery con tus datos de Firebase? ¡Déjame saber en los comentarios a continuación, y puedo trabajar en otra publicación!