How to use QueryClips as an alternative to Heroku Dataclips

You're either a developer or a product manager, or in a role where you need access to your application or reporting database.

In your company's early days, you relied on tools like Heroku Dataclips to perform rudimentary reporting. You hacked your way through SQL and/or got help from your developers to get your job done.

But, as your company has grown, you've had to migrate away from the Heroku service. With that migration comes a decision: what data tool to migrate to? How much to pay for it? What features do you need?

If you're looking for a seamless, low-cost, usable alternative to Heroku Dataclips, QueryClips may just be what you're looking for. It's inexpensive. It lets you connect to your Postgres or MySQL database no matter where it's hosted. It's secure. It runs queries in read-only transactions, and allows exporting to CSV, JSON, and Google Sheets. It lets you add sanely-named databases (not opaquely-named add-ons). And you can invite your colleagues.

Over 150 customers use QueryClips to get their jobs done every day, quickly and easily.

How it works

It takes less than a minute to get up and running. First, sign up:

Then, add your database

Now write (and iterate on) your SQL

Finally, (optionally) export your results to share with colleagues or to insert into slide decks:

That's it!

Examples

Customers use QueryClips for all sorts of data queries. For example:

A later-stage company uses QueryClips connected to a privately hosted Postgres database to run daily user engagement and retention reports
An education company uses QueryClips connected to their Heroku Postgres database to run daily roster reports
An e-commerce company uses QueryClips connected to their MySQL database hosted on Amazon RDS to build reports for revenue growth
An early stage biotech startup uses QueryClips connected to Postgres on Google Cloud to generate data for their investor update slide decks

These are just a couple examples of the kinds of uses that QueryClips actively sees today.

How to use QueryClips to build automatic weekly user growth reports

Tracking and reporting on user growth is one of the primary use cases of any analytics setup. The same is true for QueryClips, albeit specially designed for bootstrappers or SQL-savvy PMs. In this tutorial, I'll walk you through how I use QueryClips to report on user growth in a couple of different ways.

Setting up the database

I'll assume you haven't yet set up a database in QueryClips. After signing up, you will need to create a new database. Navigate to "Databases", and click the "New Database" button. You'll see this screen:

Find your database connection string. If you're using Heroku Postgres, you can simply run heroku config | grep DATABASE_URL and paste the output here. If you're using another provider, such as RDS, you can piece together your connection string by following the instructions on that page.

For example, my connection string looks like this: postgres://u76piasdtcfd2evk:p0fc6aa4bb690d1834mcb37ed41f2c82d92920028ec6dc0f77c119a30ba64881ed4@ec2-54-89-11-2.compute-1.amazonaws.com:5432/d5jtheohto5djtf. Yours may look slightly different, but the form will be the same: dialect://username:password@host:port/dbname.

NOTE: Your credentials are securely stored in QueryClips using 128-bit AES encryption. No plaintext credentials exist anywhere. All connections use SSL if your database supports it, and all queries are run inside read-only transactions to prevent any accidental data manipulation or loss. You can rest easy knowing your data is safe.

After copying your connection string and clicking "Save Credentials", you should be in business.

Finding your users

I'll suppose you have a users table with a date time column called created_at that is set once, when the user first signs up. Your names may be different - simply replace the names with the appropriate replacements from your database.

I like to build SQL queries iteratively in QueryClips, so I will begin by simply listing out each user along with the date they registered.

Click "Create QueryClip" and you'll see this screen:

Pick a descriptive name for this exploration, for example "Weekly user growth". And, make sure to select your new database in the dropdown on the right (mine happens to be called "My Database" in this example.)

Enter the following SQL to begin our exploration:

SELECT
  users.id,
  users.created_at
FROM users
ORDER BY users.created_at DESC
LIMIT 100
;

Of course, you'll need to replace any table or column names with the appropriate names from your schema.

Then, click "Run QueryClip". After a second, you should see your data results:

Extracting the week

That's great, but we don't just want a roster of users (although this may be a useful report to have). What we really want is for QueryClips to show us just the week the user joined. So, we'll use the DATE_TRUNC function that Postgres provides. We pass the created_at column and the week argument like so (and use the AS clause to specify a name for this new column):

SELECT
  users.id,
  DATE_TRUNC('week', users.created_at) AS week
FROM users
ORDER BY week DESC
LIMIT 100
;

For readability, I've updated the ORDER BY clause to specify this new column. Update the SQL in QueryClips and click "Run QueryClip". You should see something like this:

Note the week column. Postgres has replaced the created_at date with the date of the start of that week.

NOTE FOR MYSQL USERS: MySQL doesn't have a DATE_TRUNC function, but it does have EXTRACT. You can use it like this: EXTRACT(WEEK FROM users.created_at) in the example above.

Rolling up

Now that we've got a list of our users and which week they signed up for our application, we can count them in SQL using the COUNT function and GROUP BY clause. We'll COUNT the number of users (COUNT(users.id)) and GROUP BY the week (GROUP BY WEEK). This is the resulting SQL:

SELECT
  COUNT(users.id),
  DATE_TRUNC('week', users.created_at) AS week
FROM users
GROUP BY week
ORDER BY week DESC
LIMIT 100
;

Now you've got a count of users by week.

Sharing the data

Now you might want to share this data with a colleague. If you simply want to share the data with them, you can copy the URL in the upper right of the QueryClip and paste it to them in an email or slack.

Sharing with Google Sheets

I prefer sharing with Google Sheets, because it lets me also provide a visualization if I'd like. To do this, I click "Export" and then select "Google Sheets":

After authorizing access, I see that QueryClips has created a new linked Google Sheet to the right of my SQL:

Clicking, I see:

I'll highlight the "week" column and format it as a date:

Voila:

But I want a chart. So I'll highlight the last 12 months of data (including the headers):

And then "Insert", "Chart":

Google decided on a line chart:

But I really want a column chart (because I like the way that looks better). So I click the Chart type on the right and select "Column chart":

This looks pretty good (although I wish the graph showed stronger growth!) I'm definitely ready to share this with my colleague. I can do so by clicking on the "Share" button and typing their email address:

Automatically updating

QueryClips will run this report every 30 minutes and automatically update the results. So there's no need to really ever go back and manually update the query again (unless your schema changes, or you tweak the way you'd like to display the data).

For many teams, they go one step further and copy this visualization into a Google Slides presentation for discussion during a meeting or event. Google will let you choose to automatically link that visualization with the original, which is really handy.

Week over week growth

Let's pretend several weeks pass. I'd like to know how each week performs against each other week. To accomplish this, we'll need a couple new concepts. The first thing we do is to wrap our original query as a subquery:

SELECT
    count,
    week
FROM
    (SELECT
      COUNT(users.id),
      DATE_TRUNC('week', users.created_at) AS week
    FROM users
    GROUP BY week
    ORDER BY week DESC
    LIMIT 100) weekly

;

Then we add a new column using the LAG function in our outer SELECT: LAG(count) OVER (ORDER BY week) AS last_week

SELECT
    count,
    week,
    LAG(count) OVER (ORDER BY week) AS last_week
FROM
    (SELECT
      COUNT(users.id),
      DATE_TRUNC('week', users.created_at) AS week
    FROM users
    GROUP BY week
    ORDER BY week DESC
    LIMIT 100) weekly
;

This gets us close:

But what we really want is a percent change. Make an adjustment in the LAG clause:

SELECT
    count,
    week,
    (count / 
        (NULLIF
            (LAG
                (count) OVER (ORDER BY week), 0))::float) AS percent_growth
FROM
    (SELECT
      COUNT(users.id),
      DATE_TRUNC('week', users.created_at) AS week
    FROM users
    GROUP BY week
    ORDER BY week DESC
    LIMIT 12) weekly
;

It looks complicated but what we're simply doing is checking for a NULL value, then converting to a float to give us a percent growth rate. We also adjusted the LIMIT to only show the latest 12 months of data. Here's what the result looks like:

Now after some more tweaking in Google Sheets, we've got a much nicer visualization:

Sending this to my inbox every morning

If you're like me, you like to stay plugged into changes to this data on a daily basis. You can use the Daily Digest feature of QueryClips to make sure you get a copy of this report in your email inbox every morning:

Wrapping up

We covered the following in this tutorial:

Writing queries iteratively with QueryClips
Selecting records, rolling up with a COUNT and GROUP BY
Linking to Google Sheets to create visualizations and sharing
The LAG function
Using the daily email digest

Hopefully this gives you a good overview of what's possible with QueryClips when you integrate with Google Sheets.

What to pay attention to when implementing web analytics in Google Sheets

In response to this question on Quora

I find that many early stage companies are using Google Sheets to track their customers, perform growth and retention reports, and much more. If you're used to using a sophisticated data pipeline, this may be a little bit shocking. However, I firmly believe it can be the right choice. It's fast to set up. You don't need to know how to code. Your excel skills are plenty.

In fact, in the pre-product-market-fit stage, you can make the argument it's irresponsible to spend much time at all building a "real" analytics system.

And, depending on your use case, this system could last you quite awhile. If you’re successful and grow, you’ll eventually have to implement a slightly more sophisticated stack. I’ve used both approaches numerous times!

I have a couple of suggestions for those using Google Sheets this way:

Put the cleaned, reconciled, consolidated data into a database before exporting it to a Google Sheet. This isn’t for today — it’s for tomorrow when you ask / hire a developer to build out something more sophisticated, if you ever need it.
Keep the data as clean as you can. Use a consistent format for things like dates, and store them at the highest granularity you can. For example, don't mix 1/12 and 1/12/2017 and Jan 12th in the same column. Make them all /12/2017, or better yet 01/12/2017 (note the leading zero). Format the whole column and forget about it. You can always extract things like day of week, year, etc into other columns if necessary using formulas.
Include a unique identifier for each row. If you export from a database, you can just use the primary key. Same reasoning — it makes later technical work much simpler.
Practice separation of concerns. More sheets in the workbook isn't a bad thing - it can ensure that if/when you do need to put that data into a database, you can do it in an iterative fashion.
When you eventually build your fancy analytics system, you will need to sanity check the new system with the existing google sheet. Keep the data around. Don't delete it -- just hide it from users who may need their habits changed.

Google Sheets and Excel can take you quite far in your analytics. Don't assume you need a sophisticated solution right away.

QueryClips Blog