How we on board email data

Using your data in Lexer is easy, but setting it up can take time. Lexer’s Customer Data Platform ingests data from a variety of sources, and one of the most common is email data. Sending Lexer this data allows you to understand how customers engage with your emails, so you can make sure you’re marketing to them the right way.

What we do with data

When you send us your transactional data, we process it across 5 stages:

Auto-validation We check the format of the file, and the records in the file, to make sure they can be imported.
Validation We look at the data to make sure it’s what we expect it to be.
Enrichment We transform the data to turn it from what it is, into a set of value-add attributes.
Unification We unify your transaction data to other data you have provided, as well as third-party data sets.
Import We take the validated, enriched, and unified data, and import it into the Lexer product.

What we build

Once we receive the data, we will build a set of attributes you can query in Lexer. We either import it without transformation, or we change it so you can get more value from it. Here are the types of attributes we can build with this data.

Standard attributes

Lexer clean and transform your data into straightforward representations of the raw data, making it easy for your team to search, analyze and activate.

Attribute Code Description Value
Email Address The customer’s email address Necessary for continuing email marketing, and for further unification
Opt-In Status Whether the customer has opted in or out of messaging Make sure you’re complying with privacy policy, and other regulations, by only communicating with people who want to hear from you
Email Source Where the email address came from Understand where your customers have come from, so you can find more, or better engage with the ones you have

Value-add attributes

Lexer take all the data you have provided, and enrich it, often combining multiple data points to make them more useful than they are on their own.

Attribute Code Description Value
Open Rate What proportion of emails were opened by the customer Understand how often customers open your emails, so you can optimize their timing and subject copy
Click Rate What proportion of emails were clicked by the customer Understand how often customers click your emails, to see how effective your email messaging is
Sent Count How many emails the customer was sent Know how many times a customer has received an email from you, to help determine whether or not to continue marketing to them
Date of Last Click The last time an email was clicked Understand the recency of engagement
Date of Last Open The last time an email was opened Understand the recency of engagement
Email Engagement Category Whether a customer opens, clicks, is opted out of, or converts from emails See how customers engage with your emails, from not wanting to hear from you at all, to purchasing after emails are received
Converts Off Email Whether the customer made a purchase within 48 hours of receiving an email Find customers who are likely to make a purchase after receiving an email, and ensure the email is right for them.

How we unify

The data we receive can be matched to first, second, and third party sources. This could be your own data, partner data, or public data sources. To match the data, we need certain fields to do certain jobs.

Customer ID

This field is typically used to match to your other data sources, such as Transactional and CRM platforms. If this ID is used across your business, it will be the field that links across all systems.

Email address

This field is used to unify your data to Twitter, enable activation on various sales channels, and can be used to unify your data to certain second and third party sources.

Mobile

This field is used to unify on other data sets, such as Experian. Lexer transforms your mobile numbers to make sure they’re in a consistent format, ensuring the highest possible unification rates.

Detail for the IT team

To get up and running with this data right away, you need to make sure your data is formatted in a certain way. Although we work hard to make data easy for businesses to use, this process can be quite technical.

Each column is transformed in its own way, with rules around what we can import. Here is some guidance on the sort of data we accept:

  1. Lexer will automatically accept files that are in UTF-8 format, and will clean out rows that are not.
  2. Lexer only accepts flat CSV rows in files. Rows containing fields with newline-delimited text will be rejected.
  3. Lexer prefers the “PSV” format, where the pipe (“|”) character is used as the field separator. This helps to lessen the number of rejected rows. We also accept tab and “,” separated values.
  4. Supported quote characters are “ and ‘.
  5. Quotes inside quoted fields can either be escaped with "" or doubled.

Files you can send

Email data can come in two basic formats:

  • Address Level
  • Campaign Level

Both are suitable for almost all of the attributes you will want to build.

Address level example

Every row is the engagement summary of a single email address. This comes with pre-calculated rates, and inferred dates. This data format will enable every attribute, but every field below must be available for it to work.

Customer ID Email Address Opt In Status Source Open Rate Click Rate Emails Sent Last Open Date Last Click Date
995435 john@gmail.com Y Web 10% 5% 20 2018-06-21 2018-06-21
tqbf123 mary@hotmail.com Y Campaign X 20% 0% 10 2018-07-22 -
44321 michael@home.com N In Store 0% 0% 5 - -

Campaign level example

Every row in this file is an email a customer has received. This allows us to calculate the attributes ourselves.

Customer ID Email Address Opt In Status Source Campaign Name Open Date Click Date
995435 john@gmail.com Y Web Campaign A - -
995435 john@gmail.com Y Web Campaign X 2018-06-21 2018-06-21
tqbf123 mary@hotmail.com Y Campaign X Campaign X 2018-07-22 -
44321 michael@home.com N In Store Opt Out Email 2018-01-02 -

Required data

Unlike CRM and Transactional data, all of these fields are necessary to build Lexer’s attributes. Fields like email address, source, and opt in status are necessary for compliance reasons, while others are used to understand key engagement behaviors.

Customer ID  
Data Formats Accepted Numeric ID, Alphanumeric ID
Examples 995435, john.smith, tqbf123
Comments Along with Email address, this ID is ideally an identifier that is used in other files. If you do not have this ID, email address will be used as the primary identifier.
Email Address  
Data Formats Accepted Email Address
Examples john.smith@gmail.com
Comments Lexer automatically identifies email addresses as a block of text with a string (e.g. john.smith) before an ‘@’ symbol, another set of letters (e.g. gmail), then a full stop, then another set of letters (com). If any of these strings are less than 2 characters, they will be rejected.
Opt In Status  
Data Formats Accepted boolean
Examples Yes/No, TRUE/FALSE, 1/0, y/n, t/f, opted-in/opted-out
Comments Lexer automatically identifies boolean records, and will use it to calculate all return-related metrics. If there are more than 2 values in this column, it will not work correctly.
Source  
Data Formats Accepted text strings
Examples Campaign X, Website A, In-store
Comments This is any description of how you sourced the email. All strings will be accepted as is.
Click Rate (Address Level Only) Open Rate (Address Level Only)
Data Formats Accepted float
Examples 0.1, 0.10000
Comments Any value between 0 and 1 will be accepted.
Emails Sent (Address Level Only)  
Data Formats Accepted Integers
Examples 10.0, 0, 5
Comments Lexer will look for any whole number in this column, even if it has zeros after the decimal point. Anything else will be rejected.
Campaign Name (Campaign Level Only)  
Data Formats Accepted text strings
Examples Summer Sale, 50% Off, Loyalty Discount
Comments Any name you’ve designated for a campaign. All strings will be accepted as is.

Dates

The accepted date data types are:

  • Click Date (Campaign Level Only)
  • Last Open Date (Address Level Only)
  • Open Date (Campaign Level Only)
  • Last Click Date (Address Level Only) Each of these fields can be applied to one of the formats below.
Dates  
Data Formats Accepted datetime_iso8601, date_dmy, datetime_dmy, date_mdy, datetime_mdy, date_ydm, datetime_ydm, date_ymd, datetime_ymd (see Appendix for examples)
Comments A date column in this data will be identified as one of many date formats. If the column has multiple date formats in it, those not matching the identified format will be rejected.
Date Examples  
Datetime ISO8601 2018-07-23T14:22:18+10:00, 2018-07-23T14:22:18Z
Date DMY 23/07/2018
Datetime DMY 23/07/2018 13:13:13, 23/07/2018 13:13, 23/07/2018 1:01 PM
Date MDY 07/23/2018
Datetime MDY 07/23/2018 13:13:13, 07/23/2018 13:13, 07/23/2018 1:01 PM
Date YMD 2018/07/23
Datetime YMD 2018/07/23 13:13:13, 2018/07/23 13:13, 2018/07/23 1:01 PM
Date YDM 2018/23/07
Datetime YDM 2018/23/07 13:13:13, 2018/23/07 13:13, 2018/23/07 1:01 PM