Track Ticket Count by Agent and Transforming Data Using Bold Data Hub

In this article, we will demonstrate how to import tables from a CSV file, check ticket count by agent 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

Learn more about transformation here

Ticket Count by Agent

Overview

Tracking the number of tickets resolved by each agent within specific time periods (daily, weekly) helps assess performance, identify workload distribution, and optimize resource allocation.

Approach

We aggregate ticket resolution counts:

• Daily Ticket Count → Grouping by Agent_ID and Ticket_Resolution_Date
• Weekly Ticket Count → Extracting the week number from Ticket_Resolution_Date

SQL Query for Daily Ticket Count

SELECT 
    Agent_ID, 
    Ticket_Resolution_Date, 
    COUNT(Ticket_ID) AS Tickets_Resolved 
FROM {pipeline_name}.sample_csc_data 
WHERE Ticket_Status = 'Resolved'
GROUP BY Agent_ID, Ticket_Resolution_Date 
ORDER BY Ticket_Resolution_Date, Agent_ID;

SQL Query for Weekly Ticket Count

SELECT 
    Agent_ID, 
    EXTRACT(week FROM Ticket_Resolution_Date) AS Resolution_Week, 
    COUNT(Ticket_ID) AS Tickets_Resolved 
FROM {pipeline_name}.sample_csc_data 
WHERE Ticket_Status = 'Resolved' 
GROUP BY Agent_ID, EXTRACT(week FROM Ticket_Resolution_Date) 
ORDER BY Resolution_Week, Agent_ID;