> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# obt_reviews

> Every product review from your review app, with its rating, storefront status, product, customer and prior order.

Product reviews from your review app (Judge.me), one row per review: its star rating, title and text, whether it shows on your storefront, its photos and videos, the reviewed product from your catalog, the reviewer's customer record, and the reviewer's latest order of that product placed before the review.

* **Grain:** one row per review (`sm_review_key`). A review deleted in Judge.me keeps its last state, because Judge.me's API does not report deletions.
* **Storefront status:** `review_status` is `published`, `unpublished` (not shown) or `pending` (waiting for your moderation; whether it shows meanwhile depends on your auto-publish rules, which Judge.me's API does not report). `is_review_published` is true for published reviews, false for unpublished ones and NULL for pending ones, so filtering on it counts the reviews Judge.me reports as shown. The average review rating metric counts those reviews only.
* **Products:** `product_id` is the reviewed product's Shopify id. `sm_product_key` joins `dim_product_variants` and is NULL when the product is no longer in your catalog, in which case `product_title` is Judge.me's copy of the title.
* **Customers:** `sm_customer_key` joins `dim_customers` and `obt_customers`. A reviewer is matched by Judge.me's customer id, else by email, and `review_customer_match_method` says which. Reviewer contact details are not in this table.
* **Purchase:** `reviewer_latest_product_order_key` joins `obt_orders.sm_order_key`. It is the reviewer's latest valid order containing the product, placed at or before the review: evidence of a purchase, not necessarily the order the review is about. `has_prior_product_purchase` is NULL when the reviewer matches no customer.
* **Imported reviews:** reviews imported into Judge.me from an earlier review app keep their original dates. `review_source` says where each review came from, in Judge.me's own words.

## Example: lowest-rated products over the last 90 days

```sql theme={null}
SELECT
  product_id,
  product_title,
  COUNT(*) AS published_reviews,
  ROUND(AVG(review_rating), 2) AS average_rating,
  COUNTIF(review_rating <= 2) AS one_and_two_star_reviews
FROM `your_project.sm_transformed_v2.obt_reviews`
WHERE is_review_published
  AND review_created_at_local_datetime >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 90 DAY)
GROUP BY product_id, product_title
HAVING published_reviews >= 10
ORDER BY average_rating
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.