[Bug] zendesk__ticket_metrics.first_reply_time_business_minutes ignore schedule

[KaoruDev]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Comparing the data collect via Fivetran vs the Zendesk report UI; it appears our business schedule is not taken into consideration when calculating the value.

Seems related to: #53

Relevant error log or model output

N/A

Expected behavior

Values in zendesk__ticket_metrics.first_reply_time_business_minutes should consider our schedules and reduce the minutes to first reply.

dbt Project configurations

# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: "binti_data_platform"
version: "1.3.2"
config-version: 2

# This setting configures which "profile" dbt uses for this project.
# We default to using local, in the CI and Fivetran, this will be overidden by passing the --profile flag,
# I.E. dbt run --profile
profile: "local" # pass in the --profile flag to overide this when running dbt run

# These configurations specify where dbt should look for different types of files.
# The `source-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
seed-paths: ["data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target" # directory which will store compiled SQL files
clean-targets: # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"

vars:
  # Configurations for the dbt fivetran github package
  github_source:
    # This is the project Fivetran dumps data into
    github_database: my-gcp-project
  # GCP project where Fivetran Jira connector loads data
  jira_database: my-gcp-project

  zendesk:
    using_organization_tags: False

  zendesk_source:
    # This is the project Fivetran dumps data into
    zendesk_database: my-gcp-project
    using_organization_tags: False

# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models
models:
  +database: |
    {%- if target.name == "fivetran" -%}
      my-gcp-project
    {%- else -%}
      {{ target.database }}
    {%- endif -%}
  github:
    github__pull_requests:
      +schema: marts
  zendesk:
    zendesk__ticket_metrics:
      +schema: marts
  binti_data_platform:
    marts:
      +schema: marts
      jira:
        +materialized: table
      github:
        +materialized: table

Package versions

packages:
  # Source Github Models
  # https://github.com/fivetran/dbt_github_source
  - package: fivetran/github_source
    version: [">=0.6.0", "<0.7.0"]
  # Transformational Github Models
  # https://github.com/fivetran/dbt_github
  - package: fivetran/github
    version: [">=0.6.0", "<0.7.0"]
    # Source Jira Models from Fivetran
  - package: fivetran/jira_source
    version: [">=0.6.0", "<0.7.0"]

  # Transformational Zendesk Models
  # https://hub.getdbt.com/fivetran/zendesk/latest/
  - package: fivetran/zendesk
    version: [">=0.10.0", "<0.11.0"]
  - package: fivetran/zendesk_source
    version: [">=0.8.0", "<0.9.0"]

What database are you using dbt with?

bigquery

dbt Version

1.3.1

# Choose the version of dbt that will run the project.
# Supported versions are listed in https://fivetran.com/docs/transformations/dbt/setup-guide#prerequisites.
# If the version is not specified, dbt 0.18.0 will be used.
dbtVersion: "1.3.1"

jobs:
  # Give each job a unique name. The name will be displayed in the Fivetran
  # dashboard once your jobs are imported.  Job names must be unique.
  - name: Weekly Github Issue Metrics
    # The name of the target that will be used when running the job. If it's not
    # specified, the target will be named 'prod'.
    targetName: fivetran
    # Define when this job should run, using cron format. This example will run
    # every day at 12:00pm (according to your warehouse timezone). For help with
    # cron formatting, visit https://crontab.guru/.
    schedule: 0 0 * * 0
    steps:
      - name: Install Deps # Give each step in your job a name. This will enable you to track the steps in the logs.
        command: dbt deps # Enter the dbt command that should run in this step. This example will run all your models. For a list of available commands visit https://docs.getdbt.com/reference/model-selection-syntax/.
      - name: Update Github Models
        command: dbt run --select +github__pull_requests +github__pull_request_reviews
      - name: Run Tests
        command: dbt test --select +github__pull_requests +github__pull_request_reviews
  - name: Daily Jira Metrics
    targetName: fivetran
    schedule: 0 0 * * *
    steps:
      - name: Install Deps
        command: dbt deps
      - name: Update Jira Models
        command: dbt run --select +jira__field_change_history +jira__issues +jira__sprints
      - name: Run Tests
        command: dbt test --select +jira__field_change_history +jira__issues +jira__sprints
  - name: Daily Zendesk Metrics
    targetName: fivetran
    schedule: 0 0 * * *
    steps:
      - name: Install Deps
        command: dbt deps
      - name: Update Zendesk Models
        command: dbt run --select +zendesk__ticket_metrics
      - name: Run Tests
        command: dbt test --select +zendesk__ticket_metrics

Additional Context

I spot-checked the data collected vs the data in the Zendesk reports.

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[fivetran-joemarkiewicz]

HI @KaoruDev thanks for opening this issue and taking a look at the previous issues that may relate to the same result you are encountering currently.

I believe you are correct that the Issue you linked does seem to similar to what you are encountering. If that is the case, I wanted to direct you to our DECISIONLOG where we detail how we calculate the business metrics for reply time for Zendesk tickets. In particular I wanted to highlight how this will in fact differ from Zendesk's built reports.

The main reason is that Zendesk will only take into consideration the latest schedule that the ticket was assigned. However, this does not tell an accurate picture of time worked on the ticket, especially if the ticket changed hands and schedules. Therefore, our package uses every schedule (and the duration in that schedule) the ticket was associated with when calculating business metrics. We feel strongly that this is a more accurate way of reporting and have decided to intentionally not adjust the package to fit the Zendesk reporting as we do not believe it tells the full story of the ticket. The DECISIONLOG linked will include an example and how you can see that it will differ from the Zendesk reporting.

While this is not something we will adjust within the official version of the package, if you do wish you configure the logic to be custom on your end then I would encourage you to take a look at this post on how to override the logic of one of our models while still leveraging the package as a whole. This should allow you to make the adjustments I included in Issue #53 for the relevant model and get your desired outcome.

Please let me know if this does not answer your initial question. Thanks!

[KaoruDev]

Good to know, thank you for the quick response! I'll close for this for now.