Listings (Legacy) Data Dictionary | Yext Hitchhikers Platform

Note

The agg_google_my_business_location_daily and agg_google_my_business_metrics tables contain metrics that were deprecated by Google on March 30, 2023. Yext will keep the historical data, but from March 30th onwards, all of the data for the affected metrics will return zeroes.

To learn which metrics are affected, see our Google Metric Updates announcement documentation.

The schema is named legacy_listings because these are the legacy listings tables in Snowflake, not to be confused with the listings view, which is the schema for the new listings tables.

Activities Daily

legacy_listings.agg_activities_daily

This view contains various listings activity metrics, such as duplicate suppression and detection, new listings that went live, and publisher suggestions, aggregated by day.

Sample Queries

Get the average of publisher suggestions from Google per entity in the past 7 days. First, we can use the publishers view to figure out which publisher ID corresponds to Google.

select 
    publisher_id,
    resource_name
from prod_sharing.listings.publishers 
where resource_name like 'GOOGLE%'

The result of this query tells us that the publisher ID for Google is 715, so we can use that as a filter in our query on the agg_activities_daily table.

select 
    entity_id,
    date,
    avg(publisher_suggestions)
from prod_sharing.legacy_listings.agg_activities_daily 
where publisher_id = 715
and date > dateadd(day, -8, current_date)
group by 1,2 order by 2

Bing Searches

legacy_listings.agg_bing_weekly

This view contains your business’s Bing search impressions by week.

Sample Queries

Get Bing searches for a given week.

select
    businesses.business_id,
    name,
    entity_id,
    week,
    sum(serp_impressions) as bing_searches
from legacy_listings.agg_bing_weekly
join public.businesses using(business_id)
where week = 'yyyy-mm-dd'
  and week is not null
  and entity_id is not null
  and serp_impressions is not null
group by 1,2,3,4

Facebook Demographics

legacy_listings.agg_facebook_demographics_daily

This view contains an aggregation of Facebook page impressions, views and engagement by day and user demographics.

Sample Queries

Get Facebook check-ins, likes, and views on a particular day.

select
    entity_id,
    date,
    sum(checkins) as checkins,
    sum(likes) as likes,
    sum(views) as views
from legacy_listings.agg_facebook_demographics_daily
join public.businesses using (business_id)
where is_total = true
and date between 'yyyy-mm-dd' and 'yyyy-mm-dd'
group by 1,2

Facebook Locations

legacy_listings.agg_facebook_location_daily

This view contains Facebook page impressions, views and engagement by day, location, and additional dimensions such as impression_type, story_type, and cta.

Sample Queries

Get Facebook talking-about metrics, clicks, and impressions dimensioned by entity, date, and impression type.

select
    entity_id,
    date,
    impression_type,
    sum(talking_about) as talking_about,
    sum(clicks) as clicks,
    sum(impressions) as impressions
from legacy_listings.agg_facebook_location_daily
join public.businesses using (business_id)
where is_total = true
and date between 'yyyy-mm-dd' and 'yyyy-mm-dd'
group by 1,2,3

Google My Business Locations

legacy_listings.agg_google_my_business_location_daily

This view contains the legacy Google My Business (GMB) metrics. The actions metric in this table is dimensioned by each individual Google metric type, such as website clicks or driving direction clicks. This means that for a given entity and day, you will see a separate row per metric type.

Sample Queries

Get a count of values for each Google metric type, dimensioned by metric type, for a given entity on a given date.

select 
    entity_id,
    metric_type,
    actions,
    phone_actions,
    map_views,
    search_views,
    queries
from legacy_listings.agg_google_my_business_location_daily 
where entity_id = 1234567
and date = 'yyyy-mm-dd'

Google My Business Metrics

legacy_listings.agg_google_my_business_metrics

This view contains the legacy Google My Business (GMB) metrics. Whereas agg_google_my_business_location_daily is dimensioned by GMB metric type, this view aggregates the values across all the Google actions types.

In other words, the actions column contains the sum of all website clicks, driving direction clicks, queries etc.

Sample Queries

Get searches, Google map views, and Google search views dimensioned by day for the 30 days.

select
    date,
    sum(queries) as queries,
    sum(map_views) as map_views,
    sum(search_views) as search_views
from legacy_listings.agg_google_my_business_metrics
where date > dateadd(day, -31, 'yyyy-mm-dd' )
group by 1 order by 1 desc

Get searches, Google map views, and Google search views for a particular location on a given day.

select 
    sum(queries) as queries,
    sum(map_views) as map_views,
    sum(search_views) as search_views
from legacy_listings.agg_google_my_business_metrics 
where entity_id = 1234567 
and date = 'yyyy-mm-dd'

Google User Generated Content Photos Daily

legacy_listings.agg_google_ugc_photos_daily

Use this view to query data for your Google UGC photos and how many views those photos have received over time, per entity.

Sample Queries

Get the number of Google UGC photos and total photo views for a particular entity as of yesterday.

select 
    date,
    entity_id,
    photo_count,
    photo_views
from prod_sharing.legacy_listings.agg_google_ugc_photos_daily 
where entity_id = 123456
and date > dateadd(day, -2, current_date)

Listings Live

legacy_listings.agg_listings_live

This view indicates for a given entity, which publishers are live on that day. If an entity doesn’t have a listing live for that publisher on that day, then analytics wouldn’t be aggregated.

Sample Queries

Get the total number of live listings across all publishers and entities for a given date.

select 
    sum(live_count)
from prod_sharing.legacy_listings.agg_listings_live
where date = 'yyyy-mm-dd'

Location Daily

legacy_listings.agg_location_daily

This view contains an aggregation of profile views and social engagement by day, at the entity level for all of the entities associated with your business.

Sample Queries

Get profile views and SERP impressions by day, for the last 30 days.

select 
    date,
    sum(profile_views) as profile_views,
    sum(serp_impressions) as serp_impressions
from legacy_listings.agg_location_daily
where date > dateadd(day, -30, current_date)
group by 1 order by 1 desc

Social Posts

legacy_listings.agg_social_posts

Use this view to query data for all new social posts for a given entity, aggregated by day.

Sample Queries

Get the sum of new social posts for a given entity over the past 30 days.

select 
    entity_id,
    sum(count) as total_new_social_posts
from prod_sharing.legacy_listings.agg_social_posts 
where date > dateadd(day, -31, current_date)
and entity_id = 123456

Total Listings Impressions Daily

legacy_listings.agg_total_listings_impressions_daily

Use this view to query data for the total number of Listings impressions across all of the live publishers for a given entity, aggregated by day.

Sample Queries

Get the total number of Google impressions by month for a given entity.

select 
    date_trunc(month, date) as month,
    sum(impressions)
from prod_sharing.legacy_listings.agg_total_listings_impressions_daily
where publisher_id = 715 
and entity_id = 1234567
group by 1 order by 1 desc