Description
This table contains all the columns from order_primary (duplicated values along the different products of the same order) and contains additional columns about the product ordered (product_status_cd, product_qty, product_list_crncy_amt, product_discount_crncy_amt, product_margin_crncy_amt).
As the columns of the order_primary table are duplicated along the different products of the same order it is important not to sum them while grouping by order only, which would cause over-counting.
Granularity
one row per:
order
product
Columns
Field name | Type | Mode | Description |
product_id | STRING | NULLABLE | PRIMARY DIMENSION - MUST BE SET TO AVOID OVERCOUNT - the product id |
title_en | STRING | NULLABLE | title of the product in English |
title_de | STRING | NULLABLE | title of the product in German |
title_fr | STRING | NULLABLE | title of the product in French |
title_it | STRING | NULLABLE | title of the product in Italian |
category_1** | STRING | NULLABLE | 1st-level category id of the product |
category_2** | STRING | NULLABLE | 2nd-level category id of the product |
category_3** | STRING | NULLABLE | 3rd-level category id of the product |
category_4** | STRING | NULLABLE | 4th-level category id of the product |
category_5** | STRING | NULLABLE | 5th-level category id of the product |
exact_margin | FLOAT | NULLABLE | the product margin |
rounded_margin | FLOAT | NULLABLE | the rounded product margin (by default: 5% step) |
exact_price | FLOAT | NULLABLE | the product price (after discount) |
rounded_price | FLOAT | NULLABLE | the rounded product price (by default: 10.- step) |
product_owners | STRING | REPEATED | the product owners (not active by default, you can define filter rules to connect products to product owners (e.g.: suppliers) which you want to be able to select independently or in combination to the selection of any product or product property) |
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) |
line_num | FLOAT | NULLABLE | the line number of the product in the order (only useful if the order of the products in the order list is needed) |
product_status_cd | STRING | NULLABLE | the status of the order for that product (as provided in the data exported to BigQuery) |
product_qty | INTEGER | NULLABLE | the quantity of purchases |
product_list_crncy_amt | FLOAT | NULLABLE | the unit product list price (product price before discounts) (should be multiplied with product_qty to get the full product value) |
product_discount_crncy_amt | FLOAT | NULLABLE | the unit product final price (product price after discounts) (should be multiplied with product_qty to get the full product value) |
product_margin_crncy_amt | FLOAT | NULLABLE | the unit product margin (should be multiplied with product_qty to get the full product value) |
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) |
copy from the order_primary table, duplicated values for all the products of the same order
* arbitrary selection in case the product belongs in several categories, used the *_product_property table for multi-values on the categories
0 Comments