Listings Snowflake Table Reference – Yext Help

The listings schema contains the views for the new Google performance metrics, Google search keywords, and a reference view containing key publishers within our network.

Publishers

listings.publishers

This view contains general information about the most popular active publishers in the Yext network. Use the publishers view to reference publisher metadata, particularly the publisher name. This can be especially useful when decoding the publisher_id column in any other view that contains listings data.

Column	Data Type	Description
`publisher_id`	`NUMBER`	The numeric identifier for a publisher
`resource_name`	`VARCHAR`	The external-facing identifier for a publisher — the API name
`launched_date`	`TIMESTAMP_NTZ`	The publisher's launch date with Yext

Sample Query

Decode publisher IDs in the analytics_events view by joining with the publishers view.

select 
    analytics_events.*,
    publishers.resource_name
from analytics.analytics_events
left join listings.publishers using (publisher_id)

Google Performance Metrics

listings.google_performance_metrics

This view contains your business's performance data from Google's Performance API. Use this view to get data for the Listings Impressions and Listings Actions metrics.

Column	Data Type	Description
`business_id`	`NUMBER`	The numeric identifier for the account
`entity_id`	`NUMBER`	The UID for the Yext entity
`task_timestamp`	`TIMESTAMP_NTZ`	The timestamp indicating when the ingestion ETL began
`date`	`TIMESTAMP_NTZ`	The date that the datapoint corresponds to
`metric`	`VARCHAR`	The metric associated with the value
`value`	`NUMBER`	The value for a given metric

Sample Queries

Get Listings Impressions by app and week.

select
    businesses.business_id,
    businesses.name,
    entity_id,
    date_trunc('week', date) as week,
    case
      when metric in ('BUSINESS_IMPRESSIONS_DESKTOP_MAPS', 'BUSINESS_IMPRESSIONS_MOBILE_MAPS') then 'MAPS'
      else 'SEARCH' 
    end as app,
    sum(case 
        when metric in ('BUSINESS_IMPRESSIONS_DESKTOP_MAPS','BUSINESS_IMPRESSIONS_MOBILE_MAPS','BUSINESS_IMPRESSIONS_DESKTOP_SEARCH','BUSINESS_IMPRESSIONS_MOBILE_SEARCH') 
        then value end) 
    as listings_impressions
from listings.google_performance_metrics 
join public.businesses using(business_id)
where week = 'yyyy-mm-dd' 
  and app is not null
  and businesses.business_id is not null
  and google_performance_metrics.entity_id is not null
group by 1,2,3,4,5

Get Listings Actions by action and day.

select
    businesses.business_id,
    businesses.name,
    entity_id,
    date as day,
    case 
        when metric = 'WEBSITE_CLICKS' then 'WEBSITE'
        when metric = 'CALL_CLICKS' then 'TAP_TO_CALL'
        when metric = 'BUSINESS_DIRECTION_REQUESTS' then 'DRIVING_DIRECTIONS'
        when metric = 'BUSINESS_CONVERSATIONS' then 'MESSAGE'
        when metric = 'BUSINESS_BOOKINGS' then 'BOOK_APPOINTMENT'
        when metric = 'BUSINESS_FOOD_ORDERS' then 'ORDER_NOW'
        end as action,
    sum(case 
        when metric in ('WEBSITE_CLICKS','CALL_CLICKS','BUSINESS_DIRECTION_REQUESTS','BUSINESS_CONVERSATIONS','BUSINESS_BOOKINGS','BUSINESS_FOOD_ORDERS') then value 
        else null end) as listings_actions
from listings.google_performance_metrics
join public.businesses using(business_id)
where date between 'yyyy-mm-dd' and 'yyyy-mm-dd'
  and action is not null
  and businesses.business_id is not null
  and entity_id is not null
group by 1,2,3,4,5

Google Search Keywords

listings.google_search_keywords

This view contains your business's search keyword data from Google's Search Keywords API by month. Use this view to get data for the Google Search Term Impressions metric.

Column	Data Type	Description
`business_id`	`NUMBER`	The numeric identifier for the account
`entity_id`	`NUMBER`	The UID for the Yext entity
`month`	`TIMESTAMP_NTZ`	The month associated with the datapoint. The date value will be the first day of the month
`search_keyword`	`VARCHAR`	The user's search query. Always a lower-cased string
`value`	`NUMBER`	The sum of the number of unique users that searched the keyword in a month
`is_threshold`	`BOOLEAN`	Boolean indicating if value is a threshold. If a query has fewer than 15 impressions, Google returns a value of 15 but uses this to indicate the real value is less than that

Sample Query

Get Google Search Term Impressions, dimensioned by raw search term.

select
    businesses.business_id,
    businesses.name,
    entity_id,
    search_keyword,
    month,
    sum(google_search_keywords.value) as listings_google_search_term_impressions
from listings.google_search_keywords
join public.businesses using (business_id)
where is_threshold = false
    and month is not null
    and month = 'yyyy-mm-01'
    and businesses.business_id is not null
    and entity_id is not null
group by 1,2,3,4,5

Google Search Terms

listings.google_search_terms

This view contains search term type classifications for your business's search keywords. Yext classifies search terms as branded, unbranded, or competitor.

Column	Data Type	Description
`business_id`	`NUMBER`	The numeric identifier for the account
`search_term`	`VARCHAR`	The user's search query. Always a lower-cased string
`search_term_type`	`VARCHAR`	The classification given to the search term: branded, unbranded, or competitor

Sample Query

Get Google Search Term Impressions, dimensioned by raw search term.

select
   businesses.business_id,
   businesses.name,
   entity_id,
   search_keyword,
   month,
   sum(google_search_keywords.value) as listings_google_search_term_impressions
from listings.google_search_keywords
join public.businesses using (business_id)
where is_threshold = false
   and month is not null
   and month = 'yyyy-mm-01' -- The ‘month’ column is always truncated to the first day of the month
   and businesses.business_id is not null
   and entity_id is not null
group by 1,2,3,4,5