Claude Code で『モバイルアプリ収益ダッシュボード』を作る — AdMob・App Store Connect・Google Play Console の API 統合から日次レポート自動配信まで

// src/auth/appstore.ts
import { SignJWT, importPKCS8 } from 'jose';
 
interface AppStoreAuthConfig {
  issuerId: string;   // App Store Connect の Issuer ID
  keyId: string;       // P8 ファイル名の "AuthKey_XXX.p8" の XXX 部分
  privateKey: string;  // P8 ファイルの中身（PEM 形式）
}
 
/**
 * App Store Connect API 用の JWT を生成します。
 * 有効期限は最大20分（Apple の仕様）。実運用では毎回生成するよりキャッシュ推奨。
 */
export async function generateAppStoreJWT(config: AppStoreAuthConfig): Promise<string> {
  const privateKey = await importPKCS8(config.privateKey, 'ES256');
 
  const jwt = await new SignJWT({
    iss: config.issuerId,
    aud: 'appstoreconnect-v1',
  })
    .setProtectedHeader({
      alg: 'ES256',
      kid: config.keyId,
      typ: 'JWT',
    })
    .setIssuedAt()
    .setExpirationTime('18m') // 20分ぎりぎりより少し短く
    .sign(privateKey);
 
  return jwt;
}

// src/fetchers/appstore-sales.ts
import { generateAppStoreJWT } from '../auth/appstore';
import { gunzipSync } from 'node:zlib';
 
export interface AppStoreSalesRow {
  appleId: string;
  title: string;
  units: number;
  developerProceeds: number; // 開発者手取り (USD)
  date: string;
}
 
/**
 * 指定日の Sales Report を取得します。
 * レスポンスは gzip 圧縮された TSV で返ってくる点に注意。
 */
export async function fetchAppStoreSales(
  jwt: string,
  vendorNumber: string,
  reportDate: string, // YYYY-MM-DD
): Promise<AppStoreSalesRow[]> {
  const params = new URLSearchParams({
    'filter[frequency]': 'DAILY',
    'filter[reportType]': 'SALES',
    'filter[reportSubType]': 'SUMMARY',
    'filter[vendorNumber]': vendorNumber,
    'filter[reportDate]': reportDate,
    'filter[version]': '1_1',
  });
 
  const res = await fetch(
    `https://api.appstoreconnect.apple.com/v1/salesReports?${params}`,
    {
      headers: {
        Authorization: `Bearer ${jwt}`,
        Accept: 'application/a-gzip', // この Accept ヘッダが必須
      },
    },
  );
 
  if (res.status === 404) {
    // 当日データがまだ確定していない場合は 404 で返る
    return [];
  }
  if (!res.ok) {
    throw new Error(`App Store Connect error: ${res.status} ${await res.text()}`);
  }
 
  // gzip 解凍して TSV をパース
  const compressed = new Uint8Array(await res.arrayBuffer());
  const decompressed = gunzipSync(compressed).toString('utf-8');
 
  return parseTSV(decompressed);
}
 
function parseTSV(tsv: string): AppStoreSalesRow[] {
  const lines = tsv.split('\n').filter(Boolean);
  const headers = lines[0].split('\t');
  const rows = lines.slice(1);
 
  const idxAppleId = headers.indexOf('Apple Identifier');
  const idxTitle = headers.indexOf('Title');
  const idxUnits = headers.indexOf('Units');
  const idxProceeds = headers.indexOf('Developer Proceeds');
  const idxDate = headers.indexOf('Begin Date');
 
  return rows
    .map((row) => {
      const cols = row.split('\t');
      return {
        appleId: cols[idxAppleId],
        title: cols[idxTitle],
        units: Number(cols[idxUnits] || 0),
        developerProceeds: Number(cols[idxProceeds] || 0),
        date: cols[idxDate],
      };
    })
    .filter((r) => r.units > 0);
}

// src/fetchers/admob-report.ts
import { GoogleAuth } from 'google-auth-library';
 
export interface AdMobRow {
  appId: string;
  appName: string;
  date: string;
  impressions: number;
  earnings: number;
  ecpm: number;
}
 
/**
 * AdMob の日次レポートを取得します。
 * POST で JSON クエリを送る点が REST っぽくないので注意。
 */
export async function fetchAdMobReport(
  serviceAccountKeyJson: string,
  accountId: string, // "pub-XXXXXXXXXX"
  startDate: string,
  endDate: string,
): Promise<AdMobRow[]> {
  const auth = new GoogleAuth({
    credentials: JSON.parse(serviceAccountKeyJson),
    scopes: ['https://www.googleapis.com/auth/admob.readonly'],
  });
  const client = await auth.getClient();
  const token = (await client.getAccessToken()).token;
 
  const body = {
    reportSpec: {
      dateRange: {
        startDate: parseDate(startDate),
        endDate: parseDate(endDate),
      },
      dimensions: ['DATE', 'APP', 'APP_NAME'],
      metrics: ['IMPRESSIONS', 'ESTIMATED_EARNINGS', 'IMPRESSION_RPM'],
      dimensionFilters: [],
      sortConditions: [
        { dimension: 'DATE', order: 'ASCENDING' },
      ],
    },
  };
 
  const res = await fetch(
    `https://admob.googleapis.com/v1/accounts/${accountId}/networkReport:generate`,
    {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${token}`,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify(body),
    },
  );
 
  if (!res.ok) {
    throw new Error(`AdMob error: ${res.status} ${await res.text()}`);
  }
 
  const data = await res.json<any[]>();
  return data
    .filter((item) => item.row)
    .map((item) => ({
      appId: item.row.dimensionValues.APP.value,
      appName: item.row.dimensionValues.APP_NAME.value,
      date: formatDate(item.row.dimensionValues.DATE.value),
      impressions: Number(item.row.metricValues.IMPRESSIONS.integerValue || 0),
      // microsAmount は 100万倍の整数で入ってくる
      earnings: Number(item.row.metricValues.ESTIMATED_EARNINGS.microsAmount || 0) / 1_000_000,
      ecpm: Number(item.row.metricValues.IMPRESSION_RPM.microsAmount || 0) / 1_000_000,
    }));
}
 
function parseDate(ymd: string): { year: number; month: number; day: number } {
  const [y, m, d] = ymd.split('-').map(Number);
  return { year: y, month: m, day: d };
}
 
function formatDate(d: { year: number; month: number; day: number }): string {
  return `${d.year}-${String(d.month).padStart(2, '0')}-${String(d.day).padStart(2, '0')}`;
}

// src/fetchers/playstore-sales.ts
import { GoogleAuth } from 'google-auth-library';
import Papa from 'papaparse';
 
export interface PlayStoreRow {
  date: string;
  productId: string;
  appName: string;
  amountMerchantCurrency: number;
  merchantCurrency: string;
}
 
/**
 * Google Play の月次売上レポート (CSV) を GCS から取得します。
 * バケット名は Google Play Console の「Financial Reports」ページに記載。
 */
export async function fetchPlayStoreSales(
  serviceAccountKeyJson: string,
  bucketName: string, // 例: "pubsite_prod_rev_XXXXXXXXXX"
  yearMonth: string,  // YYYYMM
): Promise<PlayStoreRow[]> {
  const auth = new GoogleAuth({
    credentials: JSON.parse(serviceAccountKeyJson),
    scopes: ['https://www.googleapis.com/auth/devstorage.read_only'],
  });
  const client = await auth.getClient();
  const token = (await client.getAccessToken()).token;
 
  const objectPath = `sales/salesreport_${yearMonth}.zip`;
  const url = `https://storage.googleapis.com/storage/v1/b/${bucketName}/o/${encodeURIComponent(objectPath)}?alt=media`;
 
  const res = await fetch(url, {
    headers: { Authorization: `Bearer ${token}` },
  });
  if (!res.ok) {
    throw new Error(`Play Store GCS error: ${res.status}`);
  }
 
  const zipBuffer = await res.arrayBuffer();
  const csvText = await extractCsvFromZip(zipBuffer);
 
  const parsed = Papa.parse(csvText, { header: true, skipEmptyLines: true });
  return (parsed.data as any[]).map((row) => ({
    date: row['Transaction Date'],
    productId: row['Product id'],
    appName: row['Product Title'],
    amountMerchantCurrency: Number(row['Amount (Merchant Currency)'] || 0),
    merchantCurrency: row['Merchant Currency'],
  }));
}
 
async function extractCsvFromZip(buffer: ArrayBuffer): Promise<string> {
  const { unzipSync, strFromU8 } = await import('fflate');
  const files = unzipSync(new Uint8Array(buffer));
  const csvFile = Object.keys(files).find((name) => name.endsWith('.csv'));
  if (!csvFile) throw new Error('No CSV found in Play Store ZIP');
  return strFromU8(files[csvFile]);
}

// src/worker.ts
import { fetchAppStoreSales, generateAppStoreJWT } from './fetchers/appstore';
import { fetchAdMobReport } from './fetchers/admob-report';
 
interface Env {
  REVENUE_KV: KVNamespace;
  ASC_ISSUER_ID: string;
  ASC_KEY_ID: string;
  ASC_PRIVATE_KEY: string;
  ASC_VENDOR_NUMBER: string;
  ADMOB_SERVICE_KEY: string;
  ADMOB_ACCOUNT_ID: string;
  SLACK_WEBHOOK_URL: string;
}
 
export default {
  async scheduled(controller: ScheduledController, env: Env, ctx: ExecutionContext) {
    const yesterday = new Date(Date.now() - 86400_000).toISOString().slice(0, 10);
 
    const [appStore, adMob] = await Promise.allSettled([
      (async () => {
        const jwt = await generateAppStoreJWT({
          issuerId: env.ASC_ISSUER_ID,
          keyId: env.ASC_KEY_ID,
          privateKey: env.ASC_PRIVATE_KEY,
        });
        return fetchAppStoreSales(jwt, env.ASC_VENDOR_NUMBER, yesterday);
      })(),
      fetchAdMobReport(env.ADMOB_SERVICE_KEY, env.ADMOB_ACCOUNT_ID, yesterday, yesterday),
    ]);
 
    // KV に保存（prefix 設計: revenue:YYYY-MM-DD:platform）
    if (appStore.status === 'fulfilled') {
      await env.REVENUE_KV.put(
        `revenue:${yesterday}:appstore`,
        JSON.stringify(appStore.value),
        { expirationTtl: 60 * 60 * 24 * 365 * 3 }, // 3年保持
      );
    }
    if (adMob.status === 'fulfilled') {
      await env.REVENUE_KV.put(
        `revenue:${yesterday}:admob`,
        JSON.stringify(adMob.value),
      );
    }
 
    await postDailySummaryToSlack(env.SLACK_WEBHOOK_URL, {
      date: yesterday,
      appStore: appStore.status === 'fulfilled' ? sumProceeds(appStore.value) : null,
      adMob: adMob.status === 'fulfilled' ? sumEarnings(adMob.value) : null,
    });
  },
};
 
function sumProceeds(rows: Array<{ developerProceeds: number }>): number {
  return rows.reduce((acc, r) => acc + r.developerProceeds, 0);
}
function sumEarnings(rows: Array<{ earnings: number }>): number {
  return rows.reduce((acc, r) => acc + r.earnings, 0);
}
 
async function postDailySummaryToSlack(
  webhook: string,
  summary: { date: string; appStore: number | null; adMob: number | null },
): Promise<void> {
  const lines = [
    `*${summary.date} の売上サマリ*`,
    summary.appStore !== null ? `App Store: $${summary.appStore.toFixed(2)}` : 'App Store: 取得失敗',
    summary.adMob !== null ? `AdMob: $${summary.adMob.toFixed(2)}` : 'AdMob: 取得失敗',
  ];
  await fetch(webhook, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ text: lines.join('\n') }),
  });
}

[triggers]
crons = ["0 22 * * *"]  # UTC 22:00 = JST 07:00 に毎日実行

// src/fetchers/appstore-sales.ts（修正版）
function parseTSV(tsv: string): AppStoreSalesRow[] {
  // Apple の TSV は CRLF。\r を先に落としておく
  const lines = tsv.replace(/\r/g, '').split('\n').filter(Boolean);
  const headers = lines[0].split('\t');
 
  const idx = (name: string) => {
    const i = headers.indexOf(name);
    if (i === -1) throw new Error(`Sales report column not found: ${name}`);
    return i;
  };
  const idxAppleId = idx('Apple Identifier');
  const idxTitle = idx('Title');
  const idxUnits = idx('Units');
  const idxProceeds = idx('Developer Proceeds');
  const idxDate = idx('Begin Date');
 
  return lines.slice(1)
    .map((row) => {
      const cols = row.split('\t');
      const units = Number(cols[idxUnits] || 0);
      const perUnit = Number(cols[idxProceeds] || 0);
      return {
        appleId: cols[idxAppleId],
        title: cols[idxTitle],
        units,
        perUnitProceeds: perUnit,
        // 行の売上 = 本数 × 単価。返金行は units が負なので自然にマイナスになる
        developerProceeds: units * perUnit,
        date: cols[idxDate],
      };
    })
    // 落とすのは「無料ダウンロード行」だけ。返金（負値）は残す
    .filter((r) => r.units !== 0 && r.perUnitProceeds !== 0);
}

// src/worker.ts（修正版・日付の求め方）
/** 発火時刻に依存せず「JST の前日」を返します */
function jstYesterday(now = new Date()): string {
  const jstNow = new Date(now.getTime() + 9 * 60 * 60 * 1000);
  jstNow.setUTCDate(jstNow.getUTCDate() - 1);
  return jstNow.toISOString().slice(0, 10);
}

// app/api/revenue/route.ts
import { getCloudflareContext } from '@opennextjs/cloudflare';
 
export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const days = Math.min(Number(searchParams.get('days') || '30'), 90);
 
  const { env } = getCloudflareContext();
  const endDate = new Date();
  const startDate = new Date(Date.now() - days * 86400_000);
 
  const results: any[] = [];
  for (let d = new Date(startDate); d <= endDate; d.setDate(d.getDate() + 1)) {
    const ymd = d.toISOString().slice(0, 10);
    const [appStore, adMob] = await Promise.all([
      env.REVENUE_KV.get(`revenue:${ymd}:appstore`, 'json'),
      env.REVENUE_KV.get(`revenue:${ymd}:admob`, 'json'),
    ]);
    results.push({
      date: ymd,
      appStoreTotal: sumOrZero(appStore, 'developerProceeds'),
      adMobTotal: sumOrZero(adMob, 'earnings'),
    });
  }
 
  return Response.json(results);
}
 
function sumOrZero(data: any, key: string): number {
  if (!Array.isArray(data)) return 0;
  return data.reduce((acc, r) => acc + (r[key] || 0), 0);
}

// app/dashboard/page.tsx
'use client';
import { useEffect, useState } from 'react';
import { LineChart, Line, XAxis, YAxis, Tooltip, Legend, ResponsiveContainer } from 'recharts';
 
export default function Dashboard() {
  const [data, setData] = useState<any[]>([]);
  useEffect(() => {
    fetch('/api/revenue?days=30').then((r) => r.json()).then(setData);
  }, []);
 
  return (
    <div style={{ padding: 24 }}>
      <h1>モバイルアプリ収益ダッシュボード</h1>
      <ResponsiveContainer width="100%" height={400}>
        <LineChart data={data}>
          <XAxis dataKey="date" />
          <YAxis />
          <Tooltip />
          <Legend />
          <Line type="monotone" dataKey="appStoreTotal" stroke="#007aff" name="App Store" />
          <Line type="monotone" dataKey="adMobTotal" stroke="#00c853" name="AdMob" />
        </LineChart>
      </ResponsiveContainer>
    </div>
  );
}

// src/fetchers/playstore-sales.ts（修正版の一部）
/** 桁区切りを外し、読めない値は例外にします */
function parseAmount(raw: unknown, ctx: string): number {
  const normalized = String(raw ?? '').replace(/,/g, '').trim();
  const n = Number(normalized);
  if (normalized === '' || Number.isNaN(n)) {
    throw new Error(`Unparsable amount at ${ctx}: ${JSON.stringify(raw)}`);
  }
  return n;
}

// app/api/revenue/route.ts（修正版・sumOrZero の置き換え）
function sumStrict(data: unknown, key: string): number {
  if (!Array.isArray(data)) return 0;
  return data.reduce((acc: number, r: Record<string, unknown>) => {
    const v = r?.[key];
    if (typeof v !== 'number' || Number.isNaN(v)) {
      throw new Error(`Non-numeric ${key}: ${JSON.stringify(v)}`);
    }
    return acc + v;
  }, 0);
}

main = "src/worker.ts"
compatibility_date = "2026-03-01"
compatibility_flags = ["nodejs_compat"]

import Anthropic from '@anthropic-ai/sdk';
 
async function generateInsight(rows: any[]): Promise<string> {
  const client = new Anthropic({ apiKey: process.env.ANTHROPIC_API_KEY });
  const response = await client.messages.create({
    model: 'claude-haiku-4-5-20251001',
    max_tokens: 300,
    messages: [{
      role: 'user',
      content: `以下は過去7日のアプリ売上データです。昨日の数値が通常と異なる点があれば1〜2文で指摘し、対応が必要なら具体的に書いてください。数値データの羅列ではなく、アクションに繋がる洞察を日本語で。\n\n${JSON.stringify(rows, null, 2)}`,
    }],
  });
  return response.content[0].type === 'text' ? response.content[0].text : '';
}

// src/backfill.ts
export async function backfillAppStore(
  env: Env,
  startDate: string,
  endDate: string,
): Promise<void> {
  const jwt = await generateAppStoreJWT({
    issuerId: env.ASC_ISSUER_ID,
    keyId: env.ASC_KEY_ID,
    privateKey: env.ASC_PRIVATE_KEY,
  });
 
  const dates = enumerateDates(startDate, endDate);
  for (const date of dates) {
    try {
      const rows = await fetchAppStoreSales(jwt, env.ASC_VENDOR_NUMBER, date);
      await env.REVENUE_KV.put(`revenue:${date}:appstore`, JSON.stringify(rows));
      await sleep(1000); // Apple のレート制限を考慮して間隔を空ける
    } catch (err) {
      console.error(`Backfill failed for ${date}:`, err);
    }
  }
}
 
function enumerateDates(start: string, end: string): string[] {
  const dates: string[] = [];
  const s = new Date(start);
  const e = new Date(end);
  for (let d = new Date(s); d <= e; d.setDate(d.getDate() + 1)) {
    dates.push(d.toISOString().slice(0, 10));
  }
  return dates;
}
 
function sleep(ms: number): Promise<void> {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

await env.REVENUE_KV.put(
  `health:${new Date().toISOString()}`,
  JSON.stringify({
    ok: appStore.status === 'fulfilled' && adMob.status === 'fulfilled',
    appStore: appStore.status,
    adMob: adMob.status,
    duration_ms: Date.now() - startTime,
  }),
  { expirationTtl: 60 * 60 * 24 * 30 },
);

const normalizedKey = env.ASC_PRIVATE_KEY.endsWith('\n')
  ? env.ASC_PRIVATE_KEY
  : env.ASC_PRIVATE_KEY + '\n';