widget_search_term_product
- Sylvain Paillard
Description
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.
Granularity
one row per:
month
widget
search term
product
test variant
Columns
Field name | Type | Mode | Description |
|---|---|---|---|
product_id | STRING | NULLABLE | PRIMARY DIMENSION - MUST BE SET TO AVOID OVERCOUNT - the product id |
groupby_field | STRING | NULLABLE | GROUP BY field |
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 exact product margin |
rounded_margin | FLOAT | NULLABLE | the rounded product margin |
exact_price | FLOAT | NULLABLE | the exact product price |
rounded_price | FLOAT | NULLABLE | the rounded product price |
product_owners | STRING | REPEATED | the product owners |
search_term | STRING | NULLABLE | PRIMARY DIMENSION - MUST BE SET TO AVOID OVERCOUNT - the searched term |
widget | STRING | NULLABLE | PRIMARY DIMENSION - MUST BE SET TO AVOID OVERCOUNT - the widget name: search, navigation, basket, ... |
tpo | STRING | NULLABLE | the tpo of this widget |
journey_step | STRING | NULLABLE | the journey step of this widget (awareness, consideration, evaluation, purchase, re-buy, relationship) |
variant_id | STRING | NULLABLE | the test variant name |
variants | INTEGER | NULLABLE | the total number of test variants currently in this widget |
requests | INTEGER | NULLABLE | total number of requests, UNIQUE PER REQUEST, NO RISK OF OVERCOUNT |
landing_requests | INTEGER | NULLABLE | the number of requests which were landing page requests |
exit_requests | INTEGER | NULLABLE | the number of requests which were exit page requests |
avg_nth_page | FLOAT | NULLABLE | the average nth page view the request takes place at |
avg_page_size | FLOAT | NULLABLE | the number of results displayed |
landing_sessions | INTEGER | NULLABLE | 1 if the session has at least one request being a landing page requests 0 otherwise |
exit_sessions | INTEGER | NULLABLE | 1 if the session has at least one request being an exit page requests 0 otherwise |
day | DATE | NULLABLE | PK - starting timestamp (date time) of the session |
sessions | INTEGER | NULLABLE | number of sessions |
visitor_personas | INTEGER | NULLABLE | number of visitors (cookie-based) |
user_personas | INTEGER | NULLABLE | number of users (only set for logged in sessions) |
returnings | INTEGER | NULLABLE | number of returning sessions, based ON prior existence of the visitor_persona_id |
page_view_cnt | INTEGER | NULLABLE | total number of page views in this session |
page_view_1 | INTEGER | NULLABLE | 1 if page_view_cnt = 1 0 otherwise |
page_view_2 | INTEGER | NULLABLE | 1 if page_view_cnt = 2 0 otherwise |
page_view_3 | INTEGER | NULLABLE | 1 if page_view_cnt = 3 0 otherwise |
page_view_4 | INTEGER | NULLABLE | 1 if page_view_cnt = 4 0 otherwise |
page_view_5plus | INTEGER | NULLABLE | 1 if page_view_cnt >= 5 0 otherwise |
product_view_cnt | INTEGER | NULLABLE | total number of product detail page view in this session |
product_view_1 | INTEGER | NULLABLE | 1 if product_view_cnt = 1 0 otherwise |
product_view_2 | INTEGER | NULLABLE | 1 if product_view_cnt = 2 0 otherwise |
product_view_3 | INTEGER | NULLABLE | 1 if product_view_cnt = 3 0 otherwise |
product_view_4 | INTEGER | NULLABLE | 1 if product_view_cnt = 4 0 otherwise |
product_view_5plus | INTEGER | NULLABLE | 1 if product_view_cnt >= 5 0 otherwise |
add_to_basket_cnt | INTEGER | NULLABLE | total number of products added to the basket in this session |
add_to_basket_1 | INTEGER | NULLABLE | 1 if add_to_basket_cnt = 1 0 otherwise |
add_to_basket_2 | INTEGER | NULLABLE | 1 if add_to_basket_cnt = 2 0 otherwise |
add_to_basket_3 | INTEGER | NULLABLE | 1 if add_to_basket_cnt = 3 0 otherwise |
add_to_basket_4 | INTEGER | NULLABLE | 1 if add_to_basket_cnt = 4 0 otherwise |
add_to_basket_5plus | INTEGER | NULLABLE | 1 if add_to_basket_cnt >= 5 0 otherwise |
basket_crncy_amt | FLOAT | NULLABLE | currency amount of the items added to the baskets during the sessions |
basket_qty | INTEGER | NULLABLE | quantity of items in the baskets |
channel | STRING | NULLABLE | referrer channel: paid search, organic search, e-mail, referral, display, direct, social, other |
country | STRING | NULLABLE | the country of the session based ON the ip of the session |
device_category | STRING | NULLABLE | the type of device: Smartphone, Personal computer, Tablet, ... |
session_duration | INTEGER | NULLABLE | the number of seconds between the first and last event of the session in seconds |
session_orders | INTEGER | NULLABLE | the number of orders in this session |
orders | INTEGER | NULLABLE | the number of orders FROM the sessions found in the order export |
order_products | INTEGER | NULLABLE | the number of product in the orders FROM the sessions found in the order export |
order_qty | INTEGER | NULLABLE | the total quantity of products in the orders FROM the sessions found in the order export |
total_crncy_amt | FLOAT | NULLABLE | the total value of the orders FROM the sessions found in the order export |
total_margin_crncy_amt | FLOAT | NULLABLE | the total margin value of the orders FROM the sessions found in the order export |
order_products_list_crncy_amt | FLOAT | NULLABLE | the total value of the products before discount in the orders FROM the sessions found in the order export |
order_products_discount_crncy_amt | FLOAT | NULLABLE | the total value of the products after discount in the orders FROM the sessions found in the order export |
order_products_avg_list_crncy_amt | FLOAT | NULLABLE | the average value of the products before discount in the orders FROM the sessions found in the order export |
order_products_avg_discount_crncy_amt | FLOAT | NULLABLE | the average value of the products after discount in the orders FROM the sessions found in the order export |
scenarios | INTEGER | NULLABLE | the number of different strategy scenarios |
total_offsets | INTEGER | NULLABLE | the total sum of the offset (0 when first page) |
request_total_seconds | INTEGER | NULLABLE | the total number of seconds between the start of the sessions and the request time stamp |
display_requests | INTEGER | NULLABLE | the number of requests displaying this product |
weighted_display_requests | FLOAT | NULLABLE | Display request * the weight for this order_num |
total_product_order_num | INTEGER | NULLABLE | the total order position number of this product over the requests |
viewed_cnt | INTEGER | NULLABLE | the number of pdp views in which this product was viewed in this session |
basketed_cnt | INTEGER | NULLABLE | the number of basket in which this product was added in this session, typically 0 or 1 |
bought_orders | INTEGER | NULLABLE | the number of orders in which this product was bought in this session, typically 0 or 1 |
bought_order_products | INTEGER | NULLABLE | the number of times this product was bought in this session, typically 0 or 1 |
bought_order_product_qty | INTEGER | NULLABLE | the quantity of this product bought in this session |
bought_order_product_list_crncy_amt | FLOAT | NULLABLE | the potential sales value with the list price without discount (hypothetitical) product bought in this session |
bought_order_product_discount_crncy_amt | FLOAT | NULLABLE | the value of the products bought in this session (price after discount * quantity) |
bought_order_product_margin_crncy_amt | FLOAT | NULLABLE | the value of the products bought in this session (price after discount * quantity) |
total_viewed_cnt | INTEGER | NULLABLE | the number of pdp views in which this product was viewed in this session |
total_basketed_cnt | INTEGER | NULLABLE | the number of basket in which this product was added in this session, typically 0 or 1 |
total_bought_orders | INTEGER | NULLABLE | the number of orders in which this product was bought in this session, typically 0 or 1 |
total_bought_order_products | INTEGER | NULLABLE | the number of times this product was bought in this session, typically 0 or 1 |
total_bought_order_product_qty | INTEGER | NULLABLE | the quantity of this product bought in this session |
total_bought_order_product_list_crncy_amt | FLOAT | NULLABLE | the potential sales value with the list price without discount (hypothetitical) product bought in this session |
total_bought_order_product_discount_crncy_amt | FLOAT | NULLABLE | the value of the products bought in this session (price after discount * quantity) |
total_bought_order_product_margin_crncy_amt | FLOAT | NULLABLE | the value of the products bought in this session (price after discount * quantity) |
KPI_list | RECORD | REPEATED | list of KPIs, each KPI is technically represented by a STRUCT of (kpi_name string,kpi_val_num numeric,kpi_val_text string) |
KPI_list. kpi_name | STRING | REQUIRED | KPI_list. kpi_name |
KPI_list. kpi_val_num | INTEGER | NULLABLE | KPI_list. kpi_val_num |
KPI_list. kpi_val_text | STRING | NULLABLE | KPI_list. kpi_val_text |
KPI_defined | RECORD | NULLABLE | list of KPIs, each KPI is technically represented by a STRUCT of (kpi_name string,kpi_val_num numeric,kpi_val_text string) |
KPI_defined. guidedProductView | FLOAT | NULLABLE | KPI_defined. guidedProductView |
KPI_defined. guidedAddToBasket | FLOAT | NULLABLE | KPI_defined. guidedAddToBasket |
KPI_defined. guidedByProductViewAddToBasket | FLOAT | NULLABLE | KPI_defined. guidedByProductViewAddToBasket |
KPI_defined. guidedPurchase | FLOAT | NULLABLE | KPI_defined. guidedPurchase |
KPI_defined. guidedBannerClick | FLOAT | NULLABLE | KPI_defined. guidedBannerClick |
KPI_defined. guidedRevenue | FLOAT | NULLABLE | KPI_defined. guidedRevenue |
account | STRING | NULLABLE | the account |
parameters | RECORD | REPEATED | general structure of parameters and values |
parameters. name | STRING | NULLABLE | parameters. name |
parameters. value | STRING | NULLABLE | parameters. value |
semantic_filter | INTEGER | NULLABLE | 0 if no semantic filter applied, 1 if any did |
semantic_filter_cnt | INTEGER | NULLABLE | number of semantic filter applied |
semantic_filters | RECORD | REPEATED | applied semantic filters |
semantic_filters. name | STRING | NULLABLE | semantic_filters. name |
semantic_filters. name_label | STRING | NULLABLE | semantic_filters. name_label |
semantic_filters. value | STRING | NULLABLE | semantic_filters. value |
semantic_filters. value_label | STRING | NULLABLE | semantic_filters. value_label |
redirect | INTEGER | NULLABLE | number of sessions having done this search with a redirect |
redirect_cnt | INTEGER | NULLABLE | number of requests with a redirect |
full_query_removal | INTEGER | NULLABLE | 0 if the search term was fully removed by a semantic filter 1 otherwise |
partial_query_removal | INTEGER | NULLABLE | 0 if the search term was partially removed by a semantic filter 1 otherwise |
refinements | RECORD | REPEATED | refinements done for this search query |
refinements. query_txt | STRING | NULLABLE | refinements. query_txt |
refinements. requests | INTEGER | NULLABLE | refinements. requests |
refinements. sessions | INTEGER | NULLABLE | refinements. sessions |
refinements. visitors | INTEGER | NULLABLE | refinements. visitors |
global_sessions | FLOAT | NULLABLE | global_sessions |
global_requests | FLOAT | NULLABLE | global_requests |
global_viewed_cnt | FLOAT | NULLABLE | global_viewed_cnt |
global_basketed_cnt | FLOAT | NULLABLE | global_basketed_cnt |
global_bought_orders | FLOAT | NULLABLE | global_bought_orders |
global_bought_order_products | FLOAT | NULLABLE | global_bought_order_products |
global_bought_order_product_qty | FLOAT | NULLABLE | global_bought_order_product_qty |
global_bought_order_product_list_crncy_amt | FLOAT | NULLABLE | global_bought_order_product_list_crncy_amt |
global_bought_order_product_discount_crncy_amt | FLOAT | NULLABLE | global_bought_order_product_discount_crncy_amt |
global_bought_order_product_margin_crncy_amt | FLOAT | NULLABLE | the value of the products bought in this session (price after discount * quantity) |
global_total_sessions | FLOAT | NULLABLE | global_total_sessions |
global_total_product_view_cnt | FLOAT | NULLABLE | global_total_product_view_cnt |
global_total_add_to_basket_cnt | FLOAT | NULLABLE | global_total_add_to_basket_cnt |
global_total_orders | FLOAT | NULLABLE | global_total_orders |
global_total_order_products | FLOAT | NULLABLE | global_total_order_products |