We have been on a journey the past two weeks to deploy Copilot Reports with the new user activity reporting endpoint. While the journey has taken some time, we have arrived at the final step which is deploying our reports! You might have already performed some of this already, but I developed a template to make your life easier.
At the end of this article, you will have a report deployed to help you track week over week utilization of Copilot for Microsoft 365 within your organization. As a bonus, we will implement a few other reports to help you along the way on your adoption journey.
By the way, if you have not deployed Copilot for Microsoft 365 yet, you still want to deploy this report. There is a special page that will identify potential top users of Microsoft 365 within your organization. They are generally primed for being a participant in a Copilot for Microsoft 365 pilot program and potentially serve as a champion for your organization.
First Things First – I Made an Error
If you have been following this series, I discovered an error in our data flow code. I resolved this error on November 16th, 2024 at 9:30 AM eastern time (New York City time) and updated my GitHub repository. If you have been following this series before that correction has been made, you will need to edit the CopilotData_Raw table. You can open the advanced editor and replace it with the following code:
let
GetCopilotData = (Params) =>
let
Path = if Text.Length(Params) > 0 then Params else "$top=1000",
Source = Json.Document(
Web.Contents(
"https://graph.microsoft.com/beta/reports/getMicrosoft365CopilotUsageUserDetail(period='D30')",
[RelativePath = "?" & Path, Headers = [Authorization = "Bearer " & #"Get-AccessToken"()]]
)
),
NextList = @Source[value],
result = try
@NextList & @GetCopilotData(Text.AfterDelimiter(Source[#"@odata.nextLink"], "?"))
otherwise
@NextList
in
result,
CopilotData = GetCopilotData(""),
#"Converted to table" = Table.FromList(
CopilotData,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Expanded Column1" = Table.ExpandRecordColumn(
#"Converted to table",
"Column1",
{
"reportRefreshDate",
"userPrincipalName",
"displayName",
"lastActivityDate",
"copilotChatLastActivityDate",
"microsoftTeamsCopilotLastActivityDate",
"wordCopilotLastActivityDate",
"excelCopilotLastActivityDate",
"powerPointCopilotLastActivityDate",
"outlookCopilotLastActivityDate",
"oneNoteCopilotLastActivityDate",
"loopCopilotLastActivityDate",
"copilotActivityUserDetailsByPeriod"
},
{
"reportRefreshDate",
"userPrincipalName",
"displayName",
"lastActivityDate",
"copilotChatLastActivityDate",
"microsoftTeamsCopilotLastActivityDate",
"wordCopilotLastActivityDate",
"excelCopilotLastActivityDate",
"powerPointCopilotLastActivityDate",
"outlookCopilotLastActivityDate",
"oneNoteCopilotLastActivityDate",
"loopCopilotLastActivityDate",
"copilotActivityUserDetailsByPeriod"
}
),
#"Removed columns" = Table.RemoveColumns(
#"Expanded Column1",
{"displayName", "copilotActivityUserDetailsByPeriod"}
),
#"Renamed columns" = Table.RenameColumns(
#"Removed columns",
{
{"lastActivityDate", "Last Activity Date"},
{"copilotChatLastActivityDate", "Copilot Chat"},
{"microsoftTeamsCopilotLastActivityDate", "Copilot for Teams"},
{"wordCopilotLastActivityDate", "Copilot for Word"},
{"excelCopilotLastActivityDate", "Copilot for Excel"},
{"powerPointCopilotLastActivityDate", "Copilot for PowerPoint"},
{"outlookCopilotLastActivityDate", "Copilot for Outlook"},
{"oneNoteCopilotLastActivityDate", "Copilot for OneNote"},
{"loopCopilotLastActivityDate", "Copilot for Loop"}
}
)
in
#"Renamed columns"In short, I missed the paging requirement for this endpoint. I assumed that this endpoint was like the other graph reports endpoints. But then again, we all know what happens when we assume! Regardless, with this small change in place, you are ready to collect data for your entire organization.
If you are not up to speed, go back to my first post in this series and start collecting data to build your Copilot reports.
Deploying the Copilot Reports Template
To get started, go my GitHub repository to download the Copilot Utilization Report template. It is a pbit file that we will be able to connect to the semantic model built off your Lakehouse. Once you have downloaded the template, you can open it. You will immediately receive an “error”, but that is simply because you need to connect to your semantic model:
With our template activated, we can save and deploy the report by publishing it to Power BI! Before we get too far, let’s do a quick tour of the report!
Copilot Reports – High Level Overview
The template comes with nine pages. Three are focused on Copilot Activity, four are focused on collaboration behaviors within the Microsoft 365, and the Collaboration Score page. There is also a report template page that is available for you to create additional custom reports.
Each page has a slicer panel to filter based upon your user information from Entra ID. With the caveat that your user information must be accurate, you can easily filter to see how users leverage tools within the Microsoft 365. You can filter department, company, office location, or even job title. You can also filter based upon licenses assigned to users as well!
In addition to slicers, each page has a help button that when activated, provides an overlay to help you understand the report visualizations. This will help you and any future consumers understand how the report is interpreted and how values are calculated. Because of this, I will not go into great detail, but give you a high-level overview of the pages themselves.
How to Use the Copilot Reports
There are three pages dedicated to Copilot utilization. The first page, Copilot activity, dives into the utilization of Copilot over the past week. It also allows you to trend utilization over time. Remember, we can only capture dates when someone last used Copilot. It is best to compare week over week due to inconsistencies with how Microsoft releases the data. However, we can smooth this out over a week instead.
The Active Copilot Users page allows us to quickly identify which users used Copilot over the past week. In addition, it shows how many applications it was leveraged within. For example, if you used Copilot for Microsoft 365 within Microsoft Teams, Word, and PowerPoint, you would have an app count of three. This can help you find users for a focus group to learn more about use cases.
The Inactive Copilot Users page allows you to identify who is not using their assigned license. During a pilot, we want to make sure that everyone is leveraging Copilot. This report helps us track and activate dormant licenses. It also helps us to find who has used Copilot for Microsoft 365 in the past but have not used it for over two weeks. This might prompt you to reassign licenses to users who have more time to spend exploring Copilot.
While not a perfect science, these three reports will help you drive a quality pilot program. Beyond your pilot, it will help you drive broader adoption across the organization as well.
How to Use the Activity Analysis Reports
Why do I care about utilization of Microsoft 365 tools? Remember, Copilot for Microsoft 365 is using data from your tenant. The better you use the platform, the better your experience with Copilot for Microsoft 365 will be!
Each page is dedicated to the core utilization metrics available from Microsoft Graph. While these are high level metrics, it is enough to understand general trends over the past thirty days. Our goal is to see balance between applications and drivers of quality collaboration.
How to Use the Collaboration Score Report
There is a difference between using software and adopting software. Even though Microsoft Teams became the standard since Skype for Business was retired in 2020, it is not well adopted. The concept of the Collaboration Score is to see how well people are using their collaboration tools. But how does it work?
The Collaboration Score focuses on behaviors that lead to sharing knowledge. You could have people who work in their OneDrive and private Teams Chats all day, but they are siloed. Even if they send 500 messages a day, they will have a low score. The reason? They do not collaborate with others in shared spaces!
The better users leverage centralized collaboration repositories, the broader the corpus of knowledge will be. In addition, when new people join your organization, they will be able to draw upon that knowledge right away with Copilot. If it is siloed, it might take a while for that knowledge to be shared through individual collaboration tools like private chats and OneDrive.
What the Heck is a Collaboration Score?
I credit the Collaboration Score to my friend and colleague Kristen Hubler. She recently wrote a great article on using versus adopting software. We collaborated together to find a way to identify top users of Microsoft 365 users. What started as a hunch proved out time over time when it came to Copilot.
The concept was simple – the more diverse your Microsoft 365 utilization, the better their Copilot experience. The higher the Collaboration Score, the higher their self-reported use case count and savings. In fact, my favorite thing to do is temper “Copilot sucks” feedback with Collaboration Scores. If a user says that with a Collaboration Score of 10, it is likely because they do not use their tools well. However, if they collaborate well, there is a better corpus of knowledge to draw upon with Copilot.
In addition to the data they will have access to, it shows a user’s propensity to use new technology. It has been four years since Microsoft Teams became the official standard for collaboration. If some of your users have not been adopting these tools, they might struggle with Copilot.
What does this all mean? You might need to work on your adoption of Microsoft 365 in addition to Copilot. However, that can be part of your overall rollout of Copilot for Microsoft 365 as you go forward.
Next Steps
With these reports in place, you can now track utilization of Copilot for Microsoft 365 for adoption. If your adoption is low, start working with your organizational change management team within your organization to get the most out of your investment. Technology professionals do not consider the behavior changes you need to make with a tool like Copilot. However, having the data can help shape that roadmap.
After you have deployed this report, drop some comments below on how it is going! I want to know what you discovered with your data and some changes you might make! Also, please do not hesitate to provide feedback as I will likely be releasing new versions of this report template over time.
