Google Workspace — Sources — Binom Governance

Copy formulas into the KPI Definition Card, then adapt grain and filters.

Example

Active users

How many non-suspended users are there (snapshot)?

COUNT(*) FROM user WHERE suspended = false

Grain: Active directory user
Fields: User.id, User.suspended, User.orgUnitPath, User.organizations[0].department
Dimensions: org_unit, department

suspended=false; treat archived/deleted users separately.

Adapt: Activity-based: users with login in period (Reports API).

Example

Drive files created

How many Drive files were created in the period?

COUNT(*) FROM drive_file_meta WHERE createdTime IN period

Grain: Drive file meta
Fields: DriveFile.id, DriveFile.createdTime, DriveFile.mimeType
Dimensions: org_unit, department, file_type

Drive API meta or Reports drive usage — never load content.

Adapt: Separate shared drives vs My Drive.

Meet minutes

How many Meet minutes were consumed in the period (Reports)?

SUM(meet_minutes) FROM reports_meet_usage WHERE date IN period

Grain: Meet usage (Reports)
Fields: LoginActivity.time, User.orgUnitPath
Dimensions: org_unit, department, event_type

Via Admin Reports / Meet usage — do not derive from recordings.

Adapt: If Reports field missing: calendar Meet event duration as proxy (weak).

Login failures

How many failed logins occurred in the period?

COUNT(*) FROM login_activity WHERE eventName = 'login_failure' AND time IN period

Grain: Failed login event
Fields: LoginActivity.eventName, LoginActivity.time, LoginActivity.actor.email
Dimensions: org_unit, department, event_type

Reports API login events; aggregate at high volume.

Adapt: Separate suspicious login vs plain failure when available.

Groups count

How many groups exist in the directory?

COUNT(*) FROM group

Grain: Group
Fields: Group.id, Group.email, Group.directMembersCount
Dimensions: org_unit, department

Directory Groups API; clarify dynamic vs security groups in definition.

Adapt: Only groups with members > 0 or with mail traffic.

Formula overview

Same formulas as a table — copy into KPI cards or SQL drafts.

Label	Formula	Grain	Fields
Active users	`COUNT(*) FROM user WHERE suspended = false`	Active directory user	User.id, User.suspended, User.orgUnitPath, User.organizations[0].department
Drive files created	`COUNT(*) FROM drive_file_meta WHERE createdTime IN period`	Drive file meta	DriveFile.id, DriveFile.createdTime, DriveFile.mimeType
Meet minutes	`SUM(meet_minutes) FROM reports_meet_usage WHERE date IN period`	Meet usage (Reports)	LoginActivity.time, User.orgUnitPath
Login failures	`COUNT(*) FROM login_activity WHERE eventName = 'login_failure' AND time IN period`	Failed login event	LoginActivity.eventName, LoginActivity.time, LoginActivity.actor.email
Groups count	`COUNT(*) FROM group`	Group	Group.id, Group.email, Group.directMembersCount

Id	Label	Grain	Notes
`org_unit`	Org unit	orgUnitPath	Primary directory slice; use parent path for rollups.
`department`	Department	organizations[].department	Directory department; often incomplete — combine with OU.
`file_type`	File type	drive_file.mimeType	Group Google Docs MIME vs uploaded binaries.
`event_type`	Event type	calendar.eventType / login eventName	Do not mix calendar eventType and login eventName.

PII hotspots

Inventory direct and quasi identifiers for this source — tag, restrict RAW, hash or drop before marts.

Entity	Fields	Classification	Stage treatment	Treatment
Admin SDK Directory user	`id, primaryEmail, name, phones, organizations, recoveryEmail`	Workforce	RAW: workforce policy; Curated: id only; Mart: aggregates only	Workspace user — primaryEmail/phones only with legal basis; id as key.
Drive / Gmail content bodies	`Drive file content, Gmail message body, attachment data`	Sensitive	RAW: never store; Curated: never; Mart: never	Skip Drive/Gmail bodies — activity/metadata only for KPIs.
Login / Reports activity	`actor.email, ipAddress, login_type, device`	Sensitive	RAW: restrict access; Curated: aggregated / hashed; Mart: no cleartext IPs/emails	Login activity is security-sensitive — restrict; short retention.
Drive activity / sharing	`actor, target, permissionChange, sharedWith`	Workforce	RAW: workforce policy; Curated: ids only; Mart: aggregates only	Drive activity — actor/target as ids; no file bodies.
Org unit / group membership	`orgUnitPath, group members, member emails`	Workforce	RAW: workforce policy; Curated: ids / OU path; Mart: aggregates only	OU and groups for MDM — member emails not in default marts.

DSDR discovery

Where personal data can hide beyond obvious contact fields — warehouse copies included.

Focus	Notes
Match keys	user id, primaryEmail (hashed), orgUnitPath, group id, file id, customer id.
Primary objects	User, org unit, group, Drive activity metadata, login/Reports events.
Admin SDK / Reports / Drive Activity exports	Directory, Reports API and Drive Activity duplicate workforce PII into stages.
Test domain / sandbox copies	Do not mix test domains with prod Workspace marts.

Source-specific guidance: what goes wrong, how to prevent it, what staff must know — plus where metadata lives and how to access it.

Data quality guides 6

Prevent issues in the source first. Warehouse checks are a safety net — not a substitute for staff habits.

High User without org unit

Problem: Users without orgUnitPath break policy inheritance and directory rollups.
Prevent: Provisioning: org unit required; default OU for new users.
Staff must know: Admins: Every user needs an org unit.
Checks: org_unit_path IS NULL AND suspended = FALSE
Fix in source: Assign OU in Admin Console / Directory API.
Warehouse safety net: Unknown OU dim; exclude from OU KPIs.

High Suspended user still in groups

Problem: Suspended users in groups distort access and distribution lists.
Prevent: Lifecycle: group cleanup on suspend; archive instead of orphan.
Staff must know: IAM: Suspend ≠ membership cleanup.
Checks: suspended = TRUE AND group_membership_count > 0
Fix in source: Remove group memberships; automate leaver workflow.
Warehouse safety net: Flag stale_membership; exclude from active entitlement KPIs.

Medium Login activity without user

Problem: Login events without resolvable user_id break security joins.
Prevent: Reports API: map actor email/id; deleted users window.
Staff must know: Security: Separate orphan logins vs deleted users.
Checks: login.user_id IS NULL OR user_id NOT IN dim_user
Fix in source: Re-sync Directory; widen soft-delete lookback.
Warehouse safety net: Unknown-user dim; exclude from per-user KPIs.

Medium Drive activity without file target

Problem: Activity without file_id makes collaboration facts unusable — still never load bodies.
Prevent: Drive Activity API: persist target driveItem id.
Staff must know: Data: Activity metadata only — no file exports.
Checks: activity.file_id IS NULL AND activity_type IN ('edit','create','share')
Fix in source: Re-fetch activity with targets.
Warehouse safety net: Quarantine; exclude from file-level KPIs.

Medium Group without owner

Problem: Groups without owner weaken access governance and ownership reviews.
Prevent: Group create: owner required; periodic owner attestation.
Staff must know: Admins: Every group needs at least one owner.
Checks: owner_count = 0 AND is_deleted = FALSE
Fix in source: Assign owners in Admin Console.
Warehouse safety net: Flag ownerless_group; exclude from healthy coverage.

Medium Duplicate primaryEmail in extract

Problem: Duplicate primaryEmail distort directory counts and joins.
Prevent: Directory unique email; separate alias vs primary.
Staff must know: Admins: Do not treat aliases as a second primaryEmail.
Checks: COUNT(*) GROUP BY primary_email HAVING COUNT > 1
Fix in source: Merge/fix in Directory; re-extract.
Warehouse safety net: Golden user id; email-hash merge map.

MDM / duplicates 2

User User duplicates / rehire

Match keys: id, primaryEmail, employeeId, externalIds
Prevent in source: HR feed as source of truth; suspend instead of recreate.
Resolve in warehouse: Golden user id; email-hash merge map.
Survivorship: Active (not suspended) user with latest login activity wins.

Org unit Org unit hierarchy drift

Match keys: orgUnitId, orgUnitPath, parentOrgUnitPath
Prevent in source: Controlled OU moves; document rename policy.
Resolve in warehouse: Canonical orgUnitId; SCD2 orgUnitPath.
Survivorship: Current OU path wins; historical paths as aliases.

Product settings to improve 3

Admin Console → Organizational units Document OU hierarchy and policy inheritance

Why: Directory rollups and user-without-OU DQ stay controllable.
How: Maintain OU tree; default OU for provisioning.

Reports → Login / Audit Reports API extract allowlist; short retention for IPs

Why: Login activity KPIs without unnecessary security PII.
How: Allowlist event fields; hash IPs; omit mail/drive bodies.

Drive → Sharing settings External sharing limits; activity metadata only in warehouse

Why: Collaboration KPIs without file/mail bodies.
How: OU sharing policies; extract Drive Activity targets only.

Metadata: what exists & how to get it 4

Source-native catalogs for schema, picklists/properties and access paths — not a full dump.

Admin SDK Directory

Where in product: Admin SDK → Directory API (users, groups, orgunits)
How to access: Inventory user/group/OU fields; PII allowlist.
Use for: Directory dims, MDM keys, entitlement facts.
Watchouts: primaryEmail/phones — not in public marts.

Reports API

Where in product: Admin SDK → Reports API (login, admin, drive activity usage)
How to access: Allowlist event types; tag sensitive fields.
Use for: Login facts, admin audit, usage KPIs.
Watchouts: IP/email cleartext — restrict; short retention.

Drive Activity API

Where in product: Google Drive Activity API → activity records
How to access: Document actor/target/action metadata — no file bodies.
Use for: Collaboration activity facts, sharing change signals.
Watchouts: Never put Drive file export/content in warehouse.

Org units

Where in product: Admin Console → Directory → Organizational units
How to access: Export OU tree and policy bindings.
Use for: OU dims, user-without-OU DQ, policy coverage.
Watchouts: OU renames — SCD2; keep historical paths.

Object overview with short descriptions and load guidance — not a full schema dump.

Table	Description	Grain	Load
`user` User	Directory user — primaryEmail, name, orgUnit; workforce PII.	One user (id / primaryEmail)	Load
`group` Group	Workspace group — email, members count; membership join.	One group (id / email)	Load
`drive_file_meta` Drive file meta	Drive file meta — mimeType, createdTime; do not load content.	One Drive file (id)	Load
`calendar_event_meta` Calendar event meta	Event meta — start/end, eventType; titles may be sensitive.	One calendar event (id)	Optional
`gmail_meta` Gmail meta	Gmail message meta — ids, labels, dates; never body/attachments by default.	One message (id) meta	Optional
`login_activity` Login activity	Reports API login — success/failure, timestamp; sample at high volume.	One login event (or daily agg)	Load
`chrome_device` Chrome device	ChromeOS device — serial, status, OU join.	One Chrome device (deviceId)	Optional
`org_unit` Org unit	Directory OU — path/hierarchy for user and policy dim.	One org unit (orgUnitPath)	Load

Most important fields to load early — not a full schema dump.

Entity	Field	Role	Why
User	`User.id`	Key	Directory user ID
User	`User.primaryEmail`	PII	Primary email / PII
User	`User.name.fullName`	PII	Name / PII
User	`User.suspended`	Dimension	Suspended vs active
User	`User.orgUnitPath`	Dimension	OU path / org dim
User	`User.creationTime`	Measure	User created
User	`User.organizations[0].department`	Dimension	Department (directory)
Group	`Group.id`	Key	Group join
Group	`Group.email`	Dimension	Group email
Group	`Group.directMembersCount`	Measure	Members count
DriveFile	`DriveFile.id`	Key	Drive file join
DriveFile	`DriveFile.mimeType`	Dimension	File type / MIME
DriveFile	`DriveFile.createdTime`	Measure	File created
DriveFile	`DriveFile.modifiedTime`	Measure	File modified
DriveFile	`DriveFile.owners[].emailAddress`	PII	Owner email / PII
DriveFile	`DriveFile.name`	Dimension	File name (meta; may be sensitive)
CalendarEvent	`CalendarEvent.id`	Key	Event join
CalendarEvent	`CalendarEvent.summary`	PII	Title often sensitive
CalendarEvent	`CalendarEvent.eventType`	Dimension	default / outOfOffice / focusTime …
CalendarEvent	`CalendarEvent.start.dateTime`	Measure	Event start
CalendarEvent	`CalendarEvent.end.dateTime`	Measure	Event end / duration
GmailMeta	`GmailMeta.id`	Key	Message ID (meta)
GmailMeta	`GmailMeta.threadId`	Dimension	Thread join
GmailMeta	`GmailMeta.internalDate`	Measure	Message time
GmailMeta	`GmailMeta.labelIds`	Dimension	INBOX/SENT/… labels
LoginActivity	`LoginActivity.id`	Key	Login event ID / agg key
LoginActivity	`LoginActivity.time`	Measure	Login timestamp
LoginActivity	`LoginActivity.actor.email`	PII	Actor email / PII
LoginActivity	`LoginActivity.eventName`	Dimension	login_success / login_failure …
ChromeDevice	`ChromeDevice.deviceId`	Key	Chrome device join
ChromeDevice	`ChromeDevice.status`	Dimension	ACTIVE / DISABLED …
ChromeDevice	`ChromeDevice.orgUnitPath`	Dimension	Device OU
OrgUnit	`OrgUnit.orgUnitId`	Key	OU join
OrgUnit	`OrgUnit.orgUnitPath`	Dimension	OU path
OrgUnit	`OrgUnit.name`	Dimension	OU name

System and noise objects/fields you typically should not sync into the warehouse by default.

Skip tables / objects

Table	Category	Why
`Gmail message / attachment bodies`	System	Mail bodies — never land; metadata/counts at most.
`Drive file content / export blobs`	System	File content — expensive and sensitive; activity metadata enough.
`Login report IP cleartext archives (bulk)`	System	Security PII — aggregate or hash.
`Vault / eDiscovery export payloads`	System	Legal holds — separate from analytics; never default load.

Skip fields / content

Drive / Gmail content bodies — Skip bodies — activity/metadata KPIs only.
primaryEmail / phone cleartext in marts — user id is enough for directory KPIs.
Login IP / device cleartext (bulk) — Security PII — aggregated or hashed.
Vault / eDiscovery payloads — Legal — not in default analytics stages.
Test domain users in prod marts — Keep prod Workspace marts clean.

Warehouse-neutral examples for landing (RAW) and curated models — copy and adapt to your dialect. Not vendor SOQL/API DDL.

Landing (RAW)

RAW user + org unit landing

Admin SDK Directory shape — primaryEmail = workforce PII; no mail/drive bodies.

-- Warehouse-neutral RAW from Google Admin SDK Directory extract
CREATE TABLE raw_gws_user (
  user_id              VARCHAR,
  primary_email        VARCHAR, -- workforce PII
  org_unit_path        VARCHAR,
  suspended            BOOLEAN,
  created_at           TIMESTAMP,
  _loaded_at           TIMESTAMP
);

CREATE TABLE raw_gws_org_unit (
  org_unit_id          VARCHAR,
  org_unit_path        VARCHAR,
  parent_org_unit_path VARCHAR,
  _loaded_at           TIMESTAMP
);

RAW login + Drive activity landing

Reports/Drive Activity metadata — login restricted; no file/mail bodies.

CREATE TABLE raw_gws_login_activity (
  event_id             VARCHAR,
  user_id              VARCHAR,
  login_type           VARCHAR,
  is_suspicious        BOOLEAN,
  created_at           TIMESTAMP,
  ip_address           VARCHAR, -- security-sensitive
  _loaded_at           TIMESTAMP
);

CREATE TABLE raw_gws_drive_activity (
  activity_id          VARCHAR,
  actor_user_id        VARCHAR,
  file_id              VARCHAR,
  activity_type        VARCHAR, -- create | edit | share | ...
  created_at           TIMESTAMP,
  _loaded_at           TIMESTAMP
  -- no drive file content / export blob
);

CREATE TABLE raw_gws_group_membership (
  group_id             VARCHAR,
  user_id              VARCHAR,
  role                 VARCHAR,
  _loaded_at           TIMESTAMP
);

Curated

Curated fact gws login

Login activity KPI — no IP/email cleartext.

CREATE TABLE curated_fct_gws_login AS
SELECT
  l.event_id,
  l.user_id,
  l.login_type,
  l.is_suspicious,
  l.created_at
  -- omit ip_address from default analytics facts
FROM raw_gws_login_activity l
WHERE l.user_id IS NOT NULL;

Curated dim user / drive activity fact

User without email; Drive activity without bodies; suspended-in-groups signal.

CREATE TABLE curated_dim_gws_user AS
SELECT
  user_id,
  org_unit_path,
  suspended,
  created_at
  -- omit primary_email from default analytics dims
FROM raw_gws_user;

CREATE TABLE curated_fct_gws_drive_activity AS
SELECT
  a.activity_id,
  a.actor_user_id,
  a.file_id,
  a.activity_type,
  a.created_at
FROM raw_gws_drive_activity a
WHERE a.file_id IS NOT NULL;

CREATE TABLE curated_fct_gws_group_membership AS
SELECT
  m.group_id,
  m.user_id,
  m.role,
  u.suspended,
  CASE WHEN u.suspended = TRUE THEN TRUE ELSE FALSE END AS is_suspended_member
FROM raw_gws_group_membership m
JOIN raw_gws_user u ON u.user_id = m.user_id;

Curated measure SELECTs

Logins, Drive edits, users without OU, suspended in groups — adapt period filters.

-- Login events in period
SELECT COUNT(*) AS login_events
FROM curated_fct_gws_login
WHERE created_at BETWEEN :period_start AND :period_end;

-- Drive edit/create activities in period
SELECT COUNT(*) AS drive_edit_events
FROM curated_fct_gws_drive_activity
WHERE activity_type IN ('edit', 'create')
  AND created_at BETWEEN :period_start AND :period_end;

-- Active users without org unit
SELECT COUNT(*) AS users_without_ou
FROM curated_dim_gws_user
WHERE suspended = FALSE
  AND (org_unit_path IS NULL OR org_unit_path = '');

-- Suspended users still in groups
SELECT COUNT(DISTINCT user_id) AS suspended_users_in_groups
FROM curated_fct_gws_group_membership
WHERE is_suspended_member = TRUE;

Tools

Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.

Playbooks

Related governance stories for modelling, PII and measure design around this source.

Vendor help on Resources Official docs, governance and learning paths for this source. Open Resources →