Skip to content

Update funnels data model

Surabhi Suman requested to merge 463491-update-funnels-sql into master

What does this MR do and why?

The code changes the way the funnel data is calculated. Instead of using a window function to calculate the time between steps, it now uses a recursive query to calculate the time between each step. This change should improve the accuracy of the funnel data, especially for funnels with a large number of steps. Additionally, the code now uses a more efficient way to calculate the number of users who completed each step of the funnel.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

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/analytics-section/product-analytics/devkit!166 (merged) in devkit to update the cube schema definition.

  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"
}

Related to #463491 (closed)

Edited by Surabhi Suman

Merge request reports