Data model
- 27 Feb 2025
- Print
- DarkLight
Data model
- Updated on 27 Feb 2025
- Print
- DarkLight
Article summary
Did you find this summary helpful?
Thank you for your feedback
To generate reports and data visualizations, Zilliant Gen AI Analytics aggregates and analyzes multiple data streams. The first step to harnessing its capabilities is uploading your data in CSV format, where Analytics validates your files for common errors and accepts or rejects the files. For more details, read Data upload.
To ensure Analytics generates meaningful reports, upload the following files. These files must meet Zilliant’s data input specifications.
accounts—Customer master data.
products—Product master data.
product_categories—(Optional.) Necessary only if there are multiple levels of product hierarchy.
supplier_sku_lookups—(Optional.) Necessary only if costs change by supplier.
transactions—Transaction records.
transaction_line_items—Transaction line item records.
Data specifications
The following tables display required and optional fields for your files. Rows of required fields are marked “Yes” in the Required column and are highlighted in blue.
The following terms may appear in Analytics’ data input specifications.
FK—A foreign key. This field is a column that points to a primary key in another table. It establishes and enforces a link between the two tables and their data.
Int—An integer.
ISO 3166 alpha-3—The ISO 3166 alpha-3 country format is a three-letter code. For a list of ISO 3166 country codes, go to ISO’s Online Browsing Platform, select the Country codes filter, and select Search.
Numeric(p,s)—Fixed precision and scale numbers. The p parameter indicates the maximum total number of digits that can be stored. The s parameter indicates the maximum number of digits stored from zero to p.
PK—A primary key. This field is a column that individually or in combination with other columns uniquely identifies each row in a database table.
TimestampTZ—A time stamp with the time zone.
Varchar(n)—A string of variable length. A string can contain letters, numbers, and special characters. The n parameter indicates the string’s maximum character length from zero to n.
accounts
This file should contain the following fields.
Field | Type | Required | Attributes | Notes |
|---|---|---|---|---|
account_id | Varchar(250) | Yes | PK Customer-supplied | For definitions of PK and Varchar(n), read Data specifications. |
account_name | Varchar(250) | Yes | Customer-supplied | |
industry_segment | Varchar(100) | No | Customer-supplied | |
shipping_address | Varchar(250) | No | Customer-supplied | |
shipping_address_2 | Varchar(250) | No | Customer-supplied | |
shipping_city | Varchar(75) | No | Customer-supplied | |
shipping_principal_subdivision | Varchar(100) | No | Customer-supplied | State/province/administrative division. |
shipping_postal_code | Varchar(10) | No | Customer-supplied | |
shipping_extended_postal_code | Varchar(10) | No | Customer-supplied | |
shipping_country | Varchar(3) | No | Customer-supplied | Country code in ISO 3166 alpha-3 format. |
billing_address | Varchar(250) | No | Customer-supplied | |
billing_address_2 | Varchar(250) | No | Customer-supplied | |
billing_city | Varchar(75) | No | Customer-supplied | |
billing_principal_subdivision | Varchar(100) | No | Customer-supplied | |
billing_postal_code | Varchar(10) | No | Customer-supplied | |
billing_extended_postal_code | Varchar(10) | No | Customer-supplied | In the US, this is the +4 in a ZIP code. |
billing_country | Varchar(3) | No | Customer-supplied | Country code in ISO 3166 alpha-3 format. |
products
This file should contain the following fields.
Field | Type | Required | Attributes | Notes |
|---|---|---|---|---|
customer_product_id | Varchar(250) | Yes | PK | Customer-supplied product ID. |
product_name | Varchar(200) | Yes | Customer-supplied | |
category_name | Varchar(200) | No | FK | Foreign key to For the definition of FK, read Data specifications. |
sku | Varchar(200) | Yes | Customer-supplied | |
description | Varchar(1000) | No | Customer-supplied | |
supplier_id | Varchar(100) | No | FK | |
unit_cost | Numeric(10,4) | Yes | Customer-supplied | For the definition of Numeric(p,s), read Data specifications. |
product_categories
This file should contain the following fields.
Field | Type | Required | Attributes | Notes |
|---|---|---|---|---|
category_name | Varchar(200) | Yes | PK | |
family_name | Varchar(200) | No | Customer-supplied |
supplier_sku_lookups
This file is optional. If you choose to upload this file, it must contain the following fields.
Field | Type | Required | Attributes | Notes |
|---|---|---|---|---|
supplier_id | Varchar(250) | Yes | PK | |
supplier_name | Varchar(250) | Yes | Customer-supplied | |
sku | Varchar(250) | Yes | Customer-supplied | |
sku_supplier_cost | Numeric(10,4) | Yes | The cost of the SKU from the specific supplier. |
transactions
This file should contain the following fields.
Field | Type | Required | Attributes | Notes |
|---|---|---|---|---|
transaction_id | Varchar(100) | Yes | PK | Customer’s transaction ID. |
account_id | Varchar(250) | Yes | FK | |
total | Numeric(10,2) | Yes | Customer-supplied | Transaction total down to the hundredth place. |
quote_datetime | TimestampTZ | No | Customer-supplied | Time transaction was considered and executed as determined by the customer. |
order_datetime | TimestampTZ | Yes | Customer-supplied | Customer-supplied in case the transaction’s execution date and the effective date are different. |
ship_datetime | TimestampTZ | No |
transaction_line_items
This file should contain the following fields.
Field | Type | Required | Attributes | Notes |
|---|---|---|---|---|
transaction_line_item_id | Int | Yes | PK | For the definition of Int, read Data specifications. |
transaction_id | Varchar(100) | Yes | PK | When combined with transaction_line_item_id, this will give you a unique record. |
customer_product_id | Varchar(250) | No | Customer-supplied | Customer-supplied product ID. |
order_quantity | Numeric(10,4) | Yes | Customer-supplied | Amount of each product ordered, subject to unit of measurement and pack size. |
unit_cost | Numeric(10,4) | No | Customer-supplied | Customer’s cost. |
currency_price | Numeric(10,4) | No | Customer-supplied | Unit of cost in native currency. |
currency | Varchar(3) | Yes | Currency for the given line item. | |
currency_cost | Numeric(10,4) | No | Customer-supplied | The unit_price in native currency. |
unit_price | Numeric(10,4) | Yes | Customer-supplied | Amount a customer paid for one of the items in question within the bounds of the transaction. Can differ from the list price. Excludes shipping and includes discounts. |
list_price | Numeric(10,4) | No | Customer-supplied | List price. Usually different from the unit price. |
line_total | Numeric(10,4) | No | Customer-supplied | Amount paid for each line item. If quantity is equal to one, this will equal the unit_price. |
Was this topic helpful?
