3 Replies Latest reply: Nov 4, 2015 4:21 AM by Kalmer Kaljuste RSS

    Counting domain names from email field

    Kalmer Kaljuste

      Hello!

       

      I haven't figured out how to count all the domain names from a email field.

      So what do i have? I have the action field where i can see what the clients did in my sent out email. also in the actions i can see the email addresses so it should be pretty easy. The problem is not with the counting, but the show top 30 most used domains.

       


      "count(distinct({$<action_type={'view', 'click'}>}action_email))" - easy part
      + count everything from '@xxx.com'

      + and show the most 30 used domains. (so in SQL there is a statement called LIMIT)

      + sort numerically Descending


      Thank you in advance!

        • Re: Counting domain names from email field
          Gysbert Wassenaar

          This will be a lot easier if you extract the domain from the email in the script as a new field:

               subfield(action_email,'@',-1) as email_domain

           

          Then you can simply use email_domain as dimension in a chart and use your count expression to count the email addresses per domain. You can use Dimension Limits (on the tab with the same name) to show the top 30 and you can sort the chart on the expression values in descending order.

          • Re: Counting domain names from email field
            jagan mohan rao appala

            HI,

             

            Data:

            LOAD

            *,

            subfield(action_email,'@',-1) as email_domain

            FROM DataSource;


            Now use Count(Distinct email_domain)


            Hope this helps you.


            Regards,

            Jagan.



              • Re: Counting domain names from email field
                Kalmer Kaljuste

                Thank you. I was sure that i must have done it already in loading in the data. I thought that there would be a "know-how" how to do that without the field.
                But it also solved my problem so Good job!.

                So here's my email and domain load script:

                !

                Emails:

                LOAD

                  autonumber(email_email & email_created) as emailkey,

                     autonumber(email_email & email_domain) as email_domain_ID,

                  `email_active`,

                    `email_created`,

                    `email_domain` as email_status,

                    `email_email`,

                    `email_hash`,

                    `email_lastupdate`,

                    `email_optin`,

                    `email_optintime`;

                SQL SELECT `email_active`,

                    `email_created`,

                    `email_domain`,

                    `email_email`,

                    `email_hash`,

                    `email_lastupdate`,

                    `email_optin`,

                    `email_optintime`

                FROM emails;

                 

                Domains:

                LOAD email_domain_ID,

                  subfield(email_email,'@',-1) as email_domain_name

                RESIDENT Emails;