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,5Get 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,5Google 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,5Google 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