Announcing our $3.4M seed round 🚀 Read more →

Auditing a Customer's Usage of Your Product

You have a call with a prospective customer in 10 minutes. You need a quick glance at how their team has used your product.

Whether you’re in product, sales, support, or a founder, you’re going to face this challenge daily.

Why SQL?

If you’ve built a modern data stack, you're already ETL-ing your product data into one database/warehouse. SQL gives you the fastest 360° view of your customer.

SQL you can copy / paste

As long as you have an events table with timestamp, user_id, and event_name columns, you can use the queries below with little modification.

select
 team_id,
 count(1) as action_count,
 count(distinct user_id) as user_count,
 -- see how many actions were taken in your product by how many team members

 min(time) as first_event,
 max(time) as last_event,
 round(((extract(epoch from max(time)::timestamp) - extract(epoch from min(time)::timestamp)) / 86400.00)::numeric,4) as days_between_first_last_events,
 -- did they briefly use your product and disappear? or did usage span many days?

 count(1) filter(where name = 'Add Image') as add_image,
 count(1) filter(where name = 'Delete Image') as delete_image,
 count(1) filter(where name = 'Auto-Organize Canvas') as auto_organize_canvas,
 count(1) filter(where name = 'New Canvas') as new_canvas,
 count(1) filter(where name = 'Share Canvas') as share_canvas
 -- did they thoroughly explore your product? add event_names for any feature that matters

from events
where time between '{{start_date}}' and '{{end_date}}'
 and team_id = {{team_id}}
group by 1;

Sample Output:

| team_id | action_count | user_count | first_event         | last_event          | days_between_first_last_events | add_image | delete_image | auto_organize_canvas | new_canvas | share_canvas |
|---------|--------------|------------|---------------------|---------------------|--------------------------------|-----------|--------------|----------------------|------------|--------------|
| 48137   | 572          | 42         | 2020-05-01 12:23:32 | 2020-05-14 23:50:24 | 13.4770                        | 203       | 99           | 177                  | 55         | 14           |

Try it yourself?

Run this template against our sample database that mirrors real startup data. See the connection credentials, then connect in PopSQL.


Bonus Content

  • The UNION ALL statement makes it easy to compare results for 2 different organizations concurrently.
select ... -- hidden to simplify query
from events
where time between '{{start_date}}' and '{{end_date}}'
 and team_id = {{team_id}}
group by 1

UNION ALL

select ... -- hidden to simplify query
from events
where time between '{{start_date}}' and '{{end_date}}'
 and team_id = {{team_id_2}}
group by 1;

Sample Output:

| team_id | action_count | user_count | first_event         | last_event          | days_between_first_last_events | add_image | delete_image | auto_organize_canvas | new_canvas | share_canvas |
|---------|--------------|------------|---------------------|---------------------|--------------------------------|-----------|--------------|----------------------|------------|--------------|
| 48137   | 572          | 42         | 2020-05-01 12:23:32 | 2020-05-14 23:50:24 | 13.4770                        | 203       | 99           | 177                  | 55         | 14           |
| 46100   | 277          | 53         | 2020-05-01 04:38:05 | 2020-05-14 22:37:01 | 13.7493                        | 139       | 90           | 18                   | 19         | 3            |

This side-by-side comparison is helpful during success calls with customers. You can compare their usage of your product against a comparable customer. Example: “one of our customers is similar to you, and has had great success using XYZ feature. Can we talk through how this feature could help you?”

Note: The query above uses Query Variables, which make it extremely easy to pass parameters into queries in-line.

Ready for a modern SQL editor?