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.

QueryClips Blog