# Smartico DWH \ CRM views ## Dimensional tables ### dwh\_ext\_xxx.dm\_j\_formula Definitions of formulas for Dynamic Rewards Primary key: **formula\_id** **Joins:** * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_bo_users) * bonus\_template\_id → [dm\_bonus\_template](#dwh_ext_xxx.dm_bonus_template) * event\_type\_id → [dm\_event\_type](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_event_type) * event\_type\_id2 → [dm\_event\_type](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_event_type) * event\_type\_id3 → [dm\_event\_type](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_event_type) | 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** **Joins:** * fail\_reason\_id → [j\_communication](#dwh_ext_xxx.j_communication) | 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** **Joins:** * segment\_id → [dm\_segment](#dwh_ext_xxx.dm_segment) * event\_type\_id → [dm\_event\_type](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_event_type) * audience\_id → [j\_engagements](#dwh_ext_xxx.j_engagements) * audience\_id → [j\_av](#dwh_ext_xxx.j_av) * audience\_id → [j\_bonuses](#dwh_ext_xxx.j_bonuses) * audience\_id → [j\_communication](#dwh_ext_xxx.j_communication) * audience\_id → [j\_webhooks\_facts](#dwh_ext_xxx.j_webhooks_facts) * audience\_id → [raf\_tickets](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-gamification-views#dwh_ext_xxx.raf_tickets) * audience\_id → [dm\_ach\_activity](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-gamification-views#dwh_ext_xxx.dm_ach_activity) | 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** **Joins:** * segment\_id → [dm\_segment](#dwh_ext_xxx.dm_segment) * event\_type\_id → [dm\_event\_type](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_event_type) * activity\_type\_id → [dm\_activity\_type](#dwh_ext_xxx.dm_activity_type) * rule\_id → [j\_automation\_rule\_progress](#dwh_ext_xxx.j_automation_rule_progress) | 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 | | segment\_id | INT64 | Target segment | | event\_type\_id | INT64 | Trigger event | | activity\_type\_id | INT64 | Activity executed when rule triggers | | once\_in\_a\_lifetime | BOOL | Whether the rule runs at most once per user | | active\_from\_utc | TIMESTAMP | Rule active from this time (UTC) | | active\_till\_utc | TIMESTAMP | Rule active until this time (UTC) | | start\_time | STRING | Daily start time (HH:mm) when rule is active | | end\_time | STRING | Daily end time (HH:mm) when rule is active | | accumulation\_property\_id | INT64 | Property used for accumulation threshold, null if accumulation disabled | | accumulation\_retention\_period\_ms | INT64 | Retention period for accumulated value in ms | | accumulation\_carry\_remaining | BOOL | Whether to carry remaining value to next accumulation period | | q\_enabled | BOOL | Whether qualification conditions are enabled | | q\_event\_type\_id | INT64 | Event type for qualification trigger | | q\_validity\_time\_ms | INT64 | Qualification validity period in ms | | q\_validity\_x\_times | INT64 | Max times rule can complete after qualification | | limit\_times\_count | INT64 | Max activities per user per limit period | | limit\_period\_type\_id | INT64 | Period type for activity limit: 1=from last attempt, 2=calendar days UTC, 3=calendar days user TZ, 4=lifetime | | limit\_period\_ms | INT64 | Limit period duration in ms | | limit\_period\_timezone | STRING | Timezone for calendar-based limit period | ### 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** **Joins:** * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_bo_users) * segment\_id → [dm\_automation\_rule](#dwh_ext_xxx.dm_automation_rule) * segment\_id → [dm\_audience](#dwh_ext_xxx.dm_audience) * segment\_id → [dm\_saw\_template](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-gamification-views#dwh_ext_xxx.dm_saw_template) * segment\_id → [dm\_tournament](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-gamification-views#dwh_ext_xxx.dm_tournament) * segment\_id → [dm\_tournament](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-gamification-views#dwh_ext_xxx.dm_tournament) * segment\_id → [dm\_ach\_custom\_sections](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-gamification-views#dwh_ext_xxx.dm_ach_custom_sections) | 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 ```sql 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 ```sql 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** **Joins:** * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_bo_users) * resource\_parent\_id → [dm\_resource](#dwh_ext_xxx.dm_resource) * resource\_id → [j\_communication](#dwh_ext_xxx.j_communication) * resource\_id → [j\_engagements](#dwh_ext_xxx.j_engagements) | 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** **Joins:** * activity\_type\_id → [dm\_ach\_activity](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-gamification-views#dwh_ext_xxx.dm_ach_activity) * activity\_type\_id → [dm\_automation\_rule](#dwh_ext_xxx.dm_automation_rule) * activity\_type\_id → [j\_automation\_rule\_progress](#dwh_ext_xxx.j_automation_rule_progress) * activity\_type\_id → [g\_tournament\_winners](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-gamification-views#dwh_ext_xxx.g_tournament_winners) * activity\_type\_id → [j\_communication](#dwh_ext_xxx.j_communication) * activity\_type\_id → [j\_engagements](#dwh_ext_xxx.j_engagements) | 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** **Joins:** * fail\_reason\_id → [j\_communication](#dwh_ext_xxx.j_communication) | 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** **Joins:** * rank\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) | 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** **Joins:** * label\_bonus\_template\_id → [dm\_ach\_activity](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-gamification-views#dwh_ext_xxx.dm_ach_activity) * label\_bonus\_template\_id → [j\_bonuses](#dwh_ext_xxx.j_bonuses) | 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** **Joins:** * id → [j\_communication](#dwh_ext_xxx.j_communication) | 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** **Joins:** * id → [j\_communication](#dwh_ext_xxx.j_communication) | 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** **Joins:** * category\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) | Column | Type | Description | | -------------- | --------- | ----------- | | create\_date | TIMESTAMP | | | update\_date | TIMESTAMP | | | category\_id | INT64 | | | category\_name | STRING | | ### dwh\_ext\_xxx.dm\_bnr\_placement Banner placement definitions. Primary key: **placement\_id** **Joins:** * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_bo_users) * placement\_id → [bnr\_click](#dwh_ext_xxx.bnr_click) * placement\_id → [bnr\_view](#dwh_ext_xxx.bnr_view) | Column | Type | Description | | --------------------- | --------- | ------------------------------------------- | | placement\_id | INT64 | Primary key | | label\_id | INT64 | Label identifier | | slug | STRING | Placement slug (unique per label) | | internal\_name | STRING | Internal name | | audience\_type\_id | INT64 | Audience: 1 - real users, 2 - visitors | | rotation\_period\_ms | INT64 | Rotation period. ms | | caching\_period\_ms | INT64 | Caching period, ms | | create\_date | TIMESTAMP | Created at | | update\_date | TIMESTAMP | Updated at | | create\_by | INT64 | Created by | | placement\_status\_id | INT64 | Status: 1 - draft, 2 - active, 3 - archived | ### dwh\_ext\_xxx.dm\_bnr\_banner Banner definitions. Primary key: **banner\_id** **Joins:** * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_bo_users) * banner\_id → [bnr\_click](#dwh_ext_xxx.bnr_click) * banner\_id → [bnr\_view](#dwh_ext_xxx.bnr_view) | Column | Type | Description | | ----------------------------- | --------- | ---------------------------------------- | | banner\_id | INT64 | Primary key | | internal\_name | STRING | Internal name | | banner\_type\_id | INT64 | Type: 1 - media, 2 - html, 3 - liquid | | label\_id | INT64 | Label identifier | | target\_value | STRING | Target value (e.g. URL) | | is\_active | BOOL | Is active | | is\_fallback | BOOL | Is fallback banner | | target\_srv\_segment\_id | INT64 | Target segment ID | | target\_srv\_conditions | JSON | Target conditions (JSON) | | target\_srv\_device\_type\_id | INT64 | Device: 1 - any, 2 - desktop, 3 - mobile | | target\_srv\_dt\_from | TIMESTAMP | Target date/time from | | target\_srv\_dt\_till | TIMESTAMP | Target date/time till | | cap\_view\_cnt | INT64 | View cap count | | cap\_view\_period\_value | INT64 | View cap period, ms | | cap\_click\_cnt | INT64 | Click cap count | | cap\_click\_period\_value | INT64 | Click cap period, ms | | create\_date | TIMESTAMP | Created at | | update\_date | TIMESTAMP | Updated at | | create\_by | INT64 | Created by | | target\_srv\_weekdays | JSON | Target weekdays (array as JSON) | | target\_srv\_start\_time | STRING | Target start time of day | | target\_srv\_end\_time | STRING | Target end time of day | ## Fact tables ### dwh\_ext\_xxx.r\_churn\_predictions The table contains information about players' churn and lifetime value predictions and the times of the predictions update. The predictions are updated daily and consider the most recent user's activity as well as the user's profile data. The Churn ratio is the probability of the user to deposit in the next 30 days. The Churn Score model scores every user daily from 0 (low risk) to 1 (high risk) based on whether they will deposit again within 30 days. The Value Prediction model calculates the user's value over time based on 52 different data points, and using the machine learning pipeline. Primary key: **user\_id** Partitioned by: **none** Data availability: **all time** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_brand) * user\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) | Column | Type | Description | | -------------- | --------- | ----------------------------------------------------- | | crm\_brand\_id | INT64 | Brand ID | | user\_id | INT64 | Smartico user ID | | churn\_ratio | FLOAT64 | Current churn ratio | | ltv\_0 | FLOAT64 | User's Value prediction 30 days from today | | ltv\_1 | FLOAT64 | User's Value prediction 60 days from the current date | | ltv\_2 | FLOAT64 | User's Value prediction 90 days from the current date | | ltv\_current | FLOAT64 | User's Value prediction at current date | | ts | TIMESTAMP | Date and time when the predictions were made | Returns the most updated churn score and value predictions for a given brand (1) and user (1) ```sql SELECT * FROM dwh_ext_XXX.r_churn_predictions WHERE crm_brand_id = 1 AND user_id = 1 ORDER BY ts DESC LIMIT 1 ``` ### 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** **Joins:** * user\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) * resource\_id → [dm\_resource](#dwh_ext_xxx.dm_resource) * audience\_id → [dm\_audience](#dwh_ext_xxx.dm_audience) | 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) ```sql 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 ``` ```sql 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 * While a single Engagement can legitimately trigger multiple different communications (e.g., a Popup and an Email), technical issues at the provider level can occasionally cause duplicate records for the same individual message.Therefore you have to always use a QUALIFY or DISTINCT logic on the communication\_id (or engagement\_id) to ensure your model doesn't double-count technical retries as separate marketing actions. Primary key: **engagement\_id + fact\_type\_id** Partitioned by: **fact\_date** Data availability: **last 1080 days by fact\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_brand) * user\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) * root\_audience\_id → [dm\_audience](#dwh_ext_xxx.dm_audience) * resource\_id → [dm\_resource](#dwh_ext_xxx.dm_resource) * activity\_type\_id → [dm\_activity\_type](#dwh_ext_xxx.dm_activity_type) * fail\_reason\_id → [dm\_com\_fail\_reason](#dwh_ext_xxx.dm_com_fail_reason) * label\_provider\_id → [dm\_providers\_sms](#dwh_ext_xxx.dm_providers_sms) * label\_provider\_id → [dm\_providers\_mail](#dwh_ext_xxx.dm_providers_mail) | 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 | | push\_client\_platform\_ids | RECORD | List of the push client platforms IDs for the Push type of communication (activity\_type\_id = 40). Possible values: CHROME\_DESKTOP = 0, FIREFOX\_DESKTOP = 1, EDGE\_DESKTOP = 2, OPERA\_DESKTOP = 3, SAFARI\_DESKTOP = 4, OTHER = 5, NATIVE\_IOS = 6, NATIVE\_ANDROID = 7, CHROME\_MOBILE = 8, FIREFOX\_MOBILE = 9, SAFARI\_MOBILE = 10, OPERA\_MOBILE = 11 | Returns counts of communication by status and provider for the past 7 days ```sql 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 ```sql 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** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_brand) * user\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) * automation\_rule\_id → [dm\_automation\_rule](#dwh_ext_xxx.dm_automation_rule) * activity\_type\_id → [dm\_activity\_type](#dwh_ext_xxx.dm_activity_type) | 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 for an explanation. Important! While a single Engagement can legitimately trigger multiple different communications (e.g., a Popup and an Email), technical issues at the provider level can occasionally cause duplicate records for the same individual message.Therefore you have to always use a QUALIFY or DISTINCT logic on the communication\_id (or engagement\_id) to ensure your model doesn't double-count technical retries as separate marketing actions. Primary key: **engagement\_id or engagement\_uid** Partitioned by: **create\_date** Data availability: **last 1080 days by create\_date** **Joins:** * root\_audience\_id → [dm\_audience](#dwh_ext_xxx.dm_audience) * activity\_type\_id → [dm\_activity\_type](#dwh_ext_xxx.dm_activity_type) * user\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_brand) * resource\_id → [dm\_resource](#dwh_ext_xxx.dm_resource) * event\_type\_id → [dm\_event\_type](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_event_type) * engagement\_uid → [j\_bonuses](#dwh_ext_xxx.j_bonuses) * engagement\_uid → [j\_communication](#dwh_ext_xxx.j_communication) * activity\_id → [dm\_funnel\_marker](#dwh_ext_xxx.dm_funnel_marker) | 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. Note that NULL value means that the user is not in the control group. | | 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 ```sql 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 + fact\_date** Partitioned by: **fact\_date** Data availability: **last 1080 days by fact\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_brand) * user\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) * label\_bonus\_template\_id → [dm\_bonus\_template](#dwh_ext_xxx.dm_bonus_template) * source\_product\_id → [dm\_product](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_product) * root\_audience\_id → [dm\_audience](#dwh_ext_xxx.dm_audience) * engagement\_uid → [j\_engagements](#dwh_ext_xxx.j_engagements) | 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 ```sql 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 ```sql 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 ```sql 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** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_brand) * user\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) * root\_audience\_id → [dm\_audience](#dwh_ext_xxx.dm_audience) * source\_product\_id → [dm\_product](https://help.smartico.ai/welcome/technical-guides/smartico-dwh-system-views#dwh_ext_xxx.dm_product) | 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** **Joins:** * user\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) | 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 ```sql 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 ``` ### dwh\_ext\_xxx.bnr\_view Represents banner view (impression) events. Partitioned by: view\_date, query to the table should always use view\_date in the WHERE clause. Primary key: **view\_uuid** Partitioned by: **view\_date** Data availability: **last 1080 days by view\_date** **Joins:** * user\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) * placement\_id → [dm\_bnr\_placement](#dwh_ext_xxx.dm_bnr_placement) * banner\_id → [dm\_bnr\_banner](#dwh_ext_xxx.dm_bnr_banner) | Column | Type | Description | | --------------------- | --------- | ----------------------------------------------- | | view\_date | TIMESTAMP | When the banner was shown | | label\_id | INT64 | Label ID | | user\_id | INT64 | User ID (j\_user\_no\_enums.user\_id) | | placement\_id | INT64 | Placement ID (dm\_bnr\_placement.placement\_id) | | banner\_id | INT64 | Banner ID (dm\_bnr\_banner.banner\_id) | | view\_uuid | STRING | Unique UUID of the view event | | cli\_ip | STRING | Client IP address | | cli\_device\_type\_id | INT64 | Device type: 1 - any, 2 - desktop, 3 - mobile | | cli\_os\_type\_id | INT64 | OS type | ### dwh\_ext\_xxx.bnr\_click Represents banner click events. Partitioned by: click\_date, query to the table should always use click\_date in the WHERE clause. Primary key: **view\_uuid** Partitioned by: **click\_date** Data availability: **last 1080 days by click\_date** **Joins:** * placement\_id → [dm\_bnr\_placement](#dwh_ext_xxx.dm_bnr_placement) * banner\_id → [dm\_bnr\_banner](#dwh_ext_xxx.dm_bnr_banner) * user\_id → [j\_user\_no\_enums](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/..#query-for-user-profile-details) | Column | Type | Description | | --------------------- | --------- | ----------------------------------------------------- | | click\_date | TIMESTAMP | When the banner was clicked | | view\_date | TIMESTAMP | Original view timestamp (for measuring time to click) | | label\_id | INT64 | Label ID | | user\_id | INT64 | User ID (j\_user\_no\_enums.user\_id) | | placement\_id | INT64 | Placement ID (dm\_bnr\_placement.placement\_id) | | banner\_id | INT64 | Banner ID (dm\_bnr\_banner.banner\_id) | | view\_uuid | STRING | Original view UUID (join key to bnr\_view) | | cli\_ip | STRING | Client IP address | | cli\_device\_type\_id | INT64 | Device type: 1 - any, 2 - desktop, 3 - mobile | | cli\_os\_type\_id | INT64 | OS type | | target\_value | STRING | URL clicked |