How to indentify list of unique email domains | Community
Skip to main content
A
Anonymous
January 29, 2015
Solved

How to indentify list of unique email domains

  • January 29, 2015
  • 12 replies
  • 8925 views
Here is my business case. I want to indentify list of unique email domains in my database. Is there a way it can be done?

Here are a couple of approaches I thought but ran in to roadblocks.

1> A lead preformance report would have been great fit here if I can group by a subset of email address field. i.e. domain name. But it is not supported to be grouped by a subset of a field value.

2> I thought there might be a way to use API to get this kind of data out. 
For example, by using getLead by EMAIL 
as explained here,
https://community.marketo.com/MarketoArticle?id=kA050000000L3FlCAK

The question is if this call returns the 'exact' match of the email address passed. Like Rajesh@BrightAspects.com
or
it returns all the records which 'contain' the string I pass. So I can pass just the domain name.such as @BrightAspects.com to get a list of all email addresses in the database from that domain.

There are a couple of other ways to solve this but I am not looking to use webhooks, create a new field and can not assime that all Marketo leads are also in SFDC. Any other ideas?

Thanks

Rajesh





 
    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Jep_Castelein2

    Rajesh, you could use a new beta feature called "Munchtions" that allows you do add functions to Change Data Values. One function is "regex replace", which allows you to extract the domain from the email address (and many other things). This would be the syntax:

    • Smart List
      • trigger: Lead is Created
      • trigger: data value changes, attribute is "email address"
      • filter: email address is not empty
    • Flow
      • Change Data Value, Attribute "email domain", new value: ##REGEX_REPLACE({{lead.Email Address}},"/.+(?<=@)/","")##
    • ​Schedule
      • ​Run every time

    You will have to ask Support to enable this feature.

    Once you have the domain in a separate field, it should be much easier to count the number of unique domains, for example a Lead Performance report grouped by "email domain" (time period: all time).

    MODERATOR EDIT: This feature is only available for internal use.  Support is not able to enable this for customer use at this time.

    12 replies

    Show previous replies
    A
    Anonymous
    January 30, 2015
    Thanks Edward. I have used this method in past.

    Unfortunately, it would not work here. I forgot to mention that we have several millions of lead records so exporting and manipulation using Excel or any database tool etc is not feasible.

     
    Edward_Unthank_
    Edward_Unthank_
    New Participant
    January 29, 2015
    The easiest way would be to create a smart list that has all known leads ("is anonymous" = false), create a new temporary view that just contains "email address," export the list, and then do quick calculations in Excel.

    When you have it exported, you can do "text to columns" separated by "@" and then remove duplicates from the email domain column you just created. Select them in that column, and there's your count of unique email domains!

    Edward Unthank | Founder, Etumos
      Terms & ConditionsAccessibility statement

      Loading footer...