Little-Big Data With Heroku Dataclips

Unlock the hidden power of your Heroku postgres database.

Every startup in the Universe today claims to be “data-driven.” That buzz word is right up there with “html5” and “lean startup” in terms of things companies say, but don’t actually follow through on. I wanted to share an easy way to actually be data driven without having to shell out $$$ for a data warehousing service, or invest the time to build your own reporting app and charts.

Instead, all we need to know is a little bit of SQL. In fact, with a little patience, you’ll be able to get the BD team running their own reports in no time at all.

Prerequisites

  • Your app is using Postgresql
  • Your PG database is hosted by Heroku
  • You have some questions you want to ask your database.

Why Dataclips?

There are a few routes to take when it comes to early data at a startup. You can pull reports directly from the console when you need them. This is a great option early on, but requires everyone to have access to the production db, and knowledge of sql.

You can build your own admin stats dashboard. Another great options, but not very flexible, and very time consuming up front. Even under the best conditions, you’re looking at a day or two of work, which would be better spent on user-facing features.

Finally, you can go enterprise and shell out big money for a data warehouse solution. There are plenty of upsides to this option, but besides expense, it can eat up a lot of time to get things set up initially.

Instead, I opt for data clips. They allow for ad-hoc reports when ever someone decides they want to know something. They take very little effort to set up. Most importantly though, they are easy to share with the rest of the team, so everyone can stay in the loop on key metrics. All without having to trust anyone to not accidentally drop all your production tables.

My First Query

We’ll start by visiting Heroku Dataclips. Once there, click the “Create Dataclip” button at the top right of the page. Now it’s time to write some SQL.

If your app is like most RoR apps, you’ve got two or three models of importance. Usually “users” and whatever it is your app does. For this first query, I want to start with something easy, like figuring out how many total users I have.

select count(*)
from users;
my-first-dataclip.sql hosted with ❤ by GitHub

Drop the sql query above into the text area on the new data clip page, select the database you want to query, and click “create clip.” Once you do that, the next screen you’ll see is the result of that query.

Something A Little More Complex

Ok. We know how many users we have, but what about our new user growth rate? Like any good startup, we should figure out how many new users are signing up each week. Let’s create a time-series table so we know exactly how many new users are created each week.

SELECT date_trunc('week', "created_at") AS "Week", count(*) AS "New User Signups"
FROM Users
GROUP BY "Week"
ORDER BY "Week" DESC;

As before, drop that query into a new data clip, select the database, and run it. You should see the table below the query on the next page, sorted most recent week first. Are those user counts growing each week? Good!

Keep An Eye On It

Wicked. Now you have a table that counts user sign ups by week, as well as total users on your app. Not the most insightful metrics in the world, but it’s a really good start. What are our options for letting the team know?

  1. Link right to the dataclip - This allows for up-to-the-minute tracking, since you can reload the query as data changes.
  2. Download the CSV for some Excel tom-foolery - Build some fancy graphs to impress your potential investors.
  3. Grab the iFrame and embed it in your admin area - Build your own stats dashboard with way less work, and way more flexibility.

Boom. It’s really that easy.