Integration Design
Evaluating currency and timezone handling in reports
Evaluate reporting-day boundaries, source currencies, conversion rules and display formatting before combining marketing figures.
Evaluate a report’s currency and time-zone handling by tracing a source amount or event through its reporting period, any conversion rule and its final display. A currency symbol and date label do not establish that spend, revenue or daily activity can be compared.
Record source settings
For each source, record its account or property time zone, source currency, date field and whether an event timestamp is available. Identify whether the reported value is original or converted; display-only currency formatting is covered below. Note when the source data became available.
Check each source’s own time-zone and currency settings; do not assume they match another source.
A change to a source’s time-zone setting can affect how activity is assigned to reporting days. Record any change and consider its effect on comparisons.
Key Metrics for Report Accuracy
- Source Time Zone
- Must be recorded per source (e.g., Sydney, Melbourne, Brisbane)
- Source Currency
- Track original currency (e.g., USD, AUD) for accurate conversion
- Event Timestamp Available?
- Critical for accurate timezone derivation; otherwise, use local date with caution
- Data Availability Date
- Note when source data became accessible to ensure consistency
- Converted Value Stored?
- Always retain original amount and currency alongside converted values
Set the reporting rules
Before combining sources, decide:
- Reporting day:Will the report use each source’s local day or derive a day in one named Australian business time zone from event timestamps?
- Period boundary:Which start and end instants are included? Consider records near midnight and a daylight-saving change where the chosen zone observes one.
- Reporting currency:Will amounts remain in their source currencies or be converted to one currency?
- Conversion:If conversion is needed, which approved rate source, rate date and rounding rule apply? Who explains later corrections?
Where practical, retain the original amount and currency beside a converted value. Adding amounts in different currencies does not make a meaningful total. Changing a Data Studio field’s currency type changes its displayed symbol without converting the value; actual conversion requires an explicit rule and calculation.
BigQuery timestamp functions can derive a date in a specified time zone when an event timestamp is available. If a source supplies only an already-grouped local date, the underlying event times cannot be recovered from that date alone. Mark the limit rather than assigning the entire day to a new zone by assumption.
Currency and Timezone Handling in Marketing Reports: Key Considerations
- Reporting DayUse source’s local day or derive from event timestamps in a named Australian business time zone (e.g., AEST/AEDT)
- Period BoundaryDefine start/end instants carefully—account for midnight transitions and daylight-saving changes (e.g., AEST to AEDT)
- Reporting CurrencyRetain original currency or convert using approved rate sources (e.g., ATO exchange rates, OECD data)
- Conversion RuleSpecify rate source, rate date, rounding method; document who validates corrections
- Data IntegrityDo not sum amounts in different currencies without explicit conversion; avoid assuming event times from grouped dates
Review boundary cases
Prepare safe records just before and after midnight in the chosen reporting zone, across any relevant daylight-saving change and in different source currencies. Write the expected day and amount under the agreed rules. Then ask a supplier or implementation team to show the source record, exported row and displayed chart. Note whether each value was calculated, formatted or inherited.
If source settings change, record the effective date and annotate comparisons across it. Show the currency, reporting time zone, conversion rule and data period beside consequential figures. Defer a cross-source total if its underlying amounts or dates cannot be explained.
Evaluating Currency and Timezone Handling in Reports
- Record Source SettingsNote account time zone, source currency, date field, and whether event timestamp is available. Check if values are original or converted.
- Define Reporting RulesDecide on reporting day (local or fixed Australian zone), period boundaries, currency conversion method, and rounding rules.
- Test Boundary CasesCheck records just before and after midnight, across daylight-saving transitions, and in multiple currencies. Verify how values were calculated.
- Document ChangesRecord effective dates of source setting changes and annotate comparisons. Show currency, time zone, conversion rule, and data period.
- Avoid Invalid TotalsDefer cross-source totals if underlying amounts or dates cannot be explained or reconciled.
