[Bug] Zendesk SLAs not accurately being captured for certain First Reply Time Business SLAs

[fivetran-joemarkiewicz]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

When working on the fix to address Issue #121 I came across an issue where tickets that were properly being recorded within the zendesk__ticket_metrics model were not being populated in the zendesk__sla_policies model. See below for an example.

I have been able to narrow down the cause of the issue to the filters applied within the int_zendesk__reply_time_combined model. The ticket_ids with SLAs seem to properly be reported in the upstream models, but then are filtered out within the cte linked above.

Relevant error log or model output

See above for relevant error.

Expected behavior

All SLA Policies are properly reported and presented within the zendesk__sla_policies model.

dbt Project configurations

models:
  +schema: zendesk_dev

Package versions

packages:
  - package: fivetran/zendesk
    version: [">=0.13.0", "<0.14.0"]

What database are you using dbt with?

bigquery

dbt Version

dbt v1.5.7

Additional Context

No response

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-reneeli]

I believe the reason why the ticket ids are unintentionally filtered out in zendesk__sla_policies is indeed because of upstream model int_zendesk__reply_time_combined in the filtered_reply_times CTE. We are missing the case where a ticket is replied to outside a business schedule window (after schedule end and before next schedule start) AND business minutes have elapsed. (as for ticket 77)

So we'll need to add an additional clauses like the below to the filter:

1. (agent_reply_at >= sla_schedule_end_at and agent_reply_at <= next_schedule_start )

Additionally, for the last clause we use, we're not accounting for the fact that next_schedule_start could be null (there is no next schedule.)(as for ticket 80) So we need to make this update:

2. Update the following clause with adding next_schedule_start is null

(agent_reply_at is null and {{ dbt.current_timestamp() }} >= sla_schedule_start_at and ( {{ dbt.current_timestamp() }} < next_schedule_start or next_schedule_start is null))

^ Ideally we would do a max(sla_schedule_start_at) but we can't have that in the where clause.

As for ticket 78, I found that it's not in the model upstreams to int_zendesk__reply_time_combined either (neither in int_zendesk__reply_time_business_hours or int_zendesk__sla_policy_applied). It is last seen in int_zendesk__updates. Which is the common denominator between zendesk__ticket_metrics and zendesk__sla_policies. This one doesn't appear in zendesksla_policies intentionally. Because in int_zendesksla_policy_applied we filter where field_name in ('next_reply_time', 'first_reply_time', 'agent_work_time', 'requester_wait_time'). This field name = status.

I want to double check that case # 1. is filtering for the correct record though. And that it will only bring 1 record for that ticket through (There should only be 1 record where a reply time can be after schedule end and before next schedule start

[fivetran-joemarkiewicz]

This has issue should now be addressed in the latest v0.14.0 release of the dbt_zendesk package. As such, closing this out.

Please feel free to reopen or create a new issue if the original issue persists following the upgrade.