Skip to content

Update funnels data model

Surabhi Suman requested to merge ss/funnels_sql into main

What does this MR do and why?

The cube schema definition for funnel analysis has been updated. The new schema uses a window function to calculate the steps in the funnel, and it also adds a filter for the date dimension. Additionally, new tests have been added to verify that the funnel schema returns the correct results when filters are applied.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

How to set up and validate locally

  1. Pull from gitlab-org/gitlab!155290 (merged) and restart GDK

  2. Update any existing funnel or create a new funnel for a project already onboarded with product analytics.

    name: Path Funnel
seconds_to_convert: 3600
steps:
  - name: view_page_1
    target: '/page1.html'
    action: 'pageview'
  - name: view_page_2
    target: '/page2.html'
    action: 'pageview'
  - name: view_page_3
    target: '/page3.html'
    action: 'pageview'

  3. Verify SQL saved in clickhouse by running

    SELECT JSONExtractString(contents, 'schema') AS sql FROM ${appId}.funnel_schemas
WHERE name = 'path_funnel'

  4. Ensure newly created dynamic cube is reflected in /v1/meta endpoint.

  5. Query newly created cube on /v1/load endpoint with measures and dimensions and filter(optional) and verify if filtered data is correct.

    POST /cubejs-api/v1/load HTTP/1.1
Host: localhost:4000
Accept: */*
Accept-Language: en-GB,en-US;q=0.9,en;q=0.8
Authorization: 
content-type: application/json

{
    "query": {
        "measures": [
            "path_funnel.count"
        ],
        "filters": [
            {
                "member": "path_funnel.date",
                "operator": "inDateRange",
                "values": [
                    "2023-02-07",
                    "2023-02-08"
                ]
            },
            {
                "member": "path_funnel.step",
                "operator": "equals",
                "values": [1]
            }
        ],
        "dimensions": [
            "path_funnel.step"
        ]
    },
    "queryType": "multi"
}

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to: gitlab-org/gitlab#463491 (closed)

Edited by Surabhi Suman

Merge request reports