Chat statistics

[Divous]

Hi community,

I have export from chat. It includes some information like ID's of chat, users, support or text of message etc. Below is simplified example. The column "# of message from „who“" is added from me now - why I explain it later.

chat_id	support_id	user_id	time_of_message	who	message_text	# of message from „who“
006jIhMDiPOZ	a222ccab-2d8d	4b98a1e6-3538	21.12.21 10:00	chatbot	not important
006jIhMDiPOZ	a222ccab-2d8d	4b98a1e6-3538	22.12.21 10:00	support	%support_name% connected	1
006jIhMDiPOZ	a222ccab-2d8d	4b98a1e6-3538	23.12.21 10:00	user		1
006jIhMDiPOZ	a222ccab-2d8d	4b98a1e6-3538	24.12.21 10:00	support		2
006jIhMDiPOZ	a222ccab-2d8d	4b98a1e6-3538	25.12.21 10:00	user		2
006jIhMDiPOZ	a222ccab-2d8d	4b98a1e6-3538	26.12.21 10:00	support		3
006jIhMDiPOZ	a222ccab-2d8d	4b98a1e6-3538	27.12.21 10:00	support		3
006jIhMDiPOZ	a222ccab-2d8d	4b98a1e6-3538	28.12.21 10:00	user		3
006jIhMDiPOZ	a222ccab-2d8d	4b98a1e6-3538	29.12.21 10:00	support		4
jj0Epbxi2L7U	869d4abe-6066	7ecc3efd-909c	23.12.21 11:00	chatbot	not important
jj0Epbxi2L7U	870d4abe-6066	7ecc3efd-909c	24.12.21 11:00	user	not important
jj0Epbxi2L7U	871d4abe-6066	7ecc3efd-909c	25.12.21 11:00	support	%support_name% connected	1
jj0Epbxi2L7U	872d4abe-6066	7ecc3efd-909c	26.12.21 11:00	support		1
jj0Epbxi2L7U	873d4abe-6066	7ecc3efd-909c	27.12.21 11:00	support		1
jj0Epbxi2L7U	874d4abe-6066	7ecc3efd-909c	28.12.21 11:00	user		1
zoYPJ957fR	c071fc4f-52b4	6ca908c6-84eb	25.12.21 12:00	chatbot	not important
zoYPJ957fR	c071fc4f-52b4	6ca908c6-84eb	25.12.21 12:00	user	not important
zoYPJ957fR	c071fc4f-52b4	6ca908c6-84eb	26.12.21 12:00	support	%support_name% connected	1
zoYPJ957fR	c071fc4f-52b4	6ca908c6-84eb	27.12.21 12:00	support		1
zoYPJ957fR	c071fc4f-52b4	6ca908c6-84eb	28.12.21 12:00	user		1
zoYPJ957fR	c071fc4f-52b4	6ca908c6-84eb	29.12.21 12:00	support		2
zoYPJ957fR	c071fc4f-52b4	6ca908c6-84eb	30.12.21 12:00	support		2
zoYPJ957fR	c071fc4f-52b4	6ca908c6-84eb	31.12.21 12:00	user		2
zoYPJ957fR	c071fc4f-52b4	6ca908c6-84eb	01.01.22 12:00	support		3

I am trying to make some complex and interesting statistics. But my PBI skill are still so far from doing it right.

Desired results:

1) Mainly goal is to know how many times user and support write to each other before solving the problem after "support connected" to chat. With filters like
- User write more than 3 times
- % of users write first after "support connected" to chat

- % of users communicate or disconneted after "support connected" to chat, or after few messages, etc

The total number of messages is also interesting, but this statistics from "rotation" is important to me so then I would know for example that support "returned" to the chat x times.
There is tricky part -> we need to calculate for example 2 message from support in a row as one. Same with users. This shows colum added from me "# of message from „who“"

2) It is useful to know how long the reactions of one of the communicating sides takes, or then to calculate the total time of chatting, the average between the exchanges of messages, etc.

Then I want to put it in some funnel and see the throughput of all or then filtered problems.

Like this? With # of messages from Users and Helpers (support) and with conversion and average between reactions,..

or maybe this vizualization is not suitable for this case. My original data have over 100k of rows and some extra not important columns.

Can I ask for a tutorial? It is probably beyond the help of this forum, but I do not know what to do with it 😕

Thanks in advance

[v-yiruan-msft]

Hi @Divous ，

Not quite sure what you need. Do you want to get the number of communications between the support and the user when solving a problem? Like the value given in the field [# of message from "who"] in the example table you gave? Also, you want to get the average time per communication and create a graphical chart that is easy to understand, right? Could you please provide more example data in Text format and share the logic of the calculation based on the example data given (e.g. chat_id 006jIhMDiPOZ in the table below, why the final value of field [# of message from "who"] are ,1, 1, 2, 2, 3,3,3,4? How to get the average time?) and the final result you want. Thank you.

Best Regards

[AllisonKennedy]

@Divous power bi can do everything you are asking if the data is tidy enough:

Does the timestamp include seconds? Your sample table has lots of the same timestamp which will make it much more difficult. 

Does the first message from support always say 'supportname connected ' ?