order_primary
- Sylvain Paillard
Description
This table contains the key columns about orders and statistical information about order products (avg_list_crncy_amt, avg_discount_crncy_amt). This table can be used freely without risks of over-counting.
Granularity
one row per:
order
Columns
Field name | Type | Mode | Description |
|---|---|---|---|
order_id | STRING | NULLABLE | the order identifier (external id provided by the exported data) |
order_tm | TIMESTAMP | NULLABLE | the timestamp of the order |
order_sys_cd | STRING | NULLABLE | the order system: ERP stands for ERP system, ECM for e-commerce system, provides a general distinction between data sources providing orders |
persona_id | STRING | NULLABLE | the persona id (in most cases this is the account id of the customer from which he did the order) |
persona_type_cd | STRING | NULLABLE | V for a visitor with a tracking ID only, U for a logged in user or an export from the ERP (typically will always be U) |
customer_id | STRING | NULLABLE | the customer id (typically the e-mail address, can be different if another criteria was used for the de-duplication) |
customer_properties | RECORD | REPEATED | the most important customer properties |
customer_properties. name | STRING | NULLABLE | Name of the property |
customer_properties. value | STRING | NULLABLE | Value of the property |
order_qty | INTEGER | NULLABLE | the total quantity of products in the orders (in case 3 products are bought each with a quantity of 2, the value will be 6) |
order_products | INTEGER | NULLABLE | the total amount of different products in the orders (in case 3 products are bought each with a quantity of 2, the value will be 3) |
currency_cd | STRING | NULLABLE | the currency code (e.g.: ‘CHF’) |
total_crncy_amt | FLOAT | NULLABLE | the total value of the orders (including delivery costs and reductions) |
total_margin_crncy_amt | FLOAT | NULLABLE | the total margin value of the orders (simple calculation summing the margin of each product and not applying any additional costs or reductions) |
list_crncy_amt | FLOAT | NULLABLE | the total value of the products list price (product price before discounts) |
discount_crncy_amt | FLOAT | NULLABLE | the total value of the products final price (product price after discounts) |
avg_list_crncy_amt | FLOAT | NULLABLE | the average value of the products list price (product price before discounts) |
avg_discount_crncy_amt | FLOAT | NULLABLE | the average value of the products final price (product price after discounts) |
account | STRING | NULLABLE | the account name (typically same for all orders with the value ‘main’) |
parameters | RECORD | REPEATED | general structure of parameters and values (in this table, it will mainly contain the key order properties) |
parameters. name | STRING | NULLABLE | Name of the property |
parameters. value | STRING | NULLABLE | Value of the property |
month | DATE | NULLABLE | can be ignored, required for BigQuery partitioning (the month of the order) |