Easy Slack SQL Reporting (Scripted Behind the Scenes)

We use Slack at Scripted. We use it for the normal use - communication, but also use as our reporting hub. 

One of our favorite tools that helps us is SQLBot.co, a simple service that lets you query your reporting databases and push the results into Slack.

Let me show you with an example. One of the most important things we offer at Scripted to our customers is our pool of freelance writers. Better writers produce better writing and create happier customers. Makes sense.

We like to watch our stats daily (if not more often) at Scripted, and the easiest way to do that is to have reports automatically publish into Slack. Once there we can discuss the results and take action if necessary.

Here’s a query I wrote (yes, I still pretend to code) that tells us the number of new writer applicants we get by day:

SELECT
to_char(w.created_at AT TIME ZONE '-5', 'YYYY-MM-DD') as day,
COUNT(DISTINCT w.id) as writer_accts_created
FROM users w
WHERE w.user_type = 'writer'
GROUP BY to_char(w.created_at AT TIME ZONE '-5', 'YYYY-MM-DD')
ORDER BY to_char(w.created_at AT TIME ZONE '-5', 'YYYY-MM-DD') DESC
LIMIT 30

I use Postico as my SQL editor, and here’s what the results look like in its interface:

Great, so how do I now get this info into Slack?  I could use some complicated ETL process or expensive reporting tool, but why do that when a simpler tool exists.  Enter SQLBot.co. 

Getting results into Slack is as easy as setting up my DB connection, then copying and pasting my SQL into SQLBot and setting a few preferences.  Here's what the new report screen looks like filled out for this report.

Now that I've set up the command to call the report in Slack, all I need to do is hop over and call it.  Here's what it looks like in Slack:

So there you have it.  Simple, easy, fast Slack SQL reporting.  I can schedule this report to post daily or weekly, which makes things automatic, and means I won't forget to do it.  

---

Published by Doug Breaker on Friday, August 9, 2019 in Software & Technology.