App revenue SQL examples for BigQuery and Snowflake

Query Daily App Sales and Platform Finance as separate datasets. Daily totals use estimated_net; finance totals use signed report_net_proceeds across the relevant event set. Group amounts by their currency and carry quality counts alongside totals.

Replace the project, dataset and database names in these queries with your destination identifiers. The columns match the Daily App Sales schema and Platform Finance schema.

Daily estimates by store and date

BigQuery Standard SQL:

SELECT revenue_date, source, report_currency,
       SUM(estimated_net) AS estimated_net_total,
       COUNT(*) AS row_count,
       COUNTIF(net_method LIKE '%ASSUMED_ZERO%') AS assumed_zero_net_rows
FROM `example-project.analytics.app_sales_daily`
WHERE revenue_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31'
GROUP BY revenue_date, source, report_currency
ORDER BY revenue_date, source;

The total remains an operational estimate. The method count highlights one kind of fallback; zero flagged rows does not prove that all other amounts are source-observed or that all reports have arrived.

Finance proceeds with missing-value coverage

SELECT fiscal_period, platform, report_currency,
       SUM(report_net_proceeds) AS available_report_proceeds,
       COUNT(*) AS event_count,
       COUNTIF(report_net_proceeds IS NULL) AS missing_proceeds_events
FROM `example-project.analytics.finance_unified`
WHERE fiscal_period = DATE '2026-08-01'
GROUP BY fiscal_period, platform, report_currency;

Do not add a sale-only filter to compute net proceeds: Google fee, tax and adjustment events contribute to the ledger. SUM ignores nulls; the accompanying count prevents an incomplete sum from looking complete. Do not multiply finance proceeds by quantity.

Inspect the event mix

SELECT source_report, event_type, native_currency,
       COUNT(*) AS event_count,
       SUM(native_net_proceeds) AS available_native_proceeds
FROM `example-project.analytics.finance_unified`
WHERE fiscal_period = DATE '2026-08-01'
GROUP BY source_report, event_type, native_currency
ORDER BY source_report, event_type, native_currency;

Native totals stay separated by currency. Apple Finance does not invent separate tax or commission events just because gross and proceeds differ; Google Earnings can disclose separate events. The event mix need not look the same for both stores.

Apple daily proceeds in their own currency

SELECT revenue_date, apple_proceeds_currency,
       SUM(apple_proceeds_native) AS available_apple_proceeds,
       COUNTIF(apple_proceeds_native IS NULL) AS missing_proceeds_rows
FROM `example-project.analytics.app_sales_daily`
WHERE source = 'APPLE_APP_STORE'
  AND revenue_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31'
GROUP BY revenue_date, apple_proceeds_currency;

Use the explicit proceeds-currency fields. Grouping this measure by the row’s native_currency instead can mislabel Apple’s proceeds when customer and developer currencies differ. If source proceeds are unavailable, the daily component can use an analytical fallback; inspect net_method before treating every amount as source-disclosed.

Find which daily methods drive a total

SELECT source, net_method, fee_method, tax_method, data_completeness,
       report_currency, COUNT(*) AS row_count,
       SUM(estimated_net) AS estimated_net_total
FROM `example-project.analytics.app_sales_daily`
WHERE revenue_date = DATE '2026-08-15'
GROUP BY source, net_method, fee_method, tax_method,
         data_completeness, report_currency;

Review unexpected method combinations before treating a numeric field as a directly reported component. See missing amounts and estimates for the daily-versus-finance difference.

The Snowflake equivalent

Use quoted lowercase contract columns. Snowflake’s aggregate conditional count is COUNT_IF:

SELECT "fiscal_period", "platform", "report_currency",
       SUM("report_net_proceeds") AS available_report_proceeds,
       COUNT(*) AS event_count,
       COUNT_IF("report_net_proceeds" IS NULL) AS missing_proceeds_events
FROM EXAMPLE_DB.ANALYTICS.FINANCE_UNIFIED
WHERE "fiscal_period" = TO_DATE('2026-08-01')
GROUP BY "fiscal_period", "platform", "report_currency";

Choose fiscal or calendar attribution deliberately

These finance examples group by the source reporting period. For a calendar-attribution model, inspect calendar_period, effective_revenue_date and effective_revenue_date_basis. A fallback date is not automatically the original transaction date. Document that modeling decision before comparing with a calendar-month daily-sales sum.