Unleashing the Power of Power BI: How to Track Email Activity with Exchange Online Connector for Ultimate Insight

In today's digital world, getting a handle on email activity is super important. Since email is a go-to way for people and businesses to communicate, keeping an eye on this info can reveal trends, patterns, and how engaged folks are. That's where Power BI comes in handy. It's packed with features that help you dive deep into email data. In this post, we'll walk you through setting up a Power BI report using the Exchange Online connector to keep tabs on sent email activity.

Check your Email activity in Power BI

Understanding Email Activity in the Digital Age

Email activity goes beyond simple communication; it reveals valuable insights into how teams and organizations interact. By monitoring sent emails, you can gauge productivity, identify peak communication times, and uncover trends over months or years. For instance, a company that tracks its email activity might discover that 60% of its communication occurs on Tuesdays (Mondays too!) and Thursdays, indicating high productivity on those days.

Power BI allows you to visualize this data meaningfully, making it easy to share findings with relevant stakeholders.

Leveraging the Exchange Online Connector

Power BI’s Exchange Online connector provides direct access to your Exchange Online environment, allowing you to retrieve various aspects of email activity. Connecting Power BI to Exchange Online is a user-friendly process that opens doors to numerous reporting possibilities.

Get Data and look for Microsoft Exchange (not online!)

Step-by-Step Guide to Connecting Power BI to Exchange Online

1. Open Power BI Desktop: Ensure you are using the latest version for the best experience.

   
   

2. Select Get Data: Click on the “Get Data” button located in the Home ribbon.

   
   

3. Choose Exchange Online: Search for and select the Exchange Online connector in Power Query UI.

   
   

4. Authenticate: Sign in with your organizational account that has access to Exchange Online data. (You need Microsoft account)

   
   

5. Load Data: After authentication, select relevant tables, such as `Messages`, to analyze email activity.

   
   

These steps help you consolidate email information into Power BI, paving the way for in-depth reporting and analysis.

Extracting the Right Data Points

Once connected to Exchange Online, focus on extracting relevant data points. Here are key metrics worth tracking:

• Total Sent Emails: Count how many emails have been sent within specific time frames, helping you assess overall activity.

  
  

• Emails Sent Per User: Analyze which users are the most active. For example, if one employee sends 150 emails a week while others send around 30, you can tailor workload management accordingly.

  
  

• Email Activity Over Time: Identify trends such as peak email times. You might find that emails spike 45% during the final two weeks of projects, suggesting a need for improved communication tools.

  
  

• Response Times: Measure how quickly emails receive responses. A typical business strives for a response time of under 24 hours; falling behind could signal inefficiencies.

For this purposes I have two tables (queries) that I connected later: Mail and Mail receivers with slightly different M-code inside.

Mail receivers table:

let

    Source = Exchange.Contents(Email),

    Mail1 = Source{[Name="Mail"]}[Data],

    #"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "\Sent Items\")),

    #"Expanded ToRecipients" = Table.ExpandTableColumn(#"Filtered Rows", "ToRecipients", {"Address"}, {"Address"}),

    #"Expanded CcRecipients" = Table.ExpandTableColumn(#"Expanded ToRecipients", "CcRecipients", {"Address"}, {"Address.1"}),

    #"Removed Other Columns" = Table.SelectColumns(#"Expanded CcRecipients",{"Id", "Address.1", "Address"}),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Id"}, "Attribute", "Receiver"),

    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),

    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")

in

    #"Removed Duplicates"

Mail:

let

    Source = Exchange.Contents(Email),

    Mail1 = Source{[Name="Mail"]}[Data],

    #"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "\Sent Items\")),

    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Subject", "DateTimeSent", "Body", "Id"}),

    #"Expanded Body" = Table.ExpandRecordColumn(#"Removed Other Columns", "Body", {"TextBody"}, {"TextBody"}),

    #"Inserted Text Length" = Table.AddColumn(#"Expanded Body", "Length", each Text.Length([TextBody]), Int64.Type),

    #"Added Custom" = Table.AddColumn(#"Inserted Text Length", "IsOriginal", each not(

Text.Start([Subject],3) = "RE:" or

Text.Start([Subject],3) = "Re:" or

Text.Start([Subject],3) = "FW:" or

Text.Start([Subject],3) = "Fw:"

    )),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"IsOriginal", type    logical}}),

    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Id"}),

    #"Inserted Date" = Table.AddColumn(#"Removed Duplicates", "Date", each        DateTime.Date([DateTimeSent]), type date)

            in   #"Inserted Date"

Visualizing Email Activity in Power BI

With your data extracted, build engaging visuals that convey insights clearly. Here are a few visualization options:

• Bar Charts: Create bar charts to compare total emails sent by various users over different time periods.

  
  

• Line Graphs: Use line graphs to depict trends over time, revealing patterns that align with major company projects or initiatives.

  
  

• Pie Charts: Display the percentage of total emails sent by team members, or if emails are internal or external. This can pinpoint communication disparities and encourage more balanced engagement.

  
  

• Table: it goes always. There you can have better overview of activities.

  
  

Additonal slicers can be handy, like date slicer and also hour or day slicer so you can have different perspective of activities, like which day is "peak of the week".

Benefits of Analyzing Email Activity

Analyzing email activity with Power BI offers numerous advantages:

• Enhanced Team Collaboration: Identifying frequent correspondents can foster better partnerships. Teams might discover that individuals who communicate often collaborate more effectively on projects.

  
  

• Resource Management: By evaluating email patterns, you may pinpoint employees who are overloaded with messages. Streamlining their communication approach could improve their efficiency.

  
  

• Improved Processes: Insights derived from email data can lead to smarter practices. For instance, if teams notice long response times, it may prompt discussions on adopting platforms conducive to quicker interactions.

Some of KPIs that can be calculated

Considering Alternative Communication Tools

In this era of instant messaging, exploring alternatives, such as Microsoft Teams, can bring added benefits. By analyzing email data, you can highlight moments when real-time communication could improve efficiency, reducing reliance on email significantly.

Final Insights

Power BI provides a powerful solution for tracking and reporting email activity through the Exchange Online connector. By leveraging these insights, organizations can enhance collaboration, refine their communication strategies, and stay agile in an increasingly digital world.

If you're eager to tap into the realm of email analytics, start setting up your Power BI reports today and unlock the power of your email data!

p.s for this purpose you need Microsoft Account to be analyzed for your Emails.

Closing Thoughts

As businesses increasingly seek to harness data for decision-making, tools like Power BI will play a pivotal role in providing necessary insights.

Download M-codes for Power Query

Email activity queries.txt

Download TXT • 2KB