Intercom (docs)

NOTE: Our Intercom model and source dbt packages only work with connectors that were created in July 2020 or later. If you created your connector before July 2020, you must set up a new Intercom connector to use these dbt packages.

This package models Intercom data from Fivetran's connector. It uses data in the format described by this ERD.

This packages enables you to better understand the performance, responsiveness, and effectiveness of your team's conversations with customers via Intercom. It achieves this by:

Creating an enhanced conversations table to enable large-scale reporting on all current and closed conversations
Enriching conversation data with relevant contacts data
Aggregating your team's performance data across all conversations
Providing aggregate rating and timeliness metrics for customer conversations to enable company-level conversation performance reporting

Models

This package contains transformation models, designed to work simultaneously with our Intercom source package. A dependency on the source package is declared in this package's packages.yml file, so it will automatically download when you run dbt deps. The primary outputs of this package are described below. Intermediate models are used to create these output models.

model	description
intercom__admin_metrics	Each record represents an individual admin (employee), enriched with admin-specific conversation data like total conversations, average rating, and median response times.
intercom__company_enhanced	Each record represents a single company, enriched with data related to the company industry, monthly spend, and user count.
intercom__company_metrics	Each record represents a single row from `intercom__company_enhanced`, enriched with data like total conversation count, average satisfaction rating, median time to first response, and median time to last close with contacts associated to a single company.
intercom__contact_enhanced	Each record represents a single contact, enriched with data like the contact's role, company, last contacted information, and email list subscription status.
intercom__conversation_enhanced	Each record represents a single conversation, enriched with conversation part data like who was assigned to the conversation, which contact the conversation was with, the current conversation state, who closed the conversation, and the final conversation ratings from the contact.
intercom__conversation_metrics	Each record represents a single row from `intercom__conversation_enhanced`, enriched with data like time to first response, time to first close, and time to last close.

Installation Instructions

Check dbt Hub for the latest installation instructions, or read the dbt docs for more information on installing packages.

Configuration

By default, this package looks for your Intercom data in the intercom schema of your target database. If this is not where your Intercom data is, add the configuration below to your dbt_project.yml file.

# dbt_project.yml

...
config-version: 2

vars:
    connector_database: your_database_name
    connector_schema: your_schema_name

If you'd like, you can also add additional columns to the intercom__company_enhanced and/or intercom__contact_enhanced tables. Any columns you pass must be present in the downstream source contact and/or company tables. See below for an example of how to configure the passthrough columns in your dbt_project.yml file.

# dbt_project.yml

...
vars:
  intercom:
    company_pass_through_columns: [company_custom_field_1, company_custom_field_2]
    contact_pass_through_columns: [contact_custom_column]
  intercom_source:
    company_history_pass_through_columns: [company_custom_field_1, company_custom_field_2]
    contact_history_pass_through_columns: [contact_custom_column]

This package assumes that you use Intercom's company tag, contact tag, contact company, and conversation tag, team and team admin mapping tables. If you do not use these tables, add the configuration below to your dbt_project.yml. Since the Intercom package relies on the Intercom Source package, you will need to set the variables as False for both packages. By default, these variables are set to True:

# dbt_project.yml

...
vars:
  intercom_source:
    using_contact_company: False
    using_company_tags: False
    using_contact_tags: False
    using_conversation_tags: False
    using_team: False
  intercom:
    using_contact_company: False
    using_company_tags: False
    using_contact_tags: False
    using_conversation_tags: False
    using_team: False

For additional configurations for the source models, such as the company and tag variables, visit the Intercom source package.

Limitations

Intercom V2.0 does not support API exposure to company-defined business hours. We therefore calculate all time_to metrics in their entirety without subtracting business hours.

Database support

This package has been tested on BigQuery, Snowflake, and Redshift.

Contributions

Additional contributions to this package are very welcome! Please create issues or open PRs against master. Check out this post on the best workflow for contributing to a package.

Resources:

Provide feedback on our existing dbt packages or what you'd like to see next
Have questions, feedback, or need help? Book a time during our office hours here or email us at solutions@fivetran.com
Find all of Fivetran's pre-built dbt packages in our dbt hub
Learn how to orchestrate dbt transformations with Fivetran here
Learn more about Fivetran overall in our docs
Check out Fivetran's blog
Learn more about dbt in the dbt docs
Check out Discourse for commonly asked questions and answers
Join the chat on Slack for live discussions and support
Find dbt events near you
Check out the dbt blog for the latest news on dbt's development and best practices

robertpknight/dbt_intercom