HogQL Insights

[thmsobrmlr]

HogQL Insights

Current state of the HogQL conversion for insights and moving from filters-based insights to query-based insights.

What are we doing and why?

We are rewriting all our insights in HogQL, instead of raw ClickHouse SQL, which allows us to implement performance improvements and feature toggles (e.g. PoE modes) on this intermediate layer. This also allows us to expose the query to the end user, so they debug issues themselves or adapt queries to less frequent use cases.

In addition to the changes on the SQL layer we also change the way we store the insight configuration. Currently we have a mixin-based filters format (flat key-value structure) that became hard to maintain and doesn't allow reusage of sub-parts. The new query format (nested json) should allow copy-pasting parts and nesting "sources" in other queries to allow re-using the results throughout PostHog.

High-level plan of remaining steps

1. Remove all filters from the frontend and use the frontend-side filterToQueryNode function to convert all api responses to the new query format (when fetching) and the queryNodeToFilter function to convert them back to filters for saving/duplicating/etc.
2. Toggle the feature flag query-based-insights-saving, that then sends insights back with query, instead of filters for saving/duplicating/etc.
3. Optional: Use the backend side filter_to_query function in the insights serializer to return a query from the backend (just this endpoint, as this is only for testing the filter_to_query function works as expected).
4. Optional: Any changes we want to make to the query schema e.g. camel casing key that are still snake cased.
5. If everything works, migrate the insights backend side (iterate over them and use the backend side filter_to_query function to replace filters with query).
6. Migrate other entities that have insights e.g. activity log or notebooks.
7. Convert experiments to HogQL.
8. Convert cohorts to HogQL.
9. Cleanup.

Remove frontend side dependency on filters

We can get rid of filters frontend side first by using the backend side filter_to_query function to return only queries from insights (and any other places that might return filters) and adapting the frontend so that it only handles queries. For saving insights we can use the frontend side queryNodeToFilters function to send finally send filters to the backend.

• [x] Use the backend side filter_to_query function to return only queries, not filters https://github.com/PostHog/posthog/pull/21945
• [ ] Fix any places that might still rely on filters:
  • [ ] Make ActionFilter and entityFilterLogic based on series, not actions and events
  • [ ] Remove filter based helpers from frontend/src/scenes/insights/sharedUtils.ts e.g. isTrendsFilter or isFilterWithDisplay
  • [ ] Remove usage of filtersToQueryNode, queryNodeToFilters, etc. in as many places as possible
  • [ ] Remove all usages of the cleanFilter function
  • [ ] Make experiments use HogQL queries
  • [ ] Migrate: activity log, insights, notebook nodes
  • [ ] Cleanup: Remove all getQueryBasedInsightModel

After migrating to backend side filters

For insights

• [ ] replace InsightModel in subscriptionsLogic.test.ts
• [ ] replace InsightModel in funnelDataLogic.test.ts
• [ ] replace InsightModel in insightVizDataLogic.test.ts
• [ ] replace InsightModel in trendsDataLogic.test.ts
• [ ] convert all mocked insights in tests, stories etc. to be query based

For the activity log

For notebooks

Experiments backend

Experiments use the PA code backend side to generate trends/funnel results. We should swap out the legacy implementation for the HogQL one there as well.

• [ ] Replace funnels in experiments backend side

Finalize query schema

At some point we want to run a migration to replace filters with queries. After that migration it will be harder to make changes to the query schema, meaning we should clean up the schema as good as we can now.

Unfortunately this got complicated by the fact that notebooks already save insights as queries and not filters. Thus they need additional handling in https://github.com/PostHog/posthog/blob/master/frontend/src/scenes/notebooks/Notebook/migrations/migrate.ts and we need to come up with a way to clean up tech debt there. The queries are stored both in the notebook nodes and in the activity log from which the user can go back in time.

Some proposed changes to the current query schema:

• [ ] Camel case all properties
  • [ ] Camel case dateRange properties date_from and date_to
  • [ ] Camel case breakdownFilter properties
  • [ ] Camel case math properties in entities
  • [x] Camel case and fix hidden_legend_indexes / hidden_legend_keys Currently the legend items can be hidden by index or by key depending on the insight and a bug prevents the hidden entries from being saved. We should agree on a single way to hide the entries and fix saving them with the query.
    • [x] https://github.com/PostHog/posthog/issues/20655
  • [ ] Camel case aggregation_group_type_index
  • [ ] Remove/fix breakdown_histogram_bin_count in trends filter properties
  • [ ] Replace legacy entities in retention insights with new Event/ActionNodes

Related bugs

Trends

• [ ] https://github.com/PostHog/posthog/issues/21683 multiple selectors (frontend side issue, not HogQL)
• [ ] Persons modal doesn't open for null breakdowns (also never did in the old insights).
• [ ] Null in list if breaking down by group property
• [ ] Problem with null/other breakdowns and dashboards (1)

Funnels

• [ ] https://posthoghelp.zendesk.com/agent/tickets/9814 funnel - performance issue
• [ ] https://posthoghelp.zendesk.com/agent/tickets/13065
• [ ] persons modal does not open

Retention

• [ ] https://github.com/PostHog/posthog/issues/21700 retention data error

Cleanup

• [ ] Update mocks to query based mocks

Make it flippin' amazing

• [ ] Make sure all the bugs are fixed that we've been deferring
  • [ ] https://github.com/PostHog/posthog/issues/21270
  • [ ] https://github.com/PostHog/posthog/issues/21269
  • [ ] Caching of "load more breakdown values" should work, see https://posthog.slack.com/archives/C045L1VEG87/p1706285253686739
  • [ ] Bug: The user paths insight should not disable the persons modal in card view
  • [ ] Bug: Clicking save before the query completes -> changes should get saved
  • [ ] Follow ups from https://github.com/PostHog/posthog/pull/20089
  • [ ] Changing the title of a hogql insight and clicking “save” on the title (not the whole query) restarts the query calculation. Since the query itself hasn’t changed, I don’t think it should do that (edited) https://posthog.slack.com/archives/C045L1VEG87/p1710855088205499
  • [ ] The "load more breakdown values" button doesn't make sense when we have 25 values and are limiting to 25 values -> expand the breakdown limit in this case https://posthog.slack.com/archives/C045L1VEG87/p1706285253686739
  • [ ] Fix issues with "Leave insight? Changes you made will be discarded" https://posthog.slack.com/archives/C0368RPHLQH/p1712589416025629
  • [ ] Follow up on the commented out tests after CH 23.12 upgrade https://github.com/PostHog/posthog/pull/21437
• [ ] Implement all the new cool things users ask for https://github.com/PostHog/meta/issues/200
• [ ] Finish the HogQL pending tasks https://github.com/PostHog/meta/issues/81
• [ ] Do that little BI thing https://github.com/PostHog/meta/issues/157
• [ ] Implement a better way to cache persons modals: Previously we had a random "cache_key", so that a calculation for given filters would only run once, but refresh when the insights was reloaded. I'd like to store the current timestamp with a cached item an use that for invalidating responses.

[thmsobrmlr]

Consolidation 1.3.2024

Step 1: Make it work

https://github.com/PostHog/posthog/pull/17295 & https://github.com/PostHog/posthog/pull/17414 & https://github.com/PostHog/posthog/pull/17440

To have a scaffold for other queries and to enable discussion on implementation details, we want to have the lifecycle query ported over to HogQL from frontend to the backend and back i.e.

• [x] The frontend sends a LifecycleQuery node to the backend
• [x] The backend takes this query node and converts it to a HogQL query
• [x] Lifecycle test account filters
• [x] Global filters
• [x] The frontend displays the lifecycle insight from this response

Step 2. Make it good

• [x] The result is cached and sent to the frontend https://github.com/PostHog/posthog/pull/17483 @thmsobrmlr
• [x] Rewrite the Antlr parser in a faster language @Twixes
• [x] Persons modal: Get the persons behind a data point by sending the query and specifying the data point https://github.com/PostHog/posthog/pull/17470 @mariusandra
• [x] Save as cohort in persons modal https://github.com/PostHog/posthog/pull/18982
• [x] Why is the lifecycle insight showing different numbers?
• [x] Notebooks and insights work well together.
• [x] Open the SQL behind an insight in a new view
• [x] Expand the persons modal into a new PersonsQuery https://github.com/PostHog/posthog/pull/19198
• [x] Any changes we want to make to the query nodes, filters, etc? Did lots of capitalization.
• [x] Breakdown selection within the modal (make a new InsightActorsQueryOptions node that works similar to InsightActorsQuery, but just returns the different fields and their options for the frontend actors modal)

Step 3. Make it complete

• [x] PersonsQuery
  • [x] Make it fast (...enough)
  • [x] Make the ClickHouse-powered PersonsQuery the default view for persons pages. (Waiting for 2024 before rollout)
• [x] Lifecycle @mariusandra https://github.com/PostHog/posthog/issues/19640
  • [x] Feature complete with current implementation
  • [x] Results tested to match current for our team
  • [x] Released to PostHog's team
  • [x] Results tested to match current ones for all other teams
  • [x] Released to all users
• [x] Retention @webjunkie https://github.com/PostHog/posthog/issues/19639
  • [x] First version https://github.com/PostHog/posthog/pull/18831
  • [x] Persons modal
  • [x] Persons modal CSV export & save as cohort
  • [x] Released to PostHog's team
  • [x] Released
• [x] Trends @Gilbert09 https://github.com/PostHog/posthog/issues/19637
  • [x] All other math operators
  • [x] Breakdown
  • [x] Sampling
  • [x] Formulas
  • [x] Series filtering
  • [x] Graph display types (pie chart, cumulative line graph, etc)
  • [x] Compare to previous period
  • [x] to_persons_query
  • [x] Smoothing
  • [x] Actions
  • [x] Breakdown controls (other on/off & limit)
  • [x] Released to PostHog's team
  • [x] Breakdown in actors modal
• [x] Stickiness @Gilbert09 https://github.com/PostHog/posthog/issues/19642
  • [x] To start
  • [x] Date ranges
  • [x] Multiple series
  • [x] "Compare to previous period"
  • [x] Actors query
  • [x] Aggregations
  • [x] Unit tests
  • [x] Internal release to PostHog team
  • [x] Release to all users
• [x] Paths https://github.com/PostHog/posthog/issues/19641
  • [x] To start
• [x] Funnels https://github.com/PostHog/posthog/issues/19638
  • [x] Conversion steps
    • [x] "Sequential" https://github.com/PostHog/posthog/pull/19951
    • [x] Breakdowns https://github.com/PostHog/posthog/pull/20153
    • [x] "Strict order" https://github.com/PostHog/posthog/pull/20211
    • [x] "Any order" https://github.com/PostHog/posthog/pull/20213
  • [x] Time to convert https://github.com/PostHog/posthog/pull/20259
    • [x] ~Add support for "USING" to HogQL https://github.com/PostHog/posthog/pull/20269~
  • [x] Historical trends https://github.com/PostHog/posthog/pull/20370
  • [x] Actors https://github.com/PostHog/posthog/pull/20472 https://github.com/PostHog/posthog/pull/20496 https://github.com/PostHog/posthog/pull/20503

[MarconLP]

related issue: https://posthoghelp.zendesk.com/agent/tickets/12480

[MarconLP]

related issue: https://posthoghelp.zendesk.com/agent/tickets/12609

[thmsobrmlr]

Consolidated post-HogQL-insights points on 2024-04-23 & 2024-06-05

Trends

Known issues with the HogQL implementation of trends:

• [x] https://posthoghelp.zendesk.com/agent/tickets/12182 trends breakdown, custom aggreagations and formula
• [x] https://github.com/PostHog/posthog/issues/21690 trends breakdown with formula
• [x] Blocking: https://github.com/PostHog/posthog/issues/21326 -> Formulas with breakdowns isn't implemented yet https://github.com/PostHog/posthog/pull/21428
• [x] Blocking: Hourly trends insights with date range "yesterday" return only one hour in the result. See this ticket https://posthoghelp.zendesk.com/agent/tickets/10830. Might have a fix here.
• [x] Blocking: The actors modal date_to and date_from aren't correct for experiments https://github.com/PostHog/posthog/pull/21305#issuecomment-2037203099
• [x] Not all attributes of the action object in a trends result are populated (see this thread https://posthog.slack.com/archives/C0368RPHLQH/p1712060314228319). This is visible in at least one user-facing issue: With "Property value [aggregation]" math, this info is missing from the trends table.
• [ ] Some queries with a lot of series (~20 of them) are significantly slower than in the old version.
• [x] Custom series names are not displayed https://posthog.slack.com/archives/C0368RPHLQH/p1712257945444689
• [x] https://posthoghelp.zendesk.com/agent/tickets/12749 trends persons modal (cannot redefine actor_id)
  • [x] +1 https://posthog.slack.com/archives/C05T5UNVC5R/p1714494786224199
• [x] https://posthoghelp.zendesk.com/agent/tickets/12535 persons modal unique user + count per user average
• [x] https://github.com/PostHog/posthog/issues/21762 exports broken
• [x] Formula doesn't work
• [x] Formula + cohort fails (case 2)
• [x] Persons doesn't open
• [ ]

  Funnels

Known issues with the HogQL implementation of funnels:

• [x] https://posthoghelp.zendesk.com/agent/tickets/12609 boolean breakdown breaks funnels
• [x] https://posthoghelp.zendesk.com/agent/tickets/12555 funnel persons modal
• [x] https://posthoghelp.zendesk.com/agent/tickets/12598 funnel 14 steps
• [x] Blocking: For full compatibility with experiments we also need the "explicit date" filter setting. https://github.com/PostHog/posthog/pull/21305
• [x] ~Blocking: A ClickHouse upgrade broke some of the HogQL funnel tests: https://posthog.slack.com/archives/C0368RPHLQH/p1712193425141229~ upgrade broke both old and new insights; seems to have limited impact
• [x] There is no supertype for types Bool, String because some of them are String/FixedString and some of them are not (see comparision data)
• [x] Cannot convert string true to type Float64 (see comparision data)

Correlation

• [x] https://posthoghelp.zendesk.com/agent/tickets/12438 funnel correlation
• [x] https://posthoghelp.zendesk.com/agent/tickets/12078 negative correlation details
• [x] https://posthoghelp.zendesk.com/agent/tickets/12071 correlated events pagination broken

Lifecycle

• [x] https://github.com/PostHog/posthog/issues/21694 lifecycle
• [x] https://github.com/PostHog/posthog/issues/21691 lifecycle
• [x] https://github.com/PostHog/posthog/issues/21696 lifecycle
• [x] https://github.com/PostHog/posthog/issues/21257

Paths

• [x] Blocking: Writing and running a comparision script.