Billing-Daten abfragen
Die Abrechnung besteht aus verschiedenen Komponenten, die zusammenarbeiten, um einmalige Rechnungen und regelmäßige Abrechnungen bereitzustellen, wobei verschiedene Aspekte der Abrechnungsdaten in einer Reihe von Tabellen verfügbar sind. Alle abrechnungsspezifischen Tabellen befinden sich im Abschnitt Abrechnung des Schemas, wobei die primären Tabellen subscriptions
und invoices
sind.
Um die Abrechnungsdaten genauer zu untersuchen, können Sie die zusätzlichen Tabellen verwenden, die die Komponenten von Abonnements und Rechnungen darstellen, z. B. prices
, products
oder coupons
. Darüber hinaus ist die Tabelle customers
ein wesentlicher Bestandteil der Abrechnung und enthält Daten, über die Sie möglicherweise Berichte erstellen müssen.
Abonnements
Jede Zeile in der Tabelle subscriptions
liefert Daten zu einem einzelnen Subscription-Objekt – das sind die gleichen Informationen, die die API abruft oder im Stripe-Dashboard verfügbar sind. Sie können über jedes Abonnement Berichte erstellen, das Sie in Ihrem Konto erstellen.
Diese Tabelle ist der empfohlene Ausgangspunkt für die Erstellung von Berichten über Ihre aktuellen Abonnent/innen. Sie können diese Tabelle mit anderen zugehörigen Tabellen zusammenführen, sodass Sie Ihre Daten detaillierter untersuchen können.
Das folgende Beispiel ruft eine Liste von Abonnements ab, die als unbezahlt markiert wurden, sowie alle verfügbaren Kontaktinformationen für die Kundin/den Kunden.
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_qH5mtIGIhgrBu7h | cus_UA5hhGnibJmg7WR | jenny.rosen@example.com |
sub_kMKnkiHULq4Bdkj | cus_1U5QXv4aYPTSwYE | noah.wilson@example.com |
sub_0y02hgwHg194VQT | cus_JVwVF82unLRe7qq | joshua.miller@example.com |
sub_iA5t6yusP24bcwE | cus_Fr7K0HwkeM2FG9N | madison.jackson@example.com |
sub_uvUZtXBfwCvCmOj | cus_3conmfNkykT39Fk | elijah.smith@example.com |
Kund/innen
Daten über Customer-Objekte sind in der Tabelle customers
enthalten (diese ist nicht Teil der Gruppe Abrechnung Tabellen). Er wird üblicherweise in abrechnungsbasierten Berichten verwendet und kann mit einer Reihe von Tabellen zusammengeführt werden. Es ist auch nützlich, wenn Sie Zahlungen mit gespeicherten Zahlungsinformationen erstellen.
Im folgenden Beispiel wird eine Liste von Kund/innen mit abonnements abgerufen, die sich derzeit im Testzeitraum befinden. Es ruft sowohl die ID als auch die E-Mail-Adresse für jede/n Kund/in ab.
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_b8WZhtrpIV2xoZ1 | jenny.rosen@example.com | ruby-pro-522 |
cus_U3Bwx4aAOkQp5eg | noah.wilson@example.com | ruby-pro-522 |
cus_X6GsLlBEOwIYBAI | richard.jones@example.com | gold-basic-221 |
cus_fNbQJIRRDRS3EXJ | madison.jackson@example.com | gold-basic-221 |
cus_VUdvxpMsQVl5yCr | elijah.smith@example.com | silver-pro-498 |
Produkte und Preise
Produkte sind die Artikel, die Ihre Kund/innen mit einem Abonnement erwerben können. Preise sind an Produkte gebunden und umfassen Kosten, Abrechnungsintervall und Währung. Wenn Sie Daten aus der Tabelle subscriptions
anzeigen, können Sie diese mit subscription_items
zusammenführen. Darüber hinaus können Sie sie mit products.id
zusammenführen, indem Sie die price_product_id
aus dem Artikel verwenden.
Das folgende Beispiel gibt eine Liste aktiver Abonnements zusammen mit dem Produktnamen und seiner Zahlungsbeschreibung in der Abrechnung zurück:
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 | Name | statement_descriptor |
---|---|---|
sub_acuUNT1caohvChM | ruby-pro-522 | Ruby Pro |
sub_MydXOIg4lfokwJy | gold-basic-221 | Gold Basic |
sub_Sjzav0gwKK5Z0H7 | silver-pro-498 | Silver Pro |
sub_yRIIHBMEYDd7nhi | diamond-mid-244 | Diamond Mid |
sub_7r3DkcioYcnBghj | ruby-standard-196 | Ruby Standard |
Preisstufen
Während Sie Preise mit Stufen in Ihren Abonnements verwenden, kann die Tabelle price_tiers
spezifische Daten zu jeder Stufe bereitstellen. Wenn Sie beispielsweise Informationen zur Anfangsstufe Ihrer Abonnements erhalten möchten, einschließlich der Höchstmenge für die erste Stufe und des verwendeten Betrags für Einheiten, beziehen Sie sich auf die folgende Abfrage:
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 | Währung |
---|---|---|---|---|
sub_RdYyzLH6lIUnX6O | price_DRzKD8hjzps2ELL | 30 | 2,00 | USD |
sub_QCRXSAHPAC4Tm8o | price_G8WFeayB0Ss48SZ | 60 | 1,00 | USD |
sub_ycQG5ZIUY3QDF2x | price_yS6WdDfbCAl24r3 | 90 | 0.50 | USD |
Rechnungen
Die Tabelle invoices
enthält Daten über einzelne Rechnung-Objekte. Für jedes Abonnement wird in regelmäßigen Abständen eine Rechnung erstellt, die den vom Kunden/von der Kundin geschuldeten Betrag darstellt. Dies beinhaltet automatisch den für das Abonnement erforderlichen Betrag und alle zusätzlichen Rechnungsposten, die möglicherweise erstellt wurden (aufgelistet als Einzelposten).
Rechnungen bestehen aus einzelnen (Rechnungs-)Posten. Diese Posten stellen alle Abonnements dar, die Kund/innen in Rechnung gestellt werden, sowie Rechnungspositionen, die erstellt und auf die Rechnung angewendet wurden. Um eine Rechnung aufzuschlüsseln und jeden ihrer Posten zu analysieren, verwenden Sie die Tabelle invoice_line_items
.
Die Spalte source_id
dieser Tabelle enthält die ID des Abonnements (beispielsweise sub_1H8her1NB3SYUli
) oder der Rechnungsposition (beispielsweise ii_FdM4S0v5CGaolzr
), zu dem bzw. der der Posten gehört. Die Spalte source_type
gibt an, ob der Posten ein Abonnement oder eine Rechnungsposition darstellt.
Im Gegensatz zu anderen Fremdschlüsseln wird die Spalte subscription
der Tabelle invoice_line_items
nicht immer ausgefüllt. Wenn die entsprechende Rechnungsposition für ein Abonnement steht, ist diese Spalte leer – ihre ID steht bereits in der Spalte source_id
.
Rechnungsposten
Daten zu Rechnungsposten sind in der Tabelle invoice_items
enthalten. Rechnungsposten werden üblicherweise verwendet, um einen zusätzlichen Betrag anzugeben (oder einen Betrag abzuziehen), der auf der nächsten Rechnung zu Beginn des nächsten Abrechnungszyklus angewendet wird. Sie würden beispielsweise einen Rechnungsposten erstellen, wenn Sie Ihrem Kunden/Ihrer Kundin in Rechnung stellen müssen, dass er sein monatliches Guthaben überschreitet, oder wenn Sie auf der nächsten Rechnung eine Gutschrift für nicht in Anspruch genommene Dienstleistung ausstellen müssen.
Im folgenden Beispiel werden alle Rechnungen und zugehörigen Zahlungs-IDs für ein bestimmtes Abonnement abgerufen.
select id, charge_id, amount_due from invoices where subscription_id = 'sub_ALJXL9gBYtv6GJ'
ID | Name | |
---|---|---|
in_Qfj8tG4gwdG2DAr | ch_aeDjGRgGgviBT8Q | 1999 |
in_ybAJAkrIYmvNl8r | ch_PZt2ZfPBoH5xgh5 | 1999 |
in_FPHKSvIy2QApYiP | 1999 | ch_9FACXl3EpEU4NFQ |
in_OGQtINSm4GLRDzT | 1999 | ch_rGFoHZ9LMkdDggl |
in_U2fxOsMNHB1YyTw | 1999 | ch_WyDHYzxSbgWQBHB |
Rechnungssummen und Rabatte
Die Rechnungszwischensumme ist die Summe aller Abonnements, Rechnungspositionen und Verrechnungen auf der Rechnung, bevor eventuelle Rabatte angewendet werden. Die Rechnungsgesamtsumme ist die Summe nach Anwendung von Rabatten und Steuern:
invoice.total
= invoice.subtotal
- discount
+ invoice.tax
Es gibt keine Spalte, um den Rabattbetrag auf einer Rechnung darzustellen. Stattdessen können Sie diesen berechnen, indem Sie die Rabattbeträge der Einzelposten zusammenfassen. Die folgende Abfrage gibt eine Liste der Rechnungen, das Start- und Enddatum und den gesamten Rabattbetrag für die Rechnung zurück.
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 | Währung |
---|---|---|---|---|
in_EvCeYdnjiFpGZff | 01.05.2024 | 01.06.2024 | 24,66 | USD |
in_gL7I5NtWyWhr460 | 01.06.2024 | 01.07.2024 | 24,34 | USD |
in_2pc4ZzM0VFYWMKY | 01.04.2024 | 01.05.2024 | 45,96 | USD |
Mit Rechnungsdaten und -zeiträumen arbeiten
Abonnement werden im Voraus abgerechnet. Dies bedeutet, dass der Kunde/die Kundin die Zahlung zu Beginn eines Abrechnungszyklus leistet. Dies wird im Wert period
eines Posten dargestellt. Die Abrechnung für einen Kunden/eine Kundin mit einem monatlichen Abonnement erfolgt beispielsweise zu Beginn eines jeden Monats. Wenn sich der Kunde/die Kundin für cancel_at_period_end entscheidet, bleibt sein/ihr Abonnement bis zum Ende des Monats aktiv. Danach endet das Abonnement.
Die Werte period_start
und period_end
einer Rechnung geben an, wann Rechnungsposten erstellt wurden – sie geben nicht immer genau den Zeitraum an, für den Kundinnen/Kunden eine Rechnung gestellt wird. Wenn ein Kunde/eine Kundin beispielsweise am 1. eines jeden Monats eine Rechnung erhält und am 15. sein/ihr monatliches Kontingent überschreitet, können Sie einen Rechnungsposten für alle zusätzlichen Kosten erstellen, die dem Kunden/der Kundin in Rechnung gestellt werden. Dieser Rechnungsposten ist dann in der nächsten Rechnung enthalten, die am 1. des nächsten Monats erstellt wird. Bei der nächsten Rechnung ist das Datum für period_start
der 15. des Vormonats: das Datum, an dem der zusätzliche Rechnungsposten ursprünglich erstellt wurde.
Gutscheine
Ein Coupon-Objekt stellt einen Rabatt in Form eines Betrags oder Prozentwerts dar, den Sie auf Abonnements oder für Kundinnen/Kunden anwenden können.
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 |
Rabatte
Bei einem Rabatt handelt es sich um die Anwendung eines Gutscheins, der durch ein Discount-Objekt dargestellt wird. Mit der folgenden Abfrage wird eine Liste der Abonnements und der damit verbundenen Rabatte und Gutscheine zurückgegeben:
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_dsMmBXKIr94JBzb | di_mmWNe4qhyiMWQeB | 10OFF |
sub_tOuGBJJ6ZYI3CAV | di_rkNEccPJRYJs1sb | 25OFF |
sub_4iCYkFpHGDUiZJz | di_JxV6LBmqtC2Wvru | 10FREE |
Promo-Codes
Ein Promo-Code stellt einen von einem Kunden/einer Kundin einlösbaren Code für einen Gutschein dar. Die folgende Abfrage enthält eine Liste der Promo-Codes für einen bestimmten Gutschein und zeigt an, wie oft jeder Code eingelöst wurde:
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_rvf6tPm1YsLFaQ2 | 10OFF | 1 |
promo_Xn1pZYTVEbdtJQ7 | 25OFF | 2 |
promo_zgn7jjXxK1wJjVI | 10FREE | 3 |
Ereignisse bei der Änderung von Abonnement-Artikeln
Die Tabelle subscription_item_change_events
verfolgt Änderungen an Abonnement-Objekten, die sich auf den monatlich wiederkehrenden Umsatz (MRR) auswirken. Verwenden Sie diese Tabelle, um den MRR für einzelne Kundinnen/Kunden, Produkte und Pläne zu berechnen und benutzerdefinierte Metrikdefinitionen für Ihre Geschäftsmodelle zu erstellen.
Vorsicht
Diese Tabelle enthält aktuellere Daten als die Quelle, aus der die MRR-Metriken in der Abrechnungsübersicht im Stripe Dashboard stammen. Dies bedeutet, dass die Daten für den MRR des letzten und des aktuellen Tages hier genauer sein könnten und von dem abweichen könnten, was Sie im Dashboard sehen.
local_event_timestamp and event_timestamp
Diese Tabelle enthält zwei Zeitstempelspalten:
event_timestamp
: Dies ist der UTC-Zeitstempel.local_event_timestamp
: Dieser Zeitstempel befindet sich in Ihrer lokalen Zeitzone, in der Regel der Zeitzone der Person, die Ihr Stripe-Konto erstellt hat.
Währung
Hier finden Sie die Abrechnungswährung des Abonnementartikels als dreibuchstabigen ISO-Währungscode in Kleinbuchstaben. Die Währung muss eine sein, die Stripe unterstützt.
mrr_change
Die Spalte mrr_change
zeigt die positiven oder negativen Auswirkungen eines Ereignisses auf Ihren MRR in der Nebeneinheit der Abrechnungswährung des Abonnements (zum Beispiel Cent für USD).
event_type
Ereignistyp | Definition |
---|---|
ACTIVE_START | Der Abonnementposten hat begonnen, zum MRR beizutragen. |
ACTIVE_UPGRADE | Der MRR-Beitrag des Abonnementpostens ist gestiegen (zum Beispiel die Menge). |
ACTIVE_END | Der Abonnementposten hat aufgehört, zum MRR beizutragen. |
ACTIVE_DOWNGRADE | Der MRR-Beitrag des Abonnementpostens ist gestiegen (zum Beispiel die Menge). |
Notiz
Einige Nutzeraktionen können mehrere Ereignisse erstellen, sodass Sie ein Ereignis mit einem event_type
von ACTIVE_END
für einen Posten und dann sofort ein Ereignis mit einem event_type
von ACTIVE_START
für einen anderen Posten für dieselbe subscription_id
sehen können.
Andere Spalten
Andere Spalten (product_id
, price_id
, customer_id
, subscription_id
und subscription_item_id
) enthalten IDs im Zusammenhang mit dem Ereignis für die Änderung von Abonnementposten.
Beispielabfrage
Die Abfrage dieser Tabelle zur Berechnung des MRR umfasst Fensterfunktionen und – für all diejenigen mit Kundinnen/Kunden in verschiedenen Währungen –Fremdwährungsumrechnungen. Nachfolgend finden Sie ein Beispiel für die Berechnung des täglichen MRR für die letzten zwei Monate in USD-Cent:
-- 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 |