Google Charts Archives | Data makes sense.

I recently created a job that calculates some KPIs and writes a txt file that gets picked up as an email body for daily delivery. It was a few lines of text, with the date and kpi value, like the example below

2014-01-16 New Customers: 7 Sold_Items: 15
2014-01-15   New Customers: 6 Sold_Items: 12
2014-01-14   New Customers: 5 Sold_Items: 6
2014-01-13   New Customers: 4 Sold_Items: 8
2014-01-12   New Customers: 3   Sold_Items: 8
2014-01-11   New Customers: 2   Sold_Items: 4
2014-01-10   New Customers: 1   Sold_Items: 3

It was the easiest approach to just concatenate date, customers, items coming from my data source with some strings, but of course it was hard to read. There were several such blocks of text in it, so while the data was there, it was hard to see a trend.

the sql for it went something like:

SELECT concat_ws(' ', date, ' New Customers:', users, ' Sold_Items:', items) from kpi_table ORDER BY date DESC LIMIT 7 INTO OUTFILE 'C:\\folder\\email_kpi.txt' FIELDS TERMINATED BY ';';

Easy, but by far not the best way to deliver an email report.

To properly display this data with the idea in mind that the recipient will have to read and understand it, it must be represented differently.

Best ways are a visual aid, along with a structured format to allow the user to work with the data, so I decided for a chart and a table. Luckily, google charts offers a simple way that allows you to put parameters in an url, which will then return the chart. So I had a go at it, and came up with below.
Note the url is not secure; on the other ahnd, this is not a problem, as you can only access the specific url with the data you put in it. In other words, if you don’t have the data in the first place, you can’t access it. It’s not dynamic, so there’s no possibility of future leaks.

Google Image chart for emailing, with parameters in url

As for the table, a simple html table will do – it allows you to copy paste straight into spreadsheets and keep the formatting, and the simpler the better. Table formatting may vary, it will normally look pretty plain, but some email clients support inline css, so if you have the time, feel free to experiment.

date	New Customers	Sold Items
2014-01-26	15	30
2014-01-25	20	40
2014-01-24	49	80
2014-01-23	45	73
2014-01-22	22	40
2014-01-21	21	50
2014-01-20	18	33

What about the code to create this every day?

I use plain mysql that generates the two lines as text

for the table:

select concat('&lt;p&gt;&lt;table border="1"&gt;',
'&lt;tr&gt;&lt;td&gt;   date  &lt;/td&gt;&lt;td&gt;New Customers&lt;/td&gt;&lt;td&gt;Sold Items&lt;/td&gt;&lt;/tr&gt;',
group_concat('&lt;tr&gt;&lt;td&gt;',kpi_table.date,'&lt;/td&gt;&lt;td&gt;',kpi_table.users,'&lt;/td&gt;&lt;td&gt;',kpi_table.items,'&lt;/td&gt;&lt;/tr&gt;' separator '' ),
'&lt;/table&gt;')

	from kpi_table  order by date desc limit 7

select concat('<p><table border="1">',

'<tr><td> date </td><td>New Customers</td><td>Sold Items</td></tr>',

group_concat('<tr><td>',kpi_table.date,'</td><td>',kpi_table.users,'</td><td>',kpi_table.items,'</td></tr>' separator '' ),

'</table>')

from kpi_table order by date desc limit 7

for the chart:

select
concat('&lt;p&gt;&lt;img src="', /** link as image **/
'http://chart.googleapis.com/chart?cht=lc&amp;chs=750x300&amp;chd=t:', /** chart type, size **/
group_concat(items order by date asc), /**values line 1**/
'|',
group_concat(users order by date asc), /** values line 2**/
'&amp;chds=0,',
floor(greatest(max(items),max(activated_users))/50+1)*50, /** upper chart limit, highest value rounded up to nearest multiple of 50 **/
'&amp;chxt=x,y&amp;chxs=0,022222,11,0,lt|1,011111,10,1,lt&amp;chdl=Items_sold|new_customers&amp;chxl=0:|', /** axis label styles**/
group_concat(date order by date asc separator '|'),
'|1:|',
concat_ws('|',
0,
floor(greatest(max(items),max(users))/50+1)*10,
floor(greatest(max(items),max(users))/50+1)*20,
floor(greatest(max(items),max(users))/50+1)*30,
floor(greatest(max(items),max(users))/50+1)*40,
floor(greatest(max(items),max(users))/50+1)*50
), /** y axis notch values dinamically according to higest chart value **/
'&amp;chco=044444,099999&amp;chg=16.67,20&amp;chm=N,004040,0,-1,11|N,005050,1,-1,11&amp;chtt=Sold_items', /** line color, numbers color, grid frequency - 20 is 5, 16.67 is 6; title **/
'" &gt;'
)
from(
select k.date, k.users, k.sized_items
from kpi_table k order by date desc limit 7
	)k

select

concat('<p><img src="', /** link as image **/

'http://chart.googleapis.com/chart?cht=lc&chs=750x300&chd=t:', /** chart type, size **/

group_concat(items order by date asc), /**values line 1**/

'|',

group_concat(users order by date asc), /** values line 2**/

'&chds=0,',

floor(greatest(max(items),max(activated_users))/50+1)*50, /** upper chart limit, highest value rounded up to nearest multiple of 50 **/

'&chxt=x,y&chxs=0,022222,11,0,lt|1,011111,10,1,lt&chdl=Items_sold|new_customers&chxl=0:|', /** axis label styles**/

group_concat(date order by date asc separator '|'),

'|1:|',

concat_ws('|',

floor(greatest(max(items),max(users))/50+1)*10,

floor(greatest(max(items),max(users))/50+1)*20,

floor(greatest(max(items),max(users))/50+1)*30,

floor(greatest(max(items),max(users))/50+1)*40,

floor(greatest(max(items),max(users))/50+1)*50

), /** y axis notch values dinamically according to higest chart value **/

'&chco=044444,099999&chg=16.67,20&chm=N,004040,0,-1,11|N,005050,1,-1,11&chtt=Sold_items', /** line color, numbers color, grid frequency - 20 is 5, 16.67 is 6; title **/

'" >'

)

from(

select k.date, k.users, k.sized_items

from kpi_table k order by date desc limit 7

For the google image chart it’s a bit more work to get the vertical limit and the axes to update according to your maximum values, but otherwise pretty straightforward. The easiest way to figure out what you need is in the google playground in the link below. Alternatively you can just change the parameters in the url of the google chart above

https://developers.google.com/chart/image/docs/chart_playground?

My parameters were as follows:

cht=lc chs=650x300 chd=t:30,40,80,73,40,50,33 15,20,49,45,22,21,18 chds=0,100 chxt=x,y chxs=0,022222,11,0,lt 1,011111,10,1,lt chdl=Items_Sold New_Customers chxl=0: 2014-01-20 2014-01-21 2014-01-22 2014-01-23 2014-01-24 2014-01-25 2014-01-26 1: 0 20 40 60 80 100 chco=044444,099999 chg=16.67,20 chm=N,004040,0,-1,11 N,005050,1,-1,11 chtt=Daily_Sales

You end up with just one line for each, perfect for shooting into a html email. Easy to read, looks good.

and the final result:

Stay tuned for more!

While data will most often be used as a tool to assist in making decisions, it sometimes also serves as a motivator. Live dashboards can allow your client to react quickly to any changes in their KPIs, or can assist a sales team in seeing how close they are to their daily targets.

Stick them in fullscreen mode on a wall display or a TV, and they will be there to offer this transparency non stop.

There are quite a few pieces of software out there that allow you to create live, automatically updating, automatically refreshing dashboards, but most of them cost more than it’s worth, and some are quite glitchy. I will not go into these tools now, but will rather offer a very fast and easy alternative.

When looking for this, my first requirement was that the refresh is easy to accomplish and smooth. HTML offers that, so I wanted something that can be wrapped in HTML.

HTML also offers the advantage of being flexible in regards to where you put it: as standalone on a dedicated web page, or embedded in an admin frontend or dashboard panel.

Google has a nice free Javascript API for creating charts, compatible with data pulled from almost any source. You can find more details about the look and feel of it here.

My final result was this, for a daily chart updated every 3 minutes:

Google does not provide any connectors for MySql or other common Sql, and most often that’s where live data will be.

I tried looking around for some resources that will pull the sql data into the chart, but my search was not very fruitful. Google API wants the data in a very specific format, so I took the liberty of writing a little PHP to do the job.

If you are not interested in how this works, just scroll all the way down and copy paste the full code into Notepad or TextEdit and save it as PHP. If you want to edit the code, I use Notepad++ as it highlights the operators.

1. First part: connecting to the database

<?php

//define host, database name, user and pass:
$db_host = 'YOUR Host IP/URL:PORT'
$db_database = 'YOUR_DATABASE';
$db_user = 'USERNAME';
$db_password = 'Password';
//connect:
$db = mysql_connect($db_host, $db_user, $db_password);
mysql_select_db($db_database);
?>

<?php

//define host, database name, user and pass:

$db_host = 'YOUR Host IP/URL:PORT'

$db_database = 'YOUR_DATABASE';

$db_user = 'USERNAME';

$db_password = 'Password';

//connect:

$db = mysql_connect($db_host, $db_user, $db_password);

mysql_select_db($db_database);

2. Second part: transforming the data in the right format. We do this with an echo.

<?php
// My sql table has the columns Date, Margin, Target, Last month Margin, so I want the lines to //contain summed up values for each day. Except target, which is the monthly  target on each row, //to create a horizontal line
$sqlQuery = "SELECT fulldate,
round((SELECT SUM(last_month_margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS last_month_margin ,
round(Target,2) AS Target,
round((SELECT SUM(margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS margin
FROM dummy_value_daily d1
group by 1";
$sqlResult = mysql_query($sqlQuery);
while ($row = mysql_fetch_assoc($sqlResult)) {

//echo to put it in the right format
echo " data.addRow(['{$row['fulldate']}', {v: {$row['last_month_margin']}, f: '&#8364 {$row['last_month_margin']}' }, {v: {$row['Target']}, f: '&#8364 {$row['Target']}' }, {v: {$row['margin']}, f: '&#8364 {$row['margin']}' } ]); ";

}

?>

<?php

// My sql table has the columns Date, Margin, Target, Last month Margin, so I want the lines to //contain summed up values for each day. Except target, which is the monthly target on each row, //to create a horizontal line

$sqlQuery = "SELECT fulldate,

round((SELECT SUM(last_month_margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS last_month_margin ,

round(Target,2) AS Target,

round((SELECT SUM(margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS margin

FROM dummy_value_daily d1

group by 1";

$sqlResult = mysql_query($sqlQuery);

while ($row = mysql_fetch_assoc($sqlResult)) {

//echo to put it in the right format

echo " data.addRow(['{$row['fulldate']}', {v: {$row['last_month_margin']}, f: '&#8364 {$row['last_month_margin']}' }, {v: {$row['Target']}, f: '&#8364 {$row['Target']}' }, {v: {$row['margin']}, f: '&#8364 {$row['margin']}' } ]); ";

}

3. Final part: creating the final PHP with the Google API. You can find all the documentation for Google Charts API here

Note the refresh is set to 180 seconds with meta http-equiv="refresh" content="180" The php is run, query pulls data, and only after that is successful, the chart refreshes, so the update will be very smooth to the eye.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="refresh" content="180" >

<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<title>
Google Visualization API Sample
</title>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {packages: ['corechart']});
</script>
<script type="text/javascript">

function drawVisualization() {
// Create and populate the data table.
var data = new google.visualization.DataTable();
data.addColumn('string', 'date_minute');
data.addColumn('number', 'Last Month');
data.addColumn('number', 'Target');
data.addColumn('number', 'Margin');
<?php

$db_host = 'your_host';
$db_database = 'your_Database';
$db_user = 'your_user';
$db_password = 'your_password';

$db = mysql_connect($db_host, $db_user, $db_password);
mysql_select_db($db_database);

$sqlQuery = "SELECT right(fulldate,2) as fulldate,
round((SELECT SUM(last_month_margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS last_month_margin ,
round(Target,2) AS Target,
round((SELECT SUM(margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS margin
FROM dummy_value_daily d1
group by 1";
$sqlResult = mysql_query($sqlQuery);
while ($row = mysql_fetch_assoc($sqlResult)) {

echo " data.addRow(['{$row['fulldate']}', {v: {$row['last_month_margin']}, f: '€ {$row['last_month_margin']}' }, {v: {$row['Target']}, f: '€ {$row['Target']}' }, {v: {$row['margin']}, f: '€ {$row['margin']}' } ]); ";

}

?>

// Create and draw the visualization.
new google.visualization.LineChart(document.getElementById('visualization')).
draw(data, {curveType: "none",
title: "Monthly Margin",
titleTextStyle: {color: "orange"},
width: 1600, height: 400,
//vAxis: {maxValue: 10},
vAxis: {minValue: 0},
vAxis: {title: 'Euro'},
vAxis: {baseline: 0},
vAxis: {gridlines: {count: 10}  },
vAxis: {title: "Euro", titleTextStyle: {color: "orange"}},
hAxis: {title: "Day", titleTextStyle: {color: "orange"}},
interpolateNulls: 1
}
);
}

google.setOnLoadCallback(drawVisualization);
</script>
</head>
<body style="font-family: Arial;border: 0 none;">
<div id="visualization" style="width: 500px; height: 400px;"></div>
</body>
</html>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<head>

<title>

Google Visualization API Sample

</title>

google.load('visualization', '1', {packages: ['corechart']});

</script>

function drawVisualization() {

// Create and populate the data table.

var data = new google.visualization.DataTable();

data.addColumn('string', 'date_minute');

data.addColumn('number', 'Last Month');

data.addColumn('number', 'Target');

data.addColumn('number', 'Margin');

<?php

$db_host = 'your_host';

$db_database = 'your_Database';

$db_user = 'your_user';

$db_password = 'your_password';

$db = mysql_connect($db_host, $db_user, $db_password);

mysql_select_db($db_database);

$sqlQuery = "SELECT right(fulldate,2) as fulldate,

round((SELECT SUM(last_month_margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS last_month_margin ,

round(Target,2) AS Target,

round((SELECT SUM(margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS margin

FROM dummy_value_daily d1

group by 1";

$sqlResult = mysql_query($sqlQuery);

while ($row = mysql_fetch_assoc($sqlResult)) {

echo " data.addRow(['{$row['fulldate']}', {v: {$row['last_month_margin']}, f: '€ {$row['last_month_margin']}' }, {v: {$row['Target']}, f: '€ {$row['Target']}' }, {v: {$row['margin']}, f: '€ {$row['margin']}' } ]); ";

}

// Create and draw the visualization.

new google.visualization.LineChart(document.getElementById('visualization')).

draw(data, {curveType: "none",

title: "Monthly Margin",

titleTextStyle: {color: "orange"},

width: 1600, height: 400,

//vAxis: {maxValue: 10},

vAxis: {minValue: 0},

vAxis: {title: 'Euro'},

vAxis: {baseline: 0},

vAxis: {gridlines: {count: 10} },

vAxis: {title: "Euro", titleTextStyle: {color: "orange"}},

hAxis: {title: "Day", titleTextStyle: {color: "orange"}},

interpolateNulls: 1

}

);

}

google.setOnLoadCallback(drawVisualization);

</script>

</head>

</body>

</html>

4. Running the PHP file

To run the PHP you need a dev server, a web server, or someone who can upload it somewhere for you. I will cover how to install a lightweight dev server in a separate post. I recommend EasyPHP dev server if this is all you will ever do with it, alternatively XAMPP is a better option allowing you to publish to web as well. Guide for installing and configuring easyphp here.

5. Result:

So I hope you enjoyed this guide, and that you will find this useful. One final note: Google API has all kinds of charts, and it’s easy to switch between types. You can change this chart to a column chart by changing just one word in the html. Just give it a shot 🙂

Tag: Google Charts

KPI emails with google charts url parameters

Google Charts API – from your SQL database to a live chart with no coding skills