[Stripe Sigmaで始めるRevOps] 日ごと・週ごと・月ごとの売り上げ額や返金額・件数を取得する方法

サブスクリプションやECにおいて、「どれだけ売上が出たか」や「返金・不正請求の申立てが何件・何円発生しているか」などの分析はとても重要です。

Stripeを利用しているサービスでは、Stripe Sigmaを利用してSQLでデータの取得や分析ができます。

https://stripe.com/jp/sigma

今回の記事では、日ごと・週ごと・月ごとの各種データを分析するためのクエリの作り方や、カスタマイズ方法を紹介します。

## テンプレートから日ごとのデータを取得するクエリを実行する

Sigmaのクエリ実行画面を開きましょう。

![スクリーンショット 2023-04-21 16.55.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2366300/d7a9e89e-9856-51fe-8ed2-6a23cc3124c1.png)

左側のテンプレートから、[Daily activity]を選択します。

![スクリーンショット 2023-04-21 16.56.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2366300/bd37b646-65fd-bdb1-5def-bb54f6e872b7.png)

右側のクエリ画面に、テンプレートのSQLが表示されます。

![スクリーンショット 2023-04-21 16.56.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2366300/e3986151-6469-3600-d471-8af033e365f3.png)

[実行]ボタンをクリックすると、日毎の売上高(sales)や返金(refund)、不正請求申立て（disputes）などのデータが表示されます。

![スクリーンショット 2023-04-21 16.58.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2366300/87ebcc98-d631-2e81-fa00-fd62aa31a0c3.png)

### テンプレートのクエリは、日本円だと数値が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をカスタマイズしましょう。

## テンプレートをカスタマイズして、日本円に対応する

テンプレートをカスタマイズするには、ページ右上にある[コピーを作成]をクリックします。

![スクリーンショット 2023-04-21 17.11.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2366300/f3290db7-6f84-37dd-e9f9-4aa25043e0cb.png)

`Daily activityのコピー`が作成されますので、「`Daily activity (JPY)`」などに変更しましょう。

![スクリーンショット 2023-04-21 17.12.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2366300/5839fdc0-c235-1b32-f7fb-53571c742b9f.png)

続いてSQLを確認して、「数値を1/100にしている部分」を削除しましょう。

```diff_sql
-  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`クエリに通貨を指定する条件を追加することもできます。

```diff_sql
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`をそれぞれ変更しましょう。

```diff_sql

  select
-  date(created) as day,
+  date_trunc('week', created) as day,
```

これで集計単位を週毎に変更できます。

![スクリーンショット 2023-04-21 17.25.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2366300/430c832f-3e1b-e208-c1f6-c80fab432d04.png)

実際に運用する場合は、混乱を招かないためにも`day`を`week`や`week_start`などに変更することをお勧めします。

同様に`week`を`month`に変更すると、月ごとのレポートが生成できます。

```diff_sql

  select
-  date_trunc('week', created) as day,
+  date_trunc('month', created) as day,
```

![スクリーンショット 2023-04-21 17.27.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2366300/c2e125e2-caf4-4040-3ac6-757afe737519.png)


## [Appendix] Stripe Sigmaの活用事例

Stripe Sigmaを実際に利用して、ビジネスの改善や業務効率化につなげている事例やコミュニティでの登壇資料を一部紹介します。

https://scrapbox.io/akiroom/SQL%E3%81%A7%E3%83%93%E3%82%B8%E3%83%8D%E3%82%B9%E3%81%AE%E8%A7%A3%E5%83%8F%E5%BA%A6%E3%82%92UP!!_Stripe_Sigma%E6%B4%BB%E7%94%A8%E3%81%AE%E3%82%B9%E3%82%B9%E3%83%A1

https://stripe.com/jp/customers/slack-jonathan-gan