---
title: "Trading"
canonical: "https://www.owox.com/data-models/trading"
updated: "2026-09-26"
---

# Trading Data Model

8 data marts168 fields[Vlad Flaks](https://github.com/vladflaks)[Rus Obolonsky](https://github.com/Obolrus)

A click becomes a lead, then a registration, a verified client and a funded account at a forex and CFD brokerage, with lifetime value and withdrawals tracked alongside.

## Overview

The client acquisition and funding side of a retail forex and CFD brokerage: budget bought across platforms and targeting countries, the visits it produces, the short lead-capture forms those visits leave behind, the sales desk that calls them, the identity checks that decide who is allowed in at all, and the deposits that determine whether any of it paid for itself. The funnel is deliberately long — a click becomes a lead, a lead becomes a registration, a registration becomes a verified client, and only then a first deposit and a first real trade — and every step of it is measurable here, alongside the lifetime value, the withdrawals and the lifecycle segments that say what happened after.

**Scope:** this model ends where trading begins. It covers acquisition, verification, funding and client lifecycle up to the first trade. Trading activity itself — executed trades, instruments, volumes, and the spread and commission a broker earns on them — is not part of it, so questions about trading revenue or volume by symbol have no answer here. What it does answer is what a funded client costs, where they come from, and what they are worth once they arrive.

## Example Questions

*   Which channels buy traders worth keeping rather than merely cheap ones — do the campaigns with the lowest cost per `first deposit` produce the `clients` who end up as champions with real lifetime value, or the ones who deposit once and go dormant?
*   Does the desk change the outcome — do the `leads` someone managed to reach `deposit` more often, and sooner, than the ones that were never answered?
*   Which acquisition regions return more `deposit volume` than they consume in budget, and how much of that money is withdrawn again within a few months?

[Explore on canvas →](https://model.owox.com/?okf=https://github.com/OWOX/models/tree/main/bundles/trading)

## Ad Spend

Every dollar the brokerage puts into buying traffic, one row per day, source, medium, campaign and targeting country — the exact grain the media buyers work at. Each row carries the money twice, once as `cost` and once as `cost_normalized` — both already in this reporting's single currency, so either sums safely — next to the impressions and clicks it bought, so cost per click, cost per thousand impressions and click-through rate all come out of a single table. Targeting country is where the budget was spent, not where the trader who answered the ad turns out to live — the two diverge often enough that keeping them apart is the whole point of the field.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `date` | DATE | Date | PK. Composite key together with source, medium, campaign, targeting\_country. Calendar date the spend occurred — use for daily/weekly/monthly spend trends. FK to [Attribution](#mart-attribution) |
| `source` | STRING | Source | PK. Ad traffic source — which channel ran the ad, e.g. `google`, `facebook`, `tiktok`, `bing`, `native`, `applesearch`. Answers "which channel/platform did we spend on". FK to [Attribution](#mart-attribution) |
| `medium` | STRING | Medium | PK. Ad format / traffic medium, e.g. `cpc`, `cpm`, `paid-social`. Answers "what type of ad" (search vs social vs display). FK to [Attribution](#mart-attribution) |
| `campaign` | STRING | Campaign | PK. UTM campaign name, e.g. `search_generic_fx`, `acq_video_q3`. Use for campaign-level spend breakdowns. FK to [Attribution](#mart-attribution) |
| `targeting_country` | STRING | Targeting Country | PK. Country the ad budget was targeted at — i.e. where the money was spent. This is NOT where the resulting client lives; for that use Clients.country or Attribution.client\_country. Use this field to answer "spend by country". FK to [Attribution](#mart-attribution) |
| `targeting_region` | STRING | Targeting Region | Business-region roll-up of targeting\_country: `SEA`, `ME`, `EU`, `LATAM`, `AFRICA`, `CA`, `UK`, `AU`, `ANZ`, `Other`. Use for "spend by region" instead of listing every country. |
| `ad_platform` | STRING | Ad Platform | Name of the advertising platform that billed this spend: `Google Ads`, `Meta Ads`, `TikTok Ads`, `Bing Ads`, `YouTube Ads`, `Apple Search Ads`, `Native Ads Network`. Answers "which ad platform/network". |
| `cost` | FLOAT | Cost | Ad spend in this dataset's single reporting currency — identical to `cost_normalized` here, so summing it directly is safe. Kept as a separate field for consistency with source systems where ad platforms genuinely bill in local currency and a normalized column is needed. |
| `cost_normalized` | FLOAT | Cost Normalized | Ad spend converted to USD. This is the field to SUM for "total spend", "ad cost", "budget spent", "how much did we spend" questions — safe to aggregate across countries and campaigns. |
| `impressions` | INTEGER | Impressions | Number of times the ad was displayed. SUM for total impressions; divide clicks by impressions for CTR. |
| `clicks` | INTEGER | Clicks | Number of ad clicks. SUM for total clicks; divide cost\_normalized by clicks for CPC, or clicks by impressions for CTR. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Attribution](#mart-attribution) | `date = date, source = source, medium = medium, campaign = campaign, targeting_country = targeting_country` | 1:N | The acquisition funnel this day of spend paid for. |

## Attribution

The whole acquisition funnel already assembled on one row: for each day, source, medium, campaign and targeting country, the money spent and the impressions and clicks it bought, then the sessions that arrived, the short forms submitted, the long forms completed, the clients who cleared KYC, the first time deposits and finally the new trading clients who placed a real trade — with the deposit volume those clients went on to generate. Cost per lead, cost per FTD and return on ad spend are ratios between two columns of the same row, and because every step is carried side by side, the stage where a channel actually loses people is visible without joining anything.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `date` | DATE | Date | Calendar date of the funnel activity. Part of the join grain shared with Ad Spend (plus targeting\_country). |
| `source` | STRING | Source | Traffic source. Part of the join grain. |
| `medium` | STRING | Medium | Traffic medium. Part of the join grain. |
| `campaign` | STRING | Campaign | UTM campaign name. Part of the join grain. |
| `targeting_country` | STRING | Targeting Country | Country the ad budget was targeted at (for organic/direct rows, the country traffic actually came from). Part of the join grain; joins to Ad Spend.targeting\_country. |
| `cost_normalized` | FLOAT | Cost Normalized | Ad spend in USD for this row. `0` for organic/direct/affiliate rows (they have no matching Ad Spend row). SUM this for total spend by any slice; divide by short\_forms/ftd\_count/ntc\_count for CPA-style metrics. |
| `cost` | FLOAT | Cost | Ad spend in the original billing currency. `0` for organic/direct/affiliate rows. Prefer `cost_normalized` for any cross-currency total. |
| `impressions` | INTEGER | Impressions | Ad impressions for this row. `0` for unpaid rows. |
| `clicks` | INTEGER | Clicks | Ad clicks for this row. `0` for unpaid rows. |
| `ad_platform` | STRING | Ad Platform | Ad platform, e.g. `Google Ads`, `Meta Ads`, `TikTok Ads`. `"Organic/Direct"` for unpaid rows. |
| `sessions` | INTEGER | Sessions | Web/app sessions attributed to this row — funnel step 1. SUM for total traffic; divide short\_forms by sessions for the session→lead conversion rate. |
| `unique_users` | INTEGER | Unique Users | Unique users attributed to this row. |
| `pages_per_session` | FLOAT | Pages Per Session | Average pages viewed per session for this row — an engagement/traffic-quality signal, not a funnel step. |
| `avg_session_duration` | FLOAT | Average Session Duration | Average session duration in seconds for this row — engagement signal. |
| `short_forms` | INTEGER | Short Forms | Short-form submissions (leads) attributed to this row — funnel step 2, "Profile Short Form" in dashboards. Divide by `sessions` for session→lead conversion; divide `cost_normalized` by this for CPA per lead. |
| `long_forms` | INTEGER | Long Forms | Long-form / full registrations attributed to this row — funnel step 3, "Profile Long Form" in dashboards. |
| `registrations` | INTEGER | Registrations | Completed account registrations attributed to this row. Equal to `long_forms` in this model (registration = completing the long form). |
| `kyc_verified` | INTEGER | KYC Verified | Clients who passed KYC verification, attributed to this row. |
| `ftd_count` | INTEGER | Ftd Count | First Time Depositors attributed to this row — funnel step 4, "Profile FTD" in dashboards. Divide `cost_normalized` by this for cost-per-FTD (CPA), the primary acquisition-efficiency metric. |
| `ntc_count` | INTEGER | Ntc Count | New Trading Clients (placed a first real trade) attributed to this row — funnel step 5, "Profile NTC". Always ≤ `ftd_count` for the same row (a client must fund before trading). |
| `deposit_volume_normalized` | FLOAT | Deposit Volume Normalized | Total deposit amount in USD from clients attributed to this row (cumulative, not just their first deposit). This is the "revenue" field — divide by `cost_normalized` for ROAS. |
| `client_country` | STRING | Client Country | Country of the clients who actually converted (from Clients.country). May differ from `targeting_country` — e.g. ads targeted at country A but the client registered from country B. |
| `targeting_region` | STRING | Targeting Region | Business-region roll-up of `targeting_country`. |
| `region` | STRING | Region | Business region — equal to `targeting_region` in this mart; use either. |
| `traffic_platform` | STRING | Traffic Platform | `App` or `Web`. |
| `attribution_id` | STRING | Attribution ID | PK. Unique internal identifier for this attribution row (the row's own surrogate key, not a business dimension). |
| `user_source` | STRING | User Source | First-touch acquisition source (mirrors `source` in this already-aggregated mart; the first/last-touch distinction matters more at the Sessions grain). |
| `user_medium` | STRING | User Medium | First-touch acquisition medium. |
| `user_campaign` | STRING | User Campaign | First-touch acquisition campaign. |

## Clients

The trader profile, and the centre of the model: one row per registered client, carrying the four milestones the business is run on — registration, identity verification, first deposit and first real trade — with the dates between them, so how long a client takes to fund and then to trade is a stored number rather than a calculation. Each row also holds the money: every deposit and withdrawal already totalled, net deposits, lifetime value, how recently and how often the client funded, an RFM score on all three axes, and a lifecycle segment that names what the client is today, from someone who registered and never deposited to an active trader, a dormant account or a churned one. It is the mart that answers who the customers are, what they are worth and which of them are slipping away.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `client_id` | STRING | Client ID | PK. Unique client identifier. |
| `lead_id` | STRING | Lead ID | The first short-form submission that started this client's journey. |
| `session_id` | STRING | Session ID | The first-touch session that originally brought this client in. NULL if none could be matched. |
| `email` | STRING | Email | Client email — identity bridge key, matches Leads.email. |
| `phone` | STRING | Phone | Client phone — identity bridge key. |
| `first_name` | STRING | First Name | Client first name. |
| `last_name` | STRING | Last Name | Client last name. |
| `country` | STRING | Country | Client's country of residence/registration — the client's actual location, NOT the ad targeting country. Use this field (not Ad Spend.targeting\_country) to answer "which country has the most clients/FTD" or "FTD by country" questions. |
| `region` | STRING | Region | Business-region roll-up of the client's country: `SEA`, `ME`, `EU`, `LATAM`, `AFRICA`, `CA`, `UK`, `AU`, `ANZ`, `Other`. Use for region-level FTD/LTV roll-ups. |
| `language` | STRING | Language | Client's preferred language. |
| `registration_date` | DATE | Registration Date | Date the client completed account registration. This corresponds to the "Long Form" / "Profile Long Form" funnel step in dashboards. |
| `kyc_status` | STRING | KYC Status | Identity-verification status: `pending`, `verified`, `rejected`, `expired`. |
| `kyc_verified_date` | DATE | KYC Verified Date | Date KYC was approved. NULL if not yet verified. |
| `account_type` | STRING | Account Type | Regulatory client classification: `retail` or `professional`. |
| `is_ftd` | BOOLEAN | Is Ftd | Whether the client made at least one completed deposit (First Time Depositor). This is the "FTD" / "Profile FTD" metric — `COUNTIF(is_ftd)` gives the FTD count, the primary acquisition-efficiency metric. |
| `ftd_date` | DATE | Ftd Date | Date of the client's first completed deposit. NULL if `is_ftd = false`. Use for FTD trend charts. |
| `ftd_amount_normalized` | FLOAT | Ftd Amount Normalized | Amount of the first deposit, in USD. NULL if `is_ftd = false`. |
| `ftd_payment_method` | STRING | Ftd Payment Method | Payment method used for the first deposit: `card`, `wire_transfer`, `crypto`, `skrill`, `neteller`, `paypal`. NULL if `is_ftd = false`. |
| `days_to_ftd` | FLOAT | Days To Ftd | Days between `registration_date` and `ftd_date` — how fast a client deposits after registering. NULL if `is_ftd = false`. |
| `is_ntc` | BOOLEAN | Is Ntc | Whether the client placed at least one real (non-demo) trade — New Trading Client. Always implies `is_ftd = true` (a client must fund before trading). This is the "NTC" / "Profile NTC" metric — `COUNTIF(is_ntc)` gives the NTC count. |
| `ntc_date` | DATE | Ntc Date | Date of the client's first real trade. NULL if `is_ntc = false`. |
| `days_to_ntc` | FLOAT | Days To Ntc | Days between `ftd_date` and `ntc_date` — how fast a depositor starts trading. NULL if `is_ntc = false`. |
| `has_open_positions` | BOOLEAN | Has Open Positions | Whether the client currently has open trading positions — a live-engagement signal. |
| `created_at` | TIMESTAMP | Created At | Record creation timestamp in the warehouse. |
| `count_clients` | INTEGER | Count Clients | Always `1` on every row; SUM to count clients matching a filter. |
| `total_deposits_normalized` | FLOAT | Total Deposits Normalized | Sum of all this client's completed deposits, in USD. SUM across clients to answer "total deposit volume" / "revenue" questions. |
| `total_withdrawals_normalized` | FLOAT | Total Withdrawals Normalized | Sum of all this client's completed withdrawals, in USD. |
| `net_deposits` | FLOAT | Net Deposits | `total_deposits_normalized` minus `total_withdrawals_normalized` — net money the client has put in. |
| `deposit_count` | INTEGER | Deposit Count | Number of completed deposit transactions made by this client — the RFM "frequency" input. |
| `last_deposit_date` | DATE | Last Deposit Date | Date of the client's most recent completed deposit. |
| `days_since_last_deposit` | FLOAT | Days Since Last Deposit | Days elapsed since `last_deposit_date` — the RFM "recency" input; also drives `client_segment` (e.g. `dormant`, `churned`). |
| `days_since_ftd` | FLOAT | Days Since Ftd | Days elapsed since the client's first deposit — client "age" in the system. |
| `ltv` | FLOAT | LTV | Lifetime value in USD — equal to `total_deposits_normalized`. Use for "what is the LTV of clients acquired via X" questions. |
| `recency_score` | FLOAT | Recency Score | RFM recency score, 1-5 (5 = deposited most recently). Derived from `days_since_last_deposit`. |
| `frequency_score` | FLOAT | Frequency Score | RFM frequency score, 1-5 (5 = most deposits). Derived from `deposit_count`. |
| `monetary_score` | FLOAT | Monetary Score | RFM monetary score, 1-5 (5 = highest `net_deposits`). |
| `client_segment` | STRING | Client Segment | Behavioural lifecycle segment, already computed: `active_trader`, `dormant`, `ftd_only`, `churned`, `registered_no_ftd`, `new`. Use this directly for "give me churned/dormant clients" questions instead of recomputing from date fields. |
| `rfm_label` | STRING | Rfm Label | RFM marketing segment, already computed: `champions`, `loyal`, `at_risk`, `lost`, `new`, `promising`. `lost` specifically means the client's most recent contact (see Communications) was an unanswered/unsuccessful call. |
| `data_source` | STRING | Data Source | `App` or `Web` — the surface this client was first acquired on. |

## Communications

Every conversation between the sales desk and the people it is trying to convert: one row per contact attempt across calls, email, SMS, live chat and messengers, recording who handled it, whether the desk reached out or the client got in touch, and whether the attempt actually landed or went unanswered. Rows are flagged as the first and the most recent contact with a person and as sales-driven rather than servicing, so the shape of a relationship — how many attempts it took before someone answered, when the desk last got through, and whether the last thing that happened was silence — can be read without reconstructing the timeline. This is where the human half of the funnel lives, next to the forms and the deposits.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `communication_id` | STRING | Communication ID | PK. Unique communication-event identifier. |
| `client_id` | STRING | Client ID | — despite the name, this is the client the communication was with (legacy CRM field name, equivalent to `client_id` elsewhere). FK to [Clients](#mart-clients) |
| `agent_id` | STRING | Agent ID | Internal identifier of the agent who handled this communication. Not a foreign key to another mart in this model. |
| `lead_id` | STRING | Lead ID | The lead this communication relates to, if any. NULL for post-registration servicing contacts. FK to [Leads](#mart-leads) |
| `channel` | STRING | Channel | Channel used: `call`, `email`, `sms`, `live_chat`, `whatsapp`, `telegram`. |
| `direction` | STRING | Direction | `inbound` (client-initiated) or `outbound` (agent-initiated). |
| `status` | STRING | Status | Outcome: `successful` or `unsuccessful` (no answer / bounced / failed). This field, combined with `is_last`, is what drives Clients.rfm\_label = `lost`. |
| `subject` | STRING | Subject | Topic/subject line. NULL for phone calls. |
| `text` | STRING | Text | Message body or call notes, if recorded. |
| `autoreply` | STRING | Autoreply | Content of any triggered automated reply. NULL if none was sent. |
| `communication_date` | DATE | Communication Date | Calendar date of the communication. |
| `is_first` | BOOLEAN | Is First | `"true"`/`"false"` boolean flag — first ever communication with this client. |
| `is_last` | BOOLEAN | Is Last | `"true"`/`"false"` boolean flag — most recent communication with this client. A `"true"` row with `channel = 'call'` and `status = 'unsuccessful'` is what marks a client `lost` in Clients.rfm\_label. |
| `is_sales` | BOOLEAN | Is Sales | `"true"`/`"false"` boolean flag — flagged as a sales-focused interaction. |
| `is_last_sales` | BOOLEAN | Is Last Sales | `"true"`/`"false"` boolean flag — most recent sales-flagged communication with this client. |
| `count_communications` | INTEGER | Count Communications | Always `1` on every row; SUM to count communications matching a filter. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Clients](#mart-clients) | `client_id = client_id` | N:1 | The client the desk spoke to. |
| [Leads](#mart-leads) | `lead_id = lead_id` | N:1 | The lead the desk was trying to convert. |

## Deposits

The money ledger: one row per funding transaction, money in and money out, tied to the client, the trading account it moved through and the lead the client originally came from. Each transaction carries its amount in the currency it was made in and converted to USD, the rate used at the time, the payment method behind it — card, wire, crypto or an e-wallet — and a processing status, because a meaningful share of attempted funding never completes: it fails, is reversed or is cancelled, and counting those as revenue overstates the business. One row per client is flagged as that client's first ever deposit, which is where the acquisition funnel finally turns into cash.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `deposit_id` | STRING | Deposit ID | PK. Unique transaction identifier. |
| `client_id` | STRING | Client ID | FK to [Clients](#mart-clients) |
| `account_id` | STRING | Account ID | The specific trading account the money moved into/out of. FK to [Trading Accounts](#mart-trading-accounts) |
| `lead_id` | STRING | Lead ID | This client's originating lead. NULL if not traceable. FK to [Leads](#mart-leads) |
| `deposit_datetime` | TIMESTAMP | Deposit Datetime | Timestamp the transaction was initiated. |
| `transaction_type` | STRING | Transaction Type | `deposit` (money in) or `withdrawal` (money out). Filter on this before summing amounts — don't sum deposits and withdrawals together. |
| `is_ftd` | BOOLEAN | Is Ftd | True only for the single row that is this client's very first-ever deposit. Row-level equivalent of Clients.is\_ftd (which is a per-client flag, not per-transaction). |
| `status` | STRING | Status | Processing status: `pending`, `completed`, `failed`, `reversed`, `cancelled`. Attempted rows carry their own `payment_method`, `currency`, `amount_local` and `exchange_rate`, so payment friction can be measured and valued; only `amount_normalized` is completion-gated — always filter `status = 'completed'` before summing revenue. |
| `amount_local` | FLOAT | Amount Local | Transaction amount in its original currency. Populated on attempted transactions too — a failed or pending payment has an amount — so `amount_local * exchange_rate` is the USD value of money that never arrived. For cross-currency totals of money that DID arrive use `amount_normalized`. |
| `currency` | STRING | Currency | Original transaction currency, taken from the account the money moved through. Populated on attempted transactions too. |
| `amount_normalized` | FLOAT | Amount Normalized | Transaction amount converted to USD. NULL unless `status = 'completed'` — the one completion-gated money column, so unarrived money can never reach a revenue total. SUM this (filtered to `transaction_type = 'deposit'`, `status = 'completed'`) for "deposit volume"/"revenue" questions. |
| `exchange_rate` | FLOAT | Exchange Rate | Conversion rate captured at transaction time; `1.0` on USD rows. Populated on attempted transactions too. |
| `payment_method` | STRING | Payment Method | `card`, `wire_transfer`, `crypto`, `skrill`, `neteller`, `paypal`. Populated on attempted transactions too — the rail a failed payment was attempted on is the point of asking — so failure rates are comparable across methods. |
| `country` | STRING | Country | Client's country at the time of the transaction. |
| `created_at` | TIMESTAMP | Created At | Record creation timestamp in the warehouse. |
| `count_deposits` | INTEGER | Count Deposits | Always `1` on every row; SUM to count transactions matching a filter (e.g. count of completed deposits). |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Clients](#mart-clients) | `client_id = client_id` | N:1 | The client who funded. |
| [Leads](#mart-leads) | `lead_id = lead_id` | N:1 | The lead this funded client came from. |
| [Trading Accounts](#mart-trading-accounts) | `account_id = account_id` | N:1 | The account the money was deposited into. |

## Leads

The moment a visitor stops being anonymous: one row per short-form submission, the handful of contact details someone leaves on a landing page before anyone has spoken to them. Each row keeps the page and the session that produced it, the email and phone the desk will call, the system that captured it — a website form, the app, a chatbot or an affiliate — and the stage the lead has reached since, from contacted through the full registration to a first deposit and a first trade, or else no answer and rejection with a stated reason. This is the top of the sales funnel, and the only place where the leads that were never reached at all are visible next to the ones that converted.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `lead_id` | STRING | Lead ID | PK. Unique identifier for this short-form submission. |
| `session_id` | STRING | Session ID | The session during which this form was submitted. NULL if no session could be matched (e.g. affiliate-referred leads entered outside the web funnel). FK to [Sessions](#mart-sessions) |
| `client_id` | STRING | Client ID | Set only once this lead converts into a registered client. NULL until then — use `IS NOT NULL` to filter "converted leads". FK to [Clients](#mart-clients) |
| `short_form_submitted_at` | TIMESTAMP | Short Form Submitted At | Timestamp the form was submitted. Use for lead-volume trends over time. |
| `landing_page` | STRING | Landing Page | Landing page path where the form was filled. Matches Sessions.landing\_page for the same session. |
| `country` | STRING | Country | Country of the lead, from IP geolocation or form input. |
| `language` | STRING | Language | Browser / form language of the lead. |
| `email` | STRING | Email | Email entered in the form — identity bridge key that later matches Clients.email. |
| `phone` | STRING | Phone | Phone entered in the form. |
| `status` | STRING | Status | Current funnel stage of this lead: `contacted`, `long_form`, `ftd`, `ntc`, `no_answer`, `rejected`. Use this to answer "how many leads reached X stage" without needing to join Clients — though `ftd`/`ntc` status here should match `Clients.is_ftd`/`is_ntc` for the linked client\_id. |
| `rejection_reason` | STRING | Rejection Reason | Why the lead was rejected. NULL unless `status = 'rejected'`. |
| `form_type` | STRING | Form Type | Always `"short"` in this mart — it only captures short-form submissions (the fuller registration is tracked as Clients.registration\_date / the `long_form` status, not a separate row here). |
| `source_system` | STRING | Source System | System that captured this lead: `website_form`, `app_form`, `affiliate`, `chatbot`. |
| `is_manual_entry` | BOOLEAN | Is Manual Entry | True if a staff member entered this lead manually rather than it being captured automatically from a form. |
| `created_at` | TIMESTAMP | Created At | Record creation timestamp in the warehouse. |
| `count_leads` | INTEGER | Count Leads | Always `1` on every row; SUM to count leads — this is the "Short Form" / "Profile Short Form" metric seen in dashboards. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Clients](#mart-clients) | `client_id = client_id` | N:1 | The client this lead became, where it converted. |
| [Sessions](#mart-sessions) | `session_id = session_id` | N:1 | The visit the form was submitted in. |

## Sessions

Every visit to the broker's site and app, one row per session, from the anonymous first click on an ad to the return visit of a client who is already trading. A row records where the visit came from — its own last-touch source, medium and campaign alongside the first-touch channel that originally acquired the visitor — where it landed, what device and country it came from, and how engaged it was in pages viewed and seconds spent. Once a visitor registers, the session carries the client identifier, which is what turns raw traffic into something you can follow all the way to a deposit.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `date` | DATE | Date | Calendar date of the session. FK to [Attribution](#mart-attribution) |
| `session_id` | STRING | Session ID | PK. Unique identifier for a single session. Don't COUNT this to get session totals — SUM the `count_sessions` field below instead, it is purpose-built for that. |
| `source` | STRING | Source | This session's own (last-touch) traffic source. If you need the channel that FIRST acquired the visitor (not just this visit), use `user_source` instead. FK to [Attribution](#mart-attribution) |
| `medium` | STRING | Medium | This session's own (last-touch) traffic medium. See `user_medium` for the first-touch equivalent. FK to [Attribution](#mart-attribution) |
| `campaign` | STRING | Campaign | This session's own (last-touch) UTM campaign. See `user_campaign` for the first-touch equivalent. FK to [Attribution](#mart-attribution) |
| `ad_content` | STRING | Ad Content | UTM ad content — identifies the specific ad creative shown. Only populated for paid sessions. |
| `ad_group` | STRING | Ad Group | Ad group within the campaign. Only populated for paid sessions. |
| `channel_grouping` | STRING | Channel Grouping | Simplified channel bucket: `Paid Search`, `Paid Social`, `Organic`, `Direct`, `Referral`, `Affiliate`, `Video`, `Display`. Use this for a high-level channel-mix chart instead of raw source/medium. |
| `keyword` | STRING | Keyword | Search keyword that triggered the session. Only populated for search (cpc/organic) sessions. |
| `landing_page` | STRING | Landing Page | Path of the first page viewed in the session (e.g. `/open-account`, `/promo/welcome-bonus`), without UTM parameters. Use to answer "which landing page converts best" — join to Leads on `session_id` to compute a conversion rate per landing page. Note: there is no cost breakdown by landing page (ad platforms only report cost per campaign), so CPA/CPL by landing page cannot be computed, only conversion rate/counts. |
| `landing_host_name` | STRING | Landing Host Name | Landing hostname for web sessions, or the app identifier for App sessions. |
| `url` | STRING | URL | Full first-hit URL including UTM parameters, for web sessions. |
| `started_at` | TIMESTAMP | Started At | Timestamp the session began. Use MIN/MAX only — not a metric to SUM or AVG. |
| `consent_at` | TIMESTAMP | Consent At | Timestamp of first recorded (GDPR-style) consent. NULL if no consent was recorded in this session. |
| `ga_client_id` | STRING | Ga Client ID | Browser/device analytics cookie id, used only to link anonymous sessions to the same device across visits. This is NOT the CRM client — do not confuse with `client_id` below. |
| `country` | STRING | Country | Visitor's country from IP geolocation — this is where the visitor actually is, not the ad targeting country (see Ad Spend.targeting\_country). |
| `region` | STRING | Region | Business-region roll-up of the visitor's country: `SEA`, `ME`, `EU`, `LATAM`, `AFRICA`, `CA`, `UK`, `AU`, `ANZ`, `Other`. |
| `city` | STRING | City | Visitor's city. |
| `device_category` | STRING | Device Category | `Desktop`, `Mobile` or `Tablet`. |
| `is_first_visitor_session` | BOOLEAN | Is First Visitor Session | `"true"`/`"false"` boolean flag — whether this is the visitor's very first ever session. |
| `traffic_platform` | STRING | Traffic Platform | `App` or `Web` — which surface the session happened on. Use for "app vs web" traffic-split questions. |
| `unique_users` | INTEGER | Unique Users | Always `1` on every row; SUM to count unique users in a report, do not AVG or use as a real per-row metric. |
| `pages_per_session` | FLOAT | Pages Per Session | Number of pages viewed in this specific session. Engagement/quality-of-traffic signal. |
| `avg_session_duration` | FLOAT | Average Session Duration | Duration of this specific session, in seconds. Engagement signal. |
| `client_id` | STRING | Client ID | Set only once the visitor is an identified, registered client — NULL for anonymous, not-yet-registered visitors. Use to join session behaviour to CRM/deposit data. FK to [Clients](#mart-clients) |
| `count_sessions` | INTEGER | Count Sessions | Always `1` on every row; SUM this field to answer "how many sessions" — this is the standard sessions-count metric. |
| `user_source` | STRING | User Source | First-touch acquisition source for this visitor — the channel that originally brought them in, which may differ from this particular session's own `source`. Use when the question is about acquisition/attribution rather than this specific visit. |
| `user_medium` | STRING | User Medium | First-touch acquisition medium. See `user_source`. |
| `user_campaign` | STRING | User Campaign | First-touch acquisition campaign. See `user_source`. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Attribution](#mart-attribution) | `date = date, source = source, medium = medium, campaign = campaign` | N:1 | The acquisition funnel this visit rolls into. |
| [Clients](#mart-clients) | `client_id = client_id` | N:1 | The client this visit is recognised as. |

## Trading Accounts

The accounts clients actually trade on: one row per MT4 or MT5 account, with the terms it was opened under — the base currency it is denominated in, the maximum leverage from a cautious 1:10 up to 1:500, and how the holder is classified: an ordinary retail client, a professional one, a swap-free islamic account or a practice account. Alongside the terms sit the two numbers that describe its state: the settled balance, and the equity that includes the profit or loss running on positions still open. A client can hold several accounts, which is where multi-account behaviour becomes visible — a second, higher-leverage account opened next to a conservative first one is a meaningful change in how someone is trading.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `account_id` | STRING | Account ID | PK. Unique trading account identifier. |
| `client_id` | STRING | Client ID | Owner of this account — a client may have more than one account, so COUNT(account\_id) can exceed COUNT(DISTINCT client\_id). FK to [Clients](#mart-clients) |
| `trading_platform` | STRING | Trading Platform | Trading platform: `MT4` or `MT5`. |
| `account_type` | STRING | Account Type | `retail`, `professional`, `demo` (no real money), `islamic` (swap-free). |
| `currency` | STRING | Currency | Account's base currency: `USD`, `EUR`, `GBP`, `AUD`. `balance`/`equity` are denominated in this currency, not USD. |
| `leverage` | STRING | Leverage | Maximum leverage on this account: `1:10`, `1:50`, `1:100`, `1:200`, `1:500`. |
| `status` | STRING | Status | Account state: `active`, `inactive`, `closed`. |
| `balance` | FLOAT | Balance | Current balance, in the account's own `currency` — excludes unrealized P&L from open positions. |
| `equity` | FLOAT | Equity | Current equity, in the account's own `currency` — balance adjusted for unrealized P&L on open positions. |
| `country` | STRING | Country | Country of the account holder at account-creation time. |
| `created_at` | TIMESTAMP | Created At | Timestamp the account was opened. |
| `count_deposits` | INTEGER | Count Deposits | Number of deposit transactions made into this specific account (see Deposits for the transactions themselves). |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Clients](#mart-clients) | `client_id = client_id` | N:1 | The client who trades on this account. |

## Apply to your project

1.  1

    ### Install the Import Model plugin

    One plugin, installed once, in your own OWOX workspace.

    [Get the plugin →](https://github.com/OWOX/import-model)

2.  2

    ### Import this model

    Point it at this bundle and it creates every data mart above, joins and all.

    [Open the model →](https://model.owox.com/?okf=https://github.com/OWOX/models/tree/main/bundles/trading)

3.  3

    ### Plug in your data and destinations

    Connect your own sources and send the results where your team already works.

    [Browse connectors →](/connectors)

**4\. Optional — customize as you wish.** Rename a column, drop a mart, add your own: once it is imported it is yours, and nothing here syncs back.

## References

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Browse connectors →](https://www.owox.com/connectors) — /connectors.md