Joining Viva Insights queries

Overview

This tutorial walks through how to join the four custom queries in Viva Insights (Person, Consumption, GitHub and Agent) to bring together behavioural and AI usage metrics, alongside organisational attributes, in a single panel data file for analysis and reporting. The resulting file has one row per person per week.

This is the current reference for these cross-query joins and takes precedence over older schema and join guidance in this repository. Historical layouts and synthetic fixture manifests remain available for reproducing earlier examples. This guide does not extend the existing dashboard runners' supported inputs.

You need the Person Query plus at least one of the Consumption, GitHub or Agent queries. We recommend using all four for a broader view of a population's ways of working and AI usage across the products and activities they measure.

All four queries are available under Create analysis > Customized query in Viva Insights, where you can adjust parameters such as date grouping and population filters. We recommend this route because it offers more customisation parameters than dashboard exports.

We recommend exporting all selected queries grouped by Week, with the same week start. Consumption and GitHub default to Day, so change their grouping at setup. Weekly exports avoid daily-to-weekly aggregation and sparse-day handling, although service, spending-policy and agent rules still apply. If you need daily detail or must align different week starts, export those queries by Day and aggregate to the Person Query weeks.

Record the configured grouping for every query before validating its dates. Sparse observed dates alone cannot distinguish daily from weekly or monthly data.

The cards below use the query icons and descriptions shown in Viva Insights. Select a query to jump to its schema.

The four queries
Query	Files	Period	Role
Person query	1 CSV file `Person_<username>_<date>_<time>Hours.csv`	Week (Recommended)	Spine (left side of every join)
Consumption query	4 CSV files `PersonM365CreditsMetrics.csv`, `PersonGitHubCreditsMetrics.csv`, `PeopleMetaData.csv`, `M365SpendingPolicyMetaData.csv`	Week (Recommended), Day (default)	Joined onto the spine
GitHub query	5 CSV files `PersonGitHubActivityMetrics.csv`, `GitHubActivityBreakdownByFeatureMetrics.csv`, `GitHubActivityBreakdownByLanguageFeatureMetrics.csv`, `GitHubActivityBreakdownByLanguageModelMetrics.csv`, `GitHubActivityBreakdownByModelFeatureMetrics.csv`	Week (Recommended), Day (default)	Joined onto the spine
Agent query	2 CSV files `PersonAgentActivityMetrics.csv`, `AgentMetadata.csv` (optional)	Week (Recommended)	Joined onto the spine

Join architecture

The weekly Person Query is the spine. Validate each other selected query, bring it to one row per person per week, and left join it onto the Person Query. The diagram shows all four queries. Follow the paths for the queries you have, then select your analysis cohort from the joined population.

Query schemas

Export layouts vary between tenants and over time, so check each supplied export against the schemas below. For all four queries, we recommend the Create analysis route because it offers more customisation parameters than dashboard exports.

Person query

Recommended route: Create analysis > Customized query > Person query.

The Person query is available only through this route. It gives you access to the full Person query metric list, including behavioural metrics such as Email hours, Meeting hours and Internal network size. Select the metrics you need when setting up the query.

Weekly. Daily or monthly groupings are outside this contract.

`Person_<username>_<date>_<time>Hours.csv`

Grain: One row per person per week. Native key: PersonId, MetricDate. A single CSV, for example Person_jdoe_Oct27_2026_1027Hours.csv. Localised exports translate the words in the name, and the public sample fixture is PersonQuery.csv, so identify the file by its PersonId and MetricDate columns.

Columns in Person_<username>_<date>_<time>Hours.csv
Column	Type	Role	Notes
`PersonId`	string	Key	Pseudonymised person identifier. Keep as text.
`MetricDate`	date	Key	Week start. Detect the weekday from the data.
HR attributes	varies	Column group	Configured at query setup. Observed examples: Organization, FunctionType, SupervisorIndicator, Layer. Values can change between weeks.
Metric columns	varies	Column group	Selected at query setup. See the metric families table.

Metric families

Metric columns are chosen when the query is set up, so each list is representative rather than complete, and wording or capitalisation can vary by query version. Headers are listed in their raw export form, with spaces. The vivainsights R and Python packages replace spaces with underscores when they import a query, so Collaboration hours becomes Collaboration_hours. Treat the two forms as the same column. This applies to headers only, never to identifier values. See the metric definitions on Microsoft Learn for meanings.

Person Query metric families
Family	Kind	Headers
Collaboration	weekly total	`Collaboration hours`, `External collaboration hours`, `Meeting hours`, `Email hours`, `Chat hours`, `After hours collaboration hours`
Focus	weekly total	`Available to focus hours`, `Uninterrupted hours`
Network	stock	`Internal network size`, `External network size`, `Strong ties`, `Diverse ties`, `Network outside organization`
Copilot eligibility	day count	`Total Copilot enabled days`
Copilot activity, totals	weekly total or day count	`Total Copilot actions taken`, `Total Copilot active days`
Copilot activity, by surface	weekly total or day count	`Copilot actions taken in Copilot Chat`, `Copilot actions taken in Microsoft 365 Copilot app`, `Days of active Copilot Chat usage`, `Days of active Copilot Chat (legacy web) usage`, `Days of active Copilot usage in Excel`, `Days of active Copilot usage in Loop`, `Days of active Copilot usage in OneNote`, `Days of active Copilot usage in Outlook`, `Days of active Copilot usage in Powerpoint`, `Days of active Copilot usage in Teams`, `Days of active Copilot usage in Word`
Cowork	weekly total or day count	`Cowork tasks submitted`, `User-initiated Cowork tasks submitted`, `Days of active Cowork usage`
Unlicensed Copilot Chat	weekly total or day count	Inspect the export A separate metric family for Copilot Chat use without a Microsoft 365 Copilot licence. Inspect the export for its headers and keep it separate from licensed Copilot metrics.

Consumption query

Recommended route: Create analysis > Customized query > Consumption query.

You can also use the Export data button in the Consumption dashboard.

Daily by default, or weekly when grouped by week at setup. Facts plus metadata files.

`PersonM365CreditsMetrics.csv`

Grain: One row per person, service, spending policy and period (day or week). Native key: PersonId, ServiceId, SpendingPolicyId, MetricDate.

Columns in PersonM365CreditsMetrics.csv
Column	Type	Role	Notes
`PersonId`	string	Key
`ServiceId`	string	Key
`ServiceName`	string	Attribute
`SpendingPolicyId`	string	Key
`MetricDate`	date	Key	Day or week start, as set at query setup.
`Session count`	number	Measure
`Spending policy limit`	number	Limit
`Total Copilot Credits used`	number	Measure
`User limit`	number	Limit
`PeopleHistoricalId`	string	Join key	Links to `PeopleMetaData.csv`.

`PersonGitHubCreditsMetrics.csv`

Grain: One row per person and period with billable GitHub usage (sparse). Native key: PersonId, MetricDate.

Columns in PersonGitHubCreditsMetrics.csv
Column	Type	Role	Notes
`PersonId`	string	Key
`MetricDate`	date	Key	Day or week start, as set at query setup.
`Total GitHub AI Credits used`	number	Measure
`PeopleHistoricalId`	string	Join key	Links to `PeopleMetaData.csv`.

`PeopleMetaData.csv`

Grain: One row per historical person record. Native key: PeopleHistoricalId. Carries no HR attributes. Take organisational attributes from the Person Query.

Columns in PeopleMetaData.csv
Column	Type	Role	Notes
`PeopleHistoricalId`	string	Key	No `PersonId` in this file.
`IsCopilotLicensed`	boolean-like text	Attribute	Historical Microsoft 365 Copilot licence evidence only. Not evidence of GitHub Copilot provisioning.

`M365SpendingPolicyMetaData.csv` outside the core panel

Grain: One row per spending policy. Native key: SpendingPolicyId.

Columns in M365SpendingPolicyMetaData.csv
Column	Type	Role	Notes
`SpendingPolicyId`	string	Key
Policy attributes	varies	Column group	Descriptive policy fields. Inspect the headers in each export.

GitHub query

Recommended route: Create analysis > Customized query > GitHub query.

Daily by default, or weekly when grouped by week at setup. Activity facts plus dimensional breakdowns.

`PersonGitHubActivityMetrics.csv`

Grain: One row per person and period. Native key: PersonId, MetricDate.

Columns in PersonGitHubActivityMetrics.csv
Column	Type	Role	Notes
`PersonId`	string	Key
`MetricDate`	date	Key	Day or week start, as set at query setup.
`Agent adoption`	number	Indicator	Adoption indicator or percentage. Excludes code review.
`Code completions accepted`	number	Measure
`Code completions suggested`	number	Measure
`User-initiated chat requests`	number	Measure

Breakdown files part of the export, outside the core panel

Each adds one or two dimensions to the person-period grain, so the four files do not share a grain with each other or with the activity file. Keep them as linked detail (G11).

GitHub breakdown files
File	Native key	Measure
`GitHubActivityBreakdownByFeatureMetrics.csv`	`PersonId`, `MetricDate`, `Feature`	`Feature Usage Count`
`GitHubActivityBreakdownByLanguageFeatureMetrics.csv`	`PersonId`, `MetricDate`, `Language`, `Feature`	`Usage count by language and feature`
`GitHubActivityBreakdownByLanguageModelMetrics.csv`	`PersonId`, `MetricDate`, `Language`, `Model`	`Usage count by language and model`
`GitHubActivityBreakdownByModelFeatureMetrics.csv`	`PersonId`, `MetricDate`, `Model`, `Feature`	`Usage count by model and feature`

Agent query

Recommended route: Create analysis > Customized query > Agent query.

You can also use the Export data button in the Agent Dashboard.

Weekly facts with an optional metadata file.

`PersonAgentActivityMetrics.csv`

Grain: One row per person, agent and week. Native key: PersonId, AgentId, MetricDate.

Columns in PersonAgentActivityMetrics.csv
Column	Type	Role	Notes
`PersonId`	string	Key
`AgentId`	string	Key
`MetricDate`	date	Key	Weekly. Sunday-start in observed exports.
`Conversational agent sessions`	number	Measure
`Returning Agent 365 user (most recent and prior 7 days)`	number	Indicator	0 or 1 over a 7-day window.
`Returning Agent 365 user (most recent and prior 28 days)`	number	Indicator	0 or 1 over a 28-day window.

`AgentMetadata.csv` optional in the export, outside the core panel

Grain: One row per agent. Native key: AgentId. Present in some exports and absent in others.

Columns in AgentMetadata.csv
Column	Type	Role	Notes
`AgentId`	string	Key
Agent attributes	varies	Column group	Descriptive agent fields. Inspect the headers in each export. Never use them to identify people.

Join keys

Keys and where they appear
Key	Appears in	Use
`PersonId`	Every person-level file in all four queries	Pseudonymised person and the panel join key. Verify overlap between sources (G15).
`MetricDate`	Every fact file and the Person Query	Period start. Weekly in the Person and Agent queries. Daily by default in Consumption and GitHub, or weekly when grouped by week at setup. Real exports and the sample fixtures use Sunday-start weeks, while some documentation describes Monday, so detect it (G03, G03b).
`WeekStart`	Chosen name in this guide	`WeekStart` is this guide's chosen name for an aligned week-start date. It is not an extra column supplied by the exports. You may keep `MetricDate` or choose another name, provided every source uses the same week definition.
`PeopleHistoricalId`	Both Consumption fact files and PeopleMetaData.csv	Historical person record. Read it from each fact row. It cannot be derived from PersonId.
`ServiceId`	PersonM365CreditsMetrics.csv	Part of the native key. Keep service detail as linked detail.
`SpendingPolicyId`	PersonM365CreditsMetrics.csv and M365SpendingPolicyMetaData.csv	Part of the native key. Unique keys do not establish additivity (G05).
`AgentId`	Both Agent files	Stable agent key. Agent names can change.
`Feature`, `Language`, `Model`	GitHub breakdown files	Extra dimensions that change the grain (G11).

Summaries you create

The names below describe intermediate tables you create in your script. They are not extra downloads or required object names. Choose your own names for data frames, SQL views or other objects. Each summary, and the final combined panel, has one row per person per week.

WeekStart is this guide's chosen name for an aligned week-start date. It is not an extra column supplied by the exports. You may keep MetricDate or choose another name, provided every source uses the same week definition. The source filenames below link to their schemas. The step links take you to the numbered Join procedure later on this page.

Preparing summaries and the final panel
Table you create	Source export	What to do
Microsoft 365 credits summary	`PersonM365CreditsMetrics.csv`	Sum credits across services and spending policies after G05, and across days if the export is daily. Include sessions summed across policies only when G05b passes. Step 5: Check policy additivity Step 6: Bring each source to person-week
GitHub credits summary	`PersonGitHubCreditsMetrics.csv`	Sum credits across days if the export is daily. A weekly export is already one row per person-week. Step 6: Bring each source to person-week
GitHub activity summary	`PersonGitHubActivityMetrics.csv`	Apply the measure rules, aggregating across days only if the export is daily. Step 6: Bring each source to person-week
Agent activity summary	`PersonAgentActivityMetrics.csv`	Sum sessions across agents. Derive any person-level returning flag explicitly. Step 6: Bring each source to person-week
Combined person-week panel	`Person_<username>_<date>_<time>Hours.csv`	Start with the Person Query and left join each summary. Keep every Person Query row. Step 7: Join onto the spine

How to connect the data

Attach metadata before summarising

In step 4: Validate each file, match each fact row to at most one metadata row using the exported key shown below. First check that the key is unique in the metadata file. Report unmatched keys and ensure the join does not multiply fact rows.

Metadata lookups on exported columns
Fact file	Metadata file	Match on	Use
`PersonM365CreditsMetrics.csv`	`PeopleMetaData.csv`	`PeopleHistoricalId`	Microsoft 365 Copilot licence evidence, before aggregation
`PersonGitHubCreditsMetrics.csv`	`PeopleMetaData.csv`	`PeopleHistoricalId`	Check that each historical person record exists. `IsCopilotLicensed` describes Microsoft 365 only and cannot confirm GitHub eligibility.
`PersonM365CreditsMetrics.csv`	`M365SpendingPolicyMetaData.csv`	`SpendingPolicyId`	Check that each spending policy exists in the metadata.
`PersonAgentActivityMetrics.csv`	`AgentMetadata.csv`	`AgentId`	Optional enrichment, only when the metadata file is supplied

Add summaries to the Person Query

In step 7: Join onto the spine, start with the weekly Person Query on the left and add each summary on the right with a left join. The summary names below refer to the tables you created above. Match on PersonId and the aligned week-start date, called WeekStart in this guide. Both sides must have at most one row per person per week.

Left joins that preserve the Person Query rows
Summary to add (right side)	Match on	Expected result
Microsoft 365 credits summary	`PersonId`, `WeekStart`	Keep every Person Query row. Match at most one summary row per person and week.
GitHub credits summary	`PersonId`, `WeekStart`	Keep every Person Query row. Match at most one summary row per person and week.
GitHub activity summary	`PersonId`, `WeekStart`	Keep every Person Query row. Match at most one summary row per person and week.
Agent activity summary	`PersonId`, `WeekStart`	Keep every Person Query row. Match at most one summary row per person and week.

Keep GitHub breakdowns separate

These files have multiple rows per person and period. Joining them directly to the person-week panel would repeat panel rows and inflate totals. Keep each as a separate detail table, aligning its dates to the Person Query weeks when needed.

Detail outside the person-week panel
Source files	Treatment	How to relate them
`GitHubActivityBreakdownByFeatureMetrics.csv`, `GitHubActivityBreakdownByLanguageFeatureMetrics.csv`, `GitHubActivityBreakdownByLanguageModelMetrics.csv`, `GitHubActivityBreakdownByModelFeatureMetrics.csv`	Never joined wide. Keep as separate detail tables keyed to the panel	Use `PersonId` and the aligned week to relate detail to the panel. Retain each file's additional dimensions.

Aggregation rules

Apply these rules to panel-bound summaries and when rolling person-weeks up to longer periods. Weekly grouping removes daily aggregation, but does not make every measure additive across services, spending policies or agents. GitHub breakdowns remain separate, retaining their dimensions.

Person-week rule for each measure
ID	Columns	File	Kind	Person-week rule
M01	`Total Copilot Credits used`	`PersonM365CreditsMetrics.csv`	additive	Sum across services and spending policies after G05 passes, and across days when the export is daily.
M02	`Session count`	`PersonM365CreditsMetrics.csv`	conditionally additive	Sum across services, and across days when the export is daily. Sum across `SpendingPolicyId` rows only when G05b passes. Sessions are not people.
M03	`Spending policy limit`, `User limit`	`PersonM365CreditsMetrics.csv`	limit	Never sum. Keep per policy as linked detail.
M04	`Total GitHub AI Credits used`	`PersonGitHubCreditsMetrics.csv`	additive	Sum across days when the export is daily. Keep separate from Microsoft 365 credits.
M05	`Code completions accepted`, `Code completions suggested`, `User-initiated chat requests`	`PersonGitHubActivityMetrics.csv`	additive	Sum across days when the export is daily. Acceptance rate is summed accepted divided by summed suggested, with a stated zero-denominator rule.
M06	`Agent adoption`	`PersonGitHubActivityMetrics.csv`	indicator	Do not sum. For a daily export, state a derived weekly rule, for example any positive day in the week. For a weekly export, use the exported value.
M07	`Feature Usage Count`	`GitHubActivityBreakdownByFeatureMetrics.csv`	additive within file	Sum days within this file when daily, retaining Feature for each person-week. Keep as linked detail. Never add to another breakdown.
M08	`Usage count by language and feature`	`GitHubActivityBreakdownByLanguageFeatureMetrics.csv`	additive within file	Sum days within this file when daily, retaining Language and Feature for each person-week. Keep as linked detail. Never add to another breakdown.
M09	`Usage count by language and model`	`GitHubActivityBreakdownByLanguageModelMetrics.csv`	additive within file	Sum days within this file when daily, retaining Language and Model for each person-week. Keep as linked detail. Never add to another breakdown.
M10	`Usage count by model and feature`	`GitHubActivityBreakdownByModelFeatureMetrics.csv`	additive within file	Sum days within this file when daily, retaining Model and Feature for each person-week. Keep as linked detail. Never add to another breakdown.
M11	`Conversational agent sessions`	`PersonAgentActivityMetrics.csv`	additive	Sum across agents to person-week.
M12	`Returning Agent 365 user (most recent and prior 7 days)`	`PersonAgentActivityMetrics.csv`	window indicator	Do not sum across agents or weeks. A person-level flag, such as returning for any agent, is a stated derived definition.
M13	`Returning Agent 365 user (most recent and prior 28 days)`	`PersonAgentActivityMetrics.csv`	window indicator	Do not sum across agents or weeks. A person-level flag, such as returning for any agent, is a stated derived definition.
M14	`Internal network size`, `External network size`	`Person_<username>_<date>_<time>Hours.csv`	stock	Detect and record the measurement window. Values can repeat within a month. Average or deduplicate snapshots according to that window, never sum.
M15	`Days of active Copilot Chat usage`, `Days of active Copilot Chat (legacy web) usage`, `Days of active Copilot usage in Excel`, `Days of active Copilot usage in Loop`, `Days of active Copilot usage in OneNote`, `Days of active Copilot usage in Outlook`, `Days of active Copilot usage in Powerpoint`, `Days of active Copilot usage in Teams`, `Days of active Copilot usage in Word`	`Person_<username>_<date>_<time>Hours.csv`	day count	Never add across surfaces to make total active days, because one day can include several surfaces. Use `Total Copilot active days`.

Join procedure

Follow the procedure for the queries you have. Apply source-specific checks only when that source is supplied, for example step 5: Check policy additivity applies to Consumption. Record omitted queries as unavailable, without treating their absence as evidence of non-use.

Match the layout G01
Confirm that the Person Query and at least one of Consumption, GitHub or Agent are supplied. List the files for each selected query and compare their names and key columns with the schema tables. Record the export date, configured population, date window and product filters, which layout matched and which queries are unavailable.
Load safely G02
Read every identifier column as text and parse dates explicitly. Headers may arrive in raw form with spaces, or with underscores after import through the vivainsights packages.
Fix the target grain G03, G03b
Recommend weekly grouping for every selected query. Record each configured grouping from query setup or equivalent export metadata before checking dates. Confirm the Person Query is weekly with one row per PersonId and MetricDate, and detect its weekday as WeekStart. Validate daily fact dates or matching weekly starts. Stop if grouping is unknown, rather than inferring it from sparse dates.
Validate each file G04, G06
Assert each native key. Join metadata files many-to-one and report unmatched counts. Check that each PeopleHistoricalId maps to one PersonId.
Check policy additivity G05, G05b
In PersonM365CreditsMetrics.csv, count person-service-period keys with more than one positive-credit SpendingPolicyId row. Zero means credits can be summed. Otherwise stop for a documented decision, and confirm separately whether it covers sessions.
Bring each source to person-week G07, G10, G11
Build only panel-bound summaries at one row per person-week. For a configured daily export, map MetricDate to WeekStart and sum permitted measures across days. For a weekly export, use MetricDate as WeekStart. Follow the measure-specific service, policy and agent rules, including G05b for sessions. Keep unconfirmed per-policy sessions and dimension-preserving GitHub breakdowns as linked detail. Keep Microsoft 365 and GitHub credits separate.
Join onto the spine G08, G15
Left join each summary to the Person Query on PersonId and WeekStart. Assert one-to-one, check PersonId overlap, and reconcile row counts and additive totals.
Label missing values G09
Assume supplied Viva Insights data are complete within the recorded query scope. For activity measures, treat omitted inactive person-days, or an entire inactive person-week, as zero. Label unavailable or out-of-scope data, known export problems and null cells separately. Record partial boundary weeks and keep licence eligibility separate from activity zeros.
Select the cohort G12, G13
Choose the population for the question, for example everyone eligible for an adoption view, and record the rule and the measurement windows.
Publish safely G14, G16, G17
Publish only groups of at least 10 distinct people in every slice, withhold related breakdowns that would allow subtraction, keep identifiers out of every output and describe results as associations.

Gates

19 checks before and during the join
ID	Level	Rule
G01	MUST	Require the Person Query plus at least one of Consumption, GitHub or Agent. Record which queries are supplied and identify each export's layout. Within each selected query, files with `required_for_layout_match` set to true must all be present. If files or key columns differ from this contract, stop and record the difference.
G02	MUST	Read identifiers as text. Do not change their case, trim them silently or fuzzy-match people.
G03	MUST	Confirm weekly grouping from the Person Query configuration or equivalent export metadata, then validate one row per `PersonId` and `MetricDate` and detect the weekday of `MetricDate`. Stop if grouping is unknown, daily or monthly. Do not assume Monday or Sunday.
G03b	MUST	Record each fact export's configured date grouping from query setup or equivalent export metadata, then validate `MetricDate` against it. Sparse dates alone cannot establish cadence. Recommend weekly grouping for all queries. Daily and weekly exports are supported. A weekly export must start on the same weekday as the Person Query. Stop for unknown grouping, any other period such as monthly, or a different weekly start.
G04	MUST	Assert uniqueness of each file's native key. Report duplicate counts and stop. Never keep an arbitrary first row.
G05	MUST	Before summing Microsoft 365 credits across `SpendingPolicyId`, count person-service-period keys with more than one positive-credit row. If any exist, obtain a documented confirmation that the rows record disjoint consumption.
G05b	MUST	Sum `Session count` across `SpendingPolicyId` rows only when the documented confirmation explicitly covers sessions. Otherwise keep sessions per policy.
G06	MUST	Join metadata many-to-one on unique keys. Report unmatched key counts without identifiers. Check that each `PeopleHistoricalId` maps to one `PersonId`.
G07	MUST	Bring only panel-bound summaries to one row per `PersonId` and `WeekStart` before joining. Apply the measure rules: sum days only for configured daily exports, credits across policies only after G05, and sessions across policies only after G05b. Otherwise retain sessions per policy outside the panel. Keep GitHub breakdown dimensions as linked detail under G11. Never expand weekly rows into invented daily rows.
G08	MUST	Left join each person-week summary onto the Person Query. Assert one-to-one, preserve the Person Query row count, and reconcile additive totals before and after.
G09	MUST	Assume supplied Viva Insights queries are complete within their configured population, date window and product filters. Omitted activity rows represent inactivity by default, so record zero activity within that scope without requiring separate completeness evidence. Do not zero-fill unavailable queries, out-of-scope periods or people, null cells in existing rows, rolling indicators, limits, or known export problems. Record partial boundary weeks separately. Eligibility is a separate attribute: Microsoft 365 licence evidence never confirms GitHub eligibility.
G10	MUST	Keep Microsoft 365 and GitHub credits in separate columns. Never derive tokens or currency from credits.
G11	MUST	Do not join GitHub breakdown files wide. Keep them as linked detail tables.
G12	SHOULD	Treat integration and cohort selection as separate, declared steps. An unavailable query does not establish non-use.
G13	SHOULD	Record each variable's measurement window. Network sizes can repeat within a month and are averaged rather than summed.
G14	MUST	Coverage, licence and additivity confirmations apply only to the export they were given for.
G15	MUST	Check `PersonId` overlap between sources before joining. Investigate low overlap against activity filters, omitted inactive people and configured populations before concluding that pseudonymisation differs. Do not repair identifiers by matching on attributes.
G16	MUST	Publish a group only when it contains at least 10 distinct people, counted with count_distinct(`PersonId`), in every filter and time slice. Withhold an entire related breakdown if remaining cells or totals would reveal a suppressed cell by subtraction. Never output identifiers.
G17	MUST	Describe joined results as associations. Credits, sessions and actions do not measure value, quality or productivity.

Missing values

Default: assume supplied Viva Insights queries are complete. Inactive person-days are excluded by default. Within the query's scope, treat omitted activity rows as zero activity, including a person-week with no activity rows. This does not require separate completeness evidence.

Scope: The configured population, date window and product filters of each supplied Viva Insights query. Keep unavailable queries, out-of-scope people or periods, null cells in existing rows and known export problems distinct from inactivity. Partial boundary weeks do not establish full-week totals.

Activity zeros do not establish product eligibility or licence status. Use product-specific evidence for adoption denominators. Every joined measure should carry a state below, judged separately for each product. Use absent_inactive_zero for omitted activity rows under this default. Do not fill null cells, rolling indicators or limits merely because omitted activity is zero.

Missingness states
State	Meaning
`observed_positive`	A row exists with a positive value
`observed_zero`	A row exists with a recorded zero
`absent_inactive_zero`	No activity row within a supplied query's configured population and date window. Assume complete Viva Insights data and record zero activity
`absent_unknown`	The query is unavailable, the person or period is outside its scope, a known export problem applies, or an absent non-activity measure has no defined zero interpretation. Keep as unknown
`observed_null`	A row exists but the measure is null. Do not apply the omitted-inactive-row default to this cell
`ineligible`	Not eligible for that product in the week
`outside_cohort`	Not selected by the analysis cohort rule

Common errors

Symptoms, causes and fixes
Symptom	Likely cause	Fix
Row count grows after a join	A fact file was joined before aggregation, or GitHub breakdowns were joined wide	Aggregate to one row per `PersonId` and `WeekStart` first. Keep breakdowns as linked detail.
Duplicate key on `PersonId`, `ServiceId` and `MetricDate`	The export includes `SpendingPolicyId` in the native key	Use the four-part key, then run the additivity check before summing.
Credits double after joining metadata	A metadata key is not unique	Assert a unique `PeopleHistoricalId` or `SpendingPolicyId` before the join.
Most Consumption or Agent rows do not match the Person Query	Week starts differ, daily dates were not mapped to `WeekStart`, or `PersonId` values come from different populations	Align to the detected `WeekStart`. Check `PersonId` overlap. Never match on attributes.
No HR attributes for grouping	`PeopleMetaData.csv` carries only `PeopleHistoricalId` and `IsCopilotLicensed`	Take attributes from the Person Query with a stated assignment rule.
Network size totals look implausibly large	Repeated monthly snapshots were summed	Average snapshots and record the measurement window.
Total active days exceed seven per week	Per-surface active days were added together	Use `Total Copilot active days` for the total.
Weekly Consumption or GitHub rows match no Person Query weeks	The weekly export starts on a different weekday from the Person Query	Export the query daily and aggregate the days to the Person Query weeks. Weekly totals cannot be shifted to another week start.
Out-of-scope GitHub periods treated as zero	The query's configured date window or population differs from the Person Query	Apply the inactivity default only within the supplied query's scope. Keep unavailable or out-of-scope data unknown. Microsoft 365 licence evidence does not establish GitHub eligibility.

For coding agents

How coding agents should use this page

This page is designed to be read by coding agents as well as people. The complete contract is embedded as JSON in the script element with id join-contract and type application/json. Parse it instead of scraping the tables. The tables are generated from the same JSON.
Use input_requirements to check that the Person Query and at least one other query are supplied. Apply file requirements, joins and source-specific gates only to the selected queries. Record the unavailable queries explicitly.
Read coverage_assumption before handling absent rows. Assume complete Viva Insights exports within their configured scope, with omitted inactive person-days contributing zero activity. This is separate from product eligibility and does not apply to unavailable queries or null cells.
Every file, derived dataset, relationship (J1 to J9), measure (M01 to M15) and gate has a stable ID. These are contract references, not exported columns or required script object names. Resolve file IDs to file and derived IDs to their label, then map them to your own objects. Cite contract IDs in logs and summaries.
WeekStart is a chosen name for the aligned week date. Map it to your implementation, including MetricDate if you keep that header. See naming_conventions in the contract. build_steps refers to the numbered Join procedure on this page.
columns lists literal headers to find. column_groups describes configurable sets whose headers must be inspected in each export.
MUST gates block the join when they fail. Stop and report the gate ID, a bounded count and the next required input. SHOULD gates require a recorded decision.
Never print identifiers or employee rows. Report counts only.
If a supplied export does not match a layout here, do not adapt the contract silently. Report the difference (G01).

Download the JSON contract.

Show the JSON contract

{
  "contract_id": "viva-insights-query-join",
  "version": "0.7.0",
  "date": "2026-10-28",
  "status": "reference",
  "scope": "Joining weekly Viva Insights Person Query exports with Consumption, GitHub and Agent query exports into one person-week panel",
  "spine": "person_query.person",
  "input_requirements": {
    "required_query_ids": [
      "person_query"
    ],
    "additional_query_ids": [
      "consumption_query",
      "github_query",
      "agent_query"
    ],
    "minimum_additional_queries": 1,
    "recommendation": "Use all four queries for a broader view of ways of working and AI usage across the products and activities they measure.",
    "applicability": "Apply file requirements, joins and source-specific checks only to the selected queries. Record omitted queries as unavailable, without inferring non-use."
  },
  "naming_conventions": {
    "ids": "File and dataset IDs are references within this guide's contract. They are not exported columns, file paths or required names for objects in your script.",
    "derived_datasets": "Dataset labels describe intermediate summaries and the final panel that your script creates. Choose your own object names.",
    "week_start": "WeekStart is this guide's chosen name for an aligned week-start date. It is not an extra column supplied by the exports. You may keep MetricDate or choose another name, provided every source uses the same week definition."
  },
  "target_grain": {
    "keys": [
      "PersonId",
      "WeekStart"
    ],
    "person_query_cadence_required": "weekly",
    "week_start": "detect_from_person_query",
    "rule": "Export all selected queries grouped by week, with the same week start, wherever possible. This contract requires a weekly Person Query. `WeekStart` is its `MetricDate`. Consumption and GitHub default to daily, so change their grouping at setup. Use daily exports only when daily detail or week-start alignment is needed (G03, G03b)."
  },
  "coverage_assumption": {
    "complete_by_default": true,
    "scope": "The configured population, date window and product filters of each supplied Viva Insights query.",
    "missing_activity_rows": "Inactive person-days are excluded by default. Within the query's scope, treat omitted activity rows as zero activity, including a person-week with no activity rows. This does not require separate completeness evidence.",
    "exceptions": "Keep unavailable queries, out-of-scope people or periods, null cells in existing rows and known export problems distinct from inactivity. Partial boundary weeks do not establish full-week totals.",
    "eligibility": "Activity zeros do not establish product eligibility or licence status. Use product-specific evidence for adoption denominators."
  },
  "header_matching": "Headers are listed in their raw export form, with spaces. The vivainsights R and Python packages replace spaces with underscores when they import a query, so `Collaboration hours` becomes Collaboration_hours. Treat the two forms as the same column. This applies to headers only, never to identifier values.",
  "query_setup_recommendation": "For all four queries, we recommend the Create analysis route because it offers more customisation parameters than dashboard exports.",
  "queries": [
    {
      "id": "person_query",
      "name": "Person query",
      "role": "spine",
      "icon": "/assets/images/queries/person-query.svg",
      "product_description": "Analyse collaboration trends across the company",
      "recommended_path": [
        "Create analysis",
        "Customized query",
        "Person query"
      ],
      "access_context": "The Person query is available only through this route. It gives you access to the full Person query metric list, including behavioural metrics such as Email hours, Meeting hours and Internal network size. Select the metrics you need when setting up the query.",
      "cadence": "Weekly. Daily or monthly groupings are outside this contract.",
      "period_options": [
        "week"
      ],
      "default_period": "week",
      "verification": "observed_in_real_exports",
      "files": [
        {
          "id": "person_query.person",
          "file": "Person_<username>_<date>_<time>Hours.csv",
          "grain": "One row per person per week",
          "native_key": [
            "PersonId",
            "MetricDate"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": true,
          "columns": [
            {
              "name": "PersonId",
              "type": "string",
              "role": "key",
              "note": "Pseudonymised person identifier. Keep as text."
            },
            {
              "name": "MetricDate",
              "type": "date",
              "role": "key",
              "note": "Week start. Detect the weekday from the data."
            }
          ],
          "column_groups": [
            {
              "name": "HR attributes",
              "headers": "inspect_export",
              "note": "Configured at query setup. Observed examples: Organization, FunctionType, SupervisorIndicator, Layer. Values can change between weeks."
            },
            {
              "name": "Metric columns",
              "headers": "inspect_export",
              "note": "Selected at query setup. See the metric families table."
            }
          ],
          "file_pattern": "A single CSV, for example `Person_jdoe_Oct27_2026_1027Hours.csv`. Localised exports translate the words in the name, and the public sample fixture is `PersonQuery.csv`, so identify the file by its `PersonId` and `MetricDate` columns."
        }
      ]
    },
    {
      "id": "consumption_query",
      "name": "Consumption query",
      "role": "fact",
      "icon": "/assets/images/queries/consumption-query.svg",
      "product_description": "Analyse credit usage across services, users, groups, and spending policies.",
      "recommended_path": [
        "Create analysis",
        "Customized query",
        "Consumption query"
      ],
      "alternative_path": [
        "Consumption dashboard",
        "Export data"
      ],
      "cadence": "Daily by default, or weekly when grouped by week at setup. Facts plus metadata files.",
      "period_options": [
        "day",
        "week"
      ],
      "default_period": "day",
      "verification": "observed_in_real_exports",
      "files": [
        {
          "id": "consumption.m365_credits",
          "file": "PersonM365CreditsMetrics.csv",
          "grain": "One row per person, service, spending policy and period (day or week)",
          "native_key": [
            "PersonId",
            "ServiceId",
            "SpendingPolicyId",
            "MetricDate"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": true,
          "columns": [
            {
              "name": "PersonId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "ServiceId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "ServiceName",
              "type": "string",
              "role": "attribute",
              "note": ""
            },
            {
              "name": "SpendingPolicyId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "MetricDate",
              "type": "date",
              "role": "key",
              "note": "Day or week start, as set at query setup."
            },
            {
              "name": "Session count",
              "type": "number",
              "role": "measure",
              "note": ""
            },
            {
              "name": "Spending policy limit",
              "type": "number",
              "role": "limit",
              "note": ""
            },
            {
              "name": "Total Copilot Credits used",
              "type": "number",
              "role": "measure",
              "note": ""
            },
            {
              "name": "User limit",
              "type": "number",
              "role": "limit",
              "note": ""
            },
            {
              "name": "PeopleHistoricalId",
              "type": "string",
              "role": "foreign_key",
              "note": "Links to `PeopleMetaData.csv`."
            }
          ],
          "column_groups": []
        },
        {
          "id": "consumption.github_credits",
          "file": "PersonGitHubCreditsMetrics.csv",
          "grain": "One row per person and period with billable GitHub usage (sparse)",
          "native_key": [
            "PersonId",
            "MetricDate"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": true,
          "columns": [
            {
              "name": "PersonId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "MetricDate",
              "type": "date",
              "role": "key",
              "note": "Day or week start, as set at query setup."
            },
            {
              "name": "Total GitHub AI Credits used",
              "type": "number",
              "role": "measure",
              "note": ""
            },
            {
              "name": "PeopleHistoricalId",
              "type": "string",
              "role": "foreign_key",
              "note": "Links to `PeopleMetaData.csv`."
            }
          ],
          "column_groups": []
        },
        {
          "id": "consumption.people_metadata",
          "file": "PeopleMetaData.csv",
          "grain": "One row per historical person record",
          "native_key": [
            "PeopleHistoricalId"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": true,
          "columns": [
            {
              "name": "PeopleHistoricalId",
              "type": "string",
              "role": "key",
              "note": "No `PersonId` in this file."
            },
            {
              "name": "IsCopilotLicensed",
              "type": "boolean-like text",
              "role": "attribute",
              "note": "Historical Microsoft 365 Copilot licence evidence only. Not evidence of GitHub Copilot provisioning."
            }
          ],
          "column_groups": [],
          "note": "Carries no HR attributes. Take organisational attributes from the Person Query."
        },
        {
          "id": "consumption.policy_metadata",
          "file": "M365SpendingPolicyMetaData.csv",
          "grain": "One row per spending policy",
          "native_key": [
            "SpendingPolicyId"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": false,
          "columns": [
            {
              "name": "SpendingPolicyId",
              "type": "string",
              "role": "key",
              "note": ""
            }
          ],
          "column_groups": [
            {
              "name": "Policy attributes",
              "headers": "inspect_export",
              "note": "Descriptive policy fields. Inspect the headers in each export."
            }
          ]
        }
      ]
    },
    {
      "id": "github_query",
      "name": "GitHub query",
      "role": "fact",
      "icon": "/assets/images/queries/github-query.svg",
      "product_description": "Analyse GitHub Copilot usage to understand developer activity and coding trends.",
      "recommended_path": [
        "Create analysis",
        "Customized query",
        "GitHub query"
      ],
      "cadence": "Daily by default, or weekly when grouped by week at setup. Activity facts plus dimensional breakdowns.",
      "period_options": [
        "day",
        "week"
      ],
      "default_period": "day",
      "verification": "reference_schema_not_yet_verified_on_real_exports",
      "files": [
        {
          "id": "github.activity",
          "file": "PersonGitHubActivityMetrics.csv",
          "grain": "One row per person and period",
          "native_key": [
            "PersonId",
            "MetricDate"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": true,
          "columns": [
            {
              "name": "PersonId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "MetricDate",
              "type": "date",
              "role": "key",
              "note": "Day or week start, as set at query setup."
            },
            {
              "name": "Agent adoption",
              "type": "number",
              "role": "indicator",
              "note": "Adoption indicator or percentage. Excludes code review."
            },
            {
              "name": "Code completions accepted",
              "type": "number",
              "role": "measure",
              "note": ""
            },
            {
              "name": "Code completions suggested",
              "type": "number",
              "role": "measure",
              "note": ""
            },
            {
              "name": "User-initiated chat requests",
              "type": "number",
              "role": "measure",
              "note": ""
            }
          ],
          "column_groups": []
        },
        {
          "id": "github.by_feature",
          "file": "GitHubActivityBreakdownByFeatureMetrics.csv",
          "grain": "One row per person, period and feature",
          "native_key": [
            "PersonId",
            "MetricDate",
            "Feature"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": false,
          "columns": [
            {
              "name": "PersonId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "MetricDate",
              "type": "date",
              "role": "key",
              "note": ""
            },
            {
              "name": "Feature",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "Feature Usage Count",
              "type": "number",
              "role": "measure",
              "note": ""
            }
          ],
          "column_groups": []
        },
        {
          "id": "github.by_language_feature",
          "file": "GitHubActivityBreakdownByLanguageFeatureMetrics.csv",
          "grain": "One row per person, period, language and feature",
          "native_key": [
            "PersonId",
            "MetricDate",
            "Language",
            "Feature"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": false,
          "columns": [
            {
              "name": "PersonId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "MetricDate",
              "type": "date",
              "role": "key",
              "note": ""
            },
            {
              "name": "Language",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "Feature",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "Usage count by language and feature",
              "type": "number",
              "role": "measure",
              "note": ""
            }
          ],
          "column_groups": []
        },
        {
          "id": "github.by_language_model",
          "file": "GitHubActivityBreakdownByLanguageModelMetrics.csv",
          "grain": "One row per person, period, language and model",
          "native_key": [
            "PersonId",
            "MetricDate",
            "Language",
            "Model"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": false,
          "columns": [
            {
              "name": "PersonId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "MetricDate",
              "type": "date",
              "role": "key",
              "note": ""
            },
            {
              "name": "Language",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "Model",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "Usage count by language and model",
              "type": "number",
              "role": "measure",
              "note": ""
            }
          ],
          "column_groups": []
        },
        {
          "id": "github.by_model_feature",
          "file": "GitHubActivityBreakdownByModelFeatureMetrics.csv",
          "grain": "One row per person, period, model and feature",
          "native_key": [
            "PersonId",
            "MetricDate",
            "Model",
            "Feature"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": false,
          "columns": [
            {
              "name": "PersonId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "MetricDate",
              "type": "date",
              "role": "key",
              "note": ""
            },
            {
              "name": "Model",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "Feature",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "Usage count by model and feature",
              "type": "number",
              "role": "measure",
              "note": ""
            }
          ],
          "column_groups": []
        }
      ]
    },
    {
      "id": "agent_query",
      "name": "Agent query",
      "role": "fact",
      "icon": "/assets/images/queries/agent-query.svg",
      "product_description": "Analyse agent adoption trends across the company",
      "recommended_path": [
        "Create analysis",
        "Customized query",
        "Agent query"
      ],
      "alternative_path": [
        "Agent Dashboard",
        "Export data"
      ],
      "cadence": "Weekly facts with an optional metadata file.",
      "period_options": [
        "week"
      ],
      "default_period": "week",
      "verification": "observed_in_real_exports",
      "files": [
        {
          "id": "agent.activity",
          "file": "PersonAgentActivityMetrics.csv",
          "grain": "One row per person, agent and week",
          "native_key": [
            "PersonId",
            "AgentId",
            "MetricDate"
          ],
          "required_for_layout_match": true,
          "required_for_core_panel": true,
          "columns": [
            {
              "name": "PersonId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "AgentId",
              "type": "string",
              "role": "key",
              "note": ""
            },
            {
              "name": "MetricDate",
              "type": "date",
              "role": "key",
              "note": "Weekly. Sunday-start in observed exports."
            },
            {
              "name": "Conversational agent sessions",
              "type": "number",
              "role": "measure",
              "note": ""
            },
            {
              "name": "Returning Agent 365 user (most recent and prior 7 days)",
              "type": "number",
              "role": "indicator",
              "note": "0 or 1 over a 7-day window."
            },
            {
              "name": "Returning Agent 365 user (most recent and prior 28 days)",
              "type": "number",
              "role": "indicator",
              "note": "0 or 1 over a 28-day window."
            }
          ],
          "column_groups": []
        },
        {
          "id": "agent.metadata",
          "file": "AgentMetadata.csv",
          "grain": "One row per agent",
          "native_key": [
            "AgentId"
          ],
          "required_for_layout_match": false,
          "required_for_core_panel": false,
          "columns": [
            {
              "name": "AgentId",
              "type": "string",
              "role": "key",
              "note": ""
            }
          ],
          "column_groups": [
            {
              "name": "Agent attributes",
              "headers": "inspect_export",
              "note": "Descriptive agent fields. Inspect the headers in each export. Never use them to identify people."
            }
          ],
          "note": "Present in some exports and absent in others."
        }
      ]
    }
  ],
  "derived_datasets": [
    {
      "id": "summary.m365_credits_person_week",
      "label": "Microsoft 365 credits summary",
      "from_file_ids": [
        "consumption.m365_credits"
      ],
      "grain": [
        "PersonId",
        "WeekStart"
      ],
      "build_steps": [
        5,
        6
      ],
      "built_by": "Sum credits across services and spending policies after G05, and across days if the export is daily. Include sessions summed across policies only when G05b passes."
    },
    {
      "id": "summary.github_credits_person_week",
      "label": "GitHub credits summary",
      "from_file_ids": [
        "consumption.github_credits"
      ],
      "grain": [
        "PersonId",
        "WeekStart"
      ],
      "build_steps": [
        6
      ],
      "built_by": "Sum credits across days if the export is daily. A weekly export is already one row per person-week."
    },
    {
      "id": "summary.github_activity_person_week",
      "label": "GitHub activity summary",
      "from_file_ids": [
        "github.activity"
      ],
      "grain": [
        "PersonId",
        "WeekStart"
      ],
      "build_steps": [
        6
      ],
      "built_by": "Apply the measure rules, aggregating across days only if the export is daily."
    },
    {
      "id": "summary.agent_person_week",
      "label": "Agent activity summary",
      "from_file_ids": [
        "agent.activity"
      ],
      "grain": [
        "PersonId",
        "WeekStart"
      ],
      "build_steps": [
        6
      ],
      "built_by": "Sum sessions across agents. Derive any person-level returning flag explicitly."
    },
    {
      "id": "panel.person_week",
      "label": "Combined person-week panel",
      "from_file_ids": [
        "person_query.person"
      ],
      "grain": [
        "PersonId",
        "WeekStart"
      ],
      "build_steps": [
        7
      ],
      "built_by": "Start with the Person Query and left join each summary. Keep every Person Query row."
    }
  ],
  "person_query_metric_families": [
    {
      "family": "Collaboration",
      "kind": "weekly_total",
      "headers": [
        "Collaboration hours",
        "External collaboration hours",
        "Meeting hours",
        "Email hours",
        "Chat hours",
        "After hours collaboration hours"
      ]
    },
    {
      "family": "Focus",
      "kind": "weekly_total",
      "headers": [
        "Available to focus hours",
        "Uninterrupted hours"
      ]
    },
    {
      "family": "Network",
      "kind": "stock",
      "headers": [
        "Internal network size",
        "External network size",
        "Strong ties",
        "Diverse ties",
        "Network outside organization"
      ]
    },
    {
      "family": "Copilot eligibility",
      "kind": "day_count",
      "headers": [
        "Total Copilot enabled days"
      ]
    },
    {
      "family": "Copilot activity, totals",
      "kind": "weekly_total_or_day_count",
      "headers": [
        "Total Copilot actions taken",
        "Total Copilot active days"
      ]
    },
    {
      "family": "Copilot activity, by surface",
      "kind": "weekly_total_or_day_count",
      "headers": [
        "Copilot actions taken in Copilot Chat",
        "Copilot actions taken in Microsoft 365 Copilot app",
        "Days of active Copilot Chat usage",
        "Days of active Copilot Chat (legacy web) usage",
        "Days of active Copilot usage in Excel",
        "Days of active Copilot usage in Loop",
        "Days of active Copilot usage in OneNote",
        "Days of active Copilot usage in Outlook",
        "Days of active Copilot usage in Powerpoint",
        "Days of active Copilot usage in Teams",
        "Days of active Copilot usage in Word"
      ]
    },
    {
      "family": "Cowork",
      "kind": "weekly_total_or_day_count",
      "headers": [
        "Cowork tasks submitted",
        "User-initiated Cowork tasks submitted",
        "Days of active Cowork usage"
      ]
    },
    {
      "family": "Unlicensed Copilot Chat",
      "kind": "weekly_total_or_day_count",
      "note": "A separate metric family for Copilot Chat use without a Microsoft 365 Copilot licence. Inspect the export for its headers and keep it separate from licensed Copilot metrics.",
      "headers": []
    }
  ],
  "relationships": [
    {
      "id": "J1",
      "from_id": "consumption.m365_credits",
      "to_id": "consumption.people_metadata",
      "on": [
        "PeopleHistoricalId"
      ],
      "cardinality": "many_to_one",
      "purpose": "Microsoft 365 Copilot licence evidence, before aggregation"
    },
    {
      "id": "J2",
      "from_id": "consumption.github_credits",
      "to_id": "consumption.people_metadata",
      "on": [
        "PeopleHistoricalId"
      ],
      "cardinality": "many_to_one",
      "purpose": "Check that each historical person record exists. `IsCopilotLicensed` describes Microsoft 365 only and cannot confirm GitHub eligibility."
    },
    {
      "id": "J3",
      "from_id": "consumption.m365_credits",
      "to_id": "consumption.policy_metadata",
      "on": [
        "SpendingPolicyId"
      ],
      "cardinality": "many_to_one",
      "purpose": "Check that each spending policy exists in the metadata."
    },
    {
      "id": "J4",
      "from_id": "agent.activity",
      "to_id": "agent.metadata",
      "on": [
        "AgentId"
      ],
      "cardinality": "many_to_one",
      "purpose": "Optional enrichment, only when the metadata file is supplied"
    },
    {
      "id": "J5",
      "from_id": "summary.m365_credits_person_week",
      "to_id": "panel.person_week",
      "on": [
        "PersonId",
        "WeekStart"
      ],
      "cardinality": "one_to_one",
      "purpose": "Left join onto the spine"
    },
    {
      "id": "J6",
      "from_id": "summary.github_credits_person_week",
      "to_id": "panel.person_week",
      "on": [
        "PersonId",
        "WeekStart"
      ],
      "cardinality": "one_to_one",
      "purpose": "Left join onto the spine"
    },
    {
      "id": "J7",
      "from_id": "summary.github_activity_person_week",
      "to_id": "panel.person_week",
      "on": [
        "PersonId",
        "WeekStart"
      ],
      "cardinality": "one_to_one",
      "purpose": "Left join onto the spine"
    },
    {
      "id": "J8",
      "from_id": "summary.agent_person_week",
      "to_id": "panel.person_week",
      "on": [
        "PersonId",
        "WeekStart"
      ],
      "cardinality": "one_to_one",
      "purpose": "Left join onto the spine"
    },
    {
      "id": "J9",
      "from_ids": [
        "github.by_feature",
        "github.by_language_feature",
        "github.by_language_model",
        "github.by_model_feature"
      ],
      "to_id": "panel.person_week",
      "on": [
        "PersonId",
        "WeekStart"
      ],
      "cardinality": "linked_detail",
      "purpose": "Never joined wide. Keep as separate detail tables keyed to the panel"
    }
  ],
  "measures": [
    {
      "id": "M01",
      "file_id": "consumption.m365_credits",
      "columns": [
        "Total Copilot Credits used"
      ],
      "kind": "additive",
      "person_week_rule": "Sum across services and spending policies after G05 passes, and across days when the export is daily."
    },
    {
      "id": "M02",
      "file_id": "consumption.m365_credits",
      "columns": [
        "Session count"
      ],
      "kind": "conditionally_additive",
      "person_week_rule": "Sum across services, and across days when the export is daily. Sum across `SpendingPolicyId` rows only when G05b passes. Sessions are not people."
    },
    {
      "id": "M03",
      "file_id": "consumption.m365_credits",
      "columns": [
        "Spending policy limit",
        "User limit"
      ],
      "kind": "limit",
      "person_week_rule": "Never sum. Keep per policy as linked detail."
    },
    {
      "id": "M04",
      "file_id": "consumption.github_credits",
      "columns": [
        "Total GitHub AI Credits used"
      ],
      "kind": "additive",
      "person_week_rule": "Sum across days when the export is daily. Keep separate from Microsoft 365 credits."
    },
    {
      "id": "M05",
      "file_id": "github.activity",
      "columns": [
        "Code completions accepted",
        "Code completions suggested",
        "User-initiated chat requests"
      ],
      "kind": "additive",
      "person_week_rule": "Sum across days when the export is daily. Acceptance rate is summed accepted divided by summed suggested, with a stated zero-denominator rule."
    },
    {
      "id": "M06",
      "file_id": "github.activity",
      "columns": [
        "Agent adoption"
      ],
      "kind": "indicator",
      "person_week_rule": "Do not sum. For a daily export, state a derived weekly rule, for example any positive day in the week. For a weekly export, use the exported value."
    },
    {
      "id": "M07",
      "file_id": "github.by_feature",
      "columns": [
        "Feature Usage Count"
      ],
      "kind": "additive_within_file",
      "person_week_rule": "Sum days within this file when daily, retaining Feature for each person-week. Keep as linked detail. Never add to another breakdown."
    },
    {
      "id": "M08",
      "file_id": "github.by_language_feature",
      "columns": [
        "Usage count by language and feature"
      ],
      "kind": "additive_within_file",
      "person_week_rule": "Sum days within this file when daily, retaining Language and Feature for each person-week. Keep as linked detail. Never add to another breakdown."
    },
    {
      "id": "M09",
      "file_id": "github.by_language_model",
      "columns": [
        "Usage count by language and model"
      ],
      "kind": "additive_within_file",
      "person_week_rule": "Sum days within this file when daily, retaining Language and Model for each person-week. Keep as linked detail. Never add to another breakdown."
    },
    {
      "id": "M10",
      "file_id": "github.by_model_feature",
      "columns": [
        "Usage count by model and feature"
      ],
      "kind": "additive_within_file",
      "person_week_rule": "Sum days within this file when daily, retaining Model and Feature for each person-week. Keep as linked detail. Never add to another breakdown."
    },
    {
      "id": "M11",
      "file_id": "agent.activity",
      "columns": [
        "Conversational agent sessions"
      ],
      "kind": "additive",
      "person_week_rule": "Sum across agents to person-week."
    },
    {
      "id": "M12",
      "file_id": "agent.activity",
      "columns": [
        "Returning Agent 365 user (most recent and prior 7 days)"
      ],
      "kind": "window_indicator",
      "person_week_rule": "Do not sum across agents or weeks. A person-level flag, such as returning for any agent, is a stated derived definition."
    },
    {
      "id": "M13",
      "file_id": "agent.activity",
      "columns": [
        "Returning Agent 365 user (most recent and prior 28 days)"
      ],
      "kind": "window_indicator",
      "person_week_rule": "Do not sum across agents or weeks. A person-level flag, such as returning for any agent, is a stated derived definition."
    },
    {
      "id": "M14",
      "file_id": "person_query.person",
      "columns": [
        "Internal network size",
        "External network size"
      ],
      "kind": "stock",
      "person_week_rule": "Detect and record the measurement window. Values can repeat within a month. Average or deduplicate snapshots according to that window, never sum."
    },
    {
      "id": "M15",
      "file_id": "person_query.person",
      "columns": [
        "Days of active Copilot Chat usage",
        "Days of active Copilot Chat (legacy web) usage",
        "Days of active Copilot usage in Excel",
        "Days of active Copilot usage in Loop",
        "Days of active Copilot usage in OneNote",
        "Days of active Copilot usage in Outlook",
        "Days of active Copilot usage in Powerpoint",
        "Days of active Copilot usage in Teams",
        "Days of active Copilot usage in Word"
      ],
      "kind": "day_count",
      "person_week_rule": "Never add across surfaces to make total active days, because one day can include several surfaces. Use `Total Copilot active days`."
    }
  ],
  "missingness_states": [
    {
      "id": "observed_positive",
      "meaning": "A row exists with a positive value"
    },
    {
      "id": "observed_zero",
      "meaning": "A row exists with a recorded zero"
    },
    {
      "id": "absent_inactive_zero",
      "meaning": "No activity row within a supplied query's configured population and date window. Assume complete Viva Insights data and record zero activity"
    },
    {
      "id": "absent_unknown",
      "meaning": "The query is unavailable, the person or period is outside its scope, a known export problem applies, or an absent non-activity measure has no defined zero interpretation. Keep as unknown"
    },
    {
      "id": "observed_null",
      "meaning": "A row exists but the measure is null. Do not apply the omitted-inactive-row default to this cell"
    },
    {
      "id": "ineligible",
      "meaning": "Not eligible for that product in the week"
    },
    {
      "id": "outside_cohort",
      "meaning": "Not selected by the analysis cohort rule"
    }
  ],
  "gates": [
    {
      "id": "G01",
      "level": "MUST",
      "rule": "Require the Person Query plus at least one of Consumption, GitHub or Agent. Record which queries are supplied and identify each export's layout. Within each selected query, files with `required_for_layout_match` set to true must all be present. If files or key columns differ from this contract, stop and record the difference."
    },
    {
      "id": "G02",
      "level": "MUST",
      "rule": "Read identifiers as text. Do not change their case, trim them silently or fuzzy-match people."
    },
    {
      "id": "G03",
      "level": "MUST",
      "rule": "Confirm weekly grouping from the Person Query configuration or equivalent export metadata, then validate one row per `PersonId` and `MetricDate` and detect the weekday of `MetricDate`. Stop if grouping is unknown, daily or monthly. Do not assume Monday or Sunday."
    },
    {
      "id": "G03b",
      "level": "MUST",
      "rule": "Record each fact export's configured date grouping from query setup or equivalent export metadata, then validate `MetricDate` against it. Sparse dates alone cannot establish cadence. Recommend weekly grouping for all queries. Daily and weekly exports are supported. A weekly export must start on the same weekday as the Person Query. Stop for unknown grouping, any other period such as monthly, or a different weekly start."
    },
    {
      "id": "G04",
      "level": "MUST",
      "rule": "Assert uniqueness of each file's native key. Report duplicate counts and stop. Never keep an arbitrary first row."
    },
    {
      "id": "G05",
      "level": "MUST",
      "rule": "Before summing Microsoft 365 credits across `SpendingPolicyId`, count person-service-period keys with more than one positive-credit row. If any exist, obtain a documented confirmation that the rows record disjoint consumption."
    },
    {
      "id": "G05b",
      "level": "MUST",
      "rule": "Sum `Session count` across `SpendingPolicyId` rows only when the documented confirmation explicitly covers sessions. Otherwise keep sessions per policy."
    },
    {
      "id": "G06",
      "level": "MUST",
      "rule": "Join metadata many-to-one on unique keys. Report unmatched key counts without identifiers. Check that each `PeopleHistoricalId` maps to one `PersonId`."
    },
    {
      "id": "G07",
      "level": "MUST",
      "rule": "Bring only panel-bound summaries to one row per `PersonId` and `WeekStart` before joining. Apply the measure rules: sum days only for configured daily exports, credits across policies only after G05, and sessions across policies only after G05b. Otherwise retain sessions per policy outside the panel. Keep GitHub breakdown dimensions as linked detail under G11. Never expand weekly rows into invented daily rows."
    },
    {
      "id": "G08",
      "level": "MUST",
      "rule": "Left join each person-week summary onto the Person Query. Assert one-to-one, preserve the Person Query row count, and reconcile additive totals before and after."
    },
    {
      "id": "G09",
      "level": "MUST",
      "rule": "Assume supplied Viva Insights queries are complete within their configured population, date window and product filters. Omitted activity rows represent inactivity by default, so record zero activity within that scope without requiring separate completeness evidence. Do not zero-fill unavailable queries, out-of-scope periods or people, null cells in existing rows, rolling indicators, limits, or known export problems. Record partial boundary weeks separately. Eligibility is a separate attribute: Microsoft 365 licence evidence never confirms GitHub eligibility."
    },
    {
      "id": "G10",
      "level": "MUST",
      "rule": "Keep Microsoft 365 and GitHub credits in separate columns. Never derive tokens or currency from credits."
    },
    {
      "id": "G11",
      "level": "MUST",
      "rule": "Do not join GitHub breakdown files wide. Keep them as linked detail tables."
    },
    {
      "id": "G12",
      "level": "SHOULD",
      "rule": "Treat integration and cohort selection as separate, declared steps. An unavailable query does not establish non-use."
    },
    {
      "id": "G13",
      "level": "SHOULD",
      "rule": "Record each variable's measurement window. Network sizes can repeat within a month and are averaged rather than summed."
    },
    {
      "id": "G14",
      "level": "MUST",
      "rule": "Coverage, licence and additivity confirmations apply only to the export they were given for."
    },
    {
      "id": "G15",
      "level": "MUST",
      "rule": "Check `PersonId` overlap between sources before joining. Investigate low overlap against activity filters, omitted inactive people and configured populations before concluding that pseudonymisation differs. Do not repair identifiers by matching on attributes."
    },
    {
      "id": "G16",
      "level": "MUST",
      "rule": "Publish a group only when it contains at least 10 distinct people, counted with count_distinct(`PersonId`), in every filter and time slice. Withhold an entire related breakdown if remaining cells or totals would reveal a suppressed cell by subtraction. Never output identifiers."
    },
    {
      "id": "G17",
      "level": "MUST",
      "rule": "Describe joined results as associations. Credits, sessions and actions do not measure value, quality or productivity."
    }
  ]
}