Skip to main content

Funnel

The funnel reporting type is built for customer event data. If you have your user behavior data in your data warehouse, you can understand if your users are performing a series of events with funnels. For example, let's say that you have pageview, add_to_basket and transaction events stored in a database and you want to calculate a metric called cart abandonment rate (i.e. people who added an item to their basket but didn't make any purchase).

Most of the BI tools let you ask Segmentation questions but it's not easy to build funnel metrics since it doesn't play well with the customer behavioral data. If you're familiar with SQL, you can write a complex SQL query that calculates the cart abandonment rate and share it with your product people. While you can parametrize the SQL query in your BI tool, the product people won't have much flexibility to compare the different user segments, define different funnel steps, or filter by an event property.

That's why Metriql provides different reporting types for different use-cases. Let's see a typical funnel query:

steps:
  - dataset: pageview
    filters: 
      - {dimension: campaign, operator: 'equals', 'winter_sale'}
  - dataset: add_to_basket
  - dataset: transaction
dimension:
window: {value: 1, type: hour} # the maximum time period in between the events
connector: user_id # optional, the default is user_id mapping

Metriql compiles the funnel query above to an SQL query if you're using PostgreSQL as follows:

➕Click to see SQL

The compiled SQL queries use advanced SQL features such as window functions that are not that easy to read. It's primarily because behavioral analytics doesn't fit in SQL linear algebra. However; product people often need to run ad-hoc funnel queries and Metriql tries to reduce the time data analysts need to deal with report requests. Most of the companies that have modern data stacks have their customer event data in the data warehouse. The new generation data warehouses are able to run these queries efficiently.

Metriql makes use of advanced SQL features such as match_recognize and HyperLogLog++ depending on your adapter and tries to build the most efficient query for the funnel.

window:#

Set the maximum duration between two steps. window.type can be either minute, hour, or day. The default is unbounded.

excluded_steps:#

If you want to exclude some of the users who did specific events, you can use this property as follows:

steps:
- dataset: pageview
- dataset: add_to_basket
excluded_steps:
- step:
    dataset: transaction
  start: 1

connector:#

By default, Metriql uses user_id mapping to contruct funnel queries, if you want to use another connector that buckets the data into individual segments and see if the buckets are performing the steps, use connector. It usually makes sense if you're analyzing the user events within a single session id or device id.

approximate:#

If the data warehouse supports approximation, you can enable the approximation using this parameter. Currently, BigQuery and Snowflake support it.

strictly_ordered:#

If the data warehouse supports strictly ordered funnel queries you can use this parameter. Since the relational SQL model doesn't fit in an event data model, most of the data warehouses don't support strictly ordered funnel queries except for the ones that support match_recognize. Currently, only Snowflake supports this parameter.

dimension:#

You can break down funnels into different groups using a dimension in a funnel step. It's useful if you're comparing different user segments. Here is an example:

steps:
- dataset: pageview
- dataset: add_to_basket
dimension:
- step:
    step: 1
    dimension: campaign
Edit this page