fivetran/dbt_ad_reporting

Time zone mis match when dealing with data from multiple data sources

teja-goud-kandula opened this issue · 4 comments

teja-goud-kandula commented

Is there an existing feature request for this?

  • I have searched the existing issues

Describe the Feature

The data provided by the LinkedIn ads take reports the data in the UTC timestamp. The data fetched from the Facebook ads and the Google ads reports the data in the timestamp from which time zone the accounts are created. Let's assume it is EST for now. When combining data multiple data from multiple ad sources we have LinkedIn data showing values by considering UTC time zone and the Facebook and the Google ads shows the metrics based on the EST time zone.

If we are using ads reporting package we are getting a consolidated view of the entire data but different ads report in different time zones. If we are considering to figure out conversion attribution using a tool like heap then we need to convert the heap data into EST time zone before joining the data with Facebook ads data and Google ads, when joining heap data with LinkedIn data heap data needs to be converted into UTC time zone. This seems to be like a bad idea. Wouldn't it be better if we can configure the required time zone from a single .yml file and the data from all the ads network is reported based on that time zone? Looking forward to this becoming a feature on the ad reporting package.

If this functionality is already present on this package let me know how to do achieve that and where I need to configure that?

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

  • Yes.
  • Yes, but I will need assistance and will schedule time during your office hours for guidance.
  • No.

Anything else?

No response

fivetran-joemarkiewicz commented

Hi @teja-goud-kandula thanks so much for opening this issue and posing a thoughtful and relevant question!

Your reasoning does make sense to me and ensuring standardization of timezones across the platforms is necessary in order to generate an accurate view of your Ad performance. My initial understanding was that all Ad platforms synced via Fivetran were in UTC and therefore would be consistent. My team will do some more digging into this inquiry and will get back shortly with what we find.

Thanks again and we look forward to continuing this conversation 😄

fivetran-sheringuyen commented

Hi @teja-goud-kandula! I've been digging into this with our product and engineering teams and I do believe that what you're seeing is very likely correct (UTC for Linkedin and account set timezones for Google and FB) and agree that this definitely could introduce a number of issues when performing analyses. I have a couple of follow up questions for you: 1. What is your destination warehouse? 2. Would you be able to check what timezone settings (if any) are currently set up in your warehouse instance?

teja-goud-kandula commented

Hi @fivetran-sheringuyen ,
Here are the requested answers:

  1. The destination warehouse is Snowflake.
  2. I am able to check the default timezone settings on the warehouse, it is set to the default value, i.e. America\Los Angles.
fivetran-sheringuyen commented

Hey @teja-goud-kandula! Hm that is strange, what should happen is the timestamps should be changed to the destination timezone so you wouldn't get the mismatched timezone issues using our package. I spoke to our product team about this and believe that this should be addressed at the connector level.

I believe the best way for you to proceed would be to file a support ticket with Fivetran which you can do here.