Kaizen 255 Building a Real-Time Operational Dashboard with Zoho CRM Queries

Kaizen 255 Building a Real-Time Operational Dashboard with Zoho CRM Queries



Hello Everyone,

Welcome back to another edition of the Kaizen series, where we uncover powerful ways to extend and customize Zoho CRM.

In the previous Query Kaizens, we explored how Queries can retrieve CRM data, invoke REST APIs, and even update CRM records. In Kaizen #250, we demonstrated how a Kiosk used a Query to identify the most suitable Field Engineer and automatically assign that engineer to a Service Request during a Blueprint transition.

In this Kaizen, we'll use a Query serializer to transform the raw Query response into enriched business data by calculating additional fields at runtime. This demonstrates that serializers can do much more than rename columns. They can derive new values from existing CRM data and present actionable insights that go beyond what a standard report typically provides.

Quote

Business Scenario

Zylker Home Services receives hundreds of installation, maintenance, and repair requests every week. The Service Manager reviews ongoing Service Requests to monitor work progress, identify overdue jobs, and ensure Field Engineers are assigned effectively.
While Zoho CRM reports can display the available data, the manager often needs additional operational insights that are not stored in CRM. For example:
  1.  How many days has a request been overdue? 
  2.  Which requests should be treated as high priority? 
  3.  Has the service request breached its SLA? 
  4.  Does the request require immediate escalation? 
  5.  Is the target completion date falling on a weekend? 
These insights are derived by evaluating multiple fields, rather than by simply displaying existing CRM fields.
Instead of exporting data or manually interpreting reports, the Service Manager wants a single dashboard that not only displays Service Request information but also computes these decision-making indicators in real time, enabling quicker and more informed operational decisions.

Solution Overview

The dashboard is powered by a Query that retrieves CRM data and a serializer that computes operational indicators dynamically for every execution.

Idea

Architecture Overview

Presentation Layer

A single Kiosk is embedded on the Service Manager's CRM Home page. The Kiosk displays a tabular operational dashboard containing all Service Requests.

Data Access Layer

The Kiosk is powered by a Module Query on the Service Applications module.
The Query retrieves live CRM data, while the serializer transforms and enriches the response before it reaches the Kiosk by:
  1.  Renaming fields 
  2.  Computing overdue duration 
  3.  Deriving SLA status 
  4.  Determining request priority 
  5.  Identifying requests requiring escalation 
  6.  Flagging weekend target dates 
  7.  Replacing missing values with meaningful messages 

Data Layer

The dashboard reads live data directly from the Service Applications module and its related Field Engineer records, ensuring managers always view the latest CRM data without synchronizations or exports.

Implementation

Step 1: Create the Query

Navigate to: Setup → Developer Space → Queries

Configure the Query using the following settings to retrieve Service Application records for the operational dashboard.

Source: Zoho CRM → Module
Module: Service Applications
Fields:
  1.  Name 
  2.  Service Request Type 
  3.  Zone 
  4.  Target Completion Date 
  5.  Reason For Delay 
  6.  Field Engineer Name 
  7.  Field Engineer Status 
  8.  Field Engineer Specialization 
  9.  Field Engineer Phone 
No additional filtering is applied so that both assigned and unassigned Service Requests are returned.


Serializer

The serializer calculates these additional fields dynamically whenever the Query executes. They are recalculated each time the Home page loads rather than being retrieved as stored CRM fields. The serializer not only renames fields but also enriches the response by calculating additional operational information.
It derives: 
  1. Overdue By 
  2. Priority 
  3. SLA Status
  4. Escalation Required
  5. Weekend Warning
const today = new Date();

return result.map(record => {

    let overdueDays = null;
    let priority = "Not Available";
    let slaStatus = "Unknown";
    let escalationRequired = "Cannot Determine";
    let weekendWarning = "N/A";

    if (record.Target_Completion_Date) {

        const targetDate = new Date(record.Target_Completion_Date);

        overdueDays = Math.max(
            0,
            Math.floor((today - targetDate) / (1000 * 60 * 60 * 24))
        );

        // Priority
        if (overdueDays >= 15) {
            priority = "🔴 Critical";
        } else if (overdueDays >= 7) {
            priority = "🟠 High";
        } else if (overdueDays > 0) {
            priority = "🟡 Medium";
        } else {
            priority = "🟢 On Track";
        }

        // SLA Status
        if (overdueDays === 0) {
            slaStatus = "🟢 Within SLA";
        } else if (overdueDays <= 3) {
            slaStatus = "🟡 Approaching SLA";
        } else {
            slaStatus = "🔴 SLA Breached";
        }

        // Escalation Required
        escalationRequired =
            overdueDays > 7
                ? "🔴 Escalate Immediately"
                : "No";

        // Weekend Warning
        const day = targetDate.getDay();
        weekendWarning =
            (day === 0 || day === 6)
                ? "⚠ Weekend Target"
                : "Working Day";
    }

    return {
        "Service Request": record.Name,

        "Service Request Type": record.Service_Request_Type,

        "Zone": record.Zone,

        "Target Completion Date":
            record.Target_Completion_Date || "Date Not Provided",

        "Overdue By":
            overdueDays !== null
                ? (overdueDays > 0 ? overdueDays + " day(s)" : "Not Overdue")
                : "Date Not Provided",

        "SLA Status": slaStatus,

        "Priority": priority,

        "Escalation Required": escalationRequired,

        "Weekend Warning": weekendWarning,

        "Reason For Delay":
            record.Reason_For_Delay || "Not Provided",

        "Field Engineer Name":
            record["Field_Engineer.Name"]|| "Not Assigned",

        "Field Engineer Status":
            record["Field_Engineer.Status"]|| "Engineer Not assigned",

        "Field Engineer Specialization":
            record["Field_Engineer.Specialization"]|| "Engineer Not assigned",

        "Field Engineer Phone":
            record["Field_Engineer.Phone"] || "Phone Not Available"
    };
});

The following table summarizes the additional business insights derived by the serializer, describing how it is calculated.

Derived Field
How the Serializer Calculates It
Example Output
Overdue By
Calculates the difference between the current date (today) and the Target Completion Date. If the request is not overdue, it displays Not Overdue. If the target completion date is unavailable, it displays Date Not Provided.
12 days(s)
Not OverdueDate
Priority
Determines the priority based on the calculated Overdue By value. Requests overdue by 15 or more days are marked Critical, those overdue by 7–14 days are marked High, those overdue by 1–6 days are marked Medium, and requests that are not overdue are marked On Track.
🔴 Critical
🟠 High
🟡 Medium
🟢 On Track
SLA Status
Uses the calculated overdue duration to determine whether the request is Within SLA, Approaching SLA, or SLA Breached. If the target completion date is unavailable, the serializer returns Unknown.
🟢 Within SLA
🟡 Approaching SLA
🔴 SLA Breached
Escalation Required
Checks whether the request has been overdue for more than 7 days. If so, the serializer flags it for escalation; otherwise, it indicates that escalation is not required.
🔴 Escalate Immediately
Weekend Warning
Determines the day of the week from the Target Completion Date using getDay(). If the date falls on Saturday or Sunday, the serializer displays a warning; otherwise, it indicates a normal working day.
⚠ Weekend Target
Working Day
Null Value Handling
Replaces missing values with meaningful messages.
Not Provided 
Phone Not Available.

Step 2: Associate Queries with Kiosks

Navigate to Setup → Customization → Kiosk Studio and create a Kiosk.
  1.  Add the screen state. 
  2.  Choose Get data via Queries as the data element. 
  3.  Select the corresponding Query. 
  4.  Configure the table columns using the serializer output. 
  5.  Save and publish the Kiosk. 

Step3: Associate the Kiosk to Manager's Home Page

Navigate to Setup -> Customization -> Customize CRM Home.
  1.  Edit the Service Manager Home dashboard. 
  2.  Click Kiosk from the Dashboard Components. 
  3.  Drag and Drop the Kiosk created in Step2. Arrange the Kiosk according to your preferred layout. 
  4.  Save the dashboard. 

Service Manager Home Page

The Service Manager Home page displaying the Service Request Summary dashboard powered by Zoho CRM Queries and serializers.






Queries are more than a mechanism for retrieving CRM records. Combined with serializers, they become a lightweight presentation layer capable of enriching, transforming, and deriving business insights from live CRM data before it is displayed to users.

In this Kaizen, we built a real-time operational dashboard powered by a single Query. Rather than simply displaying stored CRM fields, the serializer calculated metrics providing Service Managers with actionable information the moment they log into CRM.
This demonstrates that Queries can go beyond traditional reporting by not only retrieving data but also preparing it for consumption in a way that supports faster and more informed operational decision-making.

We hope you found this post useful. 
We would love to hear from you! Write to us at support@zohocrm.com or let us know in the comments section.

Happy Querying!