Our schedule-aware metric should only capture the non-grey time: Let’s assume that your customer support team always works Monday to Friday, and from 8am to 8pm, and your schedule looks something like this:Īnd let’s say you have a few tickets that come in, and your team works through them diligently, as always: This approach works great for the case where we have a standard business schedule, but falls flat when we want to bring in more complex, real world applications. Specifically, as we diagram below, we needed a way to model non-working time to properly remove it from a standard date_diff calculation. Our first approach to calculating Time on Task relied upon tying together a series of macros. The one size fits all solution: nested macros We’ve put together a series of questions to ask yourself to make sure that you are optimizing Time on Task for the problems it is best suited to solve. Time on Task can be a huge boon for operational reporting, but like any metric it has inherent strengths and weaknesses in terms of reflecting actual business value. You can find example code for each of these approaches in an example repo.Īfter we’ve walked through the mechanics of calculating Time on Task, we’ll spend some time thinking about how and why to use this metric in your reporting. non business hours in a fully customizable way. By using an hourly-grain date table, you are able to standardize your organization's unique definition of business hours vs. Our current production Time on Task calculation is able to be both powerful and flexible by bringing in a construct you rarely see at dbt Labs - a subquery. Bespoke and customizable with a subquery.This strategy does a great job in being able to account for nights, weekends and custom holidays, but lacks the flexibility to accommodate changes in business hours, so we've transitioned off of it to the 2nd option: This solution allowed us to create a one-line dbt macro to account for most common Time On Task use cases by having a series of nested macros behind the scenes. Two strategies to calculate Time on Task This piece will provide an overview of how and critically why to calculate Time on Task and how we use it here at dbt Labs. Accommodating for changes in business hour schedules. ![]() ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |