Forum Discussion
Shared Mailbox Analytics - Average response time and number of email responses by agent
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
5 Replies
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?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.
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,
Hi
You don’t need extra tools for this. You can solve this with what you already have in Microsoft 365.
In about 10 minutes, you can connect your Microsoft Outlook / Exchange shared mailbox directly to Excel as a data source. Excel can read the shared mailbox and load all emails into a structured table with columns such as:
Subject
Sender
Received time
Sent time
Conversation ID
Body (if needed)
Assigned person (if you use categories or flags)
From there, you can clean and structure the table in Power Query and build a dashboard in Excel or Power BI.
With this setup, you can easily calculate:
1. Emails per hour, day, month
Use the received timestamp and build time-based aggregations.
2. Response time
By person: if each agent replies from their own account or uses categories, you can measure the difference between received and first reply.
Overall: calculate the time between received and first sent reply in the conversation thread.
3. Unanswered emails
Filter emails without a reply in the same conversation ID and calculate how long they have been open.
You can refresh the dataset automatically, even close to real time depending on your setup. The dashboard updates as new emails arrive.
I have implemented this multiple times with Excel and Power BI for shared mailboxes working in shifts. It is flexible, transparent, and fully under your control.
Why pay for extra tools when you can build this with a simple setup using what you already own?
Good luck.
