App revenue SQL examples for BigQuery

Which Netice BigQuery table should you query?

Netice app revenue data can appear in more than one BigQuery table because not all app-store revenue questions use the same source data. Daily App Sales and Finance Unified are intentionally separate outputs.

Daily App Sales uses the default BigQuery table app_sales_daily. It is built for operational analytics over Apple App Store and Google Play daily sales-style reporting. Apple daily rows come from Sales and Trends / Summary Sales style sources. Google daily rows come from Estimated Sales and subscription lifecycle style sources.

Finance Unified, where configured, uses the default BigQuery table finance_unified. It is a monthly platform finance output. Apple finance rows come from Apple financeReports. Google finance rows come from Google Play Earnings. It is not the daily app-sales table and should not be queried as if it were.

Daily App Sales field map

The daily table is designed around revenue_date, source context, report-currency estimates, native/local values, method fields, data completeness, lifecycle flags and provenance. Daily App Sales uses data_completeness for source-readiness context rather than is_final. Do not treat daily readiness as settlement finality.

Example 1: daily revenue trend by platform

This query is the standard starting point for daily operational analytics. It groups revenue by day, platform and report currency. It uses estimated_net because the table is a daily app-sales analytics table.

SELECT
  revenue_date,
  platform,
  report_currency,
  SUM(estimated_net) AS estimated_net_revenue,
  SUM(units) AS units
FROM `example_project.example_dataset.app_sales_daily`
WHERE revenue_date BETWEEN DATE '2026-04-01' AND DATE '2026-04-30'
  AND event_has_revenue = TRUE
GROUP BY revenue_date, platform, report_currency
ORDER BY revenue_date, platform, report_currency;

This is not payout reconciliation. It is a daily trend query for operational reporting.

Example 2: source-report breakdown

Source-report splits help analysts see which report families contributed rows. This is important because Apple Summary Sales, Apple subscription events, Google Play Estimated Sales and Google subscription lifecycle rows have different semantics.

SELECT
  source,
  platform,
  source_report,
  report_currency,
  COUNT(*) AS row_count,
  SUM(CASE WHEN event_has_revenue THEN estimated_net ELSE 0 END) AS estimated_net_revenue
FROM `example_project.example_dataset.app_sales_daily`
WHERE revenue_date BETWEEN DATE '2026-04-01' AND DATE '2026-04-30'
GROUP BY source, platform, source_report, report_currency
ORDER BY source, platform, source_report, report_currency;

Example 3: revenue-impacting rows vs lifecycle-only rows

Subscription events can describe lifecycle changes without necessarily being revenue rows. The event_has_revenue flag helps keep lifecycle counts and money totals separate.

SELECT
  transaction_type,
  event_has_revenue,
  COUNT(*) AS row_count,
  SUM(CASE WHEN event_has_revenue THEN estimated_net ELSE 0 END) AS estimated_net_revenue
FROM `example_project.example_dataset.app_sales_daily`
WHERE revenue_date BETWEEN DATE '2026-04-01' AND DATE '2026-04-30'
GROUP BY transaction_type, event_has_revenue
ORDER BY event_has_revenue DESC, row_count DESC;

This query intentionally does not count every subscription event as money. Use it when building dashboards that show lifecycle activity beside revenue-impacting activity.

Example 4: product/SKU rollup

A common BI model groups daily app revenue by product or SKU. The example below uses the synthetic item ID premium_monthly. Keep real SKUs, product IDs and package names out of public examples.

SELECT
  item_id,
  item_name,
  platform,
  report_currency,
  SUM(estimated_net) AS estimated_net_revenue,
  SUM(units) AS units
FROM `example_project.example_dataset.app_sales_daily`
WHERE revenue_date BETWEEN DATE '2026-04-01' AND DATE '2026-04-30'
  AND event_has_revenue = TRUE
  AND item_id = 'premium_monthly'
GROUP BY item_id, item_name, platform, report_currency
ORDER BY estimated_net_revenue DESC;

Example 5: country rollup

Country reporting is useful for market-level analysis. Keep in mind that country availability and meaning depend on the source report family and row type.

SELECT
  country,
  platform,
  report_currency,
  SUM(estimated_net) AS estimated_net_revenue,
  SUM(units) AS units
FROM `example_project.example_dataset.app_sales_daily`
WHERE revenue_date BETWEEN DATE '2026-04-01' AND DATE '2026-04-30'
  AND event_has_revenue = TRUE
GROUP BY country, platform, report_currency
ORDER BY estimated_net_revenue DESC;

Example 6: data completeness coverage

data_completeness helps users understand source-readiness context for daily app-sales rows. It should not be described as settlement finality, payout finality or finance close.

SELECT
  revenue_date,
  data_completeness,
  COUNT(*) AS row_count,
  MAX(ingested_at) AS latest_ingested_at
FROM `example_project.example_dataset.app_sales_daily`
WHERE revenue_date BETWEEN DATE '2026-04-01' AND DATE '2026-04-30'
GROUP BY revenue_date, data_completeness
ORDER BY revenue_date, data_completeness;

Example 7: method-field coverage

Method fields help explain how daily values were sourced, estimated or derived. This is especially useful for fee and tax analysis because Apple and Google do not expose the same daily source facts.

SELECT
  source,
  platform,
  source_report,
  net_method,
  fee_method,
  tax_method,
  COUNT(*) AS row_count,
  SUM(estimated_net) AS estimated_net_revenue,
  SUM(estimated_fee) AS estimated_fee_amount,
  SUM(estimated_tax) AS estimated_tax_amount
FROM `example_project.example_dataset.app_sales_daily`
WHERE revenue_date BETWEEN DATE '2026-04-01' AND DATE '2026-04-30'
  AND event_has_revenue = TRUE
GROUP BY source, platform, source_report, net_method, fee_method, tax_method
ORDER BY source, platform, source_report, row_count DESC;

The goal is not to prove finance truth. The goal is to make analytical assumptions visible.

Finance Unified examples over finance_unified

The next examples query finance_unified. Use them only where Finance Unified is configured. The finance table is separate from app_sales_daily, and its fields should be interpreted through platform finance periods, event types, native/report currency and null-not-zero behavior.

Example 8: finance period rollup

This query groups Finance Unified rows by fiscal period, platform, source report and report currency. It is a finance-period summary, not a daily app-sales trend.

SELECT
  fiscal_period,
  platform,
  source_report,
  report_currency,
  is_final,
  SUM(report_net_proceeds) AS report_net_proceeds
FROM `example_project.example_dataset.finance_unified`
WHERE fiscal_period BETWEEN DATE '2026-04-01' AND DATE '2026-06-01'
GROUP BY fiscal_period, platform, source_report, report_currency, is_final
ORDER BY fiscal_period, platform, source_report, report_currency, is_final;

is_final is a Finance Unified derived field. It is not provider-guaranteed audit truth, accounting close, GAAP, IFRS, ASC 606, tax advice or bank reconciliation.

Example 9: finance null and zero coverage

Finance SQL should preserve the difference between unknown values and real zeros. The query below counts null report fields and real-zero values separately.

SELECT
  platform,
  source_report,
  event_type,
  COUNT(*) AS row_count,
  COUNTIF(report_net_proceeds IS NULL) AS report_net_proceeds_null_rows,
  COUNTIF(report_customer_charge IS NULL) AS report_customer_charge_null_rows,
  COUNTIF(fx_rate IS NULL) AS fx_rate_null_rows,
  COUNTIF(report_net_proceeds = 0) AS report_net_proceeds_real_zero_rows
FROM `example_project.example_dataset.finance_unified`
WHERE fiscal_period = DATE '2026-04-01'
GROUP BY platform, source_report, event_type
ORDER BY platform, source_report, event_type;

This is safer than hiding unknown finance values with blanket COALESCE(..., 0). A display-only dashboard may choose presentation defaults, but finance models should preserve null coverage.

Example 10: daily vs finance aggregate side-by-side

Daily App Sales and Finance Unified can be compared at a high-level aggregate, source-aware level. This does not mean every daily row reconciles to a finance row. Apple Summary Sales differs from Apple financeReports. Google Estimated Sales differs from Google Play Earnings.

WITH daily AS (
  SELECT
    DATE_TRUNC(revenue_date, MONTH) AS calendar_month,
    platform,
    report_currency,
    SUM(estimated_net) AS daily_estimated_net
  FROM `example_project.example_dataset.app_sales_daily`
  WHERE revenue_date BETWEEN DATE '2026-04-01' AND DATE '2026-04-30'
    AND event_has_revenue = TRUE
  GROUP BY calendar_month, platform, report_currency
),
finance AS (
  SELECT
    calendar_period AS calendar_month,
    platform,
    report_currency,
    SUM(report_net_proceeds) AS finance_report_net_proceeds
  FROM `example_project.example_dataset.finance_unified`
  WHERE calendar_period = DATE '2026-04-01'
  GROUP BY calendar_month, platform, report_currency
)
SELECT
  COALESCE(daily.calendar_month, finance.calendar_month) AS calendar_month,
  COALESCE(daily.platform, finance.platform) AS platform,
  COALESCE(daily.report_currency, finance.report_currency) AS report_currency,
  daily.daily_estimated_net,
  finance.finance_report_net_proceeds
FROM daily
FULL OUTER JOIN finance
  ON daily.calendar_month = finance.calendar_month
 AND daily.platform = finance.platform
 AND daily.report_currency = finance.report_currency
ORDER BY calendar_month, platform, report_currency;

This query uses COALESCE only to align dimension keys in the final projection. It does not convert finance amounts from null to zero.