Overview

In this Data Type, you can define all the data about the orders made in the e-shop or offline.

Example

In this example, we provide a simple attribute definition.

see example in JSON

Here is the example for the first case above (make sure to format it in JSONL before loading to BigQuery: Newline delimited JSON : https://en.wikipedia.org/wiki/JSON_streaming).

{
  "internal_id": "286172",
  "external_id": "100285945",
  "creation": "2020-09-21 02:37:36",
  "confirmation": "2020-09-21 02:37:36",
  "sent": "2020-09-23 10:22:49",
  "persona_id": "82474",
  "status_code": "complete",
  "internal_state": "processing",
  "email": "xxx@hotmail.com",
  "tax_amnt": 4.2468,
  "currency_cd": "CHF",
  "total_crncy_amt": 152.2,
  "total_crncy_amt_net": 141.3185,
  "shipping_costs": 0,
  "shipping_method": "DIGITAL",
  "shipping_description": "Deponieren",
  "payment_method": "MASTERCARD",
  "contacts": [
    {
      "type": "billing",
      "prefix": "Herr",
      "firstname": "Marco",
      "lastname": "XXX",
      "street": "Amselweg 3",
      "zipcode": "4538",
      "city": "Oberbipp",
      "phone": "079947xxxx",
      "email": "xxx@hotmail.com",
      "countryID": "CH"
    },
    {
      "type": "shipping",
      "prefix": "Herr",
      "firstname": "Marco",
      "lastname": "XXX",
      "street": "Amselweg 3",
      "zipcode": "4538",
      "city": "Oberbipp",
      "phone": "079947xxxx",
      "email": "xxx@hotmail.com",
      "countryID": "CH"
    }
  ],
  "products": [
    {
      "sku_id": "117372",
      "unit_list_price": 29.7,
      "unit_sales_price": 27.5766,
      "quantity": 2
    }
  ],
  "vouchers": [
    {
      "internal_id": "1234",
      "voucher_percentage_value": 0.05,
      "voucher_absolute_value": 0
    }
  ],
  "creation_tm": "2020-10-20 00:00:00",
  "client_id": 1,
  "src_sys_id": 1
}

Properties

Field name	Type	Mode	Description
internal_id	STRING	REQUIRED	the internal identifier of the order
external_id	STRING	NULLABLE	the external identifier of the order (can be the same as the internal identifier)
parent_order_id	STRING	NULLABLE	the parent order id (when applicable)
persona_type	STRING	NULLABLE	the persona type who created this order
persona_id	STRING	REQUIRED	the persona who created this order
order_sys_cd	STRING	NULLABLE	the order system code (e.g.: 'ECM', 'ERP', ...)
store	STRING	NULLABLE	the store in which the order was done
seller_persona_type	STRING	NULLABLE	the persona type who sold this order
seller_persona_id	STRING	NULLABLE	the persona who sold this order
currency_cd	STRING	NULLABLE	the order currency code (e.g.: 'chf', 'eur', ...)
total_crncy_amt	NUMERIC	REQUIRED	the total value of the order (what is paid by the customer)
total_crncy_amt_net	NUMERIC	NULLABLE	the net value of the order
total_gross_margin_crncy_amt	NUMERIC	NULLABLE	the gross margin of the order
total_net_margin_crncy_amt	NUMERIC	NULLABLE	the net margin of the order
shipping_costs_net	NUMERIC	NULLABLE	the net shipping costs of the order
shipping_costs	NUMERIC	NULLABLE	the shipping costs of the order
currency_factor	NUMERIC	NULLABLE	the currency factor of the order
tax_free	BOOLEAN	NULLABLE	was the order tax free?
tax_rate	NUMERIC	NULLABLE	the tax rate of the order
tax_amnt	NUMERIC	NULLABLE	the tax amount of the order
payment_method	STRING	NULLABLE	the payment method should be one of the following value (other values will be considered as OTHER) FREE/GRATIS/GIFT INVOICE PREPAYMENT MASTERCARD VISA AMEX TWINT POINTS_PAY MARKETPLACES GALAXUS_MARKETPLACE COLLECTIVE_INVOICE OTHER
shipping_method	STRING	NULLABLE	the shipping method should be one of the following value (other values will be considered as OTHER) MAIL DIGITAL NONE DPD_PREDICT DPD_STANDARD_DEPOSIT DPD_STANDARD_SIGNING DPD_EXPRESS_SIGNING PLANZER_DEPOSIT PLANZER_SIGNING NOTIME PERSONAL_PICKUP OWN_DRIVER OTHER
shipping_description	STRING	NULLABLE	the shipping description
device	STRING	NULLABLE	the device used
referer	STRING	NULLABLE	the referer used
partner	STRING	NULLABLE	the partner used
language	STRING	NULLABLE	the language of the order
tracking_code	STRING	NULLABLE	the tracking code of the order
is_gift	BOOLEAN	NULLABLE	was the order a gift?
wrapping	BOOLEAN	NULLABLE	was the order with wrapping?
email	STRING	NULLABLE	the email of the order
comments	COMMENT	REPEATED	the comments
internal_comments	COMMENT	REPEATED	the internal comments
customer_comments	COMMENT	REPEATED	the customer comments
contacts	CONTACT	REPEATED	billing/shipping information
creation	DATETIME	REQUIRED	the date time the order was created
last_update	DATETIME	NULLABLE
confirmation	DATETIME	NULLABLE
cleared	DATETIME	NULLABLE
sent	DATETIME	NULLABLE
received	DATETIME	NULLABLE
returned	DATETIME	NULLABLE
repaired	DATETIME	NULLABLE
status	BOOLEAN	NULLABLE	should the order be considered as successful or not
status_code	STRING	NULLABLE	should be one of the following value (other values will be considered as OTHER): MANUALLY_CREATED IMPORTED INCONSISTENT CONSISTENT CLEARED CONFIRMED UNCONFIRMED PROCESSING SHIPPED RECEIVED CLOSED ABORTED OTHER
string_attributes	MAP	REPEATED	additional string (not localized) attributes of the product line (MAP type: STRING)
localized_string_attributes	MAP	REPEATED	additional localized string attributes (MAP type: LOCALIZED in STRING)
numeric_attributes	MAP	REPEATED	additional numeric (not localized) attributes (MAP type: NUMERIC)
localized_numeric_attributes	MAP	REPEATED	additional localized numeric attributes (MAP type: LOCALIZED in NUMERIC)
datetime_attributes	MAP	REPEATED	additional datetime (not localized) attributes (MAP type: DATETIME)
localized_datetime_attributes	MAP	REPEATED	additional localized datetime attributes (MAP type: LOCALIZED inDATETIME)
products	RECORD	REPEATED	the products of the order
products. sku_id	STRING	REQUIRED	the internal identifier of the sku
products. connection_property	STRING	NULLABLE	the name of the connecting property for the sku_id value in case not the sku.internal_id
products. type	STRING	NULLABLE	the type of product : sku, bundle, ...
products. unit_list_price	NUMERIC	NULLABLE	unit price before any discount
products. unit_sales_price	NUMERIC	NULLABLE	unit price displayed to the customer after discounts
products. unit_gross_margin	NUMERIC	NULLABLE	unit the sales gross margin as an absolute value
products. quantity	NUMERIC	NULLABLE	quantity of units of products
products. total_list_price	NUMERIC	NULLABLE	unit price before any discount
products. total_sales_price	NUMERIC	NULLABLE	unit price displayed to the customer after discounts
products. total_gross_margin	NUMERIC	NULLABLE	unit the sales gross margin as an absolute value
products. status	BOOLEAN	NULLABLE	the order product status
products. status_code	STRING	NULLABLE	the order product status code
products.string_attributes	MAP	REPEATED	additional string (not localized) attributes of the product line (MAP type: STRING)
products.localized_string_attributes	MAP	REPEATED	additional localized string attributes (MAP type: LOCALIZED in STRING)
products. numeric_attributes	MAP	REPEATED	additional numeric (not localized) attributes (MAP type: NUMERIC)
products.localized_numeric_attributes	MAP	REPEATED	additional localized numeric attributes (MAP type: LOCALIZED in NUMERIC)
products.datetime_attributes	MAP	REPEATED	additional datetime (not localized) attributes (MAP type: DATETIME)
products.localized_datetime_attributes	MAP	REPEATED	additional localized datetime attributes (MAP type: LOCALIZED inDATETIME)
vouchers	RECORD	REPEATED
vouchers. internal_id	STRING	NULLABLE	the internal identifier of the voucher
vouchers. external_id	STRING	NULLABLE	the external identifier of the voucher (can be the same as the internal identifier)
vouchers. voucher_products	RECORD	REPEATED	products related to the voucher
vouchers.voucher_products. type	STRING	NULLABLE
vouchers.voucher_products. name	STRING	NULLABLE
vouchers.voucher_products. product_line	STRING	NULLABLE
vouchers.voucher_products. product_group	STRING	NULLABLE
vouchers.voucher_products. voucher	STRING	NULLABLE
vouchers.voucher_products. value	NUMERIC	NULLABLE
vouchers. type	STRING	NULLABLE	the type value)
vouchers. ean	STRING	NULLABLE	the ean value)
vouchers. label	STRING	NULLABLE	label of the voucher
vouchers. voucher_percentage_value	NUMERIC	NULLABLE	the voucher percentage value (value should be normally > 0)
vouchers. voucher_absolute_value	NUMERIC	NULLABLE	the voucher absolute value (value should be normally > 0)
vouchers. status	BOOLEAN	NULLABLE	the voucher status
vouchers.string_attributes	MAP	REPEATED	additional string (not localized) attributes of the product line (MAP type: STRING)
vouchers.localized_string_attributes	MAP	REPEATED	additional localized string attributes (MAP type: LOCALIZED in STRING)
vouchers. numeric_attributes	MAP	REPEATED	additional numeric (not localized) attributes (MAP type: NUMERIC)
vouchers.localized_numeric_attributes	MAP	REPEATED	additional localized numeric attributes (MAP type: LOCALIZED in NUMERIC)
vouchers.datetime_attributes	MAP	REPEATED	additional datetime (not localized) attributes (MAP type: DATETIME)
vouchers.localized_datetime_attributes	MAP	REPEATED	additional localized datetime attributes (MAP type: LOCALIZED inDATETIME)
creation_tm	DATETIME	REQUIRED	technical field
client_id	INTEGER	REQUIRED	technical field
src_sys_id	INTEGER	REQUIRED	technical field

Resources

BigQuery JSON Schema

https://github.com/boxalino/data-integration-doc-schema/blob/master/doc/doc_order.json

BigQuery DDL

https://github.com/boxalino/data-integration-doc-schema/blob/master/ddl/doc_order.sql