Trading Data Model

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

Relationships

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

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

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

Relationships

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

Relationships

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

Relationships

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

Relationships

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

Relationships