Data Models Overview

Initial data export steps are explained below to help guide you through the process of your integration.

Tables below are the main source of information for your data export. It holds all data categories (Transactions, Items, Leaflets, Customers, Stores) listed by Solver AI Suite studios (i.e. Segmentation Studio, Campaigning Studio, Touchpoint Studio,...) with all the details and necessity level (mandatory, recommended, optional). Examples of source data are also given in a separate page to illustrate how the data you’re sending us should look like.

Unique files are generated for each data category (eg. Items, Customers, Transactions etc)

Where?

Data is delivered on AWS S3 under bucket with clients name (eg. s3://sais-client_name). Credentials for clients bucket on Solver infrastructure are delivered through email and sms.

Entity	Path
Transactions	s3://sais-{client_name}/initial-data/transactions/transactions_{date}.csv
Items	s3://sais-{client_name}/initial-data/items/items.csv
Customers	s3://sais-{client_name}/initial-data/customers/customers.csv
Leaflet	s3://sais-{client_name}/initial-data/leaflet/leaflet.csv
Store	s3://sais-{client_name}/initial-data/stores/stores.csv
All the above listed paths are fixed and used for initial data export as well as all future daily data exports.

How often?

All data is delivered once per day, ideally during the night (around 2-3am). Transactions data is delivered from the previous day.

Data Storage (S3) Instructions

We create 'sais-{client_name}' bucket on S3. Your data should be stored in folder 'initial-data'. If you have never used S3, there are 4 steps that are useful:

The first step is to install a command line for AWS (aws-cli). On the next link you can find steps for each OS. For Windows, you will use the graphical interface, but for Linux, it’s enough to copy the first block from the documentation.
After installation, you need to add the user profile via the command: 'aws configure import --csv file:///path/to/file/sais-{client_name}.csv'. This CSV file is attached to the email Things Solver team provided to the client after initial environment setup. That email contains credentials for accessing the S3.
It’s possible to check if the profile is added with the next command: 'aws configure list-profiles'. The output of the command should be a list of all users (if there are more than one), and one of them is 'sais-{client_name}'.
To upload files to S3, it is necessary to use the command 'cp' in the next format: 'aws s3 cp /path/to/file/data_to_upload.csv s3://sais-client_name/initial-data/folder-in-which-you-want-to-uload/ --profile sais-{client_name}'.

After setup, it is possible to upload all files from the local directory. For CSV files, you can use the next command:

aws s3 cp /path/to/folder s3://sais-client_name/initial-data --recursive --exclude"*" --include ".csv" --profile sais-client_name

As the bucket made for you has a predefined structure, you should take into account the location where you are sending the data. All the data you send should be in a folder *'s3://sais-{clinet_name}/initial-data'*.

Data Models

Transactions

Notes

Combination of transaction id, customer id, item id, purchase date, quantity, price is a unique raw are included

Entity	Description	Type	Is personal	Customer studio	Campaigning nonpersonalized	Campaigning personalized	Touchpoint recommender	Touchpoint search
transaction_id	Transaction/Invoice ID (unique ID of every transaction)	string	FALSE	mandatory	mandatory	mandatory	mandatory	optional
transaction_type	Type of the transaction completed or other	string	FALSE	recommended	recommended	recommended	recommended	optional
transaction_status	Status of the transaction	string	FALSE	recommended	recommended	recommended	recommended	optional
transaction_datetime	Timestamp of the transaction	datetime	FALSE	mandatory	mandatory	mandatory	mandatory	optional
customer_id	Unique customer ID if available (loyalty card or similar)	string	TRUE	mandatory	mandatory	mandatory	mandatory	optional
item_id	Unique item/product ID	string	FALSE	mandatory	mandatory	mandatory	mandatory	optional
quantity	Purchased quantity	float	FALSE	mandatory	mandatory	mandatory	mandatory	optional
price	Price paid including vat (per transaction_id/item_id/quantity combination)	float	FALSE	mandatory	mandatory	mandatory	mandatory	optional
price_excluding_vat	Price paid excluding vat (either for a single item/product or all if more than one in quantity)	float	FALSE	optional	optional	optional	optional	optional
original_price	Original item/product price	float	FALSE	optional	optional	optional	optional	optional
vat	vat amount for paid price	float	FALSE	optional	optional	optional	optional	optional
currency	Currency of country where purchase happen	string	FALSE	mandatory	mandatory	mandatory	mandatory	optional
discount_percent	Percent amount discount is applied	float	FALSE	optional	recommended	recommended	recommended	optional
loyalty_points	Whether any loyalty points were earned with the purchase	float	FALSE	optional	recommended	recommended	recommended	optional
delivery_phone	Customers phone used in transaction/order	string	TRUE	optional	optional	optional	optional	optional
delivery_email	Customers email used in transaction/order	string	TRUE	optional	optional	optional	optional	optional
delivery_address	Customers address used in transaction/order	string	TRUE	optional	optional	optional	optional	optional
seller_id	Seller ID for item purchased in transaction	string	FALSE	optional	optional	optional	optional	optional
carrier_id	Carrier ID handled delivery for item purchased in transactions	string	FALSE	optional	optional	optional	optional	optional
store_id	ID of the store where the purchase was made	string	FALSE	recommended	recommended	recommended	optional	optional

Items

Notes

Item Id must be unique and to match the item_id from Transactions and any other entity group

Entity	Description	Type	Is personal	Customer studio	Campaigning nonpersonalized	Campaigning personalized	Touchpoint recommender	Touchpoint search
item_id	Unique item/product ID	string	FALSE	mandatory	mandatory	mandatory	mandatory	mandatory
is_active	Flag if item/product is active	boolean	FALSE	mandatory	mandatory	mandatory	mandatory	mandatory
alternative_item_id	Unique alternative item/product id in case of two system's usage	string	FALSE	optional	optional	optional	optional	optional
item_name	Item/Product name	string	FALSE	mandatory	mandatory	mandatory	mandatory	mandatory
item_description	Detailed item/product description	string	FALSE	recommended	optional	optional	recommended	recommended
item_sms_name	Item/Product name for sms message	string	FALSE	optional	optional	mandatory	optional	optional
item_promotion_name	Item/Product name for viber, whatsapp and other online messages platforms	string	FALSE	optional	optional	mandatory	mandatory	mandatory
price	Current price of item/product	float	FALSE	mandatory	mandatory	mandatory	mandatory	mandatory
original_price	Original price of item/product without any special offer	float	FALSE	recommended	recommended	recommended	recommended	recommended
currency	Currency of country where purchase happen	string	FALSE	mandatory	mandatory	mandatory	mandatory	mandatory
discount_percent	Percent amount discount is applied	float	FALSE	optional	recommended	recommended	recommended	recommended
loyalty_points	Loyalty point that can be achivied by purchasing the item/product	float	FALSE	recommended	recommended	recommended	recommended	recommended
unit_measure	Detailed item/product description	string	FALSE	recommended	optional	optional	recommended	recommended
item_main_image	Detailed item/product description	string	FALSE	recommended	optional	optional	recommended	recommended
item_images	More item images in string devided by ;	string	FALSE	recommended	optional	optional	recommended	recommended
item_url	URL to item page on webshop	string	FALSE	recommended	optional	optional	mandatory	mandatory
category_level_0	Null category level	string	FALSE	mandatory	optional	optional	recommended	recommended
category_level_1	Lowest category for the item/product	string	FALSE	recommended	optional	optional	optional	optional
category_level_2	Second lowest category of the item/product	string	FALSE	recommended	optional	optional	optional	optional
category_level_3	Third lowest category of the item/product	string	FALSE	recommended	optional	optional	optional	optional
category_level_4	Fourth lowest category of the item/product	string	FALSE	recommended	optional	optional	optional	optional
category_level_5	Fifth lowest category of the item/product	string	FALSE	recommended	optional	optional	optional	optional
category_level_6	Sixth lowest category of the item/product	string	FALSE	recommended	optional	optional	optional	optional
category_level_7	Seventh lowest category of the item/product	string	FALSE	recommended	optional	optional	optional	optional
category_level_8	Eight lowest category of the item/product	string	FALSE	recommended	optional	optional	optional	optional
category_level_9	Ninth lowest category of the item/product	string	FALSE	recommended	optional	optional	optional	optional
attribute_{filter}	Attribute filtere example: Brand, value Bambi (up to 100 different filters)	string	FALSE	recommended	optional	optional	recommended	recommended
additional_item_data	Additional item data in string-array	string	FALSE	recommended	optional	optional	recommended	recommended

Leaflet

Entity	Description	Type	Is personal	Customer studio	Campaigning nonpersonalized	Campaigning personalized	Touchpoint recommender	Touchpoint search
item_id	Unique item/product ID	string	FALSE	mandatory	recommended	mandatory	recommended	optional
alternative_item_id	Unique alternative item/product id in case of two system's usage	string	FALSE	optional	optional	optional	optional	optional
is_active	Flag if item/product is active	boolean	FALSE	mandatory	recommended	mandatory	recommended	optional
item_name	Item/Product name	string	FALSE	recommended	recommended	mandatory	recommended	optional
item_description	Detailed item/product description	string	FALSE	recommended	optional	optional	recommended	optional
item_sms_name	Item/Product name for sms message	string	FALSE	recommended	recommended	mandatory	optional	optional
item_promotion_name	Item/Product name for viber, whatsapp and other online messages platforms	string	FALSE	recommended	recommended	mandatory	mandatory	optional
price	Current price of item/product	float	FALSE	mandatory	recommended	mandatory	mandatory	optional
currency	Currency of country where purchase happen	string	FALSE	mandatory	mandatory	mandatory	mandatory	mandatory
original_price	Original price of item/product when product is not in special offering	float	FALSE	recommended	optional	recommended	mandatory	optional
discount_percent	Percent amount discount is applied	float	FALSE	optional	optional	recommended	recommended	optional
loyalty_points	Loyalty point that can be achivied by purchasing the item/product	float	FALSE	recommended	recommended	recommended	recommended	optional
leaflet_id	Unique leaflet id	string	FALSE	optional	recommended	recommended	recommended	optional
leaflet_name	Leaflet name	string	FALSE	optional	recommended	recommended	recommended	optional
offer_start_datetime	Start of leaflet validity	datetime	FALSE	optional	recommended	recommended	recommended	optional
offer_end_datetime	End of leaflet validity	datetime	FALSE	optional	recommended	recommended	recommended	optional

Customers

Notes

Customer Id must be unique and to match customer from Transactions and any other entity group

Entity	Description	Type	Is personal	Customer studio	Campaigning nonpersonalized	Campaigning personalized	Touchpoint recommender	Touchpoint search
customer_id	Unique customer ID (loyalty card or similar)	string	TRUE	mandatory	mandatory	mandatory	mandatory	optional
is_active	Flag if customer is active	boolean	FALSE	mandatory	mandatory	mandatory	mandatory	optional
alternative_id	Unique alternative_id (loyalty card or similar)	string	FALSE	recommended	recommended	recommended	recommended	optional
mobile_device_token	Token generated through devices	array of string	FALSE	optional	optional	optional	recommended	optional
cookie	Cookie generated through browsers	array of string	TRUE	optional	optional	optional	recommended	optional
first_name	Customer's first name	string	TRUE	recommended	optional	recommended	optional	optional
last_name	Customer's last name	string	TRUE	recommended	optional	recommended	optional	optional
phone_number	Phone number to communicate	string	TRUE	optional	mandatory	mandatory	optional	optional
email	Email to communicate	string	TRUE	optional	mandatory	mandatory	optional	optional
gender	Gender of the customer	string	TRUE	recommended	optional	optional	optional	optional
birth_date	Date of birth of the customer	string (format "1970-01-31")	TRUE	recommended	optional	optional	optional	optional
city	City of the customer	string	TRUE	recommended	optional	recommended	optional	optional
customer_since	Date when the customer's loyalty card was issued	datetime	FALSE	recommended	optional	optional	optional	optional
zip_code	Zip code for customer's location	string	TRUE	recommended	optional	optional	optional	optional
attribute_{filter}	Additional data that can help provide richer insights (up to 100 different filters)	string	TRUE	recommended	optional	optional	optional	optional
email_consent	Flag if customer want to be contacted through email	boolean	FALSE	recommended	mandatory	mandatory	optional	optional
viber_consent	Flag if customer want to be contacted through viber	boolean	FALSE	recommended	mandatory	mandatory	optional	optional
sms_consent	Flag if customer want to be contacted through sms	boolean	FALSE	recommended	mandatory	mandatory	optional	optional
avg_basket_value (derived)	Average value of all baskets for customer	float	FALSE	optional	optional	optional	optional	optional
avg_basket_size (derived)	Average number of items in baskets for customer	float	FALSE	optional	optional	optional	optional	optional
avg_spent (derived)	Average spent amount of money by customer	float	FALSE	optional	optional	optional	optional	optional
avg_number_of_visits (derived)	Average number of visits by customer	float	FALSE	optional	optional	optional	optional	optional
dominant_store (derived)	ID of dominant store	string	FALSE	optional	optional	optional	optional	optional

Stores

Notes

Store Id must be unique and to match stores from Transactions and any other entity group

Entity	Description	Type	Is personal	Customer studio	Campaigning nonpersonalized	Campaigning personalized	Touchpoint recommender	Touchpoint search
store_id	Unique store ID	string	FALSE	recommended	optional	recommended	optional	optional
is_active	Flag if store is active	boolean	FALSE	recommended	optional	recommended	optional	optional
store_name	Store name	string	FALSE	recommended	optional	recommended	optional	optional
online store	If store is online or physical	boolean	FALSE	recommended	optional	recommended	optional	optional
coordinates	Coordinate locations of stores	Array of floats	FALSE	optional	optional	optional	optional	optional
address	Address of the store	string	FALSE	recommended	optional	recommended	optional	optional
city	City of the store	string	FALSE	recommended	optional	recommended	optional	optional
region	Region of the store	string	FALSE	recommended	optional	recommended	optional	optional
country	Country of the store	string	FALSE	recommended	optional	recommended	optional	optional
store_size	Size of the store	Optional	FALSE	optional	optional	optional	optional	optional