VIP Customer Service
888-GO-JANGO

Deep In The Weeds: Custom Data Integration with JangoMail

Deep In The Weeds: Custom Data Integration with JangoMail

One of the key benefits to using JangoMail is our advanced integration capabilities.  We offer a robust API which can be used to send email, report on past campaigns, manage lists, and much more!  We also offer a Web Database connection feature that allows us to pull from your database, and to even push data back based on specified events.

Typically, if you want to integrate JangoMail with your own Database, you can do so by selecting what type of database you want to connect to.  We offer the following combinations out of the box:

  • ODBC / Classic ASP
  • MS SQL / Classic ASP
  • Access DB / Classic ASP
  • MS SQL / ASP.net
  • MySQL / PHP

When you select the database you want to integrate with, we provide you with a helper file to download and save to your publicly accessible web server, and then configure the parameters of the connection.  When you attempt to connect to your database inside JangoMail, we connect to this helper file, using the parameters you’ve specified, and the file handles the heavy lifting.

But, did you know we also support any custom databases or other integrations using this feature as well?

This is possible, because at its core, all the helper file does is accept a set of parameters and return a pre-defined response.  All the logic that happens in the middle is up to you!

Let’s take a deep dive into how this feature works, by extending the MS SQL / ASP.net configuration.

When you specify the MS SQL / ASP.net web server & database type, we provide you with a w_5.aspx file, which handles the connection details.  However, this is actually a custom configuration that you can extend.  This is the configuration screen that you’re presented with:

screen-shot-2016-10-21-at-11-36-23-am

Under Connection Name, enter the name you want to set up this Connection as – this is just for your own use, so it can be anything you like.

Next, you need to specify the script location.  This is the path to the file (or any other URL that will accept the necessary parameters and provide the appropriate response) which will handle the request.

Finally, if you plan to use our Web DB Event Management feature, you’ll need to specify the SQL Variable, and it MUST match one of the Variable Names you specify below.

This is all you need to get started.

Now, let’s look at what’s required using the helper file.

When you make the connection from JangoMail, there is only one parameter which is required, and will always be present in the request.  That is action.  Action can be one of the following values:

massmail

This occurs when you connect to a Web DB Profile inside of the Lists section in our UI, or are otherwise sending a campaign that pulls data from this Profile.

test

This occurs when you hit the Test button in the UI while editing the Web DB Profile.

unsubscribe

This occurs when an unsubscribe occurs, if you have settings configured in Web DB Synchronization for this action.

bounce

This occurs when a bounce occurs, if you have settings configured in Web DB Synchronization for this action.

change

This occurs when an address change occurs, if you have settings configured in Web DB Synchronization for this action.

view

This occurs when an view event occurs, if you have settings configured in Web DB Synchronization for this action.

click

This occurs when an click occurs, if you have settings configured in Web DB Synchronization for this action.

sent

This occurs when a campaign has completed sending, if you have settings configured in Web DB Synchronization for this action.

forward

This occurs when an forward to friend occurs, if you have settings configured in Web DB Synchronization for this action.

The action parameter is important because it dictates what JangoMail is trying to do with the helper file – and what is expected in response.  Here is a list of the expected responses based on the action parameter:

massmail

This is by far the most complex response, as it is designed to pass the retrieved data back to JangoMail.  How you obtain the data is up to you – you can extend this custom script to access Postgres, Redis, MongoDB, flat files, or anything else you can get data out of – so long as the format coming back to us matches our expectations.  Let’s take a look at this custom format by example.  Let’s say you’re pulling from a CSV with the following data:

First, list all the field names, separated by commas:

Now, append ___ASDF—BREAK:

We’ve established our header row.  Let’s move on to the data rows.  Each column needs to be separated by the following delimiter:

And each row needs to be separated by the following delimiter:

This is how your first row would look:

You would then loop through all your rows, continuing to build your response string with this same format.  Finally, when you’re done, cap it off with:

So, your final response string would look like this:

This is the full response for massmail.

test

If successful:

If unsuccessful, any other value can be returned.

unsubscribe

If successful:

If unsuccessful:

bounce

If successful:

If unsuccessful:

change

If successful:

If unsuccessful:

view

If successful:

If unsuccessful:

click

If successful:

If unsuccessful:

sent

If successful:

If unsuccessful:

forward

If successful:

If unsuccessful:

What does the raw request look like?

We’ve talked about what the handler needs to do in response to a request from JangoMail, so let’s look at the raw request using the massmail example above:

Request made to Handler

Response from Handler

Using Custom Web Profiles with Web DB Events

JangoMail provides a Website Database Events feature which will push updates to your database when certain actions occur, as listed above.  You must set a Master Profile in Settings in order for JangoMail to know which Profile to use when these events occur.

Let’s say you want to capture Click tracking events in your Custom Web Profile as JSON data.  Assuming you’ve already set up your Custom Profile and the code behind it (whether it’s a file or custom handler), let’s take a look at the UI setup steps.

  1. Navigate to Lists -> Databases.
  2. Click Create New Database.
  3. Click Internet Web Database.
  4. Click Microsoft SQL Server / Microsoft ASP.net.
    At this point, you can choose to click on the w_5.aspx file if you want to download and use our helper, or just use it for reference.  However, so long as you follow the notes above, you can skip this step and use your own handler.
  5. Click Configure This Connection.
  6. For Connection name, enter the name you want to use to identify this connection.
  7. For JangoMail script location, specify the URL where your handler resides.
  8. For SQL Variable, set this to be the parameter name you want to accept in your handler for the data passed back via the Web DB Event.
  9. Insert any Form Variables you want.  The only one that is required is for the variable you specified in step #8.  You can also specify other key/value pairs here if you like.This is what our sample looks like with the data filled in:
    screen-shot-2016-10-21-at-12-23-21-pm
  10. Navigate to Settings -> Integrating JangoMail with Other Systems -> Set Master Profile for Web DB.
  11. Select your newly created Profile from the dropdown list.Navigate to Settings -> Integrating JangoMail with Other Systems -> Web DB Event Management.
  12. Click the Click tab.
  13. Enter the following in the text area:
    screen-shot-2016-10-21-at-12-27-47-pm
  14. Click Save.
  15. Send a new campaign with some links and click tracking enabled, then click on a link.  Within a couple of minutes, you should see your event handled if everything is set up correctly.

How can I test this?

Want to try this in your own environment without building a handler from scratch?  We have created a sample Node app that can act as a handler, logging all request and response data to the console.  You can find it at https://github.com/JangoMailLLC/SampleWebDBHandler.

Here’s some sample output from this application:

For test:

For massmail:

For a Web DB click:

This is a very powerful, but also very complex feature in JangoMail.  If you have any questions about setting this up, please contact our helpful support team here.