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.
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:
|
1 2 3 4 5 6 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:
|
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 |
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('|', 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 **/ '&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 )k |
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!