This article "How to calculate Salesforce field utilization with Node.js" by Pablo Gonzalez 🇨🇷 has inspired me to explore another way to estimate field utilization.
🔗 LINK: https://lnkd.in/grkRxqfP
In one client org, all COUNT queries on the Case object were timing out. Since we only needed estimates, the workaround was to request the query plan and use the cardinality estimates instead of exact counts.
The first two screenshots show how to enable the Query Plan button in the Developer Console.
In the article, utilization is calculated as:
👉 query count of records where field != null
👉 divided by total record count
My alternative approach (faster but less precise):
👉 request the query plan for
SELECT count(Id) FROM Object WHERE Field != null
👉 extract:
cardinality (estimated rows matching predicate)
SObject cardinality (estimated total rows)
👉 Utilization ≈ cardinality / SObject cardinality
In my tests, estimates deviated from actual counts by less than 2 percent.
This can be automated to run on each field using the Tooling API (response shown in the 3rd screenshot):
⚙️ /services/data/v62.0/tooling/query/?explain=QUERY
This gives a fast signal on whether a field is mostly populated or mostly blank. It does not work for long text fields, but Pablo's article covers a workaround.
Additional considerations:
🔎 Ideally we should get a distinct count of the values on a field - it might be useful to see if a value is dominating. Required fields won't have nulls but the users might be populating it with a placeholder just to save the record.
🔎 We should also get counts over time - a field might have been just added so a small percentage of records have it populated. Or maybe a field was migrated from other system and/or is no longer populated.
Query plan cardinality is not a replacement for exact counts, but it is cheap and scalable proxy that can give a preliminary distribution insight.
