Heap SQL: Funnel and Conversion Rate Analysis
Funnels are not just restricted to understanding marketing page or checkout process conversions. They 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 next section, we will cover:
- Building a basic funnel
- Modifying the date range
- Limiting the funnel to one session
- Increasing the flexibility (can occur in any order)
- Conversion rate over time (week over week)
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, and 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 firsts 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 caculates 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,
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, e-commerce 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. Mode Analytics E1: First we need to gather a list of 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 and and statement within the join allows us to join this data successfully. Make sure you continue to include session ID in your
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 first field to submit, or event 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.