How we on board transactional 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 is a list of the attributes we can build with this data.

Standard attributes

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

Attribute Code Description Value
Products purchased Which products the customer has purchased Useful for understanding previous behavior, at a 1:1 level and across all customers. Users can gain a deeper understanding of product preferences, and tastes.
Product category purchased Which category of products the customer has bought Useful for understanding previous behavior, at a 1:1 level and across all customers. Users can gain a deeper understanding of product preferences, and tastes.
Size Purchased What product sizes a customer has purchased Useful for understanding previous behavior, at a 1:1 level and across all customers. Users can gain a deeper understanding of product preferences, and tastes.
Color Purchased What colors a customer has purchased Useful for understanding previous behavior, at a 1:1 level and across all customers. Users can gain a deeper understanding of product preferences, and tastes.
Stores Visited Which stores a customer has visited Used to infer the location of a customer, and to inform future Calls To Action

Value-add attributes

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

Attribute Code Description Value
Total Spend The total amount spent by a customer across all channels Used to inform lifetime value of one or many customers.
Annual Spend The total amount spent in a 1 year period (e.g. Last 12 Months) or annually (e.g. Total Spend in 2017) Used to inform a customer’s loyalty, spend cycles, and recency of purchase.
Total Online Spend The total amount spent on E-Commerce platforms Understand the value of a customer’s online experience.
Total Retail Spend The total amount spent in-store Understand the value of a customer’s in-store experience.
Average Spend Per Product The average amount the customer spends on each product Know, at a glance, how much customers tend to spend on products.
Average Spend Per Order The average amount the customer spends per order Know, at a glance, how much customers tend to spend each time they interact with you.
Total Orders How many orders a customer has made Understand how frequently a customer has bought from you.
Annual Orders The total number of orders made in a 1 year period (e.g. Last 12 Months) or annually (e.g. Total Spend in 2017) Used to inform a customer’s loyalty, spend cycles, and recency of purchase.
Last Order Date The last time a customer ordered a product Understand if you have a new, loyal, or lapsed customer.
First Order Date The first time a customer ordered a product Understand if you have a new, loyal, or lapsed customer.
Total Returns How many returns the customer has made Know if a customer is happy with your products, so you can intervene for help.
Return Rate What percentage of products were returned Know if a customer is happy with your products, so you can intervene for help.
Total Return Value The total value of all products returned Know if a customer is happy with your products, so you can intervene for help.
Spend Decile Split into 10 groups, how much does the customer spend Identify your low, medium, and high spend customers without having to do any data analysis.
Discount Buyer Identifies customers who have bought at a discount Segment your customers into discount and non-discount buyers for targeting and offers.
Buys Premium Products Whether the customer buys more expensive products, relative to all products in the category Know if this customer pays a premium for their products, so you can make a greater effort to have them return.
Transaction Segment Splitting customers into single order, single product, and multi-order segments Assigning customers to these segments allows marketers to understand how engaged a customer is with your brand.
Channel Preference The most common sales channel a customer purchases from Know where to direct a customer to complete their sale, based on previous behavior.
Closest Store Which store is closest to the customer Understand the customers within 10km of each store, and direct them there to complete a purchase.

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 CRM and Email Engagement 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. If this field is only available in your CRM system, then the Customer ID in your Transaction file should match the IDs in the CRM file.

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.

Product ID/SKUs

This field is matched to product files, capturing richer descriptions of categories and products your customers purchase.

Order ID

This field is used ot capture information about an order from a variety of sources. Order IDs are a great way to match all data about a purchase a customer has made.

Store ID/Name

This field is typically attached to a specific sales channel. Orders with store information can be unified to data about stores to understand channel behaviors.

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

Some fields are necessary to get basic transactional data up and running in Lexer. Every transactional system is different, and some of the data below may appear in one file, or multiple files. As long as there are fields we can match on, and the fields in these files contains the right data, we can build the attributes you need. Email data can come in two basic formats:

  • Transaction File: the file containing details about orders
  • Product File: the file with details about the products that were purchased
  • Store File: the file containing information about the store or channel the product was purchased from

Transaction file

We accept files that are one row per product. In this way, an order is split across multiple rows, and a customer can have multiple orders. Transaction files with linkage columns like Product ID can link to information in another file.

Customer ID Order ID Order Date Price Paid Discount Amount Product ID Store ID
995435 123999 2018-07-25 $50.30 - 12345678 14
995435 123999 2018-07-25 $30.12 - 44563352 14
995435 456999 2015-03-12 $10.21 - 34141241 18
tqbf123 334543 2018-07-12 $42 - 95428485 26
tqbf123 334543 2018-07-12 $19.20 - 23593281 26
tqbf123 12311 2017-02-10 $5 $10 59923951 99
44321 99542 2018-01-04 $354 - 52923883 4

Product file

The product file contains additional information about products purchased. To capture this information, there must be a common link with the transaction file. In this case it’s Product ID.

Product ID Product Name Full Price Product Category Color Size
12345678 Men’s Hat $50.30 Hats Black L
44563352 Women’s Shoes $30.12 Shoes Red W-8
34141241 Boy’s Mittens $10.21 Accessories Blue S
95428485 Girl’s Shoes $42 Shoes Green G-5
23593281 Men’s Shirt $19.20 Shirts Black XL
59923951 Boy’s Hat $15 Hats Orange M
52923883 Premium Jacket $354 Jackets Blue L

Store file

The product file contains additional information about the place the product was purchased from. To capture this information, there must be a common link with the transaction file. In this case it’s Store ID.

Store ID Store Name Store Latitude Store Longitude
14 Sydney 151.2093 -33.8688
18 Melbourne 144.9631 -37.8136
26 New York -74.0060 40.7128
4 London 51.5099 -0.1181
99 Website - -

Required data

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.
Order ID  
Data Formats Accepted Numeric ID, Alphanumeric ID
Examples 123999, ABC999
Comments This is an identifier for a single transaction. This is used to determine the number of products per order, spend per order, and total orders.
Order Date  
Data Formats Accepted datetime_iso8601, date_dmy, datetime_dmy, date_mdy, datetime_mdy, date_ydm, datetime_ydm, date_ymd, datetime_ymd
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

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.

Price Paid  
Data Formats Accepted Integers, Floats, Currency
Examples 10, 10.50, $10.50
Comments Lexer will detect a column that has either a numeric value, or a numeric value with a currency prefix (e.g. $, £). It will strip away the currency and import the raw numeric value. Only one currency can be received per field, and it will display with the currency in the Lexer product. Any value not matching this format will be rejected.
Product Name  
Data Formats Accepted text strings
Examples hats, ford falcon, SAMSUNG GALAXY S9, 12311 MEN’S HAT
Comments Lexer will capture a product field in its raw format, and not pre-process it. This is because product names are so varied that they are difficult to identify. Whatever is in this column will be imported as is.

Preferred data

Return Flag  
Data Formats Accepted boolean
Examples Yes/No, TRUE/FALSE, 1/0, y/n, t/f
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.
Product Category  
Data Formats Accepted text strings
Examples Menswear, Summer Collection, High Value
Comments Much like Product Name, this is any higher level categorization of your product that you define within your business. Whatever is in this column will be imported as is.
Full Price  
Data Formats Accepted Integers, Floats, Currency
Examples 10, 10.50, $10.50
Comments Lexer will detect a column that has either a numeric value, or a numeric value with a currency prefix (e.g. $, £). It will strip away the currency and import the raw numeric value. Only one currency can be received per field, and it will display with the currency in the Lexer product. Any value not matching this format will be rejected.
Discount Amount  
Data Formats Accepted Integers, Floats, Currency
Examples 10, 10.50, $10.50
Comments Lexer will detect a column that has either a numeric value, or a numeric value with a currency prefix (e.g. $, £). It will strip away the currency and import the raw numeric value. Only one currency can be received per field, and it will display with the currency in the Lexer product. Any value not matching this format will be rejected.
Size  
Data Formats Accepted text strings
Examples S, Small, SMA
Comments Lexer will capture a product field in its raw format, and not pre-process it. Whatever is in this column will be imported as is.
Color  
Data Formats Accepted text strings
Examples Red, RD, Maroon, #800000
Comments Lexer will capture a product field in its raw format, and not pre-process it. Whatever is in this column will be imported as is.
Store Name  
Data Formats Accepted text strings
Examples Melbourne, AWESOME BRAND - MEL, Melbourne Victoria
Comments Lexer will capture a product field in its raw format, and not pre-process it. This is because store names are so varied that they are difficult to identify. Whatever is in this column will be imported as is.
Store Latitude  
Data Formats Accepted floats
Examples -37.8640, -37.8
Comments This data will be combined with the longitude field to derive a Geo-location for the customer. If either value is incorrect, the point will also be incorrect. This value should match up to the store.
Store Longitude  
Data Formats Accepted floats
Examples 144.9820, 144.98
Comments This data will be combined with the latitude field to derive a Geo-location for the customer. If either value is incorrect, the point will also be incorrect. This value should match up to the store.