One of the most difficult things about dealing with data from multiple platforms is that there is no agreement on what the 'right' timezone is. At scale and volume, a swing of a few hours tends to not affect the overall trends. But if you're digging in on a specific day or week, differences in timezones can lead to tangible differences in statistics and metrics.

If there is a standard, it's UTC. But different corporations are spawned, and continue to report, in different timezones. So when dealing with data coming out of multiple platforms, reconciling those times can be difficult.

Frakture attempts to standardize all transaction dates, publish dates, etc to UTC for storage into your warehouse. Like democracy, this is the worst solution — except for everything else. If you're in California, you may say "Hey, everyone important is in PST, use PST". Agencies in New York may say the same thing about EST. Some may say "Hey, we only work with ad platforms, just use whatever they say" ..... to the chagrin of the email team sitting beside them. Most will simply say, "just make it work.” That’s what this article is about.

For a tangible example, Facebook and Google will report statistics for ads based on the timezone you ORIGINALLY set that ad account up in. Opens, clicks, spend, etc. Even the API reports data — time shifted to your specific timezone. However, some payment processors will report transactions in the API via UTC, and in the user interface using EST (like ActBlue). So, if you're trying to figure out ROI (raised/spend) for a 24 hour period, you CAN'T use any of the numbers you see out of the platforms.... you HAVE to time shift them.

Frakture does provide raw transaction_summary tables to allow you to do that if you wish.

However, at some point Frakture has to pick a timezone to roll stats up by date — for our by_date summary views.

Given that many of our clients compare their clients in PST to their clients in EST, etc, picking a timezone per client has its own issues, particularly as many platforms don't let you choose a timezone. To hew as close to standard as possible across hundreds of different platforms and users, Frakture chooses UTC.

If you want to hew to standards as well — choose UTC where you can. If you choose UTC everywhere you can, like FB/Google — then everything is 100% matched up out of the box. If you choose to use other timezones for certain platforms (not really an unusual choice, and sometimes not a choice you can vary at all) just be aware that the time shifting of data will cause smaller time windows (like daily) to be out-of-step compared to what you might be seeing in your own platform, even though the broader trends (weekly/monthly/yearly) remain the same.