You can query for email usernames and domains to identify records that share suspicious email patterns. This can support fraud review, duplicate review, and data cleanup when a set of applications or records uses similar usernames, uncommon domains, or disposable email providers.
Try a Slate example 💼
Copy this Suitcase ID and paste it in Database → Suitcase Import to import a pre-made example query:
82575928-d3b7-4548-821c-45201544b649:slate-admissions-showcase💡 Tip
Find this query and other importable examples in the Admissions Showcase environment.
Reviewing email patterns
Use this approach when you want to review a specific email address, username, or domain across person records. The example returns each person's reference ID, name, full email address, the portion of the email before the at sign, and the portion of the email after the at sign.
The split username and domain exports make it easier to sort, filter, and compare records when you are looking for repeated patterns in application submissions or inquiry records.
Building the query
Creating the query
Go to Queries / Reports.
Select New Query.
Configure the following settings:
Name: Email usernames and domains
Folder: Select the folder where the query should be saved.
Type: Configurable Joins
Base: Person
Select Save.
Adding person exports
Add exports similar to these so the results identify the person and the email address being reviewed:
Person Reference ID
Person First
Person Last
Person Email
Adding formula exports
The formula exports split the email address into separate review columns. These columns help you compare the username and domain without manually scanning the full email address.
Select Subquery Export.
Configure the following settings:
Name: Left of @
Type: Dependent subquery
Output: Formula
Formula:
LEFT(@Person-Email, CHARINDEX('@', @Person-Email) - 1)
Select Export, then select Person Email.
Select Save.
Select Subquery Export again.
Configure the following settings:
Name: Right of @
Type: Dependent subquery
Output: Formula
Formula:
RIGHT(@Person-Email, CHARINDEX('@', (REVERSE(@Person-Email))) - 1)
Select Export, then select Person Email.
Select Save.
Adding formula filters
The example includes two formula filters. Use one or both depending on whether you want to review a full email address, a username pattern, or a domain pattern.
Email by User
This active filter returns records where the full person email address matches the value in the formula. Replace the example email address with the address or pattern you want to review.
Select Subquery Filter.
Configure the following settings:
Name: Email by User
Status: Active
Type: Dependent subquery
Aggregate: Formula
Formula:
@Person-Email LIKE 'abc@gmail.com'
Select Export, then select Person Email.
Select Save.
Email by Domain
This inactive filter is available when you want to review records by domain instead of a full email address. Activate the filter when you want the query to return records for a specific domain.
Select Subquery Filter.
Configure the following settings:
Name: Email by Domain
Status: Inactive
Type: Dependent subquery
Aggregate: Formula
Formula:
RIGHT(@Person-Email, CHARINDEX('@', (REVERSE(@Person-Email))) - 0) LIKE '@gmail.com'
Select Export, then select Person Email.
Select Save.
Reviewing the results
Run the query and review the username and domain columns for repeated or unusual values. Add local filters, such as entry term, application status, population, or application round, when you need to focus review on a specific cohort.