HyperConnect Reviews Fact Table
Fact table of online reviews collected by HyperConnect for each center, one row per review, including the rating, review text, and the business's reply. A review is delivered again whenever it is edited, replied to, or removed from the review platform. Removed reviews stay in the table, marked in review_status and void. Rows appear only for organizations that use HyperConnect. HyperConnect sends this data once a day, covering the previous day. When you load the S3 files into your own warehouse, update rows by review_id; export_run_id identifies the delivery and is not part of the key. Do not delete a review because it is missing from a later delivery.
Columns
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| facthcreputereviewstorewid | BIGINT IDENTITY | NOT NULL | Surrogate warehouse key for the review record. |
| review_id | VARCHAR(256) | NOT NULL | Identifier of the review, assigned by the review platform. |
| centerwid | BIGINT | NULL | Warehouse key referencing the center dimension. |
| center_name | VARCHAR(256) | NULL | Center display name when the record was exported. |
| org_name | VARCHAR(256) | NULL | Organization display name when the record was exported. |
| created_on | TIMESTAMP | NULL | When the review was first seen on the platform. |
| updated_on | TIMESTAMP | NULL | When the review was last updated. |
| last_updated_on | TIMESTAMP | NULL | When the review record last changed; usually the same as updated_on. |
| review_date | DATE | NULL | Calendar date of the review. |
| review_source | VARCHAR(64) | NULL | Platform the review came from. See Enumerator Values. |
| rating | FLOAT | NULL | Rating given; the scale depends on the platform (Google uses 1–5). |
| reviewer_name | VARCHAR(256) | NULL | Reviewer's display name. |
| reviewer_profile_photo_url | VARCHAR(2048) | NULL | URL of the reviewer's profile photo; can be empty. |
| review_text | VARCHAR(65535) | NULL | Review text; empty for a rating-only review. |
| review_reply | VARCHAR(65535) | NULL | The business's reply as JSON text; empty when the review has no reply. Its parts are also delivered in the review_reply_* columns. |
| review_reply_time | TIMESTAMP | NULL | When the reply was posted. |
| review_reply_responded_by | VARCHAR(256) | NULL | Name of the person who replied. |
| review_reply_responded_by_id | VARCHAR(64) | NULL | Identifier of the person who replied. |
| review_reply_response | VARCHAR(65535) | NULL | Text of the reply. |
| review_status | VARCHAR(32) | NULL | Whether the review is still on the review platform. See Enumerator Values. |
| void | BOOLEAN | NULL | Soft-delete flag; TRUE when the review was removed from the review platform. |
| export_run_id | VARCHAR(64) | NULL | Identifier of the HyperConnect export run that delivered the row: its UTC date and time as YYYYMMDDHHMMSS. |
Note: All fact tables also include standard ETL metadata columns (
podname,organizationid,groupid,etlCreatedDate) which are omitted from this reference for brevity.
Primary Key
facthcreputereviewstorewid
Foreign Keys
| Column | References |
|---|---|
| centerwid | Bi_DimCenter.centerwid |
Enumerator Values
| Column | Values |
|---|---|
| review_source | google, facebook, ZENOTI |
| review_status | 0 = Active 1 = Removed from the review platform |
| void | FALSE = Active, TRUE = Removed from the review platform |