Analytics Data model

This is a description of the data model used in the activity analytics solution.

SQL tables

The following tables are used contain the activity data showed in the report:

Table	Description	Data type
ActivitiesWeekly	Pivoted Activity metrics. Data in rows.	bigint
ActivitiesWeeklyColumns	Activity metrics	bigint
UsageWeekly	Platform metrics	bool

Additional tables:

Table	Description
TraceLogs	Aggregation logs

Available metrics and data

Even though the data available in the APIs is aggregated daily, the report shows the data aggregated weekly. We sum the activity from Monday to Sunday. The date shown in the report will be a Monday, that represents the activity of the week that starts that Monday.

The following metrics show the number of activities of this type performed by the user. These are available in the ActivitiesWeekly and ActivitiesWeeklyColumns tables.

Activity metrics

OneDrive

• Shared Externally
• Shared Internally
• Synced
• Viewed/Edited

Outlook

• Emails Read
• Emails Received
• Emails Sent
• Outlook Meetings Created
• Outlook Meetings Interacted

SharePoint

• Shared Externally
• Shared Internally
• Synced
• Viewed/Edited

Teams

• Private Chats
• Calls
• Meetings Attended
• Meetings Organized
• Adhoc Meetings Attended
• Adhoc Meetings Organized
• Scheduled OneTime Meetings Attended
• Scheduled OneTime Meetings Organized
• Scheduled Recurring Meetings Attended
• Scheduled Recurring Meetings Organized
• Audio Duration Seconds
• Video Duration Seconds
• Screenshare Duration Seconds
• Urgent Messages
• Post Messages
• Reply Messages

Yammer

• Liked
• Posted
• Read

Copilot

Hosts where Copilot has been used:

• Assist365
• Bing
• BashTool
• DevUI
• Excel
• Loop
• M365AdminCenter
• M365App
• Office
• OneNote
• Outlook
• Planner
• PowerPoint
• SharePoint
• Stream
• Teams
• VivaCopilot
• VivaEngage
• VivaGoals
• Whiteboard
• Word

Copilot interactions

• Number of chat interactions.
• Number of files where there was an interaction (ie. used Copilot to create a slide in PowerPoint).
• Number of meeting where Copilot was used.

Usage metrics

The following metrics' values are true/false. These are available in the UsageWeekly table.

Microsoft 365 Apps (Office apps)

Totals per OS	Totals per App

• Windows
• Mac
• Mobile
• Web

|

• Outlook
• Word
• Excel
• Powerpoint
• Onenote
• Teams

App per platform

Windows	Mac	Mobile	Web

• Outlook Windows
• Word Windows
• Excel Windows
• Powerpoint Windows
• Onenote Windows
• Teams Windows

|

• Outlook Mac
• Word Mac
• Excel Mac
• Powerpoint Mac
• Onenote Mac
• Teams Mac

|

• Outlook Mobile
• Word Mobile
• Excel Mobile
• Powerpoint Mobile
• Onenote Mobile
• Teams Mobile

|

• Outlook Web
• Word Web
• Excel Web
• Powerpoint Web
• Onenote Web
• Teams Web

Teams Devices by Platform/OS

• Used Web
• Used Mobile
• Used Windows
• Used Linux
• Used Mac
• Used Chrome
• Used iOS
• Used Android
• Used WinPhone

Yammer Devices by Platform/OS

• Used Web
• Used Mobile
• Used Others
• Used iPhone
• Used iPad
• Used Android
• Used WinPhone
• Platform Count

Raw data

The raw data for the report can be found in the following tables of the database:

Activity tables

• dbo.onedrive_user_activity_log
• dbo.outlook_user_activity_log
• dbo.platform_user_activity_log
• dbo.sharepoint_user_activity_log
• dbo.teams_user_activity_log
• dbo.teams_user_device_usage_log
• dbo.yammer_device_activity_log
• dbo.yammer_user_activity_log

User details

• dbo.users
• dbo.user_license_type_lookups
• dbo.user_company_name
• dbo.user_country_or_region
• dbo.user_departments
• dbo.user_job_titles
• dbo.user_license_type_lookups
• dbo.user_office_locations
• dbo.user_state_or_province
• dbo.user_usage_locations
• dbo.provinces
• dbo.license_types
• dbo.countries
• dbo.cities

Audit events

• dbo.audit_events
• dbo.copilot_chats
• dbo.copilot_event_files
• dbo.copilot_event_meetings