Warehouse Destinations
Warehouse destinations are the main way of getting direct access to the underlying Freshpaint data. Once you've set up a data warehouse, you can run SQL over the raw Freshpaint data.
identify calls will go to the new warehouse automatically. To send events, you'll need to opt-in each event you want to send to the new warehouse destination.Once configured, Freshpaint will sync data to your data warehouse every six hours.
Warehouses are only available for users on the Freshpaint business plan. If you are interested in setting up warehouses, email [email protected] and we will walk you through the steps to configure a warehouse destination.
For every event you send to a warehouse, Freshpaint will create a corresponding table. For example, if you send an event,
View Homepage to your warehouse, Freshpaint will create a table, view_homepage, and load all the View Homepage events into it. Event tables have a column for each custom property you send in your events. When a new custom property is added to an event, a new column will be added to the table for that property.
All event tables also have the following additional columns:
Column | 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. |
initial_utm_campaign | The value of utm_campaign on the user's first visit. |
initial_utm_content | The value of utm_content on the user's first visit. |
initial_utm_medium | The value of utm_medium on the user's first visit. |
initial_utm_source | The value of utm_source on the user's first visit. |
initial_utm_term | The value of utm_term on the user's first visit. |
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. |
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. |
Name normalization for event definitions and columns occur prior to syncing data with your warehouse destination.
Events are sent to the warehouse as tables and properties of events become columns of these tables in your destination. The following naming rules are applied to table and column names in the order in which they are listed:
- Transliterate non-ASCII characters. Transliterations for the following character sets are supported:
- Han
- Katakana
- Arabic
- Cyrillic
- Convert any character that is not a letter, digit, or underscore to underscore. A space is considered a character.
- Divide alphanumeric strings containing letters and numbers into multiple words and numbers joined with underscores. A change from lower-case letter to an upper-case letter and alternation of numbers and letters are recognized as separators. However, two or more adjacent capital letters are considered a part of one word. For example, "anotherName" becomes "another_name", but "ANOTHERName" becomes "anothername". "MyName123" becomes "my_name_123".
- Convert multiple underscores to a single underscore
- Convert all upper-case letters to lower-case
- Prepend names that start with a number with an underscore
- For table names only, any underscore prefixes are removed if they are followed by a letter
If two event/table or event property/column names normalize to the same value, only one of these will be synced in order to avoid syncing duplicate data. Make sure you name properties and events differently enough that they will not normalize to the same value.
For example, if an event property is created with the name exampleProperty, and then later, it is changed to example_property, there are actually two properties for the event - an exampleProperty with a value and an example_property with a value. In this case, the event occurs after the schema change, exampleProperty will be null and example_property will have a value. However these property values will collide, and only one will sync. So it's possible that example_property in the destination will show as null for some events if the original exampleProperty is the property that is syncing.
A similar situation could occur if two events normalize to the same name. These names will collide, and only one event definition will sync to the destination.
See the table below to see examples of name conversions as a result of these rules being applied.
SOURCE NAME | NAME CONVERSION | NOTES |
|---|---|---|
My_Name | my_name | |
My Name | my_name | |
MyName | my_name | |
mYName | m_yname | |
MyName123 | my_name_123 | |
My123name | my_123_name | |
My_Name! | my_name_ | |
My_Name____c | my_name_c | |
1MyName | _1_my_name | |
!MyName | my_name | In table names |
!MyName | _my_name | In column names |
In addition to any event tables you create, Freshpaint will also create an
identifies table with all the identify calls you made.Column | Description |
id | The identity assigned to the user for the given identify call. |
anonymous_id | The ID of the device the identify call was performed on. |
sent_at | The time the identify call was performed. |
The table will also contain any user properties you send in your identify calls.
Freshpaint supports sending data to the following warehouses:
When changing the name of an event definition, Freshpaint will not rename the table in the warehouse. It is recommended you create a new event definition and sync it to the warehouse instead of updating an old one.
When updating an event definition synced to a warehouse, Freshpaint will not delete the old data out of the warehouse. It is recommended you either delete the old data out of the warehouse before updating the event definition or create a new event definition instead of updating an old one.
Last modified 1mo ago