Data input specifications

Before you upload the following data files to Zilliant, use the data input specifications in this topic to make sure they are formatted correctly.

Required files
Optional files
Files for active pricing

Prerequisites

Before uploading data for your organization’s active pricing, you must be familiar with frameworks and how they work. For how to import cost or price records, read Import cost or price records.

Terms

The following terms and abbreviations may appear in data input specifications:

Alphabetical—Alphabetical characters, spaces, and special characters (such as ñ or ü).
Datetime ISO—Dates in format YYYY-MM-DDTHH:NNTz (timezone).
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.
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)—Number with a specified precision (p), which is the total number of digits that can be stored, and scale (s), which is the number of digits that can be to the right of the decimal point. For example, a field bounded by Numeric(10,3) can store a number like 1234567.890 with a total of ten digits, three after the decimal.
Primary key—This field is a column that individually, or in combination with other columns, uniquely identifies each row in a database table.

Required files

products file

Contains product master data.

Column Name	Column Key	Data type	Required	Attributes	Notes / Validations
Product Id	`customer_product_id`	String	Yes	Customer-supplied	Primary key Maximum character length: 250 Column must be mapped. Each column cell must contain a valid value.
Product Name	`product_name`	String	No	Customer-supplied	Maximum character length: 200
Category Name	`category_name`	String	No	Customer-supplied	Foreign key to `product_category.category_name` Maximum character length: 200
Sku	`sku`	String	No	Customer-supplied	Maximum character length: 200
Description	`description`	String	No	Customer-supplied	Maximum character length: 1000
Unit Cost	`unit_cost`	Numeric(25,8)	No	Customer-supplied
Currency	`currency`	Alphabetical	No	Customer-supplied	Maximum character length: 3 Currency in ISO format, such as “USD”
Unit of Measure	`unit_of_measure`	String	No	Customer-supplied	Maximum character length: 100

accounts file

Contains customer master data.

Column name	Column key	Data type	Required	Attributes	Notes / Validations
Account Id	`account_id`	String	Yes	Customer-supplied	Primary key Maximum character length: 250 Column must be mapped. Each column cell must contain a valid value.
Account Owner Id	`account_owner_id`	String	No	Customer-supplied	Maximum character length: 250
Account Name	`account_name`	String	Yes	Customer-supplied	Maximum character length: 250 Column must be mapped. Each column cell must contain a valid value.
Account Type	`account_type`	String	No	Customer-supplied	Maximum character length: 100
Industry Segment	`industry_segment`	String	No	Customer-supplied	Maximum character length: 100
Shipping Address	`shipping_address`	String	No	Customer-supplied	Maximum character length: 250
Shipping Address 2	`shipping_address_2`	String	No	Customer-supplied	Maximum character length: 250
Shipping City	`shipping_city`	String	No	Customer-supplied	Maximum character length: 75
Shipping State	`shipping_state`	String	No	Customer-supplied	Maximum character length: 100
Shipping Postal Code	`shipping_postal_code`	String	No	Customer-supplied	Maximum character length: 10
Shipping Extended Postal Code	`shipping_extended_postal_code`	String	No	Customer-supplied	Maximum character length: 10
Shipping Country	`shipping_country`	String	No	Customer-supplied	Country code in ISO 3166 alpha-3 format Maximum character length: 3
Billing Address	`billing_address`	String	No	Customer-supplied	Maximum character length: 250
Billing Address 2	`billing_address_2`	String	No	Customer-supplied	Maximum character length: 250
Billing City	`billing_city`	String	No	Customer-supplied	Maximum character length: 75
Billing State	`billing_state`	String	No	Customer-supplied	Maximum character length: 100
Billing Postal Code	`billing_postal_code`	String	No	Customer-supplied	Maximum character length: 10
Billing Extended Postal Code	`billing_extended_postal_code`	String	No	Customer-supplied	In the United States, the +4 in a ZIP code Maximum character length: 10
Billing Country	`billing_country`	String	No	Customer-supplied	Country code in ISO 3166 alpha-3 format Maximum character length: 3
Account Region	`account_region`	String	No	Customer-supplied	Maximum character length: 100

transactions file

Contains customer transaction records.

Column name	Column key	Data type	Required	Attributes	Notes / Validations
Transaction Id	`transaction_id`	String	Yes	Customer-supplied	Primary key Maximum character length: 100 Column must be mapped. All category name values must be unique. Each column cell must contain a valid value.
Account Id	`account_id`	String	Yes	Customer-supplied	Foreign key Maximum character length: 250 Column must be mapped. Each column cell must contain a valid value.
Transaction Type	`transaction_type`	String	No	Customer-supplied	Maximum character length: 100
Total Revenue	`total_revenue`	Numeric(10,2)	Yes	Customer-supplied	Transaction total down to the hundredth place Maximum character length: 10 Column must be mapped. Each column cell must contain a valid value.
Currency	`currency`	Alphabetical	No	Customer-supplied	Maximum character length: 3 Currency in ISO format, such as “USD”
Quote Datetime	`quote_datetime`	Datetime ISO	No	Customer-supplied	Time transaction was considered and executed as determined by the customer.
Order Datetime	`order_datetime`	Datetime ISO	Yes	Customer-supplied	Customer-supplied when a transaction’s execution and effective dates differ. Column must be mapped. Each column cell must contain a valid value.
Ship Datetime	`ship_datetime`	Datetime ISO	No	Customer-supplied
Channel	`channel`	String	No	Customer-supplied	Maximum character length: 250

transaction_line_items file

Contains transaction line item records.

Column name	Column key	Data type	Required	Attributes	Notes / Validations
Transaction Line Item Id	`transaction_line_item_id`	String	Yes	Customer-supplied	Primary key Maximum character length: 250 Column must be mapped. Each column cell must contain a valid value.
Transaction Id	`transaction_id`	String	Yes	Customer-supplied	Primary key Foreign key When combined with `transaction_line_item_id`, will give you a unique record. Maximum character length: 100 Column must be mapped. Each column cell must contain a valid value.
Product Id	`product_id`	String	No	Customer-supplied	Customer-supplied product ID. Maximum character length: 250
Order Quantity	`order_quantity`	Numeric(25,8)	Yes	Customer-supplied	Amount of each product ordered, subject to unit of measurement and pack size. Column must be mapped. Each column cell must contain a valid value.
Unit Cost	`unit_cost`	Numeric(25,8)	No	Customer-supplied	Customer cost
Currency Price	`currency_price`	Numeric(25,8)	No	Customer-supplied
Currency	`currency`	Alphabetic	No	Customer-supplied	Column must be mapped. Each column cell must contain a valid value.
Currency Cost	`currency_cost`	Numeric(25,8)	No	Customer-supplied
Unit Price	`unit_price`	Numeric(25,8)	No	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. Column must be mapped. Each column cell must contain a valid value.
Price Type	`price_type`	String	No	Customer-supplied	Maximum character length: 50
List Price	`list_price`	Numeric(25,8)	No	Customer-supplied	List price. Typically different from the unit price.
Line Total	`line_total`	Numeric(25,8)	No	Customer-supplied	Amount paid for each line item. If quantity is equal to one, this will equal the unit_price. If quantity is more than one, this will equal the quantity times the final price. This value may be rounded down to the nearest cent.
Start Price	`start_price`	Numeric(25,8)	No	Customer-supplied
Target Price	`target_price`	Numeric(25,8)	No	Customer-supplied
Floor Price	`floor_price`	Numeric(25,8)	No	Customer-supplied
Unit of Measure	`unit_of_measure`	String	No	Customer-supplied	Maximum character length: 100
Package Size	`package_size`	Numeric	No	Customer-supplied

currency_conversion_rates file

Use if your organization data includes more than one currency.

Column Name	Column Key	Data type	Required	Attributes	Notes / Validations
Quote Currency	`quote_currency`	String	Yes	Customer-supplied	Primary key Maximum character length: 3 Currency in ISO format, such as “USD” Column must be mapped. Each column cell must contain a valid value.
Rate	`rate`	Numeric(12,6)	No	Customer-supplied
Effective From	`effective_from`	Datetime ISO	No	Customer-supplied	The effective start date for the conversion
Effective To	`effective_to`	Datetime ISO	No	Customer-supplied	The effective end date for the conversion
Source System	`source_system`	String	No	Customer-supplied	Maximum character length: 100