Build a Mobile App Revenue Dashboard with Claude Code — Integrating AdMob, App Store Connect, and Google Play Console APIs with Automated Daily Reports

// src/auth/appstore.ts
import { SignJWT, importPKCS8 } from 'jose';
 
interface AppStoreAuthConfig {
  issuerId: string;
  keyId: string;       // the 10-char ID in the P8 filename
  privateKey: string;  // contents of the P8 file (PEM)
}
 
/**
 * Generates a JWT for the App Store Connect API.
 * Apple caps validity at 20 minutes; we intentionally use 18.
 */
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')
    .sign(privateKey);
 
  return jwt;
}

// src/fetchers/appstore-sales.ts
import { gunzipSync } from 'node:zlib';
 
export interface AppStoreSalesRow {
  appleId: string;
  title: string;
  units: number;
  developerProceeds: number;
  date: string;
}
 
export async function fetchAppStoreSales(
  jwt: string,
  vendorNumber: string,
  reportDate: string,
): 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', // required or you'll get 404
      },
    },
  );
 
  if (res.status === 404) {
    // Data for "today" or sometimes "yesterday" isn't finalized yet.
    return [];
  }
  if (\!res.ok) {
    throw new Error(`App Store Connect error: ${res.status} ${await res.text()}`);
  }
 
  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;
}
 
export async function fetchAdMobReport(
  serviceAccountKeyJson: string,
  accountId: string,
  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 is the value multiplied by 1,000,000
      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;
}
 
export async function fetchPlayStoreSales(
  serviceAccountKeyJson: string,
  bucketName: string, // e.g. "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),
    ]);
 
    if (appStore.status === 'fulfilled') {
      await env.REVENUE_KV.put(
        `revenue:${yesterday}:appstore`,
        JSON.stringify(appStore.value),
        { expirationTtl: 60 * 60 * 24 * 365 * 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} revenue summary*`,
    summary.appStore \!== null ? `App Store: $${summary.appStore.toFixed(2)}` : 'App Store: fetch failed',
    summary.adMob \!== null ? `AdMob: $${summary.adMob.toFixed(2)}` : 'AdMob: fetch failed',
  ];
  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 daily

// 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>Mobile App Revenue Dashboard</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>
  );
}

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: `Here are the last 7 days of app revenue. If yesterday looks unusual, say so in 1-2 sentences with a concrete action. No number dumps — just actionable insight.\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) {
    // Serialize to respect Apple's rate limits.
    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); // be polite
    } 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 }, // 30 days
);

async function loadFxRate(kv: KVNamespace, date: string, pair: string): Promise<number> {
  const data = await kv.get(`fx:${date}`, 'json') as Record<string, number> | null;
  // Fall back to the most recent rate if weekend/holiday
  if (data && data[pair]) return data[pair];
  for (let i = 1; i <= 5; i++) {
    const fallbackDate = new Date(new Date(date).getTime() - i * 86400_000).toISOString().slice(0, 10);
    const fallback = await kv.get(`fx:${fallbackDate}`, 'json') as Record<string, number> | null;
    if (fallback && fallback[pair]) return fallback[pair];
  }
  throw new Error(`No FX rate available near ${date} for ${pair}`);
}

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