Variables in endpoints

Variables let you customize endpoint results at execution time without creating multiple endpoints.

Variables in SQL-based endpoints

For SQL-based endpoints, use variables to inject values into your query at runtime.

Define variables in your query using the {variables.variable_name} syntax:

SELECT count()
FROM events
WHERE properties.$country = {variables.country}

Then pass values when executing:

curl -X POST \
  -H "Authorization: Bearer $POSTHOG_PERSONAL_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"variables": {"country": "US"}}' \
  "<ph_app_host>/api/projects/{project_id}/endpoints/{endpoint_name}/run"

Materialization

SQL-based endpoints with variables can be materialized when each variable is used in a WHERE clause with a supported comparison operator.

Supported operators include: =, >=, >, <, <=

You can use multiple variables in the same query, including range queries on the same column.

For example, the following queries can be materialized:

SELECT count()
FROM events
WHERE properties.customer_name = {variables.customer_name}

SELECT count()
FROM events
WHERE event = {variables.event_name}
  AND hour >= {variables.start_hour}
  AND hour < {variables.end_hour}

However, queries with unsupported operators or variable usage outside WHERE clauses cannot be materialized:

-- Cannot materialize: LIKE operator not supported
SELECT count()
FROM events
WHERE event LIKE {variables.event_pattern}

Variables in insight-based endpoints

For insight-based endpoints, variables work differently. Instead of defining them in your query, certain magic variables are automatically available based on your insight configuration.

The breakdown property variable

If your insight has a single breakdown configured, the breakdown property name automatically becomes a variable. For example, if your TrendsQuery breaks down by $browser, you can filter results by passing that property as a variable:

Note: The breakdown variable is only available for insights with a single breakdown. Insights with multiple breakdowns can still be used as endpoints, but you will not be able to filter by the variables.

curl -X POST \
  -H "Authorization: Bearer $POSTHOG_PERSONAL_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"variables": {"$browser": "Chrome"}}' \
  "<ph_app_host>/api/projects/{project_id}/endpoints/{endpoint_name}/run"

This filters the results to only return data where $browser equals "Chrome".

Breakdown variables work with:

• TrendsQuery
• FunnelsQuery
• RetentionQuery

Date variables

For non-materialized insight endpoints, you can also use date_from and date_to variables to filter by date:

curl -X POST \
  -H "Authorization: Bearer $POSTHOG_PERSONAL_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"variables": {"date_from": "2024-01-01", "date_to": "2024-01-31"}}' \
  "<ph_app_host>/api/projects/{project_id}/endpoints/{endpoint_name}/run"

You can combine date variables with the breakdown variable:

curl -X POST \
  -H "Authorization: Bearer $POSTHOG_PERSONAL_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"variables": {"$browser": "Chrome", "date_from": "2024-01-01", "date_to": "2024-01-31"}}' \
  "<ph_app_host>/api/projects/{project_id}/endpoints/{endpoint_name}/run"

Materialization

Materialized insight-based endpoints only support the breakdown property variable. Date variables (date_from, date_to) are not available for materialized endpoints because the data is pre-computed.

If your insight has a breakdown, you can still filter by that property:

curl -X POST \
  -H "Authorization: Bearer $POSTHOG_PERSONAL_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"variables": {"$browser": "Chrome"}}' \
  "<ph_app_host>/api/projects/{project_id}/endpoints/{endpoint_name}/run"