Update /product_analytics/funnels API to return Snowplow-compatible SQL
Overview
- Currently the REST API endpoint
GET /api/v4/projects/:project_id/product_analytics/funnels
returns generated SQL to run but it assumes that the database schema has been generated by Jitsu. - Since we're transitioning to use the snowplow schema, this endpoint will soon return invalid SQL for columns that do not exist.
- This change, alone, should be behind a default-off feature flag so it can be enabled once the switch to snowplow has been completed. (FYI @dennis)
Implementation plan
- Update the output from this endpoint according to the plan below:
Before
[
{
"name": "completed_purchase",
"sql": " SELECT\n (SELECT max(utc_time) FROM jitsu) as x,\n windowFunnel(3600)(utc_time, doc_path = '/page1.html', doc_path = '/page2.html', doc_path = '/page3.html') as step\n FROM gitlab_project_20.jitsu\n",
"steps": [
"doc_path = '/page1.html'",
"doc_path = '/page2.html'",
"doc_path = '/page3.html'"
]
}
]
After
[
{
"name": "completed_purchase",
"sql": " SELECT\n (SELECT max(derived_tstamp) FROM snowplow_events) as x,\n windowFunnel(3600)(derived_tstamp, page_urlpath = '/page1.html', page_urlpath = '/page2.html', page_urlpath = '/page3.html') as step\n FROM gitlab_project_20.snowplow_events\n",
"steps": [
"page_urlpath = '/page1.html'",
"page_urlpath = '/page2.html'",
"page_urlpath = '/page3.html'"
]
}
]
This existing API is behind a default-off flag with no real-world users so there's no breaking changes to be aware of.
Note: Double-check how snowplow stores paths: does it have the leading forward slash?
/cc @dennis @jheimbuck_gl
Edited by Max Woolf