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.

• Use the backend side filter_to_query function to return only queries, not filters feat(hogql): replace filters with query backend side posthog#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
  • 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.
    • Hidden legend indexes of an insight can't be edited posthog#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

• Error when searching by multiple CSS selectors in Product Analytics posthog#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

• Overall retention percentages went down retroactively posthog#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
  • Async queries don't have user friendly errors posthog#21270
  • HogQL optimization that filters events early on posthog#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 feat(insights): improve series and exclusion entity handling posthog#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 test(clickhouse): skip tests incompatible with 23.12 posthog#21437
• Implement all the new cool things users ask for Insight feature requests and bugs (trends, funnels, paths, etc) #200
• Finish the HogQL pending tasks HogQL & Data Exploration next steps #81
• Do that little BI thing BI #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

PostHog/posthog#17295 & PostHog/posthog#17414 & PostHog/posthog#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.

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

Step 2. Make it good

• The result is cached and sent to the frontend feat(hogql): implement basic caching for hogql insight queries posthog#17483 @thmsobrmlr
• Rewrite the Antlr parser in a faster language @Twixes
• Persons modal: Get the persons behind a data point by sending the query and specifying the data point feat(insights): sourced person queries posthog#17470 @mariusandra
• Save as cohort in persons modal feat(insights): cohorts from queries in persons modal posthog#18982
• Why is the lifecycle insight showing different numbers?
• Notebooks and insights work well together.
• Open the SQL behind an insight in a new view
• Expand the persons modal into a new PersonsQuery feat(insights): explore persons modal further posthog#19198
• Any changes we want to make to the query nodes, filters, etc? Did lots of capitalization.
• 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

• PersonsQuery
  • Make it fast (...enough)
  • Make the ClickHouse-powered PersonsQuery the default view for persons pages. (Waiting for 2024 before rollout)
• Lifecycle @mariusandra Rewrite the Lifecycle Insight in HogQL posthog#19640
  • Feature complete with current implementation
  • Results tested to match current for our team
  • Released to PostHog's team
  • Results tested to match current ones for all other teams
  • Released to all users
• Retention @webjunkie Rewrite the Retention Insight in HogQL posthog#19639
  • First version chore(hogql): Add retention queries in HogQL posthog#18831
  • Persons modal
  • Persons modal CSV export & save as cohort
  • Released to PostHog's team
  • Released
• Trends @Gilbert09 Rewrite the Trends Insight in HogQL posthog#19637
  • All other math operators
  • Breakdown
  • Sampling
  • Formulas
  • Series filtering
  • Graph display types (pie chart, cumulative line graph, etc)
  • Compare to previous period
  • to_persons_query
  • Smoothing
  • Actions
  • Breakdown controls (other on/off & limit)
  • Released to PostHog's team
  • Breakdown in actors modal
• Stickiness @Gilbert09 Rewrite the Stickiness Insight in HogQL posthog#19642
  • To start
  • Date ranges
  • Multiple series
  • "Compare to previous period"
  • Actors query
  • Aggregations
  • Unit tests
  • Internal release to PostHog team
  • Release to all users
• Paths Rewrite the Paths Insight in HogQL posthog#19641
  • To start
• Funnels Rewrite the Funnels Insight in HogQL posthog#19638
  • Conversion steps
    • "Sequential" feat(hogql): sequential steps funnel posthog#19951
    • Breakdowns feat(hogql): add funnel breakdowns posthog#20153
    • "Strict order" feat(hogql): strict order funnel posthog#20211
    • "Any order" feat(hogql): unordered funnel posthog#20213
  • Time to convert feat(hogql): time to convert funnel posthog#20259
    • Add support for "USING" to HogQL feat(hogql): add support for using join contraint posthog#20269
  • Historical trends feat(hogql): trends funnel posthog#20370
  • Actors feat(hogql): add funnel hogql actors posthog#20472 feat(hogql): add remaining funnel actors posthog#20496 feat(hogql): implement include_recordings for funnel actors posthog#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:

• https://posthoghelp.zendesk.com/agent/tickets/12182 trends breakdown, custom aggreagations and formula
• When "Breakdown by" is set for a formula, it throws an error posthog#21690 trends breakdown with formula
• Blocking: HogQL trends insights return only one result for an insight with formula broken down by multiple cohorts posthog#21326 -> Formulas with breakdowns isn't implemented yet fix(hogql): add support for breakdowns with formula for trends posthog#21428
• 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.
• Blocking: The actors modal date_to and date_from aren't correct for experiments feat(hogql): add explicit date posthog#21305 (comment)
• 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.
• Custom series names are not displayed https://posthog.slack.com/archives/C0368RPHLQH/p1712257945444689
• https://posthoghelp.zendesk.com/agent/tickets/12749 trends persons modal (cannot redefine actor_id)
  • +1 https://posthog.slack.com/archives/C05T5UNVC5R/p1714494786224199
• https://posthoghelp.zendesk.com/agent/tickets/12535 persons modal unique user + count per user average
• Export of reports are broken posthog#21762 exports broken
• Formula doesn't work
• Formula + cohort fails (case 2)
• Persons doesn't open
• [ ]

Funnels

Known issues with the HogQL implementation of funnels:

• https://posthoghelp.zendesk.com/agent/tickets/12609 boolean breakdown breaks funnels
• https://posthoghelp.zendesk.com/agent/tickets/12555 funnel persons modal
• https://posthoghelp.zendesk.com/agent/tickets/12598 funnel 14 steps
•  Blocking: For full compatibility with experiments we also need the "explicit date" filter setting. feat(hogql): add explicit date posthog#21305
• 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
•  There is no supertype for types Bool, String because some of them are String/FixedString and some of them are not (see comparision data)
• Cannot convert string true to type Float64 (see comparision data)

Correlation

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

Lifecycle

• Problem with lifecycle report posthog#21694 lifecycle
• Error W/Product Analytics  posthog#21691 lifecycle
• can't get user lifecycle insight posthog#21696 lifecycle
• ActorsModal broken in the lifecycle insight posthog#21257

Paths

• Blocking: Writing and running a comparision script.