:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
import re
from datetime import datetime
from enum import Enum
from typing import Any, cast
from posthog.schema import (
ActionsNode,
AssistantFunnelsActionsNode,
AssistantFunnelsEventsNode,
AssistantFunnelsQuery,
DateRange,
EventsNode,
FunnelsDataWarehouseNode,
FunnelsFilter,
FunnelsQuery,
FunnelStepReference,
FunnelVizType,
)
from posthog.dataclasses import frozen
from posthog.hogql_queries.utils.query_date_range import QueryDateRange
from posthog.models import Team
from .utils import (
PARTIAL_BUCKET_MARKER,
format_duration,
format_matrix,
format_number,
format_percentage,
partial_bucket_flags,
partial_bucket_note,
strip_datetime_seconds,
)
SESSION_ID_EXPRESSIONS = {"properties.$session_id", "$session_id"}
# Only a short property path goes into the LLM context, so a saved expression cannot carry free-form text.
DISPLAYABLE_EXPRESSION_RE = re.compile(r"[A-Za-z_$][\w$]*(\.[A-Za-z_$][\w$]*)*")
DISPLAYABLE_EXPRESSION_MAX_LENGTH = 100
@frozen
class FunnelAggregation:
count_label: str
distribution_label: str
unit: str
class FunnelResultsFormatter:
"""
Compresses and formats funnels results into a LLM-friendly string.
Example answer for a steps funnel:
```
Date range
Metric|Label 1|Label 2
Total person count|value1|value2
Conversion rate|value1|value2
Drop-off rate|value1|value2
Average conversion time|value1|value2
Median conversion time|value1|value2
```
Example answer for a time to convert funnel:
```
Date range: 2025-01-20 00:00:00 to 2025-01-22 23:59:59
Events: $pageview (custom) -> $ai_trace
Time|User distribution
10m|100%
10m 1s|0%
The user distribution is the percentage of users who completed the funnel in the given period.
```
Example answer for funnel trends:
```
Date|$pageview (custom) -> $ai_trace conversion|$pageview (custom) -> $ai_trace drop-off
2025-01-05|10%|90%
2025-01-12|91%|9%
2025-01-19|100%|0%
2025-01-26|100%|0%
```
"""
def __init__(
self,
query: AssistantFunnelsQuery | FunnelsQuery,
results: list[dict[str, Any]] | list[list[dict[str, Any]]] | dict[str, Any] | None,
team: Team,
utc_now_datetime: datetime,
):
self._query = query
self._results = results
self._team = team
date_range = DateRange.model_validate(query.dateRange.model_dump()) if query.dateRange else None
self._query_date_range = QueryDateRange(date_range, team, query.interval, utc_now_datetime)
def format(self) -> str:
if self._viz_type == FunnelVizType.STEPS:
return self._format_steps()
elif self._viz_type == FunnelVizType.TIME_TO_CONVERT:
return self._format_time_to_convert()
else:
return self._format_trends()
def _format_steps(self) -> str:
results = self._results
if not isinstance(results, list) or len(results) == 0:
return "No data recorded for this time period."
results = cast(list[dict[str, Any]] | list[list[dict[str, Any]]], results)
matrixes = []
if isinstance(results[0], list):
for result in results:
matrixes.append(self._format_steps_series(cast(list[dict], result)))
else:
matrixes.append(self._format_steps_series(cast(list[dict], results)))
unit = self._aggregation.unit
conversion_type_hint = f'Conversion and drop-off rates are calculated in overall. For example, "Conversion rate: 9%" means that 9% of {unit} from the first step completed the funnel.'
if self._step_reference == FunnelStepReference.PREVIOUS:
conversion_type_hint = f"Conversion and drop-off rates are relative to the previous steps. For example, 'Conversion rate: 90%' means that 90% of {unit} from the previous step completed the funnel."
joined_matrixes = "\n\n".join(matrixes)
return f"{self._format_time_range()}\n\n{joined_matrixes}\n\n{conversion_type_hint}"
def _format_time_to_convert(self) -> str:
results = self._results
if not isinstance(results, dict) or len(results) == 0:
return "No data recorded for this time period."
aggregation = self._aggregation
matrix: list[list[Any]] = [
["Average time to convert", aggregation.distribution_label],
]
bins = results.get("bins") or []
total = sum(count for _, count in bins)
for bin_from_seconds, count in bins:
matrix.append([format_duration(bin_from_seconds), format_percentage(count / total if total else 0)])
hint = f"The {aggregation.distribution_label.lower()} is the percentage of {aggregation.unit} who completed the funnel in the given period."
return f"{self._format_time_range()}\n\nEvents: {self._format_filter_series_label()}\n{format_matrix(matrix)}\n\n{hint}"
def _format_trends(self) -> str:
results = self._results
if not isinstance(results, list) or len(results) == 0:
return "No data recorded for this time period."
results = cast(list[dict[str, Any]], results)
return self._format_trends_series(results)
@property
def _step_reference(self) -> FunnelStepReference:
step_reference = FunnelStepReference.TOTAL
if self._query.funnelsFilter and self._query.funnelsFilter.funnelStepReference:
step_reference = self._query.funnelsFilter.funnelStepReference
return step_reference
@property
def _viz_type(self) -> FunnelVizType:
viz_type = FunnelVizType.STEPS
if self._query.funnelsFilter and self._query.funnelsFilter.funnelVizType:
viz_type = self._query.funnelsFilter.funnelVizType
return viz_type
@property
def _aggregation(self) -> FunnelAggregation:
funnels_filter = self._query.funnelsFilter
if isinstance(funnels_filter, FunnelsFilter) and funnels_filter.customAggregationTarget:
# Data warehouse steps count rows by each node's aggregation_target_field, not by person.
return FunnelAggregation(
count_label="Total entity count", distribution_label="Entity distribution", unit="entities"
)
# Group aggregation takes precedence over a HogQL expression, the same as in the funnel query runner.
if self._query.aggregation_group_type_index is not None:
return FunnelAggregation(
count_label=f"Total group count (group type index {self._query.aggregation_group_type_index})",
distribution_label="Group distribution",
unit="groups",
)
hogql = self._query.funnelsFilter.funnelAggregateByHogQL if self._query.funnelsFilter else None
if isinstance(hogql, Enum):
hogql = hogql.value
if not hogql or hogql == "person_id":
return FunnelAggregation(
count_label="Total person count", distribution_label="User distribution", unit="users"
)
if hogql in SESSION_ID_EXPRESSIONS:
return FunnelAggregation(
count_label=f"Total session count (aggregated by {hogql})",
distribution_label="Session distribution",
unit="sessions",
)
if len(hogql) <= DISPLAYABLE_EXPRESSION_MAX_LENGTH and DISPLAYABLE_EXPRESSION_RE.fullmatch(hogql):
return FunnelAggregation(
count_label=f"Total unique value count (aggregated by {hogql})",
distribution_label="Value distribution",
unit=f"unique values of {hogql}",
)
return FunnelAggregation(
count_label="Total unique value count (aggregated by a custom HogQL expression)",
distribution_label="Value distribution",
unit="unique values of the custom HogQL expression",
)
def _format_steps_series(self, results: list[dict[str, Any]]) -> str:
matrix: list[list[Any]] = [
["Metric"],
[self._aggregation.count_label],
["Conversion rate"],
["Dropoff rate"],
["Average conversion time"],
["Median conversion time"],
]
for idx, series in enumerate(results):
label = series["name"]
if series.get("custom_name") is not None:
label = f"{label} {series['custom_name']}"
matrix[0].append(label)
matrix[1].append(series["count"])
this_step_count = series["count"]
first_step_count = matrix[1][1]
if idx == 0:
conversion_rate = "100%"
dropoff_rate = "0%"
elif self._step_reference == FunnelStepReference.PREVIOUS:
prev_count = matrix[1][idx]
if prev_count != 0:
conversion_rate = format_percentage(this_step_count / prev_count)
dropoff_rate = format_percentage((prev_count - this_step_count) / prev_count)
else:
conversion_rate = "0%"
dropoff_rate = "100%"
else:
if first_step_count != 0:
conversion_rate = format_percentage(this_step_count / first_step_count)
dropoff_rate = format_percentage((first_step_count - this_step_count) / first_step_count)
else:
conversion_rate = "0%"
dropoff_rate = "100%"
matrix[2].append(conversion_rate)
matrix[3].append(dropoff_rate)
matrix[4].append(
format_duration(series["average_conversion_time"])
if series["average_conversion_time"] is not None
else "-"
)
matrix[5].append(
format_duration(series["median_conversion_time"])
if series["median_conversion_time"] is not None
else "-"
)
matrix[1] = [format_number(cell) for cell in matrix[1]]
formatted_matrix = format_matrix(matrix)
if results[0].get("breakdown_value") is not None:
breakdown_value = results[0]["breakdown_value"]
if isinstance(breakdown_value, list):
breakdown_value = ", ".join(breakdown_value)
return f"---{breakdown_value}\n{formatted_matrix}"
return formatted_matrix
def _format_time_range(self) -> str:
return (
f"Date range: {self._query_date_range.date_from_str} to {self._query_date_range.date_to_str} "
f"({self._team.timezone})"
)
def _format_filter_series_label(self) -> str:
series_labels: list[str] = []
for node in self._query.series:
if isinstance(node, AssistantFunnelsEventsNode | EventsNode):
if node.custom_name is not None:
series_labels.append(f"{node.event} ({node.custom_name})")
else:
series_labels.append(f"{node.event}")
elif isinstance(node, AssistantFunnelsActionsNode | ActionsNode):
series_labels.append(f"{node.name} (action {node.id})")
elif isinstance(node, FunnelsDataWarehouseNode):
if node.custom_name is not None:
series_labels.append(f"{node.name} ({node.custom_name})")
else:
series_labels.append(f"{node.name}")
return " -> ".join(series_labels)
def _format_trends_series(self, results: list[dict[str, Any]]) -> str:
# Get dates and series labels
result = results[0]
dates = result["days"]
label = self._format_filter_series_label()
now_local = self._query_date_range.now_with_timezone.replace(tzinfo=None)
current_interval_start = self._query_date_range.align_with_interval(now_local)
partial = partial_bucket_flags(dates, current_interval_start)
# Build header row
header = ["Date"]
for series in results:
label_with_breakdown = label
if series.get("breakdown_value") is not None:
breakdown = ", ".join([str(value) for value in series["breakdown_value"]])
label_with_breakdown = f"{label} {breakdown} breakdown"
header.append(f"{label_with_breakdown} conversion")
header.append(f"{label_with_breakdown} drop-off")
matrix: list[list[str]] = [
header,
]
# Build data rows
for i, date in enumerate(dates):
date_cell = strip_datetime_seconds(date)
if partial[i]:
date_cell += PARTIAL_BUCKET_MARKER
row = [date_cell]
for series in results:
row.append(format_percentage(series["data"][i] / 100))
row.append(format_percentage((100 - series["data"][i]) / 100))
matrix.append(row)
formatted = format_matrix(matrix)
if any(partial):
formatted = f"{formatted}\n\n{partial_bucket_note(self._team.timezone)}"
return formatted