Interroger des données Billing
Stripe Billing comprend différents composants qui, associés, fournissent des factures 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 individuel. Ces mêmes informations sont récupérées par l’API ou accessibles dans le Dashboard Stripe. 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.
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.
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 | |
---|---|---|
sub_uiDLwdXtLMcZGlt | cus_WjGpeFtu6LHkFrK | emilie.joubert@exemple.fr |
sub_KO0gAHDSK2JqCuh | cus_u7tAvolkeJTb6ZQ | pierre.durand@exemple.fr |
sub_jIcpX8hmeUC3RrK | cus_OCSK43N86GpGDOc | philippe.legrain@exemple.fr |
sub_uQgEcf74D2OBxBH | cus_L5whHvYeTdchxp4 | sylvie.dumont@exemple.fr |
sub_Xwbt5ob78oJJGhu | cus_HqRe8b7jEfxhAZL | elias.boudet@exemple.fr |
Clients
Les données relatives aux objets Customer 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 avec des informations de paiement enregistrées.
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.
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 | price_id | |
---|---|---|
cus_YIHhQifzpq67bjC | emilie.joubert@exemple.fr | ruby-pro-522 |
cus_Jb4FAFqC4X5lczw | pierre.durand@exemple.fr | ruby-pro-522 |
cus_A9Uhf45tQLgPrvr | richard.dupont@exemple.fr | gold-basic-221 |
cus_no8DmMdShAeFGkl | sylvie.dumont@exemple.fr | gold-basic-221 |
cus_grkjJzUcTnguKkJ | 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 :
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_M6KrfRkqkcxxXHD | ruby-pro-522 | Ruby Pro |
sub_VzxhKQg6Qp0O6yU | gold-basic-221 | Gold Basic |
sub_LQH8aAe4aVbzkvV | silver-pro-498 | Silver Pro |
sub_7QdFH4MhFN8TdA6 | diamond-mid-244 | Diamond Mid |
sub_G6iNlGZASiiRdom | ruby-standard-196 | Ruby Standard |
Niveaux de tarifs
Lorsque vous utilisez des niveaux 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 :
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 | currency |
---|---|---|---|---|
sub_fhLQPSUNH9sBL1J | price_xVW1eHdDhlgtoqz | 30 | 2 | usd |
sub_QVOKKfRlmg3JYPI | price_OltY8AxtBNIAVY5 | 60 | 1 | usd |
sub_7BCu4wysJAwh5io | price_gHMmZtKQDLBR6Qi | 90 | 0,50 | usd |
Factures
La table invoices
contient des données sur les objets Invoice. 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 supplémentaires qui ont été créés (et répertoriés comme tels).
Les factures sont constituées de postes de facture 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_zLhgvCUfFXFdadA
) ou du poste de facture (par exemple, ii_svECgGF0qUVMQlM
) 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 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é.
L’exemple suivant récupère toutes les factures et tous les ID de paiement associés pour un abonnement spécifique.
select id, charge_id, amount_due from invoices where subscription_id = 'sub_ALJXL9gBYtv6GJ'
id | nom | |
---|---|---|
in_BdqOP77yqbSHih0 | ch_8r35FcBTmuzxtDi | 1999 |
in_dIhCo3FbPvAFAId | ch_glb7IwOdd5iNmTz | 1999 |
in_PuLA23LrGCPyEX6 | 1999 | ch_tqwIaUTAKhwv2wd |
in_TWOR7ykleHUqp5p | 1999 | ch_PUEocEXdNV4Vxbo |
in_3Uu0i4kuoFLzrbr | 1999 | ch_Ci3dcYEatZS5YFn |
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.
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 | currency |
---|---|---|---|---|
in_VyoRUWvEQZ7YPuS | 01/05/2024 | 01/06/2024 | 24,66 | usd |
in_cdDRuFI0aBTxfo6 | 01/06/2024 | 01/07/2024 | 24,34 | usd |
in_DvCSSHXx2le9lrw | 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, 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.
Coupons
Un objet Coupon correspond à un montant ou à un pourcentage de réduction pouvant s’appliquer à des abonnements ou à des clients.
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 |
Remises
Une réduction correspond à l’application d’un bon de réduction représenté par un objet Discount. La requête suivante renvoie une liste des abonnements avec les réductions et les bons de réduction qui leur sont associés :
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_BPVx86UopHN0dUu | di_EQ8L6Eg6ZQZjw2G | 10OFF |
sub_j9f9Y1DMYXjRtsd | di_F4TIuqS4SSpWcD7 | 25OFF |
sub_gHyGEztKQw4b4Yj | di_1M7gLyHodERp7df | 10FREE |
Codes promotionnels
Un code promotionnel est un code utilisable par le client pour obtenir un bon de réduction. 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é :
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_BPXopTF6Yqi4LcC | 10OFF | 1 |
promo_MXZZQCzJc3lNLFx | 25OFF | 2 |
promo_frhWaZtzNpXbOha | 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 éléments d’abonnement qui ont une incidence sur le revenu récurrent mensuel (MRR). Utilisez ce tableau pour calculer le MRR des clients individuels, des produits, des plans et pour créer des définitions d’indicateurs personnalisés pour vos modèles économiques.
Mise en garde
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.
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.
currency
Vous y trouverez la devise de règlement du poste d’abonnement sous la forme d’un code ISO à trois lettres minuscules. La devise doit être prise en charge 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).
event_type
Type d’événement | Définition |
---|---|
ACTIVE_START | L’élément d’abonnement a commencé à contribuer au MRR. |
ACTIVE_UPGRADE | La contribution de l’élément d’abonnement au MRR a augmenté (par exemple, la quantité a augmenté). |
ACTIVE_END | L’élément d’abonnement a cessé de contribuer au MRR. |
ACTIVE_DOWNGRADE | La contribution de l’élément d’abonnement au MRR a diminué (par exemple, la quantité a diminué). |
Note
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.
Exemple de requête
Pour demander à ce tableau de calculer le MRR, il faut utiliser des fonctions de fenêtre et, pour ceux dont les clients utilisent des devises différentes, il convient également de réaliser des calculs des taux de change. Voici un exemple de calcul du MRR quotidien des deux derniers mois en cents de dollars américains :
-- the following six CTEs load the foreign currency exchange rates into a more efficient datastructure to use later. with date_ranges as ( SELECT date_col FROM UNNEST( sequence(date '2010-01-01', current_date, INTERVAL '1' DAY) ) t (date_col) ), currencies AS ( SELECT DISTINCT t.currency FROM exchange_rates_from_usd CROSS JOIN UNNEST ( CAST(json_parse(buy_currency_exchange_rates) AS MAP(VARCHAR, DOUBLE)) ) AS t (currency, rate) ), dates as ( select date_col as date, date_col + INTERVAL '1' DAY as fx_date from date_ranges ), dates_currencies as ( select date, fx_date, currency from dates cross join currencies ), fx_rates_json as ( select dates.date, last_value( exchange_rates_from_usd.buy_currency_exchange_rates ) ignore nulls over ( order by exchange_rates_from_usd.date asc ) as buy_currency_exchange_rates from dates full outer join exchange_rates_from_usd on dates.fx_date = exchange_rates_from_usd.date ), fx_rates as ( select transform_values( multimap_agg( date_format(date, '%Y-%m-%d'), row(currency, rate) ), ((k, v) -> map_from_entries(v)) ) as rates from fx_rates_json cross join unnest( map_entries( cast( json_parse(buy_currency_exchange_rates) as map(varchar, double) ) ) ) t(currency, rate) where date in ( select date from dates ) ), -- this sums up the per-currency MRR changes for each day per_day_sums as ( select date(local_event_timestamp) as event_date, currency, sum(mrr_change) as mrr_change from subscription_item_change_events t group by 1, 2 ), per_currency_sums as ( select date_format(c.date, '%Y-%m-%d') as date, c.currency, sum(mrr_change) over ( partition by c.currency order by c.date asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as mrr from per_day_sums right join dates_currencies c on c.currency = per_day_sums.currency and c.date = per_day_sums.event_date ) -- this does the foreign exchange calculation and filters to the time range you want, 2 months. select date, cast( sum( round( -- replace 'usd' below with the currency you want your MRR represented in mrr / fx_rates.rates[date][currency] * fx_rates.rates[date]['usd'] ) ) as bigint ) as mrr from per_currency_sums cross join fx_rates where -- here's where you filter to the range you want, in this case 2 months date >= date_format(current_date - interval '2' month, '%Y-%m-%d') group by 1 order by 1 desc
event_date | mrr |
---|---|
17/04/2024 | 14402570 |
16/04/2024 | 14410900 |
15/04/2024 | 14403403 |
14/04/2024 | 14372582 |
13/04/2024 | 14365918 |
12/04/2024 | 14327600 |
11/04/2024 | 14346759 |
10/04/2024 | 14363419 |
09/04/2024 | 14351757 |
08/04/2024 | 14333431 |
07/04/2024 | 14319270 |
06/04/2024 | 14316771 |
05/04/2024 | 14240135 |
04/04/2024 | 14250131 |
03/04/2024 | 14253463 |
02/04/2024 | 14247632 |
01/04/2024 | 14247632 |
31/03/2024 | 14214312 |
30/03/2024 | 14187656 |
29/03/2024 | 14146839 |
28/03/2024 | 14152670 |
27/03/2024 | 14152670 |
26/03/2024 | 14131012 |
25/03/2024 | 14124348 |
24/03/2024 | 14116851 |
23/03/2024 | 14115185 |
22/03/2024 | 14097692 |
21/03/2024 | 14104356 |
20/03/2024 | 14113519 |
19/03/2024 | 14097692 |
18/03/2024 | 14088529 |