Display Heroku Dataclip metrics in Geckoboard
Important note
Heroku have recently changed both the format of Dataclips URLs and how to generate them. This means you'll need to regenerate your URLs in Heroku and create new Dataclips widgets in Geckoboard.
Heroku's Dev Center has instructions on how to generate a new Dataclip .csv URL.
In this article, we'll cover how you set up the data on Dataclips and configure each of the Heroku Postgres Dataclip widgets on Geckoboard.
What is a Dataclip?
It's is a secret URL that holds the results of a SQL query on a particular Heroku Postgres database. By sharing a Dataclip URL the same way you would share the underlying data, any recipient can view that data within, download it as a .csv
, or even embed it live into a tool like Google Sheets.
Display numerical data
The number widget allows you to display numeric data in a widget from a Heroku Dataclip URL.
Requirements for the number widget
The widget expects numeric data for the column specified in the configuration under field.
How to configure the number widget
From the configuration menu in the widget, specify the following options:
- URL
- The URL to your Heroku Dataclip, provided in the browser address field or from the share option.
Dataclip .csv URL structure
https://data.heroku.com/dataclips/###.csv?access-token=###-###-###-###-###">
Note
Versions are ignored, and just the current Dataclip is used.
- Field
- You can choose which of the options returned from the Dataclip you want to display in the number widget.
The drop-down menu shows the attributes returned in theSELECT
statement, in this examplebook_purchases
andmonth
, so we'll choosebook_purchases
. If the drop-down menu is empty, then your data is not being returned in the expected format.
See the SQL example below for data that is returned in the correct way. - Display
- Choose to display the first or last entry from the column indicated by field. e.g. in this example, the data is displayed in ascending order with the most recent month being last, so we choose last entry.
The example above uses the following SQL:
SELECT
date_trunc('month', purchases.created_at) as month,
sum(quantity * purchase_items.price) as book_purchases
FROM
products,
purchases,
purchase_items
WHERE
tags @> ARRAY[CAST('Book' as varchar(255))]
AND purchases.id = purchase_items.purchase_id
AND products.id = purchase_items.product_id
GROUP BY 1
ORDER BY 1 ASC;
This produces the following data:
month | book_purchases |
2011-01-01 00:00:00+00 | 747.58 |
2011-02-01 00:00:00+00 | 953.48 |
2011-03-01 00:00:00+00 | 767.51 |
2011-04-01 00:00:00+00 | 1221.28 |
2011-05-01 00:00:00+00 | 931.50 |
2011-06-01 00:00:00+00 | 1045.41 |
2011-07-01 00:00:00+00 | 863.42 |
2011-08-01 00:00:00+00 | 629.56 |
2011-09-01 00:00:00+00 | 681.56 |
2011-10-01 00:00:00+00 | 827.51 |
2011-11-01 00:00:00+00 | 701.55 |
2011-12-01 00:00:00+00 | 771.57 |
Display trend chart data
The trend chart widget allows you to display a line chart from a Heroku Dataclip URL. An example is shown below:
Requirements for the trend chart widget
The x-axis field will take alpha-numeric data but the y-axis expects just numeric data.
How to configure the trend chart widget
From the configuration menu in the widget, specify the following options:
- URL
- The URL to your Heroku Dataclip, provided in the browser address field or from the share option.
Dataclip .csv URL structure
https://data.heroku.com/dataclips/###.csv?access-token=###-###-###-###-###">
Note
Versions are ignored, and just the current Dataclip is used.
- X-Axis Field
- You can choose which of the options returned from the Dataclip you want to display in the widget along the x-axis. The drop-down menu shows the attributes returned in the
SELECT
statement, in this examplebook_purchases
andmonth
, so we'll choosemonth
.
If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example above for data that is returned in the correct way. - Y-Axis Field
- You can choose which of the options returned from the Dataclip you want to display in the widget along the y-axis. The drop-down menu shows the attributes returned in the
SELECT
statement, in this examplebook_purchases
andmonth
, so we'll choosebook_purchases
.
If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example above for data that is returned in the correct way.
Display Geck-O-Meter data
The Geckometer widget allows you to display data from a Heroku Dataclip URL. An example is shown below:
Requirements for the Geck-O-Meter widget
The current item field expects just numeric data from the column selected in field. Minimum and maximum values are automatically calculated based on all items in this column.
Configuration for the Geck-O-Meter widget
From the configuration menu in the widget, specify the following options:
- URL
- The URL to your Heroku Dataclip, provided in the browser address field or from the share option.
Dataclip .csv URL structure
https://data.heroku.com/dataclips/###.csv?access-token=###-###-###-###-###">
Note
Versions are ignored, and just the current Dataclip is used.
- Field
- You can choose which of the options returned from the Dataclip you want to display in the widget. The drop-down menu shows the attributes returned in the
SELECT
statement, in this examplebook_purchases
andmonth
, so we'll choosebook_purchases
.
If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example above for data that is returned in the correct way. - Current Item
- Specifies which data value in the field chosen to treat as the current item, the main white number in the picture above.
Display line chart data
The Line Chart widget allows you to display data as a line chart from a Heroku Dataclip URL. An example is shown below:
Requirements for the line chart widget
The x-axis takes alpha-numeric data, and the y-axis takes just numeric data. Date/time values used in the x-axis are automatically detected and are automatically formatted for you in the line chart. Non-date/time data can also be used but isn't re-formatted in any way.
Configuration for the line chart widget
From the configuration menu in the widget, specify the following options:
- URL
- The URL to your Heroku Dataclip, provided in the browser address field or from the share option.
Dataclip .csv URL structure
https://data.heroku.com/dataclips/###.csv?access-token=###-###-###-###-###">
Note
Versions are ignored, and just the current Dataclip is used.
- X-Axis Field
- You can choose which of the options returned from the Dataclip you want to display in the widget along the x-axis. The drop-down menu shows the attributes returned in the
SELECT
statement, in this examplebook_purchases
andmonth
, so we'll choosemonth
.
If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example above for data that is returned in the correct way. - Y-Axis Field
- You can choose which of the options returned from the Dataclip you want to display in the widget along the y-axis. The drop-down menu shows the attributes returned in the
SELECT
statement, in this examplebook_purchases
andmonth
, so we'll choosebook_purchases
.
If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example above for data that is returned in the correct way.
Display bar data
The bar chart widget allows you to display data in a widget as a bar chart from a Heroku Dataclip URL. An example is shown below:
Requirements for the bar chart widget
The x-axis takes alpha-numeric data, and the y-axis takes just numeric data..
Configuration for the bar chart widget
From the config menu in the widget, specify the following options:
- URL
- The URL to your Heroku Dataclip, provided in the browser address field or from the share option.
Dataclip .csv URL structure
https://data.heroku.com/dataclips/###.csv?access-token=###-###-###-###-###">
Note
Versions are ignored, and just the current Dataclip is used.
- X-Axis Field
- You can choose which of the options returned from the Dataclip you want to display in the widget along the x-axis. The drop-down menu shows the attributes returned in the
SELECT
statement, in this examplestate
andtotal_sales
, so we'll choosestate
.
If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example below for data that is returned in the correct way. - Y-Axis Field
- You can choose which of the options returned from the Dataclip you want to display in the widget along the y-axis. The drop-down menu shows the attributes returned in the
SELECT
statement, in this examplestate
andtotal_sales
, so we'll choosetotal_sales
.
If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example below for data that is returned in the correct way.
The example above uses the following SQL:
SELECT
details->'state' as state,
SUM(quantity * purchase_items.price) as total_sales
FROM
purchase_items,
purchases,
users
WHERE
users.id = purchases.user_id
AND purchases.id = purchase_items.purchase_id
AND details ? 'state'
GROUP BY 1
ORDER BY 1;
This produces the following data:
state | total_sales |
Florida | 10778.27 |
Illinois | 7916.60 |
New York | 6283.70 |
South Carolina | 8769.53 |
Virginia | 6566.08 |
Display RAG data
The RAG (Red, Amber, Green) widget allows you to display three data values from a Heroku Dataclip url. An example is shown below:
Requirements for the RAG widget
The widget expects numeric data for the column specified in the config under field. It displays up to 3 values, with null values being included in that count but not being shown.
Configuration for the RAG widget
From the configuration menu in the widget, specify the following options:
- URL
- The URL to your Heroku Dataclip, provided in the browser address field or from the share option.
Dataclip .csv URL structure
https://data.heroku.com/dataclips/###.csv?access-token=###-###-###-###-###">
Note
Versions are ignored, and just the current Dataclip is used.
- Field
- You can choose which of the options returned from the Dataclip you want to display in the widget. The drop-down menu shows the attributes returned in the
SELECT
statement, in this examplegender
andtotal_sales
, so we'll choosetotal_sales
.
If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example below for data that is returned in the correct way. - Display
- Choose to display the first or last 3 entries from the column indicated by field. e.g. in this example, only three values are returned, so we'll choose first 3 entries.
- State 1 label
- Optional label shown against the first state, indicated in red in the above picture.
- State 2 label
- Optional label shown against the first state, indicated in amber in the above picture.
- State 3 label
- Optional label shown against the first state, indicated in green in the above picture.
- Reverse colors
- Show the colors in the reverse order, so GAR (Green, Amber, Red).
The example above uses the following SQL:
SELECT
coalesce(details->'sex', 'unknown') as gender,
SUM(quantity * purchase_items.price) as total_sales
FROM
purchase_items,
purchases,
users
WHERE
users.id = purchases.user_id
AND purchases.id = purchase_items.purchase_id
GROUP BY 1;
This produces the following data:
gender | total_sales |
Unknown | 105298.68 |
M | 109714.74 |
F | 104051.56 |
Display funnel widget data
The Funnel widget allows you to display up to 8 values from a Heroku Dataclip URL. An example is shown below:
Requirements for the funnel widget
The widget expects numeric data for the column specified in the config under field. It displays up to 8 values, with null values being included in that count but not being shown.
Configuration for the funnel widget
From the configuration menu in the widget, specify the following options:
- URL
- The URL to your Heroku Dataclip, provided in the browser address field or from the share option.
Dataclip .csv URL structure
https://data.heroku.com/dataclips/###.csv?access-token=###-###-###-###-###">
Note
Versions are ignored, and just the current Dataclip is used.
- Field
- You can choose which of the options returned from the Dataclip you want to display in the widget. The drop-down menu shows the attributes returned in the
SELECT
statement, in this examplegender
andtotal_sales
, so we'll choosetotal_sales
.
If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example below for data that is returned in the correct way. - Display
- Choose to display the first or last 8 entries from the column indicated by 'field'. e.g. in this example, only three values are returned so we'll choose first 8 entries.
- Funnel 1 label
- Optional label shown against the first funnel. Unknown in our example.
- Funnel 2 label
- Optional label shown against the first state, indicated in amber in the above picture.
- Funnel 3 label
- Optional label shown against the second funnel. Male in our example
- Funnel 4 label
- Optional label shown against the fourth funnel.
- Funnel 5 label
- Optional label shown against the fifth funnel.
- Funnel 6 label
- Optional label shown against the sixth funnel.
- Funnel 7 label
- Optional label shown against the seventh funnel.
- Funnel 8 label
- Optional label shown against the eighth funnel.
- Reverse colors
- Show the colors in the reverse order, so larger numbers are red instead of green, and smaller ones are green instead of red.
- Hide percentages
- Percentages are shown as conversions between stages in the funnel. In our example we aren't displaying data as stages so we'd set the Hide percentages to Yes.