automationauditSaaS

Audit Scripts to Find Underused SaaS: SQL & API Recipes for Usage Data

UUnknown

2026-02-15

9 min read

Run SQL and API recipes to find underused SaaS seats, measure license utilization, and automate reclamation with Zapier/Make in 2026.

Hook: Cut the SaaS Waste — Find underused seats and overlapping apps with scripts that actually work

If your bill run looks like a subscription graveyard and teams complain about too many logins, you need an evidence-backed audit — not opinions. This guide gives you ready-to-run SQL queries and API recipes for the most common CRMs and finance apps so you can quantify underuse, measure license utilization, and identify overlap for consolidation or cost savings in 2026.

Why this matters in 2026: new levers, new expectations

In late 2025 and early 2026 vendors accelerated two trends: (1) more granular telemetry and metering APIs, and (2) higher adoption of SCIM and SSO for provisioning. That means you can now retrieve accurate activity and license data more reliably than ever. Meanwhile, rising vendor pricing and continued tool sprawl (see industry reporting on marketing stacks and tool bloat) make audits a top priority for operations teams.

Quick stat: Vendors increasingly report license-level telemetry in their REST APIs; integrating that data with your internal event logs lets you cut subscriptions confidently and show ROI.

Audit approach — backbone you should follow

Extract authoritative license lists from vendor admin APIs (licenses, seats, purchases).
Extract activity telemetry — logins, API calls, key events (emails sent, invoices created, deals updated) for the same user identities.
Normalize identifiers (email is usually best) and sync to a central data warehouse.
Run analytics (SQL) to compute active vs underused seats, overlap by email, and cost per active user.
Automate alerts and approval workflows (Zapier/Make/CI) for each candidate subscription to offboard.

Key definitions (use these consistently)

Active user — user with at least one meaningful action in the last 90 days (customizable).
License utilization — percentage of purchased seats assigned and active.
Underused seat — assigned seat with no meaningful activity in the last X days (commonly 30/60/90).
Overlap — users with access across multiple tools providing similar functionality (CRM vs. helpdesk vs. sales engagement).

Data model — what to store centrally

Store these normalized tables in your warehouse (BigQuery, Snowflake, Postgres):

users(user_id, email, name, company, source_app)
licenses(app, license_id, seat_count, assigned_count, cost_per_month, billing_cycle)
usage_events(event_id, user_email, app, event_type, event_timestamp, metadata_json)
app_inventory(app, category, vendor, subscription_id)

SQL recipes — detect underuse and overlap

Below are SQL queries you can run against the normalized model. Adjust time windows and event definitions to match your business.

1) Active users in the last 90 days

SELECT
  app,
  COUNT(DISTINCT user_email) AS active_users_90d
FROM usage_events
WHERE event_timestamp >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY app
ORDER BY active_users_90d DESC;

2) License utilization per app (assigned and active)

SELECT
  l.app,
  l.seat_count,
  l.assigned_count,
  COALESCE(a.active_users_90d, 0) AS active_users_90d,
  ROUND( (COALESCE(a.active_users_90d,0)::numeric / NULLIF(l.seat_count,0)) * 100, 2) AS util_pct
FROM licenses l
LEFT JOIN (
  SELECT app, COUNT(DISTINCT user_email) AS active_users_90d
  FROM usage_events
  WHERE event_timestamp >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY app
) a ON a.app = l.app
ORDER BY util_pct ASC;

3) Identify underused assigned users (no events in 60 days)

Assumes you have an assigned_users table or assigned status in your license export.

SELECT
  au.app,
  au.user_email,
  u.name,
  MAX(e.event_timestamp) AS last_event
FROM assigned_users au
LEFT JOIN usage_events e
  ON au.user_email = e.user_email AND au.app = e.app
LEFT JOIN users u ON u.email = au.user_email
GROUP BY au.app, au.user_email, u.name
HAVING MAX(e.event_timestamp) IS NULL
   OR MAX(e.event_timestamp) < CURRENT_DATE - INTERVAL '60 days'
ORDER BY au.app, last_event NULLS FIRST;

4) Overlap matrix between apps (top overlaps by count)

WITH distinct_access AS (
  SELECT DISTINCT user_email, app
  FROM assigned_users
)
SELECT a.app AS app_a, b.app AS app_b, COUNT(DISTINCT a.user_email) AS shared_users
FROM distinct_access a
JOIN distinct_access b ON a.user_email = b.user_email AND a.app <> b.app
GROUP BY app_a, app_b
ORDER BY shared_users DESC;

API recipes — extract license and usage data from popular SaaS

Below are practical examples (curl and Python) for common CRMs and finance apps. Use OAuth where required and respect rate limits. Replace placeholders with real tokens and tenant IDs.

Salesforce exposes a REST API for LoginHistory and UserLicense. Use bulk API for large tenants.

# curl: Get user licenses
curl -H "Authorization: Bearer $SF_TOKEN" \
  "https://your-instance.salesforce.com/services/data/v57.0/sobjects/UserLicense/"

# SOQL: recent logins
# Run via REST /query endpoint
GET /services/data/v57.0/query?q=SELECT+UserId,+LoginTime+FROM+LoginHistory+WHERE+LoginTime>=2025-10-01T00:00:00Z

Python pattern (requests):

import requests
BASE = 'https://your-instance.salesforce.com'
headers = {'Authorization': f'Bearer {SF_TOKEN}'}
# Query LoginHistory
r = requests.get(f"{BASE}/services/data/v57.0/query", params={'q': "SELECT UserId, LoginTime FROM LoginHistory WHERE LoginTime >= 2025-10-01T00:00:00Z"}, headers=headers)
data = r.json()
# Normalize UserId to email via User object lookups

HubSpot — users & activity

HubSpot provides endpoints for users and events. Use the Events API for activity and the CMS/CRM APIs for owners and assignments.

# Get users
curl -H "Authorization: Bearer $HUBSPOT_API_KEY" \
  "https://api.hubapi.com/settings/v3/users"

# Get engagement events (example)
curl -H "Authorization: Bearer $HUBSPOT_API_KEY" \
  "https://api.hubapi.com/engagements/v1/engagements/paged?limit=250"

# List users and last login
curl -u $ZENDESK_EMAIL/token:$ZENDESK_TOKEN \
  "https://your_subdomain.zendesk.com/api/v2/users.json?role[]=end-user&role[]=agent"

# User object includes last_login_at

QuickBooks Online (QBO) — invoices and activity

QBO's API can show invoices created by users and company-level seat purchases. Useful metrics: users creating transactions within X days.

# Query invoices (OAuth2 token)
curl -H "Authorization: Bearer $QBO_TOKEN" \
  "https://sandbox-quickbooks.api.intuit.com/v3/company/$COMPANY_ID/query?query=select+Id,CustomerRef,TxnDate+from+Invoice+where+TxnDate+>='2025-10-01'"

Xero — users and connections

# Get users
curl -H "Authorization: Bearer $XERO_TOKEN" \
  "https://api.xero.com/api.xro/2.0/Users"

# Xero user object includes 'IsActive' and last login metadata via Audit API

Pagination, rate limits and delta loads

Use incremental / delta endpoints where available (e.g., Salesforce Bulk/CDC, HubSpot incremental events). Consider edge message brokers or message-based syncs for resilient delta loading.
Respect rate limits: implement exponential backoff and track cursor positions.
Store ETags or Last-Modified headers to make loads efficient; caching and conditional requests are discussed in practical caching strategies.

Authentication patterns

Prefer OAuth2 + refresh tokens for long-running jobs. For enterprise apps, request admin-level service accounts and SCIM provisioning access where possible. SCIM can also be used to validate assigned users vs your IdP.

Integration & automation recipes (Zapier / Make / CI pipelines)

Practical automation patterns to keep your audit current.

Recipe A — nightly inventory sync to BigQuery (Make / Integromat)

Make scenario: HTTP > iterate over your app list.
For each app: call license API, call recent events API, transform to normalized JSON.
Write to staging bucket (GCS) then load to BigQuery via native modules — or use an edge-backed message broker to smooth spikes.
Trigger a db procedure to run utilization calculations and emit Slack report.

Recipe B — low-friction Zapier alert for underused seats

Zap trigger: New row in a Google Sheet (exported SQL result or webhook from your warehouse).
Zap filter: last_activity < 60 days.
Zap action: Send approval request to managers (Slack or Gmail) with buttons to reclaim seat or mark as exception. For secure mobile approvals and contract notifications, consider channels beyond email like RCS and secure mobile channels.

Recipe C — provisioning & souped-up reporting using SCIM + SSO

Enable SCIM in apps that support it. Use SCIM captures to maintain an authoritative assigned_users table. Combine with SSO logs (IdP) to confirm last authentication timestamps.

Practical governance rules and thresholds (ops playbook)

Default rule: reclaim seats with no activity in 60–90 days.
Exceptions: contractors, seasonal users (store tag in users table).
Approval flow: owner gets 7 days to contest before seat is unassigned.
Measure savings: report cost saved and reduction in tool fragmentation quarterly.

Example: Quick analysis — small case study

We audited a 150-seat sales stack and found:

Salesforce: 150 seats purchased, 120 assigned, 85 active in 90 days (utilization 57%).
HubSpot Sales Hub: 100 seats purchased, 90 assigned, 40 active in 90 days (utilization 40%).
Overlap: 70 users had both Salesforce and HubSpot access; 35 of those were inactive in HubSpot.

Action: reclaimed 30 HubSpot seats and migrated key automations into Salesforce, saving ~$18k/year. The SQL and API recipes above were used to generate the owner approval list and to automate offboarding via SCIM deprovisioning.

Privacy, compliance and security considerations

Limit audit access to designated admins and store tokens securely (vault). Rotate tokens periodically and consider security programs such as running a bug bounty or coordinated disclosure to validate your defenses.
Avoid pulling PII unnecessarily — prefer emails (hashed at rest) where possible. Document consent and retention, especially for EU/UK users (GDPR/UK-GDPR) and other jurisdictional rules updated through 2025–2026. A privacy policy template can help standardize internal practices.
Store tokens in a vault and rotate them; vet telemetry vendors using independent trust-score frameworks.

Advanced strategies and 2026 trends to adopt

AI-assisted prioritization: Use machine learning to rank candidates to offboard by combining utilization, overlap, cost, and owner sentiment. When using AI, also adopt controls to reduce bias and ensure fair decisions.
Behavioral signals: Move beyond logins — track MAUs for key actions that show value (opportunity creation, invoices posted, tickets resolved). Combine these signals with robust edge and cloud telemetry where relevant.
Automated contract gating: Use audit outputs to prevent auto-renewals for subscriptions that drop below utilization thresholds.
Vendor metering APIs: Leverage vendor metered billing data to map direct spend to feature consumption — this helps when deciding whether to downgrade plans.

Common pitfalls and how to avoid them

Misreading activity: Frequent logins don't equal value. Map events to business outcomes.
Under-assigning thresholds: Blanket 30-day rules can hit valid inactive-but-critical seats. Include tags and owner reviews.
One-off automations: Manual exports are brittle. Automate delta loads and centralize reporting; resilient patterns include message brokers and cloud-native orchestration discussed in cloud hosting and orchestration reviews.

Implementation checklist — get started this week

Create a central project in your warehouse (dataset and tables from the Data model).
Enable API access for top 6 apps (Salesforce, HubSpot, Zendesk, Slack, QuickBooks, Xero).
Run the provided API scripts to ingest licenses and recent events into staging.
Run the SQL queries to produce an initial underused seat list and overlap matrix.
Automate approvals with Zapier/Make and schedule monthly re-checks.

Final takeaways

In 2026, you have better telemetry and provisioning hooks than ever to make evidence-based cuts to your SaaS stack. Use the SQL recipes to quantify utilization, the API recipes to extract vendor data, and automation to make the process repeatable and low-friction. Accurate audits let you reduce cost, simplify onboarding, and improve productivity rather than guess.

Call to action

Ready to run a pilot? Export your top 5 apps' admin tokens and run the provided API calls into a temporary dataset this week. If you want a template that wires these scripts into BigQuery and a Zapier approval flow, request our Ops Audit Starter Pack — we’ll share a sample project and a 30-day implementation checklist to cut unused spend and speed consolidation.

Unknown

Contributor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.