Reconstitution de données historiques avec BigQuery

Imaginez disposer d'une base de données contenant les données d'abonnement à votre plateforme ou à votre produit. La table indique la date de création de l'abonnement et, s'il a été annulé, vous avez cette date également. Mais comment pouvez-vous savoir combien d'abonnements actifs vous aviez un jour donné dans le passé ? Vous ne pouvez pas. Du moins, pas sans reconstituer les données historiques.

Il s'agit d'un défi courant dans de nombreuses entreprises où l'historique des états n'est pas préservé, ce qui rend l'analyse des tendances difficile. Bien que cet article se concentre sur les données d'abonnement, la même technique peut être appliquée à tout scénario où vous devez reconstituer l'historique à partir de données ponctuelles, qu'il s'agisse des statuts des utilisateurs, des niveaux de stock (inventaire) ou de l'état des commandes.

Après avoir lu ce guide, vous serez capable de :

• Reconstituer les décomptes actifs quotidiens à partir d'une table qui ne stocke que les dates de création et d'annulation
• Écrire une requête BigQuery qui génère une chronologie quotidienne complète de l'activité des abonnements
• Comprendre les compromis et les limites de la reconstitution historique
• Choisir la bonne approche pour le suivi continu une fois que le remplissage (backfill) est terminé

Structure des données et défis

Notre source de données est une table BigQuery stg_og__subscriptions contenant les enregistrements d'abonnement avec deux horodatages clés :

• subscription_id — Identifiant unique pour le suivi des abonnements individuels
• created — Horodatage du début de l'abonnement
• cancelled_date — Horodatage du moment où l'abonnement a été annulé. NULL s'il est toujours actif

Pourquoi vous ne pouvez pas interroger l'état historique directement

Notre table d'abonnement ne montre que le dernier état de chaque abonnement : quand il a commencé et, s'il a été annulé, quand cela s'est produit. Vous ne voyez aucun de l'historique entre les deux.

Cela rend la tâche difficile lorsque vous voulez répondre à des questions telles que « combien d'abonnements actifs avions-nous le mois dernier ? » parce que vous devez reconstituer les décomptes historiques à partir de ces états finaux.

Illustrons avec un exemple. Supposons que nous ayons trois enregistrements d'abonnement et que nous souhaitions connaître l'état le 2 janvier 2023 :

Bien que le changement net ait été nul (2 actifs au début, 2 actifs à la fin), la base d'abonnés sous-jacente a complètement changé. C'est pourquoi nous devons suivre les quatre métriques, et pas seulement le compte de fin de journée.

Pour reconstituer ces mesures quotidiennes à partir de données temporelles, nous devons :

• Traiter la date created de chaque abonnement comme un événement "+1" ce jour-là
• Traiter chaque cancelled_date d'abonnement comme un événement "-1" ce jour-là
• Calculer des totaux cumulatifs pour déterminer le nombre actif à tout moment

Sans le suivi de ces changements d'état quotidiens, nous ne pouvons pas mesurer avec précision la croissance, l'attrition (churn) ou effectuer une analyse de cohorte.

Limites de cette approche

La limite clé de cette méthode de reconstitution est son incapacité à capturer les transitions d'état intermédiaires. Nos données ne préservent que la date de création initiale et la date d'annulation finale, ce qui signifie que nous perdons la visibilité des changements d'état survenus entre ces deux points.

Exemple : Un abonnement créé le 1er janvier et annulé le 15 janvier apparaît comme un abonnement continu de 15 jours. Mais l'historique réel aurait pu être : créé le 1er janvier, annulé le 5 janvier, réactivé le 10 janvier, annulé à nouveau le 15 janvier. La reconstitution manque le vide de cinq jours où il était inactif.

Selon votre cas d'utilisation, cette limitation peut être ou ne pas être importante. Si vous avez principalement besoin de suivre la croissance globale des abonnements et la santé générale de l'entreprise, cette méthode de reconstitution fonctionne bien pour l'analyse des données historiques.

Explication de la solution et de la requête

Voyons d'abord la requête complète, puis décomposons chaque composant pour comprendre son fonctionnement :

WITH subscription_metrics AS (
  SELECT
    subscription_id,
    DATE(created) AS event_date,
    1 AS new_subscription,
    0 AS cancelled_subscription
  FROM \`project.dataset.stg_og__subscriptions\`
  WHERE created IS NOT NULL

  UNION ALL
  SELECT
    subscription_id,
    DATE(cancelled_date) AS event_date,
    0 AS new_subscription,
    1 AS cancelled_subscription
  FROM `project.dataset.stg_og__subscriptions`
  WHERE cancelled_date IS NOT NULL
),
daily_aggregates AS (
  SELECT
    event_date,
    SUM(new_subscription) AS new_subscriptions,
    SUM(cancelled_subscription) AS cancelled_subscriptions,
    SUM(new_subscription) - SUM(cancelled_subscription) AS net_change
  FROM subscription_metrics
  GROUP BY event_date
),
date_spine AS (
  SELECT date_day
  FROM UNNEST(
    GENERATE_DATE_ARRAY(
      (SELECT MIN(event_date) FROM daily_aggregates),
      CURRENT_DATE(),
      INTERVAL 1 DAY
    )
  ) AS date_day
),
running_totals AS (
  SELECT
    ds.date_day,
    COALESCE(da.new_subscriptions, 0) AS new_subscriptions,
    COALESCE(da.cancelled_subscriptions, 0) AS cancelled_subscriptions,
    SUM(COALESCE(da.net_change, 0))
      OVER (ORDER BY ds.date_day) AS active_subscriptions_at_end
  FROM date_spine ds
  LEFT JOIN daily_aggregates da
    ON ds.date_day = da.event_date
)
SELECT
  date_day,
  LAG(active_subscriptions_at_end, 1, 0)
    OVER (ORDER BY date_day) AS active_subscriptions_at_start,
  new_subscriptions,
  cancelled_subscriptions,
  active_subscriptions_at_end
FROM running_totals
ORDER BY date_day

La CTE *subscription metrics*

Cette CTE (Common Table Expression) traite les créations et les annulations comme des événements distincts grâce à un UNION ALL. Chaque profil de la table principale générera jusqu'à deux lignes : l'une pour sa date created (avec new_subscription = 1) et une seconde (au besoin) advenant une donnée cancelled_date valide (avec cancelled_subscription = 1). Cela standardise tous les flux.

-- Événements de création
SELECT
  subscription_id,
  DATE(created) AS event_date,
  1 AS new_subscription,
  0 AS cancelled_subscription
FROM \`project.dataset.stg_og__subscriptions\`
WHERE created IS NOT NULL

UNION ALL
-- Événements d'annulation
SELECT
  subscription_id,
  DATE(cancelled_date) AS event_date,
  0 AS new_subscription,
  1 AS cancelled_subscription
FROM `project.dataset.stg_og__subscriptions`
WHERE cancelled_date IS NOT NULL

La CTE *daily aggregates*

Cette étape combine tous les événements se produisant le même jour. Si nous avions 5 nouveaux abonnements et 2 annulations un jour donné, cette CTE les regroupe en une seule ligne avec ces totaux et un net_change de 3.

SELECT
  event_date,
  SUM(new_subscription) AS new_subscriptions,
  SUM(cancelled_subscription) AS cancelled_subscriptions,
  SUM(new_subscription) - SUM(cancelled_subscription) AS net_change
FROM subscription_metrics
GROUP BY event_date

La CTE *date spine*

Crée une série continue de dates depuis le tout premier événement jusqu'à la date d'aujourd'hui, grâce à la fonction GENERATE_DATE_ARRAY. Ceci s'assure que notre rapport ne va pas simplement ignorer ou "sauter" des dates qui viendraient à être exemptes ou creuses en créations d'abonnement ou en désabonnements.

SELECT date_day
FROM UNNEST(
  GENERATE_DATE_ARRAY(
    (SELECT MIN(event_date) FROM daily_aggregates),
    CURRENT_DATE(),
    INTERVAL 1 DAY
  )
) AS date_day

La CTE *running totals*

Ici l'on fusionne le *date spine* avec le journal des événements agrégés. On assigne la valeur zéro aux champs vides par l'entremise de la fonction COALESCE, puis la fenêtre de fonction fait rouler le calcul du nombre total estimé à cette date avec le SUM(...) OVER (ORDER BY ...).

SELECT
  ds.date_day,
  COALESCE(da.new_subscriptions, 0) AS new_subscriptions,
  COALESCE(da.cancelled_subscriptions, 0) AS cancelled_subscriptions,
  SUM(COALESCE(da.net_change, 0))
    OVER (ORDER BY ds.date_day) AS active_subscriptions_at_end
FROM date_spine ds
LEFT JOIN daily_aggregates da
  ON ds.date_day = da.event_date

Dernière extraction

La commande de `LAG` puise logiquement l'accumulation finale de la veille pour entamer celle qu'on a le présent jour. Nous obtiendrons le rendu achevé des abonnements en cours d'un jour précis avec un portrait total pour un début et une fin de journée.

SELECT
  date_day,
  LAG(active_subscriptions_at_end, 1, 0)
    OVER (ORDER BY date_day) AS active_subscriptions_at_start,
  new_subscriptions,
  cancelled_subscriptions,
  active_subscriptions_at_end
FROM running_totals
ORDER BY date_day

Évaluation du résultat de la requête

La requête produit quatre métriques clés pour chaque date :

• active_subscriptions_at_start — Abonnements actifs au début de la journée
• new_subscriptions — Nouveaux abonnements créés ce jour-là
• cancelled_subscriptions — Abonnements annulés ce jour-là
• active_subscriptions_at_end — Abonnements actifs à la fin de la journée

Voici à quoi ressemble le résultat pour les 10 premiers jours de données :

Remarquez comment la valeur `Début` de chaque ligne correspond à la valeur `Fin` de la ligne précédente. Les jours sans activité (comme les 4, 6 et 8 janvier) apparaissent toujours dans la sortie grâce à l'ossature temporelle ininterrompue (le date spine), avec des zéros pour les abonnements nouveaux et annulés.

Aller de l'avant : options de suivi continu

La requête de reconstitution est conçue pour combler et renflouer un historique de données (backfill). Pour un suivi continu à l'avenir, il existe deux approches selon vos besoins.

Agrégation quotidienne (recommandée)

Créez une table de statistiques quotidiennes (daily metrics) qui stocke la date, le nombre de nouveaux abonnements, le nombre d'abonnements annulés et le total des abonnements actifs. Chaque jour, comptez les nouveaux abonnements et ceux annulés, puis mettez à jour le total. Cette approche nécessite peu de frais informatiques, elle est simple à gérer, compréhensible pour vos collaborateurs et répondra positivement à la vaste majorité des besoins d'informations qu'une entreprise e-commerce puisse requérir.

Suivi des modifications en temps réel (Streaming)

Cette approche nécessite une intégration avec votre système d'abonnement pour capturer chaque changement d'état au moment où il se produit. Il doit être mis en œuvre à la source pour s'assurer qu'aucune modification intermédiaire n'est manquée. Elle a des exigences de stockage plus élevées et est plus complexe à maintenir, mais elle fournit une piste d'audit (audit trail) rigoureuse et ininterrompue. N'utilisez cette fonction que si vous devez impérativement suivre chaque changement d'état pour des raisons de conformité stricte.

Conclusion

La reconstitution de votre base de données vous dresse une image approximative assez fiable pour récupérer votre capacité à suivre une courbe (trend) sur une structure antérieure ou défaillante. La rançon pour obtenir votre table sur mesure à ce niveau aisé pour l'informatique, consiste à devoir accepter et ignorer ce vide temporaire advenant où un usager s'était temporairement suspendu entre-temps.

La décision la plus importante est celle que vous prenez à l'avenir. Une fois les données passées recréées, adoptez la simple recommandation de faire agréger les données à base quotidienne (daily aggregation) à vos registres. Exécuter un agrégat est simple, économique et supprime tout biais aléatoire. Si des métriques vous ont échappé—de simples ventes en e-commerce aux historiques sur inventaire—notre équipe restera à votre disposition, contactez-nous rapidement.