# Smartico Data Warеhouse

{% hint style="info" %}
Access to Smartico DWH is a paid service that should be requested separately.

Please contact your Customer Success Manager to get more details.
{% endhint %}

## Intro

Smartico provides access to the Data Warehouse (DWH), which contains row-level data related to system operations. The DWH is based on Google BigQuery and exposes fact and dimension 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, and affiliates information
* **Transactional tables** - these are tables that start with "tr\_". They contain most of the data from the integrated system (PAM) and many other Smartico events. Read more about transaction tables in the section below.

Some of these domains share information; e.g., bonuses can be given from the CRM and from Gamification.

{% hint style="warning" %} <mark style="color:red;">**IMPORTANT: Ignoring the suggestions below may result in high BigQuery read usage and higher charges.**</mark>

Smartico DWH <mark style="color:red;">**SHOULD NOT**</mark> be used to run analytical queries directly, as limitations apply to how much data can be retrieved daily.

Instead, you should perform a batch load of changes to your analytical system and run reports locally.

All fact tables are partitioned by date for your convenience, so you can efficiently load only the data delta for the completed day. 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.

Don't apply functions on the partition columns when filtering with the 'WHERE' clause. The correct example of taking data for the last day is - 'WHERE fact\_date >= TIMESTAMP\_TRUNC(TIMESTAMP\_SUB(CURRENT\_TIMESTAMP(), INTERVAL 1 DAY), DAY)'

Note that the dataset 'dwh\_ext\_xxx' is provided as an example. The dataset name for your setup is the same as the user name you will have access to the DWH under.\ <br>
{% endhint %}

Records in the fact tables are immutable (except when explicitly stated otherwise), meaning they do not change after they are added.

Fact tables are updated in **real time, while** dimensional tables are updated once per **hour**.

{% hint style="danger" %}
Avoid using "select \* from table"!. Select only the needed columns. There are two reasons for this:

* The cost of the query highly depends on the number of columns you are selecting
* Smartico is adding new columns as the product evolves, so your ETL process should be agnostic to the addition of new columns.
  {% endhint %}

**For the details of the fact & dimensions tables, please usethe following links:**

{% content-ref url="smartico-data-warehouse/smartico-dwh-affiliation-views" %}
[smartico-dwh-affiliation-views](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-affiliation-views)
{% endcontent-ref %}

{% content-ref url="smartico-data-warehouse/smartico-dwh-crm-views" %}
[smartico-dwh-crm-views](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-crm-views)
{% endcontent-ref %}

{% content-ref url="smartico-data-warehouse/smartico-dwh-gamification-views" %}
[smartico-dwh-gamification-views](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-gamification-views)
{% endcontent-ref %}

Use our "Data Expert" AI Agent in the Smartico BackOffice to explore data structures and view SQL queries to understand the relationships between tables. Data Exper is using the same DWH dataset; this means that the query you copy from the chat will work on the DWH as well.

<div data-with-frame="true"><figure><img src="https://77049817-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FfS5hl0PiysHtKAKMsQTe%2Fuploads%2Fgit-blob-d6812615c8868fa953c8cd4d2007814083115f13%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure></div>

## Segments export

You can query the DWH to get a near-real-time list of users in specific segments.

To make a segment available for export, it must first be marked as exportable in the DWH in Smartico BackOffice, as shown in the screen below.

<div data-with-frame="true"><figure><img src="https://77049817-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FfS5hl0PiysHtKAKMsQTe%2Fuploads%2Fgit-blob-8831dd8ddffc15cd06fecf635de7b13c08f09e68%2FImage%202025-09-30%2010-51-26.png?alt=media" alt=""><figcaption></figcaption></figure></div>

After that, segment content can be queried using the following SQL, where XXX needs to be replaced with the ID of your label and YYY with the ID of the segment.

```sql

select * from dwh_ext_XXX.fn_export_segment_YYY()

```

Example of query results

<figure><img src="https://77049817-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FfS5hl0PiysHtKAKMsQTe%2Fuploads%2Fgit-blob-66950841aae3c994428a6ec7cafd368cd219a938%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

{% hint style="info" %}

### Check for Segment belonging from the JavaScript on the front-end

We have a front-end JavaScript API to verify that the user belongs to a specific segment. You can check for more details [here](https://help.smartico.ai/welcome/front-end-integration/extended-integration#check-if-the-user-belongs-to-a-specific-segment) and in the [Public API documentation](https://github.com/smarticoai/public-api/blob/main/docs/classes/WSAPI.md#checksegmentmatch)
{% endhint %}

## Query for user profile details

You can query DWH to get detailed information about users stored on the Smartico side.

```sql

SELECT user_id, user_ext_id, core_registration_date, core_wallet_currency, core_user_language, core_tags, core_public_tags, core_external_markers
FROM dwh_ext_2283.j_user
where ARRAY_LENGTH(core_tags) > 0 and core_registration_date is not null

```

Note that only a small subset of user profile properties is exposed through the DWH, and only those shared by all clients. If you need to expose a specific property, please contacthave a need to expose a specific property, please get in touch with Smartico support.

Currently exposed properties of the **j\_user** view

<table><thead><tr><th width="257">Property</th><th width="159">Type</th><th>Meaning</th></tr></thead><tbody><tr><td>user_id</td><td>INT64</td><td>ID of the user in Smartico system</td></tr><tr><td>user_ext_id</td><td>STRING</td><td>ID of the user in the integrated platform</td></tr><tr><td>core_registration_date</td><td>TIMESTAMP</td><td>Date/time of registration</td></tr><tr><td>core_user_last_time_online</td><td>TIMESTAMP</td><td>Date/time when user was last time online</td></tr><tr><td>ach_level_current_id</td><td>INT64</td><td>ID of the current gamification level</td></tr><tr><td>ach_points_ever</td><td>INT64</td><td>Amount of points user collected ever</td></tr><tr><td>ach_points_balance</td><td>INT64</td><td>Current points balance of user</td></tr><tr><td>ach_diamonds_balance</td><td>INT64</td><td>Current <a href="../products/gamification-basics/points">diamonds</a> balance of user</td></tr><tr><td>ach_gems_balance</td><td>INT64</td><td>Current <a href="../products/gamification-basics/points">gems</a> balance of user</td></tr><tr><td>user_country</td><td>STRING</td><td>Country</td></tr><tr><td>core_user_language</td><td>STRING</td><td>Language, ISO code</td></tr><tr><td>core_user_last_device_type</td><td>STRING</td><td>Last used device type (Desktop, Mobile, Native, Wrapper)</td></tr><tr><td>core_wallet_currency</td><td>STRING</td><td>Currency code</td></tr><tr><td>core_tags</td><td>STRING[]</td><td>Array of <a href="../products/general-concepts/user-markers-tags">user markers</a></td></tr><tr><td>core_public_tags</td><td>STRING[]</td><td>Array of <a href="../products/general-concepts/user-markers-tags">public user marker</a></td></tr><tr><td>core_external_markers</td><td>STRING[]</td><td>Array of <a href="../products/general-concepts/user-markers-tags">external user markers</a></td></tr><tr><td>core_external_segment</td><td>STRING[]</td><td>An array of external segment names, populated by the integrated platform.<br>Behaviour is similar to <a href="../products/general-concepts/user-markers-tags">external user markers</a></td></tr><tr><td>core_rfm_segment</td><td>INT64</td><td>1 - Champions<br>2 - Loyal<br>3 - Potential Loyalist<br>4 - Promising<br>5 - New Customers<br>6 - Need Attention<br>7 - About To Sleep<br>8 - Hibernating Customers<br>9 - At Risk<br>10 - Losing But Engaged<br>11 - About To Churn<br>12 - Churned By Definition</td></tr><tr><td>core_utm_campaign</td><td>STRING</td><td>UTM campaign of user as reported from the integrated Platform</td></tr><tr><td>core_utm_medium</td><td>STRING</td><td>UTM medium of user as reported from the integrated Platform</td></tr><tr><td>core_utm_source</td><td>STRING</td><td>UTM source of user as reported from the integrated Platform</td></tr><tr><td>core_ai_player_class_id</td><td>INT64</td><td>Player class value evaluated by LTV model<br>1 - Low<br>2 - Medium<br>3 - VIP</td></tr><tr><td>core_ai_churn_rank</td><td>INT64</td><td>Rank of player for Churn model<br>1 - Low<br>2 - Medium<br>3 - High<br>4 - Critical<br>5 - Churned<br><br>Special value "-1" that has the meaning of "Invalidated", assigned to user in case he was churned or critical on the last evaluation, but did a deposit or bet, so his churn rank is not valid anymore and will be recalculated on the next iteration (by default daily)</td></tr><tr><td>core_inbox_unread_count</td><td>INT64</td><td>Number of unread inbox messages</td></tr><tr><td>core_is_test_account</td><td>BOOL</td><td>Is account indicated as test account</td></tr><tr><td>sms_last_clicked_date</td><td>TIMESTAMP</td><td>Date when user clicked on sms link last time</td></tr><tr><td>mail_last_clicked_date</td><td>TIMESTAMP</td><td>Date when user clicked on mail link last time</td></tr><tr><td>push_last_clicked_date</td><td>TIMESTAMP</td><td>Date when user clicked on push notification last time</td></tr><tr><td>last_marketing_popup_sent_date</td><td>TIMESTAMP</td><td>Date when last marketing popup was sent</td></tr><tr><td>last_marketing_push_sent_date</td><td>TIMESTAMP</td><td>Date when last marketing push was sent</td></tr><tr><td>last_marketing_email_sent_date</td><td>TIMESTAMP</td><td>Date when last marketing email was sent</td></tr><tr><td>last_marketing_sms_sent_date</td><td>TIMESTAMP</td><td>Date when last marketing SMS was sent</td></tr><tr><td>core_affiliate_id</td><td>INT64</td><td>ID of affiliate reported by PAM (in case reported as integer value)</td></tr><tr><td>core_affiliate_str</td><td>STRING</td><td>ID of affiliate reported by PAM (in case reported as string value)</td></tr><tr><td>user_email_status</td><td>STRING</td><td>Mail status of user, as explained <a href="../../products/crm-automation/communication-channels/opt-out-and-communication-statuses#email-status">here</a></td></tr><tr><td>core_account_status</td><td>STRING</td><td>Primary account status within the Smartico system. It plays a critical role in determining a user's eligibility for marketing activities. If an account's status is not set to ACTIVE, the system will not send any communications (like emails, SMS, or push notifications) to that user and won't issue the bonuses.<br>This behavior can be adjusted in the label settings.<br><br>The default status for any new account is ACTIVE. Other possible statuses include Blocked, Suspended, Deactivated, etc.</td></tr><tr><td>core_external_account_status</td><td>STRING</td><td>This property stores the account status as defined in your external system or Player Account Management (PAM) platform. It allows you to mirror the account status from your system within Smartico for segmentation and reporting.</td></tr><tr><td>core_email_valid_by_pam</td><td>BOOL</td><td>Email was validated on the PAM side</td></tr><tr><td>core_email_confirmed</td><td>BOOL</td><td>A boolean flag (true/false) that indicates whether a user's email address has been verified or confirmed on your platform (PAM). This status is managed and populated from your system and can be used in Smartico for segmentation</td></tr><tr><td>core_phone_confirmed</td><td>BOOL</td><td>Similar to email confirmation, this is a boolean flag that indicates if a user's phone number has been verified or confirmed on your platform (PAM)</td></tr><tr><td>core_is_sms_disabled_by_platform</td><td>BOOL</td><td>The user has opted out of receiving SMS messages via your platform's flow</td></tr><tr><td>core_is_sms_disabled</td><td>BOOL</td><td>The user has opted out of SMS messages using a Smartico opt-out link</td></tr><tr><td>core_is_push_disabled_by_platform</td><td>BOOL</td><td>The user has opted out of receiving push notifications via your platform's flow</td></tr><tr><td>core_is_push_disabled</td><td>BOOL</td><td>The user has opted out of Push messages using a Smartico opt-out link</td></tr><tr><td>core_is_ivr_disabled</td><td>BOOL</td><td>The user has opted out of receiving IVR (Interactive Voice Response) calls via your platform's flow</td></tr><tr><td>core_is_email_disabled_by_platform</td><td>BOOL</td><td>The user has opted out of receiving emails via your platform's flow</td></tr><tr><td>core_is_email_disabled</td><td>BOOL</td><td>The user has opted out of emails using a Smartico unsubscribe link</td></tr><tr><td>core_custom_propX</td><td>STRING</td><td>Custom string based properties, where X is 1,2,3...</td></tr><tr><td>core_custom_prop_dtX</td><td>TIMESTAMP</td><td>Custom date/time based properties, where X is 1,2,3...</td></tr><tr><td>core_custom_prop_numX</td><td>NUMERIC</td><td>Custom numeric based properties, where X is 1,2,3...</td></tr><tr><td>acc_last_deposit_date</td><td>TIMESTAMP</td><td>Date/time of the user's last approved deposit</td></tr><tr><td>core_value_score_30d</td><td>INT64</td><td>1 - Low<br>2 - Medium<br>3 - High<br>4 - Top</td></tr><tr><td>core_value_score_lt</td><td>INT64</td><td>1 - Low<br>2 - Medium<br>3 - High<br>4 - Top</td></tr><tr><td>in_manual_upload_segments</td><td>INT64[]</td><td>Array of segments IDs for the CSV-based segments and for "Common cases" segments</td></tr><tr><td>behaviour_ids</td><td>INT64[]</td><td>Array of segments IDs for the Behavioural segments</td></tr><tr><td>core_user_gender</td><td>STRING</td><td>User gender</td></tr><tr><td>core_recommended_casino_bet_amount</td><td>NUMERIC</td><td>Recommended bet amount, check <a href="../../more/release-notes/december-2025#recommended-bet-and-deposit-amounts">release notes</a> for details.</td></tr><tr><td>core_recommended_deposit_amount</td><td>NUMERIC</td><td>Recommended deposit amount, check <a href="../../more/release-notes/december-2025#recommended-bet-and-deposit-amounts">release notes</a> for details.</td></tr><tr><td>core_is_visitor</td><td>BOOL</td><td>Indicator if the record represents the visitor</td></tr><tr><td>core_fav_game_top3</td><td>INT64[]</td><td>Top 3 game <strong>names</strong> for the player, calculated if the <a href="../products/ai-models/favorite-product-game-game-type-and-game-provider">Favorites games</a> feature is enabled. FK to the dm_casino_game_name table</td></tr><tr><td>core_fav_game_type_top3</td><td>INT64[]</td><td>Top 3 game <strong>types/categories</strong> for the player, calculated if the <a href="../products/ai-models/favorite-product-game-game-type-and-game-provider">Favorites games</a> feature is enabled. FK to the dm_casino_game_type table</td></tr><tr><td>core_fav_game_provider_top3</td><td>INT64[]</td><td>Top 3 game <strong>providers</strong> for the player, calculated if the <a href="../products/ai-models/favorite-product-game-game-type-and-game-provider">Favorites games</a> feature is enabled. FK to the dm_casino_provider_name table</td></tr><tr><td>core_fav_product_type</td><td>INT64</td><td>Calculated general preference of the player between different types of products:<br>1 - Casino only<br>2 - Sport only<br>3 - Lottery only<br>4 - More Casino, Less Sport<br>5 - Casino and Sport<br>6 - More Sport, Less Casino<br>7 - Mixed preferences</td></tr><tr><td>core_fav_product_sport_p</td><td>INT64</td><td>Percentage-based preference level of the user in the sport, e.g., 90% means that most of the recent bets were on the sport, and 10% on other products</td></tr><tr><td>core_fav_product_lottery_p</td><td>INT64</td><td>Percentage based preference level of the user in the lottery</td></tr><tr><td>core_fav_product_casino_p</td><td>INT64</td><td>Percentage based preference level of the user in the casino</td></tr></tbody></table>

Examples

> Note: the table dwh\_ext\_xxx.**j\_user** has enumeration based fields mapped to the string presentations, for example core\_account\_status has type string.
>
> In addition we are providing dwh\_ext.xxx.**j\_user\_no\_enums** table that has the same set of columns, but enumeration based fields are presented as INT64 or INT64\[], and holding internal smartico specific IDs.

**How to get a list of all users with respective IDs and names of the CSV, Common Cases, and Behavioral segments**

```sql
-- Standard SQL
WITH seg AS (
  SELECT segment_id, segment_name
  FROM dwh_ext_2283.dm_segment
),

-- Build an array of names for in_manual_upload_segments
manual AS (
  SELECT
    u.user_ext_id,
    ARRAY_AGG(
      DISTINCT s.segment_name
      IGNORE NULLS
      ORDER BY s.segment_name
    ) AS in_manual_upload_segment_names
  FROM dwh_ext_2283.j_user u
  LEFT JOIN UNNEST(u.in_manual_upload_segments) AS seg_id
  LEFT JOIN seg s
    ON s.segment_id = seg_id
  GROUP BY u.user_ext_id
),

-- Build an array of names for behaviour_ids
behaviour AS (
  SELECT
    u.user_ext_id,
    ARRAY_AGG(
      DISTINCT s.segment_name
      IGNORE NULLS
      ORDER BY s.segment_name
    ) AS behaviour_segment_names
  FROM dwh_ext_2283.j_user u
  LEFT JOIN UNNEST(u.behaviour_ids) AS seg_id
  LEFT JOIN seg s
    ON s.segment_id = seg_id
  GROUP BY u.user_ext_id
)

SELECT
  u.user_ext_id,
  m.in_manual_upload_segment_names,
  b.behaviour_segment_names,
  COALESCE(m.in_manual_upload_segment_names, []) AS in_manual_upload_segment_names,
  COALESCE(b.behaviour_segment_names, [])         AS behaviour_segment_names
FROM dwh_ext_2283.j_user u
LEFT JOIN manual    m USING (user_ext_id)
LEFT JOIN behaviour b USING (user_ext_id);

```

### Additional views <a href="#table-smarticocdm_resource" id="table-smarticocdm_resource"></a>

#### dwh\_ext\_xxx.dm\_brand <a href="#table-smarticocdm_resource" id="table-smarticocdm_resource"></a>

Represents brands defined under your label

| Column           | Type   | Meaning                            |
| ---------------- | ------ | ---------------------------------- |
| crm\_brand\_id   | INT64  | PK, ID of brand in Smartico system |
| crm\_brand\_name | STRING | Name of brand                      |
| ext\_brand\_id   | STRING | ID of brand in external system     |

You will find more views on this page:

{% content-ref url="smartico-data-warehouse/smartico-dwh-crm-views" %}
[smartico-dwh-crm-views](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-crm-views)
{% endcontent-ref %}

{% content-ref url="smartico-data-warehouse/smartico-dwh-gamification-views" %}
[smartico-dwh-gamification-views](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-gamification-views)
{% endcontent-ref %}

## Transactional tables

Smartico DWH exposes many transactional tables that represent events occurring outside Smartico (in the PAM) or events created by Smartico logic.

These tables use the naming convention "**tr\_some\_event\_name**"; for example, the table "**tr\_acc\_deposit\_approved**" contains all deposits reported from the integrated platform (PAM).

The transactional tables are updated in near-real time, with a sub-second delay possible.

Here is an example of the most frequently used tables; the names of tables fully correspond to the original name of the event, e.g., table "**tr\_acc\_deposit\_approved**" corresponds to the event "**acc\_deposit\_approved**". You may also notice that these tables/events are used in the calculation of [Dynamic rewards](https://help.smartico.ai/welcome/products/general-concepts/dynamic-rewards) and in the [Behavioral segmentation](https://help.smartico.ai/welcome/products/crm-automation/segmentation/behavioral-segments)

**Payments & Wallet (from PAM)**

* tr\_acc\_deposit\_approved
* tr\_acc\_deposit\_failed
* tr\_acc\_withdrawal\_approved
* tr\_acc\_withdrawal\_requested
* tr\_acc\_withdrawal\_completed
* tr\_acc\_withdrawal\_cancelled

**Casino Activity (from PAM)**

* tr\_casino\_bet
* tr\_casino\_win
* tr\_casino\_bet\_win

**Sports Betting (from PAM)**

* tr\_sport\_bet\_open
* tr\_sport\_bet\_settled
* tr\_sport\_bet\_selection\_open
* tr\_sport\_bet\_selection\_settled

**Bonuses (from PAM)**

* tr\_acc\_bonus\_approved
* tr\_acc\_bonus\_cancelled
* tr\_acc\_bonus\_failed
* tr\_acc\_bonus\_given

**Bonuses (by Smartico)**

* tr\_core\_bonus\_given
* tr\_core\_bonus\_failed

**Dynamic rewards (by Smartico)**

* tr\_core\_dynamic\_bonus\_issued
* tr\_core\_dynamic\_bonus\_calculated

**Gamification: Points, Gems, Diamonds (by Smartico)**

* tr\_ach\_points\_added
* tr\_ach\_points\_deducted
* tr\_ach\_gems\_added
* tr\_ach\_gems\_deducted
* tr\_ach\_diamonds\_added
* tr\_ach\_diamonds\_deducted

**Gamification: Levels (by Smartico)**

* tr\_ach\_level\_changed

**Gamification: Missions (by Smartico)**

* tr\_ach\_achievement\_completed

**Gamification: Store (by Smartico)**

* tr\_shop\_item\_purchase\_successed

**Gamification: Minigames (by Smartico)**

* tr\_minigame\_attempt
* tr\_minigame\_win
* tr\_minigame\_spins\_issued

**Gamification: Tournaments (by Smartico)**

* tr\_tournament\_user\_registered
* tr\_tournament\_win
* tr\_tournament\_lose

**Gamification: Raffles & Jackpots (by Smartico)**

* tr\_raffle\_tickets\_given
* tr\_raffle\_draw\_won
* tr\_raffle\_draw\_run\_opt\_in
* tr\_raffle\_prize\_claimed
* tr\_jackpot\_opt\_in
* tr\_jackpot\_user\_win

**Core System & User Actions (by Smartico)**

* tr\_login
* tr\_client\_action
* tr\_gs\_game\_sesssion\_changed
* tr\_gs\_game\_sesssion\_ended

{% hint style="info" %}
You may not see the specific tables until the first transaction is recorded into them.

For example, the "**tr\_code\_dynamic\_bonus\_issued**" table will not be present in your DWH until at least one [dynamic reward](https://help.smartico.ai/welcome/products/general-concepts/dynamic-rewards) has been calculated and issued for a user.

**Important:** the data in the transactional is partitioned by the "**event\_time**" field. Please be sure you are ALWAYS using it in queries when retrieving data; skipping this requirement will impact the amount of data read and affect billing.
{% endhint %}

## Facts records deduplication

Note that in rare cases, some fact tables may contain duplicate records. This may occur in very exceptional cases and doesn't affect the system's operational layer. For example, if a record for the "mail sent" fact is duplicated, the mail wasn't sent twice; only the "mail sent" fact was duplicated.

You can perform deduplication using the "PK" field in the corresponding table.

## How to connect to the Google BigQuery SQL

Smartico leverages Google BigQuery to deliver robust, scalable data analytics capabilities. As part of our service, we provide a secure JSON key file that enables programmatic access to our DWH. This section outlines how to use the provided JSON key to connect to BigQuery, depending on the Business Intelligence (BI) tool you choose.

### Overview of JSON Key Usage

A JSON key file is used as a credential for authentication. It allows secure access to Google BigQuery without needing user-specific Google credentials. The JSON key file you receive will be associated with a service account created and managed by our company that has been granted access to the required BigQuery resources.

### Important Security Note

Please store your JSON key file securely and ensure it is accessible only to those who need it. Please do not share the JSON key publicly or with unauthorized users, as it provides direct access to our Data Warehouse.

### Connecting to BigQuery Using Various BI Tools

Different BI tools use different methods to set up connections to BigQuery using a JSON key. Below, we offer guidance on connecting some popular BI tools to our DWH. For detailed instructions, refer to the specific documentation for the BI tool you are using.

{% hint style="warning" %}
Note: You shouldn't run analytical queries directly on Smartico DWH, as it may incur high costs. The proper way is to build a pipeline that loads daily data in your internal DWH and build reports on top of it.
{% endhint %}

#### Google Data Studio

* **Connection Type:** Direct integration with Google services.
* **Key Usage:** Typically, Data Studio uses OAuth 2.0 for authentication, which does not require a service account JSON key when using a Google account with appropriate permissions.

#### Tableau

* **Connection Type:** Direct connection using a service account.
* **Key Usage:** Upload your JSON key file during Tableau BigQuery connection setup.

#### Looker

* **Connection Type:** Direct connection using a service account.
* **Key Usage:** Paste the contents of the JSON key file into the appropriate field when configuring the BigQuery connection in Looker.

#### Microsoft Power BI

* **Connection Type:** Connect using ODBC drivers.
* **Key Usage:** Use the Simba ODBC Driver for Google BigQuery and specify the path to your JSON key file in the DSN configuration.

#### Qlik Sense

* **Connection Type:** Connect using ODBC drivers.
* **Key Usage:** Configure a System DSN using the ODBC Data Source Administrator on Windows, including the JSON key file.<br>

## FAQ

#### How to analyze DWH usage

You can find two reports related to DWH usage in the Reports \ DWH usage section of Smartico BackOffice.

They are presented as two tabs.

Usage of DWH over the last 3 months.

<div data-with-frame="true"><figure><img src="https://77049817-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FfS5hl0PiysHtKAKMsQTe%2Fuploads%2Fgit-blob-1daa86c0e89eb736e529aae2b63bf019c566deee%2FMAU%20-%20smartico.ai%20%40%20EU2%202026-01-06%2022-01-44.png?alt=media" alt=""><figcaption></figcaption></figure></div>

Queries log for the last 24 hours - shows you the last 24 hours - will show you exact requests completed in the previous 24 hours and the amount of data read for each.

<div data-with-frame="true"><figure><img src="https://77049817-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FfS5hl0PiysHtKAKMsQTe%2Fuploads%2Fgit-blob-bbf9a40b837af864522f0507123430868e2fe69f%2FMAU%20-%20smartico.ai%20%40%20EU2%202026-01-06%2022-04-27.png?alt=media" alt=""><figcaption></figcaption></figure></div>

#### Why is the usage report showing much higher numbers of gigabytes than the data we are getting from DWH?

DWH usage is based on "data read," not "**data transferred"**.

You may build a query that returns only one record, but behind the scenes, it may still read much more data. For example, "SELECT count(\*) FROM some\_table" will return only one record, but still needs to scan the entire underlying table.

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

You need to disable the 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.

#### How to authenticate in Power BI with the service account

Use the following guide from Microsoft - <https://learn.microsoft.com/en-us/power-query/connectors/google-bigquery>

* Service Account Email: must be in email format
* Service Account JSON key file contents: once downloaded, remove all newlines so the contents are on a single line. Once the JSON file is in that format, the contents can be pasted into this field.

Pay attention to the fact that the content of the JSON file needs to be manually formatted in one line<br>

#### Can't access table dwh\_ext\_xxx.j\_engagements and any other

You need to replace "xxx" with the label\_id specific to your setup. You can also see the label\_id in the name of the BigQuery user that has the format l"dwh\_ext\_1234"

**Can different labels and brands have access using the same DWH account?**

Every label requires a separate account to access DWH.

You could have many brands under one label setup. Data related to all these labels will be available from the respective DWH account for this label

**The documentation says not to use the DW directly for analytical queries, as doing so may incur costs. Is the best way to carry out a daily data load to an internal DW?**

The best approach is to make a daily incremental load from Smartico DWH to your DWH.

Dimensional tables (prefixed with “dm”) are relatively small, and you can reload them fully every day.

Other tables (fact tables), are partitioned by date/time, so you can run every day at 1 AM UTC, get fresh data for the previous day, and load it to your DWH

**When using a BI tool such as Power BI in the import model, can I connect directly to the DWH? Is there any financial or performance impact?**

If you use Power BI or any other tool to query data directly from Smartico DWH, this may incur a high load and additional costs depending on how complex reports you are doing. The best approach is to load data from Smartico DWH into your DWH and connect Power BI to it.

Regarding the "import" mode in Power BI, per Microsoft documentation, it can operate in two modes: "Full refresh" and "Incremental refresh". Based on the names, the best approach is to use the "Incremental way" method to avoid high resource usage. For the exact setup, please consult a Power BI expert.

**Is there a limit on the number of queries that do not generate this extra cost?**

There is a 1 TB monthly limit on scanned data (this reflects how Google prices BigQuery usage). There is no limit on the number of queries. Note that if you exceed the 1 TB monthly limit, the service will continue to be provided, and there is no automated blocking; you must manage usage on your side.

**Based on the documentation, we have a few questions: Is the data in the DWH always available for a window of the last 1080 days?**

Yes, fact tables are limited to the last 1080 days, and you won't be able to query data older than 1080 days from the query start time. If you do incremental/daily load of data finally, you can store unlimited in terms of time, data on your side.

**Can we retrieve data from all campaigns in this DWH? Information related to the segmentations we used, results by channel (sms, email, push, etc.), value assigned to the active group and control group, CTR, conversion rate, etc?**

You can get results from communication channels such as SMS, email, and push notifications.

The fact table for communication channels (j\_communication) includes all fails, clicks, and impressions, so you can build derived metrics such as CTR, Delivery rate, and fail rate.

The j\_engagements fact table contains information about campaigns, and you can calculate campaign conversion rates using it.

Segment exporting is available via the table function fn\_export\_segment; see the documentation above for details.

**If we have multiple labels and want to access data for all of them, should we use multiple access keys, or could we use a single shared one?**

Both options are possible and depend on your needs. We can create a single access key that has access to all labels you manage, or we can create separate keys.

Please note that a specific key can access only labels managed in the same environment. So, if you have one label on environment "3" and five labels on environment "5", you will have at least two access keys.

**Can we have two keys/service accounts accessing data for a specific label?**

No, we are providing only one service account per label

**Can we get access to the DWH using a service account managed by our company, rather than Smartico?**

No, access to DWH is provided only using service accounts managed by Smartico.