Ten questions I could never ask a portal

HubSpot put HubSQL into public beta on the 3rd of September: a SQL endpoint over your CRM data, all hubs, all tiers. The pitch is analytics without building a pipeline first, which is true, and which is also the least interesting thing about it. What I keep coming back to is narrower than that. There is a whole class of question I have wanted to ask a client's portal for most of a decade and have not been able to, and it was never because the answer was expensive to compute. It was because a REST API can only hand you records that exist.

Which sounds obvious written down, and it is, right up until you notice that almost everything worth finding in somebody else's portal is negative space. You walk into an instance somebody has been building in for four or five years, and the finding that changes what they do next is hardly ever a thing that is there and wrong — those get noticed, because somebody is looking at them. It is the thing that should be there and isn't. The closed-won deal nobody ever onboarded. The property three workflows write into that nothing downstream reads. The association everyone in the room assumes exists because the process diagram has an arrow on it.

To find any of those through the REST API you crawl every record of two objects, hold both sets in memory, and diff them yourself. That is completely doable and I have written that script more times than I would like to count, but it costs the better part of a day, which means it only happens on engagements big enough to pay for a day of somebody looking for something that might not be there. Most portals never get asked. In SQL it is one clause.

Three questions about what is not there

1. The closed-won deal nobody delivered

Every business I have worked in has a version of this, and the number is never zero. Somebody marks a deal won, the handoff happens in a conversation, and the record that was supposed to start delivery never gets created. Nobody is being careless — the deal closing and the work starting are two different people's Mondays — but six months later the revenue report and the delivery board disagree and no one can say by how much.

SELECT d.id, d.dealname, d.closedate, c.name AS company
FROM deals d
JOIN companies c ON c.id = d.associated_company_id
WHERE d.dealstage = 'closedwon'
  AND d.closedate > NOW() - INTERVAL '180 days'
  AND NOT EXISTS (
    SELECT 1 FROM tickets t WHERE t.associated_deal_id = d.id
  )
ORDER BY d.closedate DESC

NOT EXISTS is the whole post, really. Everything else here is a variation on it.

2. The same person, three times

HubSpot deduplicates contacts on email address and that key is not configurable, which is a perfectly reasonable default and is also why every company with a shared inbox or a support alias carries duplicate humans it cannot see. Same person, three addresses, three records, three sets of activity history that never add up to one story. A self-join on name and email domain finds them in one pass, and a self-join is not a thing the REST API has.

SELECT LOWER(firstname || ' ' || lastname) AS person,
       SPLIT_PART(email, '@', 2) AS domain,
       COUNT(*) AS records,
       ARRAY_AGG(id) AS ids
FROM contacts
WHERE firstname IS NOT NULL AND lastname IS NOT NULL
GROUP BY 1, 2
HAVING COUNT(*) > 1
ORDER BY records DESC

3. Automation writing into a void

This is the one I most want to exist and least expect to get, because it needs the platform to expose its own configuration as tables rather than only its records. But if it does, it answers the single most common thing I find in a mature portal: workflows faithfully maintaining properties that nothing reads. No report, no list, no view, no other workflow. The automation runs every day, it works perfectly, and it has had no consumer since somebody rebuilt the dashboard in 2023.

SELECT w.name AS workflow, a.property_name, u.last_read_at
FROM workflows w
JOIN workflow_actions a ON a.workflow_id = w.id
LEFT JOIN property_usage u ON u.property_name = a.property_name
WHERE w.enabled = true
  AND (u.last_read_at IS NULL
       OR u.last_read_at < NOW() - INTERVAL '1 year')

Three questions about what has already died

4. Property mortality

A portal at five years old typically has somewhere between three and six hundred custom properties and uses maybe a third of them. Everyone involved knows this and nobody deletes anything, because the cost of deleting a property somebody quietly depends on is much higher than the cost of leaving it, and there has never been a cheap way to tell the two apart. Populated count against total, plus the date anything last wrote to it, separates them in one query.

SELECT property_name,
       COUNT(*) FILTER (WHERE value IS NOT NULL) AS populated,
       COUNT(*) AS total,
       MAX(updated_at) AS last_written
FROM contact_property_values
GROUP BY property_name
HAVING MAX(updated_at) < NOW() - INTERVAL '1 year'
    OR COUNT(*) FILTER (WHERE value IS NOT NULL) = 0
ORDER BY populated ASC

5. The association graph as it is used, not as it was drawn

Association types are cheap to create and nobody removes them either, so the set of associations a portal is configured for and the set it actually uses drift apart steadily. Counting edges by type tells you which relationships the business really runs on, and — more usefully — which ones somebody designed, labelled, documented and then never populated. I have found genuinely load-bearing gaps this way: a link everyone described in the discovery call as existing, with an edge count of zero.

SELECT from_object_type, to_object_type, association_type_id, label,
       COUNT(*) AS edges,
       COUNT(DISTINCT from_id) AS distinct_sources
FROM associations
GROUP BY 1, 2, 3, 4
ORDER BY edges ASC

6. Stages nobody stops in

A pipeline is a claim about how the business sells. Stage transition counts and dwell time test the claim. If two thirds of your deals jump from stage two straight to stage five, stages three and four are not steps, they are decoration, and every forecast built on stage-weighted probability is weighting something that does not happen. This one depends on stage history being exposed as a table, which is the item on this list I would bet against hardest.

SELECT from_stage, to_stage,
       COUNT(*) AS transitions,
       AVG(dwell_days) AS avg_dwell,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY dwell_days) AS p90_dwell
FROM deal_stage_history
GROUP BY 1, 2
ORDER BY transitions DESC

Four questions about what is actually happening

7. Four hops, product to industry

What do we sell, to whom, is not a hard question. It is a four-crawl question — line items to products, line items to deals, deals to companies — and by the time you have paged through all of that and joined it in a spreadsheet the answer has aged. Which is why most companies answer it from memory and are usually a little bit wrong in the direction of whatever they sold most recently.

SELECT c.industry, p.name AS product,
       SUM(li.amount) AS revenue,
       COUNT(DISTINCT d.id) AS deals
FROM line_items li
JOIN products p ON p.id = li.product_id
JOIN deals d ON d.id = li.deal_id
JOIN companies c ON c.id = d.associated_company_id
WHERE d.dealstage = 'closedwon'
GROUP BY 1, 2
ORDER BY revenue DESC

8. Time to value, as a spread rather than an average

Average sales cycle is a number that hides the thing you needed to know. The median tells you what normally happens and the ninetieth percentile tells you where the money is stuck, and the gap between them is the actual finding. Percentiles were not something you could ask an API for, so everybody exported and did it in a spreadsheet, so mostly nobody did it.

SELECT source,
       COUNT(*) AS deals,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days) AS p50,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY days) AS p90
FROM (
  SELECT hs_analytics_source AS source,
         DATE_DIFF('day', createdate, closedate) AS days
  FROM deals
  WHERE dealstage = 'closedwon' AND closedate IS NOT NULL
)
GROUP BY source
ORDER BY p90 DESC

9. Accounts going quiet

Churn is usually visible for months before anyone names it, in the form of engagement that is still happening and is happening less. The read you want is a ratio of one window against the previous one, per account, and a ratio computed at the source is a completely different proposition from one you assemble after pulling every engagement record in the portal.

SELECT c.name,
       COUNT(*) FILTER (
         WHERE e.timestamp > NOW() - INTERVAL '90 days') AS recent,
       COUNT(*) FILTER (
         WHERE e.timestamp BETWEEN NOW() - INTERVAL '180 days'
                              AND NOW() - INTERVAL '90 days') AS prior
FROM companies c
JOIN engagements e ON e.company_id = c.id
GROUP BY c.name
HAVING COUNT(*) FILTER (WHERE e.timestamp > NOW() - INTERVAL '90 days')
     < COUNT(*) FILTER (
         WHERE e.timestamp BETWEEN NOW() - INTERVAL '180 days'
                              AND NOW() - INTERVAL '90 days') * 0.5
ORDER BY prior DESC

10. Margin at the line, rolled up to the job

This is the one I would run first on a client I already work with, because it doubles as a monitor. Cost and price both live on the line item, so margin is arithmetic — but the useful filter is not the low end, it is the impossible end. A job showing a hundred per cent margin has never once meant a hundred per cent margin. It has always meant a cost that did not get written, and the query that finds a real margin problem is the same query that finds the data problem underneath it.

SELECT o.id, o.name,
       SUM(li.amount) AS revenue,
       SUM(li.cogs) AS cost,
       1 - SUM(li.cogs) / NULLIF(SUM(li.amount), 0) AS margin
FROM orders o
JOIN line_items li ON li.order_id = o.id
GROUP BY o.id, o.name
HAVING 1 - SUM(li.cogs) / NULLIF(SUM(li.amount), 0) >= 1.0
    OR 1 - SUM(li.cogs) / NULLIF(SUM(li.amount), 0) < 0.15
ORDER BY revenue DESC

What the ten have in common

Reading them back, only two of the ten are reports in the sense anybody means when they say self-serve analytics. The rest ask about absence: records that should exist and don't, properties nothing reads, associations nobody populated, stages nobody occupies, a margin figure that is arithmetically fine and cannot be true. That is not a coincidence about how I think — it is what a mature system looks like. Things that are present and wrong get found, because somebody is looking at them every day. Things that are missing are invisible by construction, and the tool you had could only return you rows.

So the part of this beta I care about is not that SQL is faster or that an LLM can write it for a business user, both of which are true and neither of which is new. It is that a query language has a way to say 'and this other thing is not there', and until last week the platform did not.

If you have the beta and the schema in front of you, I would like to know which of these the tables actually support — my honest guess is that one, two, four and nine are straightforward, seven and eight depend on how associations are exposed, and three, five and six need HubSpot to have modelled its own configuration as queryable data, which is a much bigger thing to have shipped than an endpoint. If it turns out they did, that is the story here and everybody has been reading the announcement wrong, including me.

The beta announcement, if you want the source rather than my reading of it: HubSQL Public API.