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.


Micro-conversions are consumer actions that do not provide immediate revenue, but align with a company’s priorities.  Conversions (or macro-conversions) traditionally defined as purchases or completed sales versus micro-conversions that require additional steps or provide additional information about a consume.  Refer to Avinash Kaushik’s great post Excellent Analytics Tip #13: Measure Macro AND Micro Conversions for a thorough definition.

Ten Micro-conversions could have more value than a single conversion because those 10 micro-conversions assisted in 5 sales worth $1000 each vs. the single conversion only had a single purchase ($1000) and 2 referrals resulting in 1 additional purchase ($1000) for a total of $3000 being generated from the 10 micro-conversions.


Measurement of micro-conversions can be expanded to data sources beyond web analytics including Email, e-Commerce application’s databases and CRM solutions.  Future post to be linked to here.

Minimal Viable Product for Big Data

Big data promises marketers the opportunity of measuring more data then an organization can use by measuring actions on a large scale the following:

  • Increase in brand awareness from a tv ad
  • Decrease in cost for call centers by using an e-commerce solution
  • Increase in Facebook fans from a sweepstakes
  • Website traffic and purchases made from a visitor that clicked on a paid banner ad
  • Revenue generated from a Facebook share  a plethora of other activity that contribute to a company’s objectives
  • Increase in email subscribers from a decreased frequency of email communication

There are three major obstacles for this type of data making an impact within an organization.  This site will keep these three problems top of mind, but the first series of posts will describe how a minimal viable product for big data can be developed leveraging existing resources and open-source (free!) software hence eliminating costly infrastructure investment (2) .

  1. Infrastructure
  2. Data-Driven Culture
  3. Talent