Categorizing Data Using Bold Data Hub

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

Categorizing Data

Overview

If ticket descriptions or categories are unstructured or inconsistent, standardizing them into predefined categories can improve data clarity and reporting. Common categories include “Billing Issue,” “Technical Support,” and “General Inquiry.”

Approach

We use a CASE statement to categorize tickets based on the Ticket_Category and Issue_Description fields. This ensures uniform classification of ticket types for better analysis.

SQL Query for Categorizing Ticket Types

SELECT 
    Ticket_ID,
    Customer_ID,
    Customer_Name,
    Ticket_Allocation_Timestamp,
    Ticket_Status,
    Priority,
    Region,
    City,
    Country,
    Ticket_Cost,
    Phone,
    CASE 
        WHEN LOWER(Ticket_Category) LIKE '%billing%' THEN 'Billing Issue'
        WHEN LOWER(Ticket_Category) LIKE '%technical%' OR LOWER(Issue_Description) LIKE '%error%' THEN 'Technical Support'
        WHEN LOWER(Ticket_Category) LIKE '%general%' OR LOWER(Issue_Description) LIKE '%inquiry%' THEN 'General Inquiry'
        ELSE 'Other'
    END AS Standardized_Ticket_Category
FROM {pipeline_name}.sample_csc_data;