The Data API enables the creation of custom dashboards in your BI tools. Base tables such as conversations, interactions, plugin calls, plugin resources, and users allow you to build dashboards, which are presented by Moveworks under Copilot Insights with more granular access controls, or to build your own custom dashboards.

This guide focuses on creating metrics using the Data API and teaches you how to use SQL queries to build metrics on top of the data ingested through the Data API.

Building knowledge insights through Data API

📘

We want to build out a dashboard which allows us to understand how knowledge base plugin is being used in AI Assistant and achieve the following goals

1. Provide insights to our COE team on what is being served through AI Assistant.
2. Learn user feedback on the served content.
3. Understand gaps and areas where new content needs to be created.

1. Knowledge content performance

Let's consider the use case where we need to share the performance of knowledge content with the Center of Excellence team. The inputs the team requires are:

1. Knowledge content name
2. Number of times it was cited
3. Number of times it was rated unhelpful
4. Number of times a ticket was filed when this content was cited

To obtain these details, we need to examine the plugin resources and the interaction table. Assuming all data is ingested into your database, we need to fetch the knowledge content name and the count of conversations where this knowledge was cited from the plugin resources table. Additionally, we must access the interactions table to identify conversations where the cited resource was rated as helpful/unhelpful or where a ticket was filed by the end user.

SELECT
    JSON_VALUE(PR.data_resource_details, '$.name') AS knowledge,
    COUNT(DISTINCT PR.interaction_id) AS cited,
    COUNT(DISTINCT CASE
        WHEN IT.label = 'feedback'
          AND JSON_VALUE(IT.details, '$.detail') = 'HELPFUL'
        THEN IT.conversation_id
    END) AS helpful,
    COUNT(DISTINCT CASE
        WHEN IT.label = 'feedback'
          AND JSON_VALUE(IT.details, '$.detail') = 'UNHELPFUL'
        THEN IT.conversation_id
    END) AS nothelpful,
    COUNT(DISTINCT CASE
        WHEN (IT.label = 'file_ticket' OR IT.label = 'handoff')
          AND JSON_VALUE(IT.details, '$.detail') = 'File IT Ticket'
        THEN IT.conversation_id
    END) AS tickets
FROM DATA_API.DATA_API_PLUGIN_RESOURCES PR
LEFT JOIN DATA_API.DATA_API_INTERACTIONS IT
    ON PR.conversation_id = IT.conversation_id
WHERE PR.type = 'RESOURCE_TYPE_KNOWLEDGE'
  AND PR.cited = TRUE
  AND PR.last_updated_time >= '2025-05-21T00:00:00.000Z'
GROUP BY knowledge
ORDER BY cited DESC;

The above query provides us with a table view which lists down all the knowledge content name and metrics related to its performance. This can be shared with the COE to understand the performance of the cited items. You can recreate the same view in your powerBI by ingesting the data provided by this query in your environment.

2. Total number of conversations where Knowledge base plugin was served successfully.

Let’s understand the query that is required for us to build this metric. We need to find the total number of conversations where knowledge plugin was Used and distribute this data based on date.

These are the following columns that we need to use

• Conversation ID
• Plugin name
• Used
• Created time for conversation

The following SQL query creates a table view which can be used in PowerBI to create a trend chart.

SELECT 
    TO_DATE(CT.CREATED_DATE), 
    COUNT(CT.CONVERSATION_ID) AS CONVERSATION_COUNT
FROM 
    DATA_API.PROD.CONVERSATIONS CT
LEFT JOIN 
    DATA_API.PROD.PLUGIN_CALLS PC
ON 
    CT.CONVERSATION_ID = PC.CONVERSATION_ID
WHERE 
    PC.PLUGIN_NAME = 'Knowledge Base' 
    AND PC.USED = TRUE
GROUP BY 
    TO_DATE(CT.CREATED_AT);

• Please edit the SQL queries according to the table and field name you have created in your data lake.

The result of this query should look like below

You can define this SQL query while connecting PowerBI with Snowflake

Let’s select a trend chart and plot the date on x-axis and count of conversations on the y-axis. This will provide us a trend chart to view to see conversations where knowledge was server per date.

3. Knowledge Gaps

When a knowledge base plugin is considered but not served during a conversation, it is identified as a knowledge gap. This is specifically a prescriptive knowledge gap, although there may be instances where another plugin could provide the correct answer to the user's request.

The raw data via the Data API enables us to construct both a prescriptive knowledge gap, as displayed on the knowledge insights dashboard, and modify our query to include custom logic. In this scenario, we will consider a knowledge gap only when the smart handoff plugin is served, and the knowledge plugin is considered but not served.

We can achieve this by using the plugin calls table. The "served" attribute provides a clear indication of whether a plugin was served to the end user or not. In a scenario where knowledge was considered but not served, there will be an entry in the plugin calls table with the plugin name as "knowledge base" and "served" marked as false.

Select conversation_id from DATA_API.DATA_API_PLUGIN_CALLS 
where plugin_name = 'Knowledge Base' and served = false

We can also define the additional filter like considering only the scenarios where smart handoff plugin was served.

Select * from DATA_API.DATA_API_PLUGIN_CALLS as pc1
JOIN DATA_API.DATA_API_PLUGIN_CALLS  as pc2 on pc1.conversation_id = pc2.conversation_id
where (pc1.PLUGIN_NAME = 'Knowledge Base' and pc1.served = false) and (pc2.PLUGIN_NAME = 'Smart Handoff' and pc2.served = true)

We can also fetch additional details such as the user message and the topic in order to understand the knowledge gaps in more detail

SELECT
    pc1.conversation_id,
    JSON_VALUE(IT.details,'$.entity')  AS topic,
    JSON_VALUE(IT.details,'$.content')AS user_message
FROM
    /* ── Knowledge-Base call, one row per conversation ─────────────── */
    ( SELECT *
      FROM DATA_API.DATA_API_PLUGIN_CALLS
      WHERE dim_plugin_name  = 'Knowledge Base'
        AND is_plugin_served = FALSE
      QUALIFY ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_time DESC) = 1
    ) AS pc1

JOIN
    /* ── Smart-Handoff call, one row per conversation ──────────────── */
    /* you can choose to remove this entry to define Knowledge GAP */
    ( SELECT *
      FROM DATA_API.DATA_API_PLUGIN_CALLS
      WHERE dim_plugin_name  = 'Smart Handoff'
        AND is_plugin_served = TRUE
      QUALIFY ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_time DESC) = 1
    ) AS pc2
  ON pc1.conversation_id = pc2.conversation_id

JOIN
    ( SELECT *,
             ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_time  DESC) AS rn
      FROM DATA_API.DATA_API_INTERACTIONS
      WHERE type = 'INTERACTION_TYPE_FREE_TEXT'
        AND actor = 'user'
    ) AS IT
  ON pc1.conversation_id = IT.conversation_id
WHERE IT.rn = 1;      

We have joined the plugin calls table with the interaction table to get more details on where topic , conversation ID and the user message where the knowledge plugin failed to trigger.

This gives the following view, we can use this to create a table in PowerBI or tableau

3. User feedback when knowledge base plugin was served

User feedback is captured as an UI form interaction whenever an end user has submitted the feedback. To understand what user feedback was provided when a knowledge plugin is served we need to find conversations with feedback UI form submission and knowledge plugin call.

Query to fetch feedback type and free-text verbiage.

SELECT 
    PARSE_JSON(IT.DETAILS):content AS Feedback_content, 
    PARSE_JSON(IT.DETAILS):detail AS Feedback_type 
FROM 
    DATA_API.PROD.INTERACTIONS AS IT 
WHERE 
    IT.TYPE = 'INTERACTION_TYPE_UIFORM_SUBMISSION'
    AND IT.LABEL = 'feedback'
    AND IT.conversation_id IN (
        SELECT 
            pc.conversation_id 
        FROM 
            PROD.PLUGIN_CALLS AS pc 
        WHERE 
            pc.PLUGIN_NAME = 'Knowledge Base'
            AND pc.USED = true
    );

This query can be passed in the snowflake connector for powerBI and a table visualization can be made using this data.