◾Access to DWH

Access to DWH is paid service that should be requested separately.

Please contact your account manager to get more details.

Intro

Smartico provides access to the Data Warehouse (DWH) with row data related to the system operation. The DWH is based on Google BigQuery and exposes facts and dimensions tables.

There are mainly three domains of data exposed through the DWH:

CRM - information about campaigns, communication, conversions, automation rules etc
Gamification - missions, levels, tournaments, mini-games, points, store purchases
Affiliation - registration facts, financial transactions, payments, adjustments, affiliates information

Some of these domains have shared information, e.g. bonuses - can be given from the CRM and from Gamification. In the Affilication domain you will find also CRM data, so you can analyse all communication that is addressed to the affiliates.

Smartico DWH shouldn't be used to run analytical queries directly, as limitations apply to how much data can be retrieved daily.

Instead, you should do a batch load of changes to your analytical system and run reports on your side.

All fact tables are partitioned by date for your convenience so that you can load only the data delta for the completed day efficiently. We recommend running the import of the previous day a few hours after midnight in UTC to ensure that last-day data is fully delivered to the fact tables.

Pay attention to the fact that the data set 'dwh_ext_xxx' is given as an example. The dataset name for your particular setup is the same as the name of the user that you will get to access DWH.

Records in the fact tables are immutable (except cases when stated explicitly), which means they are not changing after they are added.

Fact tables are updated in real-time, and dimensional tables are updated once per hour.

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

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_type_id

int64

1 - Mail 2 - Popup 3 - SMS 4 - Push 5 - Inbox 9 - IVR

dwh_ext_xxx.dm_ach

Table contains information about missions and badges

Column

Type

Note

ach_id

int64

ID of mission/badge

internal_name

string

Name of mission/badge

dwh_ext_xxx.dm_ach_points_change_source

Lookup table with possible sources for the points transactions.

Column

Type

Note

source_type_id

int64

source_type_name

string

Name of the source

CRM fact tables

dwh_ext_xxx.j_communication

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

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: smarticoC.dm_activity_type)

engagement_uid

uuid

Reference to the smarticoC.j_engagements, represents the point in the Campaign/Flow from where the communication was triggered

resource_id

int64

FK: smarticoC.dm_resource, main resource

resource_variation_id

int64

FK: smarticoC.dm_resource, specific variation of the resource

root_audience_id

int64

FK: smarticoC.dm_audience, the campaign that triggered communication

root_engagement_uuid

uuid/string

Reference to the smarticoC.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

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: smarticoC.dm_com_fail_reason

user_ext_id

string

The ID of the user in the operator system

dwh_ext_xxx.j_engagements

The table represents the steps of how the user enters the campaign, exits it, converts, and goes through all the "activities" of the Flow defined in the campaign.

The table represents the recursive structure of user transition over the campaign graph.

For most of the use cases, you will need to analyze only records that have activity_type_id as one of

1 - represents the start of the campaign
2 - stop the campaign
3 - conversion of campaign

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

Partitioned by: create_date

Data availability: last 180 days by create_date

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: smarticoC.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: smarticoC.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

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

Gamification fact tables

dwh_ext_xxx.g_ach_completed

The table represents history of all completed missions & badges

Partitioned by: create_date

Data availability: last 180 days by create_date

Column

Type

Note

ach_id

INT64

ID of the mission/badge

create_date

TIMESTAMP

The time when mission/badge compelted

is_recurring

BOOLEAN

Indicate if the original mission was recurring

user_ext_id

STRING

External ID of the user

crm_brand_id

INT64

ID of the brand in the Smartico system

Example of query returning number of users completed missions in the last 3 days

select A.internal_name, A.ach_id, count(*) as completed_users_count
from dwh_ext_xxx.g_ach_completed M 
inner join dwh_ext_xxx.dm_ach A ON M.ach_id = A.ach_id 
WHERE M.create_date > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
GROUP BY A.internal_name, A.ach_id

dwh_ext_xxx.g_ach_points_change_log

The table represents all changes in the gamification points

Partitioned by: create_date

Data availability: last 180 days by create_date

Column

Type

Note

create_date

TIMESTAMP

The time of transaction

source_type_id

INT64

Type of of the source of the change, refer to dm_ach_points_change_source

source_entity_id

INT64

Id of the entity that triggered transaction, the lookup table depends on the source_type_id

points_collected

INT64

Amount of points in transaction, could be negative in case of deduction

user_points_ever

INT64

Amount of points user collected ever after the transaction

user_points_balance

INT64

Current points balance after transaction

user_ext_id

STRING

External ID of the user

crm_brand_id

INT64

ID of the brand in the Smartico system

Example of query showing the source of the points credit and deposit

select S.source_type_name, 
	sum( CASE WHEN points_collected > 0 THEN points_collected ELSE 0 END) as points_credit,
	sum( CASE WHEN points_collected < 0 THEN -1 * points_collected ELSE 0 END) as points_debit
from dwh_ext_12078.g_ach_points_change_log L 
inner join dwh_ext_12078.dm_ach_points_change_source S ON L.source_type_id = S.source_type_id 
WHERE L.create_date  > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
group by S.source_type_name

Affiliation dimensional & fact tables

Example of query that calculates total commission with split by components (CPA, CPL, RevShare) for all affiliates lifetime.

WITH X as (
  SELECT 
    A.affiliate_id, A.parent_affiliate_id,
    COALESCE(SUM(cpa_fee),0) as cpa_fee,
    COALESCE(SUM(cpl_fee),0) as cpl_fee,
    COALESCE(SUM(rev_share_fee),0) as rev_share_fee,
    COALESCE(SUM(sub_affiliate_fee),0) as sub_affiliate_fee
  FROM dwh_ext_XXX.aff_fin_history_daily F
  INNER JOIN dwh_ext_XXX.dm_affiliate A ON F.affiliate_id = A.affiliate_id
  WHERE operation_date > '2000-01-01 00:00:00 UTC'
  GROUP BY A.affiliate_id, A.parent_affiliate_id
),
SUB_SUMS as (
  SELECT parent_affiliate_id, SUM(X.sub_affiliate_fee) as sub_affiliate_fee
  FROM X
  GROUP BY parent_affiliate_id
  HAVING SUM(X.sub_affiliate_fee) > 0
),
FINAL as (
  select TOP_AFFS.* EXCEPT(sub_affiliate_fee, parent_affiliate_id), SUB_SUMS.sub_affiliate_fee, 
    TOP_AFFS.cpa_fee + TOP_AFFS.rev_share_fee + TOP_AFFS.cpl_fee + COALESCE(SUB_SUMS.sub_affiliate_fee,0) as total_commission
  from X as TOP_AFFS
  left join SUB_SUMS ON TOP_AFFS.affiliate_id = SUB_SUMS.parent_affiliate_id
)
SELECT * 
FROM FINAL
ORDER BY total_commission DESC

Notes:

Provided example of calculating of sub_affiliate_fee should be used only in the setup with one level of sub-affiliates network (when only direct parent of affiliate is getting attribution from the comission generated by his children). In case of multi-level affiliation network setup, the sub-affailites commissions should be calculated in the different way
These calculations are not taking into accounts adjustments that can be done in 3 differences cases:
1. Manual adjustments
2. Adjustments based on the "Tiers" based deal structures (RevShare tiers)
3. Adjustment done beginning of the month based on "No negative cary over" calculations

dwh_ext_xxx.dm_affiliate

The table represent information about affiliate

Column

Type

Note

affiliate_id

INT64

ID of the affiliate

create_date

TIMESTAMP

Date when affiliate is created

parent_affiliate_id

INT64

ID of the parent affiliate

manager_id

INT64

ID of the manager

payment_method_id

INT64

ID of payment method

ext_affiliate_id

STRING

ID of affiliate in the external system

dwh_ext_xxx.aff_fin_history_daily

The table represents daily aggregation of all financial data system collects about a player. Each column refers to some type of financial data and reflect the SUM of all numbers affilaite system have received for some "operation_date". To understand the final numbers for a player, you should SUM all records for a player, for example if you need to know the total deposit amount of a player, you should SUM all "deposits" + "first_deposit" columns for a player.

Partitioned by: operation_date

Table structure:

Column

Type

Note

ext_customer_id

STRING

Player ID the way it's reported to affiliate system

brand_id

INTEGER

Brand ID in affiliate system

country

STRING

ISO2 country code of the player

affiliate_id

INTEGER

Affiliate ID in case player belongs to one

registration_id

INTEGER

TAP registration id

operation_date

TIMESTAMP

financial day

update_date

TIMESTAMP

last update date

first_deposit

NUMERIC

first deposit amount

deposits

NUMERIC

deposits amount reported during operation_date

withdrawals

NUMERIC

withdrawals amount reported during operation_date

chargebacks

NUMERIC

chargebacks amount reported during operation_date

bonuses

NUMERIC

bonuses amount reported during operation_date

deductions

NUMERIC

deductions amount reported during operation_date

bets

NUMERIC

bets amount reported during operation_date

wins

NUMERIC

wins amount reported during operation_date

operations

NUMERIC

operations count reported during operation_date

ggr

NUMERIC

GGR amount reported during operation_date

cpl_fee

NUMERIC

CPL fee generated during operation_date

cpa_fee

NUMERIC

CPA fee generated during operation_date

rev_share_fee

NUMERIC

RevShare fee generated during operation_date

sub_affiliate_fee

NUMERIC

Total sub affiliate fee generated during operation_date for all affiliate levels

net_pnl

NUMERIC

NET PnL calculated during operation_date

deposit_count

NUMERIC

deposits count reported during operation_date

withdrawal_count

NUMERIC

withdrawals count reported during operation_date

q_cpa

NUMERIC

if value is 1, it means that at this operation_date player was qualified for CPA

q_cpl

NUMERIC

if value is 1, it means that at this operation_date player was qualified for CPL

FAQ

Can't access DWH from Power BI, error: Unable to authenticate with Google BigQuery Storage API. Check your account permissions.

You need to disable the usage of Storage API in Power BI settings following this guide - https://learn.microsoft.com/en-us/power-query/connectors/google-bigquery.

"Use Storage API" - A flag that enables using the Storage API of Google BigQuery. This option is true by default. This option can be set to false to not use the Storage API and use REST APIs instead.

Can't access table dwh_ext_xxx.j_engagements and any other

You need to replace "xxx" value with the label_id specific for your setup. You can also see the label_id in the name of BigQuery user that has the format like "dwh_ext_1234"

Can different labels and brands are access with the samw DWH account

Every label requires separate account to access DWH.

You could have many brands under one label setup, data related to all these labels will be availalbe from the repsective DWH account for this label

Last updated 1 year ago

Was this helpful?