Listings Data Dictionary | Yext Hitchhikers Platform

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 to be used when decoding the publisher_id column in other Listings and Legacy Listings views.

listings data sharing diagram

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, such as analytics_events or any of the views in the legacy_listings schema.

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 Queries

Other views that contain Listings data will reference a publisher using the numeric publisher_id. To figure out which publishers those IDs correspond to, you’ll need to fetch the publisher name by either querying or joining with the publishers view. Once you’ve identified which IDs correspond to which publishers, you can then pull data for a particular set. The example below shows you how to decode the publisher IDs column in the analytics_events view by joining with the publishers view to fetch the resource_name, which is the publisher’s API name.

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

Note that if an analytics event is associated with a publisher other than the 11 listed above, the resource_name column will be null because the publisher is not included in this reference view.

When you join a view that contains metric data (i.e. Google Performance Metrics) with the publishers view, be careful when using inner joins (either when you specify inner join literally, or implicitly specify it by just writing join).

When you perform an inner join, it will only keep the information that is in common from both tables in the resulting output of the query. In other words, if you inner join on the publishers view, the query will only return the data from the fact view associated with the publisher IDs contained in the publishers view, which are going to be the most common publishers by far, but not every single one.

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. Impressions can be classified as either map or search impressions.

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, -- Decode the app names into their Report Builder names - Maps or Search
    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 Impressions by device and day. Impressions can also be classified as either desktop or mobile impressions.

select
    businesses.business_id,
    businesses.name,
    entity_id,
    date,
    case
      when metric in ('BUSINESS_IMPRESSIONS_DESKTOP_MAPS', 'BUSINESS_IMPRESSIONS_DESKTOP_SEARCH') then 'DESKTOP'
      else 'MOBILE' 
    end as device, -- Decode the device names into their Report Builder names - Desktop or Mobile
    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 date between 'yyyy-mm-dd' and 'yyyy-mm-dd'
  and device 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. Listings Actions can be dimensioned by the type of action the user performed.

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, -- Decode the action names into their Report Builder names
    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. Search keyword data is aggregated on a monthly basis. The date value will be the first day of the month
`search_keyword`	`VARCHAR`	The user’s search query. This is 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 < 15 impressions, Google will return a value of 15 but use this to indicate that the real value is actually less than that. Google uses thresholds in lieu of actual values to provide plausible deniability for individuals

Sample Queries

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

Google Search Terms

listings.google_search_terms

The new Google Search Term Impressions metric has an additional dimension TERM_TYPE, which indicates whether a search term is branded, unbranded, or competitor. Google classified these themselves in the old metric, but no longer do so.

Instead, Yext classifies the search terms in-house. Listings and Pages use the same logic for search term type classification - if you’ve defined a set of branded terms for Pages, the same terms will be classified as branded terms for Listings.

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

Sample Queries

Get Google Search Term Impressions by raw search term and search term type (branded, unbranded, or competitor).

select
    businesses.business_id,
    businesses.name,
    entity_id,
    search_keyword
    search_term_type, -- Dimension by search term type - branded, unbranded, or competitor
    month,
    sum(google_search_keywords.value) as listings_google_search_term_impressions
from listings.google_search_keywords
full join listings.google_search_terms
    on ((google_search_keywords.business_id = google_search_terms.business_id)
    and (google_search_keywords.search_keyword = google_search_terms.search_term))
join public.businesses 
    on (google_search_keywords.business_id = businesses.business_id)
where google_search_terms.search_term_type is not null
    and is_threshold = false
    and month = 'yyyy-mm-01' -- The ‘month’ column is always truncated to the first day of the month
    and google_search_keywords.business_id is not null
    and google_search_keywords.entity_id is not null
group by 1,2,3,4,5,6

Back

< Analytics Data Dictionary

Listings (Legacy) Data Dictionary >

Feedback

<% elem.innerText %>