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.