Email Marketing Database

There is an overwhelming amount of information related to email marketing analytics, but a lack of information related to the details of the data munging required to make it usable.  Avinash Kaushik again provides an incredible resource for KPIs related to Email Marketing: Campaign Analysis, Metrics, Best Practices. This article briefly explains  an effective email data warehouse for measuring performance and how to connect it with additional data sources.

Some of the real insights that need to be achieved through email analytics are questions like:

  1. What % of our newsletter subscribers have opened or clicked on  an email within the last 90 days?
  2. Has that % improved compared to a trailing 180 days or trailing 12 months?
  3. Does email marketing increase r

Transactional Foundation

Frequently email data is stored by an email service provider like Exact Target or Lyris and only accessible via an API.  The data that is in-turn downloaded needs to be normalized and stored in relational tables that provide a foundation for later analysis.  The foundation should consist of four major components that is shown in below image and described accordingly, which in turn can be used for aggregates that can be used for analyzing engagement.


Message : any meta data related to an email, i.e. subject, sentdate

List: a subscriber list that is the starting point for a email newsletter, this list may be segmented for the purpose of a specific send

Member: the meta data for a subscriber that is used for segmentation…meta data frequently has to be updated from a CRM and uploaded to email marketing solution to in-turn perform segmentation; therefore, a unique id to the CRM should be included for later connection of data.

Transaction: every event that is able to be logged at an individual user and message level, i.e. open, click, send, or unsubscribe

Individual Summaries

Summarizing individual campaigns will provide understanding of how a campaign performed and compare to other campaigns.  This can provide answers on :

  • What day of the week results in most opens?
  • Do shock value subject lines result in better open rates without causing drastic increases in unsubscribes or decreased opens over time
  • Does a different sender result in more opens?
  • Does using a person’s first name in subject?
  • Does a different layout result in more clicks?
subject 3-D Visualizations with rotating
charts, for small and big data
John Smith, 3-D Visualizations
with rotating charts, for small and big data
3-D Visualizations with rotating
charts, for small and big data
sentdate 4/1/2013 4/1/2013 3/30/2013
recipients 30,000 30,000 30,000
send 30,000 30,000 30,000
total_bounces 180 150 165
unique_bounces 180 150 165
total_opens 3,643 3,643 4,568
unique_opens 3,600 3,600 4,500
12% 12% 15%
total_clicks 729 765 914
unique_clicks 398 398 398
CTR 20% 21% 20%
unsubscribes                            72                             76  90
0.24% 0.25% 0.30%


1.  Does a test in subject line have less impact then a date of an email send in causing more opens?

2.  Does using a person’s first name improve open rate?

3.  Do factors other than the individual email and individual person have an impact that I as a sender can consider?

To assess these hypothesises we will need to look at transactional data and then assess if these hypotheses can be assessed across multiple emails.  The next post will address these questions using the transactional level data and comparison of similar vairables.