Funnels: a series of events that lead users towards a defined goal

Funnels represent a series of events that lead users towards a defined goal. Funnel analysis is an approach commonly used in product, marketing and sales analytics.

Regardless of the domain, every funnel has the following traits:

  • The identity of the object moving through the funnel – e.g. user or lead
  • A set of steps, through which the object moves
  • The date and time of each step
  • The time to convert between steps

Since funnels have a pretty standard structure, they are good candidates for being extracted into reusable packages. Cube.js comes pre-packaged with a standard funnel package.

// First step is to require the Funnel package
const Funnels = require(`Funnels`);

cube(`PurchaseFunnel`, {
  extends: Funnels.eventFunnel({
    userId: {
      sql: `user_id`
    },
    time: {
      sql: `timestamp`
    },
    steps: [{
      name: `view_product`,
      eventsView: {
        sql: `select * from events where event = 'view_product'`
      }
    }, {
      name: `purchase_product`,
      eventsView: {
        sql: `select * from events where event = 'purchase_product'`
      },
      timeToConvert: '1 day'
    }]
  })
});

Cube.js will generate an SQL query for this funnel. Since funnel analysis in SQL is not straight forward, the SQL code itself is quite complicated, even for such a small funnel.

Show Funnel’s SQL

SELECT
  purchase_funnel.step "purchase_funnel.step",
  count(purchase_funnel.user_id) "purchase_funnel.conversions"
FROM
  (
    WITH joined_events AS (
      select
        view_product_events.user_id view_product_user_id,
        purchase_product_events.user_id purchase_product_user_id,
        view_product_events.t
      FROM
        (
          select
            user_id user_id,
            timestamp t
          from
            (
              select
                *
              from
                events
              where
                event = 'view_product'
            ) e
        ) view_product_events
        LEFT JOIN (
          select
            user_id user_id,
            timestamp t
          from
            (
              select
                *
              from
                events
              where
                event = 'purchase_product'
            ) e
        ) purchase_product_events ON view_product_events.user_id = purchase_product_events.user_id
        AND purchase_product_events.t >= view_product_events.t
        AND (
          purchase_product_events.t :: timestamptz AT TIME ZONE 'America/Los_Angeles'
        ) 

Funnel parameters

userId

A unique key to identify the users moving through the funnel.

  userId: {
    sql: `user_id`
  }

nextStepUserId

In the situation where user_id changes between steps, you can pass a unique key to join two adjacent steps. For example, if a user signs in after having been tracked anonymously until that point in the funnel, you could use nextStepUserId to define a funnel where users are tracked by anonymous ID on the first step and then by an identified user ID on subsequent steps.

const Funnels = require(`Funnels`);

cube(`OnboardingFunnel`, {
  extends: Funnels.eventFunnel({
    userId: {
      sql: `id`
    },
    time: {
      sql: `timestamp`
    },
    steps: [{
      name: `View Page`,
      eventsView: {
        sql: `select anonymous_id as id, timestamp from pages`
      }
    }, {
      name: `Sign Up`,
      eventsView: {
        sql: `select anonymous_id as id, user_id, timestamp from sign_ups`
      },
      nextStepUserId: {
        sql: `user_id`
      },
      timeToConvert: '1 day'
    }, {
      name: `Action`,
      eventsView: {
        sql: `select user_id as id from actions`
      },
      timeToConvert: '1 day'
    }]
  })
});

time

A timestamp of the event.

  time: {
    sql: `timestamp`
  }

steps

An array of steps. Each step has 2 required and 1 optional parameters:

  • name (required) - Name of the step. It must be unique within a funnel.
  • eventsView (required) - Events table for the step. It must contain userId and time fields. For example, if we have defined the userId as user_id and time as timestamp , we need to have these fields in the table we’re selecting from.
  • timeToConvert (optional) - A time window during which conversion should happen. Set it depending on your funnel logic. If this is set to 1 day , for instance, it means the funnel will include only users who made a purchase within 1 day of visiting the product page.
  steps: [{
    name: `purchase_product`,
    eventsView: {
      sql: `select * from events where event = 'purchase_product'`
    },
    timeToConvert: '1 day'
  }]

Joining funnels

In order to provide additional dimensions, funnels can be joined with other cubes using user_id at the first step of a funnel. This will always use a belongsTo relationship, so hence you should always join with the corresponding user cube. Here, by ‘user’ we understand this to be any entity that can go through a sequence of steps within funnel. It could be a real web user with an auto assigned ID or a specific email sent by an email automation that goes through a typical flow of events like ‘sent’, ‘opened’, ‘clicked’, and so on. For example, for our PurchaseFunnel we can add a join to another funnel as following:

cube(`PurchaseFunnel`, {
  joins: {
    Users: {
      relationship: `belongsTo`,
      sql: `${CUBE}.first_step_user_id = ${Users}.id`
    }
  },

  extends: Funnels.eventFunnel({
    // ...
  })
});

Using funnels

Cube.js is based on multidimensional analysis and operates on the measures and dimensions level. Thus, all funnel data is represented via a set of measures and dimensions.

Funnel-based cubes have the following structure:

Measures

  • conversions - Count of conversions in the funnel. The most useful when broken down by steps . It’s the classic funnel view.
  • conversionsPercent - Percentage of conversions. It is useful when you want to inspect a specific step, or set of steps, and find out how a conversion has changed over time.

Dimensions

  • step - Describes funnels’ steps. Use it to break down conversions or conversionsPercent by steps, or to filter for a specific step.
  • time - time dimension for the funnel. Use it to filter your analysis for specific dates or to analyze how conversion changes over time.

In the following example, we use the conversions measure along with the steps dimension to display a classic bar chart showing the funnel’s steps.

Performance considerations

Funnel joins are extremely heavy for most modern databases and complexity grows in a non-linear way with the addition of steps. However, if the cardinality of the first event isn’t too high, very simple optimization can be applied: originalSql pre-aggregation.

Just add it to Funnel cube as follows:

cube(`PurchaseFunnel`, {
  extends: Funnels.eventFunnel({
    // ...
  }),

  preAggregations: {
    main: {
      type: `originalSql`
    }
  }
});

In this case, the heavy Funnel join will be materialized and stored as a table, which will save significant amount of time for subsequent Funnel queries.

In the case where the cardinality of the first event is too high for originalSql pre-aggregation, partitioned rollups can be used.

1 Like