Best Practices for Creating Useful Dashboards

Build the Metrics

Technically speaking this is the part that takes the most work.  It isn’t necessarily going to be the hardest part but will instead be the most work intensive.  Also, this part will likely take the most technical know how. In this part you will be using SQL or another programming language, a spreadsheet tool like Excel or Google Sheets, or using a data visualization and reporting platform like Chartio.  

The first step, however, is understanding the metrics you are trying to present which you already did in a previous lesson, with particular emphasis on the column “How is it calculated?”.  Revisiting those equations, now you can add in the column names from the tables you collected from the Data Gatekeeper in the previous step, and perform the calculations.

Adding in the columns from the tables you will be querying into the arithmetic is like high school algebra.  In many regards these equations, if you understand the equations and know which columns go where, is essentially plug and play.  You can just plug in the columns and allow your SQL editor or Chartio perform the equations for you. That is one of the beauties of SQL, not only can it do the logistical work for you by finding the columns in the data sources, but it can also complete mathematical equations as long as it is supported by the data source you’re querying from.  For more of an understanding of the SQL syntax and supported functions for the data source you are using, you will need to refer to the data source documents or use your own working knowledge.

For reference purposes, Chartio will always use the SQL syntax native to your data source.  In Chartio’s Visual SQL, you don’t need to worry about which SQL syntax to use as Chartio will always generate the SQL syntax that matches your data source. However, in the Visual SQL Data Pipeline, SQLite syntax must be used to perform custom formulas or functions.

A few examples

To build the table that will become our Revenue Chart (Metric 1) you would need to create the following SQL:

SELECT SUM("Payments"."amount") AS "Total sum of Amount"
FROM "public"."payments" AS "Payments"
WHERE ("Payments"."payment_date"::DATE BETWEEN '2018-01-01' AND {TODAY})

And in Visual SQL:

To build the table that will become Monthly Total Subscriptions Chart (Metric 7) you would need to create the following SQL:


SELECT TO_CHAR("Subscriptions"."payment_start_date", 'YYYY-MM') AS "Month of Payment Start Date",
COUNT(DISTINCT "Subscriptions"."subscription_id") AS "Subscriptions"
FROM "public"."subscriptions" AS "Subscriptions"
WHERE ("Subscriptions"."cancelled_date" IS NULL
AND "Subscriptions"."payment_start_date"::DATE BETWEEN {CALENDAR_INTERVAL.START} AND {CALENDAR_INTERVAL.END})
GROUP BY TO_CHAR("Subscriptions"."payment_start_date", 'YYYY-MM')
ORDER BY "Month of Payment Start Date" ASC

And in Visual SQL:

As a final example, a more involved query, to build the table that will build our Marketing Spend – Monthly by Type (metric 12) you would need to create the following SQL:


SELECT TO_CHAR("Marketing"."created_date", 'YYYY-MM') AS "Month of Created Date",
"Marketing"."description" AS "Description",
SUM("Marketing"."cost") AS "Total sum of Cost"
FROM "public"."marketing" AS "Marketing"
WHERE ("Marketing"."created_date"::DATE BETWEEN {CALENDAR_INTERVAL.START} AND {CALENDAR_INTERVAL.END})
GROUP BY TO_CHAR("Marketing"."created_date", 'YYYY-MM'),
"Marketing"."description"
ORDER BY "Month of Created Date" ASC,
"Description" ASC

Using Visual SQL:

Conclusion

Running the SQL queries and making the calculations is the final step in the Data Science part of the Dashboard Project.  You can now get into the presentation piece and start to analyze these data. You will now want to start considering your visualizations.  Of course in many business intelligence visualization tools, the chart library will be an important dictator of selecting the chart for the data you will be presenting. Understanding your options is very important.  Knowing which charts to use for which data presentation is another, and that piece is covered in the next lesson.