Conversation and Company mapping

Hey Everyone!

We have data of conversation, conversation parts, contact, and company to our datawarehouse, extracted using Intercom API.

Now, we are trying to replicate one table that our client has built in the Intercom Reports which basically shows the conversation with bug tag across the companies. It’s a really basic table, containing the following columns:

• Conversation ID
• Company ID (from Company Standard Attribute)
• Company Name (from Company Standard Attribute)
• Conversation Tag
• Some more additional fields

Now, we are able to create the most of the fields from the API data. However, we are facing the issue for the Company ID and Company Name.

For example, in Intercom report table, a single conversation has 8 companies (as comma separated string) in the Company ID and Company Name. While in datawarehouse we have around ~52 companies.

For the datawarehouse logic, we are using contact as the a bridge to map the conversation data with company. In datawarehouse the flow is Conversation → Contact → Company. So, basically first we are identifying the contacts participated in the conversation and then companies associated with those contacts.

Things we have taken care of (but still a mismatch as mentioned in example):

• Count distinct companies
• Consider only user/customer contact --exclude admin associated companies

So, we are not sure how in the Intercom Reports, Intercom maps the conversation to the companies and how we can implement the same with the API data.

I have gone through the multiple  community posts but have not seen for this problem. However, please direct us if this one is already covered.

Happy to hear your thoughts.

Thanks in advance!!