Analytics, Insights, Reports (CAIR)

Order

https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/16351324 _, https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/16318584 , order_primary_analytics, order_product_analytics

(first 2 tables have the same amount rows as the 2 others, but the analytics tables have more columns and more advanced data)

deprecated:
https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/16351341

Orders

none, product, product properties

Customer

_{, , , , ,}

Customers

none, product, product properties

Product / Content

_,

Products

none, product properties

Bought together

_{, , ,}

Products (source & target)

none, product properties

Session

Session

none

Widget

_{, ,}

Widget

none, product, product properties

Search term

_{, ,}

Widget & Search term (search made by user on the web-site)

none, product, product properties

Facet

_{, ,}

Widget & Facet (filter / refinements selected by users on the web-site)

none, product, product properties

Context item

_{, , , , ,}

Widget & Context Item (context page, like the Product Detail Page (PDP) or a blog / content detail page)

none, product, product properties

Position

Widget & Position of the products in the list

none

Order

order_primary_analytics

_{one row per:}

• _order

_{This table contains the key columns about orders and statistical information about order products (avg_list_crncy_amt, avg_discount_crncy_amt). This table can be used freely without risks of over-counting.
in the analytics table, additional fields are provided, in particular the new “attribution” fields related to the Google Analytics 4 and Google Ads Attribution models.}

order_product_analytics

_{one row per:}

• _order

• _product

_{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.
in the analytics table, the product properties are also provided which makes the older table (order_product_property) deprecated. also additional fields are provided, in particular the new “attribution” fields related to the Google Analytics 4 and Google Ads Attribution models.}

(deprecated)

_{one row per:}

• _order

• _product

• _{product property name}

• _{product property value}

_{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.}

Customer

_{one row per:}

• _month

• _customer

_{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.}

_{one row per:}

• _month

• _customer

• _product

_{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.}

_{one row per:}

• _month

• _customer

• _{product property name}

• _{product property value}

_{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.}

_{one row per:}

• _month

• _{customer property name}

• _{customer property value}

_{This table contains information about the performance of each customer property and value for each month.}

_{The month column is a date field always set with the first day of the month.}

_{one row per:}

• _month

• _{customer property name}

• _{customer property value}

• _{product property name}

• _{product property value}

_{This table contains information about the performance of each product for each customer property and value for each month.}

_{Only products bought during that month by that customer property and value are considered.}

_{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.}

_{one row per:}

• _month

• _customer

• _{product property name}

• _{product property value}

_{This table contains information about the performance of each product property and value for each customer property and value for each month.}

_{Only product properties and values bought during that month by that customer property and value are considered.}

_{Some columns provide information about the global values of the orders the product property and valuewas 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.}

Product / Content

_{one row per:}

• _month

• _product

_{This table contains information about the performance of each product for each month.}

_{All products (bought or not during the month) are considered.}

_{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.}

_{one row per:}

• _month

• _{product property name}

• _{product property value}

_{This table contains information about the performance of each product property and value for each month.}

_{All product properties and values (bought or not during the month) are considered.}

_{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.}

Bought together

_{one row per:}

• _month

• _{source product}

• _{target product}

_{This table contains information about the orders containing a combination of two products (source and target products) for each month.}

_{Only products which have been bought together during that month are considered.}

_{There is no logic in the source and target products, therefore, the combination are duplicated (each source → target is also appearing with the target being the source and the source being the target)}

_{Additional information (like viewed together or put together in the same baskets) are also provided, but the table does not include any product viewed together or added to the basket together which have not also been bought at least once during that month.}

_{The month column is a date field always set with the first day of the month.}

_{one row per:}

• _month

• _{source product}

• _{target product}

_{This table contains information about the customers having bought a combination of two products (source and target products) for each month (the order date of the source product is considered as the month).}

_{There is no logic in the source and target products, therefore, the combination are duplicated (each source → target is also appearing with the target being the source and the source being the target)}

_{The month column is a date field always set with the first day of the month.}

_{one row per:}

• _month

• _{source product property name}

• _{source product property value}

• _{target product property name}

• _{target product property value}

_{This table contains information about the orders containing a combination of two product property and values (source and target products) for each month.}

_{Only product property and values which have been bought together during that month are considered.}

_{There is no logic in the source and target product property and values, therefore, the combination are duplicated (each source → target is also appearing with the target being the source and the source being the target)}

_{Additional information (like viewed together or put together in the same baskets) are also provided, but the table does not include any product property and values viewed together or added to the basket together which have not also been bought at least once during that month.}

_{The month column is a date field always set with the first day of the month.}

_{one row per:}

• _month

• _{source product property value}

• _{target product property name}

• _{target product property value}

_{This table contains information about the customers having bought a combination of two product property and values (source and target products) for each month (the order date of the source product property and value is considered as the month).}

_{There is no logic in the source and target products, therefore, the combination are duplicated (each source → target is also appearing with the target being the source and the source being the target)}

_{The month column is a date field always set with the first day of the month.}

Session

_{one row per:}

• _session

_{This table contains information about each session.}

_{All robots and crawler which are present in the core tables are filtered out.}

Widget

_{one row per:}

• _session

• _widget

_{This table contains information about each session and for each widget.}

_{As the columns of the session_primary table are duplicated along the different products of the same order it is important not to sum them while grouping by session only, which would cause over-counting.}

_{one row per:}

• _week

• _widget

• _product

• _{test variant}

• _channel

• _country

• _{device category}

_{This table contains information about the performance of each widget for each product for every week (and for each test variant, channel, country and device category).}

_{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). Instead, use product specific columns giving you the specific product performance like display_requests, weighted_display_requests, total_product_order_num, viewed_cnt, basketed_cnt, bought_orders, bought_order_products, bought_order_product_qty and bought_order_product_discount_crncy_amt.}

_{The week column is a date field always set with the first day of the week.}

_{one row per:}

• _week

• _widget

• _{product property name}

• _{product property value}

• _{test variant}

• _channel

• _country

• _{device category}

_{This table contains information about the performance of each widget for each product property and value for every week (and for each test variant, channel, country and device category).}

_{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).Instead, use product specific columns giving you the specific product performance like display_requests, weighted_display_requests, total_product_order_num, viewed_cnt, basketed_cnt, bought_orders, bought_order_products, bought_order_product_qty and bought_order_product_discount_crncy_amt.}

_{The week column is a date field always set with the first day of the week.}

Search term

_{one row per:}

• _session

• _widget

• _{search term}

_{This table contains information about each session and for each search term (the query searched by the user in the web-site) and for each widget (you have several different search widget, starting by the difference between ‘autocomplete’ (each request made to the auto-completion while the user is typing his search and ‘search’ (the search page))}

_{As the columns of the session_primary table are duplicated along the different products of the same order it is important not to sum them while grouping by session only, which would cause over-counting.}

_{one row per:}

• _month

• _widget

• _{search term}

• _product

• _{test variant}

_{This table contains information about each month and for each search term (the query searched by the user in the web-site) and for each widget and test variant (you have several different search widget, starting by the difference between ‘autocomplete’ (each request made to the auto-completion while the user is typing his search and ‘search’ (the search page)) and for each product.}

_{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). Instead, use product specific columns giving you the specific product performance like display_requests, weighted_display_requests, total_product_order_num, viewed_cnt, basketed_cnt, bought_orders, bought_order_products, bought_order_product_qty and bought_order_product_discount_crncy_amt.}

_{The month column is a date field always set with the first day of the month.}

_{one row per:}

• _month

• _widget

• _{search term}

• _{product property name}

• _{product property value}

• _{test variant}

_{This table contains information about each month and for each search term (the query searched by the user in the web-site) and for each widget and test variant (you have several different search widget, starting by the difference between ‘autocomplete’ (each request made to the auto-completion while the user is typing his search and ‘search’ (the search page)) and for each product property and value.}

_{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). Instead, use product specific columns giving you the specific product performance like display_requests, weighted_display_requests, total_product_order_num, viewed_cnt, basketed_cnt, bought_orders, bought_order_products, bought_order_product_qty and bought_order_product_discount_crncy_amt.}

_{The month column is a date field always set with the first day of the month.}

Facet

_{one row per:}

• _session

• _widget

• _{facet type}

• _{facet name}

• _{facet value}

_{This table contains information about each session and for each facet type, name and value (the refinement / filtering option selected by the user in the web-site) and for each widget.}

_{As the columns of the session_primary table are duplicated along the different products of the same order it is important not to sum them while grouping by session only, which would cause over-counting.}

_{one row per:}

• _month

• _widget

• _{facet type}

• _{facet name}

• _{facet value}

• _product

• _{test variant}

• _channel

• _country

• _{device category}

_{This table contains information about each month and for each facet type, name and value (the refinement / filtering option selected by the user in the web-site), for each widget and for each product.}

_{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). Instead, use product specific columns giving you the specific product performance like display_requests, weighted_display_requests, total_product_order_num, viewed_cnt, basketed_cnt, bought_orders, bought_order_products, bought_order_product_qty and bought_order_product_discount_crncy_amt.}

_{The month column is a date field always set with the first day of the month.}

_{one row per:}

• _month

• _widget

• _{facet type}

• _{facet name}

• _{facet value}

• _{product property name}

• _{product property value}

• _{test variant}

• _channel

• _country

• _{device category}

_{This table contains information about each month and for each facet type, name and value (the refinement / filtering option selected by the user in the web-site), for each widget and for each product property and value.}

_{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). Instead, use product specific columns giving you the specific product performance like display_requests, weighted_display_requests, total_product_order_num, viewed_cnt, basketed_cnt, bought_orders, bought_order_products, bought_order_product_qty and bought_order_product_discount_crncy_amt.}

_{The month column is a date field always set with the first day of the month.}

Context item

_{one row per:}

• _month

• _widget

• _{context sub-type}

• _{context item}

• _{test variant}

_{This table contains one row per session, per widget (i.e.: choice_id), per product id appearing in the context of a query (Product Detail Page, Basket, ...) with all the key information about the session FROM the session view, AS well all the key informations about the orders of the session, and this for each product id and subtype separately (the subtype is mainProduct when there is one product or if it is the highest value item in the basket and subProduct otherwise). The requests and orders and other session metrics are specific for each product id. The contect product id should always be set with otherwise risks of overcounting, especially for the metrics of the sessions, LIKE orders, order_qty and total_crncy_amt.}

_{one row per:}

• _month

• _widget

• _{context sub-type}

• _{context item}

• _product

• _{test variant}

_{This table contains one row per session key dimension, per widget (i.e.: choice_id), per context product id (in the PDP or the basket) and per displayed/bought product with all the key information about the session FROM the session view, AS well all the key informations about the orders of the session, and this for each context product id and subtype separately but not per product, however, the display_requests and bought_orders are specific to each product. The requests and orders and other session metrics are specific for each context product id and subtype and products. The widget, the context product id and subtype and the product should always be set with otherwise risks of overcounting, especially for the metrics of the sessions, LIKE orders, order_qty and total_crncy_amt.}

_{The month column is a date field always set with the first day of the month.}

_{one row per:}

• _month

• _widget

• _{context sub-type}

• _{context item}

• _{product property name}

• _{product property value}

• _{test variant}

_{This table contains one row per session key dimension, per widget (i.e.: choice_id), per context product property name and value (in the PDP or the basket) and per displayed/bought product property name and value with all the key information about the session FROM the session view, AS well all the key informations about the orders of the session, and this for each context product property name and value and subtype separately but not per product property name and value, however, the display_requests and bought_orders are specific to each product property name and value. The requests and orders and other session metrics are specific for each context product property name and value and subtype and products. The widget, the context product property name and value and subtype and the product property name and value should always be set with otherwise risks of overcounting, especially for the metrics of the sessions, LIKE orders, order_qty and total_crncy_amt.}

_{The month column is a date field always set with the first day of the month.}

_{one row per:}

• _month

• _widget

• _{context sub-type}

• _{context item property name}

• _{context item property value}

• _{test variant}

_{This table contains one row per session, per widget (i.e.: choice_id), per product id appearing in the context of a query (Product Detail Page, Basket, ...) with all the key information about the session FROM the session view, AS well all the key informations about the orders of the session, and this for each product id and subtype separately (the subtype is mainProduct when there is one product or if it is the highest value item in the basket and subProduct otherwise). The requests and orders and other session metrics are specific for each product id. The contect product id should always be set with otherwise risks of overcounting, especially for the metrics of the sessions, LIKE orders, order_qty and total_crncy_amt.}

_{The month column is a date field always set with the first day of the month.}

_{one row per:}

• _month

• _widget

• _{context sub-type}

• _{context item property name}

• _{context item property value}

• _product

• _{test variant}

_{This table contains one row per session, per widget (i.e.: choice_id), per product id appearing in the context of a query (Product Detail Page, Basket, ...) with all the key information about the session FROM the session view, AS well all the key informations about the orders of the session, and this for each product id and subtype separately (the subtype is mainProduct when there is one product or if it is the highest value item in the basket and subProduct otherwise). The requests and orders and other session metrics are specific for each product id. The contect product id should always be set with otherwise risks of overcounting, especially for the metrics of the sessions, LIKE orders, order_qty and total_crncy_amt.}

_{The month column is a date field always set with the first day of the month.}

_{one row per:}

• _month

• _widget

• _{context sub-type}

• _{context item property name}

• _{context item property value}

• _{product property name}

• _{product property value}

• _{test variant}

_{This table contains one row per session key dimension, per widget (i.e.: choice_id), per context product property name and value (in the PDP or the basket) and per displayed/bought product property name and value with all the key information about the session FROM the session view, AS well all the key informations about the orders of the session, and this for each context product property name and value and subtype separately but not per product property name and value, however, the display_requests and bought_orders are specific to each product property name and value. The requests and orders and other session metrics are specific for each context product property name and value and subtype and products. The widget, the context product property name and value and subtype and the product property name and value should always be set with otherwise risks of overcounting, especially for the metrics of the sessions, LIKE orders, order_qty and total_crncy_amt.}

_{The month column is a date field always set with the first day of the month.}

Position

_{one row per:}

• _{page position (product_order_num)}

• _{page offset}

• _widget

• _{test variant}

• _channel

• _country

• _{device category}

_{This table contains one row per session key dimension, per widget (i.e.: choice_id) and per product id with all the key information about the sessions FROM the session view, AS well all the key informations about the orders of the sessions, and this for each product separately. While the requests and orders and other session metrics are not specific for each product, the display_requests and bought_orders are specific to each product. The widget and the product id should always be set with otherwise risks of overcounting, especially for the metrics of the sessions, LIKE orders, order_qty and total_crncy_amt AS well AS the product metrics, LIKE bought_orders.}