The most useful office security kpis BigQuery metrics measure whether physical access is reviewed, visitors are logged, security defects are resolved, and required office protections remain operational. For ISO 27001 control 7.3, track a small set of repeatable percentages, overdue counts, and time-to-remediate measures that show physical security for offices, rooms, and facilities has been designed and is continuing to work. BigQuery gives a sole IT administrator one reporting source instead of relying on screenshots, spreadsheets, and memory at audit time.
Why do metrics matter for ISO 27001 control 7.3?
ISO 27001 control 7.3 requires an organization to design and implement physical security for offices, rooms, and facilities. An access-control policy, a visitor sign-in process, and a locked server closet are useful controls, but an auditor will also want evidence that they are maintained. Metrics turn those operational activities into evidence of ongoing control performance.
For a sole IT administrator, the goal is not to create a facilities security operations center. It is to answer practical questions quickly: Were departed employees removed from building access? Did every visitor receive a host and badge? Are broken door locks or cameras being resolved? Is the office access list reviewed on schedule? A monthly KPI report makes exceptions visible before they become an audit finding or an actual physical-security problem.
BigQuery is especially useful when the data already lives in separate places: access-control exports from Kisi, Brivo, or HID; visitor records from Envoy; identity data from Google Workspace; and work orders from Jira Service Management or a facilities ticketing system. Load lightweight exports into one dataset, retain the source files, and calculate the same measures every month.
Which office security kpis BigQuery metrics should you track?
Start with six KPIs. They provide coverage across access authorization, visitor management, environmental and facility safeguards, and corrective action. Set targets that fit your organization’s risk level, but document any exception and its owner rather than silently excluding it from the calculation.
| KPI | Definition | Suggested target | BigQuery data source |
|---|---|---|---|
| Active access badges matched to active workers | Percentage of enabled permanent access credentials assigned to a current employee or approved contractor in the identity roster. | 100%; investigate every unmatched active badge within 1 business day. | Monthly access-control credential export joined to Google Workspace Directory user or contractor roster. |
| Leaver access revocation timeliness | Percentage of departing workers whose physical access was disabled by their termination effective date. | 100%; zero overdue revocations. | HR termination feed or approved offboarding list, access-control audit events, and Google Workspace offboarding record. |
| Quarterly access review completion | Percentage of offices, restricted rooms, and access groups with a documented owner review completed during the quarter. | 100% by the review due date. | Access review register in Google Sheets, AppSheet, or Jira; access-group and door-permission exports. |
| Visitor record completeness | Percentage of visitor entries containing visitor name, visit date and time, host, sign-in status, and sign-out status or documented exception. | At least 98%; review incomplete records weekly. | Envoy, Proxyclick, or reception sign-in CSV export loaded to BigQuery. |
| Open physical-security issues past SLA | Count of open tickets for doors, locks, alarms, cameras, access readers, server-room controls, or reception safeguards that exceed their assigned resolution SLA. | 0 critical issues past SLA; fewer than 3 noncritical issues past SLA. | Jira Service Management, ServiceNow, or facilities ticket export with priority, status, and due date. |
| Mean time to remediate physical-security defects | Average elapsed hours from ticket creation to verified resolution for physical-security issues, reported by priority. | Critical: under 24 hours; high: under 5 business days. | Facilities or security ticket history, including created, resolved, priority, and verification fields. |
Do not treat the targets as universal ISO requirements. ISO 27001 does not prescribe “98% visitor completeness” or a 24-hour repair window. Your organization establishes thresholds based on office size, restricted areas, contractual obligations, and risk assessment. What matters is that the targets are approved, consistently measured, and used to trigger action.
How often should you report physical-security KPIs?
Use a layered cadence that matches the effort you can realistically sustain. Review urgent exceptions weekly, publish a compact KPI summary monthly, and perform the formal access review quarterly. This keeps daily operational noise out of leadership reporting while ensuring that overdue access removal or a failed lock does not wait for the end of the quarter.
- Weekly exception review: unmatched active badges, overdue leaver revocations, critical facility tickets, and incomplete visitor records from the prior week.
- Monthly operating report: current KPI values, prior-month comparison, target status, top exceptions, and remediation owner/date.
- Quarterly control attestation: completed access reviews, evidence of approved exceptions, trend commentary, and confirmation that source exports were retained.
- Annual management review input: twelve-month trends, recurring office-security issues, resource needs, and changes to physical-security risk.
A one-page monthly report is enough for many small organizations. Include the reporting period, metric definition, target, actual result, trend arrow, exception count, and a plain-language action. Save the report as a PDF in a controlled Google Drive folder, together with the source exports or query results used to produce it. That package is far more defensible than recreating evidence after an auditor requests it.
How can you build an office-security KPI dashboard in Google Cloud?
Create a dedicated BigQuery dataset such as security_compliance, then load normalized tables for credentials, worker status, visitor logs, access reviews, and facility tickets. For a small office, scheduled CSV loads from a secured Google Cloud Storage bucket are adequate. If your access-control or visitor platform has an API, use Cloud Run or Cloud Functions to retrieve daily records and write them to BigQuery. Restrict the dataset because badge identifiers, visitor names, and access events are sensitive personal data.
Keep raw records separate from reporting views. For example, retain vendor exports in security_compliance_raw and build cleaned, access-controlled views in security_compliance_reporting. Set partition expiration deliberately; visitor data may need a shorter retention period than access-review evidence. Confirm retention periods with your privacy and legal requirements before enabling automatic deletion.
The following query calculates the active-badge matching KPI for the current reporting date. It assumes credential records contain an enabled status and a user email, while the worker roster identifies active employees and approved contractors.
WITH active_badges AS (
SELECT
credential_id,
LOWER(user_email) AS user_email
FROM `project.security_compliance.access_credentials`
WHERE credential_status = 'enabled'
AND credential_type = 'permanent'
),
active_workers AS (
SELECT LOWER(primary_email) AS user_email
FROM `project.security_compliance.worker_roster`
WHERE worker_status IN ('active_employee', 'active_contractor')
)
SELECT
CURRENT_DATE() AS report_date,
COUNT(*) AS active_badges,
COUNTIF(w.user_email IS NOT NULL) AS matched_badges,
COUNTIF(w.user_email IS NULL) AS unmatched_badges,
ROUND(100 * SAFE_DIVIDE(COUNTIF(w.user_email IS NOT NULL), COUNT(*)), 2)
AS badge_match_percentage
FROM active_badges b
LEFT JOIN active_workers w
ON b.user_email = w.user_email;Schedule the query in BigQuery after the daily imports complete, writing results to a partitioned kpi_daily_snapshot table. Connect Looker Studio to the reporting dataset and build a simple dashboard: scorecards for current values, a monthly trend chart, a table of failed targets, and a filtered exception list. Use a report-date control so you can reproduce what leadership or an auditor saw for a specific month.
For the most useful office-security KPI reporting, include data-quality checks as well. Count records missing employee email, ticket priority, visitor host, or resolution date. A dashboard that reports 100% compliance using incomplete source data is worse than no dashboard because it creates false assurance.
How should you present these metrics to leadership?
Leadership needs a decision-oriented summary, not a tour of your SQL. Lead with the overall conclusion: whether physical office protections are operating within the organization’s approved thresholds. Then identify only the exceptions that need funding, an owner decision, or risk acceptance.
- State the result: “Five of six physical-security KPIs met target in June; two former contractor badges were disabled one day late.”
- Explain the risk: “Late revocation leaves unnecessary access to the office and restricted equipment areas.”
- Show the corrective action: “The offboarding workflow now creates an access-control task automatically, owned by IT.”
- Ask for a decision when needed: “Approve replacement of the unreliable rear-door reader, which has generated four high-priority tickets this quarter.”
Use trends carefully. A rise in reported door-reader defects may indicate worsening equipment, but it could also show that staff are finally using the ticket process. Pair the number with context, evidence, and a clear statement of residual risk. That approach demonstrates that ISO 27001 control 7.3 is actively managed rather than treated as a one-time office setup task.
Set up your first scheduled BigQuery snapshot this week by loading the current access-badge export and comparing it with your active Google Workspace user roster.
