Utilisation de BigQuery, Firebase Analytics pour engager, attirer et évaluer les utilisateurs de votre application

Conseils utiles et transformations importantes lorsque vous traitez des données de flux de clics avec des exemples de requête puissants.

Vous avez développé une application étonnante, mais comment savoir si les utilisateurs l’utilisent correctement ou quelles parties de l’application les intéressent le plus? Firebase Analytics est l’une des meilleures bibliothèques gratuites disponibles pour enregistrer vos analyses utilisateur. Que votre application soit pour le Web, iOS, Android ou même Flutter, Firebase Analytics peut y être intégré. Une fois que vous avez intégré la bibliothèque d’analyse, vous devez enregistrer tous les événements qui vous intéressent ainsi que tous les paramètres spécifiques à cet événement, comme app_purchase(montant des achats), audio_play(identifiant audio), etc. Firebase par défaut vous montre un certain nombre de graphiques différents pour interpréter les événements que vous enregistrez. Si vous souhaitez effectuer une analyse plus personnalisée, vous pouvez toujours lier vos analyses Firebase à BigQuery dans le tableau de bord Firebase. BigQuery contiendra tous les événements de données brutes et vous pouvez les traiter pour effectuer une analyse plus complexe. Cet article précise comment… ..

Vous venez de créer et de lancer votre application mobile (ou site Web) et vous souhaitez maintenant savoir comment interroger cet énorme ensemble de données que Firebase génère pour vous chaque seconde au moment où nous parlons. Ou vous êtes simplement curieux de connaître certaines requêtes utiles sur les données d’un événement clickstream qui peuvent vous fournir des informations exploitables. Quoi qu’il en soit, bravo et bienvenue dans le tutoriel. Aujourd’hui, nous allons parler de trucs, astuces et bien plus encore quand il s’agit de travailler avec Google BigQuery. Commençons!

Voici à quoi devrait ressembler votre console une fois que vous avez lié les analyses Firebase à BigQuery. Je vais mentionner sur cet écran quelques éléments que j’utilise au quotidien:

• Éditeur de requêtes: Considérez-le comme un tableau noir pour nous d’écrire nos requêtes.
• Bouton Enregistrer la requête: bouton pour enregistrer une requête importante pour référence future.
• Requêtes enregistrées: c’est là que se trouvent toutes les requêtes que nous allons enregistrer à l’aide du bouton Enregistrer la requête.
• Historique des requêtes: il stocke tout les requêtes que vous avez exécutées dans le passé (utile lorsque vous souhaitez réutiliser une requête).

Maintenant que nous comprenons la disposition de base, plongons-nous dans quelques requêtes utiles.

Firebase est bon pour suivre beaucoup de événements des données à chaque clic sur votre application ou votre site Web. Par exemple, des informations concernant le moment où l’application a été ouverte pour la première fois, la dernière mise à jour de l’application, le moment où un achat intégré a été effectué, etc. Cela signifie que l’heure et la date associées à chacun de ces événements peuvent être très utiles pour fournir des informations. Par défaut, Firebase enregistre la date et l’heure d’un événement en tant que entier dans la colonne event_timestamp.

Cet entier est essentiellement l’heure (en microsecondes, UTC) à laquelle l’événement a été enregistré côté client. Il peut être tentant de convertir cet entier en deux colonnes de date et d’heure distinctes (lisibles par l’homme) à l’aide de FORMAT_DATE & FORMAT_TIME:

SELECT 
FORMAT_DATE('%d-%m-%Y', PARSE_DATE('%Y%m%d', event_date)) AS date, 
FORMAT_TIME('%T', TIME(TIMESTAMP_MICROS(event_timestamp))) AS time
FROM analytics_xxxxxxxxx.events_*

ce qui donne la sortie soignée suivante:

Si votre objectif final est de présenter ces deux colonnes à vos collègues ou à d’autres parties prenantes, ce format devrait vous convenir.

Cependant, il est probable que vous fassiez beaucoup de transformations sur votre ensemble de données en utilisant la colonne de date et d’heure du fichier journal de votre événement. Dans ce cas, FORMAT_DATE ne serait pas la transformation la plus efficace. En effet, la sortie renvoyée par celle-ci est de type chaîne tandis que la plupart des fonctions de date et d’heure prises en charge par BigQuery (telles que l’ajout de deux dates, la troncature de la date à une granularité spécifique) nécessitent que la date soit au format DATE ou DATETIME.

Dans de tels cas, j’aime extraire la date et l’heure de event_timestamp en utilisant TIMESTAMP_MICROS fonction, puis la conversion de cet horodatage en tant qu’objet DATETIME à l’aide CAST AS DATETIME:

SELECT
CAST(TIMESTAMP_MICROS(event_timestamp) AS DATETIME) As UTC_Time 
FROM analytics_xxxxxxxxx.events_*

Ce format est beaucoup plus clair par rapport à l’entier qui était stocké dans le event_timestamp colonne. Dans le même temps, nous avons réussi à conserver le format DATETIME de sorte que toutes les transformations de date ou d’heure soient toujours possibles. Nous illustrons une telle utilisation de ce format dans un exemple ci-dessous.

Conversion de l’heure UTC en heure locale

L’UTC est le fuseau horaire par défaut utilisé par Firebase pour stocker la date et l’heure associées à un événement. Il existe une autre fonctionnalité appelée «Time_zone_offset_seconds» enregistré dans la base de données des événements qui stocke le décalage par rapport à GMT en quelques secondes (peut être positif ou négatif selon le pays). Ainsi, l’ajout de ce décalage à une heure UTC donnée devrait nous donner l’heure locale correcte.

Voici la requête utilisant CTE (Common Table Expression) pour calculer l’heure locale. Il vérifie d’abord si le décalage est NULL, auquel cas nous définissons l’heure UTC comme heure locale. En cas de décalage non nul, nous convertissons d’abord les secondes de décalage en heures et les ajoutons à la date UTC en utilisant DATETIME_ADD:

WITH CTE AS (
SELECT *,
CAST(TIMESTAMP_MICROS(event_timestamp) AS DATETIME) As UTC_Time,
device.time_zone_offset_seconds AS offset_seconds,
FROM analytics_xxxxxxxxx.events_*
)SELECT UTC_Time, offset_seconds,
CASE
WHEN offset_seconds is NULL then UTC_Time
ELSE DATETIME_ADD(
UTC_Time, INTERVAL CAST(offset_seconds/3600 AS INT64) HOUR)
END As local_time
FROM CTE

Pour être honnête, j’étais assez gêné par les compensations nulles dans ma base de données (et mon garçon il y en avait beaucoup!). J’ai donc décidé de résoudre certains d’entre eux en codant à la main le temps de décalage en vérifiant le pays dans le geo.country colonne stockée par Firebase.

Ainsi, la mise à jour de la requête ci-dessus ressemble à:

WITH CTE AS (
SELECT *,
CAST(TIMESTAMP_MICROS(event_timestamp) AS DATETIME) As UTC_Time,
device.time_zone_offset_seconds AS offset_seconds,
FROM analytics_xxxxxxxxx.events_*
)
SELECT *, 
CASE
WHEN offset_seconds IS NULL AND geo.country IN ('India', 'Pakistan') THEN DATETIME_ADD(UTC_Time, INTERVAL CAST(5.5 AS INT64) HOUR) 
WHEN offset_seconds IS NULL AND geo.country = 'United States' THEN DATETIME_SUB(UTC_Time, INTERVAL CAST(5 AS INT64) HOUR) 
WHEN offset_seconds IS NULL AND geo.country = 'Australia' THEN DATETIME_ADD(UTC_Time, INTERVAL CAST(10 AS INT64) HOUR) 
WHEN offset_seconds IS NULL AND geo.country = 'Canada' THEN DATETIME_SUB(UTC_Time, INTERVAL CAST(5 AS INT64) HOUR) 
WHEN offset_seconds IS NULL AND geo.country = 'China' THEN DATETIME_ADD(UTC_Time, INTERVAL CAST(8 AS INT64) HOUR) 
WHEN offset_seconds IS NULL AND geo.country IN ('France', 'Spain', 'Germany', 'Sweden', 'Italy', 'Sweden', 'United Kingdom', 'Ireland') THEN DATETIME_ADD(UTC_Time, INTERVAL CAST(1 AS INT64) HOUR) 
WHEN offset_seconds IS NOT NULL THEN DATETIME_ADD(UTC_Time, INTERVAL CAST(IFNULL(offset_seconds,0)/3600 AS INT64) HOUR) 
ELSE UTC_time
END As local_time
FROM CTE

Ouf, ça a l’air beaucoup mieux! Nous avons réussi à fixer l’heure pour certains pays traditionnels qui enregistrent beaucoup de trafic sur l’application.

P.S. Les décalages que j’ai utilisés ne sont pas exacts, par exemple, le Canada en tant que pays observe différents fuseaux horaires à l’ouest et à l’est, mais dans le cadre de ce didacticiel, j’ai choisi la voie la plus facile.

Transformations sur l’heure locale pour extraire le jour de la semaine, le nom du jour de la semaine et l’heure de la journée

Une fois l’heure locale déterminée, nous pouvons aller plus loin en y introduisant de nouvelles transformations. La requête finale incluant tout le code ci-dessus ressemble à ceci:

WITH CTE AS (
SELECT *,
CAST(TIMESTAMP_MICROS(event_timestamp) AS DATETIME) As UTC_Time,
device.time_zone_offset_seconds AS offset_seconds,
FROM analytics_xxxxxxxxx.events_*
),CTE2 AS (
SELECT *, 
CASE
WHEN offset_seconds IS NULL AND geo.country IN ('India', 'Pakistan') THEN DATETIME_ADD(UTC_Time, INTERVAL CAST(5.5 AS INT64) HOUR) 
WHEN offset_seconds IS NULL AND geo.country = 'United States' THEN DATETIME_SUB(UTC_Time, INTERVAL CAST(5 AS INT64) HOUR) 
WHEN offset_seconds IS NULL AND geo.country = 'Australia' THEN DATETIME_ADD(UTC_Time, INTERVAL CAST(10 AS INT64) HOUR) 
WHEN offset_seconds IS NULL AND geo.country = 'Canada' THEN DATETIME_SUB(UTC_Time, INTERVAL CAST(5 AS INT64) HOUR) 
WHEN offset_seconds IS NULL AND geo.country = 'China' THEN DATETIME_ADD(UTC_Time, INTERVAL CAST(8 AS INT64) HOUR) 
WHEN offset_seconds IS NULL AND geo.country IN ('France', 'Spain', 'Germany', 'Sweden', 'Italy', 'Sweden', 'United Kingdom', 'Ireland') THEN DATETIME_ADD(UTC_Time, INTERVAL CAST(1 AS INT64) HOUR) 
WHEN offset_seconds IS NOT NULL THEN DATETIME_ADD(UTC_Time, INTERVAL CAST(IFNULL(offset_seconds,0)/3600 AS INT64) HOUR) 
ELSE UTC_time
END As local_time
FROM CTE
)SELECT 
DATE(local_time) as date, -- returns a DATE
EXTRACT(DAYOFWEEK FROM DATE(local_time)) as day_of_Week, 
FORMAT_DATE('%a', DATE(local_time)) as weekday_name,
TIME(local_time) as time, -- returns a TIME
CASE
WHEN TIME(local_time) BETWEEN '06:00:00' AND '11:00:00' THEN 'Morning'
WHEN TIME(local_time) BETWEEN '11:00:00' AND '16:00:00' THEN 'Afternoon'
WHEN TIME(local_time) BETWEEN '16:00:00' AND '19:00:00' THEN 'Evening'
WHEN TIME(local_time) BETWEEN '19:00:00' AND '23:59:00' THEN 'Night'
ELSE 'LateNight'
END AS time_of_day
from CTE2

Remarque:day_of_Week est un nombre avec dimanche = 1; Lundi = 2, et ainsi de suite.

Ces transformations sur l’heure locale peuvent être très puissantes pour évaluer quand les utilisateurs de votre application sont les plus actifs ou quel jour de la semaine serait-il judicieux de leur envoyer des e-mails marketing pour les achats via l’application.

Les systèmes de recommandation suggèrent des éléments qui intéressent les utilisateurs en fonction de leurs préférences explicites (par exemple: évaluations) et implicites (par exemple: temps passé), des préférences des autres utilisateurs et des attributs des utilisateurs et des éléments. À son niveau le plus élémentaire, le bloc de données requis pour créer de telles recommandations pour votre application aurait besoin de trois colonnes: user_id, item_id et rating. Voyons comment l’interroger à partir de la base de données.

Je travaille avec un ensemble de données de podcast depuis quelques semaines maintenant; J’ai enregistré un événement personnalisé appelé logReview. Cet événement est déclenché chaque fois que quelqu’un essaie d’évaluer un podcast sur l’application et enregistre quatre informations principales (entre autres):

• collectionId: identifiant du podcast
• guid: épisode id
• note: un entier entre 1 et 5
• interactionType: 0- supprimer; 1- créer; 2- éditer

Voici à quoi ressemblent les lignes pour cet événement pour un seul utilisateur:

SELECT user_pseudo_id, event_name, event_params
FROM analytics_xxxxxxxxx.events_*
WHERE event_name LIKE '%logReview%'

Je sais que ça a l’air effrayant au début, mais soyez indulgent avec moi quand j’explique ce qui se passe. Plutôt qu’il n’y ait qu’une seule ligne pour chacun de ces paramètres d’événement (tels que évaluation, guid), ils sont regroupés dans une sorte d’objet JSON avec des paires clé-valeur uniques. Je pourrais l’expliquer plus en détail mais rien ne se rapprocherait Todd KerpelmanLa formidable explication de ici.

L’essentiel est que nous ne pouvons extraire que les paramètres d’événements que nous souhaitons utiliser UNNEST comme dans la requête ci-dessous. Encore une fois, un grand merci à Todd pour m’avoir sauvé tant d’heures de problèmes et expliqué cela magnifiquement cette Publier.

SELECT user_pseudo_id,
(SELECT value.int_value
FROM UNNEST(event_params) WHERE key = 'rating'
) AS rating
FROM analytics_xxxxxxxxx.events_*
WHERE event_name LIKE '%logReview%'

Ce que nous faisons ici, c’est de dire à BigQuery unnest ou séparé le event_params dans des rangées séparées et choisissez le int_value à partir des seules lignes où key = rating.

La sortie ressemble à ceci:

Cela a l’air génial, sauf que nous ne savons toujours pas pour quel épisode de podcast cette note est destinée. Nous allons donc continuer et extraire l’ID de l’épisode et l’ID du podcast également.

SELECT user_pseudo_id,
(SELECT value.int_value
FROM UNNEST(event_params) WHERE key = 'rating'
) AS rating,
(SELECT value.string_value
FROM UNNEST(event_params) WHERE key = 'guid'
) AS episode_id,
(SELECT value.int_value
FROM UNNEST(event_params) WHERE key = 'collectionId'
) AS podcast_idFROM analytics_xxxxxxxxx.events_*
WHERE event_name LIKE '%logReview%'

Cela ressemble presque parfait! Pourquoi demandez-vous presque? En effet, nous n’avons pas pris en compte si cette note a été modifiée ou non (n’oubliez pas que nous stockons le interactionType pour chaque logReview un événement).

Logiquement, nous souhaitons conserver la dernière note attribuée par l’utilisateur à l’épisode de podcast. Pour ce faire, nous utiliserons le LAST_VALUE fonction fenêtre, PARTITIONED BY identifiant d’utilisateur et episode_id et ORDERED BY horodatage:

WITH CTE AS (
SELECT user_pseudo_id,
(
SELECT value.int_value
FROM UNNEST(event_params) WHERE key = 'rating'
) AS rating,
(
SELECT value.string_value
FROM UNNEST(event_params) WHERE key = 'guid'
) AS episode_id,
(
SELECT value.int_value
FROM UNNEST(event_params) WHERE key = 'collectionId'
) AS podcast_id,
(
SELECT value.int_value
FROM UNNEST(event_params) WHERE key = 'interactionType'
) AS interaction_type,
event_timestamp, timeFROM analytics_xxxxxxxxx.events_*
WHERE event_name LIKE '%logReview%'
)SELECT DISTINCT user_pseudo_id, episode_id, podcast_id,
LAST_VALUE(rating) OVER (PARTITION BY user_pseudo_id, episode_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  AS latest_rating 
FROM CTE

Qu’est-ce que LAST_VALUE... l’appel consiste essentiellement à partitionner les données, d’abord par identifiant utilisateur et au sein de chaque identifiant utilisateur, par épisode de podcast. Puis dans chacun de ces épisode utilisateur segments (ou partitions), les lignes résultantes sont ordonnées par l’horodatage de l’événement. Enfin, la valeur d’évaluation correspondant à la dernière ligne de la partition est sélectionnée.

Voila! Nous l’avons fait. 🙂 Nous avons maintenant la trame de données requise pour commencer notre modélisation d’un système de recommandation. Vous pouvez continuer et cliquer sur le bouton Enregistrer les résultats pour exporter le bloc de données localement sur votre ordinateur.

Il est naturel qu’en tant que développeur d’application, vous déployiez une nouvelle version de votre application toutes les quelques semaines. Idéalement, la nouvelle version aura plus de fonctionnalités et de fonctionnalités et aurait résolu quelques bugs désagréables de la version précédente de l’application. Par conséquent, il est important que vous vous assuriez que la plupart de vos premiers utilisateurs sont passés à cette dernière version, et s’ils ne le font pas, de leur envoyer de petits coups de pouce par e-mail.

Nous allons détecter tous les utilisateurs qui sont des utilisateurs actifs de notre application mais qui ne sont pas passés à la dernière version. Nous considérons les utilisateurs comme actifs qui ont utilisé l’application au cours des 10 derniers jours.

Nous utiliseronsFIRST_VALUE pour détecter quelle version de l’application a été utilisée pour la toute première fois lorsque l’utilisateur a interagi avec notre application. De même, nous utiliserons notre bon ami LAST_VALUE pour détecter la version de l’application associée à l’interaction la plus récente avec l’application et également pour extraire la date à laquelle l’application a été utilisée pour la dernière fois. Nous stockons tout cela dans un CTE.

WITH CTE AS (
SELECT 
user_id,
FIRST_VALUE(app_info.version) OVER (user_event_window) AS initial_version,
LAST_VALUE(app_info.version) OVER (user_event_window) AS latest_version,
LAST_VALUE(event_timestamp) OVER (user_event_window) AS app_last_used
FROM analytics_xxxxxxxxx.events_*
WHERE device.operating_system <> 'WEB' AND user_id IS NOT NULL
WINDOW user_event_window AS (PARTITION BY user_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)

Comme nous ne sommes pas intéressés par les utilisateurs d’applications Web, nous spécifions le WHERE condition pour vérifier le système d’exploitation de l’appareil.

Nous avons également utilisé un WINDOW appel user_event_window pour spécifier la fenêtre d’événement, ce qui rend le code un peu plus propre à travailler (en simplifiant tous les redondants PARTITION BY appels).

Maintenant que nous avons la date de la dernière utilisation de l’application (stockée sous app_last_used, nous pouvons le soustraire de la date d’aujourd’hui en utilisant DATE_DIFF.

DATE_DIFF(CURRENT_DATE(), DATE(TIMESTAMP_MICROS(app_last_used)), DAY) AS gap

Si la résultante est inférieure ou égale à 10, cela signifie que l’utilisateur est un utilisateur actif, et donc la cible de nos e-mails de mise à jour d’application.

Et maintenant, le code FINAL pour récupérer les identifiants des utilisateurs qui doivent recevoir l’e-mail pour la mise à jour de la version de l’application en fonction de leur dernière utilisation.

WITH CTE AS (
SELECT 
user_id,
FIRST_VALUE(app_info.version) OVER (user_event_window) AS initial_version,
LAST_VALUE(app_info.version) OVER (user_event_window) AS latest_version,
LAST_VALUE(event_timestamp) OVER (user_event_window) AS app_last_used
FROM `podcastapp-767c2.analytics_193436959.events_*`
WHERE device.operating_system <> 'WEB' AND user_id IS NOT NULL
WINDOW user_event_window AS (PARTITION BY user_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)SELECT user_id, initial_version, latest_version,
DATE(TIMESTAMP_MICROS(app_last_used)) AS last_used, 
DATE_DIFF(CURRENT_DATE(), DATE(TIMESTAMP_MICROS(app_last_used)), DAY) AS gap 
FROM CTE
WHERE latest_version NOT IN ('1.1.3') -- update the version here in future
GROUP BY user_id, initial_version, latest_version, app_last_used
HAVING gap <= 10