Edit this page

Funnels

Funnels are representing a series of events that lead users towards a defined goal. It's commonly used in product, marketing and sales analytics.

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

  • identity of the object moving through the funnel, ex. user or lead;
  • set of steps, through object moves;
  • date and time of each step;
  • time to convert between steps;

Since funnels have pretty standard structure, they are good candidates for being extracted into reasable packages. Cube.js goes 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 for this funnel. Since funnel analysis in SQL is not straightforward and easy, 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'
        ) 

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

  userId: {
    sql: `user_id`
  }

A unique key to join two adjacent steps when source of user id changes when moving from one step to another one. For exampe you can use it to build funnel that tracked by anonymous id at the first step and then by 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'
    }]
  })
});

A timestamp of the event.

  time: {
    sql: `timestamp`
  }

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 a table we're selecting.
  • timeToConvert (optional) - A time window for conversion to happen. Set it, depending on your funnel logic. If set to 1 day, for instance, it means the funnel will include only users who made a purchase within 1 day after visiting the product page.
  steps: [{
    name: `purchase_product`,
    eventsView: {
      sql: `select * from events where event = 'purchase_product'`
    },
    timeToConvert: '1 day'
  }]

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

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

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

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:

  • 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 was changing over time.

  • 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 was changing over time.

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

Funnel joins are extremely heavy for most of modern DBs and complexity grows non-linear with addition of steps. However in case of 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 heavy Funnel join will be materialized and stored as a table which will save significant amount of time for subsequent Funnel queries.

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