Heap Connect is only available for customers on a paid plan. To upgrade, contact your Customer Success Manager or sales@heap.io.
This section will go through Heap Connect queries that enable you to conduct funnel analysis by measuring all types of funnels. Funnels are fundamental to understanding any flow through your site, from onboarding to filling out forms, to virtually any step users are expected to take within your product.
In this section, we will cover:
- Building a basic funnel
- Modifying the date range
- Limiting the funnel to one session
- Increasing flexibility (steps can occur in any order)
- Conversion rate over time (week over week)
For a complete list of SQL queries located in this section, review Common Queries. If you would like help building a funnel, please reach out to support@heap.io.
The Basic Funnel
The funnel below can be used to mirror the funnel in Heap’s dashboard. It calculates the number of users who have completed Step 1 within the time frame, then the number of users who have completed Step 2, and then Step 3. It is both sequential and time-constrained, though there are many modifications you may wish to make, as shown below.
Breaking it Down
The goal of this query is to get both the counts and the percentage that make it from step to step.
E1:
This table is selecting all the users and the first time they completed event 1 during the given time range.
E2:
This selects all the users who did Step 1 and the first time they did Step 2 between the time they did Step 1 and the end date.
E3:
This selects all the users who did Step 1 one and Step 2 and the first time they did Step 3 between the time they did Step 2 and the end date.
The Counts and Percentages:
This query first creates a table with the user ID, the time of each step was completed, and 1 or 0 based on whether or not the user completed each step. It calculates the total number of users who completed each step (each step value). Then the query calculates the conversion rate between Step 1 and Step 2, Step 2 and Step 3, and the overall conversion rate.
Putting it all together, and you a table with the following results:
| Step 1 Total | Step 2 Total | Step 3 Total | Step 1 → Step 2 % | Step 2 → Step 3 % | Step 1→Step 3 % |
To take advantage of Mode’s custom visualizations, we tweaked our query to return:
| Step 1 | Count |
| Step 2 | Count |
| Step 3 | Count |
Funnels Grouped by an Event-level Property
Each event has a set of properties ranging from attributes Heap captures automatically, like referrer, UTM parameters, and device type, as well as any properties you set using snapshots, heap.addEventProperties, or heap.track.
In order to view the break down in conversion rate based on these event-level properties, only a couple of modifications need to be made.
First, select the event that contains the event-level property you would like to analyze. In this example, this event-level property is a property of event 1. This query selects the utm_source (the event-level property) for the first event so that the event property can be included in the GROUP BY clause of the funnel.
In the funnel, include the event-level property in the SELECT statement and GROUP BY clause. You can also join this on the users table (JOIN main_production.users on main_production.users.user_id=e1.user_id ) to analyze your conversion rates based on user-level properties as well.
This funnel is valuable for many reasons. For example, eCommerce sites can send the number of items in the cart as an event-level property for the beginning of the checkout process. Using this query, you can analyze the correlation between conversion rates and number of items in the cart.
Restricting the Funnel to one Session
In this funnel, user ID and session ID are aligned in order to ensure that funnel spans across one session. There are a couple of tweaks needed in each step of the query.
E1:
First we need to gather the user ID, session ID, and event time for step one data. Make sure you are grouping by both user ID and session ID so that all of the users’ sessions that occur in the time frame are included in the funnel.
E2 and E3:
Next, you want to modify the second part of this query to join step 2 and step 3 data based on both user ID and session ID. Adding an and statement within the join allows us to join this data successfully. Make sure you continue to include session ID in your SELECT and GROUP BY clauses.
Conversion Rate:
To calculate the conversion rate, we first join these three event tables and calculate the number of users who have completed steps one, two, and three in each month. Then we are going to calculate the conversion rate for each month and group by month.
Rolling Conversion Rates
This report gives the user a window to complete the funnel and tracks how your conversion changes over time.
Time to Conversion / Average Time to Event
Time can be an interesting metric to analyze. The time it takes for a user to make their first purchase from the date first seen, or the time it takes a user to fill out a form from the first field to submit, or the time between two purchases can be measured and optimized. You can analyze which behaviors, user properties, or first touch properties are associated with faster conversion rates.
This example measures the time it takes users to purchase an item but can be easily adapted to measure a slew of different events, and segmented by a variety of user and event-level properties.