Your July review cohort isn't a July travel cohort

July reviews won't tell you what happened on July trips.

You're preparing a summer campaign, and someone asks what July visitors complained about. You reach for reviewMonth: "JULY". The answer looks conveniently close.

The Actor filters by submission month, and its date records the submission timestamp. There's no year selector or visit date. Labeling that extract “July visitors” quietly changes the question your report answers.

Which July did you actually select?

The reusable problem is choosing the right event clock. A review submission and a hotel stay represent different events. A month selector can't bridge them.

The review Actor exposes the submission clock because that's what its public review data provides. Your consumer needs to preserve that meaning through ingestion, SQL, and the campaign brief.

Example request for a bounded hotel review extract:

import { ApifyClient } from 'apify-client';
import { z } from 'zod';

const run_input = {
  mode: 'reviews',
  startUrls: ['675616'],
  maxItems: 1,
  maxReviews: 100,
  reviewSort: 'MOST_RECENT',
  reviewMonth: 'JULY',
  reviewRating: 'ANY',
  reviewLanguage: 'ANY',
  reviewTripType: 'ANY',
};

Keep the target explicit when comparing campaign periods. Discovering hotels afresh can change the places under comparison, even when your date logic stays correct.

Leave reviewKeyword unset when you need a denominator for theme analysis. Selecting only reviews containing a complaint term would make the resulting proportion answer a much narrower question.

The request carries a sampling boundary

The Actor sends review filters to the upstream server. It reads the public review endpoint using offset pagination, with at most 20 reviews per request, until a collection limit or the place total stops it.

That saves downloading unrelated reviews. It also means a later year filter can only remove records from the bounded selection already collected.

With MOST_RECENT, newer matching submissions can consume the requested allowance before older years enter your extract. Finding fewer older reviews afterward doesn't establish that guests submitted less feedback then.

Record your selection before interpreting it.

Example analysis settings use explicit UTC boundaries around the submission years you want to compare:

const analysisWindow = {
  start: '2025-01-01T00:00:00Z',
  end: '2027-01-01T00:00:00Z',
  timezone: 'UTC',
};

const client = new ApifyClient({
  token: process.env.APIFY_TOKEN,
});

const run = await client
  .actor('nimbryxo-studio/tripadvisor-hotel-restaurant-and-attraction-finder')
  .call(run_input);

const { items } = await client
  .dataset(run.defaultDatasetId)
  .listItems();

const manifest = {
  datasetId: run.defaultDatasetId,
  runStatus: run.status,
  requestedInput: run_input,
  analysisWindow,
  datasetRead: 'one listItems response',
  population: 'submission-month feedback',
  completeness: 'not established',
};

A completed run isn't a census certificate. When some targets or pages fail, the Actor can retain available records and report partial counts in its run status.

Review those counts before publishing a comparison. Expanding dataset retrieval also requires checking that your consumer has read the complete dataset, rather than treating a response page as the whole extract.

Keep the clock and the raw value

Empty fields are omitted. A missing text therefore needs a different interpretation from a review that doesn't mention your chosen theme.

A missing or invalid timestamp has a different consequence: you can't assign that review to a submission year. Keep it available for inspection, outside the dated cohort.

This cell validates the fields the analysis actually uses and preserves rejected records:

const Review = z.object({
  id: z.string().min(1),
  placeId: z.string().min(1),
  date: z.string().min(1).optional(),
  text: z.string().min(1).optional(),
  tripType: z.string().min(1).optional(),
});

const Timestamp = z.string().datetime({ offset: true });

const quarantine: Array<{ reason: string; raw: unknown }> = [];
const datedReviews: Array<{
  review: z.infer<typeof Review>;
  submittedAt: Date;
}> = [];

for (const raw of items) {
  const parsed = Review.safeParse(raw);
  if (!parsed.success) {
    quarantine.push({ reason: 'invalid_review_fields', raw });
    continue;
  }

  const timestamp = Timestamp.safeParse(parsed.data.date);
  if (!timestamp.success) {
    quarantine.push({ reason: 'missing_or_invalid_submission_date', raw });
    continue;
  }

  const submittedAt = new Date(timestamp.data);
  if (!Number.isFinite(submittedAt.getTime())) {
    quarantine.push({ reason: 'unusable_submission_date', raw });
    continue;
  }

  datedReviews.push({ review: parsed.data, submittedAt });
}

Don't substitute scrapedAt when date fails validation. That would turn collection timing into customer feedback timing, usually without changing the dashboard's reassuring title.

Validation protects meaning here, not merely TypeScript types.

Build a cohort without inventing a stay

Use UTC as an explicit analysis policy. The month selector doesn't establish an upstream timezone policy, so check the returned timestamp against your own boundary too.

Keep both representations: the original timestamp for audit, and the normalized timestamp for comparison.

server-side month filter
          |
bounded review selection
          |
valid submission timestamp
          |
UTC month + explicit year window
          |
submission-month feedback

The following cell also protects aggregation from repeated identities in its input. The Actor deduplicates reviews by review ID within its collection; that doesn't make repeated downstream deliveries new observations.

const windowStart = new Date(analysisWindow.start);
const windowEnd = new Date(analysisWindow.end);
const seen = new Set<string>();

const exclusions = {
  duplicateIdentity: 0,
  outsideUtcJuly: 0,
  outsideYearWindow: 0,
};

const analysisRows = datedReviews.flatMap(({ review, submittedAt }) => {
  const identity = JSON.stringify([review.placeId, review.id]);
  if (seen.has(identity)) {
    exclusions.duplicateIdentity++;
    return [];
  }
  seen.add(identity);

  if (submittedAt.getUTCMonth() !== 6) {
    exclusions.outsideUtcJuly++;
    return [];
  }

  if (submittedAt < windowStart || submittedAt >= windowEnd) {
    exclusions.outsideYearWindow++;
    return [];
  }

  return [{
    place_id: review.placeId,
    review_id: review.id,
    submission_date_raw: review.date,
    submitted_at: submittedAt.toISOString(),
    submission_year: submittedAt.getUTCFullYear(),
    submission_month: submittedAt.getUTCMonth() + 1,
    traveler_type: review.tripType ?? null,
    review_text: review.text ?? null,
  }];
});

These snake-case names belong to your analysis model. They aren't additional source fields.

For persisted snapshots, use dataset identity plus place and review identity as the membership key. Reprocessing the same dataset should replace that membership, rather than append another observation.

A new extraction can remain a separate snapshot. Otherwise, a canonical review upsert can erase which selection originally supported a campaign decision.

What broke was the report's contract

The failure is the chain of assumptions between selection and presentation.

Consumer assumption	Behavior that breaks it	Consumer design
July means a July stay	The filter uses submission month	Name the event in the table and chart
A month selection identifies a year	The request has no year selector	Apply explicit timestamp boundaries
Sorted results cover the period	Pagination stops at collection limits	Treat counts as extract counts
Every review can enter theme analysis	Empty fields are omitted	Count text availability separately
Source deduplication prevents repeat ingestion	Collection deduplication doesn't govern your database	Enforce snapshot membership keys

Traveler type can help separate themes reported by different reviewer segments. It remains reviewer-reported context, without establishing when the trip occurred.

The published hometown doesn't solve the timing problem either. Turning it into a travel-origin segment would require another analytical assumption.

Keep those assumptions visible. Attractive column names are surprisingly good at hiding them.

Give the query its selection history

Start with a modest comparison: received reviews, reviews with text, and reviews mentioning a noise term, grouped by place, submission year, and traveler type.

A term match measures a mention. It doesn't establish a complaint: a reviewer can praise the absence of noise. Use the result to select text for inspection before choosing campaign claims.

This cell builds a parameterized query configuration for an existing pg worker and an export envelope that can also travel through n8n:

const analysisExport = {
  manifest: {
    ...manifest,
    exclusions,
    quarantinedRecords: quarantine.length,
    includedRecords: analysisRows.length,
    themeMethod: 'case-insensitive whole-word noise or noisy mention',
  },
  rows: analysisRows,
  quarantine,
};

const comparisonQuery = {
  text: String.raw`
    SELECT
      place_id,
      submission_year,
      traveler_type,
      count(*) AS received_reviews,
      count(review_text) AS reviews_with_text,
      count(*) FILTER (
        WHERE review_text ~* '\m(noise|noisy)\M'
      ) AS noise_mention_reviews,
      $2::jsonb AS selection_manifest
    FROM jsonb_to_recordset($1::jsonb) AS r(
      place_id text,
      submission_year integer,
      traveler_type text,
      review_text text
    )
    GROUP BY place_id, submission_year, traveler_type
    ORDER BY place_id, submission_year, traveler_type NULLS FIRST
  `,
  values: [
    JSON.stringify(analysisExport.rows),
    JSON.stringify(analysisExport.manifest),
  ],
};

The query needs no staging table. Your worker can execute the configuration and persist the export envelope alongside its results.

Keep the envelope even when the cohort is empty. An empty grouped result carries no row on which to attach selection history.

The worked example uses TripAdvisor Hotel, Restaurant & Attraction Finder. The pattern applies anywhere a source offers a convenient month filter without the event date your business question requires.

Before comparing proportions, inspect coverage by year and text availability. Matching request settings help reproducibility; they can't prove equally complete historical samples.

Before the campaign query ships

• Name the event: submission-month feedback.
• Retain the requested filters, sort, caps, and dataset identity.
• Inspect partial counts before approving the extract.
• Preserve raw timestamps and quarantine unusable dates.
• Keep year boundaries and timezone policy explicit.
• Enforce snapshot membership before counting reviews.
• Read the matched text before calling mentions complaints.

If the campaign decision depends on when guests stayed, the missing input is a visit date. A more polished query can't supply it.