◾Smartico DWH \ CRM views

Dimensional tables

dwh_ext_xxx.dm_j_formula

Definitions of formulas for Dynamic Rewards

Primary key: formula_id

Column	Type	Description
formula_id	INT64	Unique identifier for the formula
label_id	INT64	Label identifier for the formula
formula_name	STRING	Name of the formula
create_by	INT64	User who created the formula
create_date	TIMESTAMP	Date and time when the formula was created
update_date	TIMESTAMP	Date and time when the formula was last updated
formula_result_type_id	INT64	Type of the formula result: BONUS_AMOUNT = 1, BONUS_TEMPLATE = 2, POINTS = 4, GEMS = 5, DIAMONDS = 6
bonus_template_id	INT64	ID of Bonus template, only in case result type is BONUS_TEMPLATE (2)
auto_approve	BOOL	Flag indicating if the results of formula are automatically approved as soon as calculations are done
is_enabled	BOOL	Flag indicating if the formula is enabled
approve_amount_below	FLOAT64	The amount below which the bonus is approved automatically (not applicable for formula with type BONUS_TEMPLATE(2))
round_to_decimals	INT64	The number of decimals to round the result to (not applicable for formula with type BONUS_TEMPLATE(4))
min_allowed_bonus_amount	FLOAT64	Amounts lower than defined will be treated as invalid and won't be issued (not applicable for formula with type BONUS_TEMPLATE(2))
max_allowed_bonus_amount	FLOAT64	If the calculated bonus exceeds this limit, only the capped amount will be issued (not applicable for formula with type BONUS_TEMPLATE(2))
round_rule_type_id	INT64	Threee types of rounding: 1 - to floor, 2 - to ceil, 3 - to nearest
event_type_id	INT64	Event type 1
conditions	JSON	Conditions for the first event
event_type_id2	INT64	Event type 2
conditions2	JSON	Conditions for the second event
event_type_id3	INT64	Event type 3
conditions3	JSON	Conditions for the third event

dwh_ext_xxx.dm_engagement_fail_reason

Lookup table with possible reasons for engagement failures.

Primary key: fail_reason_id

Column	Type	Description
fail_reason_id	INT64	Unique identifier for the fail reason
description	STRING	Description of the fail reason
bo_bi_name	STRING	BackOffice/BI display name
create_date	TIMESTAMP	Date and time when the fail reason was created
update_date	TIMESTAMP	Date and time when the fail reason was last updated

dwh_ext_xxx.dm_audience

The list of campaigns. Includes both real-time and scheduled campaigns.

Primary key: audience_id

Column	Type	Description
audience_id	INT64	Unique identifier for the campaign
label_id	INT64	Label identifier
create_date	TIMESTAMP	Date and time when the campaign was created
update_date	TIMESTAMP	Date and time when the campaign was last updated
audience_name	STRING	Name of the campaign
entry_mode_id	INT64	Entry mode: 0 - ONCE_IN_A_LIFE, 1 - ONCE_IN_AN_OPEN_JOURNEY, 2 - EVERY_TIME_CONDITION_MET, 3 - STOP_AND_START
audience_status_id	INT64	Status: 1 - Draft, 2 - Active, 3 - Paused, 4 - Disabled, 5 - Archived, 6 - Executed (for Scheduled only)
segment_id	INT64	ID of the segment targeted by the campaign
audience_exec_type_id	INT64	Execution type: 1 - Realtime Marketing, 2 - Realtime Operational, 3 - Scheduled Marketing, 4 - Scheduled Operational
category_id	INT64	Category: 1 - Marketing campaign, 2 - Operational campaign
create_by	INT64	ID of backoffice user that created entity
event_type_id	INT64	Event type identifier for real-time campaigns

dwh_ext_xxx.dm_automation_rule

The list of automation rules. Includes both real-time and scheduled.

Primary key: rule_id

Column	Type	Description
rule_id	INT64	Unique ID of rule
label_id	INT64	Label identifier
create_date	TIMESTAMP	Date and time when the rule was created
update_date	TIMESTAMP	Date and time when the rule was last updated
rule_name	STRING	Name of rule
is_active	BOOL	Indicator if rule is active or archived/disabled
rule_type_id	INT64	Type: 1 - realtime, 2 - scheduled
rule_control_group_percents	INT64	Control group percentage for the rule
create_by	INT64	ID of backoffice user that created entity

dwh_ext_xxx.dm_segment

The table contains information about segment's names, types, status and the times of creation and last update.

Primary key: segment_id

Column	Type	Description
segment_id	INT64	ID of the segment
label_id	INT64	Label identifier
create_date	TIMESTAMP	Date and time when the segment was created
update_date	TIMESTAMP	Date and time when the segment was last updated
segment_name	STRING	Name of the segment
segment_status_id	INT64	Status of the segment: 1 - Active, 2 - Archived
segment_type_id	INT64	Type of segment: 1 - State based, 2 - Imported from CSV, 3 - Deprecated, 4 - Behavioural
create_by	INT64	ID of backoffice user that created entity
enable_dwh_export	BOOL	Indication that users of segment can be exported from DWH
estimated_users_count	INT64	Estimated number of users in the segment
last_estimated_date	TIMESTAMP	Date and time when the segment size was last estimated
conditions_readable	STRING	Conditions of the segment in human readable format
dwh_sql	STRING	DWH SQL to get users of the segment

Returns list of the active segments with their descriptions and counts the number of active campaigns that the segment is currently used in

SELECT 
	s.segment_name, 
	s.create_date, 
	s.update_date, 
	s.segment_type_id,
	CASE 
		WHEN s.segment_type_id = 1 THEN 'State based'
		WHEN s.segment_type_id = 2 THEN 'Imported from CSV'
		WHEN s.segment_type_id = 3 THEN 'Deprecated'
		WHEN s.segment_type_id = 4 THEN 'Behavioral'
		ELSE 'Unknown'
	END AS segment_type_description,
	COUNT(DISTINCT a.audience_id) AS active_campaign_count
FROM 
	dwh_ext_XXX.dm_segment AS s
JOIN 
	dwh_ext_XXX.dm_audience AS a
ON 
	s.segment_id = a.segment_id
WHERE 
	s.segment_status_id = 1  -- Active segments
	AND a.audience_status_id = 1  -- 1 indicates active campaigns
GROUP BY 
	s.segment_name, 
	s.create_date, 
	s.update_date, 
	s.segment_type_id;

If you are looking how to get list of users that belongs to specific segment, please check Export segment article. In the dm_segment table you can use enable_dwh_export column to see which of the segments are marked as available for exporting.

dwh_ext_xxx.dm_funnel_marker

This table defines specific markers within a campaign funnel, allowing for detailed tracking of user progression. It connects activities within an audience's journey to custom-defined funnel steps.

Primary key: audience_activity_id

Column	Type	Description
label_id	INT64	Label identifier
create_date	TIMESTAMP	Date and time when the marker was created
update_date	TIMESTAMP	Date and time when the marker was last updated
audience_activity_id	INT64	The ID of the audience activity to which the funnel marker is associated. Provides for direct connection to the j_engagements table on the activity_id.
funnel_marker	STRING	A string representing the specific marker within the funnel (e.g., "Made Deposit", "Played a game," "Received SMS ").

Query that reveals the number of users who reached each funnel marker in different campaigns over the past 7 days

SELECT
	A.audience_name,
	F.funnel_marker,
	COUNT(DISTINCT E.user_id) AS user_count
FROM
	dwh_ext_XXX.j_engagements AS E
INNER JOIN
	dwh_ext_XXX.dm_funnel_marker AS F ON E.activity_id = F.audience_activity_id
INNER JOIN
	dwh_ext_XXX.dm_audience AS A ON E.root_audience_id = A.audience_id
WHERE
	E.create_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY
	A.audience_name, F.funnel_marker
ORDER BY
	A.audience_name, F.funnel_marker

dwh_ext_xxx.dm_resource

Communication resources (templates) of all types, e.g. SMS, popups, emails, etc. Note that the list includes both main resources and variations of the main resources.

Primary key: resource_id

Column	Type	Description
label_id	INT64	Label identifier
create_date	TIMESTAMP	Date and time when the resource was created
update_date	TIMESTAMP	Date and time when the resource was last updated
resource_id	INT64	Unique ID of resource
resource_type_id	INT64	Type: 1 - Mail, 2 - Popup, 3 - SMS, 4 - Push, 5 - Inbox, 9 - IVR
resource_name	STRING	Name of resource in the backoffice
resource_subject	STRING	Subject line for Mail, Push, Inbox. For SMS contains full body. For Popups and IVR will be null.
create_by	INT64	ID of backoffice user that created entity
mail_content_type_id	INT64	Mail content type identifier
resource_parent_id	INT64	ID of parent resource, in case current one is variation

dwh_ext_xxx.dm_activity_type

List of possible "activities" that can be used to build a Flow for the scheduled & real-time campaigns. There are activities like "Send SMS", "Send Mail", "Give Bonus", "Stop campaign" etc. There are essential activities that exist in any campaign, like * Journey start (1) - represents the fact that the campaign started

• Journey stopped (2) - represents the stop of the campaign

• Journey Converted (3) - represents the fact that the campaign was converted

Primary key: activity_type_id

Column	Type	Description
create_date	TIMESTAMP	Date and time when the activity type was created
update_date	TIMESTAMP	Date and time when the activity type was last updated
activity_type_id	INT64	Unique identifier for the activity type
activity_name	STRING	Name of the activity

dwh_ext_xxx.dm_com_fail_reason

Lookup table with possible reasons for communication failures.

Primary key: fail_reason_id

Column	Type	Description
create_date	TIMESTAMP	Date and time when the fail reason was created
update_date	TIMESTAMP	Date and time when the fail reason was last updated
fail_reason_id	INT64	Unique identifier for the fail reason
description	STRING	Description of the fail reason

dwh_ext_xxx.dm_churn_rank

Contains information about churn ranks.

Primary key: rank_id

Column	Type	Description
create_date	TIMESTAMP	Date and time when the churn rank was created
update_date	TIMESTAMP	Date and time when the churn rank was last updated
rank_id	INT64	Unique identifier for the churn rank
rank_name	STRING	Name of the churn rank
upper_bound	FLOAT64	Upper bound of probability for the churn rank

dwh_ext_xxx.dm_bonus_template

Contains all bonus templates with their labels, IDs, types, and names.

Primary key: label_bonus_template_id

Column	Type	Description
label_id	INT64	Label identifier
create_date	TIMESTAMP	Time when this bonus template was created
update_date	TIMESTAMP	Time when this bonus template was last updated
label_bonus_template_id	INT64	Unique identifier for the bonus template
product_bonus_type_id	INT64	Type of the bonus
internal_name	STRING	Internal name of the bonus template
public_name	STRING	Public name of the bonus template

dwh_ext_xxx.dm_providers_sms

List of all SMS providers with their label, id, provider name and type.

Primary key: id

Column	Type	Description
label_id	INT64	Label identifier
create_date	TIMESTAMP	Time when this communication provider is defined
update_date	TIMESTAMP	Time when this communication provider is last updated
id	INT64	ID of the communication provider
provider_name	STRING	Name of the exact provider
provider_type	STRING	Name of the overall service provider

dwh_ext_xxx.dm_providers_mail

List of all email providers with their label, id, provider name and type.

Primary key: id

Column	Type	Description
label_id	INT64	Label identifier
create_date	TIMESTAMP	Time when this communication provider is defined
update_date	TIMESTAMP	Time when this communication provider is last updated
id	INT64	ID of the communication provider
provider_name	STRING	Name of the exact provider
provider_type	STRING	Name of the overall service provider

dwh_ext_xxx.dm_rfm_category

Contains information about RFM categories.

Primary key: category_id

Column	Type	Description
create_date	TIMESTAMP
update_date	TIMESTAMP
category_id	INT64
category_name	STRING

Fact tables

dwh_ext_xxx.j_av

Attribution value data. Includes all the attribution value data. You can read more about attribute value in the documentation. Note: the data can mutate over the time and depends if the deposit events are delivered with delay. We recommend to reload last 3 days of data on every day ETL job.

Primary key: event_id, root_engagement_id, engagement_id, kpi_impact_id, resource_id

Partitioned by: event_date

Data availability: last 1080 days by event_date

Column	Type	Description
id	INT64	The incremental id of the calculation fact. Important: this column is not populated anymore. See the note above regarding primary key (deprecated)
label_id	INT64	Label ID
event_date	TIMESTAMP	The date when the attribution event (deposit) happened
resource_id	INT64	FK: dm_resource, main resource that was used for the communication
audience_id	INT64	FK: dm_audience, the campaign that triggered communication (root_audience_id)
value	FLOAT64	The attribution amount in the label base currency that resource+campaign generated
raw_value	FLOAT64	The raw deposit amount in the original deposit currency
converted_value	FLOAT64	The deposit amount converted to the label base currency
event_id	INT64	The id of the attribution event (deposit) on smartico side
engagement_id	INT64	Reference to the j_engagements, represents the point in the Campaign/Flow from where the communication was triggered
root_engagement_id	INT64	Reference to the j_engagements, represents the "root" point in the Campaign/Flow from where the communication was triggered
kpi_impact_id	INT64	The id of the attribution value impact: 1 - Because of resource delivery, 2 - Because the user has an impression on the resource, 3 - Because the user has interaction with the resource (click), 4 - Indirect impact, when nothing from above happened
default_currency	STRING	The original deposit currency of the user
user_id	INT64	The user id of the user who made the deposit
calculated_ts	TIMESTAMP	Timestamp when the calculation happened

Returns attribute value per campaign (audience_id)

SELECT audience_id, sum(value) as value
				FROM dwh_ext_XXX.j_av
				WHERE event_date >= TIMESTAMP("2024-07-01")
				GROUP BY audience_id

			Returns conversion of specific user per campaign, including the time of conversion
		

WITH x as (
					select audience_id, event_date, sum(value) as av,
					from dwh_ext_XXX.j_av
					where user_id = 69726604
					group by audience_id, event_date
				)
				select a.audience_id, a.audience_name, x.av, x.event_date
				from x
				inner join dwh_ext_XXX.dm_audience a on x.audience_id = a.audience_id
				order by x.event_date desc

dwh_ext_xxx.j_communication

This includes all communications sent to the end-user and all possible outcomes (fact_type_id) of these communications, like failure of delivery, impressions, clicks, etc. Ideas to understand communications concept * the communication is always triggered by some "activity" from the campaign. This activity is referenced by engagement_id or engagement_uid. Such "activity" is part of the campaign, where the starting point of the campaign (the fact that the user entered into the campaign) is presented by the root_engagement_uuid & root_engagement_create_date. The campaign definition itself if presented by root_audience_id

• The first fact that happens for communication is CREATE, after that, it is usually either SENT and DELIVERED or FAILED. It can be later seen by the end-user (IMPRESSION) and CLICKED. Users can also OPTOUT from the communication by following the "opt-out" link in the resource. For Inbox type of message, the user can also READ it and DELETE

• The communication is always connected to some resource (template), presented as resource_id, and in case the resource has variations, by the resource_variation_id

• Note that smartico is tracking the fact of any communication only 30 days after it's created

Primary key: engagement_id + fact_type_id + fact_date

Partitioned by: fact_date

Data availability: last 1080 days by fact_date

Column	Type	Description
label_id	INT64	Label ID
fact_date	TIMESTAMP	The date/time when fact happened
user_id	INT64	User ID in the Smartico system
fact_type_id	INT64	CREATE = 1, SENT = 2, IMPRESSION = 3, CLICK = 4, FAIL = 5, DELIVERED = 6, OPTOUT = 7, READ = 8 (for Inbox only), DELETE = 9 (for Inbox only), SNOOZE = 10 (for Mails & SMS)
fact_details	STRING	For CLICK fact - the link that was clicked, for FAIL - the detailed reason for fail
activity_type_id	INT64	Type of activity that triggered that communication (FK: dm_activity_type)
engagement_uid	STRING	Reference to the j_engagements, represents the point in the Campaign/Flow from where the communication was triggered
resource_id	INT64	FK: dm_resource, main resource
resource_variation_id	INT64	FK: dm_resource, specific variation of the resource
root_audience_id	INT64	FK: dm_audience, the campaign that triggered communication
root_engagement_uuid	STRING	Reference to the j_engagements, represents the "root" point in the Campaign/Flow from where the communication was triggered
root_engagement_create_date	TIMESTAMP	The date when the campaign that triggered specific communication is started for the user
engagement_id	INT64	Similar to engagement_uid, but in the int64 presentation
communication_id	INT64	The unique ID of the communication; the same for all facts through which the communication is transitioning. The ID is generated on the fact CREATE(1)
audience_category_id	INT64	Category of the campaign: 1 - Marketing, 2 - Operational
external_id	STRING	ID of the communication assigned by the external gateway at the moment when the external gateway accepted communication from Smartico
label_provider_id	INT64	ID of the communication provider (dm_providers_mail and dm_providers_sms)
planned_send_date	TIMESTAMP	In some setups, the campaign that creates communication is scheduling delivery for a later time, in such case planned_send_date will reflect this date
fail_reason_id	INT64	ID of the fail reason, FK: dm_com_fail_reason
user_ext_id	STRING	The ID of the user in the operator system
crm_brand_id	INT64	ID of the brand in the Smartico system

Returns counts of communication by status and provider for the past 7 days

SELECT
					COALESCE(sm.provider_name, ml.provider_name) AS provider_name,
					DATE(c.fact_date) AS communication_date,
					CASE
						WHEN c.fact_type_id = 1 THEN 'CREATE'
						WHEN c.fact_type_id = 2 THEN 'SENT'
						WHEN c.fact_type_id = 3 THEN 'IMPRESSION'
						WHEN c.fact_type_id = 4 THEN 'CLICK'
						WHEN c.fact_type_id = 5 THEN 'FAIL'
						WHEN c.fact_type_id = 6 THEN 'DELIVERED'
						WHEN c.fact_type_id = 7 THEN 'OPTOUT'
						WHEN c.fact_type_id = 8 THEN 'READ'
						WHEN c.fact_type_id = 9 THEN 'DELETE'
						ELSE 'UNKNOWN'
					END AS status_name,
					COUNT(*) AS status_count
				FROM dwh_ext_XXX.j_communication c
				LEFT JOIN dwh_ext_XXX.dm_providers_sms sm ON c.label_provider_id = sm.id
				LEFT JOIN dwh_ext_XXX.dm_providers_mail ml ON c.label_provider_id = ml.id
				JOIN dwh_ext_XXX.dm_resource r ON c.resource_id = r.resource_id
				WHERE c.fact_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
					AND r.resource_type_id IN (1, 3) -- Filter for Mail (1) and SMS (3)
				GROUP BY communication_date, provider_name, status_name
				ORDER BY communication_date DESC, status_count DESC

Returns number of created, delivered, failed mails, with clicks, views metrics for each day and each campaign

WITH 
				eng_st_dt AS (
					SELECT root_engagement_id, DATE(create_date) AS engagement_create_date
					FROM dwh_ext_XXX.j_engagements 
					WHERE activity_type_id = 1 AND DATE(create_date) = '2025-07-18'
				),
				eng AS (
					SELECT DISTINCT root_audience_id, crm_brand_id, engagement_id, root_engagement_id
					FROM dwh_ext_XXX.j_engagements
					WHERE DATE(create_date) >= '2025-07-18' AND activity_type_id = 50
				)
				SELECT
					S.engagement_create_date,
					R.root_audience_id AS audience_id,
					A.audience_name,
					SUM(CASE WHEN C.fact_type_id = 1 THEN 1 ELSE 0 END) AS email_created,
					SUM(CASE WHEN C.fact_type_id = 2 THEN 1 ELSE 0 END) AS email_sent,
					COUNT(DISTINCT CASE WHEN C.fact_type_id = 3 THEN R.engagement_id ELSE NULL END) AS email_impression,
					COUNT(DISTINCT CASE WHEN C.fact_type_id = 4 THEN R.engagement_id ELSE NULL END) AS email_click,
					COUNT(DISTINCT CASE WHEN C.fact_type_id = 5 THEN R.engagement_id ELSE NULL END) AS email_fails
				FROM eng R
				INNER JOIN dwh_ext_XXX.j_communication AS C ON C.engagement_id = R.engagement_id 
				INNER JOIN eng_st_dt S ON R.root_engagement_id = S.root_engagement_id 
				INNER JOIN dwh_ext_XXX.dm_audience A ON R.root_audience_id = A.audience_id
				WHERE DATE(C.fact_date) >= '2025-07-18'
				GROUP BY R.root_audience_id, A.audience_name, S.engagement_create_date

Ideas to understand communications concept:

• The communication is always triggered by some "activity" from the campaign. This activity is referenced by engagement_id or engagement_uid. Such "activity" is part of the campaign, where the starting point of the campaign (the fact that the user entered into the campaign) is presented by the root_engagement_uuid & root_engagement_create_date. The campaign definition itself is presented by root_audience_id.

• The first fact that happens for communication is CREATE, after that, it is usually either SENT and DELIVERED or FAILED. It can be later seen by the end-user (IMPRESSION) and CLICKED. Users can also OPTOUT from the communication by following the "opt-out" link in the resource. For Inbox type of message, the user can also READ it and DELETE.

• The communication is always connected to some resource (template), presented as resource_id, and in case the resource has variations, by the resource_variation_id.

• Note that smartico is tracking the fact of any communication only 30 days after it's created.

dwh_ext_xxx.j_automation_rule_progress

Represents the progress in automation rules. Partitioned by: dt_executed, query to the table should always use dt_executed in the WHERE clause.

Primary key: automation_rule_id + event_id

Partitioned by: dt_executed

Data availability: last 1080 days by dt_executed

Column	Type	Description
label_id	INT64	Label ID
automation_rule_id	INT64	FK: dm_automation_rule
dt_executed	TIMESTAMP	Time when the rule was executed
user_id	INT64	Smartico User ID
activity_type_id	INT64	FK: dm_activity_type. Indication of activity type that was executed
user_ext_id	STRING	The ID of the user in the operator system
execution_count	INT64	Number of times that rule was executed for one transaction. Value could be different from 1 in cases like "Give 1 point for 1 EUR bet", when user bet 3 EUR, the value will be 3
remaining_score	FLOAT64	For accumulated rules, e.g. give 1 EUR for each 1 EUR bet, in case of 1.5 EUR bet, remaining value will be 0.5 EUR
event_id	INT64	ID of event that triggered rule execution
scheduled_rule_log_id	INT64	For scheduled automation rules, the ID of the execution batch
q_date	TIMESTAMP	For rules with qualification action, the date when rule was qualified
q_valid_till_date	TIMESTAMP	For rules with qualification action, the date till which the rule will stay qualified
q_event_uuid	STRING	For rules with qualification action, UUID of event that qualified the rule
crm_brand_id	INT64	ID of the brand in the Smartico system

dwh_ext_xxx.j_engagements

The table represents the steps of how the user enters the campaign, exits it, converts, and goes through all the "activities" of the Flow defined in the campaign. The table represents the recursive structure of user transition over the campaign graph. For most of the use cases, you will need to analyze only records that have activity_type_id as one of * 1 - represents the start of the campaign

• 2 - stop the campaign

• 3 - conversion of campaign The table structure below describes only essential fields that could be used to analyze the campaign performance. If you need a deeper analysis, please contact [email protected] for an explanation.

Primary key: engagement_id or engagement_uid

Partitioned by: create_date

Data availability: last 1080 days by create_date

Column	Type	Description
label_id	INT64	Label ID
engagement_id	INT64	Unique ID
engagement_uid	STRING	Same as unique id, but uuid presentation
root_audience_id	INT64	FK: dm_audience
create_date	TIMESTAMP	Time when user got this activity
user_id	INT64	User ID in Smartico system
root_engagement_id	INT64	Reference to the same table pointing to the starting point of this campaign for the specific user
root_engagement_create_date	TIMESTAMP	The date when the campaign is started for a specific user
activity_type_id	INT64	FK: dm_activity_type
user_ext_id	STRING	The ID of the user in the operator system
resource_id	INT64	ID of the resource if the activity is related to the communication. Important that the field is populated retrospectively every night at 3 UTC
expected_stop_date	TIMESTAMP	Expected stop date/time of the campaign, set as create_date of the activity_type_id = 1 (start of the campaign) + campaign duration set in the BackOffice. Note that the campaign can be forcefully stopped inside the flow using "Stop" activity
ab_target	BOOL	Indicating if the current element in the campaign flow was set as an AB test target for the communication element followed this target
from_control_group	BOOL	Indicate if the entrance in the campaign was marked as "Control group" case. Users that are entering in the "Control group" will have engagements records with activity_type_id = 1,2,3. Because they are "Entering campaign" (1), they can "Stop in campaign" (2) and they can "Convert in campaign" (3), but there will be no other types of engagements as such users are not progressing through the flow
audience_id	INT64	The ID of the "connector" in the campaign flow, the "arrow" that connects one activity to another one. In most of the cases not needed in any reports **
event_id	INT64	Smartico ID of event that progressed the flow **
entry_event_details	JSON	The payload of event that triggered start of campaign **
activity_id	INT64	ID of activity in the flow **
event_type_id	INT64	ID of type of event that progressed the flow **
root_audience_entry_mode_id	INT64	Type of campaign entry mode: 0 - ONCE_IN_A_LIFE, 1 - ONCE_IN_AN_OPEN_JOURNEY, 2 - EVERY_TIME_CONDITION_MET, 3 - STOP_AND_START
root_audience_exec_type_id	INT64	Type of campaign: 1 - REALTIME, 2 - REALTIME_OPERATIONAL, 3 - SCHEDULED, 4 - SCHEDULED_OPERATIONAL
activity_details_json	JSON	Definition of activity that was executed **
scheduled_audience_log_id	INT64	ID of batch for the scheduled campaign. If campaign is recurring, this ID will indicate different time period when campaign started
ab_resources_cnt	INT64	In A/B testing of the resource, count of number of times that resource was sent **
crm_brand_id	INT64	ID of brand in Smartico system to which user belongs

Returns users that entered a specific campaign and converted

WITH SRC AS (
				SELECT
					engagement_id, root_audience_id, root_engagement_id,
					root_engagement_create_date, user_id, engagement_uid,
					activity_type_id, create_date, from_control_group, activity_id
				FROM dwh_ext_XXX.j_engagements
				WHERE activity_type_id in (1,2,3) -- 1=start, 2=stop, 3=convert
					AND root_audience_id IN (793485)
					AND create_date >= '2023-11-20T00:00:00Z'
					AND root_engagement_create_date >= '2023-11-20T00:00:00Z'
				),
				PREFINAL as (
					SELECT
						SRC.root_audience_id, SRC.root_engagement_id, user_id,
						MAX(CASE WHEN SRC.activity_type_id = 1 then SRC.engagement_uid else null end) as root_engagement_uid,
						MAX(CASE WHEN SRC.activity_type_id = 1 then SRC.create_date ELSE NULL END) as start_date,
						MAX(CASE WHEN SRC.activity_type_id = 3 then SRC.create_date ELSE NULL END) as convert_date,
						MAX(CASE WHEN SRC.activity_type_id = 2 then SRC.create_date ELSE NULL END) as stop_date,
						MAX(CASE WHEN SRC.activity_type_id = 1 THEN SRC.from_control_group ELSE NULL END) as from_control_group
					FROM SRC
					GROUP BY SRC.root_audience_id, SRC.root_engagement_id, SRC.user_id
				)
				SELECT * FROM PREFINAL WHERE convert_date IS NOT NULL

The table structure describes only essential fields that could be used to analyze the campaign performance. If you need a deeper analysis, please contact support. Fields marked with ** are given mainly for reference and rarely can be used in any type of reports.

dwh_ext_xxx.j_bonuses

The table represents the history of bonuses given to users, including all the attempts to issue bonuses through the API provided by the platform. Each bonus fact can be represented in the table multiple times, where each time it reflects the change in the lifecycle of the bonus. The status of the bonus is refllected in the column bonus_status_id with possible values: * 1 - NEW

• 3 - REDEEMED

• 4 - REDEEM_FAILED

Primary key: bonus_id

Partitioned by: fact_date

Data availability: last 1080 days by fact_date

Column	Type	Description
label_id	INT64	Label ID
fact_date	TIMESTAMP	The date of the fact
bonus_id	INT64	Unique ID of the transaction preserved for all facts of this transaction
user_id	INT64	Smartico user ID
user_ext_id	STRING	The ID of the user in the operator system
crm_brand_id	INT64	ID of the brand in the Smartico system
bonus_status_id	INT64	Status of bonus: 1 - NEW, 3 - REDEEMED, 4 - REDEEM_FAILED
bonus_cost_value	INT64	Shows the indicative value of real money spent on bonuses issued from the Smartico system if previously defined in the bonus templates
label_bonus_template_id	INT64	ID of template in the Smartico system
source_product_id	INT64	ID of the sub-system that issued the bonus: 0 - Campaign, 9 - Automation rule, 100 - Gamification, 125 - Dynamic Bonuses engine, 126 - Manual bonus, 5000 - Store, 8000 - Tournament, 8001 - Mini-game for Retention user, 8002 - Mini-game for Visitor
source_product_ref_id	INT64	ID that can be used to connect with the source product
engagement_uid	STRING	UID that matches the same in the j_engagements table
entity_id	INT64	ID of the entity related to the source_product_id that issued the bonus
root_audience_id	INT64	Similar to the entity_id, but should be used for the Campaign reference
error_code	INT64	Error code in case of failed to issue bonus
bonus_meta	JSON	Metadata of the bonus (JSON). Avoid querying this field whenever possible as it heavy JSON object

Returns final statuses of bonuses in the defined period

WITH MAIN AS (
					SELECT
						JB.fact_date AS create_date,
						JB.bonus_id,
						JB.user_id,
						JB.label_id,
						JB.crm_brand_id,
						JB.source_product_id,
						JB.source_product_ref_id,
						JB.root_audience_id,
						JB.bonus_status_id,
						JB.label_bonus_template_id,
						JB.entity_id,
						RANK() OVER (PARTITION BY bonus_id ORDER BY fact_date DESC) AS rank_desc,
						RANK() OVER (PARTITION BY bonus_id ORDER BY fact_date ASC) AS rank_asc
					FROM dwh_ext_XXX.j_bonuses JB
					WHERE JB.fact_date >= '2024-04-01T00:00:00Z' AND JB.fact_date < '2024-04-15T23:59:59.000Z'
				),
				FINAL AS (
					SELECT A.*, B.create_date AS bonus_create_date, B.entity_id AS entity_id
					FROM MAIN A
					LEFT JOIN MAIN B ON B.rank_asc = 1 AND A.bonus_id = B.bonus_id
					WHERE A.rank_desc = 1 AND (B.create_date >= '2024-04-01T00:00:00Z' AND B.create_date < '2024-04-15T23:59:59Z')
				)
				SELECT * FROM FINAL

Get bonuses count and cost related to campaigns for yesterday

SELECT
					da.audience_id AS campaign_id,
					da.audience_name AS campaign_name,
					COUNT(DISTINCT jb.bonus_id) AS bonus_count,
					SUM(jb.bonus_cost_value) AS total_bonus_cost,
					ARRAY_AGG(DISTINCT dbt.public_name) AS bonus_names
				FROM dwh_ext_XXX.j_bonuses AS jb
				JOIN dwh_ext_XXX.j_engagements AS je ON jb.source_product_ref_id = je.engagement_id
				JOIN dwh_ext_XXX.dm_audience AS da ON je.audience_id = da.audience_id
				JOIN dwh_ext_XXX.dm_bonus_template AS dbt ON jb.label_bonus_template_id = dbt.label_bonus_template_id
				WHERE DATE(je.create_date) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
					AND DATE(jb.fact_date) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
				GROUP BY 1, 2

Get transactions issued from Dynamic Bonuses engine

SELECT
					fact_date as create_date,
					user_id,
					user_ext_id,
					JSON_VALUE(bonus_meta,'$.bonusAmount') as bonus_amount
				FROM dwh_ext_XXX.j_bonuses
				WHERE TIMESTAMP_TRUNC(fact_date, DAY) = TIMESTAMP("2024-10-15")
					and source_product_id = 125 -- issued by dynamic bonuses
					and bonus_status_id = 3 -- show only redeemed transactions

dwh_ext_xxx.j_webhooks_facts

The table represents the history of executing webhooks. Webhooks can be triggered from Campaigns, Automation rules and upon completing the mission. Partitioned by: fact_date, query to the table should always use fact_date in the WHERE clause.

Primary key: engagement_uid

Partitioned by: fact_date

Data availability: last 1080 days by fact_date

Column	Type	Description
label_id	INT64	Label ID
fact_date	TIMESTAMP	The date of the fact
details	STRING	JSON structure holding details of HTTP request that was executed
http_code	INT64	The code returned by the target web server during execution
response	STRING	JSON structure holding details of HTTP response from the target web server
user_id	INT64	Smartico ID of the user in the context of which the webhook was executed
user_ext_id	STRING	ID of the user in the external system
crm_brand_id	INT64	Smartico ID of the brand in Smartico system to which the user belongs
source_product_id	INT64	ID of the sub-system that initiated the webhook, e.g. 0 - Campaign, 9 - Automation rule, 100 - Gamification (Missions)
source_product_ref_id	INT64	ID that can be used to connect with the source product. For example if the source_product_id is a Campaign, then the value here will correspond to the event_id in the j_engagements table
engagement_uid	STRING	UID that matches the same in the j_engagements table
root_audience_id	INT64	Similar to the entity_id, but should be used for the Campaign reference (dm_audience table)
audience_activity_id	INT64	ID of audience activity that represents uniquely the block in the campaign flow. Relevant only for webhooks executed from campaign, for other contexts will keep 0 value

dwh_ext_xxx.ml_player_preferences

The table contains information about players' gaming preferences names, types, the times of last update. The preferences are updated daily and consider the most recent player's gaming activity.

Primary key: user_id

Partitioned by: none

Data availability: all time

Column	Type	Description
label_id	INT64	Label ID
user_id	INT64	Smartico user ID
sport_player_share	FLOAT64	The share of the user's sport activity out of his total activity
casino_player_share	FLOAT64	The share of the user's casino activity out of his total activity
lottery_player_share	FLOAT64	The share of the user's lottery activity out of his total activity
favorite_casino_games	RECORD	List of the top 10 games (field: smr_game_id), the user engaged in most casino activity on, with the activity share per each game (field: share). In order to get the actual name of the favorite casino game the smr_game_id field needs to be joined with the smr_game_id field from the dwh_ext_xxx.dm_casino_game_name table.
favorite_casino_game_types	RECORD	List of the top 10 game types (field: smr_game_type_id), the user engaged in most casino activity on, with the activity share per each game type (field: share). In order to get the actual name of the favorite casino game type the smr_game_type_id field needs to be joined with the smr_game_type_id field from the dwh_ext_xxx.dm_casino_game_type table.
favorite_casino_games_by_type	RECORD	Per each game type (smr_game_type_id) - list of the top 10 games (field: smr_game_id), the user engaged in most casino activity on, with the activity share per each game type (field: share). In order to get the actual names of the casino games and the casino game types, the smr_game_type_id field needs to be joined with the smr_game_type_id field from the dwh_ext_xxx.dm_casino_game_type table and the smr_game_id field needs to be joined with the smr_game_id field from the dwh_ext_xxx.dm_casino_game_name table.
favorite_casino_game_providers	RECORD	List of the top 10 game providers (field: smr_provider_id), the user engaged in most casino activity on, with the activity share per each provider (field: share). In order to get the actual name of the favorite casino provider the smr_provider_id field needs to be joined with the smr_provider_id field from the dwh_ext_xxx.dm_casino_provider_name table.
favorite_casino_games_by_provider	RECORD	Per each game provider - list of the top 10 games (field: smr_provider_id), the user engaged in most casino activity on, with the activity share per each game type (field: share). In order to get the actual names of the casino games and the casino providers, the smr_provider_id field needs to be joined with the smr_provider_id field from the dwh_ext_xxx.dm_casino_provider_name table, and the smr_game_id field needs to be joined with the smr_game_id field from the dwh_ext_xxx.dm_casino_game_name table.
favorite_sport_types	RECORD	List of the top 10 sport branches (field: smr_sport_type_id), the user engaged in most sport activity on, with the activity share per each sport branch (field: share). In order to get the actual name of the favorite sport this field needs to be joined with the smr_sport_type_id field from the dwh_ext_xxx.dm_sport_type table.
favorite_sport_leagues	RECORD	List of the top 10 sport leagues (field: smr_sport_league_id), the user engaged in most sport activity on, with the activity share per each sport league (field: share). In order to get the actual name of the favorite league this field needs to be joined with the smr_sport_league_id field from the dwh_ext_xxx.dm_sport_league table.
update_date	TIMESTAMP	Date and time when the preferences were last updated

Returns the split of the database by the players' product preference, marking the players by "Pure Sport" when the share of the Sport activity is 1, "Pure Casino" when the share of the Casino activity is 1, or "Mixed" for the rest of the cases

SELECT
	case
		when sport_player_share = 1 then 'Pure Sport'
		when casino_player_share = 1 then 'Pure Casino'
		else 'Mixed'
	end as product_preference,
	count (user_id)
FROM dwh_ext_XXX.ml_player_preferences
GROUP BY sport_player_share, casino_player_share