Tracking number of mentions per user in Teams and export to CSV

[kyleb]

Hi there,

Would it be possible to count the number of mentions in a channel, by user, and then export that message to a CSV file? We have a "Kudos" channel and we want to see the number of times a person receives a kudos, the date received and who submitted it. 

Is this possible? I think using the mention function as the trigger would work, but I am unsure about the rest.

Thanks for any help

[takolota]

Is there a reason it needs to be a CSV file?

And would just logging any mentions in a SharePoint list work?
I think that would be simpler as you could export that list any time.

If you needed to automate the CSV creation & send it somewhere then you could just schedule a Get items to run on that list and use the CSV action from there.

[kyleb]

Hi there,

I am not familiar with logging functionality in Sharepoint. Do you have a site or link that would be able to showcase that for me? And it doesn't have to be a CSV, I just want to have a list of all of the mentions and be able to analyze it and create detailed graphs etc for that analysis.

[takolota]

@kyleb 

Sure, so I'm saying you could create a SharePoint list, then set a flow to enter all that information from each Teams message to the channel into a SharePoint list.

From there you could connect a Power BI report to the SharePoint list and have the Power BI report count the number of SharePoint rows that mention each person. And those graphs could be updated from the click of a refresh button in Power BI.

Alternatively, you could use something like a recurrence trigger, get all the messages from the Teams channel, then use a Filter action to filter the list of Teams messages to just the messages for each person, then use a

length(body('Filter_array')) expression to count the number of messages mentioning that person from that filtered list.

Then you could list all those length expressions & the people associated with them in a CSV or Excel file.

Will either of these work? Or do you have many and/or changing people being given Kudos, so making a new set of actions for each doesn't make sense? (There should be ways to deal with that too, it may just get more complicated to describe.)

[kyleb]

Thanks for your help! Yes, the Kudos are coming from random individuals in our domain, and mentioning random individuals that are also in the domain.. I also would want to read the previous ones that have already been made and have those included as well, not just new ones going forward. 

[takolota]

@kyleb 

Okay, then you will probably want to go with the 1st set-up I mentioned sending everything to SharePoint.

Then when you load that list into Power BI, you should be able to Group by the person or person’s email and get aggregate counts of kudos or rows mentioning each person from there.

[kyleb]

Okay, so I tried to make it based on your screenshots but I am not sure what to put for the fields in here. Here is what I have so far. I am not sure what the Id is for that one so I just put 0 for now.

[takolota]

@kyleb 

Sorry, I was only using the Compose step because I’m currently working in an environment where I don’t have access to any SharePoint lists.

You will need to create a SharePoint list with all the columns you need.

Then in your flow you will use a Create item in that list, not an Update item. So you’ll create a new SharePoint row each time someone is kudoed and add all the parts you need, like if you want to fill in the date of the post, the email of the person kudoed, the message that went with it, etc.

AND, if you have instances where multiple people are kudoed in the same post, then you will probably need to use a split( ) expression on the dynamic content that lists the emails/people and run an Apply to each loop with a Create item action that will run to make a new row for each person. That way each row will only have 1 person associated with it, and the number of rows for each person will match their number of kudos.

[kyleb]

Okay, so this is a bit confusing as to what add. Am I doing Create Item in Sharepoint? And if so, how do I make the rows you are talking about?

[kyleb]

[takolota]

Go to the “Kudos Tracker” list you created in SharePoint and add the columns for the fields you want there. 
Then when you reload the flow, those columns should show up below Title in that SharePoint action.

[kyleb]

Okay so I did that and one of them allows for me to reference the date and time it was submitted, but how do I add the columns for the information I need, like who was mentioned?

Thanks again for your help

[takolota]

Add SharePoint Columns

https://support.microsoft.com/en-us/office/create-a-column-in-a-list-or-library-2b0361ae-1bd3-41a3-8...

[kyleb]

Thanks for that, but how can I make a column that accesses the data of mentions? I don't see that as an option. Or do you have a different way?

[takolota]

No, you make the column to hold that data. Then you use Power Automate flows to fill that & the other columns with the rows of data.

[kyleb]

How do I use the power automate flows to fill that column?

[takolota]

@kyleb 

If you want to use past kudos/messages too, you will have to create a manually triggered flow with some kind of Get messages from Teams Channel action & use a Create item SharePoint action with the dynamic content from the Teams action.

Then for any future messages, you’ll use something like the flow you shared earlier with the Teams channel trigger & the Create item action filled with dynamic content from the trigger.