Display Heroku Dataclip metrics in Geckoboard

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.Data_Clip_Number_widget.png

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.

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 the SELECT statement, in this example book_purchases and month, so we'll choose book_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:Data_Clip_Trend_Chart_widget.png

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.

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 example book_purchases and month, so we'll choose month.

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 example book_purchases and month, so we'll choose book_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:Data_Clip_Geck-o-meter_widget.png

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.

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 example book_purchases and month, so we'll choose book_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:Data_Clip_Line_Chart_widget.png

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.

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 example book_purchases and month, so we'll choose month.

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 example book_purchases and month, so we'll choose book_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:Data_Clip_Bar_Chart_widget.png

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.

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 example state and total_sales, so we'll choose state.

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 example state and total_sales, so we'll choose total_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:Data_Clip_RAG_widget.png

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.

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 example gender and total_sales, so we'll choose total_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:Data_Clip_Funnel_widget.png

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.

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 example gender and total_sales, so we'll choose total_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.
Was this article helpful?

Awesome! 👍  Thanks so much for your feedback!