Adobe Analytics OAuth 1.4 connector - Update a dataset hourly

smitiyadav

GrantSmith

Hi @smitiyadav

Is it a matter of the time and data are the same but Domo is just pulling data in 5 hours behind or is it the data appears the same but the times are different? If it's the former it may be because of a delay from the API. If it's the latter it's likely a timezone / UTC issues. Domo expects data to be imported as UTC. Are you looking at the data on the dataset or are you pulling it up in a table card and comparing the two dates? The raw dataset will be in UTC but the table card will have converted the dates to be the timezone your instance is configured to.

jaeW_at_Onyx

if i had to guess i'm betting money that your API (from Adobe doesn't update the data every two hours and instead has a delay in the data that's reported via API.

Domo will extract the data from the API that's available. That's standard functionality across all connectors so you can be reasonably confident this isn't an issue on Domo's end.

Martellooooo

Hi there!

We have this problem when pulling data from a couple advertising publishers (i.e. ZipRecruiter). It's with how they're sending data over to Domo, which is a moot point because that doesn't tell you how to fix it lol.

What we do is setup a basic MySQL dataflow to correct the data before we process it further with subsequent dataflows. I'm not sure how others do it, or if it's considered a "sloppy" way of doing it, but it's helped us correct the timestamp to our timezone (CST, Chicago). We call the dataset into the MySQL dataflow, add a simple Table transformation with the following line (edited to allow for flexibility):

SELECT `Column_A`,`Column_B`, `unix_timestamp`, (`unix_timestamp` - 25200) AS `corrected_timestamp` FROM My_Dataset

The "- 25200" subtracts seven hours off the Unix timestamp that's being delivered by the publisher, correcting the time displacement.

Hope this helps! (or at least points you in the right direction!)

jaeW_at_Onyx

@Martellooooo ,

this is dangerous and can lead to heartache down the road.

As @GrantSmith indicated, Domo stores (and therefore expects) data to arrive in UTC. If the data isn't in UTC as it enters Domo, then i guess... sure transform it to UTC, but be wary of daylight savings.

in the Viz layer, Domo adjusts the data to your company timezone (this includes analyzer and dataset views) but be careful about transforms in ETL while they may display the data in an inuitive way, when it get's written to vault it's actually being stored in UTC.

https://domohelp.domo.com/hc/en-us/articles/360043439073-Time-Zone-Troubleshooting-FAQ

i guess that's a long way of saying... be careful and check the docs!

Martellooooo

@jaeW_at_Onyx completely agree, this was simply the last resort route that we had to take as it wasn't entering/correcting to the right time values like the rest of our publishers. Sometimes we gotta use solutions that aren't pretty lol

EDIT: I should add, the biggest reason this was a necessity for us was that if this data wasn't corrected prior to final visualizations, publisher data was posting to different days thus distorting our cost reporting for our clients. After a few back and forth's with Domo Support, this was the most viable solution. But yes, dangerous and not pretty lol