Display Heroku Data Clip metrics in Geckoboard

Here's a guide to setting up the data on Data Clips and configuring the Heroku Postgres Data Clip widgets on Geckoboard.

Number Widget

The number widget allows you to display numeric data in a widget from a Heroku Data Clip url. An example is shown below:

 

Data_Clip_Number_widget.png

 

Requirements:

The widget expects numeric data for the column specified in the configuration under 'field'.

Configuration:

From the configuration menu in the widget, specify the following options:

URL - The URL to your Heroku Data Clip, provided in the browser address field or from the share option. e.g. https://dataclips.heroku.com/vgyygvzqtezwpmwpcmmjlluamjlk. Note that versions are ignored, and just the current Data Clip is used.

Field - You can choose which of the options returned from the Data Clip 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.

Example Data Clip:
https://dataclips.heroku.com/vgyygvzqtezwpmwpcmmjlluamjlk

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

 

Trend Chart Widget

The trend chart widget allows you to display a line chart from a Heroku Data Clip URL. An example is shown below:

 

Data_Clip_Trend_Chart_widget.png

Requirements:

The x-axis field will take alpha-numeric data but the y-axis expects just numeric data.

Configuration:

From the configuration menu in the widget, specify the following options:

URL - The URL to your Heroku Data Clip, provided in the browser address field or from the share option. e.g. https://dataclips.heroku.com/vgyygvzqtezwpmwpcmmjlluamjlk. Note that versions are ignored, and just the current Data Clip is used.

X-Axis Field - You can choose which of the options returned from the Data Clip 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 Data Clip 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.

Geck-O-Meter Widget

The Geckometer widget allows you to display data from a Heroku Data Clip URL. An example is shown below:

Data_Clip_Geck-o-meter_widget.png

Requirements:

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:

From the configuration menu in the widget, specify the following options:

URL - The URL to your Heroku Data Clip, provided in the browser address field or from the share option. e.g. https://dataclips.heroku.com/vgyygvzqtezwpmwpcmmjlluamjlk. Note that versions are ignored, and just the current Data Clip is used.

Field - You can choose which of the options returned from the Data Clip 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.

Line Chart Widget

The Line Chart widget allows you to display data as a line chart from a Heroku Data Clip URL. An example is shown below:

Data_Clip_Line_Chart_widget.png

Requirements:

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:

From the configuration menu in the widget, specify the following options:

URL - The URL to your Heroku Data Clip, provided in the browser address field or from the share option. e.g. https://dataclips.heroku.com/fykmkrwjtjozzokqrwbpxquxcpfw. Note that versions are ignored, and just the current Data Clip is used.

X-Axis Field - You can choose which of the options returned from the Data Clip 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 Data Clip 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.

Example Data Clip:
https://dataclips.heroku.com/fykmkrwjtjozzokqrwbpxquxcpfw

 

Bar Chart Widget

The bar chart widget allows you to display data in a widget as a bar chart from a Heroku Data Clip URL. An example is shown below:

Data_Clip_Bar_Chart_widget.png

 

Requirements:

The x-axis takes alpha-numeric data, and the y-axis takes just numeric data..

Configuration:

From the config menu in the widget, specify the following options:

URL - The URL to your Heroku Data Clip, provided in the browser address field or from the share option. e.g. https://dataclips.heroku.com/xqzzcwmlubhblavdipydzzqmlmbm. Note that versions are ignored, and just the current Data Clip is used.

X-Axis Field - You can choose which of the options returned from the Data Clip 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 Data Clip 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.

Example Data Clip:
https://dataclips.heroku.com/xqzzcwmlubhblavdipydzzqmlmbm

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

RAG Widget

The RAG widget allows you to display three data values from a Heroku Data Clip url. An example is shown below:

 

Data_Clip_RAG_widget.png

Requirements:

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:

From the configuration menu in the widget, specify the following options:

URL - The URL to your Heroku Data Clip, provided in the browser address field or from the share option. e.g. https://dataclips.heroku.com/zmqbdstcpbvcqhurltpslfwzslvq. Note that versions are ignored, and just the current Data Clip is used.

Field - You can choose which of the options returned from the Data Clip 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 second state, indicated in amber in the above picture.

State 3 label - Optional label shown against the third state, indicated in green in the above picture.

Reverse colors - Show the colors in the reverse order, so GAR.

Example Data Clip:
https://dataclips.heroku.com/zmqbdstcpbvcqhurltpslfwzslvq

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

 

Funnel Widget

The Funnel widget allows you to display up to 8 values from a Heroku Data Clip URL. An example is shown below:

Data_Clip_Funnel_widget.png

Requirements:

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:

From the configuration menu in the widget, specify the following options:

URL - The URL to your Heroku Data Clip, provided in the browser address field or from the share option. e.g. https://dataclips.heroku.com/zmqbdstcpbvcqhurltpslfwzslvq. Note that versions are ignored, and just the current Data Clip is used.

Field - You can choose which of the options returned from the Data Clip 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 second funnel. Male in our example.

Funnel 3 label - Optional label shown against the third funnel. Female in our example.

...

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?
👍

Thank you for your feedback!