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.
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!
Customers use QueryClips for all sorts of data queries. For example:
These are just a couple examples of the kinds of uses that QueryClips actively sees today.
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.
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:
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.Google Sheets and Excel can take you quite far in your analytics. Don't assume you need a sophisticated solution right away.