Overview

In this Data Type, you can define all the data about the user-generated content.

Example

In this example, we provide a simple attribute definition.

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": "rating",
  "creation": "2020-10-20 00:00:00",
  "persona_id": "82474",
  "products": [
    {
      "sku": "55111"
    }
  ],
  "value": 4.5,
  "title": [
    {
      "language": "de",
      "value": "Cool Produkt!"
    }
  ],
  "creation_tm": "2020-10-20 00:00:00",
  "client_id": 1,
  "src_sys_id": 1
}

Properties

Field name	Type	Mode	Description

Field name	Type	Mode	Description
id	STRING	REQUIRED	the unique id of the ugc
type	STRING	REQUIRED	the type of ugc: 'rating', 'question', 'answer', 'review', 'testimonial', 'comment', ...
creation	DATETIME	NULLABLE	the creation date time of the ugc
last_update	DATETIME	NULLABLE	the last update date time of the ugc
persona_type	STRING	NULLABLE	the persona type who created this ugc
persona_id	STRING	NULLABLE	the persona who created this ugc
parent_ugc_ids	STRING	REPEATED	the parent ugcs related to this ugc (e.g.: rating of themost helpful customer review / comments)
products	PRODUCT	REPEATED	connections to products
contents	CONTENT	REPEATED	relations to other contents
customers	CUSTOMER	REPEATED	relations to other customers
value	NUMERIC	REQUIRED	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

Resources

BigQuery JSON Schema

https://github.com/boxalino/data-integration-doc-schema/blob/master/doc/doc_user_generated_content.json

BigQuery DDL

https://github.com/boxalino/data-integration-doc-schema/blob/master/ddl/doc_user_generated_content.sql