Count the Total of specific values in {{webResponse}}

[Matthew Challenger]

Looking for a bit of assistance here. I can't seem to get this working.

My use-case is click data from marketing emails. I'm trying to update a field with the # of unique (distinct) link clicks of a particular link.

I'm using an API call to return all click data in a {{webResponse}} object.

The response data looks something like this: 

"ClickData": [
    
    {
      "UserEmail": "test1@gmail.com",

      "LinkUrl": "https://www.google.com/",
      
      "Click Date": "2024-01-01"

    },

    {

      "UserEmail": "test1@gmail.com",

      "LinkUrl": "https://www.google.com/",

      "Click Date": "2024-01-01"

    },

    {

      "UserEmail": "test2@gmail.com",

      "LinkUrl": "https://www.google.com/",

      "Click Date": "2024-01-01"

    },

    {

      "UserEmail": "test2@gmail.com",

      "LinkUrl": "https://www.adobe.com/",

      "Click Date": "2024-01-01"
    },

    {

      "UserEmail": "test3@gmail.com",

      "LinkUrl": "https://www.apple.com/",

      "Click Date": "2024-01-01"
    }

]

Now imagine I want to count all unique clicks on "www.google.com" from users. My expected value would be 2, since test1@gmail.com (clicked twice, but should count only once) and test2@gmail.com were the distinct contacts that clicked on the link.

Any thoughts on how to accomplish this?

I've tried different approaches of variables, functions and advanced branching but nothing seems to work.

[Bill Sheboy]

Hi @Matthew Challenger 

This is possible, and...

It would likely take me as longer to implement and fully test this than for you to do so...primarily because you have access to the source sending the message, and so can foresee any challenges and edge cases.

Some recommendations to get this started: 

• pair up with a teammate on this to validate your understanding of the problem
• as you work, write every step to the audit log, confirming results as you go
• create your parsing / matching expressions in a text editor which can match the brackets and parentheses to validate them, and then paste them into the rule  (You will likely need multiple replace, replaceAll, and match expressions to do this scenario)
• remove all of the extraneous characters before trying to parse with
  • replaceAll("(\n| |\")","")
• do this incrementally: get a count for distinct UserEmail values, then LinkUrl ones; use the lessons learned to then get the combined count
• big hint: after doing the above, use split("}") to gather the records before final parsing

 If you get stuck, please post your rule details so the community can help.

Kind regards,
Bill

[Matthew Challenger]

Hi @Bill Sheboy I've tried some RegEx matching and Splitting already, but not with that pattern or in that way. I'll give it a shot in the coming days and let you know. I'm hoping this points me in the right direction of a solution. Thank you!

[Matthew Challenger]

Also hoping for some advice from @Bill Sheboy, as a lot of the approaches I've tried so far are based on community questions where you shared valuable insights in helping others with somewhat similar challenges.

[Vikrant Yadav]

Hi @Matthew Challenger  For getting the count of an email address from a JSON response, you can use this smartvalue : 

{{webhookData.ClickData.UserEmail.match(".*(test1@gmail.com).*").size}}

It gives count result : 2. Count number of times test1@gmail.com comes in a JSON body. 

[Matthew Challenger]

Sorry, maybe I wasn't clear.

I would like to know how many (distinct) users clicked on "https://www.google.com".

If "test1@gmail.com" had 10 records of click data, I would still only want to count them once.

[Vikrant Yadav]

okay @Matthew Challenger  I understand now, 

Try this smartvalue : {{webhookData.ClickData.UserEmail.distinct.size}}

It gives you the count of distinct email address.

Above date it gives : 3 

[Matthew Challenger]

Yes, I would only want 2, because test3@gmail.com never clicked on the specific link I'm looking for.

[Vikrant Yadav]

Hi @Matthew Challenger  Here is the solution after long effort :), I am not sure, if it works for you or not, for me it's working : 

Use this smartvalue to get email address mapped with google.com Link URL. 

{{#webhookData.ClickData}} {{#if(equals(LinkUrl, "https://www.google.com/"))}} {{UserEmail}} {{/}} {{/webhookData.ClickData}}

Output : test1@gmail.com test1@gmail.com test2@gmail.com

Now create a variable and put this output in Smart value 

To get distinct email address out of these three, use smartvalue : 

{{GetMail.trim().replaceAll("\\s+", " ").split(" ").distinct.size}}

Output : 2

GetMail is my variable. Kindly replace it with yours.