Visualizing Heroku App Data with Google Sheets

By Matthew Rathbone on February 03, 2017

postgres analytics database heroku google sheets psql

This article was co-authored by Anton Antonenko

dashboard

In my last post I showed you how to take data out of Heroku Postgres and move it to Google Sheets. Today I’ll show you some more advanced tools that you can use to explore and report on your data.

The tools I present here are in both Google Sheets and in Microsoft Excel, with some slight menu differences.

Set-up

For the purposes of this tutorial I’ll be working with some fake (and simplified) e-commerce data. In this example our e-commerce store has grown to the point where we need to monitor some key statistics.

To start, we want information on best-selling items and a breakdown of sales per item. We’ll accomplish this using pivot tables and charts on top of some simple transaction data. Just to keep it straightforward, I haven’t included transaction dates.

Alt text

With this example I assume that you are able to export data from Heroku Postgres into Google Sheets.

Building Our First Pivot Table

Bulding a pivot table is like performing a GROUP BY SQL query but with a visual designer to customize how the result appears. After telling the pivot table which columns you want to use for grouping, you tell it where to display each group (row or column), then how to aggregate other fields (e.g. sum, count, average). It also allows you to add total and sub-total rows and columns.

Pivot tables work on top of data that is already in the spreadsheet. In our example we’ve already done the work to capture nice clean data from our Postgres database, so our end result should be pretty clean.

For my example I will be producing a simple pivot table that summarizes the total amount of revenue per product sold, and the total number of products sold. The end result will look like this:

Pivot table example

Pivot Table Set-Up

Select the data range containing the transaction data and navigate to Data -> Pivot Table in the menu. Google Sheets will first prompt you for the data range you want to use. In this case we want to make sure this references the range containing our transaction data.

Alt text

A new sheet will be created for you and you’ll see an empty pivot table with an open (and slightly confusing-looking) report editor.

Alt text

Building the Pivot Table

Using the report editor can seem complicated, but don’t worry - it’s not actually that hard to get the hang of.

Let’s work with our data and make a table that will help us monitor item sales, and thus identify our best-selling item.

In the report editor we can add data items as either Rows, Columns, or Values (which is an aggregate value). We can also affect the whole report with a filter.

Let’s keep things simple and group by a single row, using only price as an input to our value fields.

When adding a field to either row or column you are going to group by that field. When adding to value you should be adding a numerical value that you can aggregate in some way. So I can add price to the value section.

Alt text

By default the report editor value will aggregate values using SUM. I will use this to provide a revenue total, but seeing the total number of sales is also useful, so I’ll add price back in the value section one more time but aggregate with COUNT instead. This will tell us the total number of items sold.

Alt text

Notice these are added to the pivot table in real-time. Neat!

Finally, we can also adjust the sort and order by sections to put things in the order we want.

Pivot Table Wrap-Up

That’s it! Now we have a pivot table which presents a simplified view of data and can be easily updated by updating the data behind it or adding new rows to the range.

Charts

We’ve summarized our data to make it manageable, so now we can add some visualizations on top of the pivot table.

First, select the first two columns of the pivot table (e.g. items & SUM of price) and click Insert -> Chart. Make sure not to include the total rows in the chart!

Alt text Alt text

Let’s move on and visualize item totals. We do basically the same thing by selecting the two columns we’d like in the chart and then just letting the chart wizard do the rest.

Pro tip: If you want, you can actually change the order of items in the values section of the report editor.

Alt text

Let’s arrange this stuff nicely and we now have a simple dashboard we can use to monitor top-selling items.

Alt text

Refreshing the Data

You can manually refresh both pivot tables and charts and update their ranges to look at new data (see below).

Alt text

This isn’t very efficient though, so next time we’ll walk through how to do this automatically using Heroku Scheduler.

Wrap Up

Hopefully by reading this article you have a good sense of how to export data from Heroku Postgres to Google Sheets and make some basic visualizations using pivot tables and charts. Stay tuned for some more analytics-on-Heroku posts!

Still Writing SQL in a Terminal?

Beekeeper is a free and full featured SQL IDE with query autocomplete, error detection, dataset previews, and result visualization. We support SparkSQL, Hive, Postgres and more!

Create your account »

Matthew Rathbone bio photo

Matthew Rathbone

CEO of Beekeeper Data. British. Data nerd.

Big Data Blog Email Twitter Github Stackoverflow