simplenews_statistics.module in Simplenews Statistics 6

Gathers newsletter statistics.

File

simplenews_statistics.module

<?php

// $Id:

/**
 * @file
 * Gathers newsletter statistics.
 */

/**
 * Implementation of hook_menu().
 */
function simplenews_statistics_menu() {
  $items['admin/content/simplenews/statistics'] = array(
    'title' => 'Statistics',
    'description' => 'View the openings/rate and clicks/CTR for your newsletters',
    'type' => MENU_NORMAL_ITEM,
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'simplenews_statistics_admin_form',
    ),
    'access arguments' => array(
      'administer newsletters',
    ),
  );
  $items['admin/content/simplenews/statistics/%'] = array(
    'title' => 'Statistics Details',
    'type' => MENU_NORMAL_ITEM,
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'simplenews_statistics_admin_opens',
      4,
    ),
    'access arguments' => array(
      'administer newsletters',
    ),
  );
  $items['admin/content/simplenews/statistics/%/opens'] = array(
    'title' => 'Newsletter opens',
    'type' => MENU_DEFAULT_LOCAL_TASK,
    'weight' => -10,
  );
  $items['admin/content/simplenews/statistics/%/clicks'] = array(
    'title' => 'Newsletter clicks',
    'type' => MENU_LOCAL_TASK,
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'simplenews_statistics_admin_clicks',
      4,
    ),
    'access arguments' => array(
      'administer newsletters',
    ),
  );
  $items['simplenews/statistics/view'] = array(
    'type' => MENU_CALLBACK,
    'page callback' => 'simplenews_statistics_view',
    'access arguments' => array(
      'access content',
    ),
  );
  $items['simplenews/statistics/click'] = array(
    'type' => MENU_CALLBACK,
    'page callback' => 'simplenews_statistics_click',
    'access arguments' => array(
      'access content',
    ),
  );
  return $items;
}

/**
 * simplenews_statistics_view.
 *
 * Gathers the opens
 */
function simplenews_statistics_view() {
  if ($_GET['mail'] && $_GET['nid']) {
    if (!db_fetch_array(db_query("SELECT * FROM {simplenews_statistics_opens} WHERE email = '%s' AND nid = %d", $_GET['mail'], $_GET['nid']))) {
      db_query("INSERT INTO {simplenews_statistics_opens} (email, nid, opens, timestamp) VALUES ('%s', %d, %d, %d)", $_GET['mail'], $_GET['nid'], 1, time());
    }
    else {
      db_query("UPDATE {simplenews_statistics_opens} SET opens = opens+1 WHERE email = '%s' AND nid = %d", $_GET['mail'], $_GET['nid']);
    }
  }
}

/**
 * simplenews_statistics_click.
 *
 * Gathers the clicks
 */
function simplenews_statistics_click() {
  if ($_GET['mail'] && $_GET['nid'] && $_GET['url']) {
    db_query("INSERT INTO {simplenews_statistics_clicks} (email, nid, url, timestamp) VALUES ('%s', %d, '%s', %d)", $_GET['mail'], $_GET['nid'], urldecode($_GET['url']), time());
  }
  drupal_goto(urldecode($_GET['url']));
}

/**
 * Implemetation of hook_mail_alter().
 *
 * Adds a hidden image to the body and counts the amount of emails send
 * 
 * @param $message
 *  Mail message array.
 */
function simplenews_statistics_mail_alter(&$message) {
  if ($message['id'] == 'simplenews_node' && $message['params']['context']['node']->simplenews['s_format'] == 'html') {
    global $base_url;
    $body = $message['body']['body'];
    $nid = $message['params']['context']['node']->nid;
    $account = $message['params']['context']['account'];

    //expand all local links using mimemail _mimemail_url function
    require_once drupal_get_path('module', 'mimemail') . '/mimemail.inc';
    $pattern = '/(<a[^>]+href=")([^"]*)/emi';
    $body = preg_replace($pattern, '"\\1"._mimemail_url("\\2")', $body);

    // replace links
    $body = preg_replace($pattern, '"\\1"._simplenews_statistics_replace_url("\\2",$nid,$account->mail)', $body);

    // add hidden image
    $url = url('simplenews/statistics/view', array(
      'absolute' => TRUE,
      'query' => array(
        'nid' => $nid,
        'mail' => $account->mail,
      ),
    ));
    $body .= '<img src="' . $url . '" width="1" height="1">';

    // count
    if (!db_fetch_array(db_query("SELECT * FROM {simplenews_statistics} WHERE nid = %d", $nid))) {
      db_query("INSERT INTO {simplenews_statistics} (nid, send) VALUES (%d, %d)", $nid, 1);
    }
    else {
      db_query("UPDATE {simplenews_statistics} SET send = send+1 WHERE nid = %d", $nid);
    }

    // set new body
    $message['body']['body'] = $body;
  }
}

/**
 * Alter link to go through statistics
 *
 */
function _simplenews_statistics_replace_url($match, $nid, $mail) {
  return url('simplenews/statistics/click', array(
    'absolute' => TRUE,
    'query' => array(
      'nid' => $nid,
      'mail' => $mail,
      'url' => $match,
    ),
  ));
}

/**
 * Implementation of hook_help().
 */
function simplenews_statistics_help($path, $arg) {
  switch ($path) {
    case 'admin/help#simplenews_statistics':
      $help = '<p>' . t('Simplenews Statistics gathers the open rate and CTR of a send newsletter.') . "<br />\n";
      $help .= t("Open Rate is the number of people who open a newsletter divided by the amount of subscriptions.") . "<br />\n";
      $help .= t("CTR (click-through rate) is the number of people who clicked a link in a newsletter divided by the amount of subscriptions") . "</p>\n";
      return $help;
    case 'admin/content/simplenews/statistics':
      $help = '<p>' . t('Shows the open rate and CTR for your newsletters.') . "<p>\n";
      return $help;
    case 'admin/content/simplenews/statistics/%':
      $help = '<p>' . t('Shows who and how many times the newsletter has been openend.') . "<p>\n";
      return $help;
    case 'admin/content/simplenews/statistics/%/clicks':
      $help = '<p>' . t('Listing of all clicks for the newsletter.') . "<p>\n";
      return $help;
  }
}

/**
 * Statistics overview
 */
function simplenews_statistics_admin_form(&$form_state, $action = 'sent') {
  drupal_set_title('Newsletter Statistics');
  $vid = variable_get('simplenews_vid', '');
  $form = array();
  $header = array(
    t('Title'),
    t('Newsletter'),
    t('Date created'),
    t('Send'),
    t('Open rate'),
    t('CTR'),
    t('Details'),
  );
  $sql = "SELECT n.*, s.s_status, s.s_format FROM {node} n INNER JOIN {simplenews_newsletters} s ON n.nid = s.nid WHERE s.s_status > 1 AND s.s_format = 'html' ORDER BY n.created DESC";
  $result = db_query(db_rewrite_sql($sql));
  $rows = array();
  while ($row = db_fetch_object($result)) {
    $node = node_load($row->nid);
    $terms = array_keys(taxonomy_node_get_terms_by_vocabulary($node, $vid, 'name'));
    $send = db_fetch_object(db_query("SELECT * FROM {simplenews_statistics} WHERE nid = %d", $node->nid));
    $data = db_fetch_object(db_query("SELECT\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t \t  COUNT(DISTINCT v.email) AS opens,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tCOUNT(DISTINCT c.email) AS clicks\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  {simplenews_statistics_opens} v\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  {simplenews_statistics_clicks} c\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tON\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  v.nid = c.nid\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  v.nid = %d", $node->nid));
    $rows[] = array(
      l($node->title, 'node/' . $node->nid),
      isset($terms[0]) ? $terms[0] : t('n/a'),
      format_date($node->created, 'small'),
      $send ? $send->send : t('No data available.'),
      $send ? $data->opens . ' (' . round($data->opens / $send->send * 100) . '%)' : '',
      $send ? $data->clicks . ' (' . round($data->clicks / $send->send * 100) . '%)' : '',
      $send ? l('Details', 'admin/content/simplenews/statistics/' . $node->nid) : '',
    );
  }
  if (!$rows) {
    $rows[] = array(
      array(
        'data' => t('No newsletters available.'),
        'colspan' => '7',
      ),
    );
  }
  $table = theme('table', $header, $rows);
  $form['table'] = array(
    '#value' => $table,
  );
  return $form;
}

/**
 * Newsletter opens overview
 */
function simplenews_statistics_admin_opens(&$form_state, $nid = NULL) {
  $node = node_load($nid);
  drupal_set_title(check_plain($node->title) . ' Statistics');
  $form = array();
  $header = array(
    array(
      'data' => t('Email'),
      'field' => 'email',
    ),
    array(
      'data' => t('Last open'),
      'field' => 'timestamp',
      'sort' => 'desc',
    ),
    array(
      'data' => t('Open(s)'),
      'field' => 'opens',
    ),
  );
  $sql = "SELECT * FROM {simplenews_statistics_opens} WHERE nid = %d" . tablesort_sql($header);
  $result = pager_query($sql, 50, 0, NULL, $nid);
  $rows = array();
  while ($view = db_fetch_object($result)) {
    $rows[] = array(
      $view->email,
      format_date($view->timestamp, 'small'),
      $view->opens,
    );
  }
  if (!$rows) {
    $rows[] = array(
      array(
        'data' => t('No statistics available.'),
        'colspan' => '2',
      ),
    );
  }
  $table = theme('table', $header, $rows);
  $form['table'] = array(
    '#value' => $table,
  );
  $form['pager'] = array(
    '#value' => theme('pager', NULL, 50, 0),
  );
  return $form;
}

/**
 * Newsletter clicks overview
 */
function simplenews_statistics_admin_clicks(&$form_state, $nid = NULL) {
  $node = node_load($nid);
  drupal_set_title(check_plain($node->title) . ' Statistics');
  $form = array();
  $header = array(
    array(
      'data' => t('Url'),
      'field' => 'url',
      'sort' => 'desc',
    ),
    array(
      'data' => t('Email'),
      'field' => 'email',
      'sort' => 'desc',
    ),
    array(
      'data' => t('Date'),
      'field' => 'timestamp',
      'sort' => 'desc',
    ),
  );
  $sql = "SELECT * FROM {simplenews_statistics_clicks} WHERE nid = %d" . tablesort_sql($header);
  $result = pager_query($sql, 50, 0, NULL, $nid);
  $rows = array();
  while ($view = db_fetch_object($result)) {
    $rows[] = array(
      $view->url,
      $view->email,
      format_date($view->timestamp, 'small'),
    );
  }
  if (!$rows) {
    $rows[] = array(
      array(
        'data' => t('No statistics available.'),
        'colspan' => '3',
      ),
    );
  }
  $table = theme('table', $header, $rows);
  $form['table'] = array(
    '#value' => $table,
  );
  $form['pager'] = array(
    '#value' => theme('pager', NULL, 50, 0),
  );
  return $form;
}

Functions

Name	Description
simplenews_statistics_admin_clicks	Newsletter clicks overview
simplenews_statistics_admin_form	Statistics overview
simplenews_statistics_admin_opens	Newsletter opens overview
simplenews_statistics_click	simplenews_statistics_click.
simplenews_statistics_help	Implementation of hook_help().
simplenews_statistics_mail_alter	Implemetation of hook_mail_alter().
simplenews_statistics_menu	Implementation of hook_menu().
simplenews_statistics_view	simplenews_statistics_view.
_simplenews_statistics_replace_url	Alter link to go through statistics