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:firstname.lastname@example.org:5432/d5jtheohto5djtf. Yours may look slightly different, but the form will be the same:
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:
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 (
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:
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
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
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:
Hopefully this gives you a good overview of what's possible with QueryClips when you integrate with Google Sheets.