Skip to main content

Notifications

Building Copilot Studio Chatbots in Microsoft T...
Unanswered

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?

Posted on by 5

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 Profile Picture
    Israel_Ramakuri 9 on at
    Re: 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?

    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.

     

    Israel_Ramakuri_0-1713770795302.png

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

     

    Israel_Ramakuri_1-1713770795309.png

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

     

    Israel_Ramakuri_2-1713770795313.png

    Figure 3: Content field that stores the chat conversation

     

    Israel_Ramakuri_3-1713770795333.png

    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).

    Israel_Ramakuri_4-1713770795337.png

    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.

    Israel_Ramakuri_5-1713770795350.png

    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.

    Israel_Ramakuri_6-1713770795355.png

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

     

    Israel_Ramakuri_7-1713770795358.png

    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.

     

    Israel_Ramakuri_8-1713770795367.png

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

     

    Israel_Ramakuri_9-1713770795374.png

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

     

    Israel_Ramakuri_10-1713770795381.png

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

     

    Israel_Ramakuri_11-1713770795391.png

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

     

    Israel_Ramakuri_12-1713770795400.png

    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.

    Israel_Ramakuri_13-1713770795407.png

    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.
  • juroy_lim Profile Picture
    juroy_lim 5 on at
    Re: 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?

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

  • Murali2 Profile Picture
    Murali2 4 on at
    Re: 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?

    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.

  • Israel_Ramakuri Profile Picture
    Israel_Ramakuri 9 on at
    Re: 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?

    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

     

Helpful resources

Quick Links

Exciting News for Copilot Studio Communi…

Get ready to experience a whole new level of engagement with the Copilot Studio…

Celebrating the May Super User of the…

LaurensM is an exceptional contributor to the Power Platform Community…

Check out the Copilot Studio Cookbook…

We are excited to announce our new Copilot Cookbook Gallery in the Community…

Leaderboard

#1
renatoromao Profile Picture

renatoromao 6,459

#2
Pstork1 Profile Picture

Pstork1 1,997

#3
Expiscornovus Profile Picture

Expiscornovus 1,708

Leaderboard

Featured topics