Goal: To efficiently give an overview of cases that has an expected revenue of $10,000.
Outcome: I created a dashboard that turned hours of manual work into 10 minutes of updating.
The Problem
The firm partners reviewed their Impact Cases during their monthly meetings. Impact Cases were defined as cases in the Sales Pipeline where the Total Expected Revenue was at least $10,000.
The report was created manually by one partner in an Excel spreadsheet and was fairly labor intensive. He would take redundant notes during the weekly Sales Pipeline meeting. The notes were redundant because they were already captured in their CRM system, which is where the Sales Pipeline lived. The partner would also meet with an employee 1-2 times a month review the list, going down each record, to learn where they were in the pipeline.
The Design
Since the data lived in the CRM database, I created an SQL query to pull all the Sales Opportunities in the current year that had a Total Expected Revenue greater than or equal to $10,000. To make the report as automatic as possible, I used Excel's Power Query to establish a connection to a folder where the raw data could be downloaded from the CRM.
The raw data had modal revenue for the firm and agents. However, I would need annualized revenue to maintain consistent values. To do this inside Power Query,
1. I created a helper column ("Mode") to cast the "Premium Mode" column from a string to the corresponding integer. Because we're only looking at First Year Commission, this translates as Monthly = 12, Quarterly = 4, Semi-Annual = 2, and everything else = 1.
2. I used "Mode" to calculate the Annualized Revenue for each person/group.
The Solution
To be able to show all the desired information, I decided to break it up between two tabs.
The first tab is the dashboard showing a broad overview of the Total Expected Revenue broken down in three important ways:
1. Total Expected Revenue by Sales Pipeline Stage
2. Total Expected Revenue by Policy Type
3. Total Expected Revenue by Agent
The second tab allows the partners to drill down to see specific client information in two ways:
1. By Sales Pipeline Stage
2. By Referring Advisor
The second tab also importantly breaks down the Total Expected Revenue to where the commission goes. The partners' commissions go to the firm. There are two non-partner agents who have different commission splits. This allows the partners to see with a high degree of accuracy how much the firm can expect to get paid on each case.
Goal: To create a dashboard that summarizes email marketing metrics to help assist with strategic decisions.
Outcome: Created a dashboard and a minimally labor-intensive process to clean the data post extraction.
The Problem
The parish has a Communications Committee, which is 100% volunteer led. There was no marketing expert or digital analyst to help with their email marketing platform.
They use an email marketing platform called Flocknotes. Although this solution provides the standard email KPIs, it is not easily accessed. To see the email metrics, the admin has to go to each email that was sent, click on the open rate, and click through each metric. Additionally, not all metrics are summed up but drilled down. Worst of all, there is no way to export all this data.
The Data Cleaning
As the control experiment, manually pulled the data from 5 emails by copy and pasting it into an Excel spreadsheet and manually cleaning the data. Each email took approximately 30-45 minutes.
After understanding the data cleaning process required, I designed a "data cleaning" Excel spreadsheet. One tab cleaned the copied & pasted data, including a publicly-available macro to extract the URL from the hyperlink. Another tab counted the total number of clicks each email had. The final tab listed out each URL according to how many times the link was clicked (i.e., if the YouTube channel was clicked 20 times, it occupied 20 rows). Then the data could be added into a database. Each email took approximately 5 minutes.
By doing this process, I also learned that not all the links in the email were input cleanly, but had the URL encoded version of the link. This was because links were likely pulled from an email that was already sent, instead of the original version.
The Solution
It was important to the Communications Committee to know which emails were successful emails according to email open rates and click rates. This by knowing what emails did well, they could also see if there were any trends for certain email campaigns (i.e., fundraising, Christmas, Easter, Black History Month).
After reviewing the first dashboard that contained data from January - April, they are planning to create A/B testing with Subject Lines.
As an added bonus, I recreated the dashboard using PowerBI.
.png)
.png)