Having lots of information about your customers can be a great thing in the email marketing world – you can segment and target for better results, recognise patterns and generally be smarter with the way you approach your marketing. But, on the downside, it can make the structure of your data very complex, particularly when faced with limited space to store this data, e.g. in an email platform where you may have a restricted number of variables available to you. One solution to this problem is to store your data using JSON (JavaScript Object Notation). JSON is quite similar to XML in that they are both designed to encode data into a format that is both human-readable and machine-readable, often to transmit data between a server and a web application among other uses. Where XML uses tags, JSON uses labels and is arguably the simpler alternative.
Now, at first glance a JSON encoded data set can look a bit daunting the more variables you have, but as with any language all you need to know are the syntax characters and rules, and once you’ve got those down it should be a breeze. JSON can cope with numerical, string (data enclosed by quotes), null and Boolean values associated with a fieldname label (key). For example, here would be the layout for a customer’s personal information using all these different types:
{
"FirstName": "John",
"LastName": "Doe",
"Age": 35,
"Income": 35150.65,
"Pets": null,
"LikesOneDirection": false
}
This single structure is known as an object and all objects are recognised by the contents enclosed within the {} brackets. One other feature JSON allows for is an array, which is created by using the [] brackets. An array would be implemented when there is more than one entry for the same type e.g. a home, mobile and office telephone number. In that example you would include the following field name and value:
“TelephoneNo”: [“01252555555″,”07999111222″,”01252444444”]
Of course that isn’t particularly helpful if you don’t know what phone number type each number refers to, so you may find yourself using objects within the array:
"TelephoneNo": [
{
"phonetype": "home",
"phonenumber": "01252555555"
},
{
"phonetype": "mobile",
"phonenumber": "07999111222"
},
{
"phonetype": "office",
"phonenumber": "01252444444"
}
]
Here’s an example incorporating the use of many different objects, utilising data from the booking of a hotel room, encoded into JSON:
{
"CustomerDetails": {
"Title": "Mr",
"Firstname": "John",
"Lastname": "Doe",
"Address": {
"AddressLine1": "11 Street Name",
"AddressLine2": "Random Neighbourhood",
"AddressLine3": null,
"Town": "Random Town",
"County": "Random County",
"Postcode": "LLNN NLL"
},
"TelephoneNo": [
{
"phonetype": "home",
"phonenumber": "01252555555"
},
{
"phonetype": "mobile",
"phonenumber": "07999111222"
},
{
"phonetype": "office",
"phonenumber": "01252444444"
}
]
},
"RoomDetails": {
"RoomType": "Twin",
"NoGuests": 2,
"GuestNames": [
"John Doe",
"Jane Doe"
]
},
"PaymentDetails": {
"CardNumber": "************1234",
"ExpiryDate": "01/16",
"SecurityCode": 675
}
}
Our JSON structure is broken down as follows (creating distinct segments of information all held within the same structure):
Object 1 – Customer Details
- Object 1a – Address Details
- Array 1a – Telephone Numbers
- Object 1b – Phone Type Home
- Object 1c – Phone Type Mobile
- Object 1d – Phone Type Office
Object 2 – Room Details
- Array 2a – Guest Names
Object 3 – Payment Details
To create a structure capable of being imported into an email platform, if you are selecting from a pre-existing database you could use code similar to this:
update HotelBooking_Customer
set JSON_Field = concat("{""CustomerDetails"":{""Title"":""",Title,""",""Firstname"":""",Firstname,""",""Lastname"":""",Lastname,"""}}");
Once inside a compatible email platform you can see how the structure is broken down with the key names (labels) being referenced when pulling out your required values:
So, given an email platform that can read and select variables from a JSON structure, the earlier complete example would store 19 potential fields into just 1 freeing up a lot more storage space for more intensive projects.
Finally, a great tool for validating JSON structures can be found at http://jsonlint.com.