Tip of the week: How to split the company/domain name from the email address?

Tip of the week: How to split the company/domain name from the email address?

We had a customer ask us this question. He has a bunch of requests along with the user details such as first name, last name and email address. He wanted a report showing the number of requests raised by each user. 

Seemed pretty forward. Since the email address field is unique for every user, all we had to do was drop the Email address field in the X axis and the request count on the Y axis and voila!



Now, he wanted to know the number of requests raised by each organization to which these users belonged to. In effect, requests raised by two users belonging to the same company had to be grouped together. i.e, 2 requests raised by adam@acme.com and 3 requests raised by mark@acme.com had to be grouped as 5 requests raised by the same company- acme.com. 

All you need to do is split the domain name- acme.com, from the email address and create it as a separate column. Here is the formula that will enable you to do that. 

substring("Email",indexof("Email",'@')+1)

Email is the name of the column containing the email addresses in my example (replace it with the name of your email column name and retain the rest of the formula as is). 

This is the result you will get. Click here for instructions to create the formula column. 





Now all you need to do is drop the output column ("Domain" in my example) onto the X axis and request count on to the Y axis. And there you have it-




                New to ADSelfService Plus?