Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Get Different Email Domain Count in WorkFlow

akshay2
Level 1
Level 1

I would like to query the Recipients table in Campaign and get an output with all the different email Domains ( of all recipients ) along with the count of these domains. 

My question is :

  1.  How can i achieve this in a Workflow 
  2.  How can i achieve this in the Javascript activity using sqlSelect / xtk:QueryDef operation 

Any help will be appreciated

 

thanks

a

1 Accepted Solution
wodnicki
Correct answer by
Community Advisor
Community Advisor

Hi,

 

1: Use a query activity, with nms:recipient as targeting/filtering dimensions, @ID>0 as where clause and select clause defined by Edit additional data:

  1. Add @domain, check the 'Group by' box next to it
  2. Add a col, type in count(@id)
  3. Click 'Advanced parameters...' link, Check Disable automatic adding of primary key

 

2: Don't use sqlSelect unless there is a clear reason, e.g. complex queries that have perf requirements. In querydef:

var recipients = xtk.queryDef.create(
  <queryDef schema="nms:recipient" operation="select" distinct="true">
    <select>
      <node expr="@domain" groupBy="1"/>
      <node expr="count(@id)" alias="@count"/>
    </select>
  </queryDef>).ExecuteQuery();
for each (var recipient in recipients.recipient) {
  logInfo(recipient.@domain, recipient.@count);
}

 

Thanks,

-Jon

View solution in original post

3 Replies
wodnicki
Correct answer by
Community Advisor
Community Advisor

Hi,

 

1: Use a query activity, with nms:recipient as targeting/filtering dimensions, @ID>0 as where clause and select clause defined by Edit additional data:

  1. Add @domain, check the 'Group by' box next to it
  2. Add a col, type in count(@id)
  3. Click 'Advanced parameters...' link, Check Disable automatic adding of primary key

 

2: Don't use sqlSelect unless there is a clear reason, e.g. complex queries that have perf requirements. In querydef:

var recipients = xtk.queryDef.create(
  <queryDef schema="nms:recipient" operation="select" distinct="true">
    <select>
      <node expr="@domain" groupBy="1"/>
      <node expr="count(@id)" alias="@count"/>
    </select>
  </queryDef>).ExecuteQuery();
for each (var recipient in recipients.recipient) {
  logInfo(recipient.@domain, recipient.@count);
}

 

Thanks,

-Jon

View solution in original post

akshay2
Level 1
Level 1

Hi Jon

Sorry my question was not clear.  What i would like to do is to group the results based on emailDomain and show the count. Something like this 

 

gmail.com       32 

yahoo.com      5

reditt.com        6

 

Thanks

a