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.

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 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:

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 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:

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 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:

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 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:

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 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:

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 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:

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