Ecommerce

General Data Requirements

Before preparing your data tables, please review the following universal formatting rules.

Data Formatting

Decimal Precision: All decimal-type attributes must be rounded to a maximum of four decimal places. For example, a value of 4.56789 must be formatted as 4.5679. This applies to all monetary fields and other decimal values.
Currency Conversion: All monetary figures must be converted into a single, consistent currency across all tables.

Required Data Tables

The following data tables describe the content, format, and labels of the required data.

Customers – Each row describes the attributes of a single customer
Orders_Summary – Each row represents an order transaction
Orders_Items – Each row represents an item within a transaction
Items_Catalog – Each row represents item IDs and their associated categories, departments, and brands

Customers

Key	Field	Data Type	Description
PK	Customer_ID**	string/int	Unique customer identifier
	Registration_Date**	date	Date the customer registered
	Email*	string (max 250)	Mandatory when using Optimail or if required by external ESP
	Mobile_Number*	string	Mandatory if required by external service provider
	Date_Of_Birth	date	Customer’s date of birth
	Is_OptIn	string	Determines whether it is acceptable to send promotional messages to the specified player. Should hold the values 'Yes' or 'No'.
	Allow_Email*	string	Determines whether sending promotional Email messages to the specified email address is acceptable. Should hold the values 'Yes' or 'No'.
	Allow_SMS*	string	Determines whether sending promotional SMS messages to the specified mobile address is acceptable. Should hold the values 'Yes' or 'No'.
	Allow_Push*	string	Determines whether sending promotional Push messages to the specified mobile number is acceptable. Should hold the values 'Yes' or 'No'.
	Is_Email_Verified	string	Determines whether the email address is verified. Should hold the values 'Yes' or 'No'.
	Is_SMS_Verified	string	Determines whether the mobile number is verified. Should hold the values 'Yes' or 'No'.
	Is_Blocked	string	'No’ = regular customer ; 'Yes’ = blocked customer (e.g. fraud)
	Is_Test	string	'No’ = regular customer ; 'Yes’ = test customer
	Gender	string	Customer’s gender. Must hold values ‘Male’, ‘Female’, ‘Unknown’
	Country	string	Customer’s country
	City	string	Customer’s city
	Address	string	Customer’s address
	First_Name	string	Customer’s first name
	Last_Name	string	Customer’s last name
	Language	string	Customer’s language
	Currency	string	Customer’s currency
	Referral_Type	string	The method by which the customer was referred to your site (e.g., SEO, Affiliate, Advertising, Marketing, etc.).
	Registered_Platform	string	The platform the customer had registered with (e.g. Web, Android App, iOS App, etc.)
	Last_Login_Date	date	The date of the customer’s last login to the website
	Allow_Whatsapp	string	Determines whether sending promotional WhatsApp messages to the specified mobile number is acceptable. Should hold the values 'Yes' or 'No'.
	Updated_Date	date	Date when the record was last modified or added (Mandatory in case of DB to DB connection)
	Is_Optin_Email_Time_Stamp*	Timestamp	Determines when it is acceptable to send promotional Email messages to the specified email address in case of approval

Notes:

Please include any additional information that you find helpful
IsOptinEmailTimeStamp* - In case Optimove will manage the “unsubscribe” for the Opti-Mail integration please include an additional column to describe when the customer opts in \ out, to allow perfect sync with the daily batch.
The email field must not exceed 250 characters (varchar(250)). This constraint is required to ensure compatibility with database and external ESP systems.

*=Mandatory if required for the execution channel integration

**=Always mandatory

Orders_Summary

Key	Field	Data Type	Description
PK, FK	Order_ID**	string	Unique order identifier
FK	Customer_ID**	string	ID of the associated customer
	Number_of_Items	int	The number of items purchased in the order
	Payment_Method	string	The method of payment method (e.g. Credit Card, PayPal, etc.)
	Total_Refund_Amount**	decimal	The total amount that was refunded in the order
	Total_Item_Price	decimal	The total price of all items in the order, before deductions.
	Discount	decimal	Total discount granted
	Shipping_Cost	decimal	Costs of shipping
	Total_Tax	decimal	Total tax amount of order
	Total_Payment**	decimal	Final payment amount made by the customer (after refund and cancellations deduction)
	Order_Date**	date	Date the order was placed
	Store_Type	string	Type of store associated with the order. Should hold values ‘Web’ or ‘Store’.
	Promo_Code	string	ID of the promo code used for the purchase.
	Updated_Date**	date	Date the record was updated or added.

Note: Please include any additional information that you find helpful.

** Always Mandatory

Order_Items

Key	Field	Data Type	Description
PK	Order_Item_ID**	string	Unique identifier of an item in an order
FK	Order_ID**	string	Unique order identifier
FK	Product_ID**	string	Unique product identifier
	SKU	string	SKU of the purchased item
	Number_of_Items	int	The number of items purchased of the product.
	Item_Price	decimal	The price of a single item
	Discount	decimal	The discount applied to the item*
	Refund_Amount	decimal	The amount that was refunded for items
	Return_Date	date	Date of the return of items

Note: Please include any additional information that you find helpful.

** Always Mandatory

*If a discount is given on the entire order, the value should be 0

Items_Catalog

Key	Field	Data Type	Description
PK, FK	Product_ID**	string	Unique product identifier
	Item_Name	string	Name of item
	Item_Description	string	Description of item
	Category	string	Item category
	Department	string	The department associated with the item
	Brand	string	The brand associated with the item
	Last_Updated	date	Last date the item was updated or added.

Note: Please include any additional information that you find helpful.

** Always Mandatory

General Data Requirements

Data Formatting

Required Data Tables

Customers

Orders_Summary

Order_Items

Items_Catalog

Database Schema