API Services Integration Reference

This document provides technical specifications for integrating with each API service to fetch daily revenue and cost data.

1. Google Play Revenue

Authentication

Type: Service Account (OAuth 2.0 JWT grant)
Credentials needed:
- GCP_SERVICE_ACCOUNT_JSON - Full JSON content of service account key file
- GOOGLE_PLAY_DEVELOPER_ID - Your developer ID (from Play Console URL)

Setup Steps

Go to Google Cloud Console
Create/select a project
Enable APIs: Google Play Android Developer API, Cloud Storage API
Create Service Account → Download JSON key
In Play Console → Users & Permissions → Invite service account email
Grant permission: "View financial data, orders, and cancellation survey responses"
Wait 24 hours for propagation

Data Access

Method: Google Cloud Storage bucket
Bucket: gs://pubsite_prod_{DEVELOPER_ID}/
Files: sales/salesreport_YYYYMM.zip (monthly files, updated daily)
Format: UTF-8 CSV (zipped) - Note: docs say UTF-16 but actual files are UTF-8

Key Fields

Field	Use
`Order Charged Date`	Transaction date (YYYY-MM-DD)
`Charged Amount`	Revenue amount (includes tax)
`Currency of Sale`	Transaction currency
`Financial Status`	`Charged`, `Refund`, `Partial refund`
`Product Type`	`paidapp`, `subscription`, `inapp`

Revenue Formula

// Step 1: Calculate net revenue (gross minus refunds)
Net Revenue = SUM(Charged Amount) where status="Charged"
            - SUM(Charged Amount) where status IN ("Refund", "Partial refund")

// Step 2: Convert to USD (multi-currency transactions)
Net Revenue USD = SUM(Net Revenue per currency × FX rate to USD)

// Step 3: Calculate developer proceeds (what you actually receive)
Developer Proceeds = Net Revenue USD × 0.90283024 (remove ~10% tax) × 0.85 (remove 15% Google fee)

Note: "Charged Amount" includes tax. The 0.90283024 factor removes the average tax component, and 0.85 removes Google's 15% commission.

Implementation Notes

Uses RS256 JWT for service account auth
Files are UTF-8 (not UTF-16 as docs suggest) - check BOM to be safe
Data has 2-3 day delay
Historic data: ~1 year available
Run with: npx tsx fetch.ts YYYY-MM-DD

Sync Strategy

Data Accuracy Considerations:

Monthly ZIP files are updated daily with new transactions
2-3 day delay: today's transactions won't appear until 2-3 days later
Refunds/corrections can appear anytime within the month
Re-downloading the entire month guarantees we have latest data

Today's Sync:

async function todaySyncGooglePlay() {
  const today = new Date();

  // Always re-process current month (ZIP updated daily with new transactions)
  const currentMonthFile = `salesreport_${formatYYYYMM(today)}.zip`;
  await downloadAndProcessMonthlyZip(currentMonthFile);
  // → Upserts ALL days in current month (replaces previous values)

  // First 7 days of month: also re-process previous month
  // (late transactions from end of previous month may still arrive)
  if (today.getDate() <= 7) {
    const prevMonthFile = `salesreport_${formatYYYYMM(subtractMonths(today, 1))}.zip`;
    await downloadAndProcessMonthlyZip(prevMonthFile);
  }
}

async function downloadAndProcessMonthlyZip(filename) {
  const zipData = await fetchFromGCS(`sales/${filename}`);
  const csvContent = unzipAndDecode(zipData);  // UTF-8
  const transactions = parseCSV(csvContent);

  // Aggregate by date + app + currency
  const dailyTotals = new Map();  // key: "YYYY-MM-DD|bundleId"

  for (const tx of transactions) {
    const key = `${tx.orderChargedDate}|${tx.productId}`;
    const entry = dailyTotals.get(key) || { byCurrency: {} };

    const amount = tx.financialStatus === 'Charged'
      ? tx.chargedAmount
      : -tx.chargedAmount;  // Refund or Partial refund

    entry.byCurrency[tx.currency] = (entry.byCurrency[tx.currency] || 0) + amount;
    dailyTotals.set(key, entry);
  }

  // Convert to USD and upsert
  for (const [key, totals] of dailyTotals) {
    const [date, appId] = key.split('|');
    let amountUsd = 0;

    for (const [currency, amount] of Object.entries(totals.byCurrency)) {
      amountUsd += await convertToUsd(amount, currency, date);
    }

    // Calculate developer proceeds: net × 0.90283024 (remove tax) × 0.85 (15% fee)
    const proceeds = amountUsd * 0.90283024 * 0.85;

    await db.upsert('daily_metrics', {
      date, app_id: appId, platform: 'android',
      service: 'google_play', type: 'revenue',
      amount_usd: proceeds
    });
  }
}

Full History Sync:

async function fullHistorySyncGooglePlay(startDate: Date) {
  // List all available monthly files in GCS bucket
  const files = await listGCSFiles('sales/');
  // e.g., ["salesreport_202406.zip", ..., "salesreport_202601.zip"]

  // Filter files to only include months >= startDate
  const startYYYYMM = formatYYYYMM(startDate);
  const relevantFiles = files.filter(f => {
    const match = f.match(/salesreport_(\d{6})\.zip/);
    return match && match[1] >= startYYYYMM;
  });

  // Process each month (oldest to newest)
  for (const file of relevantFiles.sort()) {
    console.log(`Processing ${file}...`);
    await downloadAndProcessMonthlyZip(file);
    await sleep(500);  // Be polite to GCS
  }
}

// Usage: fullHistorySyncGooglePlay(new Date('2025-01-01'))

2. Apple App Store Revenue

Authentication

Type: JWT with ES256 algorithm
Credentials needed:
- APP_STORE_KEY_ID - Key ID from App Store Connect
- APP_STORE_ISSUER_ID - Issuer ID from App Store Connect
- APP_STORE_PRIVATE_KEY - Contents of .p8 file
- APP_STORE_VENDOR_NUMBER - Your vendor number

Setup Steps

Go to App Store Connect → Users and Access → Keys
Click "+" to generate API key
Download .p8 file (only available once!)
Note the Key ID and Issuer ID
Find Vendor Number in Sales & Trends

Data Access

Method: REST API
Endpoint: GET https://api.appstoreconnect.apple.com/v1/salesReports
Format: Gzip-compressed TSV
Rate Limit: 200 requests/minute

Request Parameters

filter[reportType]=SALES
filter[reportSubType]=SUMMARY
filter[frequency]=DAILY
filter[vendorNumber]={VENDOR_NUMBER}
filter[reportDate]={YYYY-MM-DD}

Key Fields

Field	Use
`Begin Date`	Transaction date (MM/DD/YYYY)
`Units`	Quantity (negative = refund)
`Developer Proceeds`	Your earnings per unit
`Currency of Proceeds`	Payout currency
`Product Type Identifier`	1/1T (paid), IA1 (subscription), IAY/IAC (IAP)

Revenue Formula

Daily Revenue = SUM(Units × Developer Proceeds)

Implementation Notes

JWT uses ES256 (ECDSA), not RS256
One API call per date (unlike Google's monthly files)
HTTP 404 = no sales that day (not an error)
Data available ~8 AM PST next day
Historic data: ~1 year available

Sync Strategy

Data Accuracy Considerations:

~24h delay: yesterday's data available after ~8 AM PST
One API call per date (no date ranges supported for daily granularity)
Data can update for up to 30 days (attribution adjustments, late refunds)
HTTP 404 means no sales that day (not an error - skip or store $0)

Today's Sync:

async function todaySyncAppleAppStore() {
  const today = new Date();

  // Fetch last 7 days to catch attribution updates and late adjustments
  // Day 0 (today) will likely 404, Day 1 (yesterday) should be available after 8 AM PST
  const datesToFetch = [];
  for (let daysAgo = 1; daysAgo <= 7; daysAgo++) {
    datesToFetch.push(subtractDays(today, daysAgo));
  }

  // Fetch all dates in parallel (rate limit is generous: 200/min)
  const results = await Promise.allSettled(
    datesToFetch.map(date => fetchAppleReport(date))
  );

  for (let i = 0; i < datesToFetch.length; i++) {
    const date = datesToFetch[i];
    const result = results[i];

    if (result.status === 'rejected') {
      if (result.reason?.status === 404) {
        // No sales that day - store $0 or skip
        continue;
      }
      throw result.reason;  // Real error
    }

    const tsvRows = result.value;
    await processAndUpsert(date, tsvRows);
  }
}

async function fetchAppleReport(date: Date) {
  const jwt = await createAppleJWT();
  const response = await fetch(
    `https://api.appstoreconnect.apple.com/v1/salesReports?` +
    `filter[reportType]=SALES&filter[reportSubType]=SUMMARY&` +
    `filter[frequency]=DAILY&filter[vendorNumber]=${VENDOR_NUMBER}&` +
    `filter[reportDate]=${formatYYYYMMDD(date)}`,
    { headers: { Authorization: `Bearer ${jwt}` } }
  );

  if (response.status === 404) {
    throw { status: 404 };  // No sales
  }

  const gzipped = await response.arrayBuffer();
  return parseTSV(gunzip(gzipped));
}

async function processAndUpsert(date: Date, rows: TSVRow[]) {
  // Aggregate by app + currency
  const appTotals = new Map();  // key: bundleId

  for (const row of rows) {
    const entry = appTotals.get(row.sku) || { byCurrency: {} };
    // Revenue = Units × Developer Proceeds (Units can be negative for refunds)
    const revenue = row.units * row.developerProceeds;
    entry.byCurrency[row.currencyOfProceeds] =
      (entry.byCurrency[row.currencyOfProceeds] || 0) + revenue;
    appTotals.set(row.sku, entry);
  }

  // Convert to USD and upsert
  for (const [appId, totals] of appTotals) {
    let amountUsd = 0;
    for (const [currency, amount] of Object.entries(totals.byCurrency)) {
      amountUsd += await convertToUsd(amount, currency, date);
    }

    await db.upsert('daily_metrics', {
      date: formatYYYYMMDD(date), app_id: appId, platform: 'ios',
      service: 'apple_appstore', type: 'revenue',
      amount_usd: amountUsd  // Already developer proceeds (Apple's cut removed)
    });
  }
}

Full History Sync:

async function fullHistorySyncAppleAppStore(startDate: Date) {
  const today = new Date();
  let date = startDate;

  while (date < today) {
    try {
      const tsvRows = await fetchAppleReport(date);
      await processAndUpsert(date, tsvRows);
      console.log(`✓ ${formatYYYYMMDD(date)}`);
    } catch (err) {
      if (err?.status === 404) {
        console.log(`- ${formatYYYYMMDD(date)} (no sales)`);
      } else {
        console.error(`✗ ${formatYYYYMMDD(date)}:`, err);
      }
    }

    date = addDays(date, 1);
    await sleep(100);  // Respect rate limits (200/min = ~3/sec max)
  }
}

// Usage: fullHistorySyncAppleAppStore(new Date('2025-01-01'))

3. AdMob Revenue

Authentication

Type: OAuth 2.0 with user consent (NO service accounts!)
Credentials needed:
- ADMOB_CLIENT_ID - OAuth client ID
- ADMOB_CLIENT_SECRET - OAuth client secret
- ADMOB_REFRESH_TOKEN - Obtained via one-time OAuth flow
- ADMOB_PUBLISHER_ID - Your publisher ID (pub-XXXX)

Setup Steps

Go to Google Cloud Console
Enable AdMob API
Create OAuth 2.0 Client ID (Web app type)
- Add redirect URI: https://developers.google.com/oauthplayground
Configure OAuth consent screen (External)
- Add your AdMob email as test user
- IMPORTANT: Click "Publish App" so refresh token doesn't expire in 7 days
Use OAuth Playground to get refresh token:
- Check "Use your own OAuth credentials"
- Scope: https://www.googleapis.com/auth/admob.report
- Exchange for tokens, save the refresh_token
Get Publisher ID from AdMob console > Settings > Account Information

Data Access

Method: REST API
Endpoint: POST https://admob.googleapis.com/v1/accounts/{PUBLISHER_ID}/mediationReport:generate
CRITICAL: Use mediationReport, NOT networkReport! (networkReport misses ~65% of revenue from mediation partners)
Format: JSON array
Rate Limit: 900 requests/minute

Request Body

{
  "report_spec": {
    "date_range": {
      "start_date": {"year": 2026, "month": 1, "day": 1},
      "end_date": {"year": 2026, "month": 1, "day": 7}
    },
    "dimensions": ["DATE", "APP"],
    "metrics": ["ESTIMATED_EARNINGS"],
    "localization_settings": {"currency_code": "USD"}
  }
}

Key Fields

Field	Use
`DATE` dimension	Daily breakdown (YYYYMMDD format)
`APP` dimension	App identifier
`ESTIMATED_EARNINGS`	Revenue in micros

Revenue Formula

Daily Revenue = ESTIMATED_EARNINGS.microsValue / 1,000,000

Implementation Notes

Supports date ranges in single call (efficient!)
Values in micros (divide by 1,000,000)
Data finalizes at month-end (daily values may fluctuate)
Historic data: 7 years available
Currency: API converts to USD when currency_code: 'USD' is set
App IDs: Format is ca-app-pub-XXXXX~YYYYY, need to map to bundle IDs
NO service accounts! Must use OAuth refresh token flow

Sync Strategy

Data Accuracy Considerations:

~24h delay for data availability
CRITICAL: Daily values fluctuate until month-end finalization
Current month's data is "estimated earnings" - will change
Previous months' data is stable and finalized
Supports date ranges in single API call (very efficient!)
Must use mediationReport (not networkReport) for complete revenue

Today's Sync:

async function todaySyncAdMob() {
  const today = new Date();

  // Re-fetch entire current month (values fluctuate until month-end)
  // Plus previous month if we're in first 3 days (finalization lag)
  const startDate = today.getDate() <= 3
    ? startOfMonth(subtractMonths(today, 1))
    : startOfMonth(today);
  const endDate = subtractDays(today, 1);  // Yesterday (today not ready)

  const rows = await fetchAdMobReport(startDate, endDate);
  await processAndUpsert(rows);
}

async function fetchAdMobReport(startDate: Date, endDate: Date) {
  const accessToken = await refreshGoogleToken(ADMOB_REFRESH_TOKEN);

  const response = await fetch(
    `https://admob.googleapis.com/v1/accounts/${PUBLISHER_ID}/mediationReport:generate`,
    {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${accessToken}`,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({
        report_spec: {
          date_range: {
            start_date: { year: startDate.getFullYear(), month: startDate.getMonth() + 1, day: startDate.getDate() },
            end_date: { year: endDate.getFullYear(), month: endDate.getMonth() + 1, day: endDate.getDate() },
          },
          dimensions: ['DATE', 'APP'],
          metrics: ['ESTIMATED_EARNINGS'],
          localization_settings: { currency_code: 'USD' },  // API converts to USD
        },
      }),
    }
  );

  // Response is gzip-compressed newline-delimited JSON
  const data = await parseAdMobResponse(response);
  return data;
}

async function processAndUpsert(rows: AdMobRow[]) {
  // AdMob uses its own app IDs (ca-app-pub-XXX~YYY), need mapping
  const APP_ID_MAP = {
    'ca-app-pub-123~456': { bundleId: 'com.example.app', platform: 'android' },
    'ca-app-pub-123~789': { bundleId: 'com.example.app', platform: 'ios' },
    // ... populate from AdMob console
  };

  for (const row of rows) {
    const dateStr = row.dimensionValues.DATE.value;  // "YYYYMMDD"
    const admobAppId = row.dimensionValues.APP.value;
    const earningsMicros = row.metricValues.ESTIMATED_EARNINGS.microsValue;

    const appMapping = APP_ID_MAP[admobAppId];
    if (!appMapping) {
      console.warn(`Unknown AdMob app ID: ${admobAppId}`);
      continue;
    }

    const date = `${dateStr.slice(0,4)}-${dateStr.slice(4,6)}-${dateStr.slice(6,8)}`;
    const amountUsd = parseInt(earningsMicros) / 1_000_000;

    await db.upsert('daily_metrics', {
      date,
      app_id: appMapping.bundleId,
      platform: appMapping.platform,
      service: 'admob',
      type: 'revenue',
      amount_usd: amountUsd,  // Already in USD (API converted)
    });
  }
}

Full History Sync:

async function fullHistorySyncAdMob(startDate: Date) {
  const today = new Date();

  // AdMob supports large date ranges - can fetch years in chunks
  // Chunk by month for manageable response sizes
  let chunkStart = startDate;

  while (chunkStart < today) {
    const chunkEnd = min(endOfMonth(chunkStart), subtractDays(today, 1));

    console.log(`Fetching ${formatYYYYMMDD(chunkStart)} to ${formatYYYYMMDD(chunkEnd)}...`);
    const rows = await fetchAdMobReport(chunkStart, chunkEnd);
    await processAndUpsert(rows);

    chunkStart = addDays(endOfMonth(chunkStart), 1);  // Next month
    await sleep(500);  // Be polite
  }
}

// Usage: fullHistorySyncAdMob(new Date('2024-01-01'))

4. Google Ads Cost

Authentication

Type: OAuth 2.0 + Developer Token
Credentials needed:
- GOOGLE_ADS_CLIENT_ID - OAuth client ID
- GOOGLE_ADS_CLIENT_SECRET - OAuth client secret
- GOOGLE_ADS_REFRESH_TOKEN - Obtained via OAuth flow
- GOOGLE_ADS_DEVELOPER_TOKEN - From Google Ads API Center
- GOOGLE_ADS_CUSTOMER_ID - Account ID (without dashes)

Setup Steps

Create Google Ads Manager Account (if not exists)
Go to Tools & Settings → API Center → Get Developer Token
Enable Google Ads API in Google Cloud Console
Create OAuth credentials (same as AdMob can be reused)
Run OAuth flow with scope: https://www.googleapis.com/auth/adwords
Explorer Access is automatically granted and sufficient for daily cost reporting

Data Access

Method: REST API with GAQL queries
Endpoint: POST https://googleads.googleapis.com/v18/customers/{CUSTOMER_ID}/googleAds:search
Format: JSON
Headers: Requires developer-token header

GAQL Query

SELECT
  campaign.app_campaign_setting.app_id,
  campaign.app_campaign_setting.app_store,
  segments.date,
  metrics.cost_micros
FROM campaign
WHERE campaign.advertising_channel_type = 'MULTI_CHANNEL'
  AND campaign.app_campaign_setting.app_id IS NOT NULL
  AND segments.date >= '2026-01-01'
  AND segments.date <= '2026-01-07'

Key Fields

Field	Use
`segments.date`	Daily breakdown
`campaign.app_campaign_setting.app_id`	App package/bundle ID
`campaign.app_campaign_setting.app_store`	GOOGLE_APP_STORE or APPLE_APP_STORE
`metrics.cost_micros`	Cost in micros

Cost Formula

// Step 1: Convert from micros to currency units
Cost in Account Currency = metrics.cost_micros / 1,000,000

// Step 2: Convert to USD (account currency is EUR in our case)
Daily Cost USD = Cost in Account Currency × FX rate to USD

Note: Google Ads returns cost in your account's billing currency, not USD. Check your account settings to determine which currency you're billed in.

Implementation Notes

Use advertising_channel_type = 'MULTI_CHANNEL' for app campaigns
Values in micros (divide by 1,000,000)
Developer token required in header
Historic data: 11 years available
Currency: Returns cost in account's billing currency (EUR for us) - needs FX conversion to USD

Sync Strategy

Data Accuracy Considerations:

Near real-time data availability (within hours)
Cost data is stable - no significant retroactive changes
Returns cost in account's currency (EUR in our case) - needs FX conversion
Supports date ranges via GAQL queries (efficient)
Filter by MULTI_CHANNEL to get only app campaigns

Today's Sync:

async function todaySyncGoogleAds() {
  const today = new Date();

  // Fetch last 3 days (cost data is stable, minimal lookback needed)
  const startDate = subtractDays(today, 3);
  const endDate = subtractDays(today, 1);  // Yesterday

  const rows = await fetchGoogleAdsReport(startDate, endDate);
  await processAndUpsert(rows);
}

async function fetchGoogleAdsReport(startDate: Date, endDate: Date) {
  const accessToken = await refreshGoogleToken(GOOGLE_ADS_REFRESH_TOKEN);

  const query = `
    SELECT
      campaign.app_campaign_setting.app_id,
      campaign.app_campaign_setting.app_store,
      segments.date,
      metrics.cost_micros
    FROM campaign
    WHERE campaign.advertising_channel_type = 'MULTI_CHANNEL'
      AND campaign.app_campaign_setting.app_id IS NOT NULL
      AND segments.date >= '${formatYYYYMMDD(startDate)}'
      AND segments.date <= '${formatYYYYMMDD(endDate)}'
  `;

  const response = await fetch(
    `https://googleads.googleapis.com/v18/customers/${CUSTOMER_ID}/googleAds:search`,
    {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${accessToken}`,
        'developer-token': DEVELOPER_TOKEN,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({ query }),
    }
  );

  const data = await response.json();
  return data.results || [];
}

async function processAndUpsert(rows: GoogleAdsRow[]) {
  // Aggregate by date + app (multiple campaigns per app possible)
  const dailyTotals = new Map();  // key: "YYYY-MM-DD|bundleId|platform"

  for (const row of rows) {
    const date = row.segments.date;
    const appId = row.campaign.appCampaignSetting.appId;
    const appStore = row.campaign.appCampaignSetting.appStore;
    const platform = appStore === 'GOOGLE_APP_STORE' ? 'android' : 'ios';
    const costMicros = parseInt(row.metrics.costMicros || '0');

    const key = `${date}|${appId}|${platform}`;
    dailyTotals.set(key, (dailyTotals.get(key) || 0) + costMicros);
  }

  // Convert to USD and upsert
  // Note: Google Ads returns cost in account currency (check your account settings)
  const ACCOUNT_CURRENCY = 'EUR';  // Set based on your Google Ads account

  for (const [key, costMicros] of dailyTotals) {
    const [date, appId, platform] = key.split('|');
    const costInAccountCurrency = costMicros / 1_000_000;
    const costUsd = await convertToUsd(costInAccountCurrency, ACCOUNT_CURRENCY, date);

    await db.upsert('daily_metrics', {
      date,
      app_id: appId,  // Already bundle ID (e.g., "com.bonc.paintmyroom")
      platform,
      service: 'google_ads',
      type: 'cost',
      amount_usd: costUsd,
    });
  }
}

Full History Sync:

async function fullHistorySyncGoogleAds(startDate: Date) {
  const today = new Date();

  // Google Ads supports large date ranges - chunk by month for safety
  let chunkStart = startDate;

  while (chunkStart < today) {
    const chunkEnd = min(endOfMonth(chunkStart), subtractDays(today, 1));

    console.log(`Fetching ${formatYYYYMMDD(chunkStart)} to ${formatYYYYMMDD(chunkEnd)}...`);
    const rows = await fetchGoogleAdsReport(chunkStart, chunkEnd);
    await processAndUpsert(rows);

    chunkStart = addDays(endOfMonth(chunkStart), 1);  // Next month
    await sleep(500);
  }
}

// Usage: fullHistorySyncGoogleAds(new Date('2024-01-01'))

5. Apple Search Ads Advanced Cost

Authentication

Type: OAuth 2.0 with JWT client secret (ES256)
Credentials needed:
- APPLE_ADS_CLIENT_ID - From Apple Ads (SEARCHADS.xxxx format)
- APPLE_ADS_TEAM_ID - From Apple Ads (SEARCHADS.xxxx format)
- APPLE_ADS_KEY_ID - From Apple Ads
- APPLE_ADS_PRIVATE_KEY - Your private key (PEM format)
- APPLE_ADS_ORG_ID - Organization ID

Setup Steps

Sign in to Apple Ads account
Go to Settings → User Management → Create API user

Generate private key locally:

openssl ecparam -genkey -name prime256v1 -noout -out private-key.pem
openssl ec -in private-key.pem -pubout -out public-key.pem

Upload public key to Apple Ads → Settings → API
Copy clientId, teamId, keyId
Get orgId via GET /api/v5/acls

Data Access

Method: REST API
Endpoint: POST https://api.searchads.apple.com/api/v5/reports/campaigns
Format: JSON
Required Headers:
- Authorization: Bearer {ACCESS_TOKEN}
- X-AP-Context: orgId={ORG_ID} ← 403 without this!

Request Body

{
  "startTime": "2026-01-01",
  "endTime": "2026-01-07",
  "granularity": "DAILY",
  "timeZone": "UTC",
  "selector": {
    "orderBy": [{"field": "localSpend", "sortOrder": "DESCENDING"}],
    "pagination": {"offset": 0, "limit": 1000}
  }
}

Note: selector.orderBy is REQUIRED - API returns 400 without it!

Key Fields

Field	Use
`granularity[].date`	Daily breakdown
`metadata.app.adamId`	App Store numeric ID (NOT bundle ID!)
`granularity[].localSpend.amount`	Cost as string (not micros!)
`granularity[].localSpend.currency`	Currency code (need FX conversion)

Cost Formula

// Step 1: Parse the string amount (not micros!)
Cost in Local Currency = parseFloat(localSpend.amount)

// Step 2: Convert to USD using localSpend.currency
Daily Cost USD = Cost in Local Currency × FX rate to USD

Note: Unlike Google APIs, Apple returns amounts as strings (not micros). Currency varies by campaign.

Implementation Notes

ES256 signature: Node crypto outputs DER format, Apple expects raw R||S (64 bytes) - must convert
JWT client secret can last up to 180 days
Access tokens expire after 1 hour
DAILY granularity limited to 90 days in past
Data delay: ~3 hours after events
Attribution may update for up to 30 days

Sync Strategy

Data Accuracy Considerations:

~3 hour delay for data availability
Attribution updates for 30 days: conversions attributed later can change cost allocation
DAILY granularity limited to 90 days in the past (use MONTHLY for older data)
Cost values are STRINGS (not micros) - need parseFloat()
Currency varies by campaign - needs FX conversion
Uses adamId (numeric App Store ID), not bundle ID - need mapping

Today's Sync:

async function todaySyncAppleSearchAdsAdvanced() {
  const today = new Date();

  // Fetch last 30 days due to attribution window
  // Attribution changes can shift cost between days for up to 30 days
  const startDate = subtractDays(today, 30);
  const endDate = subtractDays(today, 1);  // Yesterday (today may be incomplete)

  const campaigns = await fetchAppleSearchAdsReport(startDate, endDate);
  await processAndUpsert(campaigns);
}

async function fetchAppleSearchAdsReport(startDate: Date, endDate: Date) {
  const accessToken = await getAppleAdsAccessToken();

  const response = await fetch(
    'https://api.searchads.apple.com/api/v5/reports/campaigns',
    {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${accessToken}`,
        'X-AP-Context': `orgId=${ORG_ID}`,  // Required! 403 without it
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({
        startTime: formatYYYYMMDD(startDate),
        endTime: formatYYYYMMDD(endDate),
        granularity: 'DAILY',
        timeZone: 'UTC',
        selector: {
          orderBy: [{ field: 'localSpend', sortOrder: 'DESCENDING' }],  // Required!
          pagination: { offset: 0, limit: 1000 },
        },
      }),
    }
  );

  const data = await response.json();
  return data.data?.reportingDataResponse?.row || [];
}

async function processAndUpsert(campaigns: AppleSearchAdsRow[]) {
  // adamId to bundleId mapping (get from App Store Connect)
  const ADAM_ID_MAP = {
    '1583486842': 'com.bonc.paintmyroom',
    // ... add other apps
  };

  for (const campaign of campaigns) {
    const adamId = campaign.metadata?.app?.adamId;
    const bundleId = ADAM_ID_MAP[adamId];

    if (!bundleId) {
      console.warn(`Unknown adamId: ${adamId}`);
      continue;
    }

    // Each campaign has granularity array with daily breakdown
    for (const day of campaign.granularity || []) {
      const date = day.date;  // "YYYY-MM-DD"
      const costAmount = parseFloat(day.localSpend?.amount || '0');  // STRING!
      const currency = day.localSpend?.currency || 'USD';

      const costUsd = await convertToUsd(costAmount, currency, date);

      // Aggregate at date+app level (multiple campaigns per app)
      await db.upsert('daily_metrics', {
        date,
        app_id: bundleId,
        platform: 'ios',
        service: 'apple_search_ads_advanced',
        type: 'cost',
        amount_usd: costUsd,
      }, {
        // On conflict, ADD to existing value (multiple campaigns per app)
        onConflict: 'amount_usd = amount_usd + EXCLUDED.amount_usd'
      });
    }
  }
}

Full History Sync:

async function fullHistorySyncAppleSearchAdsAdvanced(startDate: Date) {
  const today = new Date();

  // DAILY granularity limited to 90 days - chunk accordingly
  // For older data, would need MONTHLY granularity (less precise)
  const maxLookback = subtractDays(today, 90);
  const effectiveStart = max(startDate, maxLookback);

  if (startDate < maxLookback) {
    console.warn(`Can only sync DAILY data from ${formatYYYYMMDD(maxLookback)}`);
    console.warn(`For older data, use MONTHLY granularity (not implemented here)`);
  }

  // Chunk by 30 days for manageable response sizes
  let chunkStart = effectiveStart;

  while (chunkStart < today) {
    const chunkEnd = min(addDays(chunkStart, 29), subtractDays(today, 1));

    console.log(`Fetching ${formatYYYYMMDD(chunkStart)} to ${formatYYYYMMDD(chunkEnd)}...`);
    const campaigns = await fetchAppleSearchAdsReport(chunkStart, chunkEnd);
    await processAndUpsert(campaigns);

    chunkStart = addDays(chunkEnd, 1);
    await sleep(500);
  }
}

// Usage: fullHistorySyncAppleSearchAdsAdvanced(new Date('2025-10-01'))
// Note: Can only go back 90 days with DAILY granularity

6. Apple Search Ads Basic Cost

⚠️ No API Available

Apple Ads Basic has NO Campaign Management API. Cost data must be manually downloaded as CSV from the Apple Ads dashboard.

Manual Export Steps

Sign in to Apple Ads Basic dashboard
Go to your app promotion
Click download/export button
Select date range
Save CSV file to data/ folder

Data Access

Method: Local CSV file parsing
Source: Manual download from Apple Ads Basic dashboard
Format: CSV with header metadata + data rows

CSV Format

Header section (first lines):

"Start Date: Dec 01, 2025"
"End Date: Dec 31, 2025"
Currency: EUR
Time Zone: UTC

Data columnsApple Search Ads Basic:

Column	Use
`App ID`	App Store numeric ID (adamId)
`App Name`	App display name
`Country or Region`	Market breakdown
`Billing Entity`	Apple billing entity
`Spend`	Cost amount for the entire date range
`Average CPA`	Average cost per acquisition
`Installs`	Number of installs

Cost Formula

// CSV contains AGGREGATED totals, not daily breakdown
Daily Cost = SUM(Spend for app) / daysInRange

Implementation Notes

No authentication needed (local files)
Must manually download CSV periodically
AGGREGATED DATA: CSV contains totals for entire date range, not daily values
Script calculates average daily cost by dividing total by days in range
Currency specified in CSV header (EUR for our account)
FX conversion uses middle day of date range (approximation)
Filter by App ID (numeric adamId), not bundle ID
Historical data available as far back as exports allow

Sync Strategy

Data Accuracy Considerations:

NO API - manual CSV download from Apple Ads Basic dashboard required
CSV contains AGGREGATED totals for date range, NOT daily breakdown
Must calculate daily average: totalSpend / daysInRange
This is an approximation - actual daily spend unknown
Currency specified in CSV header (EUR in our case)
Uses adamId (numeric App Store ID), not bundle ID - need mapping

Today's Sync (Manual Process):

// Apple Search Ads Basic requires manual intervention:
// 1. Download CSV from Apple Ads Basic dashboard
// 2. Upload via API endpoint or place in watched folder
// 3. Process and store

// Option A: HTTP endpoint for CSV upload
async function handleBasicAdsUpload(request: Request) {
  const formData = await request.formData();
  const file = formData.get('csv') as File;
  const csvContent = await file.text();

  await processBasicAdsCsv(csvContent);
  return new Response('OK');
}

// Option B: Watch folder and process new files
async function processNewBasicAdsFiles() {
  const files = await listFiles('uploads/apple-search-basic/');
  for (const file of files) {
    if (!file.processed) {
      const content = await readFile(file.path);
      await processBasicAdsCsv(content);
      await markAsProcessed(file);
    }
  }
}

async function processBasicAdsCsv(csvContent: string) {
  // Parse header metadata
  // "Start Date: Dec 01, 2025"
  // "End Date: Dec 31, 2025"
  // Currency: EUR
  const { startDate, endDate, currency } = parseHeader(csvContent);
  const daysInRange = daysBetween(startDate, endDate) + 1;

  // Parse data rows
  const rows = parseCSVData(csvContent);

  // adamId to bundleId mapping
  const ADAM_ID_MAP = {
    '1583486842': 'com.bonc.paintmyroom',
    // ... add other apps
  };

  // Aggregate spend by app (may have multiple country rows per app)
  const appTotals = new Map();  // adamId -> totalSpend
  for (const row of rows) {
    const current = appTotals.get(row.appId) || 0;
    appTotals.set(row.appId, current + parseFloat(row.spend));
  }

  // Calculate daily average and upsert each day in range
  const middleDate = addDays(startDate, Math.floor(daysInRange / 2));
  const fxRate = await getFxRate(currency, 'USD', middleDate);

  for (const [adamId, totalSpend] of appTotals) {
    const bundleId = ADAM_ID_MAP[adamId];
    if (!bundleId) {
      console.warn(`Unknown adamId: ${adamId}`);
      continue;
    }

    const dailySpend = totalSpend / daysInRange;
    const dailySpendUsd = dailySpend * fxRate;

    // Upsert for each day in the range
    let date = startDate;
    while (date <= endDate) {
      await db.upsert('daily_metrics', {
        date: formatYYYYMMDD(date),
        app_id: bundleId,
        platform: 'ios',
        service: 'apple_search_ads_basic',
        type: 'cost',
        amount_usd: dailySpendUsd,
      });
      date = addDays(date, 1);
    }
  }
}

Full History Sync (Manual):

// Full history sync requires downloading multiple CSVs from Apple Ads Basic dashboard
// Each CSV covers a date range - download monthly exports

async function fullHistorySyncBasicAds(startDate: Date) {
  // 1. Manually download CSVs from Apple Ads Basic for each month from startDate:
  //    - Jan 2025: apple_basic_2025_01.csv
  //    - Feb 2025: apple_basic_2025_02.csv
  //    - etc.

  // 2. Process each file (filter by startDate if needed)
  const files = [
    'apple_basic_2025_01.csv',
    'apple_basic_2025_02.csv',
    // ... add all historical exports from startDate onwards
  ];

  for (const filename of files) {
    console.log(`Processing ${filename}...`);
    const content = await readFile(`uploads/${filename}`);
    await processBasicAdsCsv(content);
  }
}

// Usage: fullHistorySyncBasicAds(new Date('2025-01-01'))

// IMPORTANT: Data quality limitation
// - Daily values are AVERAGES, not actual daily spend
// - If you need precise daily data, Apple Search Ads Basic cannot provide it
// - Consider migrating to Apple Search Ads Advanced for API access

Recommended Workflow:

Weekly manual process:
1. Log into Apple Ads Basic dashboard
2. Download CSV for past week (or since last download)
3. Upload to finboard via /api/upload-basic-ads endpoint
4. System processes and stores daily averages

Alternative: Set calendar reminder for weekly CSV download

Common Patterns

OAuth Token Refresh (Google APIs)

async function refreshAccessToken(clientId, clientSecret, refreshToken) {
  const response = await fetch('https://oauth2.googleapis.com/token', {
    method: 'POST',
    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
    body: new URLSearchParams({
      client_id: clientId,
      client_secret: clientSecret,
      refresh_token: refreshToken,
      grant_type: 'refresh_token',
    }),
  });
  const data = await response.json();
  return data.access_token;
}

JWT Creation (ES256 for Apple)

// Use 'jose' library for ES256 signing
import * as jose from 'jose';

async function createAppleJWT(privateKey, keyId, issuerId) {
  const key = await jose.importPKCS8(privateKey, 'ES256');
  return await new jose.SignJWT({})
    .setProtectedHeader({ alg: 'ES256', kid: keyId, typ: 'JWT' })
    .setIssuer(issuerId)
    .setIssuedAt()
    .setExpirationTime('20m')
    .setAudience('appstoreconnect-v1')
    .sign(key);
}