Confluence — Sources — Binom Governance

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

Example

Pages created

How many pages were created in the period?

COUNT(*) FROM page WHERE created IN period AND status = current

Grain: Page
Fields: Page.created, Page.space_key, Page.status
Dimensions: space, content_type, author_dept

Confluence Cloud REST content?type=page&created…

Adapt: Filter drafts and templates from intake.

Example

Pages updated

How many pages were updated in the period?

COUNT(DISTINCT page_id) FROM version WHERE when IN period

Grain: Page (distinct)
Fields: Version.when, Version.number, Page.space_key
Dimensions: space, content_type, label

Version history or updated timestamp — avoid double counts.

Adapt: Clarify minor vs major edits and bot users.

Active spaces

How many spaces had content activity in the period?

COUNT(DISTINCT space_key) FROM page WHERE updated IN period

Grain: Space
Fields: Page.space_key, Page.updated
Dimensions: space, content_type

Activity = create/update/comment — lock the definition.

Adapt: Exclude personal and archived spaces.

Comments created

How many comments were created?

COUNT(*) FROM comment WHERE created IN period

Grain: Comment
Fields: Comment.created, Comment.id
Dimensions: space, content_type, author_dept

Unify footer and inline comments.

Adapt: Load body only when engagement text is required.

Attachment count

How many attachments (meta) were added?

COUNT(*) FROM attachment_meta WHERE created IN period

Grain: Attachment meta
Fields: Attachment Meta.id, Attachment Meta.mediaType
Dimensions: space, content_type

Metadata only — skip binaries.

Adapt: Count large binaries and external links separately.

Formula overview

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

Label	Formula	Grain	Fields
Pages created	`COUNT(*) FROM page WHERE created IN period AND status = current`	Page	Page.created, Page.space_key, Page.status
Pages updated	`COUNT(DISTINCT page_id) FROM version WHERE when IN period`	Page (distinct)	Version.when, Version.number, Page.space_key
Active spaces	`COUNT(DISTINCT space_key) FROM page WHERE updated IN period`	Space	Page.space_key, Page.updated
Comments created	`COUNT(*) FROM comment WHERE created IN period`	Comment	Comment.created, Comment.id
Attachment count	`COUNT(*) FROM attachment_meta WHERE created IN period`	Attachment meta	Attachment Meta.id, Attachment Meta.mediaType

Id	Label	Grain	Notes
`space`	Space	space_key	Often exclude personal spaces from active-spaces KPIs.
`content_type`	Content type	page / blogpost / comment	Use REST type field for throughput splits.
`label`	Label	label.name	Normalize taxonomy before the mart (synonyms).
`author_dept`	Author department	Department (external)	Department is often external (HR/IdP) — not a native Confluence dimension.

PII hotspots

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

Entity	Fields	Classification	Stage treatment	Treatment
User / Atlassian account	`accountId, displayName, email, profilePicture`	Workforce	RAW: workforce policy; Curated: separate from customer PII; Mart: aggregates only	Confluence users — email only with legal basis; accountId as key.
Page author / lastModifier	`authorId, ownerId, lastModifiedBy, version.authorId`	Workforce	RAW: workforce policy; Curated: accountId only; Mart: aggregates only	Authors as accountId — no cleartext names in default marts.
Page / blog body	`body.storage, body.atlas_doc_format, excerpt`	Quasi	RAW: rarely load; Curated: drop/redact; Mart: never	Page bodies can contain personal data and secrets — default skip.
Comments / inline comments	`comment.body, inlineComment.text`	Quasi	RAW: rarely load; Curated: drop/redact; Mart: never	Comments — unchecked PII; metadata enough for activity KPIs.
Space permissions / members	`space permissions principals, group memberships, guest access emails`	Workforce	RAW: workforce policy; Curated: aggregates only; Mart: never cleartext	Permissions reveal org structure — tight access; not in public marts.
Attachments / labels with PII	`attachment.title, attachment.downloadLink, label names with emails`	Direct	RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext	Filenames and labels can carry emails/names — inventory them.

DSDR discovery

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

Focus	Notes
Match keys	accountId, email, contentId, spaceId, pageId, external content properties.
Primary objects	Page, blogpost, space, comment, attachment + content properties.
REST content exports	Content REST and space exports copy bodies and author PII.
Personal / restricted spaces	Personal spaces and restricted pages — separate retention and access.

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 Page without space

Problem: Pages without spaceId cannot slice knowledge KPIs by space/team.
Prevent: Always create in space context; validate API create.
Staff must know: Authors: Every page needs a space.
Checks: spaceId IS NULL OR spaceKey IS NULL on page
Fix in source: Required space on create; block orphan content.
Warehouse safety net: Quarantine; exclude from space KPIs.

High Page without author

Problem: Content without authorId breaks ownership and contribution reports.
Prevent: Author from session/API user; import with mapping.
Staff must know: Imports: Always map author — do not leave anonymous.
Checks: authorId IS NULL on current page version
Fix in source: Import mapping; required creator on API.
Warehouse safety net: Unknown-author dim; exclude from contribution KPIs.

Medium lastModified before created

Problem: lastModified < created distorts freshness and edit cycle.
Prevent: Validate migration timestamps; no manual overrides.
Staff must know: Admins: Keep created/modified consistent on migrations.
Checks: lastModified < created
Fix in source: Fix on re-import; version history review.
Warehouse safety net: Flag invalid dates; use created fallback.

Medium Archived space with current pages

Problem: Active content counts from archived spaces distort knowledge inventory.
Prevent: Archive policy: maintain space status; sync content status.
Staff must know: Space admins: Archive means out of active inventory.
Checks: spaceStatus = archived AND pageStatus = current
Fix in source: Archive workflow updates content status.
Warehouse safety net: Exclude archived spaces from active inventory KPIs.

Low Label outside taxonomy

Problem: Free labels without governance break topic dimensions.
Prevent: Label taxonomy / controlled vocabulary.
Staff must know: Authors: Use approved labels only.
Checks: label NOT IN approved taxonomy list
Fix in source: Label macros / governance page; automation cleanup.
Warehouse safety net: Map to Unknown topic; refresh dim.

Medium Duplicate page titles in space

Problem: Same titles in a space hinder search and MDM for knowledge assets.
Prevent: Naming convention; duplicate check before create.
Staff must know: Authors: Search existing page before create.
Checks: COUNT(*) GROUP BY spaceId, lower(title) HAVING COUNT > 1
Fix in source: Rename / merge pages; redirect macros.
Warehouse safety net: Canonical contentId; merge map.

MDM / duplicates 2

Space Space duplicates / rename

Match keys: spaceId, spaceKey, name
Prevent in source: Space key governance; no parallel spaces for same team.
Resolve in warehouse: Golden spaceId; key history map.
Survivorship: Active space with more current pages wins.

Page Page duplicates / forks

Match keys: contentId, spaceId + title, content property external id
Prevent in source: Single source page; use include/excerpt instead of copy.
Resolve in warehouse: Canonical contentId; redirect map.
Survivorship: Latest lastModified in canonical space wins.

Product settings to improve 3

Space settings → Permissions Clarify space access and anonymous/guest policy

Why: Permissions determine extract visibility and PII surface.
How: Permissions review; document extract service account scopes.

Space / content lifecycle Maintain archive and current status consistently

Why: Active inventory KPIs need clean status.
How: Archive playbook; periodic stale-content review.

Labels / taxonomy Controlled label vocabulary

Why: Topic dimensions stay analyzable.
How: Taxonomy page; automation to flag unknown labels.

Metadata: what exists & how to get it 4

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

Space permissions

Where in product: Space settings → Permissions; Admin → Space permissions
How to access: Export permission tables; tag group vs user principals.
Use for: Access inventory, extract scope, workforce graph caution.
Watchouts: Permissions contain workforce IDs — not in public marts.

Content properties

Where in product: REST content properties; apps storing metadata on pages
How to access: Inventory property keys; allowlist for extract.
Use for: External ids, app metadata, PII scan.
Watchouts: Apps often store unexpected PII in properties.

REST content

Where in product: Confluence Cloud REST (content / pages / spaces)
How to access: Content endpoints with expand allowlist; omit bodies by default.
Use for: Extract design, join keys (spaceId, contentId), status fields.
Watchouts: body.storage expand pulls cleartext — only with use case.

Labels

Where in product: Page labels UI; REST labels on content
How to access: Label inventory export; map to taxonomy.
Use for: Topic dimensions, content classification.
Watchouts: Free labels drift — governance required.

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

Table	Description	Grain	Load
`page` Page	Confluence page — fact for created/updated and content throughput.	One page	Load
`space` Space	Space — dimension via space_key; active spaces KPI.	One space	Load
`user` User	Author/editor — accountId join; email is PII.	One user	Load
`comment` Comment	Inline/footer comment — engagement fact with free-text PII.	One comment	Optional
`attachment_meta` Attachment meta	Attachment metadata (no binary) — attachment count.	One attachment meta	Optional
`label` Label	Labels — content taxonomy and filter dimension.	One label on content	Optional
`blogpost` Blogpost	Blog post — content_type variant alongside page.	One blogpost	Optional
`version` Version	Page version — update events and author history.	One version	Optional

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

Entity	Field	Role	Why
Page	`Page.id`	Key	Content join (Cloud REST)
Page	`Page.title`	Dimension	Page title / label
Page	`Page.space_key`	Dimension	Space join
Page	`Page.status`	Dimension	current / trashed / draft
Page	`Page.author`	Dimension	Creator accountId
Page	`Page.created`	Measure	Created time
Page	`Page.updated`	Measure	Last update
Page	`Page.version`	Measure	Version number
Page	`Page.body_storage`	PII	HTML body / PII risk (optional)
Space	`Space.id`	Key	Space join
Space	`Space.key`	Dimension	space_key / label
Space	`Space.name`	Dimension	Space name
Space	`Space.type`	Dimension	global / personal
User	`User.accountId`	Key	User join
User	`User.email`	PII	Email / PII
User	`User.displayName`	PII	Display name / quasi-PII
Comment	`Comment.id`	Key	Comment join
Comment	`Comment.created`	Measure	Comment time
Comment	`Comment.body`	PII	Free text / PII
Attachment Meta	`Attachment Meta.id`	Key	Attachment join
Attachment Meta	`Attachment Meta.mediaType`	Dimension	MIME type
Attachment Meta	`Attachment Meta.fileSize`	Measure	File size
Label	`Label.name`	Dimension	Label name
Label	`Label.content_id`	Key	Content join
Blogpost	`Blogpost.id`	Key	Blog join
Blogpost	`Blogpost.created`	Measure	Publish time
Version	`Version.number`	Key	Version grain
Version	`Version.when`	Measure	Version timestamp
Version	`Version.by`	Dimension	Editor accountId

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

Skip tables / objects

Table	Category	Why
`Page / blog body storage (bulk)`	System	Free text and secrets — metadata often enough for knowledge KPIs.
`Comment bodies`	System	Unstructured PII — activity without text.
`Attachment binaries`	System	Binaries — expensive and sensitive.
`Trash / historical version blobs (bulk)`	System	Historical bodies — retention and volume.

Skip fields / content

Page body cleartext in marts — Default skip — only with documented use case.
Personal space content (bulk) — Private workforce PII.
Attachment content — Cost without KPI value.
Unused content properties (bulk) — DSDR without consumers.
Sandbox / draft test pages — Keep prod knowledge 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 page landing

REST content shape — metadata only; do not load body.storage by default.

-- Warehouse-neutral RAW from Confluence REST extract
CREATE TABLE raw_confluence_page (
  content_id       VARCHAR,
  space_id         VARCHAR,
  space_key        VARCHAR,
  title            VARCHAR,
  status           VARCHAR, -- current, archived, draft, trashed
  author_id        VARCHAR, -- workforce key
  last_modifier_id VARCHAR,
  created_at       TIMESTAMP,
  last_modified_at TIMESTAMP,
  version_number   INT,
  parent_id        VARCHAR,
  _loaded_at       TIMESTAMP
);

RAW space + label landing

Space status and labels for inventory; keep permissions separate and restricted.

CREATE TABLE raw_confluence_space (
  space_id         VARCHAR,
  space_key        VARCHAR,
  space_name       VARCHAR,
  space_type       VARCHAR,
  status           VARCHAR, -- current, archived
  owner_id         VARCHAR,
  _loaded_at       TIMESTAMP
);

CREATE TABLE raw_confluence_label (
  content_id       VARCHAR,
  label_name       VARCHAR,
  _loaded_at       TIMESTAMP
);

Curated

Curated fact confluence page

Knowledge inventory grain — no page body cleartext.

CREATE TABLE curated_fct_confluence_page AS
SELECT
  p.content_id,
  p.space_id,
  p.space_key,
  p.title,
  p.status,
  p.author_id,
  p.last_modifier_id,
  p.created_at,
  p.last_modified_at,
  p.version_number,
  CASE WHEN p.status = 'current' THEN TRUE ELSE FALSE END AS is_current
FROM raw_confluence_page p
WHERE p.space_id IS NOT NULL;

Curated dim space / label

Space without permission principals; labels as topic dim.

CREATE TABLE curated_dim_confluence_space AS
SELECT
  space_id,
  space_key,
  space_name,
  space_type,
  status,
  owner_id
FROM raw_confluence_space;

CREATE TABLE curated_dim_confluence_label AS
SELECT DISTINCT
  label_name
FROM raw_confluence_label
WHERE label_name IS NOT NULL;

Curated measure SELECTs

Current pages, created, edited — filter archived spaces.

-- Current pages in active spaces
SELECT COUNT(*) AS current_pages
FROM curated_fct_confluence_page p
JOIN curated_dim_confluence_space s ON s.space_id = p.space_id
WHERE p.is_current = TRUE
  AND s.status = 'current';

-- Pages created in period
SELECT COUNT(*) AS pages_created
FROM curated_fct_confluence_page
WHERE created_at BETWEEN :period_start AND :period_end;

-- Pages edited in period
SELECT COUNT(*) AS pages_edited
FROM curated_fct_confluence_page
WHERE last_modified_at BETWEEN :period_start AND :period_end;

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 →