Easy email dashboards with dashflow.io

If you need to create dashboards sent via email driven by a database check out https://dashflow.io. It can be used both as a command line utility or as python module.

Even with all the BI tools, I still get a request for dashboards to be emailed or printed. Tools like Tableau don't lend themselves to print-friendly or email friendly. Some people just don't have the time to go several clicks in and drill down when they are in meetings all day.

Dash flow will work out of the box with any python db-api2 database module which can be used with sqlalchemy. Your database queries are all stored in sql files, under the hood it uses https://pugsql.org/ which can be best described as a reverse ORM. Entirely built on open-source modules.

The email is constructed via a simple Json file that has an array of sections. Sections can be anything from tables, charts or text with data mixed in.

For the moment it only supports sending via smtp. But if you want to try it out, https://sendgrid.com has a nice free tier.

Charts are rendered using https://quickchart.io/.

Now, how about we show the steps to send an email.

Clone and setup dashflow

git clone https://github.com/maximoguerrero/dashflow.git
cd dashflow
chmod u+x df-cli.py

Create folders for the email

mkdir testemail
cd testemail

mkdir sql

cp ../sample/sample.db sample.db

Create your Sql file

Since we are using pusql the first line of the SQL file is a comment that is used to define the module and its return type. Here the modules name is media_profit_by_type_filtered and that is what you will use in your config file.

This query simply returns the media type that was most profitable from our sample database. Save this file in the sql folder we created in the previous step

-- :name media_profit_by_type_filtered    :many
SELECT  mt.Name as label, strftime('%Y',InvoiceDate) as year  ,  printf("%.2f", sum(ii.UnitPrice ) )  as value
FROM `tracks` t
inner join `media_types` mt on mt.MediaTypeId = t.MediaTypeId
inner join `invoice_items` ii on ii.TrackId = t.TrackId
inner join `invoices` i on i.InvoiceId = ii.InvoiceId
where mt.Name like '%' || :kind || '%'
group by t.MediaTypeId, year
order by year;

Next create a simple json

In this file, we will provide a couple of things. A connection string compatible with sqlAlchemy, a relative path to the SQL folder where the SQL files are. A quickcharts.io compatible service URL.

The section array contains a definition for a chart, moduleName is what we defined in the SQL file. The parameter is one that was used in the SQL file.

The next important part is the SMPT section where you provide parameters for SMPT Server.

{
    "isDebug": true,
    "connection": "sqlite:///<absolutePath>/sample.db",
    "sqlfolder": "sql/",
    "quickChartsUrl": "https://quickcharts.dashflow.io/chart?bkg=white&c=",
    "title": "This Months Dashboard",
    "description": "Ad reprehenderit amet mollit Lorem aliquip sint anim ipsum nisi deserunt commodo veniam magna.",
    "sections": [
        {
            "type": "chart",
            "sectionTitle": "horizontal bar chart! with query parramter",
            "description": "Irure esse eu officia consequat mollit ullamco est aliquip..",
            "moduleName": "media_profit_by_type_filtered",
            "moduleParameters": [
                {
                    "name": "kind"
                }
            ],
            "groupBy": "year",
            "chart": {
                "type": "horizontalBar"
            }
        }
    ],
    "to": [
        {
            "type": "string",
            "value": "email.to@send.com"
        }

    ],
    "from": "email@from.com",
    "subject": "my first email",
    "sendEngine": {
        "type": "smtp",
        "host": "YOUR_SMTP_HOST",
        "port": "587",
        "enableTLS": true,
        "requiresAuthentication": true,
        "useEnvVariable":{
            "username": "SMTP_USER",
            "password": "SMTP_PWD"

        }
    }
}

Next lets run the script

./df-cli.py --configFile testemail/sample-config.json --parameter="kind:audio"

You will get the following email: