Slack — Sources — Binom Governance

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

Example

Messages sent

How many messages were sent (without loading bodies)?

COUNT(*) FROM message_meta WHERE ts IN period AND subtype IS NULL AND is_bot = false

Grain: Message meta
Fields: Message.channel_id, Message.ts, Message.subtype, User.is_bot
Dimensions: channel_type, team, is_bot

Many orgs do not land message text — meta from Conversations History / Analytics APIs.

Adapt: Lock thread parents vs all replies and DM exclusion.

Example

Active channels

How many channels had messages in the period?

COUNT(DISTINCT channel_id) FROM message_meta WHERE ts IN period

Grain: Channel
Fields: Message.channel_id, Message.ts, Channel.is_private
Dimensions: channel_type, team

Filter archived and shared channels.

Adapt: Define a minimum message threshold for “active”.

Files shared

How many files were shared (meta)?

COUNT(*) FROM file_meta WHERE created IN period

Grain: File meta
Fields: File Meta.id, File Meta.created, File Meta.filetype
Dimensions: channel_type, team, is_bot

files.list / events — do not store binaries.

Adapt: Count external shares and snippets separately.

Reactions count

How many reactions were given?

SUM(count) FROM reaction WHERE message_ts IN period

Grain: Reaction
Fields: Reaction.name, Reaction.count
Dimensions: channel_type, team

Engagement possible without message text.

Adapt: Clarify unique reactors vs total reaction events.

Thread replies

How many thread replies were there?

COUNT(*) FROM message_meta WHERE thread_ts IS NOT NULL AND ts != thread_ts AND ts IN period

Grain: Reply message meta
Fields: Message.thread_ts, Message.ts, Message.reply_count
Dimensions: channel_type, team, is_bot

Caution: many orgs should not land message text — meta only for replies.

Adapt: Choose reply_count on parent vs reply rows.

Formula overview

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

Label	Formula	Grain	Fields
Messages sent	`COUNT(*) FROM message_meta WHERE ts IN period AND subtype IS NULL AND is_bot = false`	Message meta	Message.channel_id, Message.ts, Message.subtype, User.is_bot
Active channels	`COUNT(DISTINCT channel_id) FROM message_meta WHERE ts IN period`	Channel	Message.channel_id, Message.ts, Channel.is_private
Files shared	`COUNT(*) FROM file_meta WHERE created IN period`	File meta	File Meta.id, File Meta.created, File Meta.filetype
Reactions count	`SUM(count) FROM reaction WHERE message_ts IN period`	Reaction	Reaction.name, Reaction.count
Thread replies	`COUNT(*) FROM message_meta WHERE thread_ts IS NOT NULL AND ts != thread_ts AND ts IN period`	Reply message meta	Message.thread_ts, Message.ts, Message.reply_count

Id	Label	Grain	Notes
`channel_type`	Channel type	public / private	Clearly separate is_private and is_im/is_mpim.
`team`	Team / workspace	team_id	Enterprise Grid: clarify workspace vs org level.
`is_bot`	Is bot	User.is_bot / subtype	Report human activity KPIs without bots.

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 profile	`id, name, real_name, profile.email, profile.phone, profile.image_*`	Workforce	RAW: workforce policy; Curated: user_id only; Mart: aggregates only	Slack users — email/phone only with legal basis; user_id as key.
Message text / files	`text, blocks, attachments, file content`	Quasi	RAW: rarely load; Curated: never cleartext; Mart: never	Do not load message bodies into the warehouse by default — activity facts without text.
DMs / MPIM	`im channel members, mpim members, dm message text`	Direct	RAW: restrict access; Curated: drop; Mart: never	DMs are highly sensitive — default out of scope for analytics.
Channel membership	`members, user_id lists, guest invites`	Workforce	RAW: workforce policy; Curated: aggregates / hashed; Mart: aggregates only	Membership reveals org networks — tight access.
Enterprise Grid discovery / SCIM	`SCIM emails, org user directories, IdP attributes`	Workforce	RAW: workforce policy; Curated: separate; Mart: aggregates only	Grid/SCIM directories — separate retention vs channel facts.

DSDR discovery

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

Focus	Notes
Match keys	user_id, team_id, channel_id, email (hashed), enterprise_id.
Primary objects	User, channel, membership, message metadata (no text), files metadata.
Conversations / Admin analytics exports	conversations.history and Discovery exports duplicate message PII — avoid.
Retention / legal hold copies	Compliance exports are DSDR copies — do not mix into analytics 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.

Medium Channel without purpose

Problem: Channels without purpose hinder ownership and inventory quality.
Prevent: Channel create checklist: purpose required; naming convention.
Staff must know: Admins: Every channel needs a purpose.
Checks: purpose IS NULL OR purpose = '' on public channels WHERE is_archived = false
Fix in source: Admin policy; bot remind on create.
Warehouse safety net: Unknown-purpose bucket; exclude from governed inventory.

High Channel without creator

Problem: Channels without creator_id break ownership and lifecycle KPIs.
Prevent: Create API always with user context; import with mapping.
Staff must know: Integrations: Map creator — not anonymous.
Checks: creator_id IS NULL on channel
Fix in source: Fix via Admin API where possible; document orphans.
Warehouse safety net: Unknown-owner dim.

Medium Archived channel in active KPIs

Problem: Archived channels in active inventory distort adoption metrics.
Prevent: Archive flag in all extracts; document KPI definition.
Staff must know: Analysts: Filter is_archived — do not count all channels.
Checks: is_archived = true AND included_in_active_inventory = true
Fix in source: Consistent archive process; naming for temporary channels.
Warehouse safety net: Exclude archived from active channel count.

High Message body in warehouse

Problem: Cleartext messages in the warehouse create DSDR/retention risk without KPI value.
Prevent: Extract allowlist: metadata + counts only; conversations.history off by default.
Staff must know: Data: Do not load message text — use activity aggregates.
Checks: EXISTS columns text/blocks in curated OR raw message body tables without legal flag
Fix in source: Disable history export for analytics app; Admin Analytics APIs.
Warehouse safety net: Drop body columns; rebuild as message_count facts.

High Retention policy mismatch

Problem: Warehouse keeps message metadata longer than Slack retention — compliance gap.
Prevent: Warehouse retention ≤ Slack policy; documented exception process.
Staff must know: Legal/data: Retention aligned — no silent long-term copies.
Checks: max(event_date) - min(event_date) > slack_retention_days on activity facts
Fix in source: Workspace retention settings review.
Warehouse safety net: TTL jobs; purge older activity grain.

Low Channel without member count

Problem: Missing num_members prevents adoption and size slices.
Prevent: Sync conversations.info regularly; use Admin Analytics.
Staff must know: Extract: Do not omit member counts.
Checks: num_members IS NULL on non-archived public channels
Fix in source: Refresh channel metadata job.
Warehouse safety net: Impute 0 with flag or exclude from size KPIs.

MDM / duplicates 2

User User duplicates / deactivated

Match keys: user_id, profile.email, enterprise_user.id
Prevent in source: SCIM/IdP as source of truth; deactivate instead of recreate.
Resolve in warehouse: Golden user_id; email-hash merge map.
Survivorship: Active user with latest activity wins.

Channel Channel rename / archive reuse

Match keys: channel_id, name, purpose
Prevent in source: Stable channel_id as key; naming convention for renames.
Resolve in warehouse: SCD2 channel name; keep channel_id canonical.
Survivorship: channel_id wins over name.

Product settings to improve 3

Workspace settings → Message & file retention Document retention policy and align warehouse

Why: Prevents compliance gaps from long-lived copies.
How: Admin settings review; TTL in warehouse jobs.

Admin Analytics APIs Analytics APIs instead of conversations.history for KPIs

Why: Activity without message cleartext.
How: Minimal admin scopes; aggregate extracts only.

Channel management / naming Purpose + naming convention enforced

Why: Inventory quality and ownership.
How: Create guidelines; archive stale channels.

Metadata: what exists & how to get it 4

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

Conversations API

Where in product: Slack API → conversations.list / info / members
How to access: Channel metadata sync; skip history endpoints by default.
Use for: Channel inventory, membership counts, archive flags.
Watchouts: conversations.history returns cleartext — not for standard analytics.

Admin analytics APIs

Where in product: Slack Admin → Analytics APIs / admin.analytics
How to access: Aggregate member/channel activity extracts.
Use for: Adoption KPIs without message bodies.
Watchouts: Admin scopes are powerful — least privilege.

Workspace settings

Where in product: Slack Admin → Settings & permissions / Retention
How to access: Document retention, guest access, app approvals.
Use for: Governance baseline, warehouse TTL policy.
Watchouts: Enterprise Grid can override workspace policies.

Enterprise Grid discovery

Where in product: Enterprise Grid Admin → Discovery / Org-wide search (compliance)
How to access: Discovery for compliance only — not analytics load.
Use for: Legal hold awareness — separate from marts.
Watchouts: Discovery exports are full message copies — DSDR.

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

Table	Description	Grain	Load
`message` Message	Channel message — fact for sent/replies; text is heavy PII and default skip.	One message (channel_id + ts)	Optional
`channel` Channel	Public/private channel — dimension and active-channels KPI.	One channel	Load
`user` User	Workspace user — profile with email/phone (PII); filter is_bot.	One user	Load
`user_group` User group	User groups (@engineers) — optional segment dimension.	One user group	Optional
`file_meta` File meta	File metadata — files shared; skip binaries.	One file (meta)	Optional
`reaction` Reaction	Emoji reactions — engagement without message body.	Reaction on message	Optional
`canvas` Canvas	Slack canvas — optional content object.	One canvas	Optional
`huddle_summary` Huddle summary	Huddle summary — meta for voice collab; review transcripts.	One huddle	Optional

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

Entity	Field	Role	Why
Message	`Message.channel_id`	Key	Channel join (Conversations API)
Message	`Message.ts`	Key	Message timestamp key
Message	`Message.thread_ts`	Dimension	Thread parent / replies
Message	`Message.user_id`	Dimension	Author join
Message	`Message.text`	PII	Message body / heavy PII — usually skip
Message	`Message.subtype`	Dimension	bot_message / channel_join / …
Message	`Message.reply_count`	Measure	Thread replies
Channel	`Channel.id`	Key	Channel join
Channel	`Channel.name`	Dimension	Channel name
Channel	`Channel.is_private`	Dimension	Public vs private
Channel	`Channel.is_archived`	Dimension	Archive filter
Channel	`Channel.team_id`	Dimension	Workspace / team
User	`User.id`	Key	User join
User	`User.email`	PII	Profile email / PII
User	`User.phone`	PII	Phone / PII
User	`User.real_name`	PII	Display name / PII
User	`User.is_bot`	Dimension	Bot filter
User	`User.team_id`	Dimension	Workspace
User Group	`User Group.id`	Key	Group join
User Group	`User Group.handle`	Dimension	Group handle
File Meta	`File Meta.id`	Key	File join
File Meta	`File Meta.filetype`	Dimension	File type
File Meta	`File Meta.user`	Dimension	Uploader
File Meta	`File Meta.created`	Measure	Upload time
Reaction	`Reaction.name`	Dimension	Emoji name
Reaction	`Reaction.count`	Measure	Reaction count
Canvas	`Canvas.id`	Key	Canvas join
Huddle Summary	`Huddle Summary.id`	Key	Huddle join
Huddle Summary	`Huddle Summary.duration`	Measure	Duration

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

Skip tables / objects

Table	Category	Why
`Message text / blocks (bulk)`	System	Cleartext messages — default never in warehouse marts.
`DM / MPIM history`	System	Highly sensitive private chats — out of scope.
`File binaries / snippet content`	System	Binaries and snippets — expensive and PII-risky.
`Discovery / legal hold full export`	System	Compliance-only — not for business KPIs.

Skip fields / content

Message body cleartext in curated marts — Only message_count / activity aggregates.
DM / private channel content — Never without legal/compliance approval.
User email / phone cleartext — user_id is enough for activity KPIs.
Unused custom profile fields (bulk) — DSDR surface.
Bot / test workspace noise channels — Keep prod activity 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 channel landing

Conversations metadata — no message bodies; purpose for inventory DQ.

-- Warehouse-neutral RAW from Slack Conversations API (metadata only)
CREATE TABLE raw_slack_channel (
  channel_id       VARCHAR,
  team_id          VARCHAR,
  name             VARCHAR,
  purpose          VARCHAR,
  topic            VARCHAR,
  creator_id       VARCHAR, -- workforce key
  is_archived      BOOLEAN,
  is_private       BOOLEAN,
  num_members      INT,
  created_at       TIMESTAMP,
  _loaded_at       TIMESTAMP
);

RAW user + channel activity landing

Activity facts without text — Admin Analytics / counts; email = PII in RAW.

CREATE TABLE raw_slack_user (
  user_id          VARCHAR,
  team_id          VARCHAR,
  display_name     VARCHAR,
  email            VARCHAR, -- workforce PII
  is_deleted       BOOLEAN,
  is_bot           BOOLEAN,
  _loaded_at       TIMESTAMP
);

-- Prefer aggregates over message text extracts
CREATE TABLE raw_slack_channel_activity_daily (
  activity_date    DATE,
  channel_id       VARCHAR,
  team_id          VARCHAR,
  message_count    INT, -- aggregate only — no message text
  active_user_count INT,
  _loaded_at       TIMESTAMP
);

Curated

Curated fact slack channel activity

message_count aggregates — no cleartext in curated_fct_slack_*.

CREATE TABLE curated_fct_slack_channel_activity AS
SELECT
  a.activity_date,
  a.channel_id,
  a.team_id,
  a.message_count,
  a.active_user_count,
  c.name AS channel_name,
  c.is_archived,
  c.is_private,
  c.num_members
FROM raw_slack_channel_activity_daily a
LEFT JOIN raw_slack_channel c ON c.channel_id = a.channel_id
WHERE COALESCE(c.is_private, FALSE) = FALSE; -- public channel activity only by default

Curated dim channel / user

Channel inventory; user without email in default marts.

CREATE TABLE curated_dim_slack_channel AS
SELECT
  channel_id,
  team_id,
  name,
  purpose,
  creator_id,
  is_archived,
  is_private,
  num_members,
  created_at
FROM raw_slack_channel;

CREATE TABLE curated_dim_slack_user AS
SELECT
  user_id,
  team_id,
  display_name,
  is_deleted,
  is_bot
  -- omit email from default analytics dims
FROM raw_slack_user;

Curated measure SELECTs

Active channels, message volume, active users — respect retention/TTL.

-- Active (non-archived) public channels
SELECT COUNT(*) AS active_channels
FROM curated_dim_slack_channel
WHERE is_archived = FALSE
  AND is_private = FALSE;

-- Message volume in period (aggregates only)
SELECT SUM(message_count) AS message_volume
FROM curated_fct_slack_channel_activity
WHERE activity_date BETWEEN :period_start AND :period_end;

-- Active users in period (max daily actives — adapt grain as needed)
SELECT SUM(active_user_count) AS active_user_events
FROM curated_fct_slack_channel_activity
WHERE activity_date 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 →