customer_product_property
Description
This table contains information about the performance of each product property and value for each customer (de-duplicated from accounts and guest purchases based on a hash key which is typically the e-mail address) for each month.
Only product properties and values bought during that month by that customer are considered.
Some columns (like the lifetime_* columns) are neither specific to the month nor to the product and are duplicated along months, it is important not to sum them while grouping by customer only, which would cause over-counting.
Some columns provide information about the global values of the orders the product property and value was included in (for example total_crncy_amt doesn’t not provide the product turnover, but the global turnover of all the orders the products was included in).
The month column is a date field always set with the first day of the month.
Granularity
one row per:
month
customer
product property name
product property value
Columns
Field name | Type | Mode | Description |
customer_id | STRING | NULLABLE | PRIMARY DIMENSION - MUST BE SET TO AVOID OVERCOUNT - the customer id (typically the e-mail address, can be different if another criteria was used for the de-duplication) |
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) |
day | DATE | NULLABLE | PRIMARY DIMENSION - first day of the month |
gender_cd* | STRING | NULLABLE | the gender code: male or female |
country_cd* | STRING | NULLABLE | the country code : e.g.: CH |
zip_cd* | STRING | NULLABLE | the zip code |
region_cd* | STRING | NULLABLE | the region code: ZH, VD, ... |
latitude* | FLOAT | NULLABLE | latitude position |
longitude* | FLOAT | NULLABLE | longitude position |
birthdate* | DATE | NULLABLE | the birth date |
age* | INTEGER | NULLABLE | the age in years |
decade* | INTEGER | NULLABLE | the age in decades |
lifetime_orders* | INTEGER | NULLABLE | the total number of orders made by this customer over all time |
lifetime_order_products* | INTEGER | NULLABLE | the total number of ordered products made by this customer over all time |
lifetime_order_qty* | INTEGER | NULLABLE | the total number of ordered product quantity made by this customer over all time |
lifetime_list_crncy_amt* | FLOAT | NULLABLE | the total of all the of the products list price (product price before discounts) bought by this customer over all time |
lifetime_discount_crncy_amt* | FLOAT | NULLABLE | the total of all the of the products final price (product price after discounts) bought by this customer over all time |
lifetime_total_crncy_amt* | FLOAT | NULLABLE | the total value of this customer over all time |
lifetime_total_crncy_amt_rounded* | FLOAT | NULLABLE | the total value of this customer over all time rounded by 10.- steps |
lifetime_total_margin_crncy_amt* | FLOAT | NULLABLE | the total margin of this customer over all time |
lifetime_total_margin_crncy_amt_rounded* | FLOAT | NULLABLE | the total margin of this customer over all time rounded by 10.- steps |
first_order* | TIMESTAMP | NULLABLE | the timestamp of the first order (global value, independent of the current month) |
last_order* | TIMESTAMP | NULLABLE | the timestamp of the last order (global value, independent of the current month) |
nth_day* | INTEGER | NULLABLE | the number of days since the first order (global value, independent of the current month) |
nth_week* | INTEGER | NULLABLE | the number of weeks since the first order (global value, independent of the current month) |
nth_month* | INTEGER | NULLABLE | the number of months since the first order (global value, independent of the current month) |
nth_quarter* | INTEGER | NULLABLE | the number of quarters since the first order (global value, independent of the current month) |
nth_year* | INTEGER | NULLABLE | the number of years since the first order (global value, independent of the current month) |
last_purchase_distance_day* | INTEGER | NULLABLE | the number of days since the last order at that time (related to the current month) |
last_purchase_distance_week* | INTEGER | NULLABLE | the number of weeks since the the last order at that time (related to the current month) |
last_purchase_distance_month* | INTEGER | NULLABLE | the number of months since the the last order at that time (related to the current month) |
last_purchase_distance_quarter* | INTEGER | NULLABLE | the number of quarters since the the last order at that time (related to the current month) |
last_purchase_distance_year* | INTEGER | NULLABLE | the number of years since the the last order at that time (related to the current month) |
user_personas* | INTEGER | NULLABLE | the number of user personas (2 in case the same customer used 2 different accounts) |
visitor_personas* | INTEGER | NULLABLE | the number of visitor personas (correspond to the number of identified visitors (identification based on a login / tagged url in any of the visitor’s sessions), (e.g.: two sessions with flushing the cookies in between, or from two different devices will count as 2) |
orders* | INTEGER | NULLABLE | the number of orders for this customer that month |
order_products* | INTEGER | NULLABLE | the number of product in the orders for this customer that month |
order_qty | INTEGER | NULLABLE | the total quantity of THE CURRENT PRODUCT in the orders for this customer that month IMPORTANT - SPECIFIC TO THE CURRENT PRODUCT |
list_crncy_amt* | FLOAT | NULLABLE | the total value of the products list price (product price before discounts) for this customer that month |
discount_crncy_amt* | FLOAT | NULLABLE | the total value of the products final price (product price after discounts) for this customer that month |
total_crncy_amt | FLOAT | NULLABLE | the total value of THE CURRENT PRODUCT for this customer that month = order_qty * product_discount_crncy_amt IMPORTANT - SPECIFIC TO THE CURRENT PRODUCT |
total_margin_crncy_amt | FLOAT | NULLABLE | the total margin value of THE CURRENT PRODUCT for this customer that month = order_qty * product_margin_crncy_amt IMPORTANT - SPECIFIC TO THE CURRENT PRODUCT |
sessions* | INTEGER | NULLABLE | the number of identified sessions (identification based on a login / tagged url in any of the visitor’s sessions) |
customer_owners | STRING | REPEATED | the customers owners (not active by default, you can define filter rules to connect products to customer 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). The customer owners are a derived form of the product owners. A product might belong to a product owner, but a specific purchase of this product might not be connected to the related customer owner, as, an additional rule applies (e.g.: the customer must have bought at least twice in the last 6 months a product of this product owners to be considered connected to the related customer owner). |
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 |
copy from the customer_primary or order_product table, duplicated values for all products ordered by the same customer