My last blog post, Take Action on Your Data in 2016, focused on ways at a high level you can analyze a data source and turn it into actionable results. There is a common source of data that all of use every day but maybe we would not consider analyzing – our e-mail. If we think about the properties and actions with e-mail (i.e. when do I receive or send e-mail, who sends me the most e-mail, who do I send e-mail to, etc.), it becomes an interesting data source that can be easily analyzed with Microsoft Exchange Server, Power Query, Power Pivot and Excel.
Let’s get started with how I analyzed my e-mail and the possible actions I can take to be more effective with e-mail!
To get started, there are a several pre-requisites to be installed or enabled:
- Microsoft Excel 2013
- Microsoft Power Query
- Microsoft PowerPivot
- A Microsoft Exchange (Outlook) mailbox
The first step in taking action on your e-mail is to connect Power Query to your Exchange account. This can be achieved by selecting the “From Microsoft Exchange” option from the “From Other Sources” menu.
Next, we will specify the Exchange mailbox address, credentials and then let Power Query do its magic.
After a successful connection, Power Query will display attributes of your Exchange mailbox.
Power Query is an impressive tool for these types of operations due to its ability to easily add, remove, and manipulate columns. When doing analysis on e-mail, you likely do not want to query all of the properties of an e-mail such as Body, Attachments, Preview Text, Exchange ID, etc. Using Power Query’s graphical user interface, you can “cleanse” the data for analysis by adding data steps. I prepared my data using these steps:
- Remove unnecessary columns
- Expanding the details of the sender (Name, E-mail Address, etc.)
- Extracting the hour portion of the e-mail timestamp
- Calculating the day of week of the e-mail timestamp
The end result of the Power Query steps is an easy to understand data model that can quickly be analyzed and put into pivot tables and/or charts.
What are the results of my e-mail analysis?
- Most e-mails are received and sent between 8:00 AM – 12:00 PM
- After an hour lunch break at 12:00 PM, e-mails increase at 1:00 PM and slow down at 3:00 PM when we’re having afternoon tea or coffee
- Tuesday is the busiest day to receive e-mail, Friday is the least active day
- I am most likely to delete an e-mail between 8:00 – 10:00 AM
- You will be more successful in me spending quality time reading e-mail during 3:00 – 5:00 PM
- I also learned that I am keeping with my goal of having less than 50 e-mails in my Inbox
What kind of insights would you find from your e-mail? Contact Affirma Consulting today to see how you can quickly analyze your e-mail and other frequently used data sources. Affirma Consulting can help expand your data analysis capabilities and partner with you to fully implement the solution.
