---
title: "Recipes"
description: "Copy-paste OQL examples for the most common CRM questions. Sales pipeline, action stream, contact management, and activity reporting."
canonical_url: https://developer.onepagecrm.com/oql/recipes/
source: Markdown mirror of https://developer.onepagecrm.com/oql/recipes/
---

Practical examples organized by the question they answer. Run them
through the OnePageCRM [MCP server](/mcp/overview/) from any connected
AI client. New to OQL? The [tutorial](/tutorials/oql/) builds queries
like these step by step.

## Sales pipeline

### Top 10 open deals by amount

A lookup pulls the contact's name and company in alongside each deal,
so the list reads without a second query:

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

### 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"]
}
```

### Owners with meaningful won revenue this quarter

Filter groups after aggregation with `having`: only owners with more
than 5 wins and at least 10,000 in revenue survive.

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

### Typical deal size (outlier-proof)

`median` and `percentile` resist the one whale deal that skews `avg`.

```json
{
  "from": "deals",
  "select": [{ "median": ["amount"] }, { "percentile": ["amount", 0.9] }],
  "where": { "status": "won", "close_date": "THIS_YEAR()" }
}
```

### 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"] }]
}
```

### Win rate by owner this quarter

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

The caller computes the rate from the per-status counts.

### Stuck deals (in pipeline more than 90 days)

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

### Deal pipeline by stage (live)

```json
{
  "from": "deals",
  "select": ["stage", "count()", { "sum": ["amount"] }],
  "where": { "status": "pending" },
  "group_by": ["stage"]
}
```

Filtering by `stage` implicitly restricts to pending deals. Use
`last_stage` to slice closed deals by where they ended up.

## Action stream

### My open actions for today

```json
{
  "from": "actions",
  "where": {
    "assignee_id": "ME()",
    "completed": false,
    "date": "TODAY()"
  }
}
```

### My overdue actions

```json
{
  "from": "actions",
  "where": {
    "assignee_id": "ME()",
    "completed": false,
    "date": { "<": "TODAY()" }
  }
}
```

### Top of my Action Stream

```json
{ "from": "actions", "where": { "assignee_id": "ME()", "completed": false }, "limit": 25 }
```

Omitting `order_by` uses the [Action Stream priority order](/getting-started/data-model/#how-the-stream-sorts).

### Team completion this week

```json
{
  "from": "actions",
  "select": ["assignee_id", "count()"],
  "where": {
    "completed": true,
    "completed_at": { ">=": "THIS_WEEK()" }
  },
  "group_by": ["assignee_id"]
}
```

## Contact management

### New leads this week

```json
{
  "from": "contacts",
  "where": {
    "status_id": "lead",
    "created_at": { ">=": "THIS_WEEK()" }
  },
  "order_by": [{ "created_at": "desc" }]
}
```

### Stale contacts (no activity in 30+ days)

```json
{
  "from": "contacts",
  "where": {
    "owner_id": "ME()",
    "last_activity_date": { "<": { "DAYS_AGO": [30] } }
  },
  "order_by": ["last_activity_date"]
}
```

### Contact breakdown by status

```json
{
  "from": "contacts",
  "select": ["status_id", "count()"],
  "group_by": ["status_id"]
}
```

### Contacts at a specific company

```json
{
  "from": "contacts",
  "where": { "company_id": "507f1f77bcf86cd799439011" },
  "order_by": ["last_name"]
}
```

### VIP contacts I haven't called this quarter

OQL [lookups](/oql/concepts/#lookups-related-fields) pull related
fields *forward* — a call can read its `contact` — but they can't
express "contacts with *no* matching call," an anti-join. So this
stays a client-side join in two queries. First, the contact IDs who
*have* been called this quarter:

```json
{
  "from": "calls",
  "select": ["contact_id"],
  "where": { "call_time": { ">=": "THIS_QUARTER()" } }
}
```

Then your VIPs, carrying the `id` you'll match on:

```json
{
  "from": "contacts",
  "select": ["id", "first_name", "last_name"],
  "where": { "owner_id": "ME()", "tags": "VIP" }
}
```

Client-side, keep the VIPs whose `id` isn't in the first query's
`contact_id` set — those are the ones you haven't called this quarter.

## Activity reporting

### Recent calls by user, this week

```json
{
  "from": "calls",
  "select": ["author_id", "count()"],
  "where": { "call_time": { ">=": "THIS_WEEK()" } },
  "group_by": ["author_id"]
}
```

### Call outcomes this quarter

```json
{
  "from": "calls",
  "select": ["call_result_id", "count()"],
  "where": { "call_time": { ">=": "THIS_QUARTER()" } },
  "group_by": ["call_result_id"]
}
```

### Daily call volume over the last 30 days

```json
{
  "from": "calls",
  "select": ["call_time", "count()"],
  "where": { "call_time": { ">=": { "DAYS_AGO": [30] } } },
  "group_by": [{ "DAY": ["call_time"] }]
}
```

### Meetings I held this month

```json
{
  "from": "meetings",
  "where": {
    "author_id": "ME()",
    "meeting_time": { ">=": "THIS_MONTH()" }
  },
  "order_by": [{ "meeting_time": "desc" }]
}
```

## Missing a recipe?

If you have a CRM question that doesn't map cleanly to a recipe here,
the [Concepts page](/oql/concepts/) covers the full query shape, and
the [entity reference](/oql/entities/contacts/) lists every field you
can filter, sort, aggregate, or group by.