Sparkpost Webhooks

[CharlesParker]

I see sparkpost offers webhooks, and they want me to give them a page to send data to in setting up the webhook.

Can anyone help me understand what I would need on the page to capture the data and save it to a table?
I want to at a minimum, get a read receipt or an "open" confirmation on an email sent and save it to a table for later manipulation via AA.

I am not experienced in this area at all. I tried reading the documentation, but as this is totally new ground - I don't get it.
I get that they are sending the information TO the page and the page needs to say hello in some way. I also understand that I will need to do something to save the info as it comes in so I can manipulate it later - so that's what I do know - now HOW to do that is another thing.


Thanks for any advice.
It is snowing here in New Orleans, so it is like I woke up in the wrong area of the country - let's just say I have some time to sit in front of my PC today...
It might as well be raining frozen cat turds in my opinion today.

[Davidk]

Re: Sparkpost Webhooks

This is one of those things that seems complicated... and may even be documented as complicated... but is so straight forward.

You need to create an a5w page with only XBasic tags in it... no meta tags, no html tags... nothing.

<%a5

debug(1)


'Verification Request Variables
'name will drive everything.

dim name as c = if(variable_exists("request.variables.name"),request.variables.name,"name not found")
if name = "name not found" then
	'something hit this page... but not what we wanted...just end
	end 
end if

%>

It's all pure XBasic between the tags. Sparkpost will tell you what request variables they're sending in their Webhook call. You get them as in the example above. If my a5w page was named SparkpostWebhook... then Sparkpost would send out a call like...

http://www.yoursitename.com/SparkpostWebhook.a5w?name=test&otherparam=test2

This means that 2 Request Variables are available for you to process... name... and... otherparm.

Sometimes a site requires that you send back an "Everything is ok" response... so... if all is good... you send back...

Response.Status_Code = 200

The only way to test this stuff is to add in a debug(1) statement, set the request variables manually, and then click the LivePreview tab.

Once things are ok in debug, then you can run the page yourself providing request variables for testing.

[CharlesParker]

Re: Sparkpost Webhooks

well according to sparkpost the test was successful and my server responded. Can't I load the page live and set an alert box with the stuff they sent to see it and confirm I am getting something?

[Davidk]

Re: Sparkpost Webhooks

Think about that process. Sparkpost executes the callback and sends data to your page. Sparkpost does that. How are you going to load that page live to see the stuff Sparkpost sent?

The only reason for a Webhook is to capture something that happened with Sparkpost... something good... or something bad. Usually you want to save that data... or message... or whatever Sparkpost sent. So... capture it. You're running XBasic... you've got the data in the Request Variables... open a connection, and perform a SQL Insert... or Update... to your back end database.

[CharlesParker]

Re: Sparkpost Webhooks

Ok, getting somewhere now but still not exactly sure whats up - hope you can help me piece this together. First of all within sparkpost they allow for testing and when I click the test button there they claim they are sending this data to my webhook URL:

POST /sparkpost_webhook.a5w HTTP/1.1
Host: www.nwcopro.com
Content-Type: application/json
X-MessageSystems-Batch-ID: 77c2b630-d712-11e4-9642-efc2723b99c1
Connection: close

[
  {
    "msys": {
      "message_event": {
        "type": "delivery",
        "campaign_id": "Example Campaign Name",
        "customer_id": "1",
        "delv_method": "esmtp",
        "device_token": "45c19189783f867973f6e6a5cca60061ffe4fa77c547150563a1192fa9847f8a",
        "event_id": "92356927693813856",
        "friendly_from": "[email protected]",
        "ip_address": "127.0.0.1",
        "ip_pool": "Example-Ip-Pool",
        "message_id": "000443ee14578172be22",
        "msg_from": "[email protected]",
        "msg_size": "1337",
        "num_retries": "2",
        "queue_time": "12",
        "rcpt_meta": {
          "customKey": "customValue"
        },
        "rcpt_tags": [
          "male",
          "US"
        ],
        "rcpt_to": "[email protected]",
        "raw_rcpt_to": "[email protected]",
        "rcpt_type": "cc",
        "routing_domain": "example.com",
        "sending_ip": "127.0.0.1",
        "subaccount_id": "101",
        "subject": "Summer deals are here!",
        "sms_coding": "ASCII",
        "sms_dst": "7876712656",
        "sms_dst_npi": "E164",
        "sms_dst_ton": "International",
        "sms_remoteids": [
          "0000",
          "0001",
          "0002",
          "0003",
          "0004"
        ],
        "sms_segments": 5,
        "sms_src": "1234",
        "sms_src_npi": "E164",
        "sms_src_ton": "Unknown",
        "template_id": "templ-1234",
        "template_version": "1",
        "timestamp": "1454442600",
        "transmission_id": "65832150921904138"
      }
    }
  },
  {
    "msys": {
      "track_event": {
        "type": "open",
        "campaign_id": "Example Campaign Name",
        "customer_id": "1",
        "delv_method": "esmtp",
        "event_id": "92356927693813856",
        "friendly_from": "[email protected]",
        "ip_address": "127.0.0.1",
        "ip_pool": "Example-Ip-Pool",
        "message_id": "000443ee14578172be22",
        "msg_from": "[email protected]",
        "msg_size": "1337",
        "num_retries": "2",
        "queue_time": "12",
        "rcpt_meta": {
          "customKey": "customValue"
        },
        "rcpt_tags": [
          "male",
          "US"
        ],
        "rcpt_to": "[email protected]",
        "raw_rcpt_to": "[email protected]",
        "rcpt_type": "cc",
        "routing_domain": "example.com",
        "sending_ip": "127.0.0.1",
        "subaccount_id": "101",
        "subject": "Summer deals are here!",
        "template_id": "templ-1234",
        "template_version": "1",
        "timestamp": "1454442600",
        "transmission_id": "65832150921904138",
        "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.118 Safari/537.36",
        "geo_ip": {
          "country": "US",
          "region": "MD",
          "city": "Columbia",
          "latitude": 39.1749,
          "longitude": -76.8375
        }
      }
    }
  }
]

SO I created an xbasic page on my site with the following xbasic:

Response.Status_Code = 200
dim campaign_id as c = if(variable_exists("request.variables.campaign_id"),request.variables.campaign_id,"Events not found")

	dim cn as sql::Connection
	dim result as l = .f.
	dim args as sql::arguments
		result = cn.open("::Name::Connection1")
			if result then
		args.Set("pub",campaign_id)
			sqlstm = "INSERT INTO alphanwcopro.spost set post = :pub"
		cn.execute(sqlstm,args)
	
end if
cn.close()

It of course does an insert into the post field that says: "Events not found"


Looking at what they are sending I thought that campaign_id would have been a variable. What would I put as the variable name if I wanted to capture just that? Am I understanding that right right?
In sparkpost the Events I am trying to capture are Delivery and Open
How do I determine what the "variables are?


Anyways, some success.

[Davidk]

Re: Sparkpost Webhooks

This... from Sparkpost Webhook doc...

EVERY event created by SparkPost has a "type" property which refers to the Event Type JSON Key.

This means the data sent to the Webhook is JSON... and probably in the Request.Body. I always forget this stuff until I start working with it again. I've Implemented dozens of Webhooks... and somehow they're always a bit different.

In this case the data sent will not be available in Request.Variables.

Assign Request.Body to your pub argument and see what gets written to your table.

Make sure that post field is a big one. It's probably going to be a long string. Looks like there are 2 records... 1 for message_event and 1 for track_event.

Once you have the string you can get back into your Webhook code, paste that string into XBasic and assign it to a character variable in XBasic. Then convert it to an XBasic property variable (a dot variable) and then parse through it's contents.

[CharlesParker]

Re: Sparkpost Webhooks

I was just reading request.body in another thread - saw your comments relating to mandrill and an HTTP post. Yes of course, I made the field a 5000 varchar lol just in case.

[Davidk]

Re: Sparkpost Webhooks

Right... I knew I'd worked through it with Mandrill... over 3 years ago... yikes.

Grabbing the Request.Body should give you the information needed to get at the data Sparkpost is sending.

[CharlesParker]

Re: Sparkpost Webhooks

Yep, that did it.
So the code on the xbasic ONLY page is currently:

<%a5

Response.Status_Code = 200
dim events as c = Request.Body

	dim cn as sql::Connection
	dim result as l = .f.
	dim args as sql::arguments
		result = cn.open("::Name::Connection1")
			if result then
		args.Set("pub",events)
			sqlstm = "INSERT INTO alphanwcopro.spost set post = :pub"
		cn.execute(sqlstm,args)
	
end if
cn.close()
%>

That gets the whole thingy doodle in the field in the database...next I gotta figure out how to comsume this in a meaningful way. Maybe actually send an email and see what I get in there. The test data is EVERYTHING they could potentially send me. I don't want or need it, but perhaps it wouldn't hurt to get it anyway.

[Davidk]

Re: Sparkpost Webhooks

If it's all JSON, use json_parse to convert it to an XBasic dot variable... and take what you need from it.

[CharlesParker]

Re: Sparkpost Webhooks

great more to learn...lol.
Ok, so question - If I am understanding this correct, I use parse to get all of the json data into pointers (e.g. p.campaign_id) and then I can do an insert of all the data that I want right?

[Davidk]

Re: Sparkpost Webhooks

Correct

[CharlesParker]

Re: Sparkpost Webhooks

I have to say - I thought this part wold be easier with just
dim pp as p
dim camp as c
dim events as c = request.body
pp = json_parse(events)
camp = pp.campaign_id

then I would just make camp the :pub arg and do my insert.
I tried to use the returned JSON in the interactive code editor to fart around as well - I am just flailing around here, as I said I thought this would be the easier end of this since we are now dealing with what is essentially a text string.

[GGandhi]

Re: Sparkpost Webhooks

nope.
you are not reading your post well.
the sparkpost event trace sends you json, you need to parse it and see the local identity

<%a5
dim message as c
message = request.body
dim pp as p
pp = json_parse(message)
dim campaign as c
campaign = pp[1].msys.message_event.campaign_id
?campaign
%>

as far as i know, you don't need to send response.status.code=200, your server will do it for you.
you put this code in the a5w page and then run the test in sparkpost and see what the response it shows, down below the execution.
it should come back as
you server responded with
''

''
Example Campaign Name

[CharlesParker]

Re: Sparkpost Webhooks

campaign = pp[1].msys.message_event.campaign_id

I think this was the missing mental link for me. I will work on this in a bit - thanks for the insight into what I was looking for, I looked at so many different examples and yet none of them appeared to me to tell me the heirarchy to use from the parsed json. Your probably right on the 200 response - that was actually left in from some of my first attempts that were failing and I was trying to understand why - thought it needed it at first and just left it there, sloppy but in the end I hope I can post what works and help out the next guy as he works through this and maybe even another service like stripe or something. Again, thanks.