---
title: "Deals"
description: "OQL field reference for deals. Sales opportunities linked to contacts, tracked through pipeline stages."
canonical_url: https://developer.onepagecrm.com/oql/entities/deals/
source: Markdown mirror of https://developer.onepagecrm.com/oql/entities/deals/
---

Deals are sales opportunities linked to contacts and tracked through
pipeline stages. The financial fields are aggregatable, which makes
deals the natural entity for revenue reporting.

## Fields

Legend: **F** filterable, **S** sortable, **A** aggregatable, **G** groupable.

| Field | Type | F | S | A | G | Description |
|-------|------|:-:|:-:|:-:|:-:|-------------|
| `id` | ID | Y | — | — | — | Deal ID |
| `name` | string | Y | Y | — | — | Deal name |
| `text` | string | — | — | — | — | Deal description / notes (free text; writable via `create`/`update`) |
| `contact_id` | ID | Y | — | — | Y | Associated contact ID |
| `owner_id` | ID | Y | — | — | Y | Deal owner user ID |
| `status` | string | Y | Y | — | Y | One of `pending`, `won`, `lost` |
| `amount` | number | Y | Y | Y | — | Deal value |
| `total_amount` | number | Y | Y | Y | — | Total amount including recurring months |
| `cost` | number | Y | Y | Y | — | Deal cost (if cost tracking is enabled) |
| `total_cost` | number | Y | Y | Y | — | Total cost including recurring months |
| `margin` | number | Y | Y | Y | — | Profit margin (`amount - cost`) |
| `commission` | number | Y | Y | Y | — | Commission amount |
| `commission_percentage` | number | Y | — | Y | — | Commission percentage |
| `commission_type` | string | Y | — | — | Y | How commission is expressed: `none`, `percentage`, or `absolute` |
| `commission_base` | string | Y | — | — | Y | What percentage commission is calculated from: `amount` or `margin` |
| `pipeline_id` | ID | Y | — | — | Y | Pipeline ID |
| `stage` | number | Y | Y | — | Y | Pipeline stage number. Live for pending deals only. |
| `last_stage` | number (virtual) | Y | Y | — | — | Final stage for closed deals (won, lost, and every delivery-pipeline deal) |
| `expected_close_date` | date | Y | Y | — | Y | Expected close date (when `status` is `pending`) |
| `close_date` | date | Y | Y | — | Y | Actual close date (when `status` is `won` or `lost`) |
| `months` | number | Y | — | — | — | Number of recurring months |
| `reason_lost` | string (output only) | — | — | — | — | Reason lost display name |
| `reason_lost_id` | ID | Y | — | — | Y | Reason lost ID |
| `has_deal_items` | boolean | Y | — | — | — | Whether the deal has line items |
| `archived` | boolean | Y | — | — | Y | Whether the deal is archived |
| `created_at` | time | Y | Y | — | Y | Record creation timestamp |
| `modified_at` | time | Y | Y | — | Y | Last modification timestamp |

## Notes

- **`stage` vs `last_stage`** map to the same underlying value but are
  scoped by deal status. Pending deals expose `stage`; closed deals
  (won, lost, and every deal in a delivery pipeline) expose
  `last_stage`. The non-applicable one is `null` in projections, and
  filtering by `stage` implicitly restricts the result set to pending
  deals.
- Stage values are integers but **not sequential**. A typical pipeline
  uses values like `10`, `20`, `40`. Stage numbers and their labels
  are account-configurable.
- **Sales vs delivery pipelines.** A pipeline is either a *sales*
  pipeline (deals run pending → won/lost) or a *delivery* pipeline
  (post-sales project tracking, where every deal is won and the stages
  track delivery progress). Deals in a delivery pipeline are always
  `won`, so their position lives in `last_stage`, not `stage`. Use
  [`context()`](/mcp/tools/context/) to see each pipeline's type.
- **Financial fields** (`amount`, `total_amount`, `cost`, `total_cost`,
  `margin`, `commission`, `commission_percentage`) are all aggregatable
  and valid as the argument to `sum`, `avg`, `min`, `max`, `median`,
  and `percentile`.
- **`close_date` is writable** via the MCP `create`/`update` tools, but
  only on won or lost deals (or together with `status: "won"`/`"lost"`
  in the same call). Pending deals have no close date; setting it on
  one is rejected with a hint to use `expected_close_date` instead.
- **Lookups:** `contact` (via `contact_id`). Pull the contact's fields
  inline as `contact.<field>` — for example `contact.last_name`,
  `contact.company`, or `contact.country_code` — in `select`, `where`,
  and `order_by`. See [Concepts › Lookups](/oql/concepts/#lookups-related-fields).
- **Custom fields** are queryable by name as `custom_fields.<name>` in
  `select`, `where`, and `order_by`; select `custom_fields.*` (or `*`)
  to return all of them. Filterability and sortability depend on the
  field's type. Numeric custom fields can also be aggregated
  (`{ "sum": ["custom_fields.<name>"] }`), and `min`/`max` work on date
  custom fields. `group_by` and `distinct` on custom fields are not
  supported: group by a top-level field and filter on the custom field
  in `where` instead. Use [`describe`](/mcp/tools/describe/) to
  discover an account's custom-field names and types.

## Example queries

Top 25 open deals by amount, each with its contact's company (a lookup):

```json
{
  "from": "deals",
  "select": ["name", "amount", "contact.company"],
  "where": { "status": "pending" },
  "order_by": [{ "amount": "desc" }],
  "limit": 25
}
```

Deals I own that closed last quarter:

```json
{
  "from": "deals",
  "where": {
    "owner_id": "ME()",
    "status": "won",
    "close_date": "LAST_QUARTER()"
  }
}
```

Won revenue this quarter, by owner:

```json
{
  "from": "deals",
  "select": ["owner_id", "count()", { "sum": ["amount"] }],
  "where": { "status": "won", "close_date": "THIS_QUARTER()" },
  "group_by": ["owner_id"]
}
```

Monthly closed-won trend over the last year:

```json
{
  "from": "deals",
  "select": ["close_date", "count()", { "sum": ["amount"] }],
  "where": {
    "status": "won",
    "close_date": { ">=": { "DAYS_AGO": [365] } }
  },
  "group_by": [{ "MONTH": ["close_date"] }]
}
```

Stuck deals (in pipeline more than 90 days):

```json
{
  "from": "deals",
  "where": {
    "status": "pending",
    "created_at": { "<": { "DAYS_AGO": [90] } }
  },
  "order_by": [{ "amount": "desc" }]
}
```