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

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.

1
SELECT
2
    JSON_VALUE(PR.data_resource_details, '$.name') AS knowledge,
3
    COUNT(DISTINCT PR.interaction_id) AS cited,
4
    COUNT(DISTINCT CASE
5
        WHEN IT.label = 'feedback'
6
          AND JSON_VALUE(IT.details, '$.detail') = 'HELPFUL'
7
        THEN IT.conversation_id
8
    END) AS helpful,
9
    COUNT(DISTINCT CASE
10
        WHEN IT.label = 'feedback'
11
          AND JSON_VALUE(IT.details, '$.detail') = 'UNHELPFUL'
12
        THEN IT.conversation_id
13
    END) AS nothelpful,
14
    COUNT(DISTINCT CASE
15
        WHEN (IT.label = 'file_ticket' OR IT.label = 'handoff')
16
          AND JSON_VALUE(IT.details, '$.detail') = 'File IT Ticket'
17
        THEN IT.conversation_id
18
    END) AS tickets
19
FROM DATA_API.DATA_API_PLUGIN_RESOURCES PR
20
LEFT JOIN DATA_API.DATA_API_INTERACTIONS IT
21
    ON PR.conversation_id = IT.conversation_id
22
WHERE PR.type = 'RESOURCE_TYPE_KNOWLEDGE'
23
  AND PR.cited = TRUE
24
GROUP BY knowledge
25
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.

1
SELECT 
2
    TO_DATE(CT.CREATED_DATE), 
3
    COUNT(CT.CONVERSATION_ID) AS CONVERSATION_COUNT
4
FROM 
5
    DATA_API.PROD.CONVERSATIONS CT
6
LEFT JOIN 
7
    DATA_API.PROD.PLUGIN_CALLS PC
8
ON 
9
    CT.CONVERSATION_ID = PC.CONVERSATION_ID
10
WHERE 
11
    PC.PLUGIN_NAME = 'Knowledge Base' 
12
    AND PC.USED = TRUE
13
GROUP BY 
14
    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.

1
Select conversation_id from DATA_API.DATA_API_PLUGIN_CALLS 
2
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.

1
Select * from DATA_API.DATA_API_PLUGIN_CALLS as pc1
2
JOIN DATA_API.DATA_API_PLUGIN_CALLS  as pc2 on pc1.conversation_id = pc2.conversation_id
3
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

1
SELECT
2
    pc1.conversation_id,
3
    JSON_VALUE(IT.details,'$.entity')  AS topic,
4
    JSON_VALUE(IT.details,'$.content')AS user_message
5
FROM
6
    /* ── Knowledge-Base call, one row per conversation ─────────────── */
7
    ( SELECT *
8
      FROM DATA_API.DATA_API_PLUGIN_CALLS
9
      WHERE dim_plugin_name  = 'Knowledge Base'
10
        AND is_plugin_served = FALSE
11
      QUALIFY ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_time DESC) = 1
12
    ) AS pc1
13
14
JOIN
15
    /* ── Smart-Handoff call, one row per conversation ──────────────── */
16
    /* you can choose to remove this entry to define Knowledge GAP */
17
    ( SELECT *
18
      FROM DATA_API.DATA_API_PLUGIN_CALLS
19
      WHERE dim_plugin_name  = 'Smart Handoff'
20
        AND is_plugin_served = TRUE
21
      QUALIFY ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_time DESC) = 1
22
    ) AS pc2
23
  ON pc1.conversation_id = pc2.conversation_id
24
25
JOIN
26
    ( SELECT *,
27
             ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_time  DESC) AS rn
28
      FROM DATA_API.DATA_API_INTERACTIONS
29
      WHERE type = 'INTERACTION_TYPE_FREE_TEXT'
30
        AND actor = 'user'
31
    ) AS IT
32
  ON pc1.conversation_id = IT.conversation_id
33
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.

1
SELECT 
2
    PARSE_JSON(IT.DETAILS):content AS Feedback_content, 
3
    PARSE_JSON(IT.DETAILS):detail AS Feedback_type 
4
FROM 
5
    DATA_API.PROD.INTERACTIONS AS IT 
6
WHERE 
7
    IT.TYPE = 'INTERACTION_TYPE_UIFORM_SUBMISSION'
8
    AND IT.LABEL = 'feedback'
9
    AND IT.conversation_id IN (
10
        SELECT 
11
            pc.conversation_id 
12
        FROM 
13
            PROD.PLUGIN_CALLS AS pc 
14
        WHERE 
15
            pc.PLUGIN_NAME = 'Knowledge Base'
16
            AND pc.USED = true
17
    );

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