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.
Lexer clean and transform your data into straightforward representations of the raw data, making it easy for your team to search, analyze and activate.
|Email Address||The customer’s email address||Necessary for continuing email marketing, and for further unification|
|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|
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.
|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.
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.
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.
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:
- Lexer will automatically accept files that are in UTF-8 format, and will clean out rows that are not.
- Lexer only accepts flat CSV rows in files. Rows containing fields with newline-delimited text will be rejected.
- 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.
- Supported quote characters are “ and ‘.
- 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|
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|
|firstname.lastname@example.org||Y||Campaign X||Campaign X||2018-07-22||-|
|email@example.com||N||In Store||Opt Out Email||2018-01-02||-|
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.
|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.|
|Data Formats Accepted||Email Address|
|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.|
|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|
|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.|
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.
|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.|
|Datetime ISO8601||2018-07-23T14:22:18+10:00, 2018-07-23T14:22:18Z|
|Datetime DMY||23/07/2018 13:13:13, 23/07/2018 13:13, 23/07/2018 1:01 PM|
|Datetime MDY||07/23/2018 13:13:13, 07/23/2018 13:13, 07/23/2018 1:01 PM|
|Datetime YMD||2018/07/23 13:13:13, 2018/07/23 13:13, 2018/07/23 1:01 PM|
|Datetime YDM||2018/23/07 13:13:13, 2018/23/07 13:13, 2018/23/07 1:01 PM|