Integrating Drupal With dotMailer Part 3: Reporting On dotMailer Campaigns

In the last post we looked at how to use Drupal to add contacts to dotMailer using the dotMailer API and some simple Drupal form hooks. The dotMailer API can also be used to pull statistical data directly from dotMailer about how certain campaigns have performed. Perhaps the easiest method of doing this is to use the ListSentCampaignsWithActivitySinceDate method, which returns the last campaigns run since a given date, along with some statistics on each using the GetCampaignSummary method. This data is available on the dotMailer system, but allowing the user to see this information on their site dashboard is useful, especially for those users without direct access to the dotMailer admin.

The best way to display this information is in a table, so we use the built-in Drupal table theme functions to quickly generate a table from the results. Calling the dotMailer API on every page load will slow down the time taken to render the page so rather than do this a simple cache of the results is used (using the built in Drupal cache functions). The cache timeout is set to 15 minutes to allow for potentially large amounts of change in the data at the start of a campaign. Here is the code for getting the report data from dotMailer and rendering it into a table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

function list_dotmailer_campaigns() {
  // Set table headers
  $header = array('Date Sent', 'Name', 'Sends', 'Delivered', '% Delivered', 'Unique Opens', '% Unique Opens');
 
  if ($cache = cache_get('dotmailer_summary')) {
    // Build rows from cache.
    $rows = unserialize($cache->data);
  } else {
    // Build rows from dotMailer API
    $rows = array();
 
    // Get dotmailer object
    $dotmailer = new DotMailer('username', 'password');
 
    // Get all activities that have been active for the past 3 months
    $campaigns = $dotmailer->ListSentCampaignsWithActivitySinceDate(date('Y-m-d\TH:i:s', strtotime('-3 months')));
 
    // Construct the rows of the table.
    foreach ($campaigns as $campaign) {
      $campaign_summary = $dotmailer->GetCampaignSummary($campaign->Id);
      $rows[] = array(
        format_date(strtotime($campaign_summary->DateSent)),
        $campaign->Name,
        $campaign_summary->NumSent,
        $campaign_summary->NumTotalDelivered,
        round($campaign_summary->PercentageDelivered * 100, 2),
        $campaign_summary->NumTotalUniqueOpens,
        round($campaign_summary->PercentageUniqueOpens * 100, 2)
      );
    }
    // Ensure the data from dotMailer is correctly sorted.
    usort($rows, 'sort_dotmailer_campaigns_by_date');
 
    // Cache the results.
    cache_set('dotmailer_summary', serialize($rows), 'cache', strtotime('+15 minutes'));
  }
 
  return theme('table', array('header' => $header, 'rows' => $rows));
}

The return of this function can either be returned as part of the output of a page or placed into a block. I can't demonstrate the output of the function here due to sensitive nature of the output, but here is an example of data with some made up values.

One thing that was spotted when implementing this was that although dotMailer did return the correct data, there tended to be a small issue in the order of the results. I was expecting the result to be sorted by the date that they were sent, but this wasn't always the case. The following sort function was created to enforce the correct ordering of the data and is used in the above code.

1
2
3
4
5
6
7
8
9
10
11
12
13

function sort_dotmailer_campaigns_by_date($a, $b) {
  $obj_a_date = date_create_from_format('D, d/m/Y - H:i', $a[0]);
  $a_date = date_format($obj_a_date, 'U');
 
  $obj_b_date = date_create_from_format('D, d/m/Y - H:i', $b[0]);
  $b_date = date_format($obj_b_date, 'U');  
  
  // Compare
  if ($a_date == $b_date) {
    return 0;
  }
  return ($a_date > $b_date) ? -1 : 1;
}

The above code can be adapted to most of the reporting API methods available from dotMailer, most of them return a list of campaigns that can be iterated over. We haven't implemented any other reporting methods in the dotMailer API class yet, but if you want to add more then please visit the github project page and get involved.

In the next part in this series of posts we will look at integrating dotMailer into Views so that we can push multiple contacts into different address lists in dotMailer.