Getting to the Real Revenue Number — A Pipeline that Reconciles AdMob, App Store, Google Play, and Stripe

Ingest daily — idempotency above all

Each source's data is ingested at a fixed time every day. What matters is an idempotent design where ingesting the same day repeatedly yields the same result. Network retries and delayed updates on the source side happen routinely, so build for overwrite.

type RevenueEvent = {
  appId: string; source: string; kind: string;
  occurredOn: string; amountMinor: number;
  currency: string; status: 'estimated' | 'finalized'; fxToJpy: number | null;
};
 
// UPSERT by primary key. The same (app, source, kind, day, status) is always overwritten with the latest
async function upsertEvents(db: D1Database, events: RevenueEvent[]) {
  const stmt = db.prepare(`
    INSERT INTO revenue_events
      (app_id, source, kind, occurred_on, amount_minor, currency, status, fx_to_jpy)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(app_id, source, kind, occurred_on, status)
    DO UPDATE SET amount_minor = excluded.amount_minor,
                  fx_to_jpy   = excluded.fx_to_jpy
  `);
  const batch = events.map(e => stmt.bind(
    e.appId, e.source, e.kind, e.occurredOn,
    e.amountMinor, e.currency, e.status, e.fxToJpy,
  ));
  await db.batch(batch);
}

I run this once each morning on a Cloudflare Workers scheduled trigger and accumulate the results in D1. Rather than replacing the estimated rows with finalized ones the next month, I let them coexist as a separate status. That lets me verify after the fact how far last month's estimate diverged from finalized, and use it to correct next month's estimate.

I pull the yen-converted daily total like this.

SELECT app_id, source,
       SUM(amount_minor * COALESCE(fx_to_jpy, 1.0)) / 100.0 AS jpy_approx
FROM revenue_events
WHERE status = 'estimated' AND occurred_on >= date('now', '-30 day')
GROUP BY app_id, source
ORDER BY jpy_approx DESC;

Monthly balancing — do not over-trust the dashboard

Daily estimates are plenty for watching trends. But a number you use for business decisions only earns trust once you reconcile it monthly against finalized figures. What I always do at the start of the month is line up last month's estimated totals against the finalized totals from each source's financial report and look at the difference as a percentage.

If the gap sits within the expected range (2–6% for AdMob, a roughly fixed ratio net of store fees), it is healthy. Outside the range, there is a missed pickup or a double count somewhere. This reconciliation once surfaced an ingestion bug where one app's Stripe refunds were being deducted twice. Watching only the daily dashboard, I would never have caught it.

I set investment priorities from this finalized-based number. For example, "which app gets the next feature" is decided not by descending estimated revenue, but by descending finalized profit (revenue minus fees and ad cost). Even a few percent of divergence between estimate and finalized routinely reshuffles the ranking of the six.

Build a way to notice the gaps

Leave reconciliation as a manual monthly task and you will skip it in a busy month. I added a simple health check at the end of the daily ingest. It alerts when it detects an anomaly — a source's amount dropping to zero versus the prior day, or falling below half of the same weekday last week.

function anomalies(today: number, lastWeekSameDay: number): string | null {
  if (today === 0 && lastWeekSameDay > 0) return 'source is zero (likely ingest gap)';
  if (lastWeekSameDay > 0 && today < lastWeekSameDay * 0.5) {
    return `dropped to ${Math.round((today / lastWeekSameDay) * 100)}% week over week`;
  }
  return null;
}

It is not fancy. But this plain check has saved me several times from the worst pattern — a source's ingestion having quietly stopped, only to notice two weeks of missing data. The earlier and smaller you catch an anomaly, the easier it is to isolate the cause.

Where to start

Trying to integrate four sources at once will defeat you. I recommend accumulating just the largest source first (for most indie developers that is AdMob), with estimated and finalized kept separate, for one month. Simply learning what the estimate-to-finalized gap is in percent for your own business changes how you relate to the dashboard number.

I accumulated only AdMob for two months to get a feel for the gap, then added the remaining sources. The real revenue number does not appear on any single screen. It only takes shape once you reconcile multiple sources. I hope this gives a starting point to anyone juggling several revenue streams of their own.