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 ALLstatement 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.
