Billing のデータをクエリする
Billing は、相互に連携する複数のコンポーネントで構成され、複数のテーブルのさまざまな側面の請求データを使用して、1 回限りのインボイスや定期請求に対応します。主要なテーブルである subscriptions
、invoices
をはじめ、Billing 固有のテーブルはすべて、スキーマの Billing セクションにあります。
請求データをさらに調べるには、prices
、products
、coupons
など、サブスクリプションやインボイスのコンポーネントを示すその他のテーブルも利用できます。また、customers
テーブルは Billing の基本的部分であり、レポート作成に必要なデータが収録されています。
サブスクリプション
subscriptions
テーブルの各行は、個別の Subscription (サブスクリプション) オブジェクトに関するデータを示します。API でも、Stripe ダッシュボード内でも、同じ情報を取得できます。アカウントで作成するすべてのサブスクリプションに関するレポートを作成できます。
現在のサブスクリプション登録者についてのレポートを作成する際には、このテーブルから開始することをお勧めします。このテーブルは、他の関連テーブルに結合できるため、データをさらに詳細に調べることができます。
以下の例では、未払いとしてマークされたサブスクリプションと、その顧客の利用可能な連絡先情報のリストを取得します。
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_P3xKw8uDbYlCCrD | cus_cPM8kGlokisHil3 | jenny.rosen@example.com |
sub_feoFjSYHX1JQZ4x | cus_7nfhktD7EzWB3JU | noah.wilson@example.com |
sub_OleXiyscwmxPz8k | cus_KfRURJls2gYH68H | joshua.miller@example.com |
sub_UM88NrBCVE4KqE7 | cus_YiKQwPkWXU8GlN3 | madison.jackson@example.com |
sub_y48S4dSqbUQmi7V | cus_ZEghMMipvkn7dPk | elijah.smith@example.com |
顧客
Customer (顧客) オブジェクトに関するデータは、customers
テーブルに収録されています (これは、Billing テーブルグループには含まれません)。一般に請求ベースのレポートの一部として使用され、多数のテーブルと結合できます。保存された決済情報を使用して支払いを作成する場合にも利用できます。
次の例では、現在トライアル期間中のサブスクリプションの顧客のリストを取得します。各顧客の ID とメールアドレスの両方が取得されます。
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_FDpDEW1tzvCpRxR | jenny.rosen@example.com | ruby-pro-522 |
cus_CtTMyq5IYIfZJ3x | noah.wilson@example.com | ruby-pro-522 |
cus_4mTrsjLqSm4BXxz | richard.jones@example.com | gold-basic-221 |
cus_ulYi3Yra71y5JgG | madison.jackson@example.com | gold-basic-221 |
cus_dsuyhidb9QQRcVi | elijah.smith@example.com | silver-pro-498 |
商品と価格
商品は、顧客がサブスクリプションで購入できるアイテムを記述します。価格は商品に関連付けられており、コスト、請求期間、通貨を設定します。 subscriptions
テーブルのデータを表示するときは、subscription_items
と結合できます。さらに、アイテムの price_product_id
を使用して、products.id
に結合できます。
次の例では、有効なサブスクリプションのリストと、商品名およびその明細書表記を返します。
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 | 名前 | statement_descriptor |
---|---|---|
sub_BOMkeJtrkXDMSQQ | ruby-pro-522 | Ruby Pro |
sub_Tryc0mAFOjxyyIl | gold-basic-221 | Gold Basic |
sub_0aYZ3VfFlPFSmhk | silver-pro-498 | Silver Pro |
sub_w2q5kVJODe6lHZZ | diamond-mid-244 | Diamond Mid |
sub_vlL4g38ind8VgIw | ruby-standard-196 | Ruby Standard |
段階制料金
サブスクリプションで段階が設定された価格を使用している場合は、price_tiers
テーブルで各段階に固有のデータを取得できます。たとえば、最初の段階の最大数量と使用されるユニット金額を含む、サブスクリプションの初期段階を把握するには、次のクエリを参照してください。
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 | 最大 | tier_price | 通貨 |
---|---|---|---|---|
sub_nSYSmsfOsYsi2Qt | price_BuNGCeoj3yQAJNx | 30 | 2.00 | usd |
sub_DvwtirBXzN5oMJR | price_2pVmdmBrXWJKPtj | 60 | 1.00 | usd |
sub_3Bv8zxaGnkl7KGb | price_yGoLedq3F6NWI6A | 90 | 0.50 | usd |
インボイス
invoices
テーブルには、個別の Invoice (インボイス) オブジェクトに関するデータが収録されます。サブスクリプションのそれぞれから、顧客が支払うべき金額を示すインボイスが反復的に生成されます。これにはサブスクリプションに必要な金額のほかに、追加で Invoice Item (インボイスアイテム) が (ラインアイテムとして) 作成されている場合はそれらも自動的に含められます。
インボイスは、個々の (Invoice) Line Items ((インボイス) ラインアイテム) から構成されます。これらのラインアイテムは、顧客に対して請求されるサブスクリプションと、そのインボイス に対して作成および適用されるインボイスアイテムを表します。インボイスの内訳を調べてラインアイテムのそれぞれを分析するには、invoice_line_items
テーブルを使用します。
このテーブルの source_id
列には、そのラインアイテムに対応するサブスクリプション (例: sub_LFniDlBMf2Sx1dp
) またはインボイスアイテム (例: ii_85Ktx5mxGq2B5vI
) の ID が記載されます。source_type
列では、そのラインアイテムがサブスクリプションとインボイスアイテムのいずれを表しているかが示されます。
他の外部キーとは異なり、invoice_line_items
テーブルの subscription
列は、必ず入力されるとは限りません。対応するインボイスアイテムがサブスクリプションである場合は、この列がブランクになります (その ID はすでに source_id
列に表示されています)。
インボイスアイテム
インボイスアイテムに関するデータは、invoice_items
テーブルに示されます。一般にインボイスアイテムは、次の請求サイクルの開始時に次回のインボイスに適用される追加金額 (または差し引き金額) の指定に使用されます。インボイスアイテムは、たとえば月間使用量を超えた顧客に請求する必要がある場合や、次回のインボイスで未使用分のサービスのクレジットを提供する必要がある場合に作成します。
次の例では、特定のサブスクリプションのすべてのインボイスと関連する支払い ID を取得します。
select id, charge_id, amount_due from invoices where subscription_id = 'sub_ALJXL9gBYtv6GJ'
id | 名前 | |
---|---|---|
in_qmhDscqM69b1cTQ | ch_DQPsmMmwQvGDTgv | 1999 |
in_IdzepNmuykV0AEd | ch_Sh8Cl4oVFGZPKfG | 1999 |
in_CQB4upF4W6urZyc | 1999 | ch_gpNjx0qFkBrKDkF |
in_xzdIlBR0MNM1JIC | 1999 | ch_RU8PUE5zV5XtFhA |
in_QvmgO0ZFK4es4YW | 1999 | ch_Bs6pL3mkiZamOJC |
インボイスの合計と割引
インボイスの小計は、割引適用前のサブスクリプション、インボイスアイテム、およびインボイスの比例配分 (日割り / 秒割り計算) のすべての金額を表します。インボイスの合計は割引および税の適用後の金額を表します。
invoice.total
= invoice.subtotal
- discount
+ invoice.tax
割引額をインボイスに表示するための列はありません。代わりに、ラインアイテムの割引額を集計することでこれを計算できます。以下のクエリは、インボイス、その期間の開始日と終了日、インボイスの合計割引額のリストを返します。
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 | 通貨 |
---|---|---|---|---|
in_yCKdM8NHe0DW6od | 2024 年 5 月 1 日 | 2024 年 6 月 1 日 | 24.66 | usd |
in_PpVtdoIrET0tL61 | 2024 年 6 月 1 日 | 2024 年 7 月 1 日 | 24.34 | usd |
in_TkiksdiD5ztQDRD | 2024-04-01 | 2024 年 5 月 1 日 | 45.96 | usd |
インボイスの日付と期間の処理
サブスクリプションのインボイスは事前に請求されるため、顧客は請求サイクルの開始時に支払いを行います。これは、ラインアイテムの period
値で表されます。たとえば、月次のサブスクリプションを利用している顧客は、毎月始めに請求されます。 cancel_at_period_end を選択すると、サブスクリプションは月末まで有効であり、その後サブスクリプションは終了します。
インボイスの period_start
と period_end
の値は、インボイスアイテムがいつ作成されたかを表しますが、必ずしも顧客に請求されるサービス期間を完全に表しているわけではありません。たとえば、毎月 1 日に請求を受ける顧客が、15 日にその月間使用量を超えた場合、 顧客に請求される追加費用のインボイスアイテムを作成することができます。このインボイスアイテムは翌月 1 日に作成される次回のインボイスに含まれます。次回のインボイスを生成するときは、period_start
は前月の 15 日 (追加のラインアイテムが最初に作成された日付) になります。
クーポン
Coupon (クーポン) オブジェクトは、サブスクリプションや顧客に適用できる、金額または率による割引を表します。
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 |
割引
割引はクーポンの適用であり、Discount オブジェクトで表されます。以下のクエリは、サブスクリプションとそれに関連付けられた割引とクーポンのリストを返します。
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_PXWukPLBcGUfUCK | di_Wm1rY2oFPQOBv6Y | 10OFF |
sub_MNbSGNElnsMhZ8G | di_H35Gn0DdAkPgKt3 | 25OFF |
sub_wOh022WieFG2I2D | di_9fcnxf1IskvxKpJ | 10FREE |
プロモーションコード
promotion code は、顧客が引き換え可能なクーポンのコードを表します。以下のクエリは、特定のクーポンに関連するプロモーションコードのリストを提供し、各コードが引き換えられた回数を表示します。
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_PZUfEQyiXhMvaXw | 10OFF | 1 |
promo_EBjybqcR0q1msFN | 25OFF | 2 |
promo_3zhUNI8ClJNkHAJ | 10FREE | 3 |
サブスクリプションアイテム変更イベント
subscription_item_change_events
テーブルは、月間経常収益 (MRR) に影響するサブスクリプションアイテムの変更を追跡します。この表を使用して、個人顧客、商品、プランの MRR を計算し、ビジネスモデルのカスタム指標定義を作成します。
注意
この表は、Stripe ダッシュボードの Billing の概要に表示される MRR 基準を構成するソースよりも最新のデータを提供します。このため、ここの前日の MRR のデータはより正確である可能性があり、ダッシュボードでの表示とは異なる可能性があります 。
local_event_timestamp と event_timestamp
このテーブルには、次の 2 つのタイムスタンプ列が含まれます。
event_timestamp
: これは UTC タイムスタンプです。local_event_timestamp
: このタイムスタンプは、お客様のタイムゾーン (通常は、お客様の Stripe アカウントを作成した人物のタイムゾーン) です。
通貨
ここでは、サブスクリプションアイテムの売上処理通貨が、小文字の 3 文字の ISO 通貨コードとして記載されています。通貨は、Stripe がサポートしているものでなければなりません。
mrr_change
mrr_change
列は、イベントが MRR に与えるプラスまたはマイナスの影響を、サブスクリプションアイテムの売上処理通貨の補助通貨単位 (USD の場合はセントなど) で示します。
event_type
イベントタイプ | 定義 |
---|---|
ACTIVE_START | サブスクリプションアイテムが MRR への入金を開始した。 |
ACTIVE_UPGRADE | サブスクリプションアイテムの MRR 構成部分が増加した (数量の増加など)。 |
ACTIVE_END | サブスクリプションアイテムが MRR への入金を停止した。 |
ACTIVE_DOWNGRADE | サブスクリプションアイテムの MRR 構成部分が減少した (数量の減少など)。 |
注
一部のユーザーアクションでは複数のイベントが作成されることがあります。このため、あるアイテムで event_type
が ACTIVE_END
のイベントが表示され、その後すぐに、同じ subscription_id
の別のアイテムで event_type
が ACTIVE_START
のイベントが表示されることがあります。
その他の列
その他の列 (product_id
、price_id
、customer_id
、subscription_id
、subscription_item_id
) には、サブスクリプションアイテム変更イベントに関連する ID が格納されます。
クエリの例
MRR を計算するためにこのテーブルにクエリを実行するには、窓関数のほか、顧客がそれぞれ異なる通貨を使用する場合には外貨為替計算が必要となります。過去 2 カ月間の日次の MRR を USD セント単位で計算する場合の例を以下に示します。
-- 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 |
---|---|
2024-04-17 | 14402570 |
2024-04-16 | 14410900 |
2024-04-15 | 14403403 |
2024-04-14 | 14372582 |
2024-04-13 | 14365918 |
2024-04-12 | 14327600 |
2024-04-11 | 14346759 |
2024-04-10 | 14363419 |
2024-04-09 | 14351757 |
2024-04-08 | 14333431 |
2024-04-07 | 14319270 |
2024-04-06 | 14316771 |
2024-04-05 | 14240135 |
2024-04-04 | 14250131 |
2024-04-03 | 14253463 |
2024-04-02 | 14247632 |
2024-04-01 | 14247632 |
2024-03-31 | 14214312 |
2024-03-30 | 14187656 |
2024-03-29 | 14146839 |
2024-03-28 | 14152670 |
2024-03-27 | 14152670 |
2024-03-26 | 14131012 |
2024-03-25 | 14124348 |
2024-03-24 | 14116851 |
2024-03-23 | 14115185 |
2024-03-22 | 14097692 |
2024-03-21 | 14104356 |
2024-03-20 | 14113519 |
2024-03-19 | 14097692 |
2024-03-18 | 14088529 |