Count the Number of Opens/Clicks per recipient

[ChrisA93]

Hi, 

I've seen a few similar posts but none really get to what I need.

I'm hoping to segment customers based on their engagement i.e. Opened X times = High etc. For this I hoped to have each recipeint with the number of opens/clicks pull through for a particular time period.

Example query I'd like to use to split contacts: number of opens greater than X

When trying to get the count of opens/clicks by recipient I can't seem to find what i need.

I'm doing this in a workflow and have tried Count([url/@type]=1) for clicks and grouping by recipient PK. 

How would you achieve this?

[david--garcia · Accepted solution]

Something like this??

Query the Recipient tracking logs (nms) nms trackingLogRcp

Here is the XML configuration of the activity

    <query collision="0" distinct="true" img="nms:activities/query.png" keepAllExtraData="false"
           label="undefined" mask="0" name="queryCountURL" noAutoPk="true" noAutoPkFilter="0"
           onError="0" recipientLink="" runOnSimulation="true" schema="nms:trackingLogRcp"
           timezone="_inherit_" useSource="0" x="520" y="80">
      <transitions>
        <result enabled="true" label="Result" name="result" x="670" y="104"/>
      </transitions>
      <select>
        <node alias="@recipient" analyze="false" expr="[@recipient-id]" groupBy="1"
              label="recipient"/>
        <node alias="@count" analyze="false" expr="count([url/@type])" groupBy="0"
              label="Count"/>
        <node alias="@urlType" analyze="false" expr="[url/@type]" groupBy="1" label="Type (Url)"/>
      </select>
      <where displayFilter="undefined" filterName="backGroundFilterFrm" id="1663107605"/>
      <outputFilter displayFilter="count greater than 2 and type (Url) equal to Open">
        <condition boolOperator="AND" compositeKey="" dependkey="" enabledIf="" expr="@count &gt; 2"
                   internalId="1733493285"/>
        <condition compositeKey="" dependkey="" enabledIf="" expr="@urlType = 2"
                   internalId="1742864937"/>
      </outputFilter>
      <humanCond>Query: undefined</humanCond>
    </query>

[ChrisA93]

Hi @DA 

Brilliant thank you!