Quick afternoon audit: where timezone and date errors usually hide
- Webforms and browser timestamps: check whether forms submit the user's local time, the page time, or a server time. Look for timestamps ending with Z (UTC) or with +/– offsets.
- Ad platforms and UTM hits: Google/Meta often report local campaign times; compare daily ad click counts to CRM leads by UTC day vs local business day to spot shifts.
- CSV imports and emailed spreadsheets: Excel/Sheets can strip offsets or change dates depending on locale (UK vs US). Open a sample CSV in a plain text editor to see raw timestamps.
Choose a canonical timestamp and normalise at integration points
Pick one canonical store for machine time (UTC datetime) and one business-side field for operations (local business_date). Store both where you can: UTC for processing, business_date for SLAs, daily reports and billing cutoffs.
At each integration point normalise incoming timestamps into those fields. Practical low-code examples: Zapier → Formatter → Date/Time to convert to UTC; In Make use formatDate(...) to produce UTC and a Europe/London business date. For HubSpot, Salesforce, Marketo or Pardot ingest the UTC field and populate a separate business_date property rather than relying on system-created local times.
Simple checks, alerts and automation updates so the fixes stick
- Add quick tests: daily record-count comparisons (CRM vs ad platform), weekday skew checks (sudden +1 hour shifts), and a small set of canary records that flow through every integration.
- Add fail-fast rules: in Zapier/Make add a branch to tag or pause records missing offsets; in scheduled batch jobs reject or flag rows with ambiguous timestamps; in CRMs create a list for records with future dates or dates older than a threshold.
Update automations and SLA windows to always read the canonical fields: use UTC for processing/time-since calculations and business_date (converted with Europe/London rules) for SLA cutoffs. Remember UK DST: Europe/London moves +1 hour in summer — storing UTC avoids DST errors, but when you convert to business_date or calendar boundaries use DST-aware conversion.
If you want a short, practical session to run this afternoon audit and add the low-code fixes across Zapier/Make and HubSpot/Salesforce, Optira can help.