Recipes

Practical examples organized by the question they answer. Run them through the OnePageCRM MCP server from any connected AI client. New to OQL? The tutorial 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:

{
  "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

{
  "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.

{
  "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.

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

Monthly closed-won trend over the last year

{
  "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

{
  "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)

{
  "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)

{
  "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

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

My overdue actions

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

Top of my Action Stream

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

Omitting order_by uses the Action Stream priority order.

Team completion this week

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

Contact management

New leads this week

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

Stale contacts (no activity in 30+ days)

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

Contact breakdown by status

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

Contacts at a specific company

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

VIP contacts I haven’t called this quarter

OQL lookups 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:

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

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

{
  "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

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

Call outcomes this quarter

{
  "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

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

Meetings I held this month

{
  "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 covers the full query shape, and the entity reference lists every field you can filter, sort, aggregate, or group by.