It's great to know how many users you have, but in a lot of B2B communities, folks want to know how many unique companies are engaged in community, and how many users from each company.
Analytics, as we've touched on before, is more 'as it happens' — if we want to know the total number of users that exist that belong to a distinct company, we need to use the API to get a total list of users, then use excel to figure out how many from each domain exist.
First, get your list of users — I use a function like https://success.vanillaforums.com/api/v2/users?limit=5000
then add .csv after users to download it in a CSV format that plays nicely with excel.
For this exercise, I only care about their email domains, not the rest of the info in that report, so to make things easy I'll delete every column except for the email column.
In the column next to email domain I enter this excel formula:
=MID(A2,SEARCH("@",A2)+1,10000)
Next, I copy that down the whole column to extract the domains.
Now, my column "B" should just contain domains:
Ok, so now we've got our domains, how can we figure out which ones are unique?
Using the Unique function, Excel will generate this list:
Ok, now we're making progress!
Let's find the total number of users for each domain, we can do that with the COUNTIF function:
=COUNTIF(B2:B5000, E6)
Basically I'm asking how many times the value in e6 occurs in column B:
And…. voila! I've got a list of all users in the community with a particular domain.
Now, on to colour coding! 🤓
