Analytics Tutorial: Automating Reports and Emails with Attachments
I was recently asked to compile some useful business information and share it with each salesperson based on the specific customers they are assigned to. In a company approaching $1B in sales, this was not a rudimentary task. Thankfully, through my understanding of Python programming and data techniques, I was able to automate the creation and dissemination of each unique report. In this analytics tutorial, I’ll share the process and some data tips with my readers.
Analytics Tutorial Step 1: Identify Insights
First and foremost, in any exercise involving data, it is critically important to understand how to identify useful data insights. This begins with understanding your target audience, and in particular, their goals and motivations. In this example, I was quantifying the impact of a pricing change for all of our products. The salespeople needed to understand the impact these changes would have on their customers in order to gain confidence in explaining the changes and relay useful information to their customers.
By understanding my audience’s needs, I am better able to structure information in a meaningful way, and I’m also able to make better decisions during the analytical process. For example, I can determine which data can be excluded without impacting the important underlying information that the salespeople need. Ultimately, identifying the useful insights and understanding your audience will result in a business deliverable, such as a master spreadsheet as shown in the following figure.
Analytics Tutorial Step 2: Compile Contact Information
The next step in the report and email analytics tutorial is compiling the contact information. This information may be stored in separate tables, so you may need to utilize SQL to join the information together. Essentially, we are compiling a list of e-mail addresses that match each salesperson’s ID. You’ll need to verify your data during this step because any errors could result in missing emails or other problems. See the following image for an example of the outcome table.
Analytics Tutorial Step 3: Splitting Master Excel into Separate Documents
At this point in the analytics tutorial, I am ready to split the Excel sheet into separate sheets to send to each salesperson. We can perform this task quickly using Python and Pandas. You can download the source code from the Actionable-Business-Analytics GitHub.
We can break this code into three sections:
- Lines 1-5: Importing necessary packages for manipulating data and interacting with outlook
- Lines 7-12: Loading Excel data tables and formatting columns
- Lines 14-17: Iterating through each unique salesperson ID to subset the master file based on that ID, and exporting that file to the loc_dest file location
In order to run this code yourself, you will need to edit any variables to match your specific files and file locations. You now have files split by Salesperson ID in a specified file path on your computer.
The next step is to connect to our current Microsoft Outlook session, iterate through each email address, and attach and send the separate files. Be sure to test the outgoing messages before running the whole program.
A rule of thumb: if it works for two iterations, it will work for all.
The code to perform these tasks is displayed below.
Let’s explore this code as well:
- Lines 19-21: Connect to your current Outlook session
- Line 24: Storing a dictionary that returns the email for each salesperson ID
- Lines 24-36: Iterate through each email address and send a message with the attached spreadsheet for that salesperson
If you look in the Sent Messages area of Outlook, you will see outgoing messages fill the screen. The automation method described in this analytics tutorial will save you much time and effort during a mass-email with attachments scenario.
If you have any questions, please post in the comments section or in our questions/answers area.
Feel free to take a look at Automate the Boring Stuff. It is a great resource to learn automation within python that provides many practical examples to try out.