customer_product

Owned by Sylvain Paillard
May 06, 2020
5 min read

Description

This table contains information about the performance of each product 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 products 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 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

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)

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)

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)

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)

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 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