This package enables you to better understand the performance of your Support team. It calculates metrics focused on response times, resolution times, and work times for you to analyze.
Optional features (for Zendesk Professional or Enterprise users)link
- Package converts metrics to business hours
- Package calculates SLA policy breaches
This package contains transformation models, designed to work simultaneously with our Zendesk Support 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.
|zendesk__ticket_metrics||Each record represents a Zendesk ticket, enriched with metrics about reply times, resolution times, and work times. Calendar and business hours are supported.|
|zendesk__ticket_enriched||Each record represents a Zendesk ticket, enriched with data about its tags, assignees, requester, submitter, organization, and group.|
|zendesk__ticket_summary||A single record table containing Zendesk ticket and user summary metrics.|
|zendesk__ticket_backlog||A daily historical view of the ticket field values defined in the
|zendesk__ticket_field_history||A daily historical view of the ticket field values defined in the
|zendesk__sla_policies||Each record represents an SLA policy event and additional sla breach and achievement metrics. Calendar and business hour SLA breaches are supported.|
Include in your
packages.yml to stay up to date with the latest release!
packages: - package: fivetran/zendesk version: [">=0.8.0", "<0.9.0"]
The Fivetran team maintaining this package only maintains the latest version. We highly recommend you keep your
packages.yml updated with the dbt hub latest version. You may refer to the CHANGELOG and release notes for more information on changes across versions.
By default, this package looks for your Zendesk Support data in the
zendesk schema of your target database. If this is not where your Zendesk Support data is, add the following configuration to your
... config-version: 2 vars: zendesk_source: zendesk_database: your_database_name zendesk_schema: your_schema_name
Adding Passthrough Columnslink
This Zendesk Source package allows for custom fields to be added to the
stg_zendesk__ticket model. These custom fields will also persist downstream to the
zendesk__ticket_metrics models. You may add your own customer fields to these models by leveraging the below variable.
... vars: zendesk__ticket_passthrough_columns: ['account_custom_field_1', 'account_custom_field_2']
Tracking Ticket Field History Columnslink
zendesk__ticket_field_history model generates historical data for the columns specified by the
ticket_field_history_columns variable. By default, the columns tracked are
assignee_id. If you would like to change these columns, add the following configuration to your
dbt_project.yml file. Additionally, the
zendesk__ticket_field_history model allows for tracking the specified fields updater information through the use of the
zendesk_ticket_field_history_updater_columns variable. The values passed through this variable limited to the values shown within the config below. By default, the variable is empty and updater information is not tracked. If you would like to track field history updater information, add any of the below specified values to your
dbt_project.yml file. After adding the columns to your
dbt_project.yml file, run the
dbt run --full-refresh command to fully refresh any existing models.
... config-version: 2 vars: zendesk: ticket_field_history_columns: ['the','list','of','column','names'] ticket_field_history_updater_columns: [ 'updater_user_id', 'updater_name', 'updater_role', 'updater_email', 'updater_external_id', 'updater_locale', 'updater_is_active', 'updater_user_tags', 'updater_last_login_at', 'updater_time_zone', 'updater_organization_id', 'updater_organization_domain_names' , 'updater_organization_organization_tags' ]
Note: This package only integrates the above ticket_field_history_updater_columns values. If you’d like to include additional updater fields, please create an issue specifying which ones.
Changing the Build Schemalink
By default this package will build the Zendesk staging models within a schema titled (<target_schema> +
_zendesk_staging), the Zendesk intermediate models within a schema titled (<target_schema> +
_zendesk_intermediate), and the Zendesk final models within a schema titled (<target_schema> +
_zendesk) in your target database. If this is not where you would like your modeled Zendesk data to be written to, add the following configuration to your
... models: zendesk: +schema: my_new_schema_name # leave blank for just the target_schema intermediate: +schema: my_new_schema_name # leave blank for just the target_schema sla_policy: +schema: my_new_schema_name # leave blank for just the target_schema ticket_history: +schema: my_new_schema_name # leave blank for just the target_schema zendesk_source: +schema: my_new_schema_name # leave blank for just the target_schema
This package takes into consideration that not every Zendesk account utilizes the
ticket_form_history features, and allows you to disable the corresponding functionality. By default, all variables’ values are assumed to be
true. Add variables for only the tables you want to disable:
... config-version: 2 vars: using_schedules: False #Disable if you are not using schedules using_domain_names: False #Disable if you are not using domain names using_user_tags: False #Disable if you are not using user tags using_ticket_form_history: False #Disable if you are not using ticket form history using_organization_tags: False #Disable if you are not using organization tags
Note: This package only integrates the above variables. If you’d like to disable other models, please create an issue specifying which ones.
Extending and Limiting the Ticket Field Historylink
This package will create a row in
zendesk__ticket_field_history for each day that a ticket is open, starting at its creation date. A Zendesk ticket cannot be altered after being closed, so its field values will not change after this date. However, you may want to extend a ticket’s history past its closure date for easier reporting and visualizing. To do so, add the following configuration to your
... config-version: 2 vars: zendesk: ticket_field_history_extension_months: integer_number_of_months # default = 0
Conversely, you may want to only track the past X years of ticket field history. This could be for cost reasons, or because you have a BigQuery destination and have over 4,000 days (10-11 years) of data, leading to a
too many partitions error in the package’s incremental models. To limit the ticket field history to the most recent X years, add the following configuration to your
... config-version: 2 vars: zendesk: ticket_field_history_timeframe_years: integer_number_of_years # default = 50 (everything)
Opinionated Modelling Decisionslink
Business Time Metrics Logiclink
This dbt package takes an opinionated stance on how business time metrics are calculated. The dbt package takes all schedules into account when calculating the business time duration. Whereas, the Zendesk UI logic takes into account only the latest schedule assigned to the ticket. If you would like a deeper explanation of the logic used by default in the dbt package you may reference the DECISIONLOG.
This package is compatible with BigQuery, Snowflake, Redshift and Postgres.
Additional contributions to this package are very welcome! Please create issues
or open PRs against
main. Check out
on the best workflow for contributing to a package.
- Find all of Fivetran’s pre-built dbt packages in our dbt hub
- Provide feedback on our existing dbt packages or what you’d like to see next
- Learn more about Fivetran here
- Learn how to orchestrate your models with Fivetran Transformations for dbt Core™
- Check out Fivetran’s blog
- Learn more about dbt in the 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 blog for the latest news on dbt’s development and best practices