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)