Reach out with questions, bugs, or suggestions. We'll respond ASAP. Your message is sent! We'll respond via email shortly.

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

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?

Mode Analytics

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

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.

Mode Analytics

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.

Mode Analytics

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.

Mode Analytics

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.

Mode Analytics

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.

Mode Analytics

Next Topic: Funnels