Inconsistency with Intercom DBT data

[luisfer]

We have found issues with some data fields coming from Intercom. The main issue is that Intercom date fields like FIRST_ADMIN_RESPONSE_AT is not grabbing the correct time field. It is meant to show the first time an admin responded to a conversation, but we see inconsistency in this field. Sometimes it does grab the first time an admin responded, but other times it  grabs the time of the second response.
Due to this inconsistency we can't trust the fields coming from Intercom through Fivetran.

Here is an example where you can see the issue (please let me know if I can share more information):

Here I'm attaching the screenshot of the actual conversation from Intercom where you can see that our Admin, Jesus, responded at 12:10pm EST (17:10 UTC), and I am also attaching the conversation with the Intercom agent where he confirms that on his side he sees that the first admin response is 17:10 UTC (expected time)

On our side we get that the first admin response was at 12:23pm EST (17:23 UTC) which corresponds to the second admin response, not the first (seen on the second screenshot of the conversation).

On DBT I see that FIRST_ADMIN_RESPONSE_AT is created in our "int_intercom__conversation_part_aggregates.sql" using this line (last screenshot):

min(casewhen conversation_part_history.part_type ='comment' and conversation_part_history.author_type ='admin' then conversation_part_history.created_at elsenullend) as first_admin_response_at

[Joe-Markiewicz · Accepted solution]

Hi @luisfer thanks for posting here. One quick clarifying question - are there any other admin conversations following the last message at 12:23pm? I am wondering if we are possibly filtering out the initial conversation parts within the int_intercom__latest_conversation_part model. 

Would you be able to materialize and query the int_intercom__latest_conversation_part table and let me know if you see the initial response from Jesus. If you do not then I think that may be the update required. If you do see Jesus' initial response then we will want to dig a bit deeper to understand why the initial response is not being captured.