SQL Transformations

Introduction

SQL Transformations give you the full power of ANSI SQL to transform your events. Any transformation you can express with SQL, you can perform with SQL transformations. When you write a SQL transformation you will have access to an incoming_event table. This table has a number of columns that correspond to the built-in data Freshpaint collects and you will have access to an additional JSON column called props where you will have access to any custom properties. The full schema of the incoming_event table is described here.
SQL Transformations are only available when either the event being transformed is a server-side event or the destination is in server-side mode. If you want to make as much use of SQL Transformations as possible, we recommend configuring your destinations in server-side mode.

Behavior

When you write a SQL Transformation you are writing a SQL query against the incoming_event table. Your query should return either a single row or now rows. Any columns returned by your query will be attached to the event as custom properties. If your query returns zero rows, the event will filtered out and not sent to any destinations. As an example, the query:
SELECT 10 AS example_prop FROM incoming_event;
Will create a new property example_prop with the value 10. If you return NULL for the value of a property, that property will instead be deleted.
There are two reserved column names that have special behavior when you return them:
  • event_name - When you return this from a query, the name of the event will become the provided value.
  • user_id - When you return this from a query, the user_id of the event will become the provided value.

Examples

Prepend a string to your events

This transformation will prepend all events with the prefix SQL: . If the incoming event name is Login, the transformed event will have the name SQL: Login.
SELECT CONCAT('SQL: ', event_name) AS event_name FROM incoming_event;

Run a custom formula

This example will double the value of the custom property a:
SELECT 2 * JSON_EXTRACT_DOUBLE(props, 'a') AS a FROM incoming_event;

Perform a regular expression match

This example extracts a URL parameter from the url of the event using regular expressions:
SELECT REGEXP_REPLACE(url, '^.*param=(\\w+).*#x27;, '\\1') AS param FROM incoming_event;

Add a constant property

This example creates a new property called new_property an always sets it to the value 10:
SELECT 10 AS new_property FROM incoming_event;

Delete a property

This example deletes the property sensitive_identifier from the event. This is done by setting the value of the property to NULL.
SELECT NULL AS sensitive_identifier FROM incoming_event;

Filter events based on a regular expression

This example filters out all events where the email is a gmail address:
SELECT event_name
FROM incoming_event
WHERE JSON_EXTRACT_STRING(props, 'email') NOT RLIKE 'gmail.com#x27;;

Schema

The incoming_event table has the following schema:
Property Name
Description
event_name
The name of the event definition matched by the event.
time
The Unix time in seconds the event occurred at.
pageview_id
The id of the pageview the event belongs to. All events performed in the same pageview will have the same pageview_id.
session_id
The id of the session the event belongs to. A session is a group of actions perform in the same time frame. A session ends after 30 minutes of inactivity. All events performed in the same session will have the same session_id.
browser
The name of the web browser.
browser_version
The version of the web browser.
device_id
A Freshpaint generated unique identifier for the device the event was sent from. This is sometimes referred to as the anonymous_id.
host
The domain of the page the event occurred on.
initial_referrer
The full URL of the page that first referred the user to your site.
initial_referring_domain
The domain of the page that first referred the user to your site.
operating_system
The name of the operating system the user was using.
path
The URL path of the page the event was performed on.
referrer
The URL of the page the user was on before the page they performed the event on.
referring_domain
The domain of the page the user was on before the page they perfomed the event on. It may be your own domain.
screen_height
The height of the user's screen.
screen_width
The width of the user's screen.
target_text
The text of the element that was clicked on.
url
The full URL of the page the event was performed on.
user_id
The identity associated with the user through Identify.
utm_campaign
The most recent value of the utm_campaign UTM property.
utm_content
The most recent value of the utm_content UTM property.
utm_medium
The most recent value of the utm_medium UTM property.
utm_source
The most recent value of the utm_source UTM property.
utm_term
The most recent value of the utm_term UTM property.
Copy link
On this page
Introduction
Behavior
Examples
Prepend a string to your events
Run a custom formula
Perform a regular expression match
Add a constant property
Delete a property
Filter events based on a regular expression
Schema