◾Smartico DWH \ CRM views
This page is describing dimensional and fact tables related to the CRM module
Dimensional tables
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
Table structure:
activity_type_id
int64
activity_name
string
dwh_ext_xxx.dm_audience
The list of campaigns. Includes both real-time and scheduled.
Table structure:
audience_id
int64
audience_name
string
entry_mode_id
int64
0 - ONCE_IN_A_LIFE 1 - ONCE_IN_AN_OPEN_JOURNEY
2 - EVERY_TIME_CONDITION_MET
3 - STOP_AND_START
audience_status_id
int64
1 - Draft 2 - Active 3 - Paused 4 - Disabled 5 - Archived 6 - Executed (for Scheduled only)
audience_exec_type_id
int64
1 - Realtime campaign of Marketing category 2 - Realtime campaign of Operational category 3 - Scheduled campaign of Marketing category 4 - Scheduled campaign of Operational category
category_id
int64
1 - Marketing campaign 2 - Operational campaign
segment_id
int64
ID of the segment, targeted by the campaign
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
Table structure:
resource_id
int64
resource_name
string
resource_subject
string
Subject line for Mail, Push, Inbox For SMS will contain a full body of SMS For Popups and IVR will be null, as these resource types don't have a concept close to the 'subject'. Note that string can contain tags that are replaced during the actual building of the resource for specific user
resource_type_id
int64
1 - Mail 2 - Popup 3 - SMS 4 - Push 5 - Inbox 9 - IVR
dwh_ext_xxx.dm_segment
The table contains information about segment's names, types, status and the times of creation and last update.
segment_id
int64
ID of the segment
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
Example of query that returns list of the active segments with their descriptions and counts the number of active campaigns that the segment is currently used in.
dwh_ext_xxx.dm_providers_sms
List of all sms sent with their label, id, provider name and type, and create and update times.
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 emails sent with their label, id, provider name and type, and create and update times.
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_bonus_template
List of all bonus templates with their label, id, type and name.
label_id
int64
The id of the label to which this bonus templated belongs
label_bonus_template_id
int64
The id of the bonus template for the specified label
product_bonus_type_id
int64
Id that indicates the bonus type
public_name
string
Bonus template name
Fact tables
dwh_ext_xxx.j_av
Includes all the attribution value data. You can read more about attribute value here
Partitioned by: event_date
Data availability: last 180 days by event_date
Primary key: id
Note: the data can mutate over the time and depends if the deposit events are delivered with delay
id
int64
The incremental id of the calculation fact
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
value
numeric
The attribution amount in the label base currency that resource+campaign generated.
raw_value
numeric
The raw deposit amount in the original deposit currency
converted_value
numeric
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 base currency of the label
user_id
int64
The user id of the user who made the deposit
calculated_ts
timestamp
Timestamp when the calculation happened
Example of query that returns attribute value per campaign (audience_id)
Example of query that returns conversion of specific user per campaign, including the time of conversion
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
Partitioned by: fact_date
Data availability: last 180 days by fact_date
Primary key: engagement_id + fact_type_id + fact_date
Table structure:
fact_date
timestamp
The date/time when fact happened
user_id
int64
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)
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
uuid
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
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 will be the same for all facts through which the communication is transitioning. The ID id 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
Example of query that returns counts of communication by status and provider for the past 7 days.
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 support@smartico.ai for an explanation.
Partitioned by: create_date
Data availability: last 180 days by create_date
Primary key: engagement_id or engagement_uid
Table structure:
engagement_id
int64
unique id
engagement_uid
uuid/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
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
boolean
Indicating if the current element in the campaign flow was set as an AB test target for the communication element followed this target
Example of query that returns
data of label 2305
users entered campaign 793485 after 20/11/2023
and converted in this campaign
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
Partitioned by: fact_date
Data availability: last 180 days by fact_date
Primary key: bonus_id
Table structure:
fact_date
timestamp
the date of the fact
bonus_id
int65
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 as explained above
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, e.g. 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
entity_id
int64
ID of the entity related to the source_product_id that issued the bonus. e.g. If issued by the Automation Rule, this ID will represent ID of the rule.
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
error_message
string
The detailed error message
Example of query that returns final statuses of bonuses in the defined period
Example of query to get transactions issued from Dynamic Bonuses engine
Last updated