Loyalty Hive Tables

The section explains the Hive tables, including details such as schema and how dynamic attributes are stored and queried.

Introduction to Hive in Loyalty

Loyalty is based on prescriptive, person centric data model, built especially for marketing use cases. The platform uses modern technologies and one among them is Hive, used for data storage. Hive is highly scalable and has good usability features due to the familiar relational or SQL interface, though it is not built on relational tables. In our platform, Hive is mainly used to query data across a collection of people, especially for segmentation, analytics and machine learning. For example, to compile an audience list to send out an email. The main Hive tables in the platform include:

Member
Activity
Activity Item
Event
Campaign

Pre-built capabilities in the platform generate the queries for these tables to execute processes such as for segmentation and analytics.

Note: For more information on Hive User Defined Functions, see Hive User Defined Functions.

Tables

This section describes the tables that are accessible when writing Queries with the Hive Execution Type.

Note: Within this article, the term dynamic Attribute refers to Attributes that are user-defined, rather than default system Attributes.

Member Table

The Member table contains all the Member Attributes defined in your Loyalty instance.

Column	Type	Description	Example
member_id	STRING	The primary identifier of the Member table.	M-000000001
email	STRING	The email address of the Member .	foo@bar.com
first_name	STRING	The Member’s first name.	John
last_name	STRING	The Member’s last name.	Doe
mailing_street	STRING	The Member’s mailing street address.
mailing_city	STRING	The Member’s mailing city.	Oklahoma City
mailing_state	STRING	The Member’s mailing state.	Oklahoma
mailing_postal_code	STRING	The Member’s mailing postal code or zip code.	73101
mailing_country	STRING	The Member’s mailing country.	US
birthdate	DATE	The Member’s birth date.	1990-05-11
mobile_phone	STRING	The Member’s mobile phone	+14012342122
member_since	TIMESTAMP	The timestamp of the Member’s sign-up.	2020-01-01 01:01:01.000
receive_email_offers	BOOLEAN	A flag used for filtering out Members that will be skipped for email offers.	true or false
receive_sms_offers	BOOLEAN	A flag used for filtering out Members that will be skipped for SMS offers.	true or false
receive_mobile_app_offers	BOOLEAN	A flag used for filtering out Members that will be skipped for mobile app offers.	true or false
receive_mail_offers	BOOLEAN	A flag used for filtering out Members that will be skipped for mail offers.	true or false
receive_e_statements	BOOLEAN	A flag used for filtering out Members that will be skipped for electronic statements.	true or false
current_tier	STRING	The Member’s current Tier.	Silver
expiration_date	TIMESTAMP	The expiration date of the Member’s Tier.	2020-01-01 01:01:01.000
tier_in_progression	STRING	The Member's projected Tier once the current Tier expires.	Gold
suspend_email	BOOLEAN	A flag used for filtering out email addresses that will be skipped for emails.	true or false
suspend_email_date	TIMESTAMP	The timestamp when the suspend_email flag was set to *true*.	2020-01-01 01:01:01.000
suspend_email_cause	STRING	The reason why the suspend_email flag was set to true.	email bounced
referrer_id	STRING	The member_id of the person that referred this Member to sign-up in the loyalty program.	M-000000002
last_active_at	TIMESTAMP	The timestamp when the Member was last active.	2020-01-01 01:01:01.000
deactivated	BOOLEAN	A flag if the Member is deactivated.	true or false
integration_id	STRING	Reference to the unique identifier of the client’s Member database.	12312312-15678
gender	STRING	The Member’s gender.	Male or Female; M or F
marital_status	STRING	The Member’s marital status.	SINGLE
education	STRING	The Member’s education details.
income	STRING	The Member’s income details.
employment	STRING	The Member’s employment details.
ethnicity	STRING	The Member’s ethnicity.
test_member	BOOLEAN	A flag indicating the Member is Test Member.	true or false
visitor	BOOLEAN	A flag indicating the Member is a visitor, meaning they have not enrolled in the loyalty program.	true or false
suspend_redemption	BOOLEAN	A flag indicating the Member redemption should be prevented.	true or false
card_id	STRING	The Member’s card ID; this value can be different from their member_id.	1233123-12312315647
receive_newsfeed_like_notification	BOOLEAN		true or false
receive_newsfeed_comment_notification	BOOLEAN		true or false
created_at	TIMESTAMP	The timestamp when the Member Profile was created on the platform.	2020-04-04 01:01:01
has_children	BOOLEAN		true or false
sl_segments	ARRAY<STRING>	The list of Segments the Member belongs to.	[“segment1”, “segment2”]
child_member_ids	ARRAY<STRING>	The list of child accounts for the Member account.	[“M-00000009”, “M-000000011”]
bigint_attr	MAP<STRING,BIGINT>	The dynamic attributes of a Member with type BIGINT.	{ “dyn_bigint” : 10}
decimal_attr	MAP<STRING,DECIMAL>	The dynamic Attributes of a Member with type DECIMAL.	{ “dyn_decimal” : 10.1}
timestamp_attr	MAP<STRING,TIMESTAMP>	The dynamic Attributes of a Member with type TIMESTAMP/DATETIME	{ “dyn_ts” : “2020-04-04 01:01:01.000”}
date_attr	MAP<STRING,DATE>	The dynamic Attributes of a Member with type DATE.	{ “dyn_date” : “2020-04-04”}
string_attr	MAP<STRING,STRING>	The dynamic Attributes of a Member with type STRING.	{ “dyn_str” : “a customer defined value”}
boolean_attr	MAP<STRING,BOOLEAN>	The dynamic Attributes of a Member with type BOOLEAN.	{ “dyn_bool” : true}
list_of_string_attr	MAP<STRING,ARRAY<STRING>>	The dynamic Attributes of a Member with type LIST OF STRING.	{ “dyn_list_of_str” :[‘a’, ‘b’, ‘c’]}
all_time_activities	MAP<STRING,BIGINT>	The all time count per Activity Type for a Member.	{“purchase” : 10, “redemption” : 1}
all_time_metrics_earned	MAP<STRING,DECIMAL>	The all time earned values for each Metric.	{ “point”: 100, “spend” : 50}
all_time_metrics_redeemed	MAP<STRING,DECIMAL>	The all time redeemed values for each Metric.	{ “point”: 100, “spend” : 50}
all_time_metrics_expired	MAP<STRING,DECIMAL>	The all time expired values for each Metric.	{ “point”: 100, “spend” : 50}
sl_business_units	ARRAY<STRING>	The list of Business Units the Member belongs to.	[“us”, “uk”]
parent_integration_id	STRING
person_type	STRING
updated_at	TIMESTAMP	The timestamp when the Member profile was last updated.	2020-01-01 01:01:01.000
synced_at	TIMESTAMP	The timestamp when the Member was synchronized to the Hive Member table.	2020-01-01 01:01:01.000

Writing Queries Against the Member Table

Basic Queries

Below is a sample basic Query against the Member table.

Copy

SELECT member_id, first_name, last_name, email
FROM MEMBER_TABLE
LIMIT 1

Querying Dynamic Attributes

Loyalty’s query engine allows dynamic Attributes to be abstracted to query writers. Querying for dynamic Attributes and system Attributes works the same way within Loyalty’s query engine.

For example, let's say you have a dynamic Attribute named: a_boolean_attribute. Query writers can write it this way:

Copy

SELECT count(1)
FROM MEMBER_TABLE
WHERE a_boolean_attribute = true

Since a_boolean_attribute is a dynamic Attribute, Loyalty's query engine automatically transforms it to:

Copy

SELECT count(1)
FROM MEMBER_TABLE
WHERE boolean_attr[“a_boolean_attribute”] = true

Activity Table

The Activity table contains data related to the Member actions and behaviors that the Loyalty platform tracks, such as purchases, Reward Redemptions, Punch Card punches, etc.

Column	Type	Description	Example
sl_ext_id	STRING	The external identifier of the Activity.	12312312-1aufasdelerwt
sl_activity_ts	TIMESTAMP	The timestamp of the Activity.	2020-01-01 01:01:01.000
sl_member_id	STRING	The member_id to which the Activity is connected. This Attribute is *NULL* for anonymous Activities. This Attribute can be joined with the *member_id* column on the Member table.	M-000000001
sl_type	STRING	The Activity type.	sl_purchase
sl_context	STRING	The context of the Activity.
sl_id	STRING	Internal unique identifier of the Activity per member.	1
sl_subtype	STRING	The subtype of the Activity.	pending
sl_label	STRING	The Activity label is displayed within the platform interface.	Campaign Offer #441
sl_parent_id	BIGINT	The row ID of the parent object (when applicable)	12345
sl_parent_name	STRING	The internal name of the parent object (when applicable)	obj_internal_name
sl_metric	STRING	The Metric relevant to the Activity. This Attribute can be null.	point
sl_value	DECIMAL	The Metric value for the Activity. Depending on the configured Earn Rules, this may or may not be applicable. To query the earned Metrics for the Activity, refer to the earned_metrics Attribute.	11
sl_integration_id	STRING	Unique identifier of the Activity.
sl_location	STRING	The location of the Activity. It could be a postal ID, depending on your loyalty program.
sl_comment	STRING	Special notes or comments regarding the Activity.	This was issued due to some error.
sl_processed_ts	TIMESTAMP	The mutable timestamp when the Activity was last processed. If the Activity was reprocessed, this value would be updated.	2020-01-01 01:01:01.000
sl_received_ts	TIMESTAMP	The timestamp when the Activity was received by the Loyalty platform.	2020-01-01 01:01:01.000
earn_type	STRING	This Attribute can either be: earned, redeemed, or expired.	earned
sl_tz	STRING	The timezone of the Activity timestamp.	UTC
sl_offset	BIGINT	The timezone offset relative to GMT or UTC.	-7
sl_first_processed_ts	TIMESTAMP	The immutable timestamp when the Activity was first processed. If the Activity was reprocessed, this value would NOT change.	2020-01-01 01:01:01.000
bigint_attr	MAP<STRING, BIGINT>	This column contains the dynamic Attributes of an Activity with type BIGINT.	{ “dyn_bigint” : 10}
decimal_attr	MAP<STRING, DECIMAL>	This column contains the dynamic Attributes of an Activity with type DECIMAL.	{ “dyn_decimal” : 10.1}
timestamp_attr	MAP<STRING, TIMESTAMP>	This column contains the dynamic Attributes of an Activity with type TIMESTAMP/DATETIME.	{ “dyn_ts” : “2020-04-04 01:01:01”}
date_attr	MAP<STRING, DATE>	This column contains the dynamic Attributes of an Activity with type DATE.	{ “dyn_date” : “2020-04-04”}
string_attr	MAP<STRING, STRING>	This column contains the dynamic Attributes of an Activity with type STRING.	{ “dyn_str” : “a customer defined value”}
boolean_attr	MAP<STRING, BOOLEAN>	This column contains the dynamic Attributes of an Activity with type BOOLEAN.	{ “dyn_bool” : true}
list_of_string_attr	MAP<STRING, ARRAY<STRING>>	This column contains the dynamic Attributes of an Activity with type LIST OF STRING.	{ “dyn_list_of_str” :[‘a’, ‘b’, ‘c’]}
earned_metrics	MAP<STRING, DECIMAL>	The earned Metric from the Activity. This Attribute can contain multiple Metrics.	{“point” : 1 , “spend” : 5.0}
metrics_earned_snapshot	MAP<STRING, DECIMAL>	The earned Metrics snapshot before the Activity was processed. This value can be used for computing the accruals per Activity.	{“point” : 1 , “spend” : 5.0}
metrics_redeemed_snapshot	MAP<STRING, DECIMAL>	The redeemed Metrics snapshot before the Activity was processed. This value can be used for computing the accruals per Activity.	{“point” : 1 , “spend” : 5.0}
metrics_expired_snapshot	MAP<STRING, DECIMAL>	The expired Metrics snapshot before the Activity was processed. This value can be used for computing the accruals per Activity.	{“point” : 1 , “spend” : 5.0}

Writing Queries Against the Activity Table

Basic Queries

Below is a sample basic Query against the Activity table.

Copy

SELECT sl_activity_ts, sl_type, sl_member_id 
FROM ACTIVITY_TABLE 
WHERE in_period(sl_activity_ts, “last1m”) = TRUE

Querying Dynamic Attributes

Loyalty’s query engine allows dynamic Attributes to be abstracted to query writers. Querying for dynamic Attributes and system Attributes works the same way within Loyalty’s query engine.

For example, let's say you have a dynamic Attribute named: a_boolean_attribute. Query writers can write it this way:

Copy

SELECT sl_activity_ts, sl_type, a_boolean_attribute
FROM ACTIVITY_TABLE 
WHERE in_period(sl_activity_ts, “last1m”) = TRUE

Since a_boolean_attribute is a dynamic Attribute, Loyalty's query engine automatically transforms it to:

Copy

SELECT sl_activity_ts, sl_type, boolean_attr[“a_boolean_attribute”] 
FROM ACTIVITY_TABLE 
WHERE in_period(sl_activity_ts, “last1m”) = TRUE

Note: When writing ad hoc Queries, the best practice recommendation is to reduce the Query to a certain period to optimize Query performance. Use the in_period UDF if possible.

Activity Item Table

The Activity Item table contains the line item data from the Activity table. Each item in the Activity table from the Attribute sl_items is written to the Activity Item table in its own row.

Column	Type	Description	Example
sl_ext_id	STRING	The external identifier of the Activity.	12312312-1aufasdelerwt
sl_activity_ts	TIMESTAMP	The timestamp of the Activity.	2020-01-01 01:01:01.000
sl_member_id	STRING	The member_id to which the Activity is connected. This Attribute is Null for anonymous Activities. This Attribute can be joined with the *member_id* column on the Member table and also with the sl_member_id column on the Activity table.	M-000000001
sl_type	STRING	The Activity type.	sl_purchase
sl_item_id	BIGINT	A sequential count on the item number. n line items would mean 1 to n.	1
sl_item_name	STRING	The item name.	sofa
sl_item_category	STRING	The line item category.	furniture
sl_id	STRING	Internal unique identifier of the Activity per member.	1
sl_context	STRING	The context of the Activity.
sl_subtype	STRING	The subtype of the Activity.	pending
sl_item_quantity	DECIMAL	The line item quantity, if it’s a Purchase Activity.	10
sl_item_price	DECIMAL	The line item price, if it’s a Purchase Activity.	15.1
sl_location	STRING	The Activity’s location.	New York
sl_tz	STRING	The timezone of the Activity timestamp.	UTC
sl_offset	BIGINT	The timezone offset relative to GMT or UTC.	-7
bigint_attr	MAP<STRING, BIGINT>	This column contains the dynamic Attributes of an Activity with type BIGINT.	{ “dyn_bigint” : 10}
decimal_attr	MAP<STRING, DECIMAL>	This column contains the dynamic Attributes of an Activity with type DECIMAL.	{ “dyn_decimal” : 10.1}
timestamp_attr	MAP<STRING, TIMESTAMP>	This column contains the dynamic Attributes of an Activity with type TIMESTAMP/DATETIME.	{ “dyn_ts” : “2020-04-04 01:01:01”}
date_attr	MAP<STRING, DATE>	This column contains the dynamic Attributes of an Activity with type DATE.	{ “dyn_date” : “2020-04-04”}
string_attr	MAP<STRING, STRING>	This column contains the dynamic Attributes of an Activity with type STRING.	{ “dyn_str” : “a customer defined value”}
boolean_attr	MAP<STRING, BOOLEAN>	This column contains the dynamic Attributes of an Activity with type BOOLEAN.	{ “dyn_bool” : true}
list_of_string_attr	MAP<STRING, ARRAY<STRING>>	This column contains the dynamic Attributes of an Activity with type LIST OF STRING.	{ “dyn_list_of_str” :[‘a’, ‘b’, ‘c’]}

Writing Queries Against the Activity Item Table

Basic Queries

Below is a sample basic Query against the Activity Item table.

Copy

SELECT sl_activity_ts, sl_type, sl_member_id
FROM ACTIVITY_ITEM_TABLE 
WHERE in_period(sl_activity_ts, “last1m”) = TRUE

Querying Dynamic Attributes

Loyalty’s query engine allows dynamic Attributes to be abstracted to query writers. Querying for dynamic Attributes and system Attributes works the same way within Loyalty’s query engine.

For example, let's say you have a dynamic Attribute named: a_boolean_attribute. Query writers can write it this way:

Copy

SELECT sl_activity_ts, sl_type, a_boolean_attribute
FROM ACTIVITY_ITEM_TABLE 
WHERE in_period(sl_activity_ts, “last1m”) = TRUE

Since a_boolean_attribute is a dynamic Attribute, Loyalty's query engine automatically transforms it to:

Copy

SELECT sl_activity_ts, sl_type, boolean_attr[“a_boolean_attribute”] 
FROM ACTIVITY_ITEM_TABLE 
WHERE in_period(sl_activity_ts, “last1m”) = TRUE

Note: When writing ad hoc Queries, the best practice recommendation is to reduce the Query to a certain period to optimize Query performance. Use the in_period UDF if possible.

Event Table

The Event table is used to store information about Events. An Event is conceptually similar to an Activity in that it represents some action or behavior that the Member takes, and that the platform is able to capture and track. An Event is essentially a lightweight, high-volume version of an Activity that's used mainly for reporting and auditing purposes.

Column	Type	Description	Example
environment_id	STRING	The client identifier.	100
organization_id	BIGINT	Sub-client Identifier.	100383
member_id	STRING	The member_id to which the Event is connected. This Attribute can be joined with the *member_id* column on the Member table.	M-000010101
event_id	STRING	The Event’s identifier (this value is NOT totally unique).	1
integration_id	STRING	Event Identifier to identify a “send” record.	8227927:100:1752025474
event_ts	TIMESTAMP	The timestamp of the Event occurrence.	2020-01-01 01:01:01.000
event_type	STRING	The Event type.	open, click, send, etc.
local_tz	STRING	Timezone ID for the Event.	UTC
processed_at	TIMESTAMP	The timestamp when the Event was processed and written into Hive.	2020-01-01 01:01:01.000
ext_parent_id	STRING	The Event’s external parent ID. For example, a Click Event could have the same value.
camp_id	BIGINT	The Campaign identifier.	17271
camp_name	STRING	The Campaign name.	20200416-FPS-FiletLob
camp_version	STRING	The Campaign identifier.	17271
channel_type_name	STRING	Channel used by the Campaign.	Email Message
campaign_type_name	STRING	Campaign type used. Either one-time, date triggered or event triggered.	Regular (one-time)
content_body_type_name	STRING	Content type for the Click Event occurrence. Either HTML or Text. This Attribute is applicable only for Click Events.	HTML
content_id	BIGINT	Content ID for the Click Event occurrence. This Attribute is applicable only for Click Events.	237323
content_name	STRING	Content Name for the Click Event occurrence. This Attribute is applicable only for Click Events.	MahiFamilyBundle_CONTENT
content_type_name	STRING	Content type name for the Click Event occurrence. Either HTML or Text. This Attribute is applicable only for Click Events.	HTML
user_agent_raw	STRING	User Agent String for the Click or Open Event occurrence. This Attribute is applicable only for Click and Open Events.	Mozilla/5.0 (iPhone; CPU iPhone OS 13_3_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148
ip_address	STRING	IP address for the Click or Open Event occurrence. This Attribute is applicable only for Click and Open Events.	10.24.71.6
click_link_url	STRING	URL Link for the Click Event occurrence. This Attribute is applicable only for Click Events.	https://google.com
click_link_name	STRING	URL Link Name for the Click Event occurrence.This Attribute is applicable only for Click Events.	Google
click_link_tags	STRING	Link tags applied to the link for the Click Event occurrence. This Attribute is applicable only for Click Events.	["20PercentOff", "3Course", "BloodyMaryMiniBar", "in-store", "Reservations", "ThrowbackThursday"]
category_name	STRING	Category applied to the link for the Click Event occurrence. This Attribute is applicable only for Click Events. .	web
web_event_submission_id	BIGINT	The web submission identifier for the Web Event. This Attribute is applicable only for Web Events, and only for Cheetah Digital.	1001
web_event_id	BIGINT	Web event identifier for the Web Event. This Attribute is applicable only for Web Events.	1
web_event_url_referrer	STRING	URL for the Web Event. This Attribute is applicable only for Web Events.	https://infini.com/pysearch?q=best+ev er+bread+pudding+5+stars&wunv=2
web_event_post_id	BIGINT	The post processing identifier for the Web Event. This Attribute is applicable only for Web Events, and only for Cheetah Digital.
bounce_category_hard_bounce	BOOLEAN	A flag indicating if this Bounce Event was a "hard" bounce. This Attribute is applicable only for Bounce Events.	TRUE/FALSE
source	STRING	The source application that creates this Event	RTX
source_json	STRING
params_number	MAP<STRING, DECIMAL>	List of DECIMAL parameters for the Event.	{ "bounce_type_id" : 2500, "base_cont_id" : 182813 } is_precache_open is_error is_discarded is_Loyalty_campaign is_anonymous error_type
params_string	MAP<STRING, STRING>	List of STRING parameters for the Event.	{"Bus_Unit" : "search", "Device" : "MOBILE", "Type" : "search"} precache_group error_message
params_date	MAP<STRING, TIMESTAMP>	List of TIMESTAMP parameters for the Event.
params_json	STRING	List of all parameters, of all data types, for the Event. A Stringified JSON.	"{""bounce_type_id" " : 2500, ""base_cont_id"" : 182813, ""Bus_Unit"" : ""search"", ""Device"" : ""MOBILE"", ""Type"" : ""search""}"
geolocation_latitude	STRING	The latitude where the Event occurred.	37.4043
geolocation_longitude	STRING	The longitude where the Event occurred.	-122.0748
place_id	STRING	The place identifier where the Event occurred.
region_id	STRING	The region identifier where the Event occurred.
geolocation_city	STRING	The city where the Event occurred.	Mountain View
geolocation_postal_code	STRING	The postal code where the Event occurred.	94043
geolocation_dma	STRING	The designated market area where the Event occurred.	Little Rock-Pine Bluff AR
geolocation_phone_area_code	STRING	The area code where the Event occurred.	650
geolocation	STRING	The geolocation of the Event (combination of the latitude and longitude).	(37.4043, -122.0748)
geolocation_region	STRING	The region where the Event occurred.	California
geolocation_country	STRING	The country where the Event occurred.	US
event_subtype	STRING	The Event subtype.
send_time	STRING	Timestamp when the Event was sent. For Send Events, event_ts is the same as send_time.	2020-01-01 01:01:01.000
business_unit	STRING	The business unit (sub-client) name the Event was sent from.	AFG
time_partition	STRING	Index field for the table. This Attribute records the year and month in YYYY-MM format. IMPORTANT: The best practice recommendation is to include or limit Queries by a certain time_partition so Queries are more efficient.	2020-04

Writing Queries Against the Event Table

Basic Queries

Below is a sample basic Query against the Event table.

Copy

SELECT event_ts, event_type, member_id 
FROM EVENT_TABLE 
WHERE in_period(sl_activity_ts, “last1m”) = TRUEAND event_type IN 
 
(“open”, “click”)

Joins with the Member Table

Below is a sample Query against the Event table, with a join to the Member table.

Copy

SELECT event_ts, event_type, member_id 
FROM EVENT_TABLE e
INNER JOIN MEMBER_TABLE m
ON m.member_id = e.member_id
WHERE in_period(event_ts, “last1m”) = TRUEAND event_type 
IN (“open”, “click”)

Note: When writing ad hoc Queries, the best practice recommendation is to reduce the Query to a certain period to optimize Query performance. Use the in_period UDF if possible.

Campaign Table

The Campaign table is used to store information about Cheetah Digital Campaigns.

Column	Type	Description	Example
organization_id	BIGINT	The client identifier.	100
environment_id	STRING	Sub-client Identifier.	100383
campaign_id	BIGINT	The Campaign identifier.	17271
campaign_version	STRING	The Campaign version.	1
campaign_name	STRING	The Campaign name.	20200416-FPS-FiletLob
campaign_type	STRING	The Campaign type. Either: one-time, date triggered or event triggered.	Regular (one-time)
content_type	STRING	Content type for the Click Event occurrence. Either HTML or Text. This Attribute is applicable only for Click Events.	HTML
channel	STRING	Channel used by the Campaign.	Email Message
description	STRING	The Campaign details / description.	Drive foot traffic into stores with the incentive of vanishing quantity.
category	STRING	The Campaign category.	sports
subcategory	STRING	The Campaign subcategory.	nl.sports.fr
offer_type	STRING	The Campaign offer type.	loyalty points
message_type	STRING	The Campaign message type.	reminder
tags	STRING	Campaign tags applied to the Campaign .	["national", "nascar"]
params_number	MAP<STRING, DECIMAL>	List of DECIMAL parameters for the Event.	{ "bounce_type_id" : 2500, "base_cont_id" : 182813 }
params_string	MAP<STRING, STRING>	List of STRING parameters for the Event.	{"Bus_Unit" : "search", "Device" : "MOBILE", "Type" : "search"}
params_date	MAP<STRING, TIMESTAMP>	List of TIMESTAMPE parameters for the Event.	{ "some_date" : "2020-10-11 00:00:00.111"}
params_json	STRING	List of all parameters, of all data types, for the Event. A Stringified JSON..	"{""bounce_type_id" " : 2500, ""base_cont_id"" : 182813, ""Bus_Unit"" : ""search"", ""Device"" : ""MOBILE"", ""Type"" : ""search""}"
meta_data_number	MAP<STRING, DECIMAL>	List of DECIMAL metadata for the Campaign.	{ "price" : null }
meta_data_string	MAP<STRING, STRING>	List of STRING metadata for the Campaign.	{ "category_subtype":"Manual ", "utm_campaign_name":"Halloween- Fallback", "message_type":"light-personalized ", "category" :"National", "primary_message" : "Holiday ", "utm_medium" : "email-instore" }
meta_data_date	MAP<STRING, TIMESTAMP>	List of TIMESTAMPE metadata for the Campaign.	{ "some_date" : "2020-10-11 00:00:00.111"}
meta_data_json	STRING	List of all metadata, of all data types, for the Campaign. A stringified JSON.	"{ ""price"": null, ""category_subtype"":""Manual "", ""utm_campaign_name"":""Halloween- Fallback"", ""message_type"":""light-personalized "", ""category"":""National"", ""primary_message"":""Holiday "", ""utm_medium"":""email-instore"" }"
send_start_ts	TIMESTAMP	The timestamp when the Campaign was launched.	2020-04-16 20:27:44.844
send_end_ts	TIMESTAMP	The timestamp when the Campaign ends.	2020-04-16 20:27:44.844
send_tz	STRING	The timezone identifier for the Campaign when it was launched.	Etc/UTC

Writing Queries Against the Campaign Table

Basic Queries

Below is a sample basic Query against the Campaign table.

Copy

SELECT campaign_id, campaign_name, campaign_version 
FROM CAMPAIGN_TABLE

Joins with the Campaign Table

Below is a sample Query against the Event table, with a join to the Campaign table.

Copy

SELECT event_ts, event_type, member_id 
FROM EVENT_TABLE e
INNER JOIN CAMPAIGN_TABLE c
ON c.campaign_id = e.camp_id AND
c.campaign_version = e.camp_version AND 
campaign_name = e.camp_name 
WHERE in_period(event_ts, “last1m”) = TRUEAND event_type 
IN (“open”, “click”)