order_product_property

Owned by Sylvain Paillard
May 06, 2020
3 min read

Description

This table contains all the columns from order_primary (duplicated values along the different product properties and values of the same order) and contains additional columns about the product properties and values ordered (product_status_cd, product_qty, product_list_crncy_amt, product_discount_crncy_amt, product_margin_crncy_amt).

In addition, if a product property value is present in several products of the same order they will appear several times (e.g.: buying 3 products of the same brand will make the brand appear 3 times, once per product).

As the columns of the order_primary table are duplicated along the different product properties and values 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

  • product property name

  • product property value

Columns

Field name

Type

Mode

Description

property_name

STRING

NULLABLE

PRIMARY DIMENSION - MUST BE SET TO AVOID OVERCOUNT - the product property name

property_label

STRING

NULLABLE

the product property label (property_label should have easy to read values while property_name might have technical values)

property_value

STRING

NULLABLE

PRIMARY DIMENSION - MUST BE SET TO AVOID OVERCOUNT - the product property value

property_value_label

STRING

NULLABLE

the product property value label (property_value should have easy to read values while property_value_label might have technical values)

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)

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)

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