---
title: Build custom analytics dashboards in BI tools
excerpt: ''
deprecated: false
hidden: false
metadata:
title: ''
description: ''
robots: index
next:
description: ''
---
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.
```sql
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
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.
```sql
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.
```sql
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.
```sql
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
```sql
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.**
```sql
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.
