Computing Performance KPIs Using Bold Data Hub

In this article, we will demonstrate how to import tables from a CSV file, analyze the ticket resolution time summary through transformations, and move the cleaned data into the destination database using Bold Data Hub. Follow the step-by-step process below.

Sample Data Source:

Sample CSC Data

Creating Pipeline

Learn about Pipeline Creation

Applying Transformation

• Go to the Transform tab and click Add Table.

• Enter the table name to create a transform table for customer satisfaction summary.

Note: The data will initially be transferred to the DuckDB database within the designated {pipeline_name} schema before undergoing transformation for integration into the target databases. As an illustration, in the case of a pipeline named “customer_service_data”, the data will be relocated to the customer_service_data table schema.

Learn more about transformation here

Ticket Resolution Time Summary

Overview

Analyzing ticket resolution times per service category helps identify efficiency trends and potential areas for improvement. We calculate the average, minimum, and maximum resolution times for each ticket category.

Approach

We aggregate resolution time statistics for resolved tickets:

• Average Resolution Time → Mean time taken to resolve tickets
• Minimum Resolution Time → Fastest resolution recorded
• Maximum Resolution Time → Longest resolution duration

SQL Query for Ticket Resolution Time Summary

SELECT 
    Ticket_Category, 
    AVG("Resolution_Time (hrs)") AS Avg_Resolution_Time, 
    MIN("Resolution_Time (hrs)") AS Min_Resolution_Time, 
    MAX("Resolution_Time (hrs)") AS Max_Resolution_Time 
FROM {pipeline_name}.sample_csc_data 
WHERE Ticket_Status = 'Resolved' 
GROUP BY Ticket_Category 
ORDER BY Ticket_Category;