Custom Reporting Dashboards

If you’ve used the portal, you would know that there is a dashboard for reports, that can be filtered. However, you may not be aware of the flexibility and power of the MessageMedia Reporting API. If you are interested in really fine-grained reports or customising your own dashboard, you can use the Reporting API. In addition, you can use the reporting endpoints for all messages sent, regardless of if you are using the portal, the SOAP API or the REST API. The level of detail available in the Reporting API makes it ideal for consumption via Business Intelligence Software, like Tableau or Microsoft Power BI.

In this developer guide, we’ll create a very simple custom reporting interface using Google Sheets, and drill into the details. Over successive instalments, we’ll build out the complexity and detail available in this reporting interface.

In this series, we’ll cover the following topics:

Part 1: Understanding reporting concepts, detailing metadata and getting the detail of received messages emailed to you.
Part 2: Parsing detailed results into a custom interface for both sent and received messages.
Part 3: Fetching summary data, and our new shortURL reports, cleaning up the interface and translating it into graphs.

Getting Started with Reporting

Before we get stuck into the endpoint, let’s go over the main differences between reports surfaced in the portal and those available to you via the APIs.

Metadata:

The first piece of detail you can get from the API, that you can’t get directly from the portal, is a full list of metadata keys used within a particular time frame. The detailed reports screen on the portal can be filtered by specific metadata keys and values, but what if you’ve set up an integration where you have dynamic keys and values? Using the reporting API also enables you to do useful analyses like comparisons of each of these metadata keys and values.

To show a full list of the keys used in a specific time, use the getMetaDataKeys endpoint.

Detailed and finely tuned async reports:

In the context of the MessageMedia Reporting async reports refer to reports that have been emailed to the recipient. With most of the endpoints (especially those that produce large datasets), you can use the async endpoint to have it emailed to a list of recipients, or you can return the data in the body of the response, with the synchronous endpoint.

Detail:

With the portal, the reporting is focussed on usability and having everything accessible. With the reporting API you can filter by any number of the attributes available on the report. This means that you can finely tune your report lists and generate your own dashboard, surfacing the data in the way that you want, and that makes sense for you. You can also obtain detail about what reports people have generated, using the getAsyncReports endpoint, and then get the data of reports that have already been generated and emailed to people using the getAsyncReportbyID endpoint.

ShortUrls:

Finally, with the Reporting API, you have access to detailed reports about your ShortUrls. Short URLs is a feature available to messaging API users whereby it automatically and seamlessly shortens any URL to just 22 characters. Every shortened URL is unique to every recipient. The reporting API has endpoints specific to this feature, allowing users to obtain details regarding the number of click-throughs on each URL.

Demonstration

As an example, I have created a Google Sheets Dashboard that implements a few of these endpoints. The simplest demonstration of how to do this in a sheet is the “Reporting Metadata Keys by Message Type” endpoint.

I’ve created a google sheet that looks like this:

Up the top, I’ve got the Inputs and the Outputs below is where we’ll print out the results of our API call. For the messageType option, I have put data validation on the input field, with a drop down box that gives us two options: ‘received_messages’ and ‘sent_mssages’. To the side I have created the box using the “Insert> Create drawing” menu option and labelled it with the CTA “Fetch”.

Adding the script:

Go to Tools>Script Editor. Now in the script editor you can create functions for each API call you want to make. In this example, we’ll create a function like this:

function getMessagesMetadata() {

}

Now that you’ve got this function we need to do a few things. First, lets set up everything we need for authenticating and creating the right headers:

//set up the headers
var url = "https://api.messagemedia.com/v1/reporting/";

var username = "YOUR_API_KEY";
var password = "YOUR_API_SECRET";
var plaintext = username + ':' + password;
var encoded = Utilities.base64Encode(plaintext);

var header = "Basic " + encoded;
var headers = {
'Authorization': header,
'Content-Type': 'application/json'
};

Next we can get the input data from the spreadsheet and assign it to variables that can be used to form our URL:

var ss = SpreadsheetApp.getActiveSpreadsheet();

var type = ss.getRange("B5").getValue();
var startdate = ss.getRange("B6").getValue();
var enddate = ss.getRange("B7").getValue();
var timezone = ss.getRange("B4").getValue();

var url2 = url + type + "/metadata/keys" + "?start_date=" + startdate + "&end_date=" + enddate + "&timezone=" + timezone;

Now we can set up our API call with the URL we made and the headers we made earlier, and print the results out to the correct parts of our sheet:

// Make request to API with payload after this point.
var options =
{
"method" : "get",
"headers": headers,
};
var result = JSON.parse(UrlFetchApp.fetch(url2, options).getContentText());

ss.getRange("B12").setValue(result.data);
ss.getRange("B13").setValue(result.properties['end_date']);
ss.getRange("B14").setValue(result.properties['start_date']);
ss.getRange("B15").setValue(result.properties.accounts);

The final thing to do is assign the function to the button we created earlier. Go back to the spreadsheet and hover over this button. You will see three dots, which are the menu. Click on this menu and select ‘assign script’.  In the box that appears, type the name of your function ”getMessagesMetadata” without the brackets. After you click enter, you can now click the button and test it out. If everything has worked, you should see your outputs appear in the boxes.

Submitting Async Requests

The second type of call we’re going to make in this guide is submitting requests for an async report that will be emailed to you.

As you can see, in this example, there is significantly more detail that you can filter on. I’ve also added checkboxes, so that you can determine which details you want to pull in your code. There is a lot of complexity in these, so if you want to copy this example, you can do so here

Now, in the script editor, let’s do the function for this. We’ll start with:

function submitRecievedMessagesDetail() {

}

Now we have the big job of pull in all the data from the active spreadsheet and assigning it to variables:

var ss = SpreadsheetApp.getActiveSpreadsheet();

var usesPeriod = false;
var payloadString = {};

if(ss.getRange("B4").getValue() == true){

var end_date_full = ss.getRange("C4").getValue();
var end_date = "2019-03-10T13:30:00";
payloadString["end_date"] = end_date;

var start_date_full = ss.getRange("C5").getValue();
var start_date = "2019-01-01T13:30:00";
payloadString["start_date"] = start_date;

}

if(ss.getRange("B6").getValue() == true){
var period = ss.getRange("C6").getValue();
usesPeriod = true;
payloadString.period = period;
}

if(ss.getRange("B7").getValue() == true){
var sort_by = ss.getRange("C7").getValue();
payloadString.sort_by = sort_by;
}

if(ss.getRange("B8").getValue() == true){
var sort_direction = ss.getRange("C8").getValue();
payloadString.sort_direction = sort_direction;
}

if(ss.getRange("B9").getValue() == true){
var timezone = ss.getRange("C9").getValue();
payloadString.timezone = timezone;
}

if(ss.getRange("B10").getValue() == true){
var accounts = [ ss.getRange("C10").getValue()];
payloadString.accounts = accounts;
}

if(ss.getRange("B11").getValue() == true){
var destination_address_country = ss.getRange("C11").getValue();
payloadString.destination_address_country = destination_address_country;
}

if(ss.getRange("B12").getValue() == true){
var destination_address = ss.getRange("C12").getValue();
payloadString.destination_address = destination_address;
}

if(ss.getRange("B13").getValue() == true){
var message_format = ss.getRange("C13").getValue();
payloadString.message_format = message_format;
}

if(ss.getRange("B14").getValue() == true){
var metadata_key = ss.getRange("C14").getValue();
payloadString.metadata_key = metadata_key;
}

if(ss.getRange("B15").getValue() == true){
var metadata_value = ss.getRange("C15").getValue();
payloadString.metadata_value = metadata_value;
}

if(ss.getRange("B16").getValue() == true){
var source_address_country = ss.getRange("C16").getValue();
payloadString.source_address_country = source_address_country;
}

if(ss.getRange("B17").getValue() == true){
var source_address = ss.getRange("C17").getValue();
payloadString.source_address = source_address;
}

if(ss.getRange("B18").getValue() == true){
var action = ss.getRange("C18").getValue();
payloadString.action = action;
}

var delivery_type = "EMAIL";
var delivery_addresses = ss.getRange("C21").getValue();
//make sure all the emails in this field are comma seperated, without brackets or quotes, eg - test@test.com, test@test.com
var delivery_format = "CSV";
var delivery_options = [{"delivery_type": delivery_type, "delivery_addresses": [ delivery_addresses ], "delivery_format": delivery_format}]

payloadString.delivery_options = delivery_options;
var payload = JSON.stringify(payloadString);

What we’ve done here is optionally add all these to a payload string, if it exists, and then convert that JSON object to a string. Now we can use all of this to set up the headers, and make the call, as before, with the exception that the method type is a POST, rather than a GET call:

//set up the headers
var url = "https://api.messagemedia.com/v1/reporting/received_messages/detail/async";
var username = "YOUR_API_KEY";
var password = "YOUR_API_SECRET";
var plaintext = username + ':' + password;
var encoded = Utilities.base64Encode(plaintext);
var header = "Basic " + encoded;
var headers = {
'Authorization': header,
'Content-Type': 'application/json'
};
// Make request to API with payload after this point.
var options =
{
"method" : "post",
"headers": headers,
"payload": payload,
};
var result = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());

What we’ve done here is asked for the report to be emailed, so our response is just the report ID. For simplicity, we’ve printed this out as well:

ss.getRange("D24").setValue(result);

Now you can make a button on the sheet and assign the method to it before. When you click on this button it will send off a request to the MessageMedia to generate the reports, which will then get emailed to all the email addresses you submitted.

That’s it!

In the next instalments of this series, we’ll go even deeper in the reporting API to get detailed reporting data, manipulate it to be shown in a visual way. We’ll also explore summary views and the shortURL reporting features.