Your reply-rate league table may compare different fractions of each firm's review history.
You're about to publish which firms answer customers most often. The input looks reassuring: the same maxReviewsPerPro for everyone.
Dataspryva's professional-directory Actor applies that cap separately to each professional, with a default of 20. Equal review caps don't establish equal review-history coverage.
Even the full-history setting stops at 1,024.
The awkward part is the denominator. “Replies divided by reviews” sounds settled until you've to explain which reviews made it into the division.
Which review history does your percentage describe?
Start by naming the question your chart actually answers.
“Which firms have the highest proportion of captured reviews with a visible professional reply?” is an extract-level question. “Which firms have answered the greatest proportion of their lifetime reviews?” needs complete historical coverage and a defensible meaning of lifetime.
Those questions can produce different rankings.
Requesting attached reviews gives you reviews, reviewsFetched and reviewsTotalAvailable. Keep those counters beside the review rows: flattening the array and discarding its parent record throws away the evidence needed to inspect your denominator.
Example collection settings below request a bounded professional directory with attached reviews, then retrieve every emitted dataset item.
import os
import pandas as pd
from apify_client import ApifyClient
run_input = {
"mode": "search",
"country": "uk",
"city": "London",
"profession": "interior-designer",
"sortBy": "relevance",
"includeReviews": True,
"maxReviewsPerPro": 50,
"fetchDetails": True,
"maxItems": 100,
}
client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
"dataspryva/houzz-pro-directory-finder-by-city-and-category"
).call(run_input=run_input)
if run["status"] != "SUCCEEDED":
raise RuntimeError(f"Collection status: {run['status']}")
items = []
for item in client.dataset(run["defaultDatasetId"]).iterate_items():
items.append(item)
That input defines a directory extract, not a census of firms. sortBy orders professional directory results; it doesn't establish the order of attached reviews.
Calling those reviews “the latest” would add a fact the extract hasn't earned.
Equal caps leave unequal holes
A firm with less available history than the cap may contribute its whole available history. A firm with more history can't.
The same setting therefore creates different inclusion mechanisms:
Available history within the cap
-> potentially complete available history
Available history beyond the cap
-> a selected subset, with review ordering unestablished
For the latter group, the excluded reviews are the history outside the returned subset. Without an established selection order, you can't identify their period, sentiment or likelihood of receiving a reply.
That matters if response behaviour changes over time. A change in staffing, workload or reply policy could make different historical slices produce different rates. Those are mechanisms to investigate, not findings about these firms.
The professional roster has its own boundary. A capped, relevance-ordered directory can omit firms before review collection begins. Removing review truncation would still leave that roster-selection problem.
A tidy percentage can hide both layers. Percent signs are unusually good at looking finished.
Keep the coverage evidence when you flatten
Empty fields are omitted, so an absent review array needs different treatment from an explicitly empty array. Likewise, an unavailable counter must remain unknown rather than quietly becoming zero.
Preserve the parent record and identify each attached review without inventing missing values.
professional_rows, review_rows = [], []
review_fields = ("reviewId", "createdAt", "proResponse", "proResponseAt")
for parent_row, item in enumerate(items):
attached = item.get("reviews")
review_list = attached if isinstance(attached, list) else []
professional_rows.append({
"parent_row": parent_row,
"proId": item.get("proId"),
"reviewsFetched": item.get("reviewsFetched"),
"reviewsTotalAvailable": item.get("reviewsTotalAvailable"),
"scrapedAt": item.get("scrapedAt"),
"reviews_attached": isinstance(attached, list),
"review_objects_valid": all(
isinstance(review, dict) for review in review_list
),
})
for review in review_list:
if isinstance(review, dict):
review_rows.append({
"parent_row": parent_row,
**{field: review.get(field) for field in review_fields},
})
professionals = pd.DataFrame(
professional_rows,
columns=[
"parent_row", "proId", "reviewsFetched",
"reviewsTotalAvailable", "scrapedAt",
"reviews_attached", "review_objects_valid",
],
)
reviews = pd.DataFrame(
review_rows, columns=["parent_row", *review_fields]
)
professionals["parent_row"] = professionals["parent_row"].astype("int64")
reviews["parent_row"] = reviews["parent_row"].astype("int64")
review_data = reviews.assign(
review_key=reviews["reviewId"].astype("string").str.strip().replace(
"", pd.NA
),
reply_observed=reviews["proResponse"].map(
lambda value: isinstance(value, str) and bool(value.strip())
),
)
Here, a reply means non-empty extracted response text. An omitted proResponse contributes no observed reply; it doesn't prove that the business never answered through another channel.
Next, parse counters conservatively and reconcile the flattened rows against the reported fetched count.
def parse_count(values):
text = values.astype("string").str.strip()
valid = text.str.fullmatch(
r"(?:[0-9]+|[0-9]{1,3}(?:,[0-9]{3})+)(?:\.0+)?",
na=False,
)
return pd.to_numeric(
text.where(valid).str.replace(",", "", regex=False),
errors="coerce",
).astype("Float64")
flat_stats = review_data.groupby("parent_row").agg(
flat_count=("reviewId", "size"),
unique_ids=("review_key", "nunique"),
reply_count=("reply_observed", "sum"),
)
audit = professionals.join(flat_stats, on="parent_row")
count_columns = ["flat_count", "unique_ids", "reply_count"]
audit[count_columns] = audit[count_columns].fillna(0).astype("int64")
audit["fetched"] = parse_count(audit["reviewsFetched"])
audit["available"] = parse_count(audit["reviewsTotalAvailable"])
audit["pro_key"] = audit["proId"].astype("string").str.strip()
audit["reconciles"] = (
audit["reviews_attached"]
& audit["review_objects_valid"]
& audit["flat_count"].eq(audit["fetched"]).fillna(False)
)
audit["coverage"] = audit["fetched"].div(
audit["available"].where(audit["available"].gt(0))
)
audit["rate_eligible"] = (
audit["reconciles"]
& audit["flat_count"].gt(0)
& audit["unique_ids"].eq(audit["flat_count"])
& audit["available"].ge(audit["fetched"]).fillna(False)
& audit["pro_key"].notna()
& audit["pro_key"].ne("").fillna(False)
& ~audit["pro_key"].duplicated(keep=False)
)
audit["complete_available_history"] = (
audit["rate_eligible"]
& audit["fetched"].eq(audit["available"]).fillna(False)
)
The strict checks keep duplicated professional records, missing review identifiers and inconsistent counters out of the rate calculation. Inspect those exclusions before publication; dropping them silently would create another selection step.
The coverage ratio compares fetched reviews with reported available reviews. Don't substitute reviewCount when availability is missing: that would switch the denominator without establishing that the counters describe the same population.
| Audit finding | Defensible interpretation |
|---|---|
| Fetched history is below reported availability | The observed rate describes a partial history |
| Counts reconcile and match availability | The extract matches the history reported available at collection |
| Counters are missing or inconsistent | Historical coverage remains unresolved |
Matching availability is useful evidence. It still can't establish that every review ever published remains publicly available.
Pooling reviews changes whose behaviour dominates
Once the rows reconcile, there's another choice hiding inside “average reply rate”.
Pooling reviews gives more weight to firms contributing more reviews. Averaging firm-level rates gives each included firm equal weight, including firms with very different denominator sizes.
Neither calculation answers the other's question.
Calculate both with explicit names, keeping the number of included firms and reviews alongside them.
import duckdb
eligible_profiles = audit.loc[audit["rate_eligible"]].copy()
eligible_profiles["observed_reply_rate"] = (
eligible_profiles["reply_count"] / eligible_profiles["flat_count"]
)
observed_rates = duckdb.sql("""
SELECT
count(*) AS included_firms,
sum(flat_count) AS included_reviews,
sum(reply_count)::DOUBLE
/ nullif(sum(flat_count), 0)
AS review_weighted_observed_reply_rate,
avg(observed_reply_rate)
AS equally_weighted_observed_reply_rate
FROM eligible_profiles
""").df()
These remain observed-extract metrics, including for profiles with incomplete histories.
A large difference between the rates signals that weighting matters. Agreement doesn't clear the sampling problem: both calculations can inherit the same omissions.
Profiles without any attached reviews have no defined review-level response rate. They belong in the roster accounting, rather than being assigned a zero response rate.
A calendar window doesn't equalise reply opportunities
Restricting reviews to a common calendar window changes the target to behaviour during that window. It doesn't recover missing reviews from a capped history.
There's also follow-up time. A review posted shortly before collection has had less opportunity to receive a reply than an older review.
Inspect observed date ranges, review ages at collection and reply delays before choosing a time-window comparison.
timeline = review_data.merge(
eligible_profiles[["parent_row", "scrapedAt"]],
on="parent_row",
how="inner",
)
for date_field in ("createdAt", "proResponseAt", "scrapedAt"):
timeline[date_field] = pd.to_datetime(
timeline[date_field],
errors="coerce",
utc=True,
format="mixed",
)
timeline["review_age_days"] = (
timeline["scrapedAt"] - timeline["createdAt"]
).dt.total_seconds().div(86400)
timeline["reply_delay_days"] = (
timeline["proResponseAt"] - timeline["createdAt"]
).dt.total_seconds().div(86400)
timeline["invalid_timing"] = (
timeline["review_age_days"].lt(0)
| timeline["reply_delay_days"].lt(0)
| timeline["proResponseAt"].gt(timeline["scrapedAt"])
)
timeline["reply_delay_days"] = timeline["reply_delay_days"].where(
timeline["reply_observed"] & ~timeline["invalid_timing"]
)
timeline["review_age_days"] = timeline["review_age_days"].where(
timeline["review_age_days"].ge(0)
)
date_audit = timeline.groupby("parent_row").agg(
parseable_review_dates=("createdAt", "count"),
observed_start=("createdAt", "min"),
observed_end=("createdAt", "max"),
youngest_review_days=("review_age_days", "min"),
oldest_review_days=("review_age_days", "max"),
median_observed_reply_delay_days=("reply_delay_days", "median"),
timing_flags=("invalid_timing", "sum"),
)
Compare parseable date counts with flattened counts. An attractive date range can conceal undated rows.
Reply delays describe captured replies with usable timestamps. They exclude reviews still awaiting a reply, so their median can't establish how long every customer waits.
For a fixed follow-up comparison, define the response horizon before looking at rankings, retain reviews old enough to have reached it, and count replies within that horizon. Describe the exclusions, including reviews whose dates can't be interpreted.
Freeze the firms before expanding their histories
For this worked example, the collection tool is Houzz Pro Directory Finder by City & Category.
A stronger design separates roster selection from review retrieval. Freeze the professional profiles you intend to compare, then request their available histories.
Example revision below reuses the captured profile URLs as a fixed roster and replaces the smaller per-professional review cap with the full-history setting.
profile_urls = sorted({
item["profileUrl"].strip()
for item in items
if isinstance(item.get("profileUrl"), str)
and item["profileUrl"].strip()
})
history_input = {
"mode": "profiles",
"profileUrls": profile_urls,
"includeReviews": True,
"maxReviewsPerPro": 0,
"fetchDetails": True,
"maxItems": run_input["maxItems"],
}
This prepares a collection specification; it doesn't establish complete coverage. Account for roster entries without usable profile URLs, and reconcile the returned profiles with the frozen roster when collecting.
The roster above also inherits the original directory selection. A broader claim about firms in a city needs a separately justified roster and explicit treatment of directory limits, ranking and filters.
For a shared-window analysis, collect sufficient history first, then apply the same date boundaries locally. A partial extract whose observed dates span your window still can't prove that every review inside it was captured.
Where histories remain incomplete, report the observed-extract rate and its coverage beside each firm. Restricting a comparison to complete available histories is another possible target, but it changes which firms qualify and must be named accordingly.
Visible replies aren't a customer-service score
These fields describe visible review and reply behaviour. They can't establish whether a customer received a private response, whether a complaint was resolved, or why a firm chose to reply.
A publication note should identify the professional roster, collection timing, review eligibility, coverage exclusions and weighting. If the headline concerns a shared period, name its boundaries and follow-up rule too.
“Lifetime response rate” demands more than a division that runs without errors.
Your headline needs a denominator that can survive being named.
Drafted with AI assistance and reviewed for accuracy.
Top comments (0)