◾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:

Column	Type	Note
activity_type_id	int64
activity_name	string

dwh_ext_xxx.dm_audience

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

Table structure:

Column	Type	Note
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:

Column	Type	Note
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.

Column	Type	Note
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.

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;

dwh_ext_xxx.dm_providers_sms

List of all sms sent with their label, id, provider name and type, and create and update times.

Column	Type	Note
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.

Column	Type	Note
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.

Column	Type	Note
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

Communication & Campaigns (engagements) fact tables and relation between them

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

Column	Type	Note
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)

SELECT  audience_id, sum(value) as value
FROM dwh_ext_xxx.j_av
WHERE event_date >= TIMESTAMP("2024-07-01")
group by audience_id

Example of query that 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

Partitioned by: fact_date

Data availability: last 180 days by fact_date

Primary key: engagement_id + fact_type_id + fact_date

Table structure:

Column	Type	Note
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.

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_2283.j_communication` c
LEFT JOIN
    `dwh_ext_2283.dm_providers_sms` sm ON c.label_provider_id = sm.id
LEFT JOIN
    `dwh_ext_2283.dm_providers_mail` ml ON c.label_provider_id = ml.id
JOIN
    `dwh_ext_2283.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;

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 [e-mail] for an explanation.

Partitioned by: create_date

Data availability: last 180 days by create_date

Primary key: engagement_id or engagement_uid

Table structure:

Column	Type	Note
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

   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 label_id = 2305 AND activity_type_id in (1,2,3)
            AND root_audience_id IN (793485)
            AND create_date >= '2023-11-20T00:00:00Z'
            AND root_engagement_create_date >= '2023-11-20T00:00:00Z'
            -- for the performance, you can limit upper date only with 30 days from the 'to', this is MAX duration of campaign
            AND create_date <= TIMESTAMP_ADD('2023-11-21T23:59:59Z', INTERVAL 31 DAY)
            AND root_engagement_create_date <= '2023-11-21T23:59:59Z'
    ),
    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
        WHERE true
        GROUP BY SRC.root_audience_id, SRC.root_engagement_id, SRC.user_id
    ),
    FINAL as (
        SELECT 
            COUNT(*) OVER() AS cnt, ROW_NUMBER() OVER(ORDER BY PREFINAL.start_date DESC) AS row,
            PREFINAL.*,
        FROM PREFINAL
        WHERE true AND convert_date IS NOT NULL
    )
    SELECT *
    FROM FINAL

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:

Column	Type	Note
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

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_2283.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')
        AND true
)
SELECT *
FROM FINAL

Example of query to get transactions issued from Dynamic Bonuses engine

SELECT 
    fact_date as create_date,
    user_id,
    user_ext_id, 
    redeem_date, 
    // bonus amount calculated by formula in the user currency
    JSON_VALUE(bonus_meta,'$.bonusAmount') as bonus_amount
FROM dwh_ext_2283.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