Skip to content

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 Studios (Product Studio, Campaigning Studio, Touchpoint Studio and segmentation, recommender, CLV models etc) 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/products/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:
  1. 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.
  2. 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.
  3. 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}'.
  4. 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 invoice id, customer id, product 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 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 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 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 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 product_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
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
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 string FALSE recommended recommended recommended recommended recommended
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/productS 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

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
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
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 string FALSE recommended 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
age Age of the customer integer 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 Average value of all baskets for customer float FALSE optional optional optional optional optional
avg_basket_size Average number of items in baskets for customer float FALSE optional optional optional optional optional
avg_spent Average spent amount of money by customer float FALSE optional optional optional optional optional
avg_number_of_visits Average number of visits by customer float FALSE optional optional optional optional optional
dominant_store 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
Back to top