Stripeの全てのSubscriptionsのstatus（active, trialing）を集計する方法を紹介します

@Panda_Program

ソフトウェアエンジニアのための問答ラジオ

パンダとおくだが、Web業界の当たり前を「これって本当にそうだっけ？」と問い直すラジオを配信しています

Stripe APIを使って、Subscriptions（定期払い）のステータスを集計する

この記事では、StripeのAPIを使って、Subscriptions（定期払い）のユーザーのステータスをGASで集計する方法をご紹介します。

StripeとはSaaSの決済サービスです。その中でも今回はSubscriptionsという定期払いに焦点を当て、Subscriptionsのstatusがactive（課金が有効）、trialing（トライアル）、また解約予定の数を集計します。

集計にはいくつか方法があるので、それぞれのメリットとデメリットを比較していきます。

後半では集計した数値をスプレッドシートに保存し、Slackに通知する方法をご紹介します。

それでは早速見ていきましょう。

関連記事

Subscriptionsの集計方法の検討

Subscriptionsの集計には3つの手段があります。Stripe SIGMA、ダッシュボードからCSVのエクスポート、そしてStripe APIをコールすることです。

以下、順番に検討していきましょう。

Stripe SIGMAでSQLを実行する

Stripe SIGMAは、Stripeに保存されているデータに対してSQLを発行できるサービスです。

Stripe SIGMA

SQLを書いて柔軟にデータを取得できるというメリットを持つ一方、SIGMAの利用は有料です。このため、今回は別の方法を探しましょう。十分に予算が確保されている場合は、データ集計に最適な手段と言えるでしょう。

料金体系は図示されていますので、お使いになる場合は参照してみてください。なお、SIGMAの利用は別途申請が必要になります。

ダッシュボードからCSVをエクスポートする

Stripeのダッシュボードから「定期支払い」を選択します。

そして、「エクスポート」というボタンをクリックすると、定期支払いのデータをCSVの形式でダウンロードできます。

Stripeのダッシュボード

CSVのままでは扱いにくいと思うので、Googleのスプレッドシートにインポートしましょう。スプレッドシートの関数sumifを使ってstatusを指定し、active, triallingの数を集計すれば目的を達成できます。

あるいは、CSVを読み込む簡単なプログラムを書いて、ローカルで実行することもできます。どちらもデータの加工は手軽にできるところがメリットです。

また、ダッシュボードにアクセスできる人なら誰でもエクスポートができるので、データ取得の仕事はエンジニア以外の方にも任せることができます。

しかし、データは毎日集計してこそ真価を発揮するものです。ご想像の通り、毎日Stripeのダッシュボードにアクセスして、手作業でエクスポートをするのは煩雑です。これがエクスポートする方法のデメリットです。

Stripe APIを利用する

StripeはAPIを公開しており、プログラムを通してデータを取得することは容易です。

Stripeは主要なプログラミング言語に対応したライブラリが手厚く揃っています。2020年6月現在、Go, Ruby, Node.js, PHP, JAVA, Python, .NET用のライブラリが公開されています。

これらの言語のStripe用ライブラリを利用できれば、Subscriptionsを集計することは容易です。

集計プログラムを定期実行するだけなので、実行場所はFaaSが適切です。LambdaかGoogle Apps Script（以下、GAS）が最適ですね。

今回は、集計した値をスプレッドシートに保存したいので、GASを活用します。

GASはJavaScriptを実行できますが、Node.jsのモジュールを利用できません。よってStripeのライブラリは利用できないため、エンドポイントにGETリクエストを送ってSubscriptionsのデータを取得します。

GASでSubscriptionのstatusを集計する

SubscriptionsのList APIをコールする

StripeのList Subscriptions APIを利用します。

以下はcurlを使ってSubscriptionsのデータを取得するコマンドのサンプルです。

curl https://api.stripe.com/v1/subscriptions \
  -u sk_live_XXXXXXXXXXXXXX: \
  -d limit=3 \
  -G

sk_live_XXXXXXXXXXXXXXは、シークレットキーです。Stripeのダッシュボードから取得できますが、このキーは絶対に外に漏れないように管理しましょう。

次にcurlのオプションを1つずつみていきましょう。

-u: basic認証。userにシークレットキーを指定します。
-d: key=valueのデータ。limit=3なので、最大3件取得します
-G: GETメソッド

これをJavaScriptで書き替えると以下のようになります。以下はGASのコードです。

const URL = 'https://api.stripe.com/v1/subscriptions'
const LIMIT = 3
const params = `?limit=${LIMIT}`

const SECRET_KEY = 'sk_live_XXXXXXXXXXXXXX'

const options = {
  headers: {
    Authorization: 'Basic ' + Utilities.base64Encode(SECRET_KEY)
  },
  method: 'GET'
}

UrlFetchApp.fetch(URL + params, options)

curlでもGASでも、返り値は以下のようになります。

{
  "object": "list",
  "url": "/v1/subscriptions",
  "has_more": false,
  "data": [
    {
      "id": "su_XXXXXXXXXXXXXX",
      "object": "subscription",
      "current_period_end": 1594980643,
      "customer": "cus_XXXXXXXXXXXXXX",
      "status": "active",
      ...
    },
    {...},
    {...}
  ]
}

（不要な値は省略しています。全ての値を確認したい場合はAPIドキュメントを参照してください。）

1件目のstatusはactiveですね。statusactiveとtrialingの数をカウントしていきましょう。また、今回は解約予定の人の数もカウントします。

Subscriptionsのstatusをカウントするコード

main.js
// Stripe
const URL = 'https://api.stripe.com/v1/subscriptions'
// 1. StripeのシークレットキーをGASのプロパティから取得します
const SECRET_KEY = PropertiesService
    .getScriptProperties()
    .getProperty('SECRET_KEY')
// 1度のリクエストにおけるlimitの最大数の100を指定します
const LIMIT = 100

function fetchSubscriptionList(params) {
  const options = {
    headers: {
      Authorization: 'Basic ' + Utilities.base64Encode(SECRET_KEY)
    },
    muteHttpExceptions: true,
    method: 'GET'
  }
  const res = UrlFetchApp.fetch(URL + params, options)

  // レスポンスボディをオブジェクトに変換します
  return JSON.parse(res.getContentText())
}

function getSubscriptionStatusCounts() {
  let activeCount = trialingCount = cancelCount = 0
  // pagination
  let startingAfter = null

  while(true) {
    // 2. データが101件以上ある場合は、ページネーションで次の100件を取得します
    // starting_afterでカーソルを指定しましょう
    const params = startingAfter === null
        ? `?limit=${LIMIT}`
        : `?limit=${LIMIT}&starting_after=${startingAfter}`
    const subscriptions = fetchSubscriptionList(params)

    subscriptions.data.forEach(subscription => {
      // subscriptionのstatusに応じてカウント
      switch (subscription.status) {
        case 'active':
          activeCount += 1
          break;
        case 'trialing':
          trialingCount += 1
          break;
        default:
          break
      }

      // 3. キャンセル予約をしている人をカウント
      if (subscription.cancel_at_period_end){
        cancelCount += 1
      }

      // ページネーションのために最後のidを保持する
      startingAfter = subscription.id
    });

    // 4.paginationの最後でloopを抜ける
    if(!subscriptions.has_more) {
      break;
    }
  }

  return [activeCount, trialingCount, cancelCount]
}

以下、コードの解説をします。

  1. シークレットキーはスクリプトのプロパティに保存しています。[この手順は「Google Apps Scriptのプロパティに値を保存する」をご覧ください。](/posts/gas-slack-line-bot/#Google Apps Scriptのプロパティに値を保存する)
  2. 一度のリクエストで取得できるsubscriptionの件数は100件です。「定期支払い」の件数がそれ以上存在する場合、paginationの機能を使って残りのオブジェクトを取得しましょう。
  3. キャンセル予定の人のstatusはcanceledではありません。canceledはキャンセル済みの人のステータスだからです。このため、解約予約をしている人はcancel_at_period_endというbooleanを使ってカウントします。
  4. レスポンスオブジェクト内のプロパティhas_moreは、subscriptionオブジェクトが残っているかを示すbooleanです。全てのオブジェクトを取得したら、loopを抜けてエンドポイントにリクエストを送るのをストップしましょう。

なお、今回はキャンセル済みのSubscriptionsは集計対象に含まれていません。もし集計する場合は、paramsにstatus=calceledを追加してください。

スプレッドシートに集計した値を保存する

次に、取得した値をスプレッドシートに保存します。

const SPREADSHEET_ID = PropertiesService
  .getScriptProperties()
  .getProperty('SPREADSHEET_ID')

function addRow(counts) {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID)
  const sheet = ss.getActiveSheet()
  const lastRow = sheet.getLastRow()
  const data = [[
    Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd'),
    ...counts,
  ]]

  // 最終行に、日付と各種数値を追加
  sheet.getRange(lastRow + 1, 1, 1, 4).setValues(data)
}

変数countの中身が[160, 320, 8]（順に課金ユーザー数、トライアル数、解約予定数）である場合、スプレッドシートの8行目のように行を追加します。

スプレッドシート

sheet.getLastRow()で最終行を取得しているので、GASを実行する度に自動で新しく行を追加できます。

Slackと連携して集計結果を通知する

最後にこのデータをSlackに通知しましょう。これでエンジニアだけでなく、プロダクトオーナー、マーケター、デザイナー、また部門長などあらゆる人が課金ユーザー数、つまりビジネスの進捗を把握できます。

const SLACK_WEBHOOK_URL = PropertiesService
    .getScriptProperties()
    .getProperty('SLACK_WEBHOOK_URL')

function sendSlack(counts) {
  const text = `
今日の全ユーザーの課金状態です。
課金ユーザー：${counts[0]}
トライアル：${counts[1]}
解約予定：${counts[2]}
次回課金予定数：${counts[0] + counts[1] - counts[2]}
`
  const data = {
    'attachments': [{text}],
  }
  const slackOptions = {
    'method' : 'POST',
    'contentType' : 'application/json',
    'payload' : JSON.stringify(data)
  }

  UrlFetchApp.fetch(SLACK_WEBHOOK_URL, slackOptions);
}

GASのトリガーをを設定し、毎日Slackに通知するbotにする

最後に、main関数を作りましょう。

function main() {
  const counts = getSubscriptionStatusCounts()
  // Spreadsheetに値を保存する
  addRow(counts)
  // Slackに送る
  sendSlack(counts)
}

毎日main関数を定期実行するために、トリガーを設定します。

これでbot化完了です！

まとめ

Subscriptionのstatusを集計するためにGoogle Apps Scriptでコードを記述しました。Stripeについての日本語の記述はまだまだ少ないので、本記事がお役に立てたら幸いです。

コードの全文を記載します

コードが断片的になって見通しが悪いので全体を記載します。ぜひ参考にしてみてください。

main.js
// Stripe
const URL = 'https://api.stripe.com/v1/subscriptions'
const SECRET_KEY = PropertiesService
    .getScriptProperties()
    .getProperty('SECRET_KEY')
const LIMIT = 100

// Spreadsheet
const SPREADSHEET_ID = PropertiesService
  .getScriptProperties()
  .getProperty('SPREADSHEET_ID')

// Slack
const SLACK_WEBHOOK_URL = PropertiesService
    .getScriptProperties()
    .getProperty('SLACK_WEBHOOK_URL')

function fetchSubscriptionList(params) {
  const options = {
    headers: {
      Authorization: 'Basic ' + Utilities.base64Encode(SECRET_KEY)
    },
    muteHttpExceptions: true,
    method: 'GET'
  }
  const res = UrlFetchApp.fetch(URL + params, options)

  // レスポンスボディをオブジェクトに変換します
  return JSON.parse(res.getContentText())
}

function getSubscriptionStatusCounts() {
  let activeCount = trialingCount = cancelCount = 0
  // pagination
  let startingAfter = null

  while(true) {
    // 2. データが101件以上ある場合は、ページネーションで次の100件を取得します
    // starting_afterでカーソルを指定しましょう
    const params = startingAfter === null
        ? `?limit=${LIMIT}`
        : `?limit=${LIMIT}&starting_after=${startingAfter}`
    const subscriptions = fetchSubscriptionList(params)

    subscriptions.data.forEach(subscription => {
      // subscriptionのstatusに応じてカウント
      switch (subscription.status) {
        case 'active':
          activeCount += 1
          break;
        case 'trialing':
          trialingCount += 1
          break;
        default:
          break
      }

      // 3. キャンセル予約をしている人をカウント
      if (subscription.cancel_at_period_end){
        cancelCount += 1
      }

      // ページネーションのために最後のidを保持する
      startingAfter = subscription.id
    });

    // 4.paginationの最後でloopを抜ける
    if(!subscriptions.has_more) {
      break;
    }
  }

  return [activeCount, trialingCount, cancelCount]
}

function addRow(counts) {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID)
  const sheet = ss.getActiveSheet()
  const lastRow = sheet.getLastRow()
  const data = [[
    Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd'),
    ...counts,
  ]]

  // 最終行に、日付と各種数値を追加
  sheet.getRange(lastRow + 1, 1, 1, 4).setValues(data)
}

function sendSlack(counts) {
  const text = `
今日の全ユーザーの課金状態です。
課金ユーザー：${counts[0]}
トライアル：${counts[1]}
解約予定：${counts[2]}
次回課金予定数：${counts[0] + counts[1] - counts[2]}
`
  const data = {
    'attachments': [{text}],
  }
  const slackOptions = {
    'method' : 'POST',
    'contentType' : 'application/json',
    'payload' : JSON.stringify(data)
  }

  UrlFetchApp.fetch(SLACK_WEBHOOK_URL, slackOptions);
}

function main() {
  const counts = getSubscriptionStatusCounts()
  addRow(counts)
  sendSlack(counts)
}
パンダのイラスト
パンダ

記事が面白いと思ったらツイートやはてブをお願いします！皆さんの感想が執筆のモチベーションになります。最後まで読んでくれてありがとう。

  • Share on Hatena
  • Share on Twitter
  • Share on Line
  • Copy to clipboard
StripeGoogle Apps Script

「Stripe」カテゴリの投稿