Custom throughput time metrics

Introduction

With customizing data transformations and dashboard editing you can create and use custom throughput time metrics. Throughput times are the timings between two activities A and B. Below is a description of the steps you need to perform to create a custom throughput time metric when editing transformations and how to enable the througput time metric in the process app dashboards.

Creating a custom throughput time metric with editing transformations

You must first caclulate the throughput time and then make it available as a Case field.

Calculating the throughput time

Per case, you can compute throughput times between Activity A and Activity B. Since activities can occur more than once per case, you need to take into account whether you take the first or last occurrence of an activity.

Create an additional model based on the Event log to calculate the desired throughput times. For example, Cases_with_throughput_times.
In this model, create pre-processing tables in which you define which event ends you want to use for the computations. Per table, you need the Case ID, and the Event end of an activity. Below is an example of how to select the last occurrence of activity A for a case.
```
Event_end_activity_A as (
    select
        Event_log."Case_ID",
        max(Event_log."Event_end") as "Event_end_activity_A"
    from Event_log
    where Event_log."Activity" = 'Activity A'
    group by Event_log."Case_ID")Event_end_activity_A as (
    select
        Event_log."Case_ID",
        max(Event_log."Event_end") as "Event_end_activity_A"
    from Event_log
    where Event_log."Activity" = 'Activity A'
    group by Event_log."Case_ID")
```
Note:
In this example, if you want to select the first occurrence of the activity, replace max with min.

Define the throughput time table by joining the pre-processing tables to the Event log and calculating the actual throughput time.

Tip:

You can use the datediff function provided in the pm-utils package to compute the time difference between any two event ends.

The throughput time should be computed in milliseconds for the instances where Activity A precedes Activity B. Milliseconds are the unit of time used to define durations in the app template. As the throughput times are already grouped per case in the pre-processing tables, you can pick any record. In the above example he aggregation min is used. The throughput time table, selecting the throughput time and a Case ID, can be defined as displayed below.

Cases_with_throughput_times as (
    select
        Event_log."Case_ID",
        case
            when min(Event_end_activity_A."Event_end_activity_A") <= min(Event_end_activity_B."Event_end_activity_B")
                then {{ pm_utils.datediff('millisecond',
                'min(Event_end_activity_A."Event_end_activity_A")',
                'min(Event_end_activity_B."Event_end_activity_B")') }}
        end as "Throughput_time_activity_A_to_activity_B"
    from Event_log
    left join Event_end_activity_A
        on Event_log."Case_ID" = Event_end_activity_A."Case_ID"
    left join Event_end_activity_B
        on Event_log."Case_ID" = Event_end_activity_B."Case_ID"
    group by Event_log."Case_ID)"Cases_with_throughput_times as (
    select
        Event_log."Case_ID",
        case
            when min(Event_end_activity_A."Event_end_activity_A") <= min(Event_end_activity_B."Event_end_activity_B")
                then {{ pm_utils.datediff('millisecond',
                'min(Event_end_activity_A."Event_end_activity_A")',
                'min(Event_end_activity_B."Event_end_activity_B")') }}
        end as "Throughput_time_activity_A_to_activity_B"
    from Event_log
    left join Event_end_activity_A
        on Event_log."Case_ID" = Event_end_activity_A."Case_ID"
    left join Event_end_activity_B
        on Event_log."Case_ID" = Event_end_activity_B."Case_ID"
    group by Event_log."Case_ID)"

Calculating throughput time in days excluding weekends

You can use the date_from_timestamp function provided in the pm-utils package to compute the number of days between two activities. Additionally, the diff_weekdays function enables you to filter out weekend days.

See the code example below for how to calculate the number of weekdays between Activity A and Activity B.

with Event_log as (
    select * from {{ ref('Event_log') }}
),

Activity_A as (
    select
        Event_log."Case_ID",
        min({{ pm_utils.date_from_timestamp('Event_log."Event_end"') }}) as "Date_activity_A"
    from Event_log
    where Event_log."Activity" = 'Receive invoice'
    group by Event_log."Case_ID"
),

Activity_B as (
    select
        Event_log."Case_ID",
        min({{ pm_utils.date_from_timestamp('Event_log."Event_end"') }}) as "Date_activity_B"
    from Event_log
    where Event_log."Activity" = 'Pay invoice'
    group by Event_log."Case_ID"
),

Total_days_minus_weekends as (
    select
        Activity_A."Case_ID",
        Activity_A."Date_activity_A",
        Activity_B."Date_activity_B",
        {{ pm_utils.diff_weekdays('Activity_A."Date_activity_A"', 'Activity_B."Date_activity_B"') }}
    -- Only compute for cases where both dates are known.
    from Activity_A
    inner join Activity_B
        on Activity_A."Case_ID" = Activity_B."Case_ID"
)

select * from Total_days_minus_weekendswith Event_log as (
    select * from {{ ref('Event_log') }}
),

Activity_A as (
    select
        Event_log."Case_ID",
        min({{ pm_utils.date_from_timestamp('Event_log."Event_end"') }}) as "Date_activity_A"
    from Event_log
    where Event_log."Activity" = 'Receive invoice'
    group by Event_log."Case_ID"
),

Activity_B as (
    select
        Event_log."Case_ID",
        min({{ pm_utils.date_from_timestamp('Event_log."Event_end"') }}) as "Date_activity_B"
    from Event_log
    where Event_log."Activity" = 'Pay invoice'
    group by Event_log."Case_ID"
),

Total_days_minus_weekends as (
    select
        Activity_A."Case_ID",
        Activity_A."Date_activity_A",
        Activity_B."Date_activity_B",
        {{ pm_utils.diff_weekdays('Activity_A."Date_activity_A"', 'Activity_B."Date_activity_B"') }}
    -- Only compute for cases where both dates are known.
    from Activity_A
    inner join Activity_B
        on Activity_A."Case_ID" = Activity_B."Case_ID"
)

select * from Total_days_minus_weekends

Calculating throughput time in days excluding holidays

Follow the steps below to calculate the throughput time in days between Activity A and Activity B excluding weekend days and holidays.

1. Create a Holidays.csv file to define the days that should be counted as holidays. The file should at least contain a record for each holiday. Use the following format:

Holiday	Date	Weekday
New year's day	2024-01-01	Yes
Easter	2024-03-31	No
..	..	..

The records in the Holidays.csv file are used to count the number of days that need to be excluded exclude from a date range.

2. Load the Holidays.csv file as a seed file in the dbt project. For detailed information, see the official dbt documentation on jinja seeds.

3. Calculate the throughput time in days excluding weekends using the date_from_timestamp and the diff_weekdays functions provided in the pm-utils package as described above in Calculating throughput time in days excluding weekends.

4. Calculate the number of records that are stored in the holidays .csv file that fall within the given date range for each case. See the example code below.

Holidays_count as (
    select
        Total_days_minus_weekends."Case_ID",
        count(Holidays."Date") as "Number_of_holidays"
    from Total_days_minus_weekends
    left join Holidays
        on Holidays."Date" between Total_days_minus_weekends."Date_activity_A" and Total_days_minus_weekends."Date_activity_B"
    where Holidays."Weekday" = 'Yes'
    group by Total_days_minus_weekends."Case_ID"
)Holidays_count as (
    select
        Total_days_minus_weekends."Case_ID",
        count(Holidays."Date") as "Number_of_holidays"
    from Total_days_minus_weekends
    left join Holidays
        on Holidays."Date" between Total_days_minus_weekends."Date_activity_A" and Total_days_minus_weekends."Date_activity_B"
    where Holidays."Weekday" = 'Yes'
    group by Total_days_minus_weekends."Case_ID"
)

Note:

In the above example, the filter Weekday = 'Yes' is used to not subtract holidays when the holiday is on a Saturday or a Sunday. This is already taken care of in the diff_weekday function.

5. Subtract the computed number of holidays from the total number of days computed for each case. See the example code below.

Total_days_minus_weekends_and_holidays as (
    select
        Total_days_minus_weekends."Case_ID",
        Total_days_minus_weekends."Number_of_days" - Holidays_count."Number_of_holidays" as "Number_of_days_between_dates"
    from Total_days_minus_weekends
    inner join Holidays_count
        on Total_days_minus_weekends."Case_ID" = Holidays_count."Case_ID"
)Total_days_minus_weekends_and_holidays as (
    select
        Total_days_minus_weekends."Case_ID",
        Total_days_minus_weekends."Number_of_days" - Holidays_count."Number_of_holidays" as "Number_of_days_between_dates"
    from Total_days_minus_weekends
    inner join Holidays_count
        on Total_days_minus_weekends."Case_ID" = Holidays_count."Case_ID"
)

Making the throughput time available as case field

Once the throughput time table is created this table needs to be joined to the Cases table to add the additional throughput time data as case information. To have the new throughput time field available in the dashboards, it is necessary to cast the new throughput time field to one of the custom case duration fields.

Replace one of the custom case duration lines in the Cases table that look like this:

{{ pm_utils.optional(ref('Cases_base'), '"custom_case_duration_1"', 'integer') }} as "custom_case_duration_1",{{ pm_utils.optional(ref('Cases_base'), '"custom_case_duration_1"', 'integer') }} as "custom_case_duration_1",

with the newly created throughput time:

Cases_with_throughput_times."Throughput_time_activity_A_to_activity_B" as "custom_case_duration_1",Cases_with_throughput_times."Throughput_time_activity_A_to_activity_B" as "custom_case_duration_1",

The updates to the transformations for the custom throughput time metric are now done and can be imported into the app template.

Enabling the throughput time metric in the process app dashboards

When you have created a custom throughput time in your transformations it is available in your app template as a case property under its alias. You can customize your process app to create a throughput time metric based on the custom throughput time you created in the transformations.

Note:

By default, a new custom duration field is added as a field of type numeric. Make sure to edit the field and change the Type of the new field to duration. See also Data manager.

Go to Data manager and create a new metric.
Select the custom duration field to be used for the throughput time, and select Average or any other desired aggregation. You can also rename the custom case duration field to your desired name in the Data Manager.
Edit the application and put the new metric on the charts where you want to make it available for business users.
Publish the dashboards to make the throughput time metric available on the dashboards.

Note:

In Purchase-to-Pay and Order-to-Cash app templates a throughput time calculation is already available in the Purchase_order_items_with_throughput_times and Sales_order_items_with_throughput_times, respectively. Custom throughput times can be added there and then be made available as a custom duration on the Purchase_order_items or Sales_order_items.