v0.18.1 dbt_zendesk

PR #174 includes the following changes:

Bug Fix

• Addressed an issue in which some records in zendesk__sla_policies might erroneously have a null sla_policy_name due to system-generated millisecond-long gaps in timestamps. The package now compares timestamps to the nearest second when selecting valid SLA policy names in int_zendesk__sla_policy_applied.

Under the Hood

• Updated consistency_sla_policies and sla_count_match data validation tests to account for the above change.

Full Changelog: v0.18.0...v0.18.1

v0.19.0-a1 dbt_zendesk

PR #173 includes the following changes:

Breaking Changes

• Model zendesk__document is now enabled by default. To disable it, set the zendesk__unstructured_enabled variable to false in your dbt_project.yml.

Full Changelog: v0.18.0...v0.19.0-a1

v0.18.0 dbt_zendesk

PR #171 includes the following changes:

Breaking Changes (Full refresh required after upgrading)

Schedule Change Support

• Support for schedule changes has been added. This feature is disabled by default since most users do not sync the required source audit_table. To enable this feature set the variable using_schedule_histories to true in your dbt_project.yml:

vars:
  using_schedule_histories: true

• Schedule changes can now be extracted directly from the audit log, providing a view of schedule modifications over time.
• The int_zendesk__schedule_spine model is now able to incorporate these schedule changes, making it possible for downstream models to reflect the most up-to-date schedule data.
  • Note this is only in effect when using_schedule_histories is true.
• This improves granularity for Zendesk metrics related to agent availability, SLA tracking, and time-based performance analysis.

dbt_zendesk_source changes (see the Release Notes for more details)

• Introduced the stg_zendesk__audit_log table for capturing schedule changes from Zendesk's audit log.
  • This model is disabled by default, to enable it set variable using_schedule_histories to true in dbt_project.yml.

New Features

• Holiday support: Users can now choose to disable holiday tracking, while continuing to use schedules, by setting variable using_holidays to false in dbt_project.yml.
• New intermediate models have been introduced to streamline both the readability and maintainability:
  • int_zendesk__timezone_daylight: A utility model that maintains a record of daylight savings adjustments for each time zone.
    • materialization: ephemeral
  • int_zendesk__schedule_history: Captures a full history of schedule changes for each schedule_id.
    • materialization: table (if enabled)
  • int_zendesk__schedule_timezones: Merges schedule history with time zone shifts.
    • materialization: ephemeral
  • int_zendesk__schedule_holiday: Identifies and calculates holiday periods for each schedule.
    • materialization: ephemeral
• Rebuilt logic in int_zendesk__schedule_spine to consolidate updates from the new intermediate models.

dbt_zendesk_source changes (see the Release Notes for more details)

• Updated the stg_zendesk__schedule_holidays model to allow users to disable holiday processing by setting variable using_holidays to false.

Bug Fixes

• Resolved a bug in the int_zendesk__schedule_spine model where users experienced large gaps in non-holiday periods. The updated logic addresses this issue.

Decision log

• Added the following DECISIONLOG entries:
  • Entry addressing how multiple schedule changes in a single day are handled. Only the last change of the day is captured to align with day-based downstream logic.
  • Entry to clarify backfilling of schedule history. The most recent schedule is sourced from stg_zendesk__schedule, while historical changes are managed separately, allowing users to disable the history feature if needed.

Under the Hood

• Replaced instances of dbt.date_trunc with dbt_date.week_start to standardize week start dates to Sunday across all warehouses, since our schedule logic relies on consistent weeks.
• Replaced the deprecated dbt.current_timestamp_backcompat() function with dbt.current_timestamp() to ensure all timestamps are captured in UTC.
• Added seed data for audit_log to enhance integration testing capabilities.
• Introduced new helper macros, clean_data and regex_extract, to process complex text of the schedule changes extracted from audit logs.
• Updated int_zendesk__calendar_spine logic to prevent errors during compilation before the first full run, ensuring a smoother development experience.

Full Changelog: v0.17.0...v0.18.0

v0.17.0 dbt_zendesk

New model (#161)

• Addition of the zendesk__document model, designed to structure Zendesk textual data for vectorization and integration into NLP workflows. The model outputs a table with:
  • document_id: Corresponding to the ticket_id
  • chunk_index: For text segmentation
  • chunk: The text chunk itself
  • chunk_tokens_approximate: Approximate token count for each segment
• This model is currently disabled by default. You may enable it by setting the zendesk__unstructured_enabled variable as true in your dbt_project.yml.
  • This model was developed with the limit of chunk sizes to approximately 5000 tokens for use with OpenAI, however you can change this limit by setting the variable zendesk_max_tokens in your dbt_project.yml.
  • See the README section Enabling the unstructured document model for NLP for more information.

Breaking Changes (Full refresh required after upgrading)

• Incremental models running on BigQuery have had the partition_by logic adjusted to include a granularity of a month. This change only impacts BigQuery warehouses and was applied to avoid the common too many partitions error some users have experienced when partitioning by day. Therefore, adjusting the partition to a month granularity will decrease the number of partitions created and allow for more performant querying and incremental loads. This change was applied to the following models (#165):

  • int_zendesk__field_calendar_spine
  • int_zendesk__field_history_pivot
  • zendesk__ticket_field_history

• In the dbt_zendesk_source v0.12.0 release, the field _fivetran_deleted was added to the following models for use in zendesk__document model (#161):

  • stg_zendesk__ticket
  • stg_zendesk__ticket_comment
  • stg_zendesk__user
  • If you have already added _fivetran_deleted as a passthrough column via the zendesk__ticket_passthrough_columns or zendesk__user_passthrough_columns variable, you will need to remove or alias this field from the variable to avoid duplicate column errors.

Bug Fixes

• Fixed an issue in the zendesk__sla_policies model where tickets that were opened and solved outside of scheduled hours were not being reported, specifically for the metrics requester_wait_time and agent_work_time.
  • Resolved by adjusting the join logic in models int_zendesk__agent_work_time_business_hours and int_zendesk__requester_wait_time_business_hours. (#164, #156)
• Fixed an issue in the zendesk__ticket_metrics model where certain tickets had miscalculated metrics.
  • Resolved by adjusting the join logic in models int_zendesk__ticket_work_time_business, int_zendesk__ticket_first_resolution_time_business, and int_zendesk__ticket_full_resolution_time_business. (#167)

Under the hood

• Added integrity validations:
  • Test to ensure zendesk__sla_policies and zendesk__ticket_metrics models produce consistent time results. (#164)
  • Test to ensure zendesk__ticket_metrics contains all the tickets found in stg_zendesk__ticket. (#167)
• Modified the consistency_sla_policy_count validation test to group by ticket_id for more accurate testing. (#165)
• Updated casting in joins from timestamps to dates so that the whole day is considered. This produces more accurate results. (#164, #156, #167)
• Reduced the weeks looking ahead from 208 to 52 to improve performance, as tracking ticket SLAs beyond one year was unnecessary. (#156, #167)
• Updated seed files to reflect a real world ticket field history update scenario. (#165)

Full Changelog: v0.16.0...v0.17.0

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