サブスクリプションやECにおいて、「どれだけ売上が出たか」や「返金・不正請求の申立てが何件・何円発生しているか」などの分析はとても重要です。
Stripeを利用しているサービスでは、Stripe Sigmaを利用してSQLでデータの取得や分析ができます。
今回の記事では、日ごと・週ごと・月ごとの各種データを分析するためのクエリの作り方や、カスタマイズ方法を紹介します。
テンプレートから日ごとのデータを取得するクエリを実行する
Sigmaのクエリ実行画面を開きましょう。
左側のテンプレートから、[Daily activity]を選択します。
右側のクエリ画面に、テンプレートのSQLが表示されます。
[実行]ボタンをクリックすると、日毎の売上高(sales)や返金(refund)、不正請求申立て（disputes）などのデータが表示されます。
テンプレートのクエリは、日本円だと数値が1/100になる
このクエリには、次のコメントが添えられています。
Note: if you have currencies that do not have cents (e.g. JPY), you should not divide by 100.0
USDでは最小単位がセントのため、ドルでの集計には100で割る必要があります。
しかし日本円ではその必要がないため、テンプレートのクエリでは数値が1/100で表示されてしまします。
そこでテンプレートのSQLをカスタマイズしましょう。
テンプレートをカスタマイズして、日本円に対応する
テンプレートをカスタマイズするには、ページ右上にある[コピーを作成]をクリックします。
Daily activityのコピーが作成されますので、「Daily activity (JPY)」などに変更しましょう。
続いてSQLを確認して、「数値を1/100にしている部分」を削除しましょう。
- coalesce(sales / 100.0, 0) as sales,
- coalesce(refunds / 100.0, 0) as refunds,
- coalesce(disputes / 100.0, 0) as disputes,
- coalesce(disputes_won / 100.0, 0) as disputes_won,
- coalesce((total_gross_activity - (sales + refunds + disputes)) / 100.0, 0) as other_adjustments,
- coalesce(total_gross_activity, 0) as total_gross_activity,
+ coalesce(sales, 0) as sales,
+ coalesce(refunds, 0) as refunds,
+ coalesce(disputes, 0) as disputes,
+ coalesce(disputes_won, 0) as disputes_won,
+ coalesce(total_gross_activity - (sales + refunds + disputes), 0) as other_adjustments,
+ coalesce(total_gross_activity, 0) as total_gross_activity,
念の為、WHEREクエリに通貨を指定する条件を追加することもできます。
where
calendar_days.day >= (select min(daily_balance_transactions.day) from daily_balance_transactions) -- since the first transaction
+ AND currency = 'jpy'
集計期間を週ごと・月ごとに変更する
daily_balance_transactionsとdaily_customersのSELECTをそれぞれ変更しましょう。
select
- date(created) as day,
+ date_trunc('week', created) as day,
これで集計単位を週毎に変更できます。
実際に運用する場合は、混乱を招かないためにもdayをweekやweek_startなどに変更することをお勧めします。
同様にweekをmonthに変更すると、月ごとのレポートが生成できます。
select
- date_trunc('week', created) as day,
+ date_trunc('month', created) as day,
[Appendix] Stripe Sigmaの活用事例
Stripe Sigmaを実際に利用して、ビジネスの改善や業務効率化につなげている事例やコミュニティでの登壇資料を一部紹介します。