customer_primary
- Sylvain Paillard
Description
This table contains information about the performance of each customer (de-duplicated from accounts and guest purchases based on a hash key which is typically the e-mail address) for each month.
Some columns (like the lifetime_* columns) are not specific to the month and are duplicated along months, it is important not to sum them while grouping by customer only, which would cause over-counting.
The month column is a date field always set with the first day of the month.
Granularity
one row per:
month
customer
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) |
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 products in the orders for this customer that month |
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 orders for this customer that month |
total_margin_crncy_amt | FLOAT | NULLABLE | the total margin value of the orders for this customer that month |
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). |
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 |