widget_search_term_primary
- Sylvain Paillard
Description
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.
Granularity
one row per:
session
widget
search term
Columns
Field name | Type | Mode | Description |
|---|---|---|---|
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 |
landing_session | INTEGER | NULLABLE | 1 if the session has at least one request being a landing page requests 0 otherwise |
exit_session | INTEGER | NULLABLE | 1 if the session has at least one request being an exit page requests 0 otherwise |
facets | RECORD | REPEATED | list of the used facets and their values |
facets. name | STRING | NULLABLE | facets. name |
facets. type | STRING | NULLABLE | facets. type |
facets. values | RECORD | REPEATED | facets. values |
facets.values. value | STRING | NULLABLE | facets.values. value |
facets.values. weight | FLOAT | NULLABLE | facets.values. weight |
facet_session | INTEGER | NULLABLE | 1 if the session has at least one request being request with facets 0 otherwise |
context_parameters | RECORD | REPEATED | list of the used context aprameters and their values |
context_parameters. name | STRING | NULLABLE | context_parameters. name |
context_parameters. values | STRING | REPEATED | context_parameters. values |
filters | RECORD | REPEATED | list of the used context aprameters and their values |
filters. name | STRING | NULLABLE | filters. name |
filters. name_label | STRING | NULLABLE | filters. name_label |
filters. values | STRING | REPEATED | filters. values |
context_items | RECORD | REPEATED | list of the used context aprameters and their values |
context_items. name | STRING | NULLABLE | context_items. name |
context_items. values | STRING | REPEATED | context_items. values |
redirect_session | INTEGER | NULLABLE | 1 if the session has at least one request being with a redirect 0 otherwise |
redirect_cnt | INTEGER | NULLABLE | number of requests with a redirect |
extra_info_parameters | RECORD | REPEATED | list of the returned extra info parameters |
extra_info_parameters. name | STRING | NULLABLE | extra_info_parameters. name |
extra_info_parameters. values | STRING | REPEATED | extra_info_parameters. values |
avg_page_size | FLOAT | NULLABLE | the number of results displayed |
sort_field_session | INTEGER | NULLABLE | 1 if the session has at least one request being request with sort field 0 otherwise |
sort_field_cnt | INTEGER | NULLABLE | number of requests with changed sort fields |
sort_fields | RECORD | REPEATED | list of the used sort fields and their direction |
sort_fields. name | STRING | NULLABLE | sort_fields. name |
sort_fields. directions | STRING | REPEATED | sort_fields. directions |
session_id | STRING | NULLABLE | PK - unique session id |
session_start_ts | TIMESTAMP | NULLABLE | PK - starting timestamp (date time) of the session |
session_end_ts | TIMESTAMP | NULLABLE | PK - last timestamp (date time) of the session |
visitor_persona_id | STRING | NULLABLE | unique visitor id (cookie-based) |
user_persona_id | STRING | NULLABLE | unique user persona id (only set for logged in sessions) |
is_returning | STRING | NULLABLE | values Y and N indicating if the customer is a returning customer, 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 |
order_list | STRING | REPEATED | list of order ids in this session |
views_product_list | STRING | REPEATED | list of product views in this session |
basket_crncy_cd | STRING | NULLABLE | currency code of the items added to the basket during the session |
basket_crncy_amt | FLOAT | NULLABLE | currency amount of the items added to the basket during the session |
basket_qty | INTEGER | NULLABLE | quantity of items in the basket |
basket_product_list | STRING | REPEATED | list of product ids added to the basket during the session |
landing_page_url | STRING | NULLABLE | the landing page url |
landing_page_path | STRING | NULLABLE | the landing page url |
landing_page_path_array | STRING | REPEATED | the landing page path AS array |
landing_page_params | RECORD | REPEATED | the landing page path AS map |
landing_page_params. name | STRING | NULLABLE | landing_page_params. name |
landing_page_params. value | STRING | NULLABLE | landing_page_params. value |
referral_url | STRING | NULLABLE | full url string of the referrer url |
referral_path | STRING | NULLABLE | the landing page url |
referral_path_array | STRING | REPEATED | the landing page path AS array |
referral_params | RECORD | REPEATED | the landing page path AS map |
referral_params. name | STRING | NULLABLE | referral_params. name |
referral_params. value | STRING | NULLABLE | referral_params. value |
channel | STRING | NULLABLE | referrer channel: paid search, organic search, e-mail, referral, display, direct, social, other |
campaign | STRING | NULLABLE | the marketing campaign id passed to the initial referrer url of the session |
source | STRING | NULLABLE | the source web-site of the initial referrer url of the session |
medium | STRING | NULLABLE | the medium of the initial referrer url of the session |
source_medium | STRING | NULLABLE | the combination of the source and of the medium with a space in the middle |
keyword | STRING | NULLABLE | the keyword made in the search engine LIKE Google when coming to the web-site (not always availabe) |
adContent | STRING | NULLABLE | the marketing adContent id passed to the initial referrer url of the session |
country | STRING | NULLABLE | the country of the session based ON the ip of the session |
country_iso_cd | STRING | NULLABLE | the country iso code of the session based ON the ip of the session |
subdivision | STRING | NULLABLE | the subdivision (e.g.: state) of the session based ON the ip of the session |
subdivision_iso_cd | STRING | NULLABLE | the subdivision iso code (e.g.: state) of the session based ON the ip of the session |
postal_code | STRING | NULLABLE | the postal code of the session based ON the ip of the session |
city | STRING | NULLABLE | the city of the session based ON the ip of the session |
latitude | FLOAT | NULLABLE | the latitude float number point |
longitude | FLOAT | NULLABLE | the longitude float number point |
browser_name | STRING | NULLABLE | the browser name |
browser_version | STRING | NULLABLE | the browser version number |
operating_system | STRING | NULLABLE | the operating system name |
device_category | STRING | NULLABLE | the type of device: Smartphone, Personal computer, Tablet, ... |
browser_type | STRING | NULLABLE | the type of browser: Robot, Mobile Browser, Browser, Email client, ... |
user_agent | STRING | NULLABLE | the user agent full string (unparsed) |
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 |
order_id | STRING | NULLABLE | first order id of 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) |
max_offset | INTEGER | NULLABLE | the max offset |
offset_cnt | INTEGER | NULLABLE | the number of requets with an offset > 0 |
request_total_seconds | INTEGER | NULLABLE | the total number of seconds between the start of the sessions and the request time stamp |
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. guidedAddToBasket |
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 |
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 |
refinement | INTEGER | NULLABLE | 0 if no refinement, 1 if any |
refinement_cnt | INTEGER | NULLABLE | number of refinements |
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 |
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) |