Expand my Community achievements bar.

SOLVED

Get Different Email Domain Count in WorkFlow

Avatar

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

Avatar

Correct answer by
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

Avatar

Correct answer by
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

Avatar

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