Stripe Billing の顧客情報とサブスクリプション情報をGoogleスプレッドシートにエクスポートする方法

こんにちは Zaimo.ai テックリードの滝元です。

この記事では、Stripeの顧客情報とサブスクリプション情報をGoogleスプレッドシートにエクスポートする方法を説明します。Google Apps Scriptを使用して、Stripe APIから顧客データを取得し、それをGoogleスプレッドシートに書き込みます。
Zaimo.aiでは売上をアカウント数などのKPIまで細かく分解し、予実管理ができます。 実績値を入力するときにスプレッドシートに情報がまとまっていると転記がスムーズに行えます。

必要なもの

• Googleアカウント
• StripeアカウントとAPIキー

以下のコードをGoogle Apps Scriptエディタにコピーして実行します。
サンプルコードではユーザー情報、サブスクリプションの料金、トライアル中かどうか、トライアル期間がいつまでかを１ユーザー１行ごとに追加します。

GAS コード

function main() {
  getStripeInfo();
}

function getAllProducts(apiKey) {
  const products = {};
  let hasMore = true;
  let startingAfter = '';

  while (hasMore) {
    let url = 'https://api.stripe.com/v1/products?limit=100';
    if (startingAfter) {
      url += '&starting_after=' + startingAfter;
    }

    const response = UrlFetchApp.fetch(url, { "headers": { "Authorization": "Bearer " + apiKey } });
    const jsonResponse = JSON.parse(response.getContentText());

    jsonResponse.data.forEach(product => {
      products[product.id] = product.name;
    });

    hasMore = jsonResponse.has_more;
    if (hasMore) {
      startingAfter = jsonResponse.data[jsonResponse.data.length - 1].id;
    }
  }

  return products;
}

function getStripeInfo() {
  // Stripe APIキーを設定してください
  const apiKey = 'test_key';
  const sheetName = 'Stripe';
  
  // Google Sheetsの対象シートを取得
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
  }

  // 既存のデータをクリア
  sheet.clear();

  // シートにヘッダーを設定
  const headersArray = ['ID', 'Email', 'Name', 'Created', 'Plan', 'Active', 'Trialing', 'Trial End', 'Price', 'Product'];
  sheet.appendRow(headersArray);

  // 全プロダクト情報を取得
  const products = getAllProducts(apiKey);
  
  const baseUrl = 'https://api.stripe.com/v1/customers';
  const headers = {
    "Authorization": "Bearer " + apiKey
  };

  let hasMore = true;
  let startingAfter = '';
  let allRows = [];

  // 全顧客を取得するためのループ
  while (hasMore) {
    let url = baseUrl + '?limit=100';
    if (startingAfter) {
      url += '&starting_after=' + startingAfter;
    }

    // APIリクエストを送信
    const response = UrlFetchApp.fetch(url, { "headers": headers });
    const jsonResponse = JSON.parse(response.getContentText());

    jsonResponse.data.forEach(function(customer) {
      const subscriptionsUrl = `https://api.stripe.com/v1/customers/${customer.id}/subscriptions`;
      const subscriptionsResponse = UrlFetchApp.fetch(subscriptionsUrl, { "headers": headers });
      const subscriptions = JSON.parse(subscriptionsResponse.getContentText());

      let plan = '';
      let price = '';
      let productName = '';
      let trialing = false;
      let trialEnd = '';
      let active = false;

      if (subscriptions.data.length > 0) {
        const subscription = subscriptions.data[0];
        plan = subscription.plan.nickname || subscription.plan.id;
        trialing = subscription.status === 'trialing';
        active = subscription.status === 'active';
        if (trialing) {
          trialEnd = new Date(subscription.trial_end * 1000);
        }

        const priceId = subscription.plan.id;
        const priceUrl = `https://api.stripe.com/v1/prices/${priceId}`;
        const priceResponse = UrlFetchApp.fetch(priceUrl, { "headers": headers });
        const priceData = JSON.parse(priceResponse.getContentText());
        price = priceData.unit_amount;

        const productId = priceData.product;
        productName = products[productId];
      }

      const row = [
        customer.id,
        customer.email,
        customer.name,
        new Date(customer.created * 1000),
        plan,
        active,
        trialing,
        trialEnd,
        price,
        productName,
      ];
      allRows.push(row);
    });

    hasMore = jsonResponse.has_more;
    if (hasMore) {
      startingAfter = jsonResponse.data[jsonResponse.data.length - 1].id;
    }
  }

  // 一度にすべての行をシートに追加
  sheet.getRange(2, 1, allRows.length, headersArray.length).setValues(allRows);
}

今回使用したStripeのオブジェクトについてまとめました。

Productオブジェクト

説明:

StripeのProductオブジェクトは、商品やサービスを表すために使用されます。このオブジェクトは、サブスクリプションプラン、料金、その他の販売アイテムと関連付けられます。

主要プロパティ:

id: 一意の識別子
name: 商品やサービスの名前
description: 商品やサービスの説明
created: 作成日時（Unixタイムスタンプ）

APIエンドポイント:

GET /v1/products: 商品の一覧を取得
GET /v1/products/{product_id}: 特定の商品を取得

Customerオブジェクト

説明:

StripeのCustomerオブジェクトは、顧客情報を管理するために使用されます。このオブジェクトは、支払い方法、サブスクリプション、請求履歴などを含む顧客に関するすべての情報を格納します。

主要プロパティ:

id: 一意の識別子
email: 顧客のメールアドレス
name: 顧客の名前
created: 作成日時（Unixタイムスタンプ）
subscriptions: 顧客のサブスクリプション情報

APIエンドポイント:

GET /v1/customers: 顧客の一覧を取得
GET /v1/customers/{customer_id}: 特定の顧客を取得

Subscriptionオブジェクト

説明:

StripeのSubscriptionオブジェクトは、顧客のサブスクリプション（定期購入）を管理するために使用されます。このオブジェクトは、顧客がどのプランに加入しているか、サブスクリプションの状態、試用期間の終了日時などの情報を含みます。

主要プロパティ:

id: 一意の識別子
customer: 顧客ID
plan: サブスクリプションプラン（Planオブジェクトへの参照）
status: サブスクリプションの状態（例: active, trialing, canceled）
trial_end: 試用期間の終了日時（Unixタイムスタンプ）

APIエンドポイント:

GET /v1/subscriptions: サブスクリプションの一覧を取得
GET /v1/subscriptions/{subscription_id}: 特定のサブスクリプションを取得

Planオブジェクト

説明:

StripeのPlanオブジェクトは、サブスクリプションの料金プランを表します。このオブジェクトは、価格、請求周期、商品との関連を管理します。

主要プロパティ:

id: 一意の識別子
nickname: プランのニックネーム
amount: 価格
currency: 通貨
interval: 請求周期（例: month, year）

APIエンドポイント:

GET /v1/plans: プランの一覧を取得
GET /v1/plans/{plan_id}: 特定のプランを取得

Priceオブジェクト

説明:

StripeのPriceオブジェクトは、特定の商品やサービスの価格を表します。このオブジェクトは、商品の価格設定や課金の詳細を管理します。

主要プロパティ:

id: 一意の識別子
unit_amount: 価格（セント単位）
currency: 通貨
product: 関連するProductオブジェクトのID

APIエンドポイント:

GET /v1/prices: 価格の一覧を取得
GET /v1/prices/{price_id}: 特定の価格を取得

一度のリクエストでサブスクリプション情報に関連する商品名や料金プランを取得できないので、サンプルコードのようにそれぞれのオブジェクトの情報を取得する必要があります。
また一度のリクエストで100件しかデータが取得できないのでhasMore変数を利用し、ループするようにしました。

おわりに

必要なプロパティは会社ごとに異なると思うので調整してください。
main関数を時間指定のトリガーに設定することで定期的にアップデートされます。
GASを使いこなしてKPI収集マスターになろう！

一緒にZaimo.aiを作っていただけるメンバーを募集しています！

AIに囲まれた環境でプロダクト開発を一緒にしていただけるメンバーを募集しています！
グローバル使われるAI経営管理SaaSの実現を目指して開発をリードしていただけるシニアエンジニアを募集中です！ 詳細はこちら
副業からでもOKです！

参考

Stripe API の概要