Shared Mailbox Analytics - Average response time and number of email responses by agent

[carsenau_220]

Good day,

Now that PBI can us an Exchange shared mailbox as a source, I am hoping to get some basic analytics using PQ on the inbox and sent items folders.  Have anyone done this calculation before, so we don't have to go buy a 3rd party tool to do it?  Would prefer to keep it within our current tool set are we are heavy users of PBI.

Any guidance would be greatly appreciated.  This should be a nice addition since there is not analytic reporting on shared mailboxes, but only active users.

Peace and thanks to you,

carsenau_220

[pbiuseruk]

Hi,

I never knew that you can import Exchange data but really interesting.

I just tried this on my side and in order for you to do what you mentioned, you'd firstly connect and import the Mail table.

Once you've imported the mail table, go to the attribute column and expand the "InReplyTo" field.

That field is the ID of the email that is being replied to.

Close and Apply.

Then if you want to see some stats, these are my suggestions.

Make a Dax column that works out the time taken to respond to the initial query. If you send automated responses then you may need to index the responses and remove the first one to see the actual response email's time.

Then I'd suggest just making a simple measure which is something like avg(<field you made to see the time difference>)

Let me know if this helps?

[pbiuseruk]

I forgot to say but you can use the Power BI Path function to get the whole chain of mails in one column which would be really useful for this.

[carsenau_220]

Good day, pbiuseruk.

Thank you for taking the time to respond.  Greatly appreciated.  I am working on it this week, so will respond back accordingly.  As for the Path function, can you give me an example on how to use as I am not a PBI expert, but a newby.

Thank you,

[pbiuseruk]

No problem and sure,

The path function works on parent-child relationships and can pull in the whole chain into one field value.

This is the documentation: https://learn.microsoft.com/en-us/dax/path-function-dax
This is an example: = PATH(Employee[EmployeeKey], Employee[ParentEmployeeKey])

In this example we'd replace the first parameter with id and the second parameter with the InReplyTo field. This will basically make a chain of ids that will lead to the ultimate parent like this:

emailid1, emailid2,..... emailid ultimate parent. This would be useful if you wanted to find out the time taken between any selected mail and the initial mail that triggered the chain.

What you could quite easily do with this is extract out the last emailid (which would be the parent email id and the first email as part of the chain). Then you can lookup that email id and pull back the timestamp for it. Then you can make a column or measure that would show the duration (current emails timestamp - parent emails timestamp).

Just some random suggestions but hope it helps.