I wonder how many of us use shared mailboxes to funnel work? How well does a shared mailbox, on its own, work?Do all team members do their fair share of the work answering messages from the shared mailbox or does social loafing run rampant?
Maybe it is time to consider monitoring email in shared mailboxes. After all, receiving and responding to email can require a lot of effort. We all know how it is. We receive lots of email everyday and we don’t get to read all of it but we hope to get back to it. In business we may find that we probably get more email than we do in our personal lives. We get emails from vendors, wannabe vendors, co-workers, project teams, bosses, customers, etc. It seems if a person exists then we are getting email from them. So we make shared mailboxes to spread the dread of checking the mailbox among more teammates or employees and inevitably this thing becomes a work process… a work process that is hard to keep track of. Who on the team is doing what with these messages and how many things are they doing?
This is the problem that my self-paced “workshop” explores and attempts to resolve with a custom solution built using the Power Platform.
This workshop will quickly review a typical shared mailbox with subfolders and then jump right in to planning and building a custom solution to help keep track of the messages in the mailbox folders and subfolders without having to manually check the mailbox. This can be a working solution for teams or managers to assist in active workflow management.
This workshop includes:
- recorded instructional material
- written instructional material
What you’ll need:
- A Power Apps license (free trial version works)
- A Power Automate license (free trial version works)
- A Power BI license (free trial version works)
- Power BI Desktop (available on Windows only)
- An active Outlook mailbox that you have access to
Get the mailbox ready
The shared mailbox is the data source for this solution. So, it is crucial to have a well-organized mailbox.
It is a good idea to create new folders at the same level of the Inbox, not within it. At many organizations, the Inbox is subject to mail deletion policies and any email messages found in the Inbox (including folders within the Inbox folder) may be subject to deletion on a schedule.
You can also create subfolders within your new folders but take care to not create elaborate folder structures with folders within folders within folders. Too much folder structure becomes to cumbersome to use.
Also, limit the folders you create. I use this rule of thumb for folders in my personal mailbox and mailboxes that I manage. If I have created more than 25 folders, it is probably time that I review some of them, extract any important content from them and store it in a OneDrive folder or SharePoint site related to email correspondence and then consider deleting the folder. I do this because Microsoft Outlook has its limits as does everything in life.
Performance Limits for Microsoft Outlook
According to Microsoft, too many folders is simply asking for email troubles. Read the official documentation here: https://docs.microsoft.com/en-us/outlook/troubleshoot/performance/performance-issues-if-too-many-items-or-folders.
To summarize, if you have 500 folders expect synchronization issues, performance issues and other problems. If you have more folders than that it is a miracle that you can check your email at all. If you have a delegate assigned to check your email, that person will have email problems, too. So, it is just best to avoid that nonsense altogether.
The image above depicts a normal, professional, healthy folder structure. The majority of the folders are default folders. The last four folders were created by the team or team manager for the purposes of funneling work from the Inbox by those individuals.
Avoid giving folders silly (funny) or potentially offensive names. You probably would not want anyone to see that you are storing shared work correspondence in a folder called Cesspool, for example. Similarly, if you manage a shared mailbox in an accounting firm for the purpose of answering general operational questions, you should not give folders names like Bilbo Baggins or Harry Potter when those are just literary characters you like and contents of the folder has nothing to do with Bilbo or Harry.
Review and prepare the mailbox you plan to use for the rest of this workshop. Use the tips from Step 1 to guide you.
- Get to know your folder structure and the messages that are typically contained in each folder
- Gather ideas for what you, the team, or management routinely needs to know about the flow of work in the mailbox
- Create necessary folders and a single-level of subfolders if you need them but don’t overdo it on folder creation
- Keep the names of your folders professional
- Clean up folders for projects long past and consider moving the important bits to OneDrive or an applicable SharePoint site document collection
- Delete any folders that are empty and have not been used for a long time
- Don’t forget to delete the deleted folders from deleted items as this doesn’t happen automatically
In this workshop, a shared mailbox is used to facilitate business processes. There is a team of four employees that review, research, and answer questions submitted to the mailbox. The goal of this project is to quantify the general work of each employee as it relates to managing this mailbox. Here’s what our solution needs to provide management:
- The number of email messages that each employee worked on today, this week, this month and this year
- A general comparison of the amount of messages each of the four employees works on over the course of the year
- The total number of messages received by the mailbox
- The total number of messages in the Inbox
Connect your mailbox
We’ve reviewed our mailbox and cleaned out some old folders and maybe we created some new ones. We generally know the data in the mailbox and have an idea about what we routinely need to know about the mailbox.
Now, we are ready to connect our mailbox to the Power Platform. Launch Power BI Desktop and start the video, below.
At the time of this video recording, Power BI Desktop Version: 2.87.762.0 64-bit (November 2020) was current. If you are using older versions of the application, consider updating to the newest version here: https://www.microsoft.com/en-us/download/details.aspx?id=58494 or you may attempt to follow along with your existing version of the application. You may find that some features are not available for you.
Add a visualization and a new table column
Before we jump into building our report, we will take a look at a single visual and how it behaves with our mailbox data. Then we make our table data work for our specific purpose by adding a column to make our visualizations more effective.
Connect Power BI to your mailbox and transform the data. Use the tips from Step 2 to guide you.
- Filter out any folders you don’t need to see. This will improve the performance of your query
- Expand any columns you do need
- Create a new column needed for your visualizations
- Save your Power BI file to a location you specify (preferably a SharePoint site or some other location that is always accessible) and name it something memorable
Build a mailbox report
We’ve connected our mailbox to Power BI, prepared our queries and saved our work and experimented with a visualization and adding a new column.
Now, we are ready to build a beautiful report from our mailbox data.
Let’s go back to Power BI and build our report!
Build out your mailbox report and use the videos as a guide.
- Apply column and line charts to the report
- Apply filters to the column and line chart visuals
- Add color to the report by formatting the visuals
- Turn on/off chart options like, axis titles and borders
- Save your work
Refine the Report
Our report is looking very good but we still need to display a few more pieces of information. We still need to display the total number of messages received (count of all messages in employee folder and the Inbox) and the number of messages currently in the Inbox. Since these are basic pieces of information, a card is a good visualization to use to present this data.
Add a Card to a Report
- In the report, expand the Visualizations panel.
- Click the Card visualization to add it to the report. The Card visualization is shown highlighted in the image below.
- Drag the Id field to the visualization date field as highlighted in the image below.
- The card will populate with the first messages unique Id which looks pretty wacky but that’s okay. Click the drop-down arrow next to the visualization data field for this card and select Count from the menu. This changes the card so that it doesn’t display the first message Id but a a count of all the message Ids. Use the image below this text as a guide.
- Now edit the text in the visualization display field. Change it so that it reads Total Messages and not Count of Id.
- Take this time to resize the card, drag it into the desired position, and edit the visualization formatting to add color and refine font attributes.
- Save your work.
Create a second data card
Create another data card to display the number of messages in the Inbox.
- Copy and paste the existing card onto the report to duplicate it.
- Drag the new card and position it to the right of the first card.
- Edit the text in the visualization display field. Change it so that it reads Messages in Inbox and not Total Messages.
- Apply a filter to this visualization by expanding the Filters panel.
- Use a basic filter and check the box next to Inbox to display only the numbers for the Inbox in this card. Use the image below this text as a guide.
- Now the card displays a count of the messages in the Inbox, only.
- Save your work.
Publish the Report
Your report is ready to share with the team or management and you need to publish it to a workspace for others to view it. Just make sure users also have access to the workspace.
- On the ribbon, click Publish. If you haven’t saved changes in a while you may be prompted to do that.
- Select the proper workspace for the report. In the below, I should pick a workspace other than my personal workspace. Use an organization workspace associated with a Teams site or something accessible to a group. You may not want to give individual users access to your personal workspace and that information may be lost if employees leave the company.
- Click Select to publish the report.
- Once the report has published successfully, click the report link to open the report in the Power BI service. The Power BI service is the web or SaaS part of Power BI. This is where your report users and viewers access the report.
- With the report open in the Power BI service, click Share as shown below.
- Click Report.
- You can share the report via e-mail. In the Share report panel, enter an email address for each employee that you want to grant report access to and add a custom message to the email. Choose the permission that the selected users will have for this report. Use the image below as a guide.
- Click Share to share the report.
These steps work to share reports inside your organization. Sharing reports with external parties is possible but may not be allowed in your organization. Check with your Data Governance and IT teams.
Set the Report to Refresh Automatically
A report is only good as its data if that data is current. Consider keeping your report current by scheduling automatic refreshed in the Power BI service.
- With the report open in the Power BI service, click the ellipsis on the menu and select See related content. See the image below as a guide.
- In the Related content panel, click the Schedule refresh icon to launch the Datasets settings for the report . (It’s highlighted in the next image.)
- Click Scheduled refresh and toggle on the Keep your data up to date option. Select the refresh frequency, your time zone and the times when the data will refresh. Choose to send refresh failure notification to yourself and if necessary, send to others. Click Apply.
Scheduled refresh works well for data in the cloud but you can only schedule a maximum of 24 refresh periods per day. Spread evenly over an entire day, data can be refreshed every 30 minutes.
By now, you have a beautiful, working solution. It easy for team members or management to check at-a-glance and the report updates on its own. What more could you want? Lots, actually.
Display the date/time the report was last refreshed
Imagine you have set your report to update every 30 minutes. Does anyone else know that? Did you tell the team but they don’t seem to remember? Consider adding information to your report to show the last time the report data was updated.
- Open your report in Power BI Desktop.
Add a New Table
- On the ribbon’s Home tab, click Transform Data.
- Click New Source and select Blank Query.
- Name the query RefreshTime
- Enter the following code in the function bar:
In the code above, the number ‘5’ is the time zone offset for my region. Enter the offset for your region.
- Click the Convert to Table button on the ribbon.
- Rename the column to RefreshDateTime.
- Click the Transform tab and change the data type for the column to Date/Time.
- On the Home tab, click Close & Apply.
Place this information on the report
- Add a card visualization and drag it to the top of the report.
- Drag the RefreshDateTime field to the data field for the card.
- Format the card so that the Category is toggled off.
- Toggle the Title on and reads Last updated:
- Format the Data Label so that the text size is smaller.
- Save the report and publish the changes.
- View the updated report. See the refresh date/time. This information will automatically updated each time the report is refreshed.
Show more granular data in the report
The current report shows all the emails worked or in progress for each employee but there is not a breakdown between resolved emails and pending emails. We can show this but we’ll need to add another column to our data to make this easier.
Add a conditional column
- On the ribbon’s Home tab, click Transform data.
- Click the Add Column tab.
- Click Conditional Column.
- Name the column Status.
- Building the condition by setting Folder Path as the Column Name, contains as the Operator, Resolved as the Value and Resolved as the Output. (This works because my data has subfolders named Resolved.)
- Enter the word Pending for the Else clause. Use the image below as a guide. Click OK to create the new Status column.
Use the new column in the report
- Click the Activity This Year visual on the report to select it.
- Drag the Status field to the Legend field in the visualization. Now the report shows the number of messages pending and resolved by employee.
- Select each Activity visual and update it each by dragging the Status field to the Legend field for each of these visuals.
These are just a few the extras that you can build into your report. I hope you enjoyed this workshop and was able to create and share a monitor solution for your team.