BigQuery Export vs UI Revenue: Modeled Conversions and Timezone
Meridian Home is an ecommerce brand with GA4 and BigQuery export. The analytics engineer queries events_* for April, filters event_name = 'purchase', sums ecommerce.purchase_revenue, and gets $412,000. The GA4 Monetization > Ecommerce purchases report for the same April date range shows $468,000. The property serves EU and US traffic; consent banner rejection is about 35% in the EU. The team enabled Google Signals and uses blended reporting identity. The engineer's query does not filter on consent fields; it also groups by event_date in UTC, while the property timezone is America/New_York. The CFO wants one number for the board deck and asks whether BigQuery or the UI is 'right'.
1. Identify the plausible causes of the $412,000 vs $468,000 gap and explain why the BigQuery number is not automatically the 'true' revenue.
Read the worked answer
Plausible causes, in order of likely impact:
- Modeled conversions and behavioral modeling. With consent denial at about 35% in the EU and blended identity, the GA4 UI can include modeled conversions for users who declined analytics storage. The BigQuery export contains observed events, not the UI's modeled estimates, so UI revenue can be higher.
- Thresholding. The UI can withhold rows when Signals or demographics are involved; that normally pushes UI lower, so it does not explain this direction, but it means the UI is not a complete row-level source either.
- Timezone/date boundary. The property is America/New_York; grouping by UTC
event_dateshifts late-evening purchases into the next day and changes the April total. - Consent filtering and query logic. The engineer did not filter consent fields; depending on how the query is written, consent-denied events may be included or excluded inconsistently with the UI.
- Duplicates, currency, refunds. Missing
transaction_iddedupe, mixed currency, or refund handling can move either number.
BigQuery is event-level and unsampled, but it is not a complete business ledger. It is 'true' only for the events that were collected and for the metric definition you apply.
2. Give a reconciliation procedure that would let the CFO choose one number with confidence.
Read the worked answer
Define the metric first: gross purchase revenue, net of refunds, in property timezone, including or excluding modeled conversions. Then reconcile step by step:
- Re-run the BigQuery query with
event_dateconverted to America/New_York, filter toevent_name = 'purchase', dedupe bytransaction_id, and check currency and refund events. - Compare the observed-only total with the UI's observed-only view if available, or with a GA4 report that excludes modeled data; compare the modeled-inclusive total separately.
- Reconcile to Shopify orders for the same period; the order management system is the financial source of truth for booked revenue.
- Document the variance: X% consent-denied or modeled, Y% timezone, Z% duplicates and refunds.
For the board, use the Shopify or order-system number for financial revenue and label GA4 as behavioral measurement. If the board needs GA4, use one governed definition and state whether modeled conversions are included.
3. What would you change in the BigQuery export or governance to make next quarter's comparison repeatable?
Read the worked answer
Create a governed revenue view rather than ad hoc queries. The view should cast event_date and convert timestamps to the property timezone; filter event_name = 'purchase'; dedupe by transaction_id; handle refunds and currency; expose consent status as a dimension; and publish both observed and modeled-inclusive totals if the UI uses modeling. Add a data dictionary that defines each field and the inclusion rules. Schedule a monthly reconciliation to Shopify with an acceptable variance threshold and an owner.
Do not try to make BigQuery reproduce the UI exactly; make both sources explainable. Also check whether the export's consent and privacy fields are being used consistently, and avoid joining user-level Signals data that is not exported.