Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

BUG - reply_time fields in ticket_metrics not populating #49

Closed
2 of 9 tasks
emiliedecherney opened this issue Oct 6, 2021 · 27 comments
Closed
2 of 9 tasks

BUG - reply_time fields in ticket_metrics not populating #49

emiliedecherney opened this issue Oct 6, 2021 · 27 comments
Assignees
@fivetran-joemarkiewicz
Labels
bug Something isn't working

Comments

@emiliedecherney
Copy link

emiliedecherney commented Oct 6, 2021 •
edited
Loading

Are you a current Fivetran customer?
<Yes, Emilie DeCherney, Director of Analytics, Pendo>

Describe the bug

Steps to reproduce
Run example query:
select ticket_id, total_agent_replies, first_reply_time_business_minutes, first_reply_time_calendar_minutes
from pendo-reporting.pendo_zendesk.zendesk__ticket_metrics
where ticket_id = 75162

Expected behavior
Reply times to be populated

Project variables configuration

version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'default'

# 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!
source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-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_modules"


# Model Configuration
# Full documentation: https://docs.getdbt.com/docs/configuring-models
models:
    +persist_docs: # Create BigQuery descriptions for objects with resource descriptions.
        relation: True
        columns: True
    analytics:
     # Applies to all Files under models/temp
        materialized: table
        intermediate:
            dataset: staging
        final:
            dataset: final
        staging:
            schema: staging
        views:
            schema: views
        temp:
            schema: temp
    jira_source:
        +schema: staging
    jira:
        +schema: fivetran_jira
        intermediate:
            +schema: staging
    zendesk_source:
        +schema: staging
    zendesk:
        +schema: pendo_zendesk
        agent_work_time:
            +schema: staging
        intermediate:
            +schema: staging
        reply_times:
            +schema: staging
        sla_policy:
            +schema: staging
        ticket_history:
            +schema: staging
        utils:
            +schema: staging
    salesforce_source:
        +schema: staging
    salesforce:
        +schema: fivetran_salesforce
        intermediate:
            +schema: staging

vars:
    jira_schema: fivetran_jira
    jira:
        issue_field_history_columns: [
            "timespent",
            "assignee",
            "lastViewed",
            "summary",
            "resolutiondate",
            "creator",
            "resolution",
            "updated",
            "description",
            "timeoriginalestimate",
            "issuekey",
            "timeestimate",
            "security",
            "parent",
            "priority",
            "created",
            "workratio",
            "statuscategorychangedate",
            "statuscategory",
            "environment",
            "project",
            "issuetype",
            "reporter",
            "components",
            "labels",
            "versions",
            "fixVersions",
            "customfield_12602",  # The custom field for zendesk ticket count
            "customfield_12735", # one of the 'severity' fields
            "customfield_12100", #another 'severity' field
            "customfield_10114", # 'Team'
            "customfield_12801", # 'Team name'
            "customfield_11201", # 'Dev team'
            "customfield_12720", # 'Team'
            "customfield_12760", # 'Value proposition'
            "customfield_12508", # 'Buisness value'
            "customfield_12836" # 'Dev SOC Trust values'
        ]
    zendesk_schema: pendo_zendesk
    salesforce_schema: fivetran_salesforce
    fivetran_log:
        fivetran_log_using_transformations: false # this will disable all transformation + trigger_table logic
        fivetran_log_using_triggers: false # this will disable only trigger_table logic

Package Version

  - package: fivetran/jira_source
    version: 0.2.0
  - package: fivetran/jira
    version: 0.3.0
  - package: fivetran/zendesk_source
    version: 0.3.0
  - package: fivetran/zendesk
    version: 0.4.0
  - package: fivetran/salesforce_source
    version: 0.2.1
  - package: fivetran/salesforce
    version: 0.3.1
  - package: fivetran/fivetran_log
    version: 0.3.0

Warehouse

  • BigQuery
  • Redshift
  • Snowflake
  • Postgres
  • Databricks
  • Other (provide details below)

Additional context

Screenshots

Query results from zendesk__ticket_metrics for example ticket_id (75162) show null reply times, when there is a reply:
image

For the same ticket, reply is shown in Zendesk UI:
image

Please indicate the level of urgency
Urgent, this is blocking ability to report accurately through several dashboards for an extended period of time.

Are you interested in contributing to this package?

  • Yes, I can do this and open a PR for your review.
  • Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
  • No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.
@emiliedecherney emiliedecherney added the bug Something isn't working label Oct 6, 2021
@fivetran-joemarkiewicz
Copy link
Contributor

Hi @emiliedecherney thank you for opening this issue and providing all the additional context for the error you are seeing within the zendesk__ticket_metrics for the reply_time fields.

We actually helped another user address a similar type of issue (they were actually seeing extremely high reply times) and when addressing their issue we actually reworked how the reply time metric is calculated. Would you be able upgrade your package to the latest version of dbt_zendesk (that is compatible with dbt v0.19.0) and see if this issue still persists?

You can use the below config in place of your current one for your packages.yml:

  - package: fivetran/jira_source
    version: 0.2.0
  - package: fivetran/jira
    version: 0.3.0
  - package: fivetran/zendesk ##Updated to the latest dbt v0.19.0 compatible release
    version: 0.5.2
  - package: fivetran/salesforce_source
    version: 0.2.1
  - package: fivetran/salesforce
    version: 0.3.1
  - package: fivetran/fivetran_log
    version: 0.3.0

Let me know if the issue still persists and if so we will look further into the package.

@fivetran-joemarkiewicz fivetran-joemarkiewicz changed the title BUG - [your bug title here] BUG - reply_time fields in ticket_metrics not populating Oct 6, 2021
Merged
12 tasks
@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 6, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

@emiliedecherney this is due to a mismatch in the source package dependencies. You actually do not need to declare those within your packages.yml as the main packages already reference the accurate source version. The duplicate "fivetran_utils" is caused by the source packages listed out are referencing an older version of our fivetran_utils package. If you use the below it should resolve that error:

  - package: fivetran/jira
    version: 0.3.0
  - package: fivetran/zendesk ##Updated to the latest dbt v0.19.0 compatible release
    version: 0.5.2
  - package: fivetran/salesforce
    version: 0.3.1
  - package: fivetran/fivetran_log
    version: 0.3.0

@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 7, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

Hi @emiliedecherney my appologies I missed the fivetran_log upgrade since v0.3.0 uses the old version as well.

  - package: fivetran/jira
    version: 0.3.0
  - package: fivetran/zendesk ##Updated to the latest dbt v0.19.0 compatible release
    version: 0.5.2
  - package: fivetran/salesforce
    version: 0.3.1
  - package: fivetran/fivetran_log
    version: 0.3.2

@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 7, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

@emiliedecherney I am sorry to hear this did not resolve the issue, but thank you for confirming this.

I have added this issue to my current sprint tasks and will look deeper into the issue you are seeing and work see if a fix can be applied to fix the issue you are seeing. I will get back to you here once I am able to come back with updates.

@fivetran-joemarkiewicz fivetran-joemarkiewicz self-assigned this Oct 7, 2021
@fivetran-joemarkiewicz
Copy link
Contributor

@emiliedecherney After doing some digging I have been able to find that our package currently takes into consideration first reply time after an external comment is created. Taking a look at the screenshot you provided it looks like there has not been an external comment yet. Is that the case?

Additionally, do you see the nature of tickets commonly being created without a customer opening the ticket and creating a comment to be replied to? This is a type of functionality we do not have built into the package at the moment, but I would like to try and understand a bit better the nature of tickets that are not opened and first commented on by end users.

@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 11, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

fivetran-joemarkiewicz commented Oct 12, 2021 •
edited
Loading

Hi @emiliedecherney thanks for this confirmation. I also was able to find Zendesk Support Metric Definitions and see how Zendesk is calculating these metrics on their end.
image

When we originally developed the code for the reply times in this package we followed the assumption that reply times would want to be measured following the first end-user comment opposed to just the creation of the ticket. I would agree with you that in instances where an end-user creates a ticket but provides no comment then the measure will not be accurately recorded. Currently, we would like this package to replicate the definitions of these measures on Zendesk's side so our users will be able to replicate the same reports accurately.

I will make some adjustments on a branch and share in this thread so you may try and confirm if the results you are seeing are expected.

@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 12, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

fivetran-joemarkiewicz commented Oct 12, 2021 •
edited
Loading

That would be great!

I will plan to do an audit of the metrics we originally had in the first iteration of this package. A few months ago we added a suite of metrics based off the definitions highlighted by Zendesk in the doc I shared above, but we did not revisit metrics we originally had in this package (reply times being one of them). This will be a good exercise to confirm those definitions align with what we have in the package.

@fivetran-joemarkiewicz
Copy link
Contributor

Hi @emiliedecherney I have been able to take what we have talked about above and make the respective updates within a working branch bugfix/reply-time-metrics.

Would you be able to use the below packages.yml config in place of the existing fivetran/zendesk package dependency:

packages:
    - git: https://github.com/fivetran/dbt_zendesk.git
      revision: bugfix/reply-time-metrics
      warn-unpinned: false

Once you have updated the packages.yml you may execute dbt clean then dbt deps and finally dbt run to run the code in the working branch. Let me know if you have any questions when installing the working branch and let me know if you notice this fixes the reply times being null when they shouldn't error you originally experienced.

Thank you!

@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 13, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

That's great to hear @emiliedecherney! I will soon open a PR with these changes and work to merge them into the next release. I agree that total_agent_replies metric was previously off and I have since updated it in the recent branch to now be a total sum of all agent replies (both public and not public) on a ticket. I checked and confirm this aligns with Zendesk metric report. Does this align with your understanding.

In regards to the business minutes we have found that our package does not always match up to what is reported in Zendesk. While this is the case, we do believe our logic is more accurate. After looking into the business minutes we found that if a ticket changes schedules Zendesk will update the business minutes to have the ticket use only the schedule that it was last updated to. In contrast, our package takes into account the schedule of the ticket with each hand off along the way. This does result in a difference between our reporting and Zendesk, but find our logic to be more accurate. I would be happy to walk you through this live if you would like. If so, feel free to setup time during our dbt office hours and we would be happy to help!

@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 17, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

@emiliedecherney that sounds great!

Our current sprint ends this Wednesday (10/20) EOD so you can expect these changes and others included within PR #48 to be released on a new version then.

FYI the new version of the package will require a dbt upgrade as the latest versions of the packages use dbt >= 0.20.0 and changing fishtown-analytics references to now be dbt-labs.

Merged
12 tasks
@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 19, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

What types of issues had you run into in the past? Maybe I can help point you in the right direction here?!

@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 19, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

@emiliedecherney the images don't seem to have rendered when posting through Github.

My recommendation would be to upgrade the fivetran packages you are using to their latest versions which will resolve the package references to fishtown-analytics. That should be the only change you need to make! You may want to take a look at the packages you are upgrading to be aware of the release notes in case their are breaking changes, but once you upgrade the packages you should be good!

@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 20, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

@emiliedecherney sounds good! I was just doing some final tests before the package is released.

One question I want to run by you. If when an agent opens a ticket for a customer and adds a comment at the same time of the ticket creation, but another public comment has not been made yet would you expect the reply time to be 0 or null?

I have done some digging into our own Zendesk data and see that Zendesk leaves these tickets as null instead of 0. Then the first reply time is made following the next public comment. To be consistent with Zendesk reporting I am leaning to keep those records as null, but was curious what your opinion is as the ticket you shared in the original issue looks to only have one public comment made to it.

@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 20, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

@emiliedecherney the latest release v0.7.0 has just be cut!! You can expect this release to be live on the dbt hub at the top of the hour.

I will leave this ticket open until the release is live. Once the latest release is live I will close the ticket and please feel free to open another Github issue if you have any further questions or requests for the package. 😄

@emiliedecherney
Copy link
Author

emiliedecherney commented Oct 21, 2021 via email

@fivetran-joemarkiewicz
Copy link
Contributor

Hi @emiliedecherney correct, that was my clarification question above. Zendesk marks those tickets with no other follow up response as null. A first reply time is only recorded following a second internal public response if the first reply is also an internal comment.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@fivetran-joemarkiewicz fivetran-joemarkiewicz

Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@fivetran-joemarkiewicz @emiliedecherney