order_product

Owned by Sylvain Paillard
Last updated: Apr 20, 2023
3 min read

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

(deprecated, use the product_properties of the order_product_analytics table instead) 1st-level category id of the product

category_2**

STRING

NULLABLE

(deprecated, use the product_properties of the order_product_analytics table instead) 2nd-level category id of the product

category_3**

STRING

NULLABLE

(deprecated, use the product_properties of the order_product_analytics table instead) 3rd-level category id of the product

category_4**

STRING

NULLABLE

(deprecated, use the product_properties of the order_product_analytics table instead) 4th-level category id of the product

category_5**

STRING

NULLABLE

(deprecated, use the product_properties of the order_product_analytics table instead) 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