> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cube.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Implementing funnel analysis

> Configure multi-step conversion funnels with the packaged Funnels helper in JavaScript data models for product and marketing analytics.

<Info>
  This functionality only works with data models written in JavaScript, not YAML.
  For more information, check out the [Data Modeling Syntax][ref-modeling-syntax]
  page.
</Info>

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 comes pre-packaged with a standard
funnel package.

```javascript theme={"dark"}
// 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 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.

<a href="#" class="accordion-trigger" id="show-sql-accordion">
  {" "}

  Show Funnel's SQL
</a>

<div class="accordion" id="show-sql-accordion-body">
  ```sql theme={"dark"}
  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'
          ) <= (
            view_product_events.t :: timestamptz AT TIME ZONE 'America/Los_Angeles'
          ) + interval '1 day'
      )
      select
        user_id,
        first_step_user_id,
        step,
        max(t) t
      from
        (
          SELECT
            view_product_user_id user_id,
            view_product_user_id first_step_user_id,
            t,
            'View Product' step
          FROM
            joined_events
          UNION ALL
          SELECT
            purchase_product_user_id user_id,
            view_product_user_id first_step_user_id,
            t,
            'Purchase Product' step
          FROM
            joined_events
        ) as event_steps
      GROUP BY
        1,
        2,
        3
    ) AS purchase_funnel
  WHERE
    (
      purchase_funnel.t >= '2018-07-01T07:00:00Z' :: timestamptz
      AND purchase_funnel.t <= '2018-07-31T06:59:59Z' :: timestamptz
    )
  GROUP BY
    1
  ORDER BY
    2 DESC
  LIMIT
    5000
  ```
</div>

## Funnel parameters

### userId

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

```javascript theme={"dark"}
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.

```javascript theme={"dark"}
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.

```javascript theme={"dark"}
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.

```javascript theme={"dark"}
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
`many_to_one` relationship, 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:

```javascript theme={"dark"}
cube(`PurchaseFunnel`, {
  joins: {
    Users: {
      relationship: `many_to_one`,
      sql: `${CUBE}.first_step_user_id = ${Users.id}`
    }
  },

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

## Using funnels

Cube is based on
[multidimensional analysis](https://en.wikipedia.org/wiki/Multidimensional_analysis)

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.

## 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][ref-schema-ref-preaggs-origsql].

It is best to use [partitioned rollups][ref-partitioned-rollups] to cache the
steps instead. Add one to the `PurchaseFunnel` cube as follows:

```javascript theme={"dark"}
cube(`PurchaseFunnel`, {
  extends: Funnels.eventFunnel({
    // ...
  }),

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

[ref-modeling-syntax]: /docs/data-modeling/syntax

[ref-partitioned-rollups]: /docs/pre-aggregations/using-pre-aggregations#time-partitioning

[ref-schema-ref-preaggs-origsql]: /reference/data-modeling/pre-aggregations#original_sql