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.
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.
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 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.
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.
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
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
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.
Notice these are added to the pivot table in real-time. Neat!
Finally, we can also adjust the
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.
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.
SUM of price) and click
Insert -> Chart. Make sure not to include the total rows in the chart!
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.
Let’s arrange this stuff nicely and we now have a simple dashboard we can use to monitor top-selling items.
Refreshing the Data
You can manually refresh both pivot tables and charts and update their ranges to look at new data (see below).
This isn’t very efficient though, so next time we’ll walk through how to do this automatically using Heroku Scheduler.
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!