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.
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.
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:
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.
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.
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.
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:
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.
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:
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:
We covered the following in this tutorial:
COUNT
and GROUP BY
LAG
functionHopefully this gives you a good overview of what's possible with QueryClips when you integrate with Google Sheets.