What is the best way to save the conversation log and the associated user details such as email, user ID, and timestamp for each chat?

[juroy_lim]

I am developing a simple chatbot using Microsoft Copilot Studio and have deployed it in Teams. I am currently exploring how to store the entire chat history, along with user metadata such as email, user ID, and timestamp for each chat. 

I also wonder if there is a place that we can access to these data?

[Israel_Ramakuri]

Hi Juroy,

We had a similar requirement to push the chat history to teams channel if it hits escalation.

By default if the bot is pinned to teams, the individuals will be able to see their chat history.

But i am assuming that you want to see the entire chat history for all users.

We have a table inside Dataverse called 'ConversationTranscripts' which stores all the chat history. You can see the same by looking at Content field in that table. This information is kind of formatted. We need to apply some logic to make the text look simple.

You can use powerautomate dataverse connector to retrieve this information. Ofcourse this is a premium connector. 

As far as the user information is concerned, this information is available inside the bot as system variables and if required you should be able to store this information by triggering a powerautomate inside your topic and storing it in someplace like sharepoint list or can also publish this information to teams channel etc.

This whole approach works for Copilot Studio only and not for Copilot Studio for Teams. If you have a bot created using Copilot Studio then you are good.

Regards

Karun

[Murali2]

I'm using copilot studio with user login.

I would like to do the same. Retrieving conversation logs/chat history only for the logged in user.

Could you please share the scripts or a demo video on this.

[juroy_lim]

Do you mind writing a tutorial blog or recording a tutorial video on how you implement it?

[Israel_Ramakuri]

Initial Approach:

When we started looking for options, we could initially figure out that this information is stored in the Dataverse table called Conversation Transcripts. Each interaction is created as a record and the entire conversation is stored in a field called Content field. The idea is to use Power Automate in the Escalate Topic to retrieve this record from the Conversation Transcripts table using the Conversation ID and post the chat conversation in the Teams Channel, in case of an escalation.

Figure 1: Dataverse Tables accessible after navigating to Power Apps or Power Automate

Figure 2: Conversation Transcript (Stores Chat Conversations) Table in the Dataverse

Figure 3: Content field that stores the chat conversation

Figure 4: Raw data in the Content Field. Additional formatting to be done to extract the required information.

Inferences based on the initial approach:

• Significant delay in the record creation after the conversation ended. Record is not created for close to 30 mins.
• Use of Premium Connector for Dataverse in Power Automate to query the Conversation Transcripts Table adds additional cost.

The above points made us look for an alternative approach. We looked at Global Variables and we could successfully append the data to the global variable using Power Fx Formulas.

Our Approach:

The approach was based on Global Variables creation and appending the same across all the topics.

Below are high level steps as part of this approach.

1. Adding/Declaring Global Variable in Conversation Start Topic.
2. Appending the Global Variable with both the question and the user response in all the topics.
3. Use Global Variable within the Escalation Topic and trigger a PowerAutomate within the topic by passing this variable for the chat conversation to be posted to Teams Channel.

Benefits from this approach

1. Chat Conversation is available immediately in the Teams Channel in case of escalation.
2. Don’t need any premium connectors to access the Dataverse Conversation Transcripts Table.

Customizing Existing Inbuilt Topics

1. Conversation Start Topic:

This topic is triggered at the start of the conversation. Declared global variables in this Topic as below.

• BotName: Stores the name of the Chatbot.
• UserName: Stores the display name of the user interacting with the Chatbot.
• Mainvar: Stores details of the chat history.
• BreakLine: Used for new line in the PVA and Teams Chat (Post in Teams Channel).

Figure 5: Global Variables declared in Conversation Start Topic

1. Fallback Topic:

This topic is triggered when the Chatbot cannot match the user's question or message to a trigger phrase. It will prompt the user with the message:

I'm sorry, I'm not sure how to help with that. Can you try rephrasing?

If the user's question remains unanswered, it hits the Escalate Topic, where the user receives a customized the message:

I'm sorry, I couldn't help you with your query. However, I've forwarded your query to the IT Service Team. They will get in touch with you shortly.

Figure 6: Appending data to Global Variable.

1. Escalation Topic:

This topic is triggered when the bot is not able to answer the user query.

Figure 7: Input variables to Power Automate are passed here so the chat conversation is posted in Teams Channel

Figure 8: Power Automate to post the chat history to Teams Channel and to create a log in sharepoint list

Log entire chat history in sample topic:

• Create a topic IT Help desk.
• Set the global variable value at every node as below.

Figure 9: Sample Topic depicting the Global Variable Append operation.

Figure 10: Appending Name Question and User Response to Global Variable

Figure 11: Appending Age Question and User Response to Global Variable

Figure 12: Appending DOB Question and User Response to Global Variable

Figure 13: Sample Output of Chat Conversation posted to Teams Channel using PowerAutomate

Note:

• Global Variables used in the approach mentioned above has an overhead of updating the same in the new topics that will be created and if any updates are made to existing topics. This approach worked for us since the client requested us to capture only the last user query to which the bot couldn’t respond.
• The above approaches work only with Copilot Studio for Web and not in Copilot Studio for Teams.

Figure 14: Copilot Studio for Teams based Bot Actions

Copilot Studio for Teams has few limitations.

• It doesn't support the declaration of global variables.
• Data is not available for query within the Dataverse Conversation Transcripts table.