Review Data Model

Data Model Overview

🏗️ Primary Entities

Reviews: Customer reviews collected from external platforms (Google, Facebook, Yelp, etc.)

Responses: Business replies to customer reviews with source attribution

Ratings: Numeric star ratings (1–5) with review text

🔗 Key Relationships

Many-to-One: Review → Shop (each review belongs to one shop location)

Many-to-One: Review → Tenant (each review belongs to one tenant)

Many-to-One: Review → Customer (optional — linked when reviewer is a known customer)

Tip

Multi-Platform Support: Reviews are aggregated from multiple external platforms including Google, Facebook, Yelp, and MechanicAdvisor. The REVIEW_SOURCE field identifies the originating platform. Reviews can also be manually uploaded.

Note

Customer Linking: The STEER_CUSTOMER_ID field is only populated when the review author has been matched to an existing customer in the system. For unmatched reviewers, this field is null.

Field Specifications

Review Identification

Core identification fields linking reviews to tenants, shops, and customers.

Field Name	Data Type	Description	Required
STEER_TENANT_ID	String (UUID)	Unique tenant identifier (parent organization)	Yes
STEER_SHOP_ID	String (UUID)	Shop location identifier where the review was left	Yes
STEER_CUSTOMER_ID	String (UUID)	Customer identifier (populated only when reviewer is a known customer)	No
STEER_REVIEW_ID	String	Unique review identifier within Steer	Yes

Source & Attribution

Platform source and external identifiers for the review.

Field Name	Data Type	Description	Required
REVIEW_SOURCE	String	The review directory platform, stored as the string name of the internal ReviewDirectory enum (e.g., Google, Yelp, Facebook). See enum values below for the full list	Yes
REVIEW_SOURCE_DETAIL	String	For integration-sourced reviews (Google, Yelp, etc.), this mirrors REVIEW_SOURCE. For manually uploaded reviews (REVIEW_SOURCE = ManualUploaded), this is an arbitrary free-text value taken directly from the Source column in the uploaded CSV — it is not validated against the ReviewDirectory enum and can contain any string the user entered	Yes
EXTERNAL_REVIEW_ID	String	Platform-specific review identifier	Yes

Review Content

The review itself — author, text, rating, and link to the original.

Field Name	Data Type	Description	Required
AUTHOR	String	Reviewer’s display name as shown on the platform	Yes
REVIEW_TEXT	String	Full review text content (null for rating-only reviews)	No
RATING	Number	Star rating (1–5 scale)	Yes
REVIEW_URL	String	Direct URL to the original review on the source platform	Yes

Timestamps

Review and response timing in both UTC and local timezone.

Field Name	Data Type	Description	Required
POSTED_AT	Timestamp	Review posted timestamp (UTC)	Yes
POSTED_AT_LOCAL	Timestamp	Review posted timestamp (shop’s local timezone)	Yes
RESPONSE_POSTED_AT	Timestamp	Business response posted timestamp (UTC)	No
RESPONSE_POSTED_AT_LOCAL	Timestamp	Business response posted timestamp (shop’s local timezone)	No

Business Response

The shop’s reply to the review, if any.

Field Name	Data Type	Description	Required
RESPONSE_TEXT	String	Full text of the business response	No
RESPONSE_SOURCE	Number	How the response was created (see enum values)	No

System Timestamps

Record lifecycle tracking.

Field Name	Data Type	Description	Required
STEER_CREATED_AT	Timestamp	Record creation timestamp in Steer	Yes
STEER_UPDATED_AT	Timestamp	Record last update timestamp in Steer	Yes

Field Enum Values

Review Source

REVIEW_SOURCE stores the string name of the ReviewDirectory enum. For integration-sourced reviews (via BrightLocal, Birdeye, etc.), REVIEW_SOURCE_DETAIL mirrors this value. For manually uploaded reviews, REVIEW_SOURCE_DETAIL is an unvalidated free-text string from the user’s CSV file and may contain any arbitrary value. The enum has approximately 25 members; the most common values are:

• Google: Google Business Profile
• Facebook: Facebook page
• Yelp: Yelp listing
• MechanicAdvisor: MechanicAdvisor platform
• ManualUploaded: Manually uploaded into the system
• BbbOrg: Better Business Bureau
• TripAdvisor: TripAdvisor listing
• Other values may appear as additional review directories are integrated

Response Source

Numeric identifier for how the business response was created:

• 1 — OriginalPlatform: Response posted directly on the review platform
• 2 — Steer: Response created and posted through Steer
• 3 — AiSuggestion: Response generated by AI suggestion

Rating

Integer star rating on a 1–5 scale:

• 1: Very dissatisfied
• 2: Dissatisfied
• 3: Neutral
• 4: Satisfied
• 5: Very satisfied

Data Quality Notes

Null Values

Note

Expected Null Values

• STEER_CUSTOMER_ID is null when the review author has not been matched to a known customer
• REVIEW_TEXT is null for rating-only reviews (reviewer left a star rating without written feedback)
• RESPONSE_POSTED_AT and RESPONSE_POSTED_AT_LOCAL are null when no business response has been posted
• RESPONSE_TEXT is null when no business response exists
• RESPONSE_SOURCE is null when no business response exists

Timezone Handling

Tip

Dual Timestamps: Each timestamp is provided in both UTC (POSTED_AT, RESPONSE_POSTED_AT) and the shop’s local timezone (POSTED_AT_LOCAL, RESPONSE_POSTED_AT_LOCAL). Use UTC timestamps for data processing and local timestamps for display purposes.

Sample Record

Complete API response example with a review record:

{
  "data": [
    [
      "ce96b905-5647-4901-8a83-8e7dbca90418",
      "2dc598c3-1e9e-408e-82df-b8d7acff380b",
      "64e017ab-ead3-4149-8d4a-ec4598e33cd4",
      "69b1676eac6cdc0570bc0419",
      "Google",
      "Google",
      "5385923795441904374",
      "Ron Weum",
      "Easy to schedule, work performed on time, courteous staff",
      5,
      "https://www.google.com/maps/reviews/...",
      "2025-11-06T01:50:41Z",
      "2025-11-05T19:50:41Z",
      "2025-01-15T00:00:00Z",
      "2025-01-14T18:00:00Z",
      "Thank you for your feedback, Ron! We're glad to hear that scheduling was easy and our team met your expectations with courteous service.",
      1,
      "2026-03-11T13:00:30Z",
      "2026-03-11T13:00:35Z"
    ]
  ],
  "metadata": {
    "rowType": [
      {"name": "STEER_TENANT_ID", "type": "text"},
      {"name": "STEER_SHOP_ID", "type": "text"},
      {"name": "STEER_CUSTOMER_ID", "type": "text"},
      {"name": "STEER_REVIEW_ID", "type": "text"},
      {"name": "REVIEW_SOURCE", "type": "text"},
      {"name": "REVIEW_SOURCE_DETAIL", "type": "text"},
      {"name": "EXTERNAL_REVIEW_ID", "type": "text"},
      {"name": "AUTHOR", "type": "text"},
      {"name": "REVIEW_TEXT", "type": "text"},
      {"name": "RATING", "type": "fixed"},
      {"name": "REVIEW_URL", "type": "text"},
      {"name": "POSTED_AT", "type": "timestamp_ntz"},
      {"name": "POSTED_AT_LOCAL", "type": "timestamp_ntz"},
      {"name": "RESPONSE_POSTED_AT", "type": "timestamp_ntz"},
      {"name": "RESPONSE_POSTED_AT_LOCAL", "type": "timestamp_ntz"},
      {"name": "RESPONSE_TEXT", "type": "text"},
      {"name": "RESPONSE_SOURCE", "type": "fixed"},
      {"name": "STEER_CREATED_AT", "type": "timestamp_ntz"},
      {"name": "STEER_UPDATED_AT", "type": "timestamp_ntz"}
    ]
  }
}