Before we start, if you’re just wanting to email all members of the group you can “select all” then choose “E-Mail Users” from the action menu when viewing the group membership within SharePoint. This opens an Outlook window with all the addresses pre-populated.
But, if you still need an excel sheet of membership for another purpose (perhaps to format a sign-in sheet, generate documents with mail merge, share the list with others, etc.) follow these steps:
With the group open in SharePoint, copy the URL and note the ID number at the end of the URL
Open a new excel workbook
From the “Data” tab, select “New Query” –> “From Other Sources” –> “From OData Feed“
Paste the group URL in the prompt but delete everything after the site address and replace with the following, replacing “6” with your group’s ID from step 1 /_api/Web/SiteGroups/GetByID(6)/Users
When prompted for login credentials you’ll use “Windows” for on-prem/server and “Organizational account” for O365.
Click “Edit” once the group loads so we can choose which columns to keep/delete
Ctrl+click the column headers you want to keep
Right-click a header of a column you’re keeping and select “Remove Other Columns”
Close and Load
You should now see your group membership and email addresses (and any other fields you kept). Save this somewhere and, if updates are made in the future, just click “Refresh all” to bring in new members and remove old.
Updated 8/8/2019: Added step 5 to show the login credentials prompt.
I have an outstanding group of forward-thinking, hard-working volunteers who gather monthly to discuss ways in which we can improve our implementation and utilization of SharePoint at LMH Health. Tomorrow is our last meeting of the year, and we’ll be spending the majority of our meeting poring over abandoned search queries and “no result” queries provided to us by the out-of-the-box SharePoint usage and popularity reports.
Is it worth the time?
Absolutely. No matter how big your company is, investing time in improving search – even if it will only save a searcher 5 seconds – is the logical action from both a financial and efficiency standpoint. Here are some real-world stats from LMH Health (a relatively small health system) to further convince you:
19% of our annual SharePoint site visits are from someone performing a search query
That’s 18,972 searches per year, or about 75 each day
If each search takes thirty seconds, we spend 158 hours searching annually
If we play with a $20/hr wage, we would be spending $3160/year searching or $0.17 per search
If we can improve search so that we reduce that thirty seconds to twenty seconds, we’ll save 52.7 hours and $1054/year
Annual costs and savings scenario of SharePoint search times
So if saving money and time is important to you, this is worth exploring with your governance committee or SharePoint team.
How to get your search analytics/reports
Note: You must be at least a site collection admin to access the search reports.
1. Go to Site Settings –> “Popularity and Search Reports” under Site Collection Administration
2. I recommend the three reports below (all queries by month except “Query rule”) for a high-level overview with which your committee will be able to assist.
Note: You could individually use the Query Rule report to analyze the success (or lack thereof) of any query rules you’ve implemented, but I wouldn’t involve your committee in its analysis.
Available search reports as a site collection admin
3. Once those files are downloaded, I’d combine each of the months (each tab of each report is a different month) using Power BI or some excel magic to determine the queries most frequently searched on your site collection (by summing query text counts across tabs)
Engage your governance committee
Now it’s time to present your findings to your committee members. At our meeting we’ll be splitting into groups and each taking a portion of the top abandoned and “no results” queries to determine:
What the searchers were attempting to discover on our intranet using that query text
What synonyms we can come up with to add to a synonym dictionary or query rules to improve search.
Synonym dictionary is installed via PowerShell for on-prem only
Query rules can be on-prem OR hybrid and are a way of promoting certain results based on rules you establish
For my group, I’m using two files: a search thesaurus worksheet and a search analysis worksheet.
Search thesaurus
This is the “blank slate” I’ll be sharing with each group, challenging them to fill the rest of the rows with words they use that often don’t bring up the correct result because the actual result is using a variation of the query
The second worksheet is focused on analyzing actual, real search queries you collected from the SharePoint search reports. Pick the highest occurring results from the abandoned and “no result” reports and include them in the tables. Have your committee speculate as to what the desired resource actually was and provide alternative names/words so that you can build rules and/or synonyms around it.
Always follow up on governance tasks and finish your part of the work before your next meeting. Then you can share the results/impact with your members, celebrating your collaboration.
Once we’re finished with this activity tomorrow, I’ll collect the results and add appropriate suggestions to our synonym dictionary we deploy via PowerShell. There may also be suggestions made that would make great query rules.
For example, if one of your “top queries” is consistently “Dress code,” maybe you’ll create a query rule that provides the actual (and hopefully only) dress code policy at the top of results in its own promote block.
An effective governance committee is one that is put to use. Leverage your organization’s knowledge and inspire your members to be daily advocates for your digital workplace. It’s as much theirs as yours, and we make it better by building it for and with the users.
The Burton Group says “Governance uses people, process, technology, and policies to define a service, resolve ambiguity, and mitigate conflicts within an organization.” I would only add that it specifically helps create a consistent user experience which, in turn, promotes better adoption. I like the Burton Group’s definition because it doesn’t rely solely on rules/control, or the quality of the final governance document or on SharePoint itself, but on the people.
A successful implementation of SharePoint in your organization starts and ends with people from server admins to end users who have clear expectations and an understanding of proper usage and capabilities. The governance plan provides the written documentation for regular reference and sharing. People are still responsible for making it successful.
When I set out to create my own SharePoint Governance Plan, I had two objectives:
Create a plan that’s flexible enough to still encourage innovation and creativity
Create a plan written for more than IT professionals; I wanted to create an accessible, consumable (less than 20 pages) plan that avoided using too much industry jargon and encouraged an ongoing discussion with end users in the spirit of continuous improvement
