How to Track Survey Conversion Rate in Halo Using Custom Reports and Dashboards

Introduction

Tracking how many closed tickets receive customer feedback is essential to understanding survey adoption and improving response rates. This article walks you through how to create a Halo report and dashboard that help you visualize survey performance:

Prerequisites

Before starting, make sure:

• You have permission to create SQL reports in Halo
• Your Halo instance stores the survey response date in a custom field (for example, CFCrewhuFeedbackDate). Refer to this article: Halo PSA Integration - REST API – Crewhu
• Tickets are considered closed when status = 9 (adjust if your setup differs)

How to Create the Survey Conversion Rate Report

This report calculates:

• Total number of closed tickets
• Number of closed tickets with survey responses
• Survey conversion rate (%)

This is ideal for KPI tracking and leadership dashboards.

Step 1: Create a New SQL Report

1. Navigate to Reports
2. Click New Report
3. Select *Write a custom SQL query*
4. Name the report:
5. Survey Conversion Rate – Closed Tickets

Step 2: Add the SQL Query

Use the query below. You may adjust field names to match your Halo configuration.

SELECT
    COUNT(DISTINCT faults.faultid) AS TotalClosedTickets,
    SUM(
        CASE 
            WHEN faults.CFCrewhuFeedbackDate IS NOT NULL 
            THEN 1 
            ELSE 0 
        END
    ) AS TicketsWithSurvey,
    CAST(
        SUM(
            CASE 
                WHEN faults.CFCrewhuFeedbackDate IS NOT NULL 
                THEN 1 
                ELSE 0 
            END
        ) * 100.0 / COUNT(DISTINCT faults.faultid)
    AS DECIMAL(5,2)) AS ConversionRate
FROM faults
WHERE fdeleted = 0
  AND status = 9
  AND datecleared >= @StartDate
  AND datecleared <= @EndDate

Step 3: Use Date Parameters

Using @StartDate and @EndDate allows the report to:

• Adapt dynamically on dashboards
• Be reused across different time ranges

Make sure the parameters are enabled in the report settings.

Step 4: Validate the Results

This report should return one row with three columns:

• TotalClosedTickets
• TicketsWithSurvey
• ConversionRate

This format works best for KPI tiles and summary widgets.

Dashboard: Survey Conversion Rate KPI

1. Go to Dashboards
2. Create or edit a dashboard
3. Add a Report Widget
4. Type "Report Counter"
5. Select the Survey Conversion Rate – Closed Tickets report
6. Counter Type: Sum a Column
7. Column Name: ConversionRate
8. Adjust the color and other configurations about the appearance 

This provides an at-a-glance view of survey adoption.

How to Use These Reports

These reports and dashboards can be used to:

• Monitor survey adoption over time
• Identify teams or periods with low response coverage
• Validate survey automation and workflows
• Support internal coaching and performance discussions

Common Tips

• If your conversion rate is lower than expected, review when surveys are triggered in your ticket workflow
• Ensure surveys are only sent after tickets are fully resolved
• Use consistent closing statuses to avoid underreporting