Freshpaint
HomeLogin
  • Documentation
    • What is Freshpaint?
    • ⚕️HIPAA Mode
    • 🏗️Building Out Your Account
    • 🌐Overview of Features
    • Guides
      • 📡Add Autotrack to your website to collect data from your users
        • Installing the Freshpaint Javascript SDK
        • Installing the Freshpaint SDK with a Content Security Policy (CSP)
        • Installing the Freshpaint Javascript SDK with Server-Side Rendering (SSR) + React
        • Autocapture + React
        • Configuring a Destination
        • Labeling User Actions
        • How do I install Freshpaint with Typescript?
      • 🔁Send historical data to any destination with Time Machine
      • Next.js Quickstart Guide
      • Android Quickstart Guide
        • Installing the Freshpaint Android SDK
        • Configuring a Destination
        • Instrumenting Your App
      • iOS Quickstart Guide
        • Installing the Freshpaint iOS SDK
        • Configuring a Destination
        • Instrumenting Your App
      • React Native Quickstart Guide
        • Installing the Freshpaint React Native SDK
        • Configuring a Destination
        • Labeling User Actions
        • Configuring Property Capture
    • Setting up Properties
    • Setting up Your Destinations
      • Enabling and Disabling a Destination
      • Deleting a Destination
    • Setting up Your Events
    • User Identification
      • Designated Identify Properties
    • Maps
    • Analytics (Beta)
      • Web Analytics Dashboard
      • Campaigns
      • Service Lines
      • Data Glossary
    • Translations
    • Web Tracker Monitoring
      • Tracker Resolution Types
      • Historical Scans
  • Integrations
    • Destinations
      • Demand Side Platforms (DSPs)
        • Basis
          • Basis Quick Start Guide
          • Basis Reference
        • Google Campaign Manager 360
        • StackAdapt
          • StackAdapt Quick Start Guide
          • StackAdapt Reference
        • theTradeDesk
          • theTradeDesk Quick Start Guide
          • theTradeDesk Reference
          • theTradeDesk with CM360 Configuration Guide
        • Viant
          • Viant Quick Start Guide
          • Viant Reference
      • Direct Response Ads
        • Facebook Conversions API
          • Facebook Conversions API Quick Start Guide
          • Facebook Conversions API Reference
        • Google Ads Conversion API
          • Google Ads Conversion API Quick Start Guide
          • Google Ads Conversions API Reference
        • Google Ads
          • Google Ads Quick Start Guide
          • Google Ads Reference
        • LinkedIn Ads
          • LinkedIn Ads Quick Start Guide
          • LinkedIn Ads Reference
        • Microsoft Ads (formerly Bing Ads)
          • Microsoft Ads Quick Start Guide
          • Microsoft Ads Reference
          • Microsoft Ads Multi-Config Reference
        • Pinterest Ads (Beta)
          • Pinterest Ads Quick Start Guide
          • Pinterest Ads Reference
        • Pinterest Tag
        • Reddit Ads
          • Reddit Ads Quick Start Guide
          • Reddit Ads Reference
        • TikTok Ads
        • Twitter Ads
      • Data Activation
        • ActiveCampaign
          • ActiveCampaign Quick Start Guide
          • ActiveCampaign Reference
        • Amplitude
          • Amplitude Quick Start Guide
          • Amplitude Reference
        • Braze (formerly Appboy)
          • Braze Quick Start Guide
          • Braze Reference
        • Customer.io
          • Customer.io Quick Start Guide
          • Customer.io Reference
        • Freshsales
        • Google Analytics 4 Proxy
          • Google Analytics 4 Proxy Quick Start Guide
          • Google Analytics 4 Proxy Reference
          • Google Analytics 4 Proxy Advanced Tracking Configurations
        • Google Analytics 4 Server-Side
          • Google Analytics 4 Server-Side Quick Start Guide
          • Google Analytics 4 Server-Side Reference
        • Heap
        • Hotjar
        • HubSpot Cloud
        • HubSpot Web
        • impact.com
          • Impact.com Quick Start Guide
          • Impact.com Reference
        • Intercom
          • Intercom Quick Start Guide
          • Intercom Reference
        • Iterable
          • Iterable Quick Start Guide
          • Iterable Reference
        • June
        • Klaviyo
        • Mixpanel
          • Mixpanel Quick Start Guide
          • Mixpanel Reference
        • OneSignal
          • OneSignal Quick Start Guide
          • OneSignal Reference
        • Podscribe (Early Access)
        • Sendinblue
        • Sentry
        • Vero Cloud
        • Webengage
        • Webhooks
        • Woopra
        • Zendesk
      • Data Warehouses
        • Azure Warehouse Setup
        • BigQuery Warehouse Setup
        • Databricks Warehouse Setup
        • Postgres Warehouse Setup
        • Redshift Warehouse Setup
        • S3 Warehouse Setup
        • Snowflake Warehouse Setup
      • Consent Management
        • Osano
        • OneTrust
        • Custom Consent Manager
    • Sources
      • Web
        • Autotrack
        • Precision Tracking
      • CallRail
      • Invoca
      • Customer.io
      • Intercom
      • Mailchimp
      • React Native
      • SendGrid
      • Sendinblue
      • Server-Side
      • iOS
    • 🔷Google Tag Manager integration
      • Google Tag Manager migration
      • Quick Start Guide
    • 📺Freshpaint Video Platform
    • 🖼️Impression Pixel
  • Admin Panel
    • Event Library
      • Event Library Bulk Actions
      • Event Definition Filters
      • Event Tester
      • 🔁Time Machine
      • Visual Tagger
      • Advanced Options
        • Tag Manager
        • Disabling Target Text Capture
        • Cross Domain Tracking
    • Projects & Environments
    • Teams
      • Role-Based Access Control (RBAC)
    • Transformations
      • Standard Events
      • Modify Data
      • Modify User Data
      • SQL Transformations
    • 📈Destination Monitoring
    • 🔎Investigate: Testing and Debugging
      • Live View
      • Event Verification
  • Reference
    • Developer Docs
      • Freshpaint Web SDK Reference
      • Freshpaint Web SDK Options
      • Freshpaint React Native SDK Reference
      • Freshpaint iOS SDK Reference
      • Freshpaint Android SDK Reference
      • HTTP API
    • Frequently Asked Questions
      • How do I circumvent ad blockers?
      • Can Freshpaint track users across domains?
      • Can I install Freshpaint on a Chrome extension?
      • What properties are captured with my events?
      • How Do I Switchover From Segment?
      • How Do I Switch From Native Google Analytics to the Freshpaint Google Analytics Destination?
      • Where do I find my Environment ID?
      • Is Freshpaint GDPR & CCPA Compliant?
      • Can I use transformations to anonymize data for client-side destinations?
      • How do I QA or debug my data?
      • Why Do My Numbers Differ Across Different Tools?
      • Billing: How Does Freshpaint Determine MTUs?
      • Can I Use Freshpaint on Multiple Sites?
      • How Can I Export Data From Freshpaint?
      • How Does Freshpaint Identify Users?
      • How Many Events Should I Create?
      • What Should I Name My Events?
      • How do I track scroll depth?
      • What Data Does Freshpaint Collect?
        • Data Collected on Web
        • Data Collected on React Native
      • Does Freshpaint's Autotrack slow my site down?
      • Running Freshpaint with a Proxy
      • Should my Environment ID be treated as a sensitive key?
      • How Does Freshpaint Determine Session Count?
      • What is the difference between client-side and server-side connection mode?
      • What is a Proxy Integration?
      • Where can I view Freshpaint’s Status?
      • Does Freshpaint provide HIPAA audit logs?
      • Freshpaint Cookie Too Large
      • How does Freshpaint compare to server-side Google Tag Manager?
      • If a user re-installs my app, will Freshpaint generate a new device ID?
      • Why doesn't Freshpaint need a BAA before sending data to Google Ads and Facebook Ads?
      • What Implementation Services Does Freshpaint Offer?
      • Single Sign On (SSO) Setup
Powered by GitBook
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

Was this helpful?

  1. Admin Panel
  2. Transformations

SQL Transformations

PreviousModify User DataNextDestination Monitoring

Last updated 3 months ago

Was this helpful?

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 .

SQL Transformations are part of the Freshpaint SQL add-on. Contact support@freshpaint.io to request access.

SQL Transformations are only available when the destination the event is being sent to is in server-side mode. To make the most use of SQL Transformations, we recommend configuring your destinations in server-side mode (the out of box default for Freshpaint)

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 no 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+).*$', '\\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$';

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.

device_type

Either "Desktop" or "Mobile" depending on the device type.

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.

is described here