Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
HI,
Data:
LOAD
*,
subfield(action_email,'@',-1) as email_domain
FROM DataSource;
Now use Count(Distinct email_domain)
Hope this helps you.
Regards,
Jagan.
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;