Replacing Your Daily Email Reports with a Realtime Board

If you run or own a website of any kind, chances are that you produce some sort of daily e-mail with statistics collected from the previous day's activities. For a store, for example, these could include the number of sales, the best-performing products, and the geographic distribution of your customers.

There's enormous value in these e-mails: They give you a good idea of how your site is doing, and provide you with hard data that you can use to make decisions and analyze pain points that need addressing.

Despite their benefits, however, they also suffer from a few significant limitations:

  • E-mails get lost. After a while, the likelihood that they will be ignored and sent directly to spam is pretty high.
  • E-mails require active participation. In order to really understand what's going on, e-mails must, obviously, be read.
  • Once a day is too little. Sites run 24 hours a day, and by the time you get that daily e-mail, it might already be too late to address a particular problem.

In this blog post, I'll show how you can very easily replace your daily e-mails with a beautiful dashboard that everyone in your office will want to look at. I'll be using our PHP library for the purpose, but the same principles will work well in Ruby, Node.js, or pretty much any other language.

Statistics, Telemetry-style

This kind of scenarios is actually one of the initial reasons why we built Telemetry: By replacing daily e-mails and static web pages with a live dashboard, you can get an instant snapshot of everything that's happening as it happens, with the added bonus that the information is rendered in a visual format that can be interpreted at a glance. In addition, Telemetry dashboards look absolutely fabulous on a big screen TV, which makes them instant office favourites.

You can clone this project on GitHub

For this demo, I put together a bit of sample data for a fictitious online store that sells different kinds of paper. The store has a handful of customers from several countries, but they are pretty active and generate a few hundred sales a day:

You can generate a copy of this database by executing the sql/data.sql dump file.

A sales breakdown

One important question that's hard to answer by just looking at a bunch of tables in an e-mail is just how well your sales are doing today compared to yesterday.

With Telemetry, however, that's a fairly simple operation. Looking at our schema, we can pull out the sales data, broken down by hour, simply by exeuting a query like the following:

select 
    count(*) as c, hour(date) as h 
from 
    sales 
where 
    date between ? and ? 
group by 
    h 
order by 
    h

We can then run this query for yesterday, and today, giving us a breakdown for the two days; for example, in PHP:

// Create a timeseries with yesterday's sales by hour

$statement = $db->prepare('select count(*) as c, hour(date) as h from sales where date between ? and ? group by h order by h');
$rs = $statement->execute(array($yesterdayStart, $yesterdayEnd));
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

$yesterday = array();

foreach ($rows as $row) {
  $yesterday[] = (int) $row['c'];
}

// Do the same, but with today's

$rs = $statement->execute(array($todayStart, $now));
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

$today = array();

foreach ($rows as $row) {
  $today[] = (int) $row['c'];
}

while(count($today) < 24) $today[] = 0;

Note that we pad out today's results until we have 24 data points; doing so will make the comparison between the two days easier. We are now ready to send these two data series to our board, where we have created a Graph flow called sales_by_hour:

$t->addUpdate('sales_by_hour', array(
  'title'     => 'Sales by hour',
  'renderer'  => 'line',
  'x_labels'  => array('0', '1', '2', '3', '4', '5',
                       '6', '7', '8', '9', '10', '11',
                       '12', '13', '14', '15', '16', '17',
                       '18', '19', '20', '21', '22', '23'),
  'series'    => array(
                  array(
                    'values' => $yesterday,
                    'color'  => '#3abda3',
                    'label'  => 'Yesterday',
                  ),
                  array(
                    'values' => $today,
                    'color'  => '#df7426',
                    'label'  => 'Today',
                  ),
                )
));

$t->post();

That's it! Telemetry will now visualize this data for us in a way that makes it very easy to understand at a glance what's going on throughout the day:

(Obviously, there is no data to display on the current day after 2PM; therefore, the corresponding graph results are zeroed out.)

For product and country

Next, we can break down our sales by country and product. This kind of information is useful if you need a rough idea, for example, of how much paper to reorder.

To start, we'll create a new Barchart flow and call it sales_by_product. We can then extract a breakdown of product sales between two dates with a query like this one:

select 
    name, count(*) as c 
from 
    sales ss inner join products pp on ss.product = pp.id 
where 
    date between ? and ?
group by 
    name 
order by 
    c desc;

A little PHP code helps us glue this together into a data package that we can send over to the Telemetry API:

// Get sales broken down by country

$statement = $db->prepare('select name, count(*) as c 
                           from sales ss inner join products pp on ss.product = pp.id
                           where date between ? and ?
                           group by name order by c desc;');
$rs = $statement->execute(array($yesterdayStart, $yesterdayEnd));
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

$colors = array('#3abda3', '#df7426', '#f7c26c', '#c8c19c', '#aeab9c', '#97cbe0');
reset($colors);

$bars = array();

foreach($rows as $row) {
  $color = next($colors);

  if (!$color) {
    reset($colors);
  }

  $bars[] = array(
    'color' => $color,
    'value' => (int) $row['c'],
    'label' => $row['name']
  );
}

$t->addUpdate('sales_by_product', array(
  'title' => 'Country Breakdown Today',
  'bars'  => $bars
));

$t->post();

Bringing it all together

A similar process gives us a breakdown of sales by country for the current day, resulting in a board that looks like this:

And this is just the beginning, of course. You could add this script to a cron job and have it run once a day, replacing your daily e-mails and giving yourself and your colleagues something to look at any time they want.

You'd be surprised by how much you can learn just by glancing at a well-designed board, and at how much more quickly you can be on top of problems as they arise.

Even better, there's nothing stopping you from adding more information to your board. Unlike an e-mail, a dashboard can easily support a high degree of information density without overwhelming the viewer. For example, you could plug into Apache's own stats and use a Gauge to indicate how many users are currently visiting your site.

Best of all, however, is the fact that you can update this information constantly throughout the day. Telemetry allows you to send updates every second, if you like, but even being able to measure everything that's happening with a frequency of a few seconds or minutes gives you an immediate bird's-eye view of every bit of information at your disposal—something that e-mails simply cannot do.

Discuss this post on Hacker News. Ready to take the plunge? Create a free account and get started with your own boards right away!

Visit TelemetryTV