SQL Expressions
For more advanced calculations, you can use SQL custom expressions. These expressions are helpful when the aggregation results for a billable metric require complex calculations.
Creating a SQL Custom Expression
When creating a billable metric, you can choose between using a simple aggregation field or a custom expression. Custom expressions allow you to define more advanced computation logic, which is useful when the required aggregation involves complex calculations that Lago should handle.
To add a custom expression through the user interface:
- Define the
name
andcode
for the billable metric; - Choose an aggregation type;
- Select
Custom expressions
as the aggregation option instead ofUnique field
; - Enter your custom expression in the expanded side panel;
- Test the custom expression; and
- Save the billable metric.
Create a custom expression for a billable metric
Custom expressions can be used with any aggregation type except COUNT
.
Examples of SQL Custom Expressions
A variety of SQL custom expressions are available for use. Here are a few examples:
- Concatenation:
CONCAT(value1, '_', value2)
- Math operations:
((value1 * value2) / value3)
- Rounding:
ROUND(value1 / value2)
If you need a SQL custom expression that isn’t supported by default in Lago, feel free to contact our team or consider contributing to the open-source version.
Testing your SQL Custom Expression
Lago provides a testing tool to help you validate the custom expressions you’ve created. A sample event is used to test your expression. You can override any field in the test event. If your custom expression is incorrect, Lago will return an error, and you won’t be able to save it until it’s valid.
Keep the following in mind:
- You can dynamically reference any event field within your expression; and
- Use precise paths to reference fields accurately. For instance, to use the event timestamp, the path is
event.timestamp
. To reference a custom property sent with the event, the path isevent.properties.your_field
.
Test your custom expression
Was this page helpful?