Trustpilot reviews to BigQuery
Every Trustpilot review as a row in your own project, next to the orders that produced it. A review count is trivia. Reviews per thousand orders is an operations metric, and it is one join away.
Set up in under five minutes • No demo required
38,214 rows
Destination table
northwind-analytics.reviews.trustpilot_reviews
A count is trivia. A rate is an instrument.
Trustpilot invites your customers after they buy. That is what its Automatic Feedback Service is, an email a few days after each order, so your review volume follows your order volume far more closely than it follows anything marketing does. A month with more reviews is usually just a month with more orders.
Your order table already lives in BigQuery. Put Trustpilot reviews beside it and reviews per thousand orders, and the share of them at one star, become a GROUP BY over the week each order shipped. Five lines of SQL, and no spreadsheet anyone has to maintain.
A rate holds steady while the business runs normally, and that is what makes it readable. Four percent one stars every week for a quarter, then seventeen in the week of 17 August against flat order volume, is not sentiment drifting. It is something operational that broke on a date.
The follow-up is a WHERE clause, because the full review text is an ordinary column. Count the reviews from that week that mention the depot or the courier, read six of them, and the cause names itself before anyone files a ticket.
Query editor -- one-star share by the week the order shipped
SELECT
o.ship_week,
o.orders,
COUNT(r.review_id) AS reviews,
ROUND(100 * COUNTIF(r.score = 1)
/ COUNT(r.review_id), 1) AS pct_one_star
FROM `northwind-analytics.core.weekly_orders` o
LEFT JOIN `northwind-analytics.reviews.trustpilot_reviews` r
ON DATE_TRUNC(r.review_date, WEEK) = o.ship_week
GROUP BY o.ship_week, o.orders
ORDER BY o.ship_weekSomething that tells you, rather than something you remember to open
A dashboard is a thing people look at once they already suspect something. A scheduled query is a thing that looks for you. BigQuery runs one on the schedule you set, writes its result to a table, and you point whatever you already page on at that table.
The condition is the rate, not the count. One star share above eight percent over the trailing seven days, on a base of at least two hundred reviews so a quiet week cannot trip it. The threshold comes out of your own history, which you have from the first day.
What that buys you is a week of warning instead of a month of post-mortem. Your TrustScore weights recent reviews more heavily than old ones, so a bad fortnight moves the public number in a way a bad year three years ago no longer can.
None of that machinery is ours. The scheduled query is BigQuery’s, the alert is whatever your team already runs, and the only part Reviewflowz owns is that the rows are there and current. That is the right split, and it is why this works on a stack you have already chosen.
Scheduled query
trustpilot_one_star_watch
-- writes a row only when the trailing week breaks it
SELECT CURRENT_DATE() AS checked_on,
ROUND(100 * COUNTIF(score = 1)
/ COUNT(*), 1) AS pct_one_star
FROM `northwind-analytics.reviews.trustpilot_reviews`
WHERE review_date >= CURRENT_DATE() - 7
HAVING COUNT(*) >= 200 AND pct_one_star > 8.0checked_on 2026-08-24
Threshold 8.0, base 233 reviews
Nothing fires on a quiet week. The two hundred review floor is what stops a slow Tuesday from reading as a crisis.
A table, not a report
What lands is a table, not a dashboard somebody else designed. One row per Trustpilot review across a fixed 28-column header: the review id, the profile, the date, the score, the title, the full text, the language, the tags and a link back to the review. Plain columns, nothing to reverse engineer.
The review text is a STRING like any other, so the complaint that keeps coming back is a WHERE clause instead of a memory. Everyone knows the courier is a problem because someone remembers a review that said so. Memory keeps the vivid review and drops the count. A table keeps the count.
Tags arrive filled. Reviewflowz reads each review in whatever language it was written in and files it under the theme it is actually about, so a French review about a livraison and an English one about a delivery add up to one number rather than two.
One thing you will not find, said out loud rather than left for you to discover: your replies. The synced schema carries the review, not the reply underneath it, and there are no per-platform tabs. Reply coverage is the Review API, not this table.
northwind-analytics.reviews.trustpilot_reviews
28 cols
The header is fixed at 28 columns, with no per-platform tabs. There is no reply column either, so your replies are not in this table.
How the rows actually get into BigQuery
There is no BigQuery destination to pick, and this page will not pretend there is. What Reviewflowz gives you is a Google Sheet in your own Drive that it keeps current: a new Trustpilot review is a new row at the top within half an hour, and an edit updates its own row.
BigQuery reads a Drive-backed sheet as an external table. One CREATE EXTERNAL TABLE against the sheet URL and trustpilot_reviews exists in your dataset, queries like any other table, and is right at query time because it reads the sheet, not a copy. Nothing to schedule, nothing to keep alive.
If you would rather have a native partitioned table, the Review API is the other road. Poll it or take the webhook, then load it with the job you already run for every other source. Calling that a job rather than a checkbox is the point.
Either way the history comes with it. Seeding past Trustpilot reviews is one step on a paid plan, so the baseline your threshold needs exists on day one. Connect the profile through Trustpilot Business and it is checked every fifteen minutes.
Trustpilot reviews then 
Sheet in your Drive every 30 min then External table at query time Trustpilot reviews then Native table your load job Road A, run once CREATE EXTERNAL TABLE
reviews.trustpilot_reviews
OPTIONS (
format = 'GOOGLE_SHEETS',
uris = ['https://docs.google.com/...'],
sheet_range = 'Reviews'
)Your project, your rules
The table lives in your Google Cloud project, in the region you picked, under the IAM you already run and the retention policy you already wrote. Granting an analyst access is the same few clicks as every other dataset in there, and taking it away again is the same few clicks back.
You choose what flows out, and the controls are deliberately few: which Trustpilot profiles, which ratings, and whether a review has to have text written on it to count. One to three stars into an escalation table, everything into the analytics one, two syncs from the same account and no filter anyone maintains by hand.
What there is not: a keyword filter, and a language filter. Neither exists on a Trustpilot sync, and both are better done in SQL anyway, where changing your mind costs a WHERE clause rather than a resync.
One thing worth knowing before you size anything. Trustpilot volume is invitation-driven, so it arrives in the shape of your send schedule. A profile that looks quiet all month collects most of its rows in the days after each invitation batch.
Your Google Cloud project
northwind-analytics.reviews
Location
europe-west2
Access
your IAM
Retention
your policy
What flows in
northwind.com northwind.de There is no keyword filter and no language filter on a Trustpilot sync. Both are a WHERE clause once the rows are in the warehouse.
A review count is trivia. Reviews per thousand orders is an instrument.
Set up in under five minutes. No demo required.
Is there a native BigQuery connector?
No, and it would be dishonest to imply one. Reviewflowz syncs a Google Sheet in your own Drive, and BigQuery reads that sheet as an external table with a single CREATE EXTERNAL TABLE statement. If you would rather have a native partitioned table, the Review API plus a scheduled load job gets you there. Both are ordinary work for a data team, and neither needs a demo.
Our Trustpilot reviews have no order id. How does the join to orders work?
On time, not on a key. Trustpilot does not hand you an order reference on a service review, so you bucket reviews by the week they were posted and join that to orders by the week they shipped. That is enough to find the week that went wrong and to turn a count into a rate. It is not enough to name the individual order behind a single review, and no tool reading Trustpilot can do that for you.
How current is the table?
A Trustpilot profile connected through Trustpilot Business is checked every fifteen minutes; one added by its URL is checked a few times a day. The sheet is written every 30 minutes. An external table reads the sheet at query time, so a query you run now sees whatever the last sync wrote. A load job into a native table is as current as its schedule.
Which columns does the table have?
A fixed 28-column header: the review id, the profile, the platform, the title, the full review text, the score, the link, the review date, the detected language, the reviewer name and the tags, among others. There is no reply column, so your replies are not in the table, and there are no per-platform tabs. Rename the columns in a view over the external table if you want warehouse-style names.
Does the first load include our Trustpilot history?
Seeding past reviews is a paid-plan step. Connect the profile through Trustpilot Business and the backfill runs to your plan’s limit; a profile added by its URL brings in its most recent couple of hundred reviews. During the free trial new reviews arrive as they are posted, and the history loads once you subscribe.
What happens when a reviewer edits their review after we fix the problem?
The row updates in place with the new score and text, so a query over it moves with the correction rather than holding a version of the truth Trustpilot no longer shows. New reviews are inserted at the top, so the newest row is always the first one.
Do I need a demo to get started?
No. Connect your Trustpilot profile and the Google Sheet yourself in under five minutes, then run one CREATE EXTERNAL TABLE against it. 14 day free trial, no credit card.