How to turn an SQL report into a daily hipchat notification
Keep your KPIs peeled
Having up to date numbers for whatever you are working on can be the difference between success and failure. All startups at some point will want to have regular reporting for things like conversion metrics, sign ups and revenue.
Usually, that takes the form of a “dashboard”, or a regular email. If you go the dashboard route then you have to put it somewhere where you’ll remember to check it, often actually on a screen in your office. With email updates the danger is that you don’t open them and they clutter up your inbox.
Today I did a tiny experiment to see if I could use Wrangler’s “subscribe” feature (it sends you an SQL report to your inbox every morning) to send my “Daily Important Numbers” to Hipchat.
We use Hipchat for keeping track of everything we’re doing. Chatting to each other, but also getting notifications about things that happen with our products. It helps me be generally aware of what’s going on.
Get your KPIs in Hipchat every day
Today I set up Hipchat so that we see important analytics data each day. Ambient analytics, if you will. Much of this works even if you’re not using Wrangler too.
It was pretty easy to do, so here’s a how-to if you want to do the same.
Sign up to Wrangler and connect a Postgres database as a data source.
If you’re new to Wrangler, you’ll need your Postgres connection details. We recommend *not* using your production database for this. There are instructions in-app for setting up a follower or reporting database.
Create a report
Wrangler is a tool for writing SQL reports and sharing them with your team. So create a simple report and call it “Daily snapshot for Hipchat”. Here’s mine, which is a report of new activity for Attending.io—a tool for organising free events:
Yours will be different, but there are some key things here. I’m going for a single line report. The result will just be a unique identifier for the day “snapshot-15-02-2014” and three numbers. Simple!
The crucial thing is to generate an ID that will be unique each day and won’t change in the mean time. Here, my ID is based on the date. And name it “id” for Zapier.
Turn on “subscribe via email”
At the top right of the report is an option to subscribe via email to the report. It runs the report at 8am GMT every day, which is basically our trigger. This is a little experiment, so if I get a good reaction to this I’ll consider adding something slicker. For now, you’ll also receive the report via email - set up a rule to skip your inbox?
Next, grab the public JSON URL for your report. Top right, under “export”:
Then, head over to Zapier
We’ll use Zapier’s “web hook” feature to do the HipChat notifications for us. Zapier is a great utility for prototyping things, for making something occur in one system if something happens in another, for instance. Once you know it’s right, you could write your own daily script to do this for you… Personally I like having this stuff in one place and avoiding the “snowflake server” problem.
Create a new “zap”, with “Web Hook”, and “Retrieve Poll” as the input, and “HipChat” and “Send Message” as the output. You’ll need to set up an API key on HipChat for your organisation, which is pretty straight forward.
Use the JSON url you copied as the source of the web hook and leave the rest of the options blank.
You’ll then have the option to test out the notifications. Check that all is well, and then turn it on.
At around 8am GMT every day you should now get a notification in the HipChat room of your choice!
If you want to make it weekly, just set the “ID” field of your report to be the week number of the year, or round the date to the beginning of the week. Zapier only reports new rows when it sees a new ID.
There is a lot you could do with this! Any fact that you have in Postgres can now be reported to your team really easily. You could extend this to report multiple rows at a time, or link to new records by generating valid URLs using string concatenation and using Hipchat’s support for <A> tags.
If you like this idea, do let me know (I’m @stef on Twitter) because I can see this could be a useful thing to offer as part of Wrangler itself, and indeed I’m considering making a Wrangler Zapier integration so you could do this all inside Zapier in future.