Callbacks from Mandrill

Jaymer

I'm currently working on getting callbacks/webhooks from Mandrill to record opens/clicks/unsubs/etc into the database.
I'm using Node.js.

Writing the JS has been the toughest part... and I'm having trouble getting my meta-data to be returned. I'm sure its my fault, but thats where I'm at.
Planning to help TFORD & Mark with this when its finished.

jaymer...

--> jaymerTip

Jaymer

(reserved for links)

BenHayat

Ok, suppose you write a real time listener in Node.js to be subscribed to those callbacks and webhooks. How are you going to inform Aware about such data coming from an email server, so aware can react/do something about it?

Even if you go and write something/message/object to the DB, but then you have to do constant polling from the aware server. Under load, this will kill your Aware server.

Unless you have a better and cleaner way for Aware to listen to your Node.js event handler.

Jaymer

Ben, for many of my uses, no reason to bother Aware.
Since its Email, I have a OutgoingEmail BO with all emails.
I added some fields for Statistics back from Mandrill, just so they show up in the CRM (Open,Clicked,Bounce,Unsub).
I'm sending a meta-data object in the outgoing email to point to the record ID of the Email, so from Node, I just need to do a simple SQL update to 1 record in OutgoingEmail to update the type of event.
Screen Shot 2018-09-04 at 7.14.06 PM.png

I get a JSON string from Mandril, and with this I can parse the Email Row ID to update.
Screen Shot 2018-09-04 at 7.18.12 PM.png

BenHayat

Jaymer wrote
Ben, for many of my uses, no reason to bother Aware.
Since its Email, I have a OutgoingEmail BO with all emails.
I added some fields for Statistics back from Mandrill, just so they show up in the CRM (Open,Clicked,Bounce,Unsub).
I'm sending a meta-data object in the outgoing email to point to the record ID of the Email, so from Node, I just need to do a simple SQL update to 1 record in OutgoingEmail to update the type of event.
[attachment=1]Screen Shot 2018-09-04 at 7.14.06 PM.png[/attachment]

I get a JSON string from Mandril, and with this I can parse the Email Row ID to update.
[attachment=0]Screen Shot 2018-09-04 at 7.18.12 PM.png[/attachment]

Excellent. Node will work.
Great integration!

ACDC

Jaymer,
how do you reference the call back from Mandrill to the Outgoing Email message in AwareIM?

Are you embedding the OutgoingEmail. ID into the email somewhere, and then having it returned by the Mandrill call back or are you using the outgoing java mail message header ID and then somehow matching it up

I haven't worked with Mandril call back before but intend to do so soon, and was hoping to use http://xxxxxxxxx//xxxxxxxx/logonOp.awsp? to handle the call back

ACDC

I'm sending a meta-data object in the outgoing email to point to the record ID of the Email, so from Node, I just need to do a simple SQL update to 1 record in OutgoingEmail to update the type of event.

Okay, I should have paid more attention to your previous response, so you are embedding the OutGoingEmail.ID somewhere in the email

Jaymer

howdy
there's no way in hell you can process it through Aware login.
When you setup a webhook, it has to validate. Its clear what they do and expect. I'm not even sure in Aware how you'd force a Response 200, but they ain't gonna be sending stuff to an aware logon in our specific format.
Once you get past Validation, its a real PITA to deal with their Webhook tests.
AND, there's a HUGE time delay between sending a msg and getting webhook feedback, like HOURS. (am querying them soon about this).

But to answer your question, here is a submission via REST to send a message to 2 people, copied from Server Output window:

Calling REST URL https://mandrillapp.com/api/1.0/messages/send.json .Parameter string is {
"key": "thekeyxxx333222111",
"message": {
  "html": "test",
  "subject": "test123",
  "from_email": "sender@xxxsystems.com",
  "from_name": "Chris",
  "to":[{"email":"chris@gmail.com","name":"Aaron","type":"bcc"},{"email":"chris2@gmail.com","name":"Allan","type":"bcc"}],
  "headers": {
      "Reply-To": "chris@acme.com"  },
  "track_opens": true,
  "track_clicks": true,
  "auto_text": true,
  "merge": true,
  "merge_language": "mailchimp",
"recipient_metadata":[{"rcpt":"chris@gmail.com","values":[{"email_id":"834470"}]},{"rcpt":"chris2@gmail.com","values":[{"email_id":"834471"}]}],
  "subaccount": "roseville",
  "tags": [ "MyCustomTag"  ],
"merge_vars":[{"rcpt":"chris@gmail.com","vars":[{"name":"FNAME","content":"Aaron"}]},{"rcpt":"chris2@gmail.com","vars":[{"name":"FNAME","content":"Allan"}]}]
},
"ip_pool": "Main Pool"
}

There are 3 arrays in this JSON.
TO_LIST
MERGE_VARS
META_DATA
I build those strings in SQL SERVER, almost instantly. META_DATA is the actual Email ROWID in SQL (Aware's ID). So when it comes back, I can update it's Opened/Unsub/etc. details.

An email last night to 275 ppl was 66k in total REST JSON length. One of those arrays was 22k.
Can't imagine building this using only Aware.

MANDRILL DOCS/kBase
(personally, I don't like their docs. PepiPost was incredibly easy to follow. This has too many "circular references" and you keep going around thru the docs, knowing you saw something somewhere, but unable to find it.)
intro: https://mandrill.zendesk.com/hc/en-us/articles/205583287-Webhook-Format
format: https://mandrill.zendesk.com/hc/en-us/articles/205583307-Message-Event-Webhook-format

webhook submission - this is from their test. contains 6 events
You really want to parse this with Aware? If not, you'll need to pass it off to a JAVA plugin.

"mandrill_events:[{"event":"hard_bounce","msg":{"ts":1365109999,"subject":"This an example webhook message","email":"example.webhook@mandrillapp.com","sender":"example.sender@mandrillapp.com","tags":["webhook-example"],"state":"bounced","metadata":{"user_id":111},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa","_version":"exampleaaaaaaaaaaaaaaa","bounce_description":"bad_mailbox","bgtools_code":10,"diag":"smtp;550 5.1.1 The email account that you tried to reach does not exist. Please try double-checking the recipients email address for typos or unnecessary spaces."},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa","ts":1535736330},{"event":"soft_bounce","msg":{"ts":1365109999,"subject":"This an example webhook message","email":"example.webhook@mandrillapp.com","sender":"example.sender@mandrillapp.com","tags":["webhook-example"],"state":"soft-bounced","metadata":{"user_id":111},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa1","_version":"exampleaaaaaaaaaaaaaaa","bounce_description":"mailbox_full","bgtools_code":22,"diag":"smtp;552 5.2.2 Over Quota"},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa1","ts":1535736330},{"event":"open","msg":{"ts":1365109999,"subject":"This an example webhook message","email":"example.webhook@mandrillapp.com","sender":"example.sender@mandrillapp.com","tags":["webhook-example"],"opens":[{"ts":1365111111}],"clicks":[{"ts":1365111111,"url":"http:\\/\\/mandrill.com"}],"state":"sent","metadata":{"user_id":111},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa2","_version":"exampleaaaaaaaaaaaaaaa"},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa2","ip":"127.0.0.1","location":{"country_short":"US","country":"United States","region":"Oklahoma","city":"OklahomaCity","latitude":35.4675598145,"longitude":-97.5164337158,"postal_code":"73101","timezone":"-05:00"},"user_agent":"Mozilla\\/5.0 (Macintosh; U; IntelMac OS X 10.6; en-US; rv:1.9.1.8-) Gecko\\/20100317 Postbox\\/1.1.3","user_agent_parsed":{"type":"Email Client","ua_family":"Postbox","ua_name":"Postbox 1.1.3","ua_version":"1.1.3","ua_url":"http:\\/\\/www.postbox-inc.com\\/","ua_company":"Postbox, Inc.","ua_company_url":"http:\\/\\/www.postbox-inc.com\\/","ua_icon":"http:\\/\\/cdn.mandrill.com\\/img\\/email-client-icons\\/postbox.png","os_family":"OS X","os_name":"OS X 10.6 Snow Leopard","os_url":"http:\\/\\/www.apple.com\\/osx\\/","os_company":"Apple Computer, Inc.","os_company_url":"http:\\/\\/www.apple.com\\/","os_icon":"http:\\/\\/cdn.mandrill.com\\/img\\/email-client-icons\\/macosx.png","mobile":false},"ts":1535736330},{"event":"click","msg":{"ts":1365109999,"subject":"This an example webhook message","email":"example.webhook@mandrillapp.com","sender":"example.sender@mandrillapp.com","tags":["webhook-example"],"opens":[{"ts":1365111111}],"clicks":[{"ts":1365111111,"url":"http:\\/\\/mandrill.com"}],"state":"sent","metadata":{"user_id":111},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa3","_version":"exampleaaaaaaaaaaaaaaa"},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa3","ip":"127.0.0.1","location":{"country_short":"US","country":"United States","region":"Oklahoma","city":"Oklahoma City","latitude":35.4675598145,"longitude":-97.5164337158,"postal_code":"73101","timezone":"-05:00"},"user_agent":"Mozilla\\/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US; rv:1.9.1.8-) Gecko\\/20100317 Postbox\\/1.1.3","user_agent_parsed":{"type":"Email Client","ua_family":"Postbox","ua_name":"Postbox 1.1.3","ua_version":"1.1.3","ua_url":"http:\\/\\/www.postbox-inc.com\\/","ua_company":"Postbox, Inc.","ua_company_url":"http:\\/\\/www.postbox-inc.com\\/","ua_icon":"http:\\/\\/cdn.mandrill.com\\/img\\/email-client-icons\\/postbox.png","os_family":"OS X","os_name":"OS X 10.6 Snow Leopard","os_url":"http:\\/\\/www.apple.com\\/osx\\/","os_company":"Apple Computer, Inc.","os_company_url":"http:\\/\\/www.apple.com\\/","os_icon":"http:\\/\\/cdn.mandrill.com\\/img\\/email-client-icons\\/macosx.png","mobile":false},"url":"http:\\/\\/mandrill.com","ts":1535736330},{"event":"spam","msg":{"ts":1365109999,"subject":"This an example webhook message","email":"example.webhook@mandrillapp.com","sender":"example.sender@mandrillapp.com","tags":["webhook-example"],"opens":[{"ts":1365111111}],"clicks":[{"ts":1365111111,"url":"http:\\/\\/mandrill.com"}],"state":"sent","metadata":{"user_id":111},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa4","_version":"exampleaaaaaaaaaaaaaaa"},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa4","ts":1535736330},{"event":"unsub","msg":{"ts":1365109999,"subject":"This an example webhook message","email":"example.webhook@mandrillapp.com","sender":"example.sender@mandrillapp.com","tags":["webhook-example"],"opens":[{"ts":1365111111}],"clicks":[{"ts":1365111111,"url":"http:\\/\\/mandrill.com"}],"state":"sent","metadata":{"user_id":111},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa5","_version":"exampleaaaaaaaaaaaaaaa"},"_id":"exampleaaaaaaaaaaaaaaaaaaaaaaaaa5","ts":1535736330"

AND HERE IS an actual receipt of ONE real Open event. You can see "metadata" very close to the end:

   '[{"event":"open","ts":1536132312,"user_agent":"Mozilla\\/5.0 (Macintosh; Intel Mac OS X 10_10_3) AppleWebKit\\/537.36 (KHTML, like Gecko) Chrome\\/68.0.3440.84 Safari\\/537.36","user_agent_parsed":{"type":"Browser","ua_family":"Chrome","ua_name":"Chrome 68.0.3440.84","ua_version":"68.0.3440.84", "ua_url":"http:\\/\\/www.google.com\\/chrome","ua_company":"Google Inc.","ua_company_url":"http:\\/\\/www.google.com\\/","ua_icon":"http:\\/\\/cdn.mandrill.com\\/img\\/email-client-icons\\/chrome.png","os_family":"OS X","os_name":"OS X","os_url":"http:\\/\\/www.apple.com\\/osx\\/","os_company":"Apple Computer, Inc.","os_company_url":"http:\\/\\/www.apple.com\\/","os_icon":"http:\\/\\/cdn.mandrill.com\\/img\\/email-client-icons\\/macosx.png","mobile":false},"ip":"70.126.141.227","location":{"country_short":"US","country":"United States","region":"Florida","city":"Tampa","latitude":27.9475193024,"longitude":-82.4584274292,"postal_code":"33601","timezone":"-04:00"},"_id":"443082dac81649978b07f944d251c451","msg":{"ts":1536132236,"_id":"443082dac81649978b07f944d251c451","state":"sent","subject":"My Office Blast 3:18am  275 ppl","email":"demo900057919@xyz.com","tags":["MyCustomTag"],"opens":[{"ts":1536132312,"ip":"70.126.141.227","location":"Florida, US","ua":"OS X\\/OS X\\/Chrome\\/Chrome 68.0.3440.84"}],"clicks":[],"smtp_events":[{"ts":1536132273,"type":"sent","diag":"250 Requested mail action okay, completed: id=0MKsCM-1fxSAz1nwu-0000FE","source_ip":"198.2.180.28","destination_ip":"74.208.5.21","size":2797}],"subaccount":"demo","resends":[],"_version":"DgI4TT6W9EDqtvovn3A3IA","metadata":[{"email_id":"834418"}],"sender":"Demo_TeamLeader@systems.com","template":null}}]' }

hpl123

I would love to have this functionality built into Aware, support or some Java savy developer, can you build a plugin (or just sample bsv with instructions) with the entire solution i.e the send + feedback stuff (read, opened, clicked, unsub)? I would pay a small fee for this and surely others as well.

I do however love having everything in Aware i.e not be dependant on external services, node etc etc but I guess it would be difficult to have all of this natively?

Jaymer

The SQL is really complex - simple though, but a PITA to figure out.
Here's one example that builds MERGE_VARS JSON string.

ALTER PROCEDURE [dbo].[SP_LeadsBatchToJSON_Mandrill_B] 
	 @UserID int
AS
BEGIN
SET NOCOUNT ON;

declare @sql nvarchar(1000);
DELETE JSON_Mandrill_b;
DELETE JSON_REPLY2 where UserID = @UserID;

BEGIN TRANSACTION;
SET @sql = 'INSERT INTO [JSON_Mandrill_b] SELECT Lead.ID, EmailAddress as ''rcpt'', ''FNAME'', FirstName as ''content''
FROM            USERLISTLEADS INNER JOIN
                         LEAD ON USERLISTLEADS.ps_Lead_RID = LEAD.ID
WHERE        (USERLISTLEADS.ps_User_RID = ' + STR(@UserID) + ')'

exec sp_executesql @sql;

INSERT INTO JSON_REPLY2 (UserID, TheJSON)
SELECT @UserID,( SELECT B.rcpt, 
  (
    SELECT 'FNAME' as 'name', content as 'content'
	FROM Lead L
	WHERE B.theLeadID = L.ID
	FOR JSON PATH
  ) vars
  from [JSON_Mandrill_b] B 
  FOR JSON PATH, ROOT ('merge_vars')    );

Update JSON_REPLY2
SET TheJSON =  TRIM('{}' from TheJSON) 
WHERE UserID = @UserID

COMMIT
END

I have no idea how I would have done this if I was using MySQL. (unless they have some JSON output support)
And if my only hope was calling an exposed Aware REST service to make JSON for me, I have no idea how that would have worked.

tford

hpl123 wrote
I would love to have this functionality built into Aware, support or some Java savy developer, can you build a plugin (or just sample bsv with instructions) with the entire solution i.e the send + feedback stuff (read, opened, clicked, unsub)? I would pay a small fee for this and surely others as well.

I would be willing to pitch into this as well. If AwareIM could listen for Webhooks and process the JSON they send, the solution could have broader application than receiving webhooks related to email transactions.

hpl123 wrote
I do however love having everything in Aware i.e not be dependant on external services, node etc etc but I guess it would be difficult to have all of this natively?

Henrik - I'm also big fan of keeping everything in AwareIM. Short of having AwareIM build in some new webhook receiving functionality, I'm planning to further explore Integromat https://www.integromat.com/en/ and Zapier https://zapier.com/. Preliminary tests with both Interomat and Zapier show me that they can receive webhooks and parse the JSON. With me prelim look, it appears both can send the parsed read receipts, opens, clicks, etc to AwareIM, but in two different ways:

1) Integromat can send http transactions, so I think it MIGHT be able to pass the parsed data to a service exposed by AwareIM service via URL parameters.

2) Zapier can send the parsed data to MySql. AwareIM should be able to monitor a MySql table as an external table and process.

ACDC

Jaymer, thanks for the detailed response , its certainly an interesting concept but way to complex for me, MSSQL is also slightly foreign to me

there's no way in hell you can process it through Aware login.
When you setup a webhook, it has to validate. Its clear what they do and expect. I'm not even sure in Aware how you'd force a Response 200, but they ain't gonna be sending stuff to an aware logon in our specific format.

A few years ago I had callbacks working with AwareIM and the JangoEmail Transactional email interface using their smtp relay server with callbacks. It worked very well

This is a copy of the tomcat log responding to a JangoEmail callback, as you can see the response is 200 . which I understand Mandrill requires

9.0.149.203 - - [05/Sep/2018:19:09:58 +0200] "GET /MyApp/logonOp.awsp?domain=CRM&userName=Jango&password=12345&ProcessName=JangoCallBack&ObjectName=JangoCallBack&EventType=Open HTTP/1.1" 200 -

This is the url configured on the JangoMail callback url setup

"https://111.111.111.1/MyApp/logonOp.awsp?domain=CRM&userName=Jango&password=1234&ProcessName=JangoCallBack&ObjectName=JangoCallBack"

If one can find a way to embed(prepend) the above url on the Mandrill webhook , with them adding "&EventType=Open" or "&whatever" it could work

In my mind this functionality is in AwareIM and is the simplest and shortest route to get call back functionality working

Jaymer

ACDC wrote
This is the url configured on the JangoMail callback url setup

"https://111.111.111.1/MyApp/logonOp.awsp?domain=CRM&userName=Jango&password=1234&ProcessName=JangoCallBack&ObjectName=JangoCallBack"

If one can find a way to embed(prepend) the above url on the Mandrill webhook , with them adding "&EventType=Open" or "&whatever" it could work

cool
I think it would be easy to do that...
Node.js is VERY simple to install/implement.
I'm sure it can grab the JSON from Mandrill and send it to Aware.
Dealing with or contacting Mandrill is a non-starter, IMHO.
So, if its just taking their payload and as you say, prepend something to it and resend to another destination, its doable.
I'll help you with this from my side and my Node.js server.
If you already have Mandrill, you could add my server as a Webhook, and I'll resend the payload to you, OR
if you're not that far yet, I'll send some msgs out my system to make callbacks come in to send you for testing/proof of concept.

Also,
i have a similar callback on Twilio.
GET
https://www.xyzzy.com/TwilioWebHook/rest/twilio/softserv
but its handled by a Java Web Application (.war)
Never thought about doing what you did with jango.

Jaymer

ACDC
Actually, its easier than I thought.
As shown above, you don't need all the crap from Mandrill.
Once I parse out the single ID field, then its a simple GET function to send the Logon string PLUS your variable to logonOp.awsp

hpl123

tford wrote
hpl123 wrote
I would love to have this functionality built into Aware, support or some Java savy developer, can you build a plugin (or just sample bsv with instructions) with the entire solution i.e the send + feedback stuff (read, opened, clicked, unsub)? I would pay a small fee for this and surely others as well.

I would be willing to pitch into this as well. If AwareIM could listen for Webhooks and process the JSON they send, the solution could have broader application than receiving webhooks related to email transactions.

hpl123 wrote
I do however love having everything in Aware i.e not be dependant on external services, node etc etc but I guess it would be difficult to have all of this natively?

Henrik - I'm also big fan of keeping everything in AwareIM. Short of having AwareIM build in some new webhook receiving functionality, I'm planning to further explore Integromat https://www.integromat.com/en/ and Zapier [/url]https://zapier.com/[/url]. Preliminary tests with both Interomat and Zapier show me that they can receive webhooks and parse the JSON. With me prelim look, it appears both can send the parsed read receipts, opens, clicks, etc to AwareIM, but in two different ways:

1) Integromat can send http transactions, so I think it MIGHT be able to pass the parsed data to a service exposed by AwareIM service via URL parameters.

2) Zapier can send the parsed data to MySql. AwareIM should be able to monitor a MySql table as an external table and process.

A better plugin then might be to create a plugin for one of these services (or IFTTT)? If that would solve the email "problem" in some way + then we would also have a bunch of other integrations available. I would chip in for that.

ACDC

Jaymer wrote
ACDC
Actually, its easier than I thought.
As shown above, you don't need all the crap from Mandrill.
Once I parse out the single ID field, then its a simple GET function to send the Logon string PLUS your variable to logonOp.awsp

That's great, I don't have a paid MailChimp account and you need one to sign up with Mandrill. So I am in the dark as to the Mandrill Call back setup.
The concept that I have in my mind right now is:

Send an Email via the Mandrill SMPT relay server and insert the OutgoingEmail.ID into the email header using the AIM Eheader function (It appears Mandrill have a host of options when using their SMTP Custom Header Reference- "https://mandrill.zendesk.com/hc/en-us/articles/205582117-How-to-Use-SMTP-Headers-to-Customize-Your-Messages#custom-header-reference")
Receive the event call back via the Node.js as you suggested, or first prize for me, receive the Mandrill call back directly into AIM

When I get some time I will get onto it.

Jaymer

ACDC wrote
Jaymer wrote
The concept that I have in my mind right now is:

Send an Email via the Mandrill SMPT relay server and insert the OutgoingEmail.ID into the email header using the AIM Eheader function (It appears Mandrill have a host of options when using their SMTP Custom Header Reference- "https://mandrill.zendesk.com/hc/en-us/articles/205582117-How-to-Use-SMTP-Headers-to-Customize-Your-Messages#custom-header-reference")

Tom Ford is already doing this part. Perhaps he can add his experience.

Jaymer

ACDC wrote
Jaymer wrote
The concept that I have in my mind right now is:

Receive the event call back via the Node.js as you suggested, or first prize for me, receive the Mandrill call back directly into AIM

RE: receive the Mandrill Webhook directly into AIM

TL;DR This isn't even possible now

ok, do this simple thing.
copy this code below to clipboard.

[{"event":"open","ts":1536132312,"user_agent":"Mozilla\\/5.0 (Macintosh; Intel Mac OS X 10_10_3) AppleWebKit\\/537.36 (KHTML, like Gecko) Chrome\\/68.0.3440.84 Safari\\/537.36","user_agent_parsed":{"type":"Browser","ua_family":"Chrome","ua_name":"Chrome 68.0.3440.84","ua_version":"68.0.3440.84", "ua_url":"http:\\/\\/www.google.com\\/chrome","ua_company":"Google Inc.","ua_company_url":"http:\\/\\/www.google.com\\/","ua_icon":"http:\\/\\/cdn.mandrill.com\\/img\\/email-client-icons\\/chrome.png","os_family":"OS X","os_name":"OS X","os_url":"http:\\/\\/www.apple.com\\/osx\\/","os_company":"Apple Computer, Inc.","os_company_url":"http:\\/\\/www.apple.com\\/","os_icon":"http:\\/\\/cdn.mandrill.com\\/img\\/email-client-icons\\/macosx.png","mobile":false},"ip":"70.126.141.227","location":{"country_short":"US","country":"United States","region":"Florida","city":"Tampa","latitude":27.9475193024,"longitude":-82.4584274292,"postal_code":"33601","timezone":"-04:00"},"_id":"443082dac81649978b07f944d251c451","msg":{"ts":1536132236,"_id":"443082dac81649978b07f944d251c451","state":"sent","subject":"My Office Blast 3:18am  275 ppl","email":"demo900057919@xyz.com","tags":["MyCustomTag"],"opens":[{"ts":1536132312,"ip":"70.126.141.227","location":"Florida, US","ua":"OS X\\/OS X\\/Chrome\\/Chrome 68.0.3440.84"}],"clicks":[],"smtp_events":[{"ts":1536132273,"type":"sent","diag":"250 Requested mail action okay, completed: id=0MKsCM-1fxSAz1nwu-0000FE","source_ip":"198.2.180.28","destination_ip":"74.208.5.21","size":2797}],"subaccount":"demo","resends":[],"_version":"DgI4TT6W9EDqtvovn3A3IA","metadata":[{"email_id":"834418"}],"sender":"Demo_TeamLeader@systems.com","template":null}}]

go here: https://codebeautify.org/jsonviewer
paste code into left box
click Beautify button in center panel
The "prettied" code shows up on the right.

Thats a real view of the JSON structure Aware would have to parse.
Based on what I've seen getting JSON back from REST replies, it would make sense that IF Vlad offered some way to parse a reply, he'd follow the same routine observed in REST replies, which means:
You'd have to create a BO structure in Aware to accommodate this data.
1) BO "TheEvent" (top level name doesn't matter). Fields like event, ts, ip (and others at that 2nd level)
2) The data in location has to go in another BO. A field in BO_TheEvent is named "location" and is an OS (owned single) of BO_location. All those fields "under" location are now fields in BO_location.
Similarly, any other 2nd level field that is an array of other values becomes another BO. BO_user_agent_parsed and BO_msg
(bear with me, i know this is complicated.)
3) all this repeats with BO_msg. Its top-level fields are: state, subject, email, but BOs must be created to hold the child arrays: tags, opens, clicks, etc.

NOTE: If you do NOT want or need any data from user_agent_parsed, for example, (step 2 above), then Aware gracefully doesn't complain and will ignore that entire element path if you don't have that field defined in BO_TheEvent. So its easy to pick & choose which data you want to preserve, BUT even for just 1 field, you have to create the BO (example. If you only needed Location.timezone, you'd still need BO_location and it would have only 1 field in it).

So now you end up having a BO structure that looks very similar to the Beautify layout:
BO_TheEvent (fields event, ts, user_agent, ip)
L__ BO_user_agent_parsed (fields type, ua_family, ua_name)
L__ BO_location (fields country, region, state)
L__ BO_msg (fields ts, state, subject)
...L___ BO_opens
...L___ BO_smtp_events
...L___ BO_meta-data
......L___ BO_email_id *

Once an Event would be Parsed/Received/???, and IF it follows the same JSON REST Reply routine, then it would create rows and populate all these fields automatically. One BO_TheEvent structure for each Webhook sent by whomever.
All of this structure is in place just to handle 1 Webhook, BUT remember they are also individual AWARE BOs. So, I would add a field in BO_email_id for ProcessedYN, or Date_Processed (these will be undefined or a RULE can set them). Then have a Scheduled Event scan the BO_email_id table for any un-Processed records. Finding the OutgoingEmail record for that ID now links you to all existing Aware data. You can now post opened/clicked or tracking data anywhere you want. You can traverse all the way back up to TheEvent to get the type ( BO_email_id.ob_BO_meta_data.ob_BO_msg.ob_BO_TheEvent.event ).

(* NOTE: I have a problem with this description above. From Beautify, note the difference between locations and opens. opens allows for multiple arrays. Its is an Object of Arrays. Locations has only 1 array.
The names of these elements come from a API Spec. Aware matches up the element name with a real field (attribute) in the BO.
Well, we come to a problem if we are trying to design a Structure to handle Mandrill Webhooks. Because meta-data is just like opens, except the NAME of the element varies - its not in a "spec" anywhere. It just means that if you add another piece of metadata, you have to add another BO just to receive the data for that 1 field. OK, I digress, this may not be a big deal, cause you'd have to go back into code anyway to handle that data anyway. )

Jaymer

ACDC wrote
Jaymer wrote
The concept that I have in my mind right now is:

Receive the event call back via the Node.js as you suggested, or first prize for me, receive the Mandrill call back directly into AIM

RE: Receive the Mandrill Webhook via the Node.js as you suggested
Extract data in Node.JS and send a GET to Aware logonOP

I may actually try this if I can't swing others to do it all in Node.js.
WIll come back to this if needed.

BTW, ACDC, WTF is logonOp.awsp ?
Seen this here: Build 2240>
but where is the documentation for this?

Jaymer

ACDC wrote
Jaymer wrote
The concept that I have in my mind right now is:

Receive the event call back via the Node.js as you suggested, or first prize for me, receive the Mandrill call back directly into AIM

RE: Receive the Mandrill Webhook via the Node.js as you suggested
Process it ALL in Node.JS and do the SQL update there.

TL;DR This IS possible NOW, and its easier than you think
Bruce's demo on calling Jasper reports and Mailing the PDF was my first exposure to Node.js.
It hasn't been exactly easy, but its because of the JS part, not "Node" per se.

Its easy to get a skeleton "listener" file and attach to your db. No worries.
My issues were from parsing Objects and I'm past that now. (thanks bruce)

Installing Node & testing a connection to your SQL database should be under 1 hour.
And now that I know what I know, under an hour to get a Node server script running to process the Webhooks.

Here's my actual code that examines the Webhook data and does the SQL update.

if ( 'email_id' in  clickEmailNum ) {
		the_EmailID = clickEmailNum[0]["email_id"];
			var request = new sql.Request();
			request.query('UPDATE OutgoingEmail SET Clicked=1 WHERE ID=' + the_EmailID, 
				function (err, recordset) {   if (err) console.log('Error Updating Email.Clicked: ',err) 	});
}

Sure, there's no rules firing based on this.
On my Unsubscribe event, I'm going to also have to update a variable in the ContactMaster.
One ALTERNATIVE, would be to NOT touch the LIVE database data, and just post these transactions in a to-be-processed file and then do them in Aware from the Scheduler. That would be fine too, and provide an audit trail. But I don't see any need to "bother" Aware with thousands of Logins (using LogonOp.aw) just to flip a byte or two when I'm USUALLY just 1 SQL update away from doing the same thing.