Content: User Selections (wish-list, baskets, …)

Selection customers have made of products or other entities (all types, including pinning and thumbs up/down, …)

Overview

In this Data Type, you can define all the data about the user-selections (typically of products in a wishlist or a basket).

Example

In this example, we provide a simple attribute definition.

see example in JSON

Here is the example for the first case above (make sure to format it in JSONL before loading to BigQuery: Newline delimited JSON : https://en.wikipedia.org/wiki/JSON_streaming).

{
  "id": "1234",
  "type": "wishlist",
  "creation": "2020-10-20 00:00:00",
  "persona_id": "82474",
  "products": [
    {
      "sku": "123",
      "value": 2
    },
    {
      "sku": "456",
      "value": 1
    },
    {
      "sku": "789",
      "value": 1
    }
  ],
  "creation_tm": "2020-10-20 00:00:00",
  "client_id": 1,
  "src_sys_id": 1
}

Properties

Field name	Type	Mode	Description
id	STRING	REQUIRED	the unique id of the user selection
type	STRING	REQUIRED	the type of user selection: 'basket', 'wishlist', 'compare', 'favorite', ...
creation	DATETIME	NULLABLE	the creation date time of the user selection
last_update	DATETIME	NULLABLE	the last update date time of the user selection
persona_type	STRING	NULLABLE	the persona type who created this user selection
persona_id	STRING	NULLABLE	the persona who created this user selection
products	PRODUCT	REPEATED	connections to products
contents	CONTENT	REPEATED	relations to other contents
customers	CUSTOMER	REPEATED	relations to other customers
value	NUMERIC	NULLABLE	the ucg value (weighting) (e.g.: 0.0 - 5.0 for stars)
stores	STRING	REPEATED	the stores
title	LOCALIZED	REPEATED	the title of the attribute value
short_description	LOCALIZED	REPEATED	the short description of the product group
description	LOCALIZED	REPEATED	the description of the attribute value
images	LIST	REPEATED	the images of the attribute value
link	LOCALIZED	REPEATED	the link of the attribute value
tags	TAG	REPEATED	the tags , e.g.: [STRUCT('tag', 'hello world', [STRUCT('de', 'hello world')])]
labels	LABEL	REPEATED	the labels of the product line, e.g.: [STRUCT('symbol', 'delivery', '24h', [STRUCT('de', '24-H Versand')])]
status	BOOLEAN	NULLABLE	the ucg status
periods	PERIOD	REPEATED	information about the activity periods of the ugc
string_attributes	MAP	REPEATED	additional string (not localized) attributes of the product line (MAP type: STRING)
localized_string_attributes	MAP	REPEATED	additional localized string attributes (MAP type: LOCALIZED in STRING)
numeric_attributes	MAP	REPEATED	additional numeric (not localized) attributes (MAP type: NUMERIC)
localized_numeric_attributes	MAP	REPEATED	additional localized numeric attributes (MAP type: LOCALIZED in NUMERIC)
datetime_attributes	MAP	REPEATED	additional datetime (not localized) attributes (MAP type: DATETIME)
localized_datetime_attributes	MAP	REPEATED	additional localized datetime attributes (MAP type: LOCALIZED inDATETIME)
creation_tm	DATETIME	REQUIRED	technical field
client_id	INTEGER	REQUIRED	technical field
src_sys_id	INTEGER	REQUIRED	technical field

Table structure

BigQuery JSON Schema

[
      {
        "description": "the unique id of the user selection",
        "type": "STRING",
        "name": "id",
        "mode": "REQUIRED"
      },
      {
        "description": "the type of user selection: 'basket', 'wishlist', 'compare', 'favorite', ...",
        "type": "STRING",
        "name": "type",
        "mode": "REQUIRED"
      },
      {
        "type": "DATETIME",
        "name": "creation",
        "description": "the creation date time of the user selection"
      },
      {
        "type": "DATETIME",
        "name": "last_update",
        "description": "the last update date time of the user selection"
      },
      {
        "type": "STRING",
        "name": "persona_type",
        "description": "the persona type who created this user selection"
      },
      {
        "type": "STRING",
        "name": "persona_id",
        "description": "the persona who created this user selection"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name"
          },
          {
            "type": "STRING",
            "name": "product_line"
          },
          {
            "type": "STRING",
            "name": "product_group"
          },
          {
            "type": "STRING",
            "name": "sku"
          },
          {
            "type": "NUMERIC",
            "name": "value"
          },
          {
            "type": "DATETIME",
            "name": "creation"
          },
          {
            "type": "DATETIME",
            "name": "last_update"
          }
        ],
        "description": "connections to products",
        "type": "RECORD",
        "name": "products",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name"
          },
          {
            "type": "STRING",
            "name": "content_type"
          },
          {
            "type": "STRING",
            "name": "content_id"
          },
          {
            "type": "NUMERIC",
            "name": "value"
          },
          {
            "type": "DATETIME",
            "name": "creation"
          },
          {
            "type": "DATETIME",
            "name": "last_update"
          }
        ],
        "description": "relations to other contents",
        "type": "RECORD",
        "name": "contents",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name"
          },
          {
            "type": "STRING",
            "name": "persona_id"
          },
          {
            "type": "STRING",
            "name": "customer_id"
          },
          {
            "type": "NUMERIC",
            "name": "value"
          },
          {
            "type": "DATETIME",
            "name": "creation"
          },
          {
            "type": "DATETIME",
            "name": "last_update"
          }
        ],
        "description": "relations to other customers",
        "type": "RECORD",
        "name": "customers",
        "mode": "REPEATED"
      },
      {
        "description": "the ucg value (weighting) (e.g.: 0.0 - 5.0 for stars)",
        "type": "NUMERIC",
        "name": "value"
      },
      {
        "description": "the stores",
        "type": "STRING",
        "name": "stores",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "language",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "value",
            "mode": "REQUIRED"
          }
        ],
        "description": "the title of the user selection",
        "type": "RECORD",
        "name": "title",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "language",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "value",
            "mode": "REQUIRED"
          }
        ],
        "description": "the short description of the product group",
        "type": "RECORD",
        "name": "short_description",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "language",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "value",
            "mode": "REQUIRED"
          }
        ],
        "description": "the description of the user selection",
        "type": "RECORD",
        "name": "description",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "STRING",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "value",
            "mode": "REPEATED"
          }
        ],
        "description": "the images of the user selection",
        "type": "RECORD",
        "name": "images",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "language",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "value",
            "mode": "REQUIRED"
          }
        ],
        "description": "the link of the user selection",
        "type": "RECORD",
        "name": "link",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "value",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "STRING",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "loc_values",
            "mode": "REPEATED"
          }
        ],
        "description": "the tags of the product line, e.g.: [STRUCT('tag', 'hello world', [STRUCT('de', 'hello world')])]",
        "type": "RECORD",
        "name": "tags",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "value",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "STRING",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "loc_values",
            "mode": "REPEATED"
          }
        ],
        "description": "the labels of the user selection, e.g.: [STRUCT('symbol', 'helpful', 'very', [STRUCT('de', 'sehr hilfreich')])]",
        "type": "RECORD",
        "name": "labels",
        "mode": "REPEATED"
      },
      {
        "type": "BOOLEAN",
        "name": "status",
        "description": "the ucg status"
      },
      {
        "fields": [
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "DATETIME",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "start_datetime",
            "mode": "REPEATED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "DATETIME",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "end_datetime",
            "mode": "REPEATED"
          }
        ],
        "description": "information about the activity periods of the user selection",
        "type": "RECORD",
        "name": "periods",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional string (not localized) attributes of the user selection",
        "type": "RECORD",
        "name": "string_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "STRING",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional localized string attributes of the user selection",
        "type": "RECORD",
        "name": "localized_string_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "type": "NUMERIC",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional numeric (not localized) attributes of the user selection",
        "type": "RECORD",
        "name": "numeric_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "NUMERIC",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional localized numeric attributes of the user selection",
        "type": "RECORD",
        "name": "localized_numeric_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "type": "DATETIME",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional datetime (not localized) attributes of the user selection",
        "type": "RECORD",
        "name": "datetime_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "DATETIME",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional localized datetime attributes of the user selection",
        "type": "RECORD",
        "name": "localized_datetime_attributes",
        "mode": "REPEATED"
      },
      {
        "description": "technical field",
        "type": "DATETIME",
        "name": "creation_tm",
        "mode": "REQUIRED"
      },
      {
        "description": "technical field",
        "type": "INTEGER",
        "name": "client_id",
        "mode": "REQUIRED"
      },
      {
        "description": "technical field",
        "type": "INTEGER",
        "name": "src_sys_id",
        "mode": "REQUIRED"
      }
    ]

BigQuery DDL

CREATE OR REPLACE TABLE `YOUR_PROJECT.YOUR_DATASET.doc_user_selection_F_20201030` (
	  id STRING NOT NULL OPTIONS(description="the unique id of the user selection")
	, type STRING NOT NULL OPTIONS(description="the type of user selection: 'basket', 'wishlist', 'compare', 'favorite', ...")
	, creation DATETIME OPTIONS(description="the creation date time of the user selection")
	, last_update DATETIME OPTIONS(description="the last update date time of the user selection")
	, persona_type STRING  OPTIONS(description="the persona type who created this user selection")
	, persona_id STRING  OPTIONS(description="the persona who created this user selection")
	, products ARRAY<STRUCT<type STRING, name STRING, product_line STRING, product_group STRING, sku STRING, value NUMERIC, creation DATETIME, last_update DATETIME>> OPTIONS(description="connections to products")
	, contents ARRAY<STRUCT<type STRING, name STRING, content_type STRING, content_id STRING, value NUMERIC, creation DATETIME, last_update DATETIME>> OPTIONS(description="relations to other contents")
	, customers ARRAY<STRUCT<type STRING, name STRING, persona_id STRING, customer_id STRING, value NUMERIC, creation DATETIME, last_update DATETIME>> OPTIONS(description="relations to other customers")
	, value NUMERIC NOT NULL OPTIONS(description="the ucg value (weighting) (e.g.: 0.0 - 5.0 for stars)")
	, stores ARRAY<STRING> OPTIONS(description="the stores")
	, title ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the title of the user selection")
	, short_description ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the short description of the product group")
	, description ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the description of the user selection")
	, images ARRAY<STRUCT<name STRING NOT NULL, value ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>> OPTIONS(description="the images of the user selection")
	, link ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the link of the user selection")
	, tags ARRAY<STRUCT<type STRING NOT NULL, value STRING NOT NULL, loc_values ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>> OPTIONS(description="the tags of the product line, e.g.: [STRUCT('tag', 'hello world', [STRUCT('de', 'hello world')])]")
	, labels ARRAY<STRUCT<type STRING NOT NULL, name STRING NOT NULL, value STRING NOT NULL, loc_values ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>> OPTIONS(description="the labels of the user selection, e.g.: [STRUCT('symbol', 'helpful', 'very', [STRUCT('de', 'sehr hilfreich')])]")
	, status BOOLEAN OPTIONS(description="the ucg status")
	, periods ARRAY<STRUCT<start_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>, end_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>> OPTIONS(description="information about the activity periods of the user selection")
	, string_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRING NOT NULL>>> OPTIONS(description="additional string (not localized) attributes of the user selection")
	, localized_string_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>> OPTIONS(description="additional localized string attributes of the user selection")
	, numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<NUMERIC NOT NULL>>> OPTIONS(description="additional numeric (not localized) attributes of the user selection")
	, localized_numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<language STRING NOT NULL, value NUMERIC NOT NULL>>>> OPTIONS(description="additional localized numeric attributes of the user selection")
	, datetime_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<DATETIME NOT NULL>>> OPTIONS(description="additional datetime (not localized) attributes of the user selection")
	, localized_datetime_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>> OPTIONS(description="additional localized datetime attributes of the user selection")
    , creation_tm   DATETIME NOT NULL OPTIONS(description="technical field")
    , client_id     INT64 NOT NULL OPTIONS(description="technical field")
    , src_sys_id    INT64 NOT NULL OPTIONS(description="technical field")
    )
OPTIONS(description="The user-selction about products or other contents: e.g.: baket, wishlist, compare, favorite, ...");