Understanding Your Intercom Schema

This tutorial is to get you familiar with the default Intercom schema found in Stitch and understand the context of these fields to effectively use Intercom for your data analytics.

A schema is a collection of objects within a database, typically consisting of tables (defined below) and the relationships between those tables.

Note: The Primary key in every table is denoted by 🔑

intercom.admin

This table contains information about the admin and teams in Intercom.

AttributeDescription of Attribute
emaile-mail address of the admin. Null for teams
namename of admin or team
🔑idid of admin or team
typevalue is “admin” or “team”

intercom.companies

This table contains information about companies in Intercom. Companies represent organizations using your product. Due to an Intercom API limitation, we can import only the first 10k companies, ordered by Intercom default API ordering.

AttributeDescription of Attribute
company_idid you have defined for the company in your system
created_attime the company was added to Intercom system
🔑idIntercom defined id representing the company in the Intercom system
monthly_spendamount of revenue the company generates for your business
namename of the company
planthe plan you have associated with the company
remote_created_attime the company was created by you
session_countnumber of sessions a company has recorded
typevalue is “company”
updated_atlast time a company was updated
user_countnumber of users in the company

intercom.company_segments

This table contains info about company segments. A segment is a group of users that are defined by a set of rules.

AttributeDescription of Attribute
🔑idid for segment
updated_attime the segment was last updated
created_attime the segment was created
namename of the segment
person_typetype of record. either “user” or “lead”
typevalue is “segment”

intercom.contacts (leads)

This table contains information about logged-out users, or leads, within your Intercom account.

AttributeDescription of Attribute
location_data__latitudelocation latitude for lead
location_data__longitudelocation longitude for lead
session_countnumber of sessions recorded by one lead
avatar__image_urlavatar image URL for the lead
avatar__typevalue is “avatar”
created_attime the lead was added to Intercom
emaile-mail defined for the lead
🔑idid representing the lead
last_request_attime the lead last recorded making a request
last_seen_iplast ip address the Lead visited your application from.
location_data__city_namecity name the lead is associated with
location_data__continent_codecontinent code associated with the lead’s location
location_data__country_namecountry name associated with the leads location
location_data__country_codecountry code associated with the lead
location_data__postal_codepostal code associated with the lead’s location
location_data__region_namename of the region associated with the lead’s location
location_data__timezoneISO 8601 timezone associated with the lead’s location
name name of the lead
unsubscribed_from_emailsindicates if the lead is unsubscribed for the e-mail
user_agent_datadata about the last user agent the lead was seen using
updated_atlast time the lead was updated
user_idautomatically generated identifier for the contact
phonephone number associated with the lead
location_data__type value is “location_data”

intercom.contacts__companies

This table contains details about the company the lead is associated with. This table might be a subtable of the company_contacts table with the following attributes.

AttributesDescription of Attributes
🔑_sdc_source_key_idlead’s id defined by Intercom
🔑_sdc_level_0_idcomposite key for table. value will auto-increment for each unique record, beginning with 0
🔑idcompany id defined by Intercom
company_idid you have assigned to the company in your system
namename of company
typevalue is “company”

intercom.contacts__social_profiles

This table contains details about the social profiles the lead is associated with. This table might be a subtable of the contacts table with the following attributes.

AttributeDescription of Attributes
🔑_sdc_source_key_idid defined by Intercom for the lead
🔑_sdc_level_0_idforms composite key for table. value will auto-increment for each unique record, beginning with 0
🔑idlead’s user id on social platform
namename of social service. Ex: “facebook”
urllead’s url on the social platform
usernamelead’s username on social platform
typevalue is “social_profile”

intercom.contacts__segments

This table has information about segments. A segment is a group of users defined by certain rules. Users are automatically added to the segment as soon as they match specific rules. This table might be a subtable of the contacts table with the following attributes.

AttributesDescription of Attributes
🔑segments_sdc_source_key_idlead’s Intercom-defined id
🔑segments_sdc_level_0_idcomposite key for the table. Value will auto-increment for each unique record, beginning with 0
🔑idsegment’s id
typevalue of field is “type”
segments_sdc_source_key_idlead’s Intercom-defined id

intercom.contacts__tags

This table has information about tags a lead is associated with. Tags are labels for users and companies. This table might be a subtable of the contacts table with the following attributes.

AttributesDescription of Attributes
🔑segments_sdc_source_key_idlead’s Intercom-defined id
🔑segments_sdc_level_0_idcomposite key for the table. value will auto-increment for each unique record, beginning with 0
🔑idid of tag
typetype of field. value is “tag”
namename of the tag

intercom.conversations

This table holds information about the conversations created such as date started, status of conversation, or owner of the conversation.

AttributesDescription of Attributes
🔑idconversation id
updated_attime the conversation was last updated
created_attime the conversation was created
waiting_sincetimestamp which shows at what time a customer has responded to an admin or the time a customer has started to wait for a response.
snoozed_untiltime in that the conversation will be open
statecurrent status of conversation. Values can be “open”, “closed”, “snoozed”
conversation_message__author__idid of user that created the conversation message
conversation_message__author__typetype of user that created the conversation. Values can be “user”, “lead”, “admin”
conversation_message__bodymessage body in HTML. Body that started the conversation
conversation_message__subjectsubject of the message that started the conversation
conversation_message__attachmentsdetails about the message that has started the conversation
conversation_message__typevalue of field is “conversation_message”
total_countnumber of total conversation parts in the conversation
user__idid of user or lead in the conversation
user__typetype of user involved in the conversation. Value can be “lead”, “user”
assignee__idid of admin that is assigned to the conversation
assignee__typetype of admin the conversation is assigned to. Values can be “nobody_admin”, “admin”
openthis attribute indicates if a conversation is “open” (true), or “closed” (false)
readthis attribute indicates if a conversation has been read
typeValue of field. value is “conversation”

_conversation_parts

This table contains details about individual elements in the conversation. This table might be a subtable of the conversation table with the following attributes. The name would be conversations_conversation_parts.

AttributesDescription of Attributes
🔑_sdc_source_key_idid of the conversation
🔑_sdc_level_0_idcomposite key for the table. value will auto-increment for each unique record, beginning with 0
🔑idid of conversation part
part_typetype of conversation. value can be “comment”, “note”, “assignment”, “open”, “close”
body body of conversation in HTML
created_attime the conversation part was created
updated_atlast time the conversation part was updated
assigned_toonly for “assignment” types. the id the admin of the conversation is assigned to
author__idid of user or admin that created the conversation part
author__typetype of user that has created the conversation part
attachmentsShows details about the attachment if available in conversation part

_conversation_customers

This table contains details about the customers, users or leads, that are involved in the conversation. This table might be a subtable of the conversation_customers table with the following attributes. The name would be conversations_conversation_customers.

AttributesDescription of Attributes
🔑_sdc_source_key_idid of conversation
🔑_sdc_level_0_idComposite key for the table. Value will auto-increment for each unique record, beginning with 0
🔑id id of user
typetype of the field. value is “user”

_conversation_tags

This table contains details about tags associated with the conversation. The name would be conversations_conversation_tags.

AttributesDescription of Attribute
🔑_sdc_source_key_idid of conversation
🔑_sdc_level_0_idcomposite key for the table. Value will auto-increment for each unique record, beginning with 0
🔑id id of tag
namename of tag
typetype of field. value is “tag”

intercom.segments

This table contains details about segments in Intercom. Segments are defined as a group of users with a set of rules.

AttributesDescription of Attribute
created_atTime the segment was created
🔑idid of the segment
nameName of segment
typeType of segment. Value is “segment”
person_typeType of record. Value is “lead” or “user”
updated_atTime the segment was last updated

intercom.tags

This table contains details about tags in your Intercom account.

AttributeDescription of Attribute
🔑id id of tag
namename of the tag
typetype of field. value is “tag”

intercom.users

This table contains details about the users in your Intercom account.

AttributeDescription of Attribute
🔑id id of user
updated_attime the user was last updated
created_attime the user was added to Intercom system
signed_up_attime the user has signed up
emailfull address of user’s email
namefull name of the user
phonephone number associated with the user
last_request_attime the user had made the last request
session_countnumber of sessions the user has recorded
avatar__image_urlurl of avatar that is associated with the user
avatar__typevalue is “avatar”
unsubscribed _from _mailsshows details if the user is subscribed
location_data__city_name name of city that user is associated with
location_data__continent_codecontinent code associated with the user’s location
location_data__country_codecountry code associated with the user’s location
location_data__country_namename of country associated with the user’s location
location_data__latitudelatitude associated with user’s location
location_data__longitudelongitude associated with user’s location
location_data__postal_codepostal code associated with user’s location
location_data__region_namename of region associated with user’s location
location_data__timezoneISO 8601 timezone associated with user’s location
location_data__typetype of field. value is “location_data”
user_agent_datalast user agent the user was seen using
last_seen_iplast user’s ip address your application was visited from
pseudonymfield will be used with previous pseudonym of lead
anonymousalways “false” since attribute indicates if user is a lead
typevalue is “user”

_users__companies

This table contains details about companies the user is associated with. This table might be a subtable of the users table with the following attributes. The table name would be users__companies.

AttributeDescription of Attribute
🔑_sdc_source_key_idid of user
🔑_sdc_level_0_idcomposite key for the table. Value will auto-increment for each unique record, beginning with 0
🔑idcompany id defined in Intercom
company_idid that you have assigned to the company
namename of company
typetype of field. value is “company”

_users__social_profiles

This table contains details about the social profiles the user is associated with. This table might be a subtable of the users table with the following attributes. The table name would be users__social-profiles.

AttributesDescription of Attributes
🔑_sdc_source_key_idid of user
🔑_sdc_level_0_idcomposite key for the table. value will auto-increment for each unique record, beginning with 0
🔑iduser id on social platform
namename of social platform
urlhomepage of user on social platform
usernameusername of user on social platform
typetype of field. value is “social_profile”

_users__segments

This table contains details about segments the user is associated with. This table might be a subtable of the users table with the following attributes. The table name would be users__segments.

AttributeDescription of Attribute
🔑_sdc_source_key_idid of user
🔑_sdc_level_0_idcomposite key for the table. value will auto-increment for each unique record, beginning with 0
🔑idid of segment
typevalue is “segment”

_users__tags

This table contains details of tags the user is associated with. This table might be a subtable of the users table with the following attributes. The table name would be users__tags.

AttributesDescription of Attributes
🔑_sdc_source_key_idid of user
🔑_sdc_level_0_idcomposite key for the table. value will auto-increment for each unique record, beginning with 0
🔑idid of tag
typetype of field. value is “tag”
namename of the tag

Foreign Key Joins

A foreign key is a column in a table that establishes an association with another table via shared values.

To accomplish this, a foreign key is populated with values of the primary key from the other table. Foreign keys are also typically titled IDs, but prepended with the name of the referenced table. Below are some of the relations you could have when joining the different Intercom tables.

Note: All foreign key relationships are color coordinated based on each primary key in the tables above for easy identification.

 

admin with company

When you want to link an admin to a specific company, you can combine the the admin id with the Intercom defined company id.

intercom.admin_id <-> intercom.company_id

 

conversations with conversation parts

When you want to link a conversation with a specific conversation part, you can combine the id of conversations with conversation_id of conversations_part table.

intercom.conversations_id <-> intercom.conversations_conversation_part_conversation_id

 

conversations with users

When you want to link conversations with a unique user, you can combine the user_id in the conversations table with the id of the user in the users table.

intercom.conversations_user__id <-> intercom.users_id

 

conversation with customer customer

You can also link a conversation with a specific customer. You can do so by combining the following attributes:

intercom.conversations_customer__sdc_source_key_id <-> intercom.conversation_id

 

conversation with tags

When you want to link conversations with a specific tag, you can do so by combining the following attributes:

intercom.conversations_conversation_tags_id   <-> intercom.tags_id

 

users with companies

When you want to link unique users with a specific company, you can do so by combining the users_companies table with the unique id in the companies table.

intercom.users_companies_id  <-> intercom.companies_id

 

users with segments

When you want to link a unique user with a specific segment, you can combine the users table with the segments table via:

intercom.users__segments_id <-> intercom.segments_id

 

users with social profiles

You can also link users with social profiles via the following attributes:

intercom.users__id <-> intercom.users.social__profiles_sdc_source_key_id

 

segments with companies

You can also link segments with companies by combining via the following attributes:

intercom.segments_id <-> intercom.company_segments_id

 

users with tags

When you want to link a unique user with a specific tag, you can combine the users table with the tags id.

intercom.users__tags_id <-> intercom.tags_id

 

contacts with tags

Also contacts can be linked with tags. Therefore, you can combine contacts with the tags id.

intercom.contacts__tags_id <-> intercom.tags_id

 

social profiles with contacts

When you want to combine a social profile with a unique contact, you can do so by combining the following tables and attributes:

intercom.contacts__social_profiles_id <-> intercom.contacts_id

 

Resource

https://www.stitchdata.com/integrations/intercom/

 

Zoé Meckbach

About Zoé Meckbach