Heap SQL: Aggregate Metrics
In this section you’ll find a variety of queries that focus on aggregate metrics for your site. The value of these queries increases as you segment them by user personas and the same logic can also be applied to calculate more specific statistics.
The visualizations may take a few seconds to load, but you can view a complete list of SQL queries located in the guide look here, if you have any questions about please reach out to firstname.lastname@example.org.
Top N Events
The top N events shed light into how users are interacting with your app. Looking at this list generates a lot of questions: Why are users clicking this button? Why are users viewing this page? Are these two actions correlated? Does the high event count come from users success within the product, or is this caused by confusion?
You can modify the
WHERE statement to adjust the date range. You can include the date in
SELECT statement and
GROUP BY clause to measure how most frequently performed actions change over time. You can also adjust the number of events you are looking at by modifying the
Average Time on Site
This query is primarily beneficial to you if you have a content site, and there aren’t a lot of user interactions to capture while reading, and avid readers don’t always comment or share. Although a proxy for engagement, time on site, or drastic changes in time on site can cue you in to investigate how a user is engaging with your product. You can tweak this query to analyze average time between events or break it down into different segments of users to get a better understanding of your site’s usage.
Average Events Per Session
Likewise, the average events per session can give you insight into aggregate behavior. This query can also be modified to analyze behavior across segments and over time, provoking questions such as: Does this number change with a product launch or tweaks to the on-boarding flow? Do different types of users interact differently? etc. Changes in the average can prompt investigation into what is really happening within your app.
Average Events per Session Over Time
To modify this query to calculate the average number of events per session over the past year or past six months, simply update the
WHERE statement to include the time range and add a
GROUP BY clause to address the granularity.
Average by User Property
To do this, we join the all events table on the user table and add a
GROUP BY clause based on the User Properties you want to use for segmentation.
Average Sessions Per User
The average number of sessions per user can be used in the same way as average events per session, and can be modified in the same manner. To find the average number of sessions per user, simply divide the number of unique sessions by the number of unique users across the time period.