v0.16.0 dbt_zendesk

🚨 Minor Upgrade 🚨

Although this update is not a breaking change, it will likely impact the output of the zendesk__sla_policies and zendesk__sla_metrics models. PR #154 includes the following changes:

Bug Fixes

• Addresses the potential issue where the first_reply_time_business_minutes metric within the zendesk__ticket_metrics model would incorrectly calculate the elapsed time when daylight savings occurred. This change involved adjusting a join to reference the difference of two dates as opposed to timestamps. This more accurately applies a cutoff event during daylight savings.
• Introduction of an additional condition within the filtered_reply_times cte of the int_zendesk__reply_time_combined model to ensure tickets replied to before any schedule begins and no business minutes have been spent is reserved for only the first day the ticket is open. Previously, this condition could be met on days other than the first. This would potentially result in duplicates of sla_event_id's further downstream in the zendesk__sla_policies model.

Under the Hood

• Addition of integrity and consistency validation tests within integration tests for the zendesk__sla_policies and zendesk__ticket_metrics models.

Full Changelog: v0.15.0...v0.16.0

v0.15.0 dbt_zendesk

🚨 Minor Upgrade 🚨

Although this update is not a breaking change, it will significantly impact the output of the zendesk__sla_policies model. PR #146 includes the following changes:

Bug Fixes

• Fixes the issue of potential duplicate sla_event_id's occurring in the zendesk__sla_policies model.
  • This involved updating the int_zendesk__schedule_spine which was previously outputting overlapping schedule windows, to account for when holidays transcended a given schedule week.
  • This also involved updating the int_zendesk__reply_time_business_hours model, in which two different versions of a schedule could exist due to daylight savings time.
• Improved performance by adjusting the int_zendesk__reply_time_business_hours model to only perform the weeks cartesian join on tickets that require the further look into the future.
  • Previously the int_zendesk__reply_time_business_hours would perform a cartesian join on all tickets to calculate weeks into the future. This was required to accurately calculate sla_elapsed_time for tickets with first replies far into the future. However, this was only necessary for a handful of tickets. Therefore, this has been adjusted to accurately only calculate the future weeks as far as either the first reply time or first solved time.

Documentation Updates

• Addition of the reference to the Fivetran prebuilt Zendesk Streamlit report in the README.
• Updates DECISIONLOG to include a note that the generated time series for ticket SLA policies is limited to a year into the future to maintain performance.

Full Changelog: v0.14.0...v0.15.0

v0.14.0 dbt_zendesk

PR #136 includes the following changes:

Bug Fixes

• Converted the sla_elapsed_time metric within the zendesk__sla_policies model to be reported in minutes to the second as opposed to just the nearest rounded minute. This ensures more accurate reporting.
• Adjusted the next_reply_time SLA elapsed time metric calculation within the zendesk__sla_policies model to also take into consideration the ticket solved event as a valid SLA event. Previously if a reply time SLA was attached to a ticket and there was no reply, but the ticket was closed then the SLA would be breached. This update ensures a closed event serves as a route for the SLA to be achieved or breached.
• Updated the int_zendesk__reply_time_combined model to additionally account for the following business hour scenarios as they were erroneously being filtered out in previous versions of the package:
  • A ticket is first replied to outside SLA schedules
  • A ticket has not yet received an agent reply
• Overhauled the logic used within the int_zendesk__reply_time_combined model to calculate sla_breach_at within the zendesk__sla_policies and upstream models for reply time SLAs. It was found this field was inconsistent with the actual breach/achieve time of an SLA. The overhaul should now ensure reply time SLA is accurate to either be the time of the SLA breach or achieve event.
  • In particular, for first and next reply time SLAs the sla_breach_at will be the time of the breach if the SLA was breached or the time the SLA was achieved if it was not breached.
• Modified the logic that matches schedule weeks within the int_zendesk__reply_time_combined model when calculating reply time business metrics. Previously long running SLAs would be excluded from the final model, now all reply time business SLAs regardless of sla elapsed duration will be included in the end zendesk__sla_policies model.
• Included additional logic within the int_zendesk__ticket_schedules model to more accurately select the active default schedule used when calculating the business metrics for the downstream zendesk__ticket_metrics and zendesk__sla_policies models.
  • Previously the model could possibly select a deleted schedule. This update ensures only an active schedule is selected.

Documentation Updates

• Updated "Zendesk" references within the README to now refer to "Zendesk Support" in order to more accurately reflect the name of the Fivetran Zendesk Support Connector.
• Added new entries to the DECISIONLOG to highlight nuances and opinionated stances this package uses when calculating business metrics and first_reply_time SLAs.

Full Changelog: v0.13.1...v0.14.0

v0.13.1 dbt_zendesk

PR #128 includes the following changes:

Bug Fixes

• The int_zendesk__schedule_spine model was updated to properly account for schedules that recognized daylight savings time (DST) at one point in time, and then stopped recognizing it at a later date.
  • For example, the Hong Kong timezone originally recognized DST, but then stopped in 1979. The previous versions of this package only recorded the schedule business hours until 1979. This update addresses this bug.
  • Please note, this update will only effect users leveraging schedules.

Under the Hood

• Included auto-releaser GitHub Actions workflow to automate future releases.
• Updated the maintainer PR template to resemble the most up to date format.
• Included a quickstart.yml file to allow for automated Quickstart data model deployments.

Contributors

• @elijamuel (Issue #121)

Full Changelog: v0.13.0...v0.13.1

v0.13.0 dbt_zendesk

🚨 Breaking Change (Snowflake users) 🚨

• We have changed our identifier logic in the initial Zendesk source package to account for group being both a Snowflake reserved word and a source table. Given dbt_zendesk_source is a dependency for this package, Snowflake users will want to execute a dbt run --full-refresh before using the new version of the package. PR #42

Bug Fixes

• Updates the int_zendesk__schedule_spine model to convert the Holiday schedules into proper UTC values before being used in comparison with the schedule times. This ensures the holidays are properly mapped to schedules regardless of timezones. (PR #126)

🚀 Feature Updates 🚀

• Added solve_time_in_calendar_minutes and solve_time_in_business_minutes to our zendesk__ticket_metrics model, which calculates calendar and business minutes for when the ticket was in the 'new', 'open', 'hold', or 'pending' status. (PR #123)

🔎 Under the Hood 🔎

• Updated the seed files and seed file configurations for the package integration tests to align with changes in dbt_zendesk_source made in PR #42 for applying the dbt_utils.star macro.
• Corrected the folder structure for the .github folder to properly categorize the Community and Maintainer PR templates. (PR #126)

Contributors

• @fivetran-jessicacherny (Issue #119)
• @cth84 (PR #118)

Full Changelog: v0.12.0...v0.13.0

v0.12.0 dbt_zendesk

This release includes fixes to issues introduced in v0.11.0-v0.11.1 surrounding the incorporation of schedule holidays.

Special thanks to @cth84 and @nschimmoller for working with us to figure out some seriously tricky bugs!

Bug Fixes

• Adjusted the gap-merging logic in int_zendesk__schedule_spine to look forward in time instead of backward. This allows the model to take Daylight Savings Time into account when merging gaps. Previously, schedule periods with different start_time_utcs (because of DST) were getting merged together (PR #114).
  • Also removed the double_gap logic as it was rendered unnecessary by the above change.
• In all of our intermediate business hour models, adjusted the join logic in the intercepted_periods CTE, where we associate ticket weekly periods with the appropriate business schedule period. Previously, we did so by comparing the ticket's status_valid_starting_at and status_valid_ending_at fields to the schedule's valid_from and valid_until dates. This was causing fanout in certain cases, as we need to take the ticket-status's week_number into account because it is part of the grain of the CTE we are joining (PR #114).
• Adjusted the way we calculate the end of holidays in int_zendesk__schedule_spine. Previously, we calculated the end of holiday day by adding 24*60*60-1 seconds (making the end the last second of the same day) to the start of the holiday. This previously worked because our downstream joins for calculating business metrics were inclusive (ie >= instead of >). We've updated these joins to be exclusive (ie > or <), so we've set the end of the holiday to truly be the end of the day instead of a second prior (PR #114).
• Updated int_zendesk__requester_wait_time_filtered_statuses to include the hold status, as zendesk updated on-hold to just hold (PR #114).
• Updates the logic in int_zendesk__reply_time_combined to bring through the correct sla_event_id records to the end zendesk__sla_policies model. (PR #108)
  • Originally, duplicate sla_event_id records were being persisted because the upstream filtered_reply_times CTE did not include for all scenarios. With this update, the CTE will filter for the following scenarios:
    • Ticket is replied to between a schedule window
    • Ticket is replied to before a schedule window and no business minutes have been spent on it
    • Ticket is not replied to and therefore active. But only bring through the active SLA record that is most recent (after the last SLA schedule starts but before the next)
• Updated the ordering within the int_zendesk__comments_enriched model logic to also take into account when two comments are posted at the exact same time. Previously, the next comment would be picked arbitrarily. However, we now use the commenter_role as the tie breaker giving preference to the end-user as they will likely be the first commenter when two comments are posted at the exact same time. (PR #114)
• Modified the requester and agent wait time sla_elapsed_time metric calculations within the zendesk__sla_policies to capture the max running_total_scheduled_minutes record as opposed to the cumulative sum. Max more accurately represents the upstream data as it is presented in a rolling sum in the previous intermediate models. (PR #114)

Dependency Updates

• The dbt-date dependency has been updated to reflect the recommended latest range, [">=0.9.0", "<1.0.0"]. This will help to avoid upstream dependency conflicts. (PR #113)

Contributors:

• @nschimmoller
• @cth84

Full Changelog: v0.11.2...v0.12.0

v0.11.2 dbt_zendesk

Rollback

This PR #110 is a rollback to v0.10.2. We are seeing issues in business minutes and SLA duplicate records following the v0.11.0 release. While the team is looking into this, please use the rollback in the meantime.

Full Changelog: v0.11.1...v0.11.2

v0.11.1 dbt_zendesk

Tiny release ahead!

Under the Hood:

• Removes whitespace-escaping from Jinja code in int_zendesk__field_history_scd. In different whitepace parsing environments, this can jumble code up with SQL comments (PR #106).

Contributors:

• @bcolbert978 (PR #106)

Full Changelog: v0.11.0...v0.11.1

v0.11.0 dbt_zendesk

Feature Updates:

• Added support of the new schedule_holiday table in the schedule_spine intermediate model in order to properly capture how holidays impact ticket schedules and their respective SLAs. (PR #98)
• Made relevant downstream changes within the following models to capture proper business hour metrics when taking into account holiday schedules: (PR #98)
  • int_zendesk__agent_work_time_business_hours
  • int_zendesk__reply_time_business_hours
  • int_zendesk__reply_time_combined
  • int_zendesk__requester_wait_time_business_hours
  • zendesk__sla_policies
• Added open_status_duration_in_business_minutes and new_status_duration_in_business_minutes columns to the int_zendesk__ticket_work_time_business and zendesk__ticket_metrics models. These are counterparts to the already existing open_status_duration_in_calendar_minutes and new_status_duration_in_calendar_minutes columns. (PR #97)

Fixes:

• Added coalesce to 0 statements to the following fields in the zendesk__ticket_metrics model. This is necessary as some tickets may have responses entirely outside of business hours which will not count towards business minute metrics. As such, a coalesce to 0 is more representative to the metric as opposed to a null record: (PR #103)
  • first_resolution_business_minutes
  • full_resolution_business_minutes
  • first_reply_time_business_minutes
  • agent_wait_time_in_business_minutes
  • requester_wait_time_in_business_minutes
  • agent_work_time_in_business_minutes
  • on_hold_time_in_business_minutes
• Fixed the total_agent_replies field in zendesk__ticket_metrics so the value is derived from public agent comments logic, and also ignores ticket creation comments from an agent, matching the Zendesk definition. (PR #102)

Under the Hood:

• Leveraged dbt_date.week_start in place of dbt.date_trunc for business hour metrics to more consistently capture the start of the week across warehouses. (PR #98)
• Start of the week is now consistently set to Sunday. (PR #98)
• Incorporated the new fivetran_utils.drop_schemas_automation macro into the end of each Buildkite integration test job. (PR #98)
• Updated the pull request templates. (PR #98)

Contributors:

• @Tim-Hoare (PR #97)

Full Changelog: v0.10.2...v0.11.0

v0.10.2 dbt_zendesk

PR #101 includes the following updates:

Fixes

• Updated the group variable in the dbt_project.yml to have properly closed quotes within the variable declaration.
• Adjusted the in_zendesk__calendar_spine to set the return result of dbt.current_timestamp_backcompat() as a variable. This ensures that when the variable is being called within the model it can properly establish a dependency within the manifest.

Full Changelog: v0.10.1...v0.10.2