# Interroger les données de facturation Utilisez Sigma ou Data Pipeline pour récupérer les informations de facturation. Stripe Billing comprend différents composants qui, associés, fournissent des *factures* (Invoices are statements of amounts owed by a customer. They track the status of payments from draft through paid or otherwise finalized. Subscriptions automatically generate invoices, or you can manually create a one-off invoice) ponctuelles et périodiques. Les différents aspects des données de facturation sont disponibles dans plusieurs tables. Toutes les tables spécifiques à la facturation sont accessibles dans la section **Facturation** du schéma, les tables principales étant `subscriptions` et `invoices`. Pour aller plus loin, vous pouvez utiliser les tables supplémentaires qui représentent les composants des abonnements et des factures, comme `prices`, `products` ou `coupons`. En outre, la table `customers` fait partie intégrante de la facturation et contient des données pour lesquelles vous devrez peut-être créer un rapport. ## Abonnements Chaque ligne du tableau `subscriptions` représente les données d’un objet [Subscription](https://docs.stripe.com/api.md#subscription_object) individuel. Ces mêmes informations sont récupérées par l’API ou accessibles dans le [Dashboard Stripe](https://dashboard.stripe.com/test/subscriptions). Vous pouvez créer un rapport pour chaque abonnement créé sur votre compte. Nous vous recommandons de commencer par cette table pour la création de rapports sur vos abonnés actuels. Vous pouvez la lier à d’autres tables et ainsi explorer vos données plus en détails. ![](https://b.stripecdn.com/docs-statics-srv/assets/subscriptions.b2676e216021e4a013eb4da5f4f839ec.png) L’exemple suivant récupère une liste d’abonnements marqués comme impayés, ainsi que les coordonnées du client, le cas échéant. ```sql select subscriptions.id, subscriptions.customer_id, customers.email from subscriptions inner join customers on customers.id = subscriptions.customer_id where subscriptions.status = 'unpaid' limit 5 ``` | id | customer_id | Adresse e-mail | | ------------------- | ------------------- | --------------------------- | | sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | florence.martin@exemple.com | | sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | pierre.durand@exemple.fr | | sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | philippe.legrain@exemple.fr | | sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | sylvie.dumont@exemple.fr | | sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | elias.boudet@exemple.fr | ## Clients Les données relatives aux objets [Customer](https://docs.stripe.com/api.md#customers) figurent dans la table `customers` (elles ne font pas partie du groupe Tables Billing). Elles sont couramment utilisées dans le cadre des rapports basés sur la facturation et peuvent être liées à un certain nombre de tables. Elles sont également utiles si vous créez des [paiements](https://docs.stripe.com/stripe-data/query-transactions.md) avec des informations de paiement enregistrées. ![](https://b.stripecdn.com/docs-statics-srv/assets/customers.60b1588c3375261e7f8b6ec1a4ab8302.svg) L’exemple suivant génère une liste de clients disposant d’un abonnement et qui bénéficient actuellement d’une période d’essai, avec leur ID et leur adresse e-mail. ```sql select customers.id, customers.email, subscriptions.price_id from subscriptions inner join customers on customers.id = subscriptions.customer_id where subscriptions.status = 'trialing' limit 5 ``` | id | Adresse e-mail | price_id | | ------------------- | --------------------------- | -------------- | | cus_orWziM4j7CiRL8J | florence.martin@exemple.com | ruby-pro-522 | | cus_orWziM4j7CiRL8J | pierre.durand@exemple.fr | ruby-pro-522 | | cus_orWziM4j7CiRL8J | richard.dupont@exemple.fr | gold-basic-221 | | cus_orWziM4j7CiRL8J | sylvie.dumont@exemple.fr | gold-basic-221 | | cus_orWziM4j7CiRL8J | elias.boudet@exemple.fr | silver-pro-498 | ## Produits et tarifs Les produits décrivent les articles que vos clients peuvent acheter avec un abonnement. Les tarifs sont liés aux produits et définissent le coût, l’intervalle de facturation et la devise. Lorsque vous consultez des données dans le tableau `subscriptions`, vous pouvez les lier à `subscription_items`. En outre, vous pouvez les lier à `products.id` en utilisant le `price_product_id` de l’article. L’exemple suivant renvoie une liste des abonnements actifs avec le nom du produit et son libellé de relevé bancaire : ```sql with active_subscriptions as ( select s.id as subscription_id, p.name as product_name, p.statement_descriptor from subscriptions s join subscription_items si on s.id = si.subscription_id join products p on si.price_product_id = p.id where s.status = 'active' ) select subscription_id, subscription_item_id, price_id, product_name, statement_descriptor from active_subscriptions order by 1,2 ``` | id | nom | statement_descriptor | | ------------------- | ----------------- | -------------------- | | sub_orWziM4j7CiRL8J | ruby-pro-522 | Ruby Pro | | sub_orWziM4j7CiRL8J | gold-basic-221 | Gold Basic | | sub_orWziM4j7CiRL8J | silver-pro-498 | Silver Pro | | sub_orWziM4j7CiRL8J | diamond-mid-244 | Diamond Mid | | sub_orWziM4j7CiRL8J | ruby-standard-196 | Ruby Standard | ## Niveaux de tarifs Lorsque vous utilisez des [niveaux](https://docs.stripe.com/products-prices/pricing-models.md#tiered-pricing) de tarifs pour vos abonnements, le tableau `price_tiers` peut fournir des données spécifiques sur chaque niveau. Par exemple, si vous voulez comprendre des éléments du premier niveau de vos abonnements, tels que la quantité maximale et le montant unitaire utilisé, utilisez la requête suivante : ```sql with subscription_item_prices as ( select si.subscription_id, si.price_id, p.currency from subscription_items si join prices p on si.price_id = p.id ), price_tier_details as ( select sp.subscription_id, pt.price_id, pt.upto, stringify_amount(sp.currency, pt.amount, '.') as tier_price, sp.currency from subscription_item_prices sp join price_tiers pt on sp.price_id = pt.price_id ) select ptd.subscription_id, ptd.price_id, ptd.upto, ptd.tier_price, ptd.currency from price_tier_details ptd order by ptd.subscription_id, ptd.price_id, ptd.upto asc ``` | subscription_id | price_id | upto | tier_price | devise | | ------------------- | --------------------- | ---- | ---------- | ------ | | sub_orWziM4j7CiRL8J | price_orWziM4j7CiRL8J | 30 | 2 | USD | | sub_orWziM4j7CiRL8J | price_orWziM4j7CiRL8J | 60 | 1 | USD | | sub_orWziM4j7CiRL8J | price_orWziM4j7CiRL8J | 90 | 0,50 | USD | ## Factures Pour en savoir plus sur les factures et les postes de facture, consultez notre documentation consacrée aux [factures](https://docs.stripe.com/invoicing/overview.md). La table `invoices` contient des données sur les objets [Invoice](https://docs.stripe.com/api.md#invoice_object). Chaque abonnement génère une facture récurrente qui représente le montant dû par le client, à savoir le montant de l’abonnement ainsi que tous les [postes de facture](https://docs.stripe.com/api.md#invoiceitems) supplémentaires qui ont été créés (et répertoriés comme tels). Les factures sont constituées de [postes de facture](https://docs.stripe.com/api.md#invoice_line_item_object) individuels. Ces postes correspondent aux abonnements pour lesquels le client est facturé, ainsi qu’aux postes de facture qui ont été créés et appliqués à la facture. Pour décomposer une facture et analyser chacun de ses postes, utilisez la table `invoice_line_items`. La colonne `source_id` de cette table contient l’**ID** de l’abonnement (par exemple, `sub_orWziM4j7CiRL8J`) ou du poste de facture (par exemple, `ii_orWziM4j7CiRL8J`) auquel correspond le poste. La colonne `source_type` permet de déterminer si les postes représentent un abonnement ou un poste de facture. Contrairement à d’autres clés étrangères, la colonne `subscription` de la table `invoice_line_items` n’est pas toujours renseignée. Si le poste de facture correspondant est un abonnement, cette colonne est vide : son **ID** apparaît déjà dans la colonne `source_id`. ### Postes de facture Les données relatives aux [postes de facture](https://docs.stripe.com/api.md#invoiceitems) sont disponibles dans la table `invoice_items`. Les postes de facture sont couramment utilisés pour spécifier un montant supplémentaire (ou déduire un montant) qui est appliqué à la facture suivante au début du cycle de facturation suivant. Par exemple, vous pouvez créer un poste de facture si vous devez facturer votre client car il a dépassé son plafond mensuel, ou si vous devez offrir un crédit sur la facture suivante pour un service non utilisé. ![](https://b.stripecdn.com/docs-statics-srv/assets/invoices.ed8822925c41f368821ba104bd52efb1.svg) L’exemple suivant récupère toutes les factures et tous les ID de paiement associés pour un abonnement spécifique. ```sql select id, charge_id, amount_due from invoices where subscription_id = 'sub_ALJXL9gBYtv6GJ' ``` | id | nom | | ------------------ | ------------------ | | in_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1999 | | in_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1999 | | in_orWziM4j7CiRL8J | 1999 | ch_orWziM4j7CiRL8J | | in_orWziM4j7CiRL8J | 1999 | ch_orWziM4j7CiRL8J | | in_orWziM4j7CiRL8J | 1999 | ch_orWziM4j7CiRL8J | ### Totaux de factures et ristournes Le sous-total de la facture correspond à la somme de tous les abonnements, postes de facture et calculs au prorata sur la facture avant l’application d’une ristourne. Le total de la facture correspond au montant après l’application des ristournes et des taxes : `invoice.total` = `invoice.subtotal` - `discount` + `invoice.tax` Aucune colonne ne correspond au montant de la réduction sur une facture. Vous pouvez cependant la calculer en faisant la somme de tous les montants de réduction des postes. La requête suivante renvoie une liste avec des factures, le début et la fin de leur période ainsi que le montant total de la remise pour chaque facture. ```sql with invoices_with_discounts as ( select invoice_id, sum(amount) as total_discount_amount from invoice_line_item_discount_amounts group by invoice_id ) select i.id as invoice_id, i.period_start, i.period_end, stringify_amount(i.currency, ilda.total_discount_amount, '.') as total_discount_amount i.currency from invoices i join invoices_with_discounts ilda on i.id = ilda.invoice_id order by i.id ``` | invoice_id | period_start | period_end | total_discount_amount | devise | | ------------------ | ------------ | ---------- | --------------------- | ------ | | in_orWziM4j7CiRL8J | 01/05/2024 | 01/06/2024 | 24,66 | USD | | in_orWziM4j7CiRL8J | 01/06/2024 | 01/07/2024 | 24,34 | USD | | in_orWziM4j7CiRL8J | 01/04/2024 | 01/05/2024 | 45,96 | USD | ### Utilisation des dates et des périodes de facturation Les factures d’abonnement sont pré-facturées, ce qui signifie que le client effectue le paiement au début d’un cycle de facturation. La date est précisée dans la valeur `period` d’un poste. Par exemple, un client ayant souscrit un abonnement mensuel est facturé au début de chaque mois. S’il annule son abonnement à l’aide de [cancel_at_period_end](https://docs.stripe.com/api/subscriptions/object.md#subscription_object-cancel_at_period_end), son abonnement reste actif jusqu’à la fin du mois, date à laquelle il prendra fin. Les valeurs `period_start` et `period_end` d’une facture indiquent la date de création des postes de facture. Elles ne correspondent pas forcément à la période de facturation du client. Par exemple, si un client est facturé le premier de chaque mois et qu’il dépasse son plafond mensuel le 15, vous pouvez créer un poste de facture pour les frais supplémentaires facturés au client. Ce poste est ensuite inclus dans la facture suivante, qui est créée le premier du mois suivant. Lorsque la facture suivante est générée, la date `period_start` correspond au 15 du mois précédent, date à laquelle le poste supplémentaire a été créé pour la première fois. ## Facturation à l’usage La [facturation à l’usage](https://docs.stripe.com/billing/subscriptions/usage-based.md) vous permet de facturer vos clients en fonction de la quantité de produit ou service qu’ils utilisent. ### Dispositif de mesure de la facturation Un objet [Meter](https://docs.stripe.com/api/billing/meter.md) indique comment agréger les événements de mesure sur une période de facturation. Les événements liés aux objets Meter représentent toutes les actions que les clients effectuent dans votre système (notamment les requêtes à l’API). Les objets Meter sont associés à des tarifs et constituent la base de la facture. Ces objets sont disponibles via la table `billing_meters`. La requête suivante renvoie tous les dispositifs de mesure de la facturation actifs. ```sql select id, status, display_name, default_aggregation_formula from billing_meters where status = 'ACTIVE' and livemode ``` | ID | état | display_name | default_aggregation_formula | | ------------------- | ----- | --------------------- | --------------------------- | | mtr_orWziM4j7CiRL8J | ACTIF | alpaca_ai_token | SOMME | | mtr_orWziM4j7CiRL8J | ACTIF | alpaca_ai_image_token | NOMBRE | ### Récapitulatifs des événements de mesure de la facturation Un objet [Billing Meter Event Summary](https://docs.stripe.com/api/billing/meter-event-summary.md) représente une vue agrégée des événements de mesure de la facturation d’un client sur une période donnée. Il représente la quantité cumulée consommée par un client au cours de cette période. Ces objets sont disponibles via la table `billing_meter_event_summaries`. Des récapitulatifs horaires sont disponibles, comme l’indique la colonne `value_grouping_window`. La requête suivante renvoie tous les événements de mesure de la facturation non valides pour un client spécifique. ```sql select billing_meters.display_name, sum(billing_meter_event_summaries.aggregated_value) AS total_usage from billing_meter_event_summaries join billing_meters on billing_meters.id = billing_meter_event_summaries.meter_id where billing_meter_event_summaries.customer_id = 'cus_EDQkYj7P2Jf3sJ1' and billing_meter_event_summaries.start_time >= timestamp '2025-02-01 08:00' and billing_meter_event_summaries.end_time <= timestamp '2025-02-01 20:00' and value_grouping_window = 'hourly' group by display_name ``` | display_name | total_usage | | --------------------- | ----------- | | alpaca_ai_token | 227416 | | alpaca_ai_image_token | 22 | ### Billing analyse utilisation de compteurs Les objets [Analyses de l’utilisation du compteur de facturation](https://docs.stripe.com/api/billing/analytics/meter-usage.md) représentent un résumé analytique de l’utilisation du compteur de facturation d’un client sur une période spécifiée. Ils peuvent être groupés ou filtrés par compteurs, dimensions et locataires pour alimenter des dashboards d’analytique client. Le [guide d’intégration](https://docs.stripe.com/billing/subscriptions/usage-based/analytics.md) illustre les formes de requête et de réponse. Cette API est disponible en aperçu public. pour demander l’accès à cette API. ### Événement de mesure de la facturation non valides Un objet [Billing Meter Invalid Event](https://docs.stripe.com/billing/subscriptions/usage-based/recording-usage-api.md#handle-meter-event-errors) représente un événement de mesure de la facturation qui n’a pas été validé. Ces objets sont disponibles via la table `billing_meter_invalid_events`. La table `billing_meter_invalid_events_payload` associée contient la charge utile de l’événement initial. La requête suivante renvoie tous les événements de mesure de la facturation non valides pour un client spécifique. ```sql SELECT billing_meter_invalid_events.id as event_id, billing_meter_invalid_events.error_code, billing_meter_invalid_events.error_message FROM billing_meter_invalid_events JOIN billing_meter_invalid_events_payload ON billing_meter_invalid_events_payload.event_id = billing_meter_invalid_events.id WHERE billing_meter_invalid_events_payload.key = 'stripe_customer_id' AND billing_meter_invalid_events_payload.value = 'cus_EDQkYj7P2Jf3sJ1' ``` | event_id | error_code | error_message | | ------------------------------------ | --------------- | -------------------------------------------------------------------------- | | orWziM4j*orWz*orWz*orWz*orWziM4j7CiR | METER_NOT_FOUND | Aucun dispositif de mesure ne correspond à event_name mtr_orWziM4j7CiRL8J. | | orWziM4j*orWz*orWz*orWz*orWziM4j7CiR | METER_NOT_FOUND | Aucun dispositif de mesure ne correspond à event_name mtr_orWziM4j7CiRL8J. | ## Coupons Un objet [Coupon](https://docs.stripe.com/billing/subscriptions/coupons.md) correspond à un montant ou à un pourcentage de réduction pouvant s’appliquer à des abonnements ou à des clients. ```sql select coupons.id, coupons.amount_off, coupons.percent_off from coupons where valid = false limit 5 ``` | id | amount_off | percent_off | | -------- | ---------- | ----------- | | 10FF | | 10 | | SUMMER25 | | 25 | | 10FREE | 10 | | | 15OFF | | 15 | | FALL30 | 30 | | ## Discounts Une réduction correspond à l’application d’un bon de réduction représenté par un objet [Discount](https://docs.stripe.com/api.md#discounts). La requête suivante renvoie une liste des abonnements avec les réductions et les bons de réduction qui leur sont associés : ```sql select subscriptions.id as subscription_id, t.discount_id, coupons.id as coupon_id from subscriptions cross join unnest(split(subscriptions.discounts, ',')) as t(discount_id) join discounts on discounts.id = t.discount_id join coupons on coupons.id = discounts.coupon_id limit 3 ``` | subscription_id | discount_id | coupon_id | | ------------------- | ------------------ | --------- | | sub_orWziM4j7CiRL8J | di_orWziM4j7CiRL8J | 10OFF | | sub_orWziM4j7CiRL8J | di_orWziM4j7CiRL8J | 25OFF | | sub_orWziM4j7CiRL8J | di_orWziM4j7CiRL8J | 10FREE | ## Codes promotionnels Un [code promotionnel](https://docs.stripe.com/api/promotion_codes.md) est un code utilisable par le client pour obtenir un [bon de réduction](https://docs.stripe.com/billing/subscriptions/coupons.md). La requête suivante fournit une liste des codes promotionnels relatifs à un bon de réduction spécifique et affiche le nombre de fois où chaque code a été utilisé : ```sql select promotion_codes.id as promotion_code_id, promotion_codes.code as promotion_code, promotion_codes.times_redeemed from promotion_codes limit 3 ``` | promotion_code_id | code | times_redeemed | | --------------------- | ------ | -------------- | | promo_orWziM4j7CiRL8J | 10OFF | 1 | | promo_orWziM4j7CiRL8J | 25OFF | 2 | | promo_orWziM4j7CiRL8J | 10FREE | 3 | ## Événements liés à la modification des éléments d’abonnement Le tableau `subscription_item_change_events` suit les modifications apportées aux postes d’abonnement qui ont une incidence sur le [revenu récurrent mensuel (MRR)](https://support.stripe.com/questions/calculating-monthly-recurring-revenue-\(mrr\)-in-billing) et les quantités d’abonnement. Utilisez ce tableau pour calculer le MRR des clients individuels, des produits ou des offres pour créer des définitions d’indicateurs personnalisés pour vos modèles économiques et pour suivre les modifications des quantités d’abonnement. > Ce tableau fournit des données plus récentes que la source des mesures MRR dans l’aperçu de Billing du Dashboard Stripe. Ainsi, les données pour le MRR du dernier jour et du jour en cours peuvent être plus précises et différer de ce que vous voyez dans le Dashboard. ### Événements de modification des éléments d’abonnement v2 (Public preview) Le tableau `subscription_item_change_events_v2_beta` remplace le [tableau ](https://docs.stripe.com/stripe-data/query-billing-data.md#subscription-item-change-events) `subscription_item_change_events` par une actualisation des données améliorée. Les données de ce tableau conservent une actualisation de 3 heures dans Sigma et de 7 heures dans Stripe Data Pipeline. Elle partage le même schéma avec l’ensemble de données existant. Vous pouvez effectuer cette requête en ajoutant le suffixe `v2_beta` au tableau dans les mêmes exemples de requêtes de modèle. > #### Les données peuvent être modifiées > > Ce tableau fournit des données plus récentes et plus cohérentes avec le Dashboard de Stripe que l’ensemble de données existant. Cela signifie que les futures livraisons de cet ensemble de données pourront contenir des données mises à jour pour le MRR du jour précédent et du jour actuel. Attendez que toutes les données soient finalisées (48 heures maximum) avant d’utiliser ces données de manière incrémentielle. ### local_event_timestamp et event_timestamp Ce tableau comprend deux colonnes d’horodatage : - `event_timestamp` : horodatage UTC. - `local_event_timestamp` : horodatage dans votre fuseau horaire local. Il s’agit généralement du fuseau horaire de la personne qui a créé votre compte Stripe. ### devise Vous y trouverez la devise de règlement du poste d’abonnement sous la forme d’un [code ISO](https://docs.stripe.com/currencies.md) à trois lettres minuscules. La devise doit être [prise en charge](https://docs.stripe.com/currencies.md) par Stripe. ### mrr_change La colonne `mrr_change` indique l’impact positif ou négatif d’un événement sur votre MRR dans la sous-unité de la devise de règlement de l’élément d’abonnement (par exemple, les cents pour le dollar américain). ### quantity_change La colonne `quantity_change` indique l’augmentation ou la diminution de la quantité associée à un poste d’abonnement auquel un client est abonné. ### event_type | Type d’événement | Définitions | | ------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | ACTIVE_START | L’élément d’abonnement a commencé à contribuer au MRR. | | ACTIVE_END | L’élément d’abonnement a cessé de contribuer au MRR. | | ACTIVE_UPGRADE | La contribution du poste d’abonnement au MRR a augmenté. Cela peut se produire lorsque le tarif d’un poste d’abonnement augmente ou lorsque la quantité associée à ce poste d’abonnement augmente. | | ACTIVE_DOWNGRADE | La contribution du poste d’abonnement au MRR a diminué. Cela peut se produire lorsque le tarif d’un poste d’abonnement diminue ou lorsque la quantité associée à ce poste d’abonnement diminue. | | ACTIVE_QUANTITY_INCREASE | La quantité associée au poste d’abonnement a augmenté, mais le MRR n’a pas été affecté. Cette situation peut se produire si vous utilisez une tarification progressive et que la quantité doit être supérieure à un certain seuil avant que le tarif ne soit modifié. | | ACTIVE_QUANTITY_DECREASE | La quantité associée au poste d’abonnement a diminué, mais le MRR n’a pas été affecté. Cette situation peut se produire si vous utilisez une tarification progressive et que la quantité doit être inférieure à un certain seuil avant que le tarif ne soit modifié. | > Certaines actions de l’utilisateur peuvent créer plusieurs événements. Ainsi, pour le même `subscription_id`, vous pourriez voir un événement avec un `event_type` `ACTIVE_END` sur un élément, puis directement après un événement avec un `event_type` `ACTIVE_START` sur un autre élément. ### Autres colonnes Les autres colonnes (`product_id`, `price_id`, `customer_id`, `subscription_id` et `subscription_item_id`) contiennent les identifiants liés à l’événement de modification de l’élément d’abonnement. ### Exemples de requêtes Pour des exemples supplémentaires et actualisés, veuillez vous référer à la [section Abonnements de la bibliothèque de modèles de requêtes dans la barre latérale de Sigma](https://dashboard.stripe.com/sigma/queries). Pour calculer le revenu récurrent mensuel (MRR) et le nombre d’abonnés actifs à partir de ce tableau, vous devrez utiliser des fonctions de fenêtre. De plus, si vos clients utilisent des devises différentes, vous devrez effectuer des calculs de change. Le calcul vise à suivre le MRR mensuel et l’évolution du nombre d’abonnés actifs, en faisant la distinction entre les nouveaux abonnements, les réactivations, les expansions, les contractions et les désabonnements. Les résultats finaux sont présentés en unités monétaires mineures, par exemple en cents pour le dollar américain. ```sql WITH ts_grouped_sub_item_events AS ( SELECT local_event_timestamp, customer_id, currency, sum(mrr_change) AS mrr_change FROM subscription_item_change_events_v2_beta GROUP BY 1, 2, 3 ), ts_grouped_sub_item_events_with_mrr AS ( SELECT *, date_trunc( 'day', date(local_event_timestamp) ) AS local_event_date, -- Stripe defines an "active subscriber" as a customer with non-zero MRR. -- Therefore instead of summing up event_type to get subscription count (and its diff), -- We count the amount of revenue on each customer instead and later check its movement from / to zero sum(mrr_change) over ( PARTITION by customer_id ORDER BY local_event_timestamp ASC ) AS mrr, -- We count the # of times MRR has actually changed, and use nullif to ignore events that do not impact MRR -- Otherwise we may confuse between new vs. reactivation count(nullif(mrr_change, 0)) over ( PARTITION by customer_id ORDER BY local_event_timestamp ASC ) AS mrr_change_count FROM ts_grouped_sub_item_events ), ts_grouped_sub_item_events_with_previous_mrr AS ( SELECT *, coalesce( last_value(mrr) IGNORE nulls OVER ( PARTITION by customer_id ORDER BY local_event_timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ), 0 ) AS previous_mrr FROM ts_grouped_sub_item_events_with_mrr ), customer_events AS ( SELECT *, CASE WHEN mrr = 0 AND previous_mrr > 0 THEN 'ACTIVE_END' WHEN mrr > 0 AND previous_mrr = 0 AND mrr_change_count = 1 THEN 'ACTIVE_START' WHEN mrr > 0 AND previous_mrr = 0 AND mrr_change_count > 1 THEN 'REACTIVATE' WHEN mrr > previous_mrr THEN 'ACTIVE_UPGRADE' WHEN mrr < previous_mrr THEN 'ACTIVE_DOWNGRADE' ELSE NULL END AS cus_event_type FROM ts_grouped_sub_item_events_with_previous_mrr ), date_grouped_customer_events AS ( SELECT local_event_date, currency, sum(mrr_change) AS mrr_change, sum( CASE cus_event_type WHEN 'ACTIVE_START' THEN mrr_change ELSE 0 END ) AS new_mrr, sum( CASE cus_event_type WHEN 'REACTIVATE' THEN mrr_change ELSE 0 END ) AS reactivation_mrr, sum( CASE cus_event_type WHEN 'ACTIVE_UPGRADE' THEN mrr_change ELSE 0 END ) AS expansion_mrr, sum( CASE cus_event_type WHEN 'ACTIVE_DOWNGRADE' THEN mrr_change ELSE 0 END ) AS contraction_mrr, sum( CASE cus_event_type WHEN 'ACTIVE_END' THEN mrr_change ELSE 0 END ) AS churn_mrr, sum( CASE WHEN mrr = 0 AND previous_mrr > 0 THEN -1 WHEN mrr > 0 AND previous_mrr = 0 THEN 1 ELSE 0 END ) AS active_subscribers_change, sum( CASE cus_event_type WHEN 'ACTIVE_END' THEN 1 ELSE 0 END ) AS churned_subscribers, sum( CASE cus_event_type WHEN 'ACTIVE_START' THEN 1 ELSE 0 END ) AS new_subscribers, sum( CASE cus_event_type WHEN 'REACTIVATE' THEN 1 ELSE 0 END ) AS reactivated_subscribers FROM customer_events GROUP BY 1, 2 ), -- Prepare the multi dimensional table with all days + currency combinations and conversion rate metadata -- note that exchange_rates_from_usd contains one row for every date from 2010-01-07 until today -- which is why we don't need to generate a separate date series for the full table dates_with_rate_per_usd AS ( SELECT -- We use previous day's closing rates in precomputed metrics date - INTERVAL '1' DAY AS fx_date, cast( json_parse(buy_currency_exchange_rates) AS map(varchar, double) ) AS rate_per_usd FROM exchange_rates_from_usd ), currencies AS ( SELECT DISTINCT(currency) FROM subscription_item_change_events_v2_beta ), first_default_currency AS ( SELECT default_currency FROM accounts WHERE default_currency IS NOT NULL LIMIT 1 ), dates_x_currencies_with_conversion_rate AS ( SELECT fx_date as local_date, currency, default_currency, 1 / rate_per_usd [currency] * rate_per_usd [coalesce(default_currency, 'usd')] AS conversion_rate FROM dates_with_rate_per_usd CROSS JOIN currencies CROSS JOIN first_default_currency ORDER BY 1, 2 ), daily_metrics_by_currency AS ( SELECT dpc.local_date, dpc.currency, dpc.conversion_rate, coalesce( sum(mrr_change) over ( PARTITION by dpc.currency ORDER BY dpc.local_date ASC ), 0 ) AS mrr, coalesce( round( sum(mrr_change) over ( PARTITION by dpc.currency ORDER BY dpc.local_date ASC ) * dpc.conversion_rate ), 0 ) AS converted_mrr, coalesce(round(new_mrr * conversion_rate), 0) AS converted_new_mrr, coalesce(round(reactivation_mrr * conversion_rate), 0) AS converted_reactivation_mrr, coalesce(round(expansion_mrr * conversion_rate), 0) AS converted_expansion_mrr, coalesce(round(contraction_mrr * conversion_rate), 0) AS converted_contraction_mrr, coalesce(round(churn_mrr * conversion_rate), 0) AS converted_churn_mrr, coalesce(dgce.mrr_change, 0) AS mrr_change, coalesce(dgce.new_mrr, 0) AS new_mrr, coalesce(dgce.reactivation_mrr, 0) AS reactivation_mrr, coalesce(dgce.expansion_mrr, 0) AS expansion_mrr, coalesce(dgce.contraction_mrr, 0) AS contraction_mrr, coalesce(dgce.churn_mrr, 0) AS churn_mrr, coalesce( sum(active_subscribers_change) over ( PARTITION by dpc.currency ORDER BY dpc.local_date ASC ), 0 ) AS active_subscribers, coalesce(dgce.active_subscribers_change, 0) AS active_subscribers_change, coalesce(dgce.churned_subscribers, 0) AS churned_subscribers, coalesce(dgce.new_subscribers, 0) AS new_subscribers, coalesce(dgce.reactivated_subscribers, 0) AS reactivated_subscribers FROM dates_x_currencies_with_conversion_rate dpc LEFT JOIN date_grouped_customer_events dgce ON dpc.local_date = dgce.local_event_date AND dpc.currency = dgce.currency ), daily_metrics AS ( SELECT local_date, sum(converted_mrr) AS mrr, sum(converted_new_mrr) AS new_mrr, sum(converted_reactivation_mrr) AS reactivation_mrr, sum(converted_expansion_mrr) AS expansion_mrr, sum(converted_contraction_mrr) AS contraction_mrr, sum(converted_churn_mrr) AS churn_mrr, -- Customer can only have active subscription in a single currency at a time, as a result this doesn't result in over-counting subscriber changes -- This also matches the precomputed metrics logic in billing dashboard / CSV download sum(active_subscribers) AS active_subscribers, sum(churned_subscribers) AS churned_subscribers, sum(new_subscribers) AS new_subscribers, sum(reactivated_subscribers) AS reactivated_subscribers FROM daily_metrics_by_currency GROUP BY 1 ), daily_metrics_with_derived AS ( SELECT *, mrr - lag(mrr) over ( ORDER BY local_date ) - new_mrr - reactivation_mrr - expansion_mrr - contraction_mrr - churn_mrr AS fx_adjustment_mrr, lag(mrr) over ( ORDER BY local_date ) AS previous_mrr FROM daily_metrics ), -- Turn daily into monthly metrics monthly_metrics_with_derived AS ( SELECT date_trunc('month', local_date) AS local_month_start, max_by(mrr, local_date) AS ending_mrr, sum(new_mrr) AS new_mrr, sum(reactivation_mrr) AS reactivation_mrr, sum(expansion_mrr) AS expansion_mrr, sum(contraction_mrr) AS contraction_mrr, sum(churn_mrr) AS churn_mrr, sum(fx_adjustment_mrr) AS fx_adjustment_mrr, max_by(active_subscribers, local_date) AS ending_subscribers, sum(churned_subscribers) AS churned_subscribers, sum(new_subscribers) AS new_subscribers, sum(reactivated_subscribers) AS reactivated_subscribers FROM daily_metrics_with_derived GROUP BY 1 ) SELECT local_month_start, ending_mrr - fx_adjustment_mrr - churn_mrr - contraction_mrr - expansion_mrr - reactivation_mrr - new_mrr AS beginning_mrr, new_mrr, reactivation_mrr, expansion_mrr, contraction_mrr, churn_mrr, fx_adjustment_mrr, ending_mrr, -- Churned subscribers is a positive number in CSV reports instead of negative for churn / contraction mrr ending_subscribers - (-1 * churned_subscribers) - reactivated_subscribers - new_subscribers AS beginning_subscribers, new_subscribers, reactivated_subscribers, churned_subscribers, ending_subscribers FROM monthly_metrics_with_derived ORDER BY 1 DESC ``` | local_month_start | beginning_mrr | new_mrr | reactivation_mrr | expansion_mrr | contraction_mrr | churn_mrr | fx_adjustment_mrr | ending_mrr | beginning_subscribers | new_subscribers | reactivated_subscribers | churned_subscribers | ending_subscribers | | ----------------- | ------------- | ------- | ---------------- | ------------- | --------------- | --------- | ----------------- | ---------- | --------------------- | --------------- | ----------------------- | ------------------- | ------------------ | | 01/05/2024 | 100072149 | 104000 | 0 | 40000 | 0 | 0 | 0 | 100216149 | 9 | 3 | 0 | 0 | 12 | | 01/04/2024 | 100065149 | 7180 | 0 | 0 | 0 | -180 | 0 | 100072149 | 7 | 3 | 0 | 0 | 12 | | 2024-03-01 | 100066099 | 124 | 0 | 0 | 0 | -1074 | 0 | 100065149 | 7 | 2 | 0 | 2 | 7 | | 2024-02-01 | 100066099 | 1000 | 0 | 0 | 0 | -1000 | 0 | 100066099 | 7 | 1 | 0 | 1 | 7 | | 01/01/2024 | 100038102 | 29216 | 0 | 1998 | -175 | -3042 | 0 | 100066099 | 5 | 4 | 0 | 2 | 7 | | 2023-12-01 | 100038102 | 0 | 0 | 0 | 0 | 0 | 0 | 100038102 | 5 | 0 | 0 | 0 | 5 | | 2023-11-01 | 100037102 | 1000 | 0 | 0 | 0 | 0 | 0 | 100038102 | 4 | 1 | 0 | 0 | 5 | | 2023-10-01 | 100037102 | 0 | 0 | 0 | 0 | 0 | 0 | 100037102 | 4 | 0 | 0 | 0 | 4 | | 2023-09-01 | 100037102 | 0 | 0 | 0 | 0 | 0 | 0 | 100037102 | 4 | 0 | 0 | 0 | 4 | | 2023-08-01 | 100033902 | 0 | 0 | 5 000 | 0 | -1800 | 0 | 100037102 | 5 | 0 | 0 | 1 | 4 | | 2023-07-01 | 100037065 | 0 | 0 | 0 | 0 | -3159 | -4 | 100033902 | 6 | 0 | 0 | 1 | 5 | | 01/06/2023 | 100036402 | 35 | 3369 | 0 | 0 | -2742 | 1 | 100037065 | 6 | 1 | 3 | 4 | 6 | | 2023-05-01 | 100034898 | 2748 | 0 | 30437 | -83 | -31598 | 0 | 100036402 | 7 | 3 | 0 | 4 | 6 | | 2023-04-01 | 100034065 | 933 | 0 | 0 | 0 | -100 | 0 | 100034898 | 6 | 2 | 0 | 1 | 7 | | 2023-03-01 | 100002715 | 31350 | 0 | 0 | 0 | 0 | 0 | 100034065 | 4 | 2 | 0 | 0 | 6 | | 2023-02-01 | 100006048 | 6086 | 0 | 6088 | 0 | -15507 | 0 | 100002715 | 5 | 2 | 0 | 3 | 4 | | 2023-01-01 | 100006048 | 3043 | 0 | 0 | 0 | -3043 | 0 | 100006048 | 5 | 1 | 0 | 1 | 5 | | 2022-12-01 | 100152134 | 25910 | 0 | 1363600 | -30000 | -1505574 | -22 | 100006048 | 9 | 6 | 0 | 10 | 5 | | 2022-11-01 | 100178232 | 48688 | 3333 | 621878 | -10600 | -689397 | 0 | 100152134 | 7 | 16 | 1 | 15 | 9 | | 2022-10-01 | 100036193 | 136333 | 120000 | 20600 | -10000 | -124894 | 0 | 100178232 | 7 | 4 | 2 | 6 | 7 |