Tips & Optimization

General principles

RevOpsQL performs two types of calculations:

• HubSpot side (server): main WHERE filters, LIMIT
• RevOpsQL side (client): WITH WHERE filters, TAKE, REQUIRE, dynamic expressions

HubSpot-side calculations are always faster — they reduce the volume of data downloaded before processing even begins.

Tip #1 — Filter early with the main WHERE

Move as many conditions as possible into the FROM WHERE rather than in the WITH blocks:

-- ❌ Less efficient: all companies are downloaded
SELECT name, domain
FROM company
WITH ASSOCIATED deal AS won_deals
  WHERE dealstage = 'closedwon'
    AND annualrevenue > 1000000  -- company property filtered client-side

-- ✅ More efficient: HubSpot only sends companies > €1M
SELECT name, domain
FROM company
WHERE annualrevenue > 1000000  -- filtered HubSpot-side
WITH ASSOCIATED deal AS won_deals
  WHERE dealstage = 'closedwon'

Tip #2 — Use LIMIT for large volumes

If you don't need all objects, use LIMIT to avoid downloading thousands of unnecessary records:

-- Analyze the 200 most recent deals (not all deals)
SELECT dealname, amount, closedate
FROM deal
WHERE dealstage = 'closedwon'
LIMIT 200 ORDER BY RECENTLY_CREATED
WITH ASSOCIATED company AS company
  SELECT name, domain

Tip #3 — Minimal SELECT in WITH blocks

Only list in SELECT of WITH blocks the properties you actually need. Fewer properties = less data downloaded.

-- ❌ Downloads all contact properties
WITH ASSOCIATED contact AS contacts

-- ✅ Downloads only email and name
WITH ASSOCIATED contact AS contacts
  SELECT email, firstname, lastname

Exception: Properties used in WHERE, REQUIRE, or TAKE ORDER BY are automatically added to the fetch — no need to declare them in SELECT if you don't want them in the result.

Tip #4 — INNER instead of REQUIRE COUNT > 0

WITH INNER ASSOCIATED is strictly equivalent to WITH ASSOCIATED ... REQUIRE COUNT > 0, but more concise and slightly more readable:

-- Both are identical:
WITH INNER ASSOCIATED deal AS deals ...
WITH ASSOCIATED deal AS deals
  REQUIRE COUNT > 0 ...

Tip #5 — SO-FAR periods for your KPIs

For your performance dashboards, LAST-MONTH-SO-FAR is more relevant than LAST-MONTH when the month isn't over:

-- Fair comparison on May 18:
-- THIS-MONTH-SO-FAR  = May 1-18
-- LAST-MONTH-SO-FAR  = April 1-18
-- Both cover exactly 18 days
SELECT dealname, amount
FROM deal
WHERE closedate IN LAST-MONTH-SO-FAR
  AND dealstage = 'closedwon'

Tip #6 — COALESCE for incomplete data

In your dynamic expressions, protect yourself from empty properties with COALESCE:

-- Without COALESCE: deals without a budget are silently excluded
WHERE amount > 0.1 * budget

-- With COALESCE: deals without a budget use 0 as the default value
WHERE amount > 0.1 * COALESCE(budget, 0)

Tip #7 — Structuring a complex query

For a query with multiple WITH blocks, build it in stages:

1. Write the main SELECT ... FROM ... WHERE first and verify the results
2. Add the first WITH block and verify
3. Add REQUIRE clauses and verify that primary objects are correctly filtered
4. Add the other WITH blocks
5. Add TAKE clauses to structure the final result

Tip #8 — Count queries before extraction

Before running a query on a large volume, estimate its size with a simple count query — this saves you from long extractions on unexpected scopes.