Analytics Snowflake Table Reference – Yext Help

This schema contains a log of all user analytics events.

Analytics Events

analytics.analytics_events

Column	Data Type	Description
`business_id`	`INT NOT NULL`	The numeric identifier for the account which is the subject of the event
`analytics_event_id`	`VARCHAR NOT NULL`	The unique ULID identifying the event. Events with the same analytics_event_id are considered duplicates
`event_timestamp`	`TIMESTAMP_NTZ NOT NULL`	The timestamp at which the event occurred
`count`	`INT NOT NULL`	When one record summarizes multiple events, this is the number of events the record represents
`action`	`VARCHAR NOT NULL`	The action which caused the event. Custom actions start with C_
`type`	`VARCHAR`	The high-level type of the event, if the action is an in-built event
`request_timestamp`	`TIMESTAMP_NTZ NOT NULL`	The timestamp at which the event was received by the analytics events pixel endpoint
`request_url`	`VARCHAR`	The URL of the request received by the analytics events pixel endpoint
`browser`	`VARCHAR`	The browser associated with the event
`browser_version`	`VARCHAR`	The browser version associated with the event
`device`	`VARCHAR`	The device associated with the event
`device_class`	`VARCHAR`	The class of device associated with the event
`os`	`VARCHAR`	The operating system associated with the event
`os_version`	`VARCHAR`	The operating system version associated with the event
`user_agent`	`VARCHAR`	The user agent string for the event
`yext_user`	`BOOLEAN`	Whether the event is associated with a Yext user
`bot_effective`	`BOOLEAN NOT NULL`	Whether the event is a result of bot activity
`internal_user`	`BOOLEAN NOT NULL`	Indicates whether the event is the result of activity internal to the customer
`session_id`	`VARCHAR`	Identifies the visitor session across multiple events
`user_id`	`INT`	The Yext userId of the visitor, if they were identified using Yext authentication
`visitor`	`VARCHAR`	Identifies the visitor associated with the event
`visitor_method`	`VARCHAR`	The method used to identify the visitor
`coordinates`	`GEOGRAPHY`	The geographic coordinates of the visitor for the event, as a POINT object with latitude and longitude
`country`	`VARCHAR`	The country of the visitor for the event, as a ISO 3166-1 alpha-2 country code
`locale`	`VARCHAR`	Specifies the locale of the visitor for the event
`page`	`VARCHAR`	The URL of the page the event occurred on
`referrer`	`VARCHAR`	The URL of the page which the visitor came from prior to the event
`destination`	`VARCHAR`	The URL of the page the event is directing the visitor to
`search_rank`	`INT`	The 1-based position of the search result associated with the event
`entity_id`	`INT`	The UID for the entity associated with the event
`publisher_id`	`INT`	The publisherId of the publisher associated with the event
`listings`	`OBJECT`	The Listings properties of the event, other than the publisher_id
`site_id`	`INT`	The Yext siteId of the site associated with the event
`pages`	`OBJECT`	The Pages properties of the event
`search`	`OBJECT`	The Search properties of the event

Sample Queries

Get a log of all user analytics events in the past week, filtering out Yext users, bot traffic, and internal user traffic.

select 
    analytics_event_id,
    event_timestamp,
    action,
    type,
    request_url,
    browser,
    browser_version,
    device,
    device_class,
    os,
    os_version,
    user_agent,
    session_id,
    user_id,
    visitor,
    visitor_method,
    country,
    locale,
    page,
    referrer,
    destination, 
    search_rank,
    entity_id,
    publisher_id,
    listings,
    site_id,
    pages,
    search
from analytics.analytics_events 
where event_timestamp > dateadd(day, -8, current_date)
and yext_user = false
and bot_effective = false
and internal_user = false
order by event_timestamp desc

Get a log of all user analytics events for Listings in the past week filtering out users from a Yext IP, bot traffic, and internal user traffic.

select 
    analytics_event_id,
    event_timestamp,
    action,
    type,
    request_url,
    browser,
    browser_version,
    device,
    device_class,
    os,
    os_version,
    user_agent,
    session_id,
    user_id,
    visitor,
    visitor_method,
    country,
    locale,
    page,
    referrer,
    destination, 
    search_rank,
    entity_id,
    publisher_id,
    listings,
    site_id,
    pages,
    search
from analytics.analytics_events 
where event_timestamp > dateadd(day, -8, current_date)
and yext_user = false
and bot_effective = false
and internal_user = false
and listings is not null
and pages is null
and search is null
order by event_timestamp desc