Reviews Snowflake Table Reference – Yext Help

Use these views to track all the reviews coming in for your account and any responses you created for those reviews.

Entity Reviews

reviews.entity_reviews

This view contains general information about each review received by the account.

Column	Data Type	Description
`business_id`	`NUMBER`	The numeric identifier of the account
`desensitized`	`BOOLEAN`	Indicates if content has been redacted due to reviews being deleted
`entity_id`	`NUMBER`	The numeric internal UID for the entity
`review_id`	`NUMBER`	The numeric identifier for the review
`publisher_id`	`NUMBER`	The numeric identifier for a publisher. Join on `listings.publishers.publisher_id` to retrieve publisher name.
`review_api_identifier`	`VARCHAR`	A unique identifier used in API requests
`rating`	`FLOAT`	The star rating of the review, integer from 1 to 5
`recommendation`	`VARCHAR`	"Recommended" or "Not Recommended" (applies to Facebook Recommendations only)
`title`	`VARCHAR`	The title of the review
`content`	`VARCHAR`	The content of the review
`language`	`VARCHAR`	The language code of the review
`author_name`	`VARCHAR`	The name of the person who left the review
`author_email`	`VARCHAR`	The email address of the person who left the review
`labels`	`ARRAY`	An ARRAY containing the internal ID and display names for all labels associated with the review
`status`	`VARCHAR`	The status of the review. Options are LIVE, QUARANTINED, and REMOVED
`publisher_timestamp`	`TIMESTAMP_NTZ`	Date of the review on the publisher's listing
`url`	`VARCHAR`	The URL of the review, or the URL of the listing where the review can be found
`deidentification_status`	`VARCHAR`	The status of deidentification for First Party, Healthcare Reviews
`external_id`	`VARCHAR`	A unique external identification number for each review
`last_event_timestamp`	`TIMESTAMP_NTZ`	The last time the review was updated

Sample Query

Get all reviews for an account for the past seven days.

select 
    entity_id,
    publisher_id,
    review_id,
    rating,
    recommendation,
    title,
    content,
    language,
    author_name,
    author_email,
    publisher_timestamp
from reviews.entity_reviews
where date(publisher_timestamp) > dateadd(day, -8, current_date)

Review Comments

reviews.entity_review_comments

This view contains general information on all the responses left on your account's reviews.

Column	Data Type	Description
`author_name`	`VARCHAR`	The name of the author of the response
`author_role`	`VARCHAR`	The role of the author of the response
`business_id`	`NUMBER`	The numeric identifier of the account
`comment_id`	`NUMBER`	The numeric identifier of the response
`content`	`VARCHAR`	The content of the review response
`desensitized`	`BOOLEAN`	Indicates if content has been redacted due to reviews being deleted
`entity_id`	`NUMBER`	The numeric internal UID for the entity
`external_id`	`VARCHAR`	A unique external identification number for each comment
`last_event_timestamp`	`TIMESTAMP_NTZ`	The last time the response was updated
`parent_comment_id`	`NUMBER`	If this comment is in response to another comment, the ID of the parent comment
`publisher_id`	`NUMBER`	The numeric identifier for a publisher
`publisher_timestamp`	`TIMESTAMP_NTZ`	Date of the comment on the publisher's listing
`review_api_identifier`	`VARCHAR`	A unique identifier used in API requests
`review_id`	`NUMBER`	The numeric identifier for the review
`status`	`VARCHAR`	The status of the review. Options are LIVE, QUARANTINED, and REMOVED

Sample Queries

Get all reviews for an account for the past seven days and include any review responses.

select 
    reviews.entity_id, 
    reviews.publisher_id, 
    reviews.review_id, 
    reviews.rating, 
    reviews.content, 
    reviews.publisher_timestamp,
    responses.author_role, 
    responses.comment_id, 
    responses.content,
    responses.publisher_timestamp
from reviews.entity_review_comments as responses
join reviews.entity_reviews as reviews
using (review_id)

Get all reviews for your business that do not have responses.

select 
    reviews.entity_id, 
    reviews.publisher_id, 
    reviews.review_id, 
    reviews.rating, 
    reviews.content
from reviews.entity_reviews as reviews
where not exists
  (select * from reviews.entity_review_comments as responses
    where reviews.review_id = responses.review_id)