Unlock Your Database and Create a Business Intelligence Radar in One Move

Christopher Peters / July 15, 2014

One of the best aspects of Zapier's company culture is that the whole team does customer support. On top of that, we eat our own dog food—the Zapier team relies on many Zaps. Together, these practices give each teammate a stake in our customers' concerns, and they often expose us to new and valuable ways to use Zapier for business.

Recently, I noticed a customer had hooked up a MySQL database to Zapier. The customer was using it to run a query and automatically send the results to another app.

This gave me an idea for a database query automation of our own: each day, grab a conversion rate from the past 24 hours and send it to our chat app.

Business models can be thought of as having a series of conversion rates. If you're a real estate agent, you'll be concerned with the conversion rate of leads to deals started, deals started to deals closed, and deals closed to another new deal (repeat business). As Zapier's data scientist, it's my job to track and analyze these rates.

Currently, we're focusing on the conversion rate defined by a person visiting our site to signing up for a free trial. I want a method that will allow me to keep the team informed in a way that's not bothersome and fairly automatic (they don't need to remember).

The customer I was helping gave me the idea to connect our postgreSQL data warehouse to Slack, our team chat app.

To do this, I first composed a query to count today's signups and unique visitors.

SELECT
    extract(hour from x.zap_ran_at) as hour_ran,
    extract(mins from x.zap_ran_at) as min_ran,
    x.zap_ran_at,
    ROUND(CAST(x.signups as float) / CAST(y.visitors as float) * 100, 3)
        AS conversion_rate
FROM
    (
        SELECT
            COUNT(ID) AS signups, max(timestamp) as zap_ran_at
        FROM
            users
        WHERE
            DATE (TIMESTAMP) = CURRENT_DATE
    ) x
JOIN (
    SELECT
        COUNT ( DISTINCT visitor_id ) AS visits
FROM
    visits
WHERE
    DATE (TIMESTAMP) = CURRENT_DATE
AND user_id IS NULL
) y ON 1 = 1

Zapier doesn't yet offer scheduling (but clearly we need to!), so I had to include the hour and minute that the query ran as columns returned by the query: hour_ran, min_ran needed to include the hour and minute that the query ran as columns returned by the query: hour_ran, min_ran.

This query could be dropped right into the Zap below, but I created a postgreSQL view to make things neater, hence SELECT * FROM todays_conversion_rate is just my own alias for the query above (both work equally well).

See how I filter on the hour and minute columns so that the filter only passes once a day? In this case, the query that runs in the 3:15 p.m. to 4:15 p.m. EDT time frame will pass through the filters while all others are filtered rather than being sent to Slack (our database is set to the UTC time zone). I actually could have returned the raw timestamp since Zapier filters do operate on timestamps, I didn't realize that when I broke out the hours and minutes from the time the query was run (postgreSQL has the common CURRENT_TIME function).

Finally, I send the results to our #general channel on Slack.

The result is that the team gets to see signup conversion rates once a day, every day (and those results are searchable for quick access).

If you don't use Slack—or another team chat app—you could share this important daily stat via email, mobile push notification or shared Google Calendar or Sheets doc.