Answered

Long-term Analysis and Reporting

(1) Share

Report

Posted on by Egbert

Hi,

I am trying to set up a Power BI dashboard to report on Bot usage, effectively replicating the Analytics dashboards but allowing for long-term analysis and reporting.

I have tried several ways to get the cleaned data that the Analytics page uses to no avail. I am not able to export 'underlying data' from the Analytics page. See the image from the link below, showing the option for exporting 'underlying data' as un-selectable. ( As raised by this post: https://powerusers.microsoft.com/t5/Bot-Analytics/Export-Analytics-underlying-data/m-p/627964 )

I have followed the documentation: https://powervirtualagents.microsoft.com/en-us/blog/view-export-and-manage-retention-time-of-your-bot-conversation-transcripts/ , and extended the retention policy for the transcripts and exported the data. However the Transcript data is stored in the ConversationTranscript table in a single field called 'Content'. This data is currently unusable without heavily parsing the contents of said field. There is no other storage of clean data that I can find.

My questions are:

Does Analytics dashboard (which is created using Power BI) use the ConversationTranscript table as a data source? If so, is the data cleaned at a Dashboard level? If not, where can I find the data source used?

How do you calculate values such as engagement, resolution, abandonment, etc. using the values contained in the 'Content' field of the ConversationTranscript table?

Does anyone have any suggestions as to how I can facilitate my requirements of long-term analysis and reporting?

Looking forward to hearing back from someone,

Thanks

Categories:

Bot analytics

I have the same question (0)

All responses (6)

Answers (1)

nmk 193 on at

Like (1)

Report
Copy link

Link copied!

100% in agreement with you. We like the reports provided by MS however, it would be nice if we have access to the underlying data so we can create our own reports as required by the business.

Was this reply helpful? Yes No
Egbert 33 on at

Like (2)

Report
Copy link

Link copied!

Thanks @nmk!

I really appreciate your response, I was pulling out my hair trying to generate the reports I need from just the available data.

I have been working my way through cleaning the parsed JSON from ConversationTranscript and constructing a PowerBI dashboard. I have found some of the data relevant to my needs but have also found that it doesn't quite line up to the data from the PVA portal.

Please let me know when you hear anything back from Microsoft as to where this data is stored and how to gain access to it. It would be great if we could have access to it to report on the usage of our bot long term.

Thanks!

Was this reply helpful? Yes No
Verified answer

nmk 193 on at

Like (1)

Report
Copy link

Link copied!

Well, there are two analytics reports in PVA portal. One is for bot level analytics and the other is at topic level.

There are also two ways to extract the session data

1 - Downloading session data from PVA portal
2 - ConversationTranscript Table in Dataverse

if you compare the data extracted from both the method, you will see that they are not same.

The data from the first option is more user-friendly than the second option which is basically a json blob.

If you see in the output from the first option, there's a column called 'SessionOutcome' and that's the main field driving the analytics and the same field is not available in the data export from point-2
so in short, the answer is NO. The ConverstationTranscript is not driving these analytics.

So where's the data? I believe, it must be stored in one of the analytics system where we don't have access to. As per the documentation, we also need Customer Service Insight license to access topic level analytics. So it may be residing in Customer Service Insight or similar system.

I maybe missing something here but this is from my observation. I will see if someone from Microsoft can confirm the understanding.

Was this reply helpful? Yes No
CU22081450-0 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

hi @Egbert

Great to know it! 🙂

I think that this video here can help you about JSON format inside Power BI (its a different structure but is the same idea): (4) Power BI Tutorial: Working with JSON data - Import JSON file and visualize using JSON dataset - YouTube

Was this reply helpful? Yes No
Egbert 33 on at

Like (0)

Report
Copy link

Link copied!

Thanks Renato,

Your answer has been really helpful. I thought this was probably the case, but was hoping for a more user friendly solution.
How would you recommend parsing the json content? Inside the Power BI dashboard itself?

Was this reply helpful? Yes No
CU22081450-0 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @Egbert ,

Regarding your question, YES, the dashboard inside Analysis panel are using the Conversation Transcript.

Today, the unique way to create dashboards with a Chatbot data is using the unique table inside the Common Data Service (Dataverse).

You need to extract using code the json content inside the Content field and manipulate the data to show inside your Power BI.

Also, you can create your idea here: https://aka.ms/pva/community/ideas (to read data to another way)

Was this reply helpful? Yes No