DEV Community: Dataspryva

Your company join can lose newer registrations

Dataspryva — Thu, 12 Nov 2026 08:15:35 +0000

Your enrichment join is losing newer registrations, and the report still needs a company count you can defend. Before blaming registry coverage, look for this: str.replace(r"\D", "", regex=True).

A digits-only cleanup can damage a valid alphanumeric CNPJ before a lookup begins.

Our CNPJ lookup Actor accepts numeric and alphanumeric identifiers, validates their check digits, and skips invalid inputs before making any request. An ingestion bug can therefore exclude companies before source availability becomes relevant.

The cleanup changes more than formatting

Removing punctuation and removing everything except digits used to look like equivalent operations. Letters make the difference expensive.

Keep the original beside each transformation; the Example identifiers below cover both accepted forms.

import os

import duckdb
import pandas as pd
from apify_client import ApifyClient

def normalise_cnpj(values):
    return (
        values.astype("string")
        .str.strip()
        .str.replace(r"[./-]", "", regex=True)
    )

original = pd.Series(
    ["18.236.120/0001-58", "00.000.000/E08G-12"],
    dtype="string",
)
audit = pd.DataFrame()
audit["original_text"] = original
audit["cnpj_key"] = normalise_cnpj(audit["original_text"])
audit["digits_only_key"] = audit["original_text"].str.replace(
    r"\D", "", regex=True
)
audit["integer_key"] = (
    pd.to_numeric(audit["cnpj_key"], errors="coerce")
    .astype("Int64")
    .astype("string")
)
audit["cleanup_changed_key"] = (
    audit["digits_only_key"].ne(audit["cnpj_key"]).fillna(True)
)

The integer conversion makes unparseable identifiers missing and discards leading zeroes from numeric identifiers. The digits-only replacement leaves a string, which can look reassuring while representing a different identifier.

Neither transformation belongs in an identity join. Arithmetic offers nothing useful here.

When investigating your pipeline, substitute the untouched ingestion column for original. Compare its normalised value with the actual key saved immediately before your existing lookup call. Recreating the old cleanup is useful, but preserving what it really submitted is better evidence.

If your first surviving copy is already numeric, converting it back to text can't reconstruct discarded characters or leading zeroes.

Which companies does your join represent?

Since July 2026, new registrations can include letters. Damage correlated with identifier format can therefore become correlated with registration cohort.

It doesn't follow that every newer registration has letters, or that every unmatched company suffered this bug. That's precisely why the audit needs separate stages.

The selection path makes the potential exclusion visible:

your original company list
    -> identifier transformation
    -> validation and deduplication
    -> lookup processing cap
    -> source response
    -> returned company records

Your intended population is the company list you started with. Your enriched dataset represents the subset that survives this path.

Suppose your question is whether newer registrations have a different industry mix. Excluding damaged identifiers changes which registrations contribute to that comparison, even when every returned industry field is correct.

The direction of the distortion depends on the excluded companies. The identifier bug alone can't establish it.

There's another unit-of-analysis trap: cnpjRaiz groups establishments sharing a root. Using it instead of the full cnpj changes which records you count as distinct.

A tidy join can still answer the wrong question. Tidy joins are quite accommodating that way.

Keep an audit boundary before validation

The normalised key should preserve the full 14-character identifier. A character-pattern check is useful for locating damage, but it can't establish valid check digits.

This cell labels identifier format, flags structural failures and builds a distinct submission list without deleting the audit rows.

audit["shape_ok"] = audit["cnpj_key"].str.fullmatch(
    r"[A-Za-z0-9]{14}", na=False
)
audit["identifier_format"] = pd.Series(
    "unclassified", index=audit.index, dtype="string"
)
audit.loc[
    audit["shape_ok"]
    & audit["cnpj_key"].str.fullmatch(r"[0-9]{14}", na=False),
    "identifier_format",
] = "numeric"
audit.loc[
    audit["shape_ok"]
    & audit["cnpj_key"].str.contains(r"[A-Za-z]", na=False),
    "identifier_format",
] = "alphanumeric"

request_keys = (
    audit.loc[audit["shape_ok"], "cnpj_key"].drop_duplicates().tolist()
)
audit["sent"] = audit["cnpj_key"].isin(request_keys)

Keep structural failures in the denominator and in a review queue. Dropping them before calculating retention makes the cleanup grade its own homework.

The Actor performs check-digit validation after this screening. Passing shape_ok means only that the string has the expected character shape.

The useful distinctions are:

Audit boundary	What it establishes
Original text versus normalised key	Whether removing formatting preserved identity
Normalised key versus saved submission key	Whether upstream handling changed the identifier
Submitted key versus returned `cnpj`	Whether that submitted identity received a record

Make the cap match the lookup list

In lookup mode, maxItems limits CNPJs processed, rather than promising a matching number of returned records. Its default is 1000.

A capped run adds another selection boundary. If you don't know which keys were processed, you can't classify every unmatched key as a source failure.

This cell sets the cap to the distinct submission count and refuses a list outside the permitted range.

if not 1 <= len(request_keys) <= 100000:
    raise ValueError("Use a non-empty batch within the lookup limit.")

run_input = {
    "mode": "lookup",
    "cnpjs": request_keys,
    "maxItems": len(request_keys),
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/brazilian-cnpj-bulk-lookup-and-partner-records"
).call(run_input=run_input)

if run["status"] != "SUCCEEDED":
    raise RuntimeError(f"Lookup ended with status {run['status']}")

items = []
for item in client.dataset(run["defaultDatasetId"]).iterate_items():
    items.append(item)

For a larger list, split distinct keys into disjoint batches and retain batch membership alongside each key. Silent slicing is another way to manufacture a sample without admitting you've sampled.

cnpjText can extract, validate and deduplicate identifiers from pasted text. It can't recover letters your spreadsheet export has already removed.

Locate losses with an outer join

Empty output fields are omitted. Build the expected columns explicitly, normalise returned identifiers consistently, and keep records without a key separate.

This cell reconciles original input rows with returned identities and parses available activity-start dates.

returned = pd.DataFrame(items).reindex(
    columns=["cnpj", "cnpjFormatado", "dataInicioAtividade", "source"]
)
returned["cnpj_key"] = normalise_cnpj(returned["cnpj"]).fillna(
    normalise_cnpj(returned["cnpjFormatado"])
)
returned["activity_start"] = pd.to_datetime(
    returned["dataInicioAtividade"],
    format="%Y-%m-%d",
    errors="coerce",
)
returned["returned_record"] = True
audit["input_record"] = True

unkeyed_returns = returned.loc[returned["cnpj_key"].isna()].copy()
reconciliation = audit.merge(
    returned.loc[returned["cnpj_key"].notna()],
    on="cnpj_key",
    how="outer",
    validate="many_to_one",
)
reconciliation["input_record"] = (
    reconciliation["input_record"].astype("boolean").fillna(False)
)
reconciliation["returned_record"] = (
    reconciliation["returned_record"].astype("boolean").fillna(False)
)

The merge preserves unmatched input rows and unexpected returned identities. Its validation also prevents duplicate returned keys from quietly multiplying your input rows.

An unmatched input row still needs interpretation. A structural failure never entered this submission list; a submitted key without a returned record needs further reconciliation.

The run summary separates invalid, not-found, duplicate and unavailable counts. Keep those categories separate when explaining the gap. Aggregate counts don't assign a cause to each missing key, and a duplicate submission isn't an additional missing company.

Report retention with its denominator

For the Brazilian CNPJ Bulk Lookup & Partner Records Scraper, the useful first comparison is retention by identifier format.

This cell calculates row-level retention against the original input rows, including those excluded by structural screening.

input_rows = reconciliation.loc[
    reconciliation["input_record"]
].copy()
input_rows["matched"] = input_rows["returned_record"]

format_retention = duckdb.sql("""
    SELECT
        identifier_format,
        count(*) AS original_rows,
        count(*) FILTER (WHERE sent) AS submitted_rows,
        count(*) FILTER (WHERE matched) AS matched_rows,
        avg(CAST(matched AS DOUBLE)) AS row_retention
    FROM input_rows
    GROUP BY identifier_format
    ORDER BY identifier_format
""").df()

Repeated identifiers remain repeated in this metric. That's appropriate for measuring enrichment of your input rows, but a distinct-registration measure needs a denominator of distinct, reviewed identities.

Report the unit, the submission boundary, structural exclusions and unresolved submitted keys alongside retention. A percentage without those details leaves your manager guessing what survived long enough to be counted.

A format gap is a diagnostic signal. It doesn't establish that the Actor or the registry treats either format differently.

A returned date can't date the missing companies

dataInicioAtividade lets you describe activity-start cohorts among records that returned a usable date.

This cell counts that observable subset without assigning dates to missing records.

dated_returns = returned.loc[returned["activity_start"].notna()].copy()
observed_cohorts = (
    dated_returns.groupby(
        dated_returns["activity_start"].dt.to_period("M")
    )
    .size()
    .rename("returned_records")
)

That's cohort composition, not cohort retention. Calculating retention by dates learned only through successful enrichment would exclude the failures you're trying to explain.

For a date-based retention comparison, you need dates available independently before enrichment. Limit that comparison to input records with those dates, disclose how many lack them, and keep activity-start date distinct from identifier issuance.

The mirrors reflect a registry published monthly, so very recent changes may also be absent. A missing return alone can't distinguish snapshot lag from other lookup failures.

Before explaining who disappeared, establish which identifiers your pipeline allowed to arrive.

Converting floor area cannot repair the wrong sample

Dataspryva — Wed, 11 Nov 2026 08:21:48 +0000

Your cross-market price chart is nearly ready to publish, and matching minSize and maxSize inputs look like evidence that you're comparing similarly sized homes.

Then you check the units.

Search size bounds use square feet in Singapore and Malaysia, but square metres in Thailand. The returned floorAreaSqft and floorAreaSqm fields standardise the units after selection; they can't recover homes excluded by the original search.

The filter has already chosen your sample

The tempting assumption is that matching numeric inputs produce comparable cohorts, provided you normalise the output afterwards.

The selection happens earlier:

physical interval → local-unit search bounds → selected listings
                                                       ↓
                                             converted area fields

If the local-unit translation is wrong, the converted fields can describe the wrong cohort perfectly well.

Bad selection can have excellent formatting.

That matters before you calculate asking-price differences, because changing the floor-area composition of a property sample can move its price summary without establishing any pricing difference between comparable homes.

Even a correctly matched floor-area band leaves location, property type, tenure and condition unresolved. It fixes a specific comparison problem.

Make rounding part of the cohort definition

Start with a physical interval, then translate it into each market's input units.

Because minSize and maxSize accept integers, conversion also requires a rounding policy. We use outward rounding: round the lower bound down and the upper bound up, then apply the exact common interval to returned areas.

This avoids narrowing the intended interval through rounding alone, while deliberately admitting a boundary fringe that the later check can identify and remove.

The Example below defines a common interval from 50 to 100 square metres and generates reviewable search inputs with explicit collection limits.

import json
import os
import re
from decimal import Decimal, ROUND_CEILING, ROUND_FLOOR

import pandas as pd
from apify_client import ApifyClient

lower_sqm, upper_sqm = Decimal("50"), Decimal("100")
sqm_per_input_unit = {
    "SG": Decimal("0.09290304"),
    "MY": Decimal("0.09290304"),
    "TH": Decimal("1"),
}

run_inputs = {}
for market, factor in sqm_per_input_unit.items():
    run_input = {
        "mode": "search",
        "country": market,
        "listingType": "sale",
        "propertyType": "condo",
        "minSize": int(
            (lower_sqm / factor).to_integral_value(rounding=ROUND_FLOOR)
        ),
        "maxSize": int(
            (upper_sqm / factor).to_integral_value(rounding=ROUND_CEILING)
        ),
        "maxItems": 100,
        "maxPages": 50,
        "sort": "recommended",
        "includeDetails": True,
    }
    run_inputs[market] = run_input

input_json = json.dumps(run_inputs, indent=2)
input_json

Retain that JSON beside the pull. The conversion factor is a unit definition, not an estimate from the listings.

Outward rounding addresses input precision; it doesn't establish how consistently a listing's published area matches the search index, so the returned-area check still matters.

The search still has boundaries

A matching size interval is only part of the coverage statement. These boundaries determine what your comparison can include:

Dimension	Coverage and limit
Geography	Search supports Singapore, Malaysia and Thailand. Indonesia is unavailable.
Categories	Sale and rental searches are separate. Unsupported property types are ignored with a status note.
Other filters	Singapore districts don't apply elsewhere; transit-distance filtering applies to Singapore and Thailand.
Collection depth	Pagination stops at the item limit, page limit or the site's own last page. Repeated promoted cards are removed.
Time	Searches collect live listings. `postedOn` and `scrapedAt` describe different moments; they don't establish a historical inventory.
Area fields	Original floor-area values and units remain available alongside converted values. Land-area fields describe a separate quantity.
Conditional detail	Empty fields are omitted. Detail enrichment depends on what the source publishes; protected Thai detail pages can fall back to search-card records with detail-only fields omitted.

Read the run's status message for ignored filters and fallback records. Identical-looking inputs can conceal different effective constraints.

The chosen sort also matters when collection stops early: a capped search ordered by recommendation is a selected slice of available results, so its area distribution can't establish the distribution of every matching listing.

Keep collection time separate from listing age. A publication timestamp doesn't make a current pull a reconstruction of what was available then.

Keep the pull identity beside each row

Fetch each market separately and attach its search identity locally. Here, pull_country is an audit column we create, rather than a guess based on an address or domain.

This cell streams each dataset so the dataset API's page size doesn't quietly become another collection limit.

client = ApifyClient(os.environ["APIFY_TOKEN"])
records = []
run_audit = []

for market, run_input in run_inputs.items():
    run = client.actor(
        "dataspryva/southeast-asia-property-listings-by-country"
    ).call(run_input=run_input)

    run_audit.append({
        "pull_country": market,
        "dataset_id": run["defaultDatasetId"],
        "run_status": run["status"],
    })
    if run["status"] != "SUCCEEDED":
        raise RuntimeError(f"Inspect the incomplete {market} run before comparing.")

    for item in client.dataset(run["defaultDatasetId"]).iterate_items():
        records.append({**item, "pull_country": market})

A successful run status permits the audit to continue. It doesn't certify market completeness or consistent floor-area measurement.

Keeping the pull identity also gives an empty market somewhere to appear in the coverage table, instead of letting it disappear because it contributed no rows.

Absence needs a denominator too.

Measure missing areas and boundary leaks

Keep the original area fields beside the parsed value. Don't fill missing floor area from landArea, even when that produces a satisfyingly complete column.

The parser below accepts numeric values and English-style formatted square-metre strings; it rejects ranges, ambiguous decimal conventions and other labels rather than extracting whichever number happens to appear first.

audit_columns = [
    "pull_country", "recordType", "listingId", "listingUrl",
    "floorArea", "floorAreaUnit", "floorAreaSqft", "floorAreaSqm",
    "landArea", "landAreaUnit", "sourcePage", "postedOn", "scrapedAt",
]
audit = pd.DataFrame.from_records(records).reindex(columns=audit_columns)

area_pattern = re.compile(
    r"\s*(?P<number>(?:\d{1,3}(?:,\d{3})+|\d+)(?:\.\d+)?)"
    r"\s*(?:sqm|sq\.?\s*m|m²)?\s*",
    re.IGNORECASE,
)

def parse_sqm(value):
    match = area_pattern.fullmatch(str(value))
    if match is None:
        return float("nan")
    number = float(match.group("number").replace(",", ""))
    return number if number > 0 else float("nan")

audit["area_sqm"] = audit["floorAreaSqm"].map(parse_sqm).astype("float64")
audit["area_missing"] = audit["floorAreaSqm"].isna()
audit["area_unusable"] = (
    audit["floorAreaSqm"].notna() & audit["area_sqm"].isna()
)
audit["below"] = audit["area_sqm"].lt(float(lower_sqm))
audit["above"] = audit["area_sqm"].gt(float(upper_sqm))
audit["inside"] = audit["area_sqm"].between(
    float(lower_sqm), float(upper_sqm), inclusive="both"
)

Omitted or null areas stay separate from supplied values the parser couldn't use. That distinction matters when exclusion from the comparison depends on whether a usable area was published.

Inspect rejected values before changing the parser. Teaching it to accept a new unit label is different from teaching it to turn an area range into a single home's measurement.

Project records deserve particular attention because the source can describe unit types and floor-area ranges. A range can't establish that a particular home belongs inside your interval.

This cell computes coverage counts and area distributions before and after the common-band restriction.

coverage = (
    audit.groupby("pull_country")
    .agg(
        rows=("pull_country", "size"),
        area_missing=("area_missing", "sum"),
        area_unusable=("area_unusable", "sum"),
        below=("below", "sum"),
        inside=("inside", "sum"),
        above=("above", "sum"),
    )
    .reindex(list(run_inputs), fill_value=0)
    .astype("int64")
)
coverage["inside_share"] = coverage["inside"].div(
    coverage["rows"].replace(0, float("nan"))
)

area_distributions = pd.concat(
    {
        "returned": audit.groupby("pull_country")["area_sqm"].describe(),
        "common_band": audit.loc[audit["inside"]]
        .groupby("pull_country")["area_sqm"].describe(),
    },
    names=["cohort", "pull_country"],
)
coverage, area_distributions

Boundary violations need investigation. The outward rounding intentionally admits a fringe; larger discrepancies need inspection against the original area, unit and listing before you attribute them to the filter.

The retained distributions matter too, because sharing endpoints doesn't ensure that homes cluster similarly within them: a market concentrated near the lower boundary can remain physically different from one concentrated near the upper boundary.

Matching the band is a starting condition for your comparison, not evidence that size composition has stopped mattering.

Check the cost of rounding inward

An alternative policy rounds the lower input upward and the upper input downward. That removes the fringe but can also exclude homes inside the intended physical interval.

The next cell identifies retained records that fit those inward-rounded bounds, then recomputes their area distributions.

inward_edges = {
    market: (
        float((lower_sqm / factor).to_integral_value(
            rounding=ROUND_CEILING
        ) * factor),
        float((upper_sqm / factor).to_integral_value(
            rounding=ROUND_FLOOR
        ) * factor),
    )
    for market, factor in sqm_per_input_unit.items()
}

audit["inside_inward_bounds"] = (
    audit["inside"]
    & audit["area_sqm"].ge(
        audit["pull_country"].map({m: edges[0] for m, edges in inward_edges.items()})
    )
    & audit["area_sqm"].le(
        audit["pull_country"].map({m: edges[1] for m, edges in inward_edges.items()})
    )
)

rounding_sensitivity = pd.concat(
    {
        "common_band": audit.loc[audit["inside"]]
        .groupby("pull_country")["area_sqm"].describe(),
        "inward_bounds": audit.loc[audit["inside_inward_bounds"]]
        .groupby("pull_country")["area_sqm"].describe(),
    },
    names=["boundary_policy", "pull_country"],
)
rounding_sensitivity

This is sensitivity analysis within the downloaded records. It isn't a simulated rerun, because changing the actual search bounds could change which listings appear before collection stops.

If you later calculate a price comparison, repeat it on both retained cohorts. A material change would make the boundary policy part of the finding, rather than a footnote about arithmetic.

Give the chart a defensible coverage sentence

The worked example uses Southeast Asia Property Listings by Country & Bedroom Count. The reusable lesson applies whenever search inputs use local units while exports offer normalised fields.

For a report, describe the selected listings and the collection boundary rather than calling the pull “the market”.

A coverage sentence can follow this pattern:

“Public condo sale listings collected under the recorded country-specific inputs and collection limits; analysis retains parseable floor areas within the stated common interval. Collection timing, returned counts and area exclusions are reported by market.”

Populate those details from the saved inputs, collection record and computed tables. Keep missing and unusable areas visible alongside the retained denominator, especially when readers might otherwise interpret an exclusion as evidence that no eligible home existed.

For a price chart, matched area still leaves currency and price semantics unresolved. Asking prices aren't completed transactions, and project ranges or “from” labels aren't interchangeable with fixed asking prices.

A tidy axis won't settle those differences.

The missing homes remain missing

These checks establish which downloaded records meet your floor-area definition. They can't establish that all eligible homes were returned, that missing areas resemble published ones, or that every market measures floor area on the same basis.

A converted area field standardises the unit. It doesn't verify the underlying measurement.

If the original search used the wrong unit, filtering the export can remove unsuitable homes, but recovering eligible homes that were never selected requires another pull.

The spreadsheet can repair the units. It can't repair the guest list.

Your priority account count is not your A-grade count

Dataspryva — Tue, 10 Nov 2026 08:19:38 +0000

Your manager wants the number of A-grade accounts. The dashboard already has a priorityFlag checkbox, and using it would make the report pleasantly quick.

It would also answer a different question: priorityFlag is true for grade A, funding in the last 180 days, or an executive change in the last 30 days.

The checkbox is a union.

Follow the routes into priority

The eligibility rule becomes easier to defend when you draw it:

Grade A ------------------\
Recent funding ------------ OR ---> priorityFlag
Recent executive change --/

An account can qualify through an event without having the top grade. It can also satisfy overlapping conditions, so adding the individual rule counts will double-count accounts that qualify through several routes.

For the requested A-grade total, count grade == "A". For operational priority, count the emitted flag. For an explanation of priority, retain separate eligibility indicators.

That distinction matters when your manager asks whether the account list improved. A change in priority membership could reflect event timing rather than stronger scores. A checkbox is remarkably efficient at hiding the difference.

Choose a company-run population

The Actor returns a graded company record with ranking fields and supporting evidence. When every enabled source fails, it returns a company_error record instead, carrying the company name, source notes and run timestamp.

Those errors belong in coverage reporting. They don't belong in a scored-company denominator.

companyName preserves the supplied name. It isn't a stable identity key. If you concatenate exports, you need an account identity and a run selection before calling the row count an account count.

This Example input requests detailed evidence and keeps the score threshold open:

import os

import duckdb
import pandas as pd
from apify_client import ApifyClient

client = ApifyClient(os.environ["APIFY_TOKEN"])
run_input = {
    "mode": "score",
    "companiesText": "Example Company",
    "outputFormat": "full",
    "lookbackDays": 180,
    "minScore": 0,
}
run = client.actor(
    "dataspryva/company-intent-scorer-hiring-funding-and-news"
).call(run_input=run_input)
items = list(
    client.dataset(run["defaultDatasetId"]).iterate_items()
)

Iterating through the dataset avoids treating a page of items as the complete export. Materialising the iterator here gives pandas the returned records for the audit.

Keep the input alongside your results. Collection settings explain the population; the returned fields don't preserve every decision that produced it.

Separate stored fields from collection settings

The fields relevant to this audit mix ranking values, nested evidence and input settings:

Field	Value and pandas treatment	Omission or analysis trap
`priorityFlag`	Logical eligibility value. Use nullable `boolean`; accept actual booleans only.	Absent from company-error records. Malformed values must remain unknown rather than becoming truthy.
`grade`	A–D label. Keep recognised values as nullable strings. A is 70+, B is 45–69, C is 20–44 and D is below 20.	Absent from company errors. Don't silently replace an unrecognised label using the score.
`score`	Numeric intent score from 0 to 100. Use nullable `Float64` without assuming an integer-only representation.	A mean excludes errors and missing or invalid scores. Its denominator needs a label.
`runTimestamp`	ISO 8601 run timestamp. Parse into a timezone-aware UTC timestamp.	An unusable timestamp prevents a reproducible event-age comparison.
`fundingSignal`	Nested funding evidence, added in full output when available. Check that the value is an object before extracting fields.	Funding requires verifiable evidence. Omission doesn't establish that the company raised nothing.
`fundingSignal.announcedDate`	Funding date; no fixed representation or precision is specified. Preserve the raw value and parse cautiously.	An absent or unusable date leaves the recency test unresolved.
`fundingSignal.daysSinceRound`	Reported round age; no fixed scalar representation is specified. Attempt numeric conversion into nullable `Float64`.	Don't assume its rounding or reference clock matches your calculation.
`execChangeSignal`	Detailed executive-change evidence in full output. Retain the raw value for inspection.	Presence alone doesn't establish a qualifying event date. This loader leaves its recency indicator unknown.
`lookbackDays`	Integer collection input. Default 90; permitted range 7–365. Applies to funding, news and executive changes.	It isn't a returned event age or a synonym for either priority cutoff.
`outputFormat`	String input, default `"compact"`; `"full"` adds detailed signal objects and the score breakdown.	Missing detail in a compact export is an export choice before it is an evidence finding.

The Actor omits empty fields at every depth. Absence therefore needs interpretation before it becomes a value.

Disabling a scored signal also normalises the remaining weights. Compare score distributions across runs only after checking that their signal selections match; the same numeric scale doesn't guarantee the same measurement.

Missing ages change the average

A naïve boolean cast turns a non-empty string into True. That's an especially unhelpful repair for a field whose meaning you're trying to audit.

This cell retains raw values, adds omitted columns explicitly and builds nullable analytical fields:

columns = [
    "companyName", "type", "grade", "score", "priorityFlag",
    "runTimestamp", "fundingSignal", "execChangeSignal",
]
raw = pd.DataFrame(items).reindex(columns=columns)
company_errors = raw["type"].eq("company_error")

df = raw[["companyName"]].astype("string")
df["grade"] = raw["grade"].astype("string").where(
    raw["grade"].isin(["A", "B", "C", "D"])
)

numeric_score = pd.to_numeric(raw["score"], errors="coerce")
df["score"] = numeric_score.where(
    numeric_score.between(0, 100)
).astype("Float64")

df["priorityFlag"] = raw["priorityFlag"].map(
    lambda value: value if isinstance(value, bool) else pd.NA
).astype("boolean")

funding_objects = raw["fundingSignal"].map(
    lambda value: value if isinstance(value, dict) else {}
)
numeric_age = pd.to_numeric(
    funding_objects.map(lambda signal: signal.get("daysSinceRound")),
    errors="coerce",
)
df["daysSinceRound"] = numeric_age.where(
    numeric_age.ge(0)
).astype("Float64")

Numeric conversion accepts numeric strings. It deliberately doesn't strip arbitrary text or invent a unit convention for an unspecified format.

An average daysSinceRound now describes records with usable reported round ages. It doesn't describe all accounts or all flagged accounts.

Funding visibility is selective: the Actor reports funding it can verify from a filing or an entity-matched press headline with a real amount or round. The average therefore describes observable funding evidence within the returned population, rather than the funding history of your whole account list.

Filling missing ages with zero would turn “age unavailable” into “just raised”. Missing data has enough problems without receiving a promotion.

Freeze the clock before testing funding

Recalculating recency against today's date makes an unchanged export produce a changing answer. Anchor the comparison to each record's runTimestamp.

This parser accepts timestamp strings with an explicit timezone and converts them to UTC. Values without a timezone remain unresolved in this audit rather than acquiring an invented one:

def parse_utc(value):
    if not isinstance(value, str):
        return pd.NaT
    stamp = pd.to_datetime(value, errors="coerce")
    if pd.isna(stamp) or stamp.tzinfo is None:
        return pd.NaT
    return stamp.tz_convert("UTC")

df["runTimestamp"] = pd.to_datetime(
    raw["runTimestamp"].map(parse_utc), utc=True
)
df["announcedDate"] = pd.to_datetime(
    funding_objects.map(
        lambda signal: parse_utc(signal.get("announcedDate"))
    ),
    utc=True,
)

Keep daysSinceRound beside the timestamp-derived test. If they disagree, preserve the discrepancy until you can explain the reference clock and date precision.

The next cell separates the eligibility routes. Its funding test uses elapsed time and an inclusive cutoff as audit conventions; internal boundary rounding isn't specified.

The executive route stays unknown here because this loader doesn't extract executive event dates. We retain execChangeSignal in raw for inspection. Treating the object's presence as proof of recency would manufacture the reason we're trying to explain.

def visible_recent(date, reference, days):
    if pd.isna(date) or pd.isna(reference) or date > reference:
        return pd.NA
    return bool(reference - date <= pd.Timedelta(days=days))

df["grade_a"] = df["grade"].eq("A").astype("boolean")
df["funding_recent"] = pd.Series(
    [
        visible_recent(date, reference, 180)
        for date, reference in zip(
            df["announcedDate"], df["runTimestamp"]
        )
    ],
    index=df.index,
    dtype="boolean",
)
df["executive_recent"] = pd.Series(
    pd.NA, index=df.index, dtype="boolean"
)
df["visible_union"] = (
    df["grade_a"] | df["funding_recent"] | df["executive_recent"]
)

Here, a false funding indicator means the supplied dated funding evidence falls outside the window. It doesn't certify that the company's entire event history has been observed.

Nullable boolean logic preserves a useful distinction: a known qualifying route establishes eligibility even when another route is unknown. Without a known qualifying route, this partial reconstruction remains unresolved.

The funding eligibility horizon exceeds the default collection window. A longer eligibility rule can't recover an event that collection didn't supply.

Our chosen input widens collection, but that doesn't guarantee complete coverage. This is a diagnostic comparison with the emitted flag, not an exact reconstruction of its internal processing.

Count the intersections, then the gaps

The record from Company Intent Scorer: Hiring, Funding & News Signals lets you distinguish the emitted classification from the evidence you can independently evaluate.

The cross-tab below intersects priority values with recognised grades. The membership table retains overlapping routes and unknowns, while each average carries its contributing record count:

audit = df.loc[~company_errors].copy()

grade_by_priority = pd.crosstab(
    audit["grade"].fillna("unresolved"),
    audit["priorityFlag"].astype("string").fillna("unresolved"),
    dropna=False,
)

rule_columns = ["grade_a", "funding_recent", "executive_recent"]
membership_counts = (
    audit.groupby(rule_columns, dropna=False)
    .size()
    .rename("records")
)

population = pd.Series({
    "returned_records": len(raw),
    "company_error_records": int(company_errors.sum()),
    "non_error_records": len(audit),
})
score_summary = audit["score"].agg(["mean", "count"])
reported_round_age_summary = audit["daysSinceRound"].agg(
    ["mean", "count"]
)

A priority share needs a declared denominator too. “Among records with a usable flag” and “among returned non-error records” answer different questions when flags are unresolved.

DuckDB can separate the requested grade count, the operational flag count and the unresolved audit population:

count_audit = duckdb.sql("""
    SELECT
        count(*) AS non_error_records,
        count(*) FILTER (WHERE grade_a IS TRUE) AS grade_a_records,
        count(*) FILTER (
            WHERE priorityFlag IS TRUE
        ) AS priority_records,
        count(*) FILTER (
            WHERE priorityFlag IS TRUE AND grade_a IS TRUE
        ) AS priority_grade_a_records,
        count(*) FILTER (
            WHERE priorityFlag IS TRUE AND grade_a IS FALSE
        ) AS priority_other_known_grade_records,
        count(*) FILTER (
            WHERE priorityFlag IS TRUE AND grade_a IS NULL
        ) AS priority_unknown_grade_records,
        count(*) FILTER (
            WHERE priorityFlag IS NULL
        ) AS unknown_priority_records,
        count(*) FILTER (
            WHERE visible_union IS NULL
        ) AS unresolved_union_records,
        count(*) FILTER (
            WHERE visible_union IS NOT NULL
              AND priorityFlag IS NOT NULL
              AND visible_union <> priorityFlag
        ) AS known_disagreements
    FROM audit
""").df()

Because the executive route remains unknown, the partial reconstruction can't establish ineligibility. It can establish a visible qualifying reason, or leave the reason unresolved.

A known disagreement deserves inspection of the original evidence and collection settings. It doesn't, by itself, prove an incorrect flag.

These counts also describe the returned population. A higher minScore removes lower-scored companies while keeping error records. Monitor mode can omit unchanged companies, making its export a change population rather than a complete account snapshot.

A dashboard label should carry that scope. “Returned A-grade accounts” is defensible where “A-grade accounts in the market” isn't.

Leave buying intent unproven

Public signals and their attached grades don't establish an account's actual budget, purchasing authority or intention to buy.

Nor does an unresolved priority reason establish that no qualifying event occurred. It establishes a limit on what this audit can explain.

An unresolved reason belongs in the report, not inside a reassuring checkbox.

Your merged review counts may give repeat reviews extra votes

Dataspryva — Mon, 09 Nov 2026 08:17:10 +0000

The theme leading your draft may have more copies behind it than reviews.

You've merged targeted exports with pd.concat(), counted themes and started writing the headline. The uncomfortable question arrives just before publication: did the same review enter through several filters?

Our review Actor accepts product URLs and review URLs, combines review filters and applies the chosen sort order to the matches. Separate collections can therefore return overlapping reviews, even when each collection makes sense on its own.

Pooling overlapping review exports weights each review by the number of collected rows carrying its identity.

That's a property of your merge, not evidence of stronger agreement.

Which reviews are you actually counting?

Suppose your question is: “Which theme appears most often among the collected reviews?”

A collection filtered to small companies and another filtered to managers don't partition that population. A manager at a small company can belong to both.

Keep the collection definition beside every observation before combining anything. The metadata below belongs to your analysis; it isn't returned by the Actor.

Example: these inputs collect overlapping segments and label them as part of the same editorial batch.

import os
import json
import pandas as pd
import duckdb
from apify_client import ApifyClient

client = ApifyClient(os.environ["APIFY_TOKEN"])
snapshot_id = "edition"
analysis_fields = ["productSlug", "title", "useCase"]
records = []

for collection_id, filters in [
    ("small_companies", {"companySizes": ["small"]}),
    ("managers", {"reviewerRoles": ["manager"]}),
]:
    run_input = {
        "mode": "reviews",
        "productUrls": ["asana"],
        "maxItems": 100,
        "maxReviewsPerProduct": 100,
        "reviewSort": "newest",
        **filters,
    }
    run = client.actor(
        "dataspryva/trustradius-product-ratings-and-feature-score"
    ).call(run_input=run_input)
    if run["status"] != "SUCCEEDED":
        raise RuntimeError(f"Collection did not succeed: {collection_id}")

    for item in client.dataset(run["defaultDatasetId"]).iterate_items():
        if item.get("recordType") == "review":
            records.append({
                **item,
                "collection_id": collection_id,
                "snapshot_id": snapshot_id,
                "content_signature": json.dumps(
                    {field: item.get(field) for field in analysis_fields},
                    sort_keys=True,
                    ensure_ascii=False,
                ),
            })

Here, snapshot_id names a collection batch. It doesn't claim that the pages were observed simultaneously.

Restricting records to reviews also matters: the Actor can emit products, discovery cards and comparisons in other modes. Those aren't additional respondents.

A cap can make overlap look like consensus

Filters determine eligibility. Ordering and caps determine which eligible reviews you actually collect.

The Actor follows review pagination until maxReviewsPerProduct, maxItems or the end of the matching archive. With reviewSort set to newest, a capped segment represents the newest collected matches, rather than a random sample of that segment.

Pooling segments then introduces another selection mechanism:

small-company collection ─┐
                         ├─ same review ─ repeated rows
manager collection ──────┘

A theme associated with the intersection can gain extra weight. Its rank might change after deduplication, or it might stay put. Your data has to answer that.

The analytical objects need different jobs:

Object	What it preserves	Appropriate use
Observation	A record as collected in a run	Collection audits and version checks
Review	A resolved review identity and analysis version	Overall theme prevalence
Membership	A review's presence in a collection	Segment comparisons and overlap

Segment counts remain useful. They simply aren't additive when membership overlaps.

Audit the URL before making it a key

reviewUrl is a candidate review identifier. productSlug provides a useful consistency check, but it identifies a product rather than an individual review.

We'd rather hold an identity out than quietly invent one from a title. Similar wording is weak evidence of sameness.

Empty fields are omitted, so build the expected columns explicitly. Preserve URL spelling apart from surrounding whitespace: stripping query parameters or rewriting paths requires evidence that the altered URLs still identify the same review.

This cell identifies missing URLs, conflicting product associations and timestamps that can't support version selection.

columns = [
    "snapshot_id", "collection_id", "reviewUrl", "scrapedAt",
    "content_signature", *analysis_fields,
]
observations = pd.DataFrame(records).reindex(columns=columns)

for column in ["reviewUrl", "productSlug"]:
    observations[column] = (
        observations[column].astype("string").str.strip()
        .replace("", pd.NA)
    )

observations["observed_at"] = pd.to_datetime(
    observations["scrapedAt"], errors="coerce", utc=True
)
keys = ["snapshot_id", "reviewUrl"]
keyed = observations.loc[observations["reviewUrl"].notna()].copy()

product_conflict = (
    keyed.groupby(keys)["productSlug"].transform("nunique").gt(1)
)
bad_timestamp = (
    keyed["observed_at"].isna().groupby(
        [keyed[column] for column in keys]
    ).transform("any")
)
identity_audit = pd.Series({
    "observations_without_url": observations["reviewUrl"].isna().sum(),
    "observations_without_product_slug": keyed["productSlug"].isna().sum(),
    "observations_with_product_conflict": product_conflict.sum(),
    "observations_in_timestamp_holdout": bad_timestamp.sum(),
})
eligible = keyed.loc[~product_conflict & ~bad_timestamp].copy()

The audit counts observations, not distinct identities. Keep those units in the labels when reporting exclusions.

A missing product association remains visible rather than becoming proof of a conflict. A URL associated with different known product slugs needs inspection before it becomes a trustworthy key.

Choose a version without losing membership

A repeated URL may contain changed text. Deduplicating with “keep whichever row arrived last” makes file order your editorial policy.

For this analysis, choose the latest parseable scrapedAt within the named batch. If observations at that latest timestamp disagree on the analysis fields, hold that review out.

This policy resolves the fields used here. It doesn't establish that every other part of the review stayed unchanged.

The next cell preserves membership, exposes changed analysis versions and creates the resolved review table.

membership = keyed[
    [*keys, "collection_id"]
].drop_duplicates()

version_counts = eligible.groupby(
    keys
)["content_signature"].transform("nunique")
version_conflicts = eligible.loc[version_counts.gt(1)].copy()

latest_time = eligible.groupby(keys)["observed_at"].transform("max")
latest = eligible.loc[eligible["observed_at"].eq(latest_time)].copy()
ambiguous_latest = latest.groupby(
    keys
)["content_signature"].transform("nunique").gt(1)

version_holdouts = latest.loc[ambiguous_latest].copy()
reviews = latest.loc[
    ~ambiguous_latest, [*keys, *analysis_fields, "observed_at"]
].drop_duplicates(keys)

The final deduplication is safe for this projection because any remaining tied observations agree on the selected fields and timestamp.

Keep membership even when a review is held out. It records provenance; joining it to reviews determines which memberships have a resolved version available for analysis.

Compare repetition with review prevalence

Before counting themes, decide what a theme count means.

A review mentioning a topic repeatedly can contribute a single presence flag, or several textual mentions. Those answer different questions. Neither becomes a count of people merely because the chart says “customers”.

For a compact audit, use presence flags over title and useCase. These are narrow word checks, not validated theme annotations: negation, synonyms and context still require review.

Example: these flags compare mentions of support and pricing in the selected text fields.

def text_value(value):
    if isinstance(value, str):
        return value
    if isinstance(value, list):
        return " ".join(part for part in value if isinstance(part, str))
    return ""

review_text = (
    reviews["title"].map(text_value).astype("string")
    + " "
    + reviews["useCase"].map(text_value).astype("string")
)

theme_terms = {
    "support": r"\bsupport\b",
    "pricing": r"\bpric(?:e|es|ing)\b",
}
review_flags = reviews[keys].copy()
for theme, pattern in theme_terms.items():
    review_flags[theme] = review_text.str.contains(
        pattern, case=False, regex=True, na=False
    )

review_flags = review_flags.melt(
    id_vars=keys, var_name="theme", value_name="mentions_theme"
)
review_flags["mentions_theme"] = review_flags["mentions_theme"].astype(bool)

row_flags = observations[[*keys, "collection_id"]].merge(
    review_flags, on=keys, how="inner", validate="many_to_many"
)

Every collected copy now receives the same resolved annotation. That isolates repetition from changes in wording across observations.

The next cell compares a denominator of collected rows with a denominator of resolved reviews.

theme_comparison = duckdb.sql("""
    SELECT
        'collected_rows' AS basis,
        theme,
        COUNT(*) AS denominator,
        COUNT(*) FILTER (WHERE mentions_theme) AS theme_count,
        AVG(CAST(mentions_theme AS DOUBLE)) AS prevalence
    FROM row_flags
    GROUP BY theme

    UNION ALL

    SELECT
        'resolved_reviews' AS basis,
        theme,
        COUNT(*) AS denominator,
        COUNT(*) FILTER (WHERE mentions_theme) AS theme_count,
        AVG(CAST(mentions_theme AS DOUBLE)) AS prevalence
    FROM review_flags
    GROUP BY theme
""").df()

Inspect theme_comparison for changes in prevalence and ranking. No change is also a useful finding: it means this particular repetition audit didn't alter those measures.

For publication, report the resolved-review denominator, the text fields analysed, the version policy and the exclusions. Treat the collected-row calculation as a sensitivity check, rather than a competing estimate of public opinion.

Collect a base population when the question needs it

The worked example uses TrustRadius Product Ratings & Feature Score Extractor, but separating identity from membership applies to any overlapping exports.

If your question concerns the broader product review archive, collecting targeted segments and stitching them together creates an awkward denominator. Eligible reviews outside every chosen segment never enter.

Where practical, define the base collection first and treat segments as separate analytical views. Don't silently substitute an absent reviewer attribute with an inferred value.

Example: this input removes segment filters and requests the entire archive through the per-product setting, while retaining the Actor's overall emission cap.

base_run_input = {
    "mode": "reviews",
    "productUrls": ["asana"],
    "maxReviewsPerProduct": 0,
    "maxItems": 5000,
    "reviewSort": "newest",
    "includeReviewDetails": True,
}

That cap still applies. Calling the input “entire archive” doesn't establish that collection reached the end.

Enabling includeReviewDetails opens each review for ROI, alternatives and full Q&A. It matters when your coding question needs that material; it doesn't make the title-and-use-case flags above cover the full review.

For a defensible methods note, describe the selection literally: products targeted, filters applied, sort order, caps, collection batch and resolved identities retained. Say “collected matching reviews” unless archive completion has been established separately.

A review still isn't a customer

Deduplication removes repeated review identities. It doesn't establish unique people, unique organisations or a representative sample of product users.

Published reviews exclude experiences that were never posted. Missing public attributes can also prevent you from checking how well your collected population matches the population named in your headline.

Your clean denominator can support a claim about the reviews analysed. It can't quietly expand into a claim about every customer.

A repeated review is another observation, not another vote.

Your annual salary median can mix currencies and pay types

Dataspryva — Sun, 08 Nov 2026 08:05:30 +0000

Your remote-pay chart can have a perfectly valid median and no coherent unit.

You're about to put salaryAnnualMin on a slide comparing roles. The column name suggests the awkward conversion work is finished, which is a comforting thought until someone asks what the numbers actually measure.

Annualised salary fields standardise time, not currency or compensation type. The Actor's salary filters compare published annualised amounts without converting currencies, while salaryType distinguishes base, ote, total-comp and hourly.

That median needs more than a successful numeric cast.

What does a salary record represent?

With Dataspryva's salary Actor, a record represents a job posting, deduplicated by its canonical job URL. It represents advertised terms, rather than an employee, an accepted offer or a payment.

That distinction matters before any arithmetic. A median of advertised lower bounds answers a different question from a median of employee earnings.

This Example input keeps postings without parsed pay and leaves salary thresholds unset, so the collection preserves the population needed to measure missingness.

import os

import duckdb
import pandas as pd
from apify_client import ApifyClient

run_input = {
    "mode": "browse",
    "categories": [],
    "requireSalary": False,
    "includeCompanyProfile": False,
    "maxItems": 5000,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/we-work-remotely-job-listings-and-salary-extractor"
).call(run_input=run_input)

if run["status"] != "SUCCEEDED":
    raise RuntimeError(f"Actor ended with status {run['status']}")

items = []
for item in client.dataset(run["defaultDatasetId"]).iterate_items():
    items.append(item)

The input's maxItems is a collection ceiling. Your eventual denominator is the returned collection under those settings, not a claim about every remote vacancy.

The fields separate amount from meaning

The salary threshold descriptions use USD wording, but the currency limitation still applies. Setting minSalary doesn't create a common-currency sample.

Compensation needs its own split. OTE includes contingent earnings, so it can't stand in for guaranteed base pay. Total compensation can include components beyond base salary, while hourly compensation needs a conversion before an annual comparison.

The useful path through these fields is a split, followed by arithmetic:

advertised pay
    |
    +-- currency label
           |
           +-- compensation type
                  |
                  +-- annual lower and upper bounds

Here's the field map we need, including the pandas types we'll use and the question each field leaves open.

Field	Value and pandas treatment	Analytical catch
`salary`	Original salary text, kept as `string` alongside the untouched record	Useful for inspection, unsuitable for direct aggregation
`salaryDetails`	Object containing `min`, `max`, `currency`, `unit`; retain as `object`	A nested representation of the range, not an additional salary observation
`salaryMin`, `salaryMax`	Flattened range amounts, parsed to nullable `Float64`	Their time basis still comes from the period fields
`salaryCurrency`	Currency label, normalised to uppercase `string`	A currency symbol alone would not establish a unique currency
`salaryUnit`	`YEAR`, `MONTH`, `WEEK`, `DAY` or `HOUR`, kept as uppercase `string`	Equal amounts with different units are different quantities
`salaryPeriod`	Lowercase period text, kept as `string`	A period label does not establish paid hours or weeks
`salaryType`	`base`, `ote`, `total-comp` or `hourly`, kept as lowercase `string`	A common currency does not make these compensation concepts interchangeable
`salaryAnnualMin`, `salaryAnnualMax`	Annualised endpoints, parsed to nullable `Float64`	These remain advertised bounds, not observed earnings
`salarySource`	`description` or `detail`, kept as lowercase `string`	Extraction provenance does not certify the employer's pay claim

Empty fields are omitted. On jobs without a confidently parsed range, salary fields can therefore be absent rather than explicitly null.

Absence doesn't come with a reason code that separates undisclosed pay from text that could not be parsed confidently. We'd rather preserve that uncertainty than turn a blank into a salary.

Parse amounts without guessing their meaning

The loader below handles omitted keys explicitly. It retains the original records and keeps salaryDetails intact, rather than merging overlapping representations and quietly choosing a winner.

It also normalises labels without trying to infer currency from the salary text.

raw = pd.DataFrame(items)

text_fields = [
    "salary", "salaryCurrency", "salaryUnit",
    "salaryPeriod", "salaryType", "salarySource",
]
amount_fields = [
    "salaryMin", "salaryMax", "salaryAnnualMin", "salaryAnnualMax",
]

pay = raw.reindex(
    columns=text_fields + amount_fields + ["salaryDetails"]
).copy()

def clean_text(values):
    return values.astype("string").str.strip().replace("", pd.NA)

pay["salary"] = pay["salary"].astype("string")

for field in text_fields:
    if field != "salary":
        pay[field] = clean_text(pay[field])

for field in ["salaryCurrency", "salaryUnit"]:
    pay[field] = pay[field].str.upper()

for field in ["salaryPeriod", "salaryType", "salarySource"]:
    pay[field] = pay[field].str.lower()

For amounts, we accept ordinary decimal notation and correctly grouped commas. Currency symbols, abbreviated amounts and unfamiliar separators remain parse failures.

That's deliberately conservative: removing every non-numeric character is an excellent way to manufacture a number with confidence.

This cell parses the flattened amounts while recording which non-empty values failed conversion.

def parse_amount(values):
    text = clean_text(values)
    valid = text.str.fullmatch(
        r"[+-]?(?:[0-9]+|[0-9]{1,3}(?:,[0-9]{3})+)(?:\.[0-9]+)?",
        na=False,
    )
    cleaned = text.where(valid).str.replace(",", "", regex=False)
    return pd.to_numeric(cleaned, errors="coerce").astype("Float64")

parse_failures = pd.DataFrame(index=pay.index)

for field in amount_fields:
    supplied = clean_text(pay[field]).notna()
    pay[field] = parse_amount(pay[field])
    parse_failures[field] = supplied & pay[field].isna()

We don't recompute annual amounts from salaryMin or salaryMax. In particular, annualising hourly pay requires assumptions about paid hours and weeks that aren't supplied here.

Hourly records remain identifiable through salaryType; accepting their supplied annualisation doesn't verify those assumptions.

Missing pay changes who the median describes

Setting requireSalary to true keeps only confidently parsed salary ranges. That's useful for a pay-only export, but it removes the records you need to calculate salary coverage within the collected postings.

Salary thresholds have another wrinkle: jobs without disclosed pay remain unless requireSalary is enabled. A salary-filtered collection can therefore mix threshold-matching salaries with jobs whose pay is unknown.

For this analysis, leaving those filters off lets you distinguish the collection denominator from the salary denominator.

The distinction survives after parsing. A lower-bound median uses records with usable lower bounds; an upper-bound median uses records with usable upper bounds. Those populations need not match.

A missing upper bound isn't permission to copy the lower bound. That would convert uncertainty into a point estimate, neatly enough to pass through most charts.

Keep exceptions beside the comparison

Our comparison policy requires a currency label with the expected letter-code shape and a recognised compensation type. The currency check validates shape, not membership in an authoritative currency registry.

We also hold out malformed amounts, negative annual amounts, reversed annual endpoints and unexpected salaryDetails shapes. A record with a usable endpoint can remain eligible when the other endpoint is missing.

This cell builds an exceptions table and a coverage table, retaining an explicit unknown group for unusable classifications.

currency_ok = pay["salaryCurrency"].str.fullmatch(
    r"[A-Z]{3}", na=False
)
type_ok = pay["salaryType"].isin(
    ["base", "ote", "total-comp", "hourly"]
)

annual_fields = ["salaryAnnualMin", "salaryAnnualMax"]

flags = pd.DataFrame({
    "missing_currency": pay["salaryCurrency"].isna(),
    "malformed_currency": pay["salaryCurrency"].notna() & ~currency_ok,
    "missing_type": pay["salaryType"].isna(),
    "unrecognised_type": pay["salaryType"].notna() & ~type_ok,
    "amount_parse_failure": parse_failures.any(axis=1),
    "no_annual_bound": pay[annual_fields].isna().all(axis=1),
    "negative_annual_bound": pay[annual_fields].lt(0).any(axis=1),
    "reversed_annual_bounds": (
        pay["salaryAnnualMin"] > pay["salaryAnnualMax"]
    ).fillna(False),
    "malformed_details": (
        pay["salaryDetails"].notna()
        & ~pay["salaryDetails"].map(lambda value: isinstance(value, dict))
    ),
})

eligible = ~flags.any(axis=1)
exceptions = raw.join(flags.add_prefix("audit_")).loc[~eligible].copy()

coverage_rows = pay[["salaryCurrency", "salaryType"]].copy()
coverage_rows["salaryCurrency"] = pay["salaryCurrency"].where(
    currency_ok, "unknown"
)
coverage_rows["salaryType"] = pay["salaryType"].where(
    type_ok, "unknown"
)
coverage_rows["eligible"] = eligible
coverage_rows["usable_lower"] = eligible & pay["salaryAnnualMin"].notna()
coverage_rows["usable_upper"] = eligible & pay["salaryAnnualMax"].notna()

coverage = coverage_rows.groupby(
    ["salaryCurrency", "salaryType"], dropna=False
).agg(
    collected_postings=("eligible", "size"),
    eligible_postings=("eligible", "sum"),
    usable_lower_bounds=("usable_lower", "sum"),
    usable_upper_bounds=("usable_upper", "sum"),
).reset_index()

The original values remain in exceptions, alongside the reasons for holding them out. Missing classifications stay visible in coverage instead of disappearing through a group-by default.

These exclusions are an analyst's policy, not a discovery that the underlying posting is wrong. A reversed range deserves inspection before it deserves a swap.

Compare lower bounds with lower bounds

Now the statistic can have a precise name: the median advertised annual lower bound within a currency and compensation group.

The upper-bound distribution gets a separate measure. Averaging each posting's endpoints would create an analyst-derived midpoint, not recover an employer's expected offer.

This DuckDB query calculates separate medians and endpoint denominators from eligible records.

comparison = pay.loc[
    eligible,
    ["salaryCurrency", "salaryType"] + annual_fields,
].copy()

connection = duckdb.connect()
try:
    connection.register("salary_comparison", comparison)
    bound_summary = connection.execute("""
        SELECT
            salaryCurrency,
            salaryType,
            COUNT(*) AS eligible_postings,
            COUNT(salaryAnnualMin) AS lower_bound_n,
            MEDIAN(salaryAnnualMin) AS median_annual_lower_bound,
            COUNT(salaryAnnualMax) AS upper_bound_n,
            MEDIAN(salaryAnnualMax) AS median_annual_upper_bound
        FROM salary_comparison
        GROUP BY salaryCurrency, salaryType
        ORDER BY salaryCurrency, salaryType
    """).df()
finally:
    connection.close()

For a role comparison, those groups are necessary but insufficient. You still need a defensible role classification and comparable employment arrangements; a title string alone doesn't establish equivalent work.

Keep coverage beside the resulting table. A median without its contributing count conceals whether your comparison is mostly describing disclosed pay or mostly describing missingness.

Where the pay claim stops

The worked record comes from We Work Remotely Job Listings & Salary Extractor.

Its salary fields describe published posting terms. They can't establish what applicants were offered, what employees earned, whether contingent compensation paid out, or which undisclosed salaries would change your result.

Nor does grouping by currency resolve purchasing power or local labour-market differences. Those require additional evidence and an explicitly chosen comparison method.

Your chart caption can be precise: median advertised annual lower bounds among eligible, salary-parsed postings in each currency and compensation group, with endpoint counts and collection coverage shown separately.

That's a narrower claim than “remote roles pay more”.

It's also a claim you can defend.

Your release count may give featured episodes extra votes

Dataspryva — Sat, 07 Nov 2026 08:12:33 +0000

You're checking a show's release count before publication, and episodes, premiumStories and preselectedStory all look ready to become rows. Concatenate them and the spreadsheet produces a reassuringly precise total.

The Actor preserves a season catalogue, the latest page stories and a featured episode as parallel representations, not independent releases.

Turning includeStories off doesn't remove the latest page stories from premiumStories. You can lose the catalogue walk while retaining the section least suited to measuring historical output. That's an awkward detail to discover after the chart has gone to layout.

Which population does your count describe?

“Episodes released by this show” sounds straightforward. It usually implies a historical population, including releases that may no longer be publicly listed.

The available sampling frame is narrower: episodes returned through the public season catalogue for your selected publisher, within the request context and collection limits.

We'd name that population before counting it. Otherwise, a technically correct calculation can answer a question your headline never asked.

Example input for checking a selected publisher's catalogue, with media and unrelated content disabled:

import os

import duckdb
import pandas as pd
from apify_client import ApifyClient

run_input = {
    "mode": "byPublisher",
    "publisherUrlsOrIds": ["efd8a13d-b69a-41d0-9c04-d19f87829798"],
    "country": "US",
    "includeStories": True,
    "includeShowSeasons": True,
    "includeSpotlights": False,
    "includeEpisodeMedia": False,
    "maxEpisodesPerPublisher": 5000,
    "maxItems": 1,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/snapchat-publisher-and-show-episode-finder"
).call(run_input=run_input)

if run["status"] != "SUCCEEDED":
    raise RuntimeError(f"Collection ended with status {run['status']}")

items = []
for item in client.dataset(run["defaultDatasetId"]).iterate_items():
    items.append(item)

A successful run status isn't a certificate of coverage. Unresolved inputs can produce error rows, so the returned record types still matter.

A page can show the same release again

The tempting myth is that each episode-shaped object represents another release.

What actually happens is easier to see as a collection diagram:

publisher
  ├─ episodes[]          → catalogue observations
  ├─ premiumStories[]    → page presentation observations
  └─ preselectedStory    → featured presentation observation

These branches describe how an observation reached you. Flattening away the branch removes the evidence you need to decide whether it belongs in a release count.

The identity rules differ too:

Section	Role in the calculation	Identity discipline
`episodes[]`	Catalogue count candidates	Count distinct `episodeId` values within the publisher
`premiumStories[]`	Presentation audit	Keep `storyId` in its own namespace
`preselectedStory`	Presentation audit	Check an available `episodeUrl` against the catalogue

If a release appears in the catalogue and a presentation section, concatenation gives that release additional weight. Recent or featured material can consequently receive more influence in a release-date histogram than material represented only in the catalogue.

That direction follows from the selection mechanism. Its size needs measuring.

Accurate records can still make an inaccurate total. Bad arithmetic doesn't require bad data.

Keep the section before flattening

You need separate tables, but also a way to trace every extracted observation back to its parent row.

Empty fields are omitted by the Actor. An absent section therefore needs an explicit audit state; it shouldn't quietly become evidence that the publisher has no releases.

This cell preserves section provenance and records omitted or unexpected section shapes:

section_fields = {
    "episodes": (
        "episodeId", "episodeUrl", "publishedAt",
        "seasonNumber", "episodeNumber",
    ),
    "premiumStories": (
        "storyId", "postedAt", "seasonNumber", "episodeNumber",
    ),
    "preselectedStory": (
        "storyId", "episodeUrl", "seasonNumber", "episodeNumber",
    ),
}

publisher_rows = [
    (position, item)
    for position, item in enumerate(items)
    if item.get("recordType") == "publisher"
]
excluded_rows = pd.DataFrame([
    item for item in items
    if item.get("recordType") != "publisher"
])

extracted = []
section_audit = []

for parent_row, publisher in publisher_rows:
    for section, fields in section_fields.items():
        value = publisher.get(section)
        featured = section == "preselectedStory"
        expected_shape = isinstance(value, dict if featured else list)
        candidates = ([value] if featured else value) if expected_shape else []
        records = [record for record in candidates if isinstance(record, dict)]

        section_audit.append({
            "parent_row": parent_row,
            "source_section": section,
            "state": (
                "omitted" if section not in publisher
                else "present" if expected_shape
                else "unexpected_shape"
            ),
            "usable_rows": len(records),
            "discarded_elements": len(candidates) - len(records),
        })

        for record in records:
            extracted.append({
                "parent_row": parent_row,
                "publisherId": publisher.get("publisherId"),
                "source_section": section,
                **{field: record.get(field) for field in fields},
            })

observation_columns = [
    "parent_row", "publisherId", "source_section",
    *dict.fromkeys(
        field for fields in section_fields.values() for field in fields
    ),
]
observations = pd.DataFrame(extracted, columns=observation_columns)
section_checks = pd.DataFrame(section_audit)

Notice what the extraction doesn't manufacture: an episodeUrl for a page story. That nested field isn't supplied in the page-story field list.

Keeping the column empty is useful. It preserves an unresolved relationship instead of replacing it with a confident guess.

This cell creates the analytical tables and parses catalogue publication timestamps without substituting page-story timestamps:

for identifier in ("publisherId", "episodeId", "storyId", "episodeUrl"):
    observations[identifier] = (
        observations[identifier].astype("string").replace("", pd.NA)
    )

catalogue = observations.loc[
    observations["source_section"].eq("episodes")
].copy()
presentations = observations.loc[
    observations["source_section"].ne("episodes")
].copy()

catalogue["parsed_published_at"] = pd.to_datetime(
    catalogue["publishedAt"], format="mixed", errors="coerce", utc=True
)
date_checks = catalogue.loc[
    catalogue["parsed_published_at"].isna(),
    ["parent_row", "episodeId", "publishedAt"],
]

Missing or unparseable dates remain visible in date_checks. They don't disappear from the catalogue count merely because they can't sit on a calendar chart.

Likewise, postedAt isn't a convenient fallback for publishedAt. Treating differently named timestamps as interchangeable would introduce another assumption precisely where you're trying to remove one.

Match URLs without inventing an ID join

Different identifier names don't establish an identity mapping.

You can retain storyId and episodeId for inspection without asserting that equal-looking values refer to the same object. Matching titles is weaker still: titles can repeat or change.

For an available featured URL, exact agreement with a catalogue URL gives you a directly checkable overlap. Keep the publisher-row scope in the join so a later expansion of the notebook doesn't accidentally match across parent records.

This cell classifies presentation observations by whether their supplied URL appears in the catalogue:

catalogue_urls = (
    catalogue.loc[
        catalogue["episodeUrl"].notna(),
        ["parent_row", "episodeUrl"],
    ]
    .drop_duplicates()
    .assign(url_in_catalogue=True)
)

links = presentations.merge(
    catalogue_urls,
    on=["parent_row", "episodeUrl"],
    how="left",
    validate="many_to_one",
)

links["link_status"] = "no_exact_catalogue_match"
links.loc[links["episodeUrl"].isna(), "link_status"] = "no_episode_url"
links.loc[
    links["url_in_catalogue"].eq(True), "link_status"
] = "exact_catalogue_url"

Don't normalise away query strings or rewrite URL paths simply to make more matches appear. That would turn an exact comparison into an undocumented reconciliation rule.

A missing match also doesn't establish an additional release. The catalogue may be incomplete, the presentation may lack a comparable URL, or the representations may need independent reconciliation.

“Unresolved” is a useful result. It's cheaper than a correction.

Let the audit challenge the headline

You want the proposed count beside the number a recursive concatenation would have produced. You also want the unresolved records beside both.

This cell produces a count audit, an overlap report and a flag for catalogue rows reaching the configured cap:

count_audit = duckdb.sql("""
    SELECT
        (SELECT count(*) FROM observations) AS concatenated_rows,
        (SELECT count(*) FROM catalogue) AS catalogue_rows,
        (
            SELECT count(*)
            FROM (
                SELECT DISTINCT parent_row, episodeId
                FROM catalogue
                WHERE episodeId IS NOT NULL
            ) AS identified
        ) AS identified_catalogue_episodes,
        (
            SELECT count(*) FROM catalogue
            WHERE episodeId IS NULL
        ) AS catalogue_rows_without_id
""").df()

overlap_report = duckdb.sql("""
    SELECT source_section, link_status, count(*) AS observations
    FROM links
    GROUP BY source_section, link_status
    ORDER BY source_section, link_status
""").df()

cap_reached = (
    catalogue.groupby("parent_row")
    .size()
    .ge(run_input["maxEpisodesPerPublisher"])
    .rename("episode_cap_reached")
)

concatenated_rows measures observations across sections. identified_catalogue_episodes measures distinct returned catalogue identifiers. Their difference is a change in counting unit, not an estimate of how many duplicates you've proved.

The exact-URL category supplies the directly linked overlap. The other categories remain unresolved presentation observations.

Before using the catalogue figure, inspect excluded_rows and section_checks. A missing publisher response mustn't become a reported release count of zero. Catalogue rows without an identifier also prevent the identified count from standing in for the entire extract without qualification.

For a publication-period chart, report the undated catalogue records separately. Otherwise, a clean chart can conceal a narrower denominator than the accompanying release total.

Change the input before changing the claim

The worked example uses Snapchat Publisher & Show Episode Finder. Its useful distinction here's between collecting a catalogue and preserving what a profile page happens to present.

The input above deliberately requests the season walk. It also keeps the declared season summaries available for comparison while avoiding episode-media requests, which add requests per returned episode without resolving this counting problem.

maxEpisodesPerPublisher controls the nested catalogue. maxItems controls dataset rows, including error rows. Raising the latter doesn't remove the former.

Treat a reached episode cap as a reason to withhold a completeness claim. It doesn't prove truncation, and it doesn't tell you whether the retained records represent the earliest or latest releases.

For repeated observations, retain each original publisher response, its scrapedAt value and the input alongside your derived tables. Combining snapshots without an observation boundary can recreate the same weighting problem across collection times.

A defensible label is specific: “Distinct episode IDs returned from the public season catalogue for the selected publisher, under the recorded request context.”

Less elegant than “total releases”. Considerably easier to defend.

The missing history stays missing

The page's declared season count can exceed what its public cursor returns. That discrepancy deserves investigation; it isn't a licence to invent the missing episode records or substitute the declaration for an observed count.

Country restrictions can also leave a publisher unavailable. An explicit country value records request context, not worldwide visibility.

Even after the presentation overlap is handled, this extract can't establish how many episodes ever existed, which unavailable releases belong in a historical denominator, or whether an unmatched story refers to a catalogue episode.

The featured episode can earn attention. It doesn't earn another place in your release count.

Your country label does not make a national sample

Dataspryva — Fri, 06 Nov 2026 08:24:28 +0000

Your country label may describe the request, not the videos. If you're about to publish a Spotlight chart headed “Trending in the UK”, that's an uncomfortable distinction to discover after the graphic leaves your notebook.

Snapchat's anonymous web feed has no working country control. Passing country, countryCode, geo or region, changing locale, or adding country headers doesn't select a national feed.

The order can change between requests, and there's no working cursor to continue through it. The anonymous Spotlight feed provides no national sampling frame, so country-labelled requests cannot establish national trends.

The reusable problem is simple: an annotation about collection can quietly become a claim about the population collected.

Can this feed support a country label?

You need to preserve the label you're auditing without promoting it into a returned video attribute. A column heading can acquire remarkable authority after a few joins.

Dataspryva's feed Actor gives us a concrete way to examine that boundary.

Example: this collection plan assigns a country claim outside the Actor input; replace that annotation with the claim attached to your chart.

import os
import json

import pandas as pd
from apify_client import ApifyClient

client = ApifyClient(os.environ["APIFY_TOKEN"])
run_input = {
    "mode": "spotlightFeed",
    "maxItems": 100,
    "enrichVideos": True,
}
claimed_countries = ["GB", "GB"]

claimed_countries belongs to your analysis. It isn't sent to the Actor, and nothing in this setup makes it an observed location.

Keep that separation through the export, too:

Collection annotation            Returned observation
claimed_country                  snapId
                                 detectedLanguage
                                 scrapedAt
          \                       /
           \---- coverage audit -/
                    |
           no automatic geography

A cap limits storage, not coverage

The selected mode reads the current anonymous public feed. It doesn't apply a topic restriction, and it doesn't establish complete coverage across content categories.

maxItems limits rows saved across the run. A cap can truncate what you retain; raising it doesn't expand the feed available to collect.

These are the distinctions that matter when you inspect the resulting rows:

Evidence available to inspect	What it supports	What it doesn't establish
`snapId`	Identity matching across observations	A video's probability of entering the feed
`topicSource`, `rank`	Returned surface provenance and position	A national popularity ranking
`detectedLanguage`	A description of content language	Creator residence or audience location
`uploadedAt`, `scrapedAt`	Content and collection timestamps	Historical exposure during a reporting period
`relatedTags`	Content classification where supplied	Complete coverage of content categories

Fields unavailable on a surface are omitted. Their absence isn't a negative finding about the video.

With enrichVideos enabled, detail requests can fill missing language and other metadata. Failed detail requests retain the original row, so this setting doesn't guarantee language completeness.

Empty snapshots belong in the collection record

A collection manifest should survive even when its dataset has no rows. Otherwise an empty observation disappears from your audit, leaving only the successful-looking intervals.

This cell records request boundaries separately from returned timestamps and retrieves the dataset through the streaming interface.

observations = []
manifest_rows = []

for snapshot_id, claimed_country in enumerate(claimed_countries):
    started_at = pd.Timestamp.now(tz="UTC")
    run = client.actor(
        "dataspryva/snapchat-topic-spotlight-feed-and-content-tag"
    ).call(run_input=run_input)
    finished_at = pd.Timestamp.now(tz="UTC")

    if run["status"] != "SUCCEEDED":
        raise RuntimeError(f"Collection stopped: {run['status']}")

    saved_rows = 0
    for item in client.dataset(run["defaultDatasetId"]).iterate_items():
        observations.append({**item, "_snapshot": snapshot_id})
        saved_rows += 1

    manifest_rows.append({
        "_snapshot": snapshot_id,
        "claimed_country": claimed_country,
        "started_at": started_at,
        "finished_at": finished_at,
        "saved_rows": saved_rows,
    })

manifest = pd.DataFrame(manifest_rows)

_snapshot is a local analysis column.

Request boundaries tell you when collection happened. They don't turn the interval between requests into continuous monitoring. For a reporting window, you can describe your collection schedule, but you can't reconstruct exposure between observations.

Surface provenance isn't geographic evidence

A coverage audit should preserve the evidence you actually have: video identities, content language, collection timestamps and the surface associated with each observation.

The normaliser below treats omitted values and blank strings as missing, keeps structured values inspectable as JSON, and parses timestamps without inventing replacements.

def normalise_value(value):
    if isinstance(value, (dict, list)):
        text = json.dumps(value, ensure_ascii=False, sort_keys=True)
    elif pd.isna(value):
        return pd.NA
    else:
        text = str(value)
    return text.strip() or pd.NA

observed = pd.DataFrame(observations).reindex(columns=[
    "_snapshot", "snapId", "detectedLanguage", "topicSource", "scrapedAt"
])
observed["_snapshot"] = pd.to_numeric(
    observed["_snapshot"], errors="raise"
).astype("Int64")

for field in ["snapId", "detectedLanguage", "topicSource"]:
    observed[field] = observed[field].map(normalise_value)

observed["scrapedAt"] = pd.to_datetime(
    observed["scrapedAt"], utc=True, errors="coerce"
)

Now count saved appearances by returned surface, retaining missing provenance as a group rather than silently dropping it.

surface_audit = (
    observed.groupby(["_snapshot", "topicSource"], dropna=False)
    .size()
    .rename("saved_observations")
    .reset_index()
    .merge(
        manifest[
            ["_snapshot", "claimed_country", "started_at", "finished_at"]
        ],
        on="_snapshot",
        how="left",
        validate="many_to_one",
    )
)

surface_audit describes where observations came from. The country claim remains visibly attached to collection.

That distinction matters when you combine exports later. A manifest label can travel alongside every video without becoming evidence about any video's location.

Even when another dataset supplies a location variable, the referent matters: creator location, content location, viewer location and distribution market are different variables. We'd rather leave the geographic claim unresolved than let a familiar column name settle it.

Language shares need named denominators

A language chart can be defensible if its title names content language and its denominator is explicit.

Calculate both the availability of detectedLanguage among saved observations and the distribution among observations where that field is available. The count columns below are derived locally, rather than read from video records.

snapshot_counts = observed.groupby("_snapshot").agg(
    language_available=("detectedLanguage", "count"),
    id_available=("snapId", "count"),
    distinct_identified_videos=("snapId", "nunique"),
    valid_scraped_at=("scrapedAt", "count"),
)

language_audit = manifest.merge(
    snapshot_counts, on="_snapshot", how="left"
)
count_columns = list(snapshot_counts.columns)
language_audit[count_columns] = (
    language_audit[count_columns].fillna(0).astype("int64")
)
language_audit["language_availability"] = (
    language_audit["language_available"]
    / language_audit["saved_rows"].replace(0, float("nan"))
)

language_rows = (
    observed.dropna(subset=["detectedLanguage"])
    .groupby(["_snapshot", "detectedLanguage"])
    .size()
)
language_denominators = language_audit.set_index("_snapshot")

language_counts = pd.DataFrame({
    "language_rows": language_rows,
    "share_of_saved_observations": language_rows.div(
        language_denominators["saved_rows"], level="_snapshot"
    ),
    "share_with_language_available": language_rows.div(
        language_denominators["language_available"], level="_snapshot"
    ),
}).reset_index()

Those shares answer different questions. One retains missing language in the denominator; the other describes only the labelled subset.

Neither becomes an audience-location estimate.

These are also observation counts, so a video encountered in repeated snapshots contributes repeated appearances. A distinct-video language analysis needs a separate rule for resolving inconsistent labels attached to the same identity.

That choice belongs in your method, not inside an unnoticed drop_duplicates().

Repeated videos measure recurrence

Repeated collection can reveal how much your observed feed repeats. It can't supply unknown inclusion probabilities or turn accumulated videos into a representative national sample.

The worked example uses Snapchat Topic Spotlight Feed & Content Tag Extractor.

This cell compares identity sets in collection order and tracks cumulative distinct identified videos.

identified = observed.dropna(subset=["snapId"]).drop_duplicates(
    ["_snapshot", "snapId"]
)
sets_by_snapshot = (
    identified.groupby("_snapshot")["snapId"].agg(set).to_dict()
)

seen = set()
previous = None
overlap_rows = []

for snapshot_id in manifest["_snapshot"]:
    current = sets_by_snapshot.get(snapshot_id, set())
    comparable = bool(previous) and bool(current)

    overlap_rows.append({
        "_snapshot": snapshot_id,
        "identified_videos": len(current),
        "shared_with_previous": (
            len(current & previous) if comparable else pd.NA
        ),
        "jaccard_with_previous": (
            len(current & previous) / len(current | previous)
            if comparable else pd.NA
        ),
        "new_identified_videos": len(current - seen),
        "cumulative_distinct_identified_videos": len(seen | current),
    })

    seen |= current
    previous = current

overlap_audit = manifest.merge(
    pd.DataFrame(overlap_rows), on="_snapshot", how="left"
)

Jaccard overlap compares shared identities with the union of identities. Here it remains unreported when either snapshot lacks usable IDs.

The manifest keeps empty snapshots visible, while language_audit exposes identity availability. Read those alongside overlap: missing identifiers are a measurement limitation, not evidence of feed turnover.

A growing cumulative total means you've encountered additional identifiable videos. It doesn't tell you what fraction of any country's feed you've covered.

The caption carries the sampling claim

“Detected language among anonymous Spotlight feed observations” names a measurable subject.

“Trending videos in the UK” requires evidence this collection doesn't supply.

For a chart or report, state the collection interval, whether the unit is a saved appearance or a distinct identified video, and the denominator used for language shares. Include language availability alongside the distribution so readers can see how much content remains unclassified.

Keep analyst-assigned country labels in the collection method. If they appear as chart groups, readers can reasonably mistake them for observed geography.

The audience remains outside the frame

You can describe public-feed observations, content metadata and recurrence across snapshots. These measurements don't locate the people watching, establish where creators live or identify the market where a video is popular.

Engagement counts don't repair that gap. A count attached to a video doesn't allocate its audience to a country.

A country name is a claim about a population. It isn't decoration.

A shared registered agent is not a shared company owner

Dataspryva — Thu, 05 Nov 2026 08:17:14 +0000

Your company network may have a service provider at its centre, just as you're about to publish it as a map of common ownership.

The join looks persuasive: matching names, matching addresses, a dense cluster of filings. But registeredAgentName records a different relationship from officers or authorizedPersons. Collapsing them into a single person-to-company edge changes what the graph claims.

A shared registered agent alone doesn't establish shared ownership or control.

What does a company record actually represent?

The Sunbiz lookup Actor returns a filing profile for each result. Its documentNumber identifies the filing; entityName supplies the registered name. Neither identifies a person.

That distinction matters before you draw anything. A trademark filing can also appear among the results, but its owner owns the mark. That relationship doesn't establish ownership of businesses with similar names.

Keep the filing between the relationship and your interpretation:

registered-agent name ── receives process for ── business filing
officer name          ── holds listed office in ── business filing
authorised name       ── has listed LLC role in ── business filing

trademark owner       ── owns mark in ─────────── trademark filing

Registered agents receive service of process. They can be organisations, so calling every node a “person” introduces another assumption before the ownership question even arrives.

A broad search isn't a relationship classification

The officerOrRegisteredAgent lookup can bring unlike connections into the same search result set. The lookup explains how a record arrived; the relationship fields explain what it says.

Name indexes also return nearest entries when there's no exact match. Only documentNumber is a true exact lookup. Search membership therefore needs checking before graph membership.

Example input below uses a broad name lookup and leaves filing types unrestricted so that the subsequent checks remain visible.

import os
import json
import unicodedata

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_input = {
    "searchType": "officerOrRegisteredAgent",
    "searchTerms": ["smith john"],
    "entityType": "any",
    "includeEventHistory": False,
    "mirrorFiledDocuments": "none",
    "maxItems": 25,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/florida-sunbiz-registry-lookup-by-name-agent"
).call(run_input=run_input)

if run["status"] != "SUCCEEDED":
    raise RuntimeError(run["status"])

items = []
for item in client.dataset(run["defaultDatasetId"]).iterate_items():
    items.append(item)

maxItems caps emitted records across the entire input. An apparent end to the network can therefore be an extraction boundary.

The resulting population is the returned filings from this lookup, subject to that cap. It isn't every client, appointment or business associated with the searched name.

The same object shape hides different relationships

Here are the fields that determine the graph's meaning. The nested structures are specified; scalar address layouts and title vocabularies aren't.

Field	Value and availability	Analytical treatment
`documentNumber`	Sunbiz filing identifier; no fixed lexical pattern specified	Nullable string key, never a quantity
`entityName`, `entityType`	Registered name and published filing-type text	Labels and filing context, not identity evidence
`registeredAgentName`	Agent name; omitted when empty, absent from trademark filings	Agent relationship, potentially an organisation
`registeredAgentAddress`	Published agent address; internal format unspecified	Preserve the value; shared premises do not resolve identity
`officers`	Array of `{title, name, address}` for corporate and non-profit officers/directors; empty fields omitted	Corporate office relationships
`authorizedPersons`	Same object shape for Florida LLC managers, members or other authorised persons; empty fields omitted	Preserve the particular listed role
Nested `title`, `name`, `address`	Role text, display name and address; no title enumeration or address subfields specified	Keep raw text alongside any comparison keys
`owners`	Trademark array of `{name, address}`, included when present	A separate mark-ownership relationship

The convenient flattening operation is also the dangerous one. Identical columns make these lists easy to concatenate; they don't make their relationships interchangeable.

An LLC entry can describe membership, management or another authorised role. Preserve title rather than translating every entry into “owner”. Corporate office, management authority and equity ownership are different claims.

Type identifiers and preserve awkward addresses

Treat filing identifiers as nullable strings on ingestion. Numeric conversion invites arithmetic, lost formatting and joins against incompatible identifiers.

Conversion can't restore leading characters already lost upstream.

This cell creates a filing table and conservative text helpers while retaining the original records in items.

records = pd.DataFrame({
    "record_row": range(len(items)),
    "documentNumber": pd.array(
        [item.get("documentNumber") for item in items],
        dtype="string",
    ),
    "entityName": pd.array(
        [item.get("entityName") for item in items],
        dtype="string",
    ),
})

def text_or_na(value):
    return value if isinstance(value, str) and value.strip() else pd.NA

def comparison_name(value):
    if not isinstance(value, str):
        return pd.NA
    normal = unicodedata.normalize("NFC", value)
    return " ".join(normal.casefold().split()) or pd.NA

The comparison key changes case and whitespace. It leaves punctuation, initials and word order alone.

That restraint is deliberate. Removing more characters can manufacture agreement between different names. Even exact display-text agreement remains a candidate match, not a resolved identity.

For addresses, we'd rather retain an awkward value than invent a street-and-postcode schema. JSON encoding preserves its supplied structure, while an explicit presence flag distinguishes an omitted key from a published null.

Missing relationships change the denominator

Empty fields are omitted. Older, inactive and certain filing types can lack values on file; trademarks don't have agents, officers or standard business addresses.

Consequently, an omitted list can't safely become an empty list for an average. That would convert “not supplied here” into “has no listed relationships” and mix filing types with different reporting structures.

The loader below builds business-role edges and an availability ledger. It leaves trademark ownership in the original records for a separate mark analysis.

edge_rows = []
audit_rows = []

for record_row, item in enumerate(items):
    for relationship_source in (
        "registeredAgentName", "officers", "authorizedPersons"
    ):
        value = item.get(relationship_source)
        entries = []

        if relationship_source not in item:
            state = "omitted"
        elif value is None:
            state = "null"
        elif relationship_source == "registeredAgentName":
            if isinstance(value, str) and value.strip():
                state = "present"
                entry = {"name": value}
                if "registeredAgentAddress" in item:
                    entry["address"] = item["registeredAgentAddress"]
                entries = [entry]
            else:
                state = "unexpected_or_blank"
        elif isinstance(value, list):
            state = "present" if value else "empty_list"
            entries = value
        else:
            state = "unexpected_type"

        objects = [entry for entry in entries if isinstance(entry, dict)]
        named_entries = 0

        for entry in objects:
            raw_name = text_or_na(entry.get("name"))
            named_entries += int(pd.notna(raw_name))
            edge_rows.append({
                "record_row": record_row,
                "documentNumber": records.at[record_row, "documentNumber"],
                "relationship_source": relationship_source,
                "title": text_or_na(entry.get("title")),
                "name": raw_name,
                "name_key": comparison_name(raw_name),
                "address_present": "address" in entry,
                "address_json": (
                    json.dumps(entry["address"], ensure_ascii=False)
                    if "address" in entry else pd.NA
                ),
            })

        audit_rows.append({
            "record_row": record_row,
            "relationship_source": relationship_source,
            "state": state,
            "named_entries": named_entries,
            "unparsed_entries": len(entries) - len(objects),
        })

relations = pd.DataFrame(edge_rows, columns=[
    "record_row", "documentNumber", "relationship_source", "title",
    "name", "name_key", "address_present", "address_json",
]).astype({
    "record_row": "int64",
    "documentNumber": "string",
    "relationship_source": "string",
    "title": "string",
    "name": "string",
    "name_key": "string",
    "address_present": "boolean",
    "address_json": "string",
})

audit = pd.DataFrame(audit_rows, columns=[
    "record_row", "relationship_source", "state",
    "named_entries", "unparsed_entries",
]).astype({
    "record_row": "int64",
    "relationship_source": "string",
    "state": "string",
    "named_entries": "int64",
    "unparsed_entries": "int64",
})

Unexpected list entries stay visible in the ledger rather than becoming invented people. Objects without a usable name remain in the edge table but can't participate in name matching.

This cell separates availability counts from the average among supplied, structurally readable lists.

availability = (
    audit.groupby(["relationship_source", "state"], dropna=False)
    .size()
    .rename("record_count")
    .reset_index()
)

readable_lists = audit.loc[
    audit["relationship_source"].isin(["officers", "authorizedPersons"])
    & audit["state"].isin(["present", "empty_list"])
    & audit["unparsed_entries"].eq(0)
]

list_statistics = (
    readable_lists.groupby("relationship_source")
    .agg(
        denominator_records=("record_row", "size"),
        mean_named_entries=("named_entries", "mean"),
    )
    .reset_index()
)

Call that metric “mean named entries among supplied, readable lists”. It counts entries, including repeated names; it doesn't count distinct people.

Report its denominator alongside the availability table. Without filing-type-specific eligibility checks, the omitted category combines structurally inapplicable fields with unavailable information.

Compare like relationships before drawing links

DuckDB can assemble the graph table without erasing the source field. UNION ALL retains the observations; deduplication belongs to the particular question you ask next.

This cell adds relationship labels while preserving raw titles and addresses.

con = duckdb.connect()
con.register("relations", relations)

edges = con.sql("""
    SELECT *, 'registered_agent' AS relationship_kind
    FROM relations
    WHERE relationship_source = 'registeredAgentName'

    UNION ALL

    SELECT *, 'corporate_office' AS relationship_kind
    FROM relations
    WHERE relationship_source = 'officers'

    UNION ALL

    SELECT *, 'llc_authorized_role' AS relationship_kind
    FROM relations
    WHERE relationship_source = 'authorizedPersons'
""").df()

con.register("edges", edges)

Now compare repeated names within each relationship. The next query counts distinct filing identifiers per comparison name, so duplicate rows or repeated appointments don't inflate that particular count.

shared_names = con.sql("""
    WITH memberships AS (
        SELECT DISTINCT
            relationship_kind,
            name_key,
            documentNumber
        FROM edges
        WHERE name_key IS NOT NULL
          AND documentNumber IS NOT NULL
          AND trim(documentNumber) <> ''
    )
    SELECT
        relationship_kind,
        name_key,
        count(*) AS filing_count
    FROM memberships
    GROUP BY relationship_kind, name_key
    HAVING count(*) > 1
    ORDER BY relationship_kind, filing_count DESC, name_key
""").df()

con.close()

The resulting groups are publication leads: repeated agent names, repeated officer names and repeated LLC authorised-person names. Missing filing identifiers are excluded from this comparison and need a separate count before you report its coverage.

A name appearing in different relationship categories remains separate here. Combining those categories requires an explicit analytical decision and evidence that the names identify the same party.

Where the ownership claim runs out of evidence

Florida Sunbiz Registry Lookup by Name, Agent & FEI supplies filing relationships. Turning them into beneficial ownership, personal identity or practical control requires further evidence.

Names and addresses can support an investigation, but neither is a reliable identity key on its own. Shared offices, service providers and namesakes can all produce plausible-looking connections.

Timing needs similar care. A collection of current filing profiles doesn't establish that every appointment overlapped historically. Dates attached to filing events don't automatically date every relationship in the profile.

For a publishable chart, keep the edge language as narrow as the evidence: “lists the same registered-agent name”, “lists the same officer name”, or “lists the same LLC authorised-person name”. State the search boundary and distinguish unresolved name matches from identified parties.

The graph can show a connection perfectly and still give it the wrong name.

Your first-seen product count is not a launch count

Dataspryva — Wed, 04 Nov 2026 08:17:47 +0000

You're about to label a chart “new product launches”. Your feed uses includeNewOnly and onlyNewProducts, so the wording feels defensible.

The catch: the former keeps products Sephora flags as new; the latter skips product IDs already returned under the same stateKey. Together, they select previously unseen product IDs carrying a current merchandising flag.

First observed is a collection event, not a launch date.

What does your new product count actually measure?

The tempting question is how many products launched during your reporting period. The answer available from this feed is narrower: how many eligible product IDs your collection encountered for the first time.

Those events can coincide. The settings don't establish that they did.

A product might enter your feed because it appeared in the category you monitor, because you expanded your coverage, or because its ID was absent from your saved history. None of those explanations requires a launch during the reporting period.

For an observation series, we'd start by removing both novelty gates and recording the retailer flag separately. Example input for a skincare collection:

run_input = {
    "mode": "category",
    "categories": ["skincare"],
    "market": "us",
    "sortBy": "newArrivals",
    "includeNewOnly": False,
    "onlyNewProducts": False,
    "stateKey": "us-skincare-observation",
    "resetState": False,
    "includeDetails": True,
    "includeImages": False,
    "maxItems": 10000,
    "maxItemsPerQuery": 0,
}

This changes the analytical question. You can observe products whether or not they currently carry the new flag, then calculate your own first-observation history.

It doesn't turn a category collection into a complete catalogue census.

A product ID can hide a new shade

Saved discovery state works at productId grain. A shade or size can be added beneath an existing parent product without creating another parent ID.

With incremental suppression enabled, that continuing product can be skipped before your dataset gives you another opportunity to inspect its variants.

The eligibility rule is easier to see as an intersection; this diagram doesn't imply an execution order:

Products reachable through your chosen inputs
    ∩ products carrying the retailer's new flag
    ∩ product IDs absent from the named saved state
    → eligible discovery records

Existing product ID + newly added shade
    → still an existing product ID

That creates a particular bias in an assortment story. A count of new parent IDs can miss expansion within continuing products. A brand adding options beneath existing products and a brand introducing new parent products need not move your metric in the same way.

You should check which pattern matters in your coverage. The dataset can't settle it through parent-ID discovery alone.

The same issue affects the denominator: “share of launches expanding shade choice” can't be calculated from a feed that systematically suppresses the continuing products where such expansion might occur.

Ordering becomes selection when the cap bites

The example orders listings by newArrivals and permits up to 10000 emitted records. Its per-source cap is 0, which removes that particular restriction; the overall cap still applies.

If a collection reaches its overall cap, ordering helps determine which records you observe. “Newest first” remains a listing order, not a supplied launch timestamp.

Record count is therefore a useful warning signal. Reaching the cap means you should investigate coverage before comparing periods. Finishing below it doesn't prove completeness.

For broader coverage, divide a declared scope into stable categories or brands and deduplicate by product ID within the selected market. Keep the partitions stable across reporting periods, and inspect capped partitions separately.

Otherwise, a change in collection coverage can make a discovery chart rise while the underlying assortment has done nothing especially newsworthy.

Keep the collection rule beside the rows

Your dataset needs a companion manifest. It should preserve the inputs, the collection time, and the baseline against which “first” has meaning.

The following cell fetches records, retains the complete chosen input, and handles omitted identity, flag and timestamp fields explicitly:

import os
from uuid import uuid4

import pandas as pd
import duckdb
from apify_client import ApifyClient

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/sephora-catalog-price-and-rating-finder"
).call(run_input=run_input)

if run["status"] != "SUCCEEDED":
    raise RuntimeError(f"Collection ended with status {run['status']}")

items = []
for item in client.dataset(run["defaultDatasetId"]).iterate_items():
    items.append(item)

batch_id = uuid4().hex
baseline_epoch = "us-skincare-start"
collected_at = pd.Timestamp.now(tz="UTC")

manifest = pd.DataFrame([{
    **run_input,
    "batch_id": batch_id,
    "baseline_epoch": baseline_epoch,
    "collected_at": collected_at,
    "emitted_records": len(items),
    "cap_reached": len(items) >= run_input["maxItems"],
}])

records = pd.DataFrame(items).reindex(
    columns=["productId", "skuId", "isNew", "scrapedAt"]
)
records["productId"] = records["productId"].astype("string").str.strip()
records["skuId"] = records["skuId"].astype("string").str.strip()
records["isNew"] = records["isNew"].map(
    lambda value: value if isinstance(value, bool) else pd.NA
).astype("boolean")
records["scrapedAt"] = pd.to_datetime(
    records["scrapedAt"], utc=True, errors="coerce"
)

unidentified_records = records.loc[
    records["productId"].isna() | records["productId"].eq("")
].copy()

observations = records.loc[
    records["productId"].notna() & records["productId"].ne("")
].copy()
observations = observations.assign(
    batch_id=batch_id,
    baseline_epoch=baseline_epoch,
    observed_at=collected_at,
)

Here, observed_at means when your collector received the batch. scrapedAt remains the source record's collection timestamp, parsed independently.

That distinction matters when a run spans a reporting boundary. Choose a clock, name it, and keep using it.

An omitted isNew stays unknown. Turning absence into False would quietly manufacture a claim about retailer labelling.

Your opening batch is inventory, not discovery

The next cell creates an in-memory archive and appends the batch in bulk:

db = duckdb.connect()
db.register("incoming_manifest", manifest)
db.register("incoming_observations", observations)

db.execute("""
    CREATE TABLE run_history AS
    SELECT * FROM incoming_manifest WHERE false
""")
db.execute("""
    CREATE TABLE product_history AS
    SELECT * FROM incoming_observations WHERE false
""")
db.execute("INSERT INTO run_history SELECT * FROM incoming_manifest")
db.execute("INSERT INTO product_history SELECT * FROM incoming_observations")

db.execute("""
    CREATE VIEW first_products AS
    SELECT baseline_epoch, productId,
           min(observed_at) AS first_observed_at
    FROM product_history
    GROUP BY baseline_epoch, productId
""")

This notebook starts with empty history, so its initial batch establishes a baseline. It can't reconstruct earlier encounters. A scheduled collector needs a durable archive that subsequent batches append to.

Now calculate independent measures, including unknown retailer flags:

product_measures = db.sql("""
    SELECT
        r.baseline_epoch,
        r.batch_id,
        r.collected_at,
        r.batch_id = first_value(r.batch_id) OVER (
            PARTITION BY r.baseline_epoch
            ORDER BY r.collected_at, r.batch_id
        ) AS is_baseline,
        count(DISTINCT p.productId) AS observed_products,
        count(DISTINCT p.productId) FILTER (
            WHERE p.isNew IS TRUE
        ) AS retailer_new_products,
        count(DISTINCT p.productId) FILTER (
            WHERE p.isNew IS NULL
        ) AS products_with_unknown_new_flag,
        count(DISTINCT p.productId) FILTER (
            WHERE p.observed_at = f.first_observed_at
        ) AS first_observed_products
    FROM run_history r
    LEFT JOIN product_history p
        USING (baseline_epoch, batch_id)
    LEFT JOIN first_products f
        USING (baseline_epoch, productId)
    GROUP BY r.baseline_epoch, r.batch_id, r.collected_at
""").df()

Keep baseline batches out of a chart of subsequent discoveries, or display them explicitly as baseline inventory. Otherwise, starting collection becomes your biggest apparent launch event. Convenient for the chart, awkward for the correction.

Resetting the Actor's saved state forgets previously seen IDs for that key. It doesn't erase encounters from your independent archive. If your series relies on that saved state, mark the reset as a baseline break; don't splice the resulting rediscoveries into an uninterrupted trend.

Separate product discovery from variant discovery

The Sephora Catalog Price & Rating Finder includes a size and shade matrix in detailed product records. Revisiting continuing products is what makes that detail useful for change analysis.

A listing record's top-level skuId isn't an enumeration of that matrix. A newly observed value can identify something to inspect, but a change in the represented variant isn't sufficient evidence of a newly launched shade.

This cell finds newly observed top-level product/SKU pairs within continuing products:

sku_observations_to_review = db.sql("""
    WITH first_skus AS (
        SELECT baseline_epoch, productId, skuId,
               min(observed_at) AS first_sku_observed_at
        FROM product_history
        WHERE skuId IS NOT NULL AND skuId <> ''
        GROUP BY baseline_epoch, productId, skuId
    )
    SELECT s.*, p.first_observed_at AS product_first_observed_at
    FROM first_skus s
    JOIN first_products p
        USING (baseline_epoch, productId)
    WHERE s.first_sku_observed_at > p.first_observed_at
""").df()

These are review candidates, not a count of variant additions. Establishing additions requires comparable variant inventories from repeated detail observations, with stable variant identities and a clear policy for missing detail.

An absent matrix should remain an unknown observation, rather than becoming evidence that every previous shade disappeared.

Give each measure its own caption

Before publication, match the chart label to the actual unit of observation:

Measure	What qualifies	What the caption needs to preserve
First-observed products	A `productId` absent from your earlier observations within the same baseline	Declared market and scope, collection schedule, baseline and coverage changes
Retailer-new products	An observed product with `isNew` equal to `True`	The flag applies at observation time; omitted flags remain unknown
SKU review candidates	A previously unobserved top-level `skuId` within a continuing product	A candidate for inspection, not a verified variant addition

Keep the measures in separate tables or chart series. Their overlap can be useful, but combining them into a single “launches” column discards the distinction your reporting needs.

Capped batches also belong in the published methodology. A lower discovery count after a change in coverage isn't automatically evidence of slower product introduction.

The launch date is still missing

Neither the retailer flag nor your observation history establishes when a product originally launched, when a shade first became available, or whether it debuted elsewhere before entering your coverage.

Even comparable variant inventories only locate an observed change between collection points. They don't recover the precise moment it happened, and missing detail can leave the change unresolved.

Your chart can date the encounter. It can't backdate the launch.

Your price alert may be a store switch in disguise

Dataspryva — Tue, 03 Nov 2026 08:17:15 +0000

Your price alert can fire because the store changed underneath it.

You've joined on itemId, calculated the difference and prepared a chart. Then your manager asks whether both prices came from the same store. That's an uncomfortable question to answer after the chart has acquired a colour scheme.

A fixed storeId doesn't guarantee a fixed price store. It identifies the store you requested; storeNumber identifies the price store Lowe's returned, which can be a nearby store.

A fixed request can produce a moving price context

For a defensible price series, the product identifier is only part of the comparison. You also need the location and currency context attached to each observation.

Dataspryva's Actor reads product data live for each product. Price, stock and fulfilment therefore reflect the collection time, rather than a frozen catalogue.

We'll use an explicit product list to keep discovery changes out of the main comparison. The capture function retains the submitted input alongside the dataset identifier and raw records.

Example input and capture cell, with the token read from the environment:

import json
import os

import duckdb
import pandas as pd
from apify_client import ApifyClient

client = ApifyClient(os.environ["APIFY_TOKEN"])

run_input = {
    "mode": "productDetails",
    "itemIds": ["5013207487", "50040962"],
    "storeId": "0208",
    "zipCode": "92253",
    "inStockOnly": False,
    "onSaleOnly": False,
    "onShelfOnly": False,
    "includeDetails": False,
    "maxItems": 50,
}

def capture():
    run = client.actor(
        "dataspryva/lowes-price-stock-and-specification-scraper"
    ).call(run_input=run_input)

    if run["status"] != "SUCCEEDED":
        raise RuntimeError(f"Capture incomplete: {run['status']}")

    records = []
    for item in client.dataset(run["defaultDatasetId"]).iterate_items():
        records.append(item)

    return {
        "dataset_id": run["defaultDatasetId"],
        "run_input": run_input.copy(),
        "records": records,
    }

captures = {"before": capture()}

Keeping the availability and sale filters off matters here. Otherwise, a product crossing a filter boundary could disappear from your snapshot just when its price becomes interesting.

Which identifier belongs in the join?

Use itemId as the product anchor. Titles are useful for human inspection, but making them join keys turns an editorial change into an apparent product departure.

The observation key also needs the snapshot identifier and requested context. That lets you collect the same product for different locations without merging those observations.

The fields fall into these roles:

Role	Fields retained	Comparison rule
Product identity	`itemId`	Join exactly; reject missing or duplicate keys
Requested context	Input `storeId`, `zipCode`	Retain from the input manifest and include in the join
Returned price context	`storeNumber`, `storeZip`, `priceScope`, `currency`	Require usable, matching values before subtraction
Measurement and timing	`price`, `scrapedAt`	Parse separately; retain the original representation

The input manifest is deliberate. It preserves what you asked for independently of which optional fields appear in a returned record.

Here's the comparison boundary:

same item + same requested context
                 |
       returned price context
          /              \
     matches             differs
        |                   |
  compare prices      context review

A store transition is useful information. It just belongs in a different event category from a price movement.

Keep the request beside the snapshot

The next cell captures the later observation. In a scheduled tracker, those captures belong at your collection intervals; executing this notebook straight through makes consecutive captures, not a historical backfill.

Empty fields are omitted from records. We make those absences explicit before joining, rather than letting a missing column decide whether the notebook runs.

Store identifiers remain strings. For the returned store number, we accept only digits within the store-number width and restore leading zeros. We don't extract digits from arbitrary text and hope they identify a store.

Timestamps are converted to UTC before removing timezone metadata for storage. Every observed_at value therefore uses the same UTC convention.

This cell creates the comparison frame and stops ambiguous product joins:

captures["after"] = capture()

field_map = {
    "itemId": "item_id",
    "storeNumber": "store_number",
    "storeZip": "store_zip",
    "priceScope": "price_scope",
    "currency": "currency",
    "price": "price_raw",
    "scrapedAt": "scrapedAt",
}

def normalise(snapshot_id, captured):
    frame = pd.DataFrame(captured["records"]).reindex(
        columns=list(field_map)
    ).rename(columns=field_map)

    for column in field_map.values():
        frame[column] = (
            frame[column].astype("string").str.strip().replace("", pd.NA)
        )

    valid_store = frame["store_number"].str.fullmatch(
        r"[0-9]{1,4}", na=False
    )
    frame["store_number"] = (
        frame["store_number"].where(valid_store).str.zfill(4)
    )
    frame["store_zip"] = frame["store_zip"].where(
        frame["store_zip"].str.fullmatch(r"[0-9]{5}", na=False)
    )
    frame["observed_at"] = pd.to_datetime(
        frame["scrapedAt"], errors="coerce", utc=True
    ).dt.tz_localize(None)
    frame["snapshot_id"] = snapshot_id
    frame["requested_store"] = captured["run_input"]["storeId"]
    frame["requested_zip"] = captured["run_input"]["zipCode"]
    return frame

snapshot_frame = pd.concat(
    [normalise(label, captured) for label, captured in captures.items()],
    ignore_index=True,
)

observation_key = [
    "snapshot_id", "requested_store", "requested_zip", "item_id"
]
if snapshot_frame["item_id"].isna().any():
    raise ValueError("Missing itemId: product identity needs review")
if snapshot_frame.duplicated(observation_key).any():
    raise ValueError("Duplicate observation key: refusing an ambiguous join")

manifest_frame = pd.DataFrame([
    {
        "snapshot_id": label,
        "dataset_id": captured["dataset_id"],
        "input_json": json.dumps(captured["run_input"], sort_keys=True),
    }
    for label, captured in captures.items()
])

The Actor deduplicates products across queries and pages, but a snapshot boundary still deserves its own check. A later concatenation or ingestion retry can introduce duplicates even when the original collection didn't.

Dropping duplicates would hide which observation survived. Refusing the join leaves the disagreement available for inspection.

Store a value you can defend

A price parser should have an explicit boundary. Removing every non-numeric character can turn a range or explanatory label into a plausible amount. Plausible amounts are particularly efficient at reaching reports.

The parser below accepts plain numeric strings and dollar-prefixed amounts, including comma grouping. It stores decimal values and leaves unsupported formats null. It doesn't infer currency from the symbol.

This cell creates the snapshot tables and enforces the observation key:

con = duckdb.connect()
con.register("snapshot_frame", snapshot_frame)
con.register("manifest_frame", manifest_frame)

con.execute(r"""
CREATE TABLE snapshot_manifest AS
SELECT * FROM manifest_frame;

CREATE TABLE snapshots AS
SELECT *,
    CASE
        WHEN regexp_full_match(
            price_raw,
            '\$?\s*(?:[0-9]+|[0-9]{1,3}(?:,[0-9]{3})+)(?:\.[0-9]{1,4})?'
        )
        THEN TRY_CAST(
            replace(replace(price_raw, '$', ''), ',', '')
            AS DECIMAL(18,4)
        )
    END AS price
FROM snapshot_frame;

CREATE UNIQUE INDEX observation_key ON snapshots
(snapshot_id, requested_store, requested_zip, item_id);
""")

The notebook uses an in-memory database so it needs no local files. For a continuing tracker, retain these tables, the raw captures and their manifests in durable storage, using an immutable identifier for each snapshot.

Keep price_raw even after parsing. A null parsed price can then be traced to an absent value, an unsupported representation or an amount outside the chosen decimal range.

Missing price never becomes zero.

Classify the context before subtracting

A full outer join preserves products present in either snapshot. An inner join would quietly remove the membership changes from your audit.

For matched products, we use a conservative comparison policy: all returned context fields must be present and equal. priceScope stays an opaque label because we don't need to invent a vocabulary to compare it.

This policy may withhold usable observations when context is incomplete. That's an explicit cost of making the remaining comparisons easier to defend.

The query assigns context events before considering price values:

con.execute("""
CREATE TABLE snapshot_diff AS
WITH paired AS (
    SELECT b AS prior_row, a AS next_row
    FROM (
        SELECT * FROM snapshots WHERE snapshot_id = 'before'
    ) b
    FULL OUTER JOIN (
        SELECT * FROM snapshots WHERE snapshot_id = 'after'
    ) a
    USING (item_id, requested_store, requested_zip)
)
SELECT *,
    CASE
        WHEN prior_row.item_id IS NULL THEN 'entered_snapshot'
        WHEN next_row.item_id IS NULL THEN 'left_snapshot'
        WHEN prior_row.store_number IS NULL
          OR next_row.store_number IS NULL
          OR prior_row.store_zip IS NULL
          OR next_row.store_zip IS NULL
          OR prior_row.price_scope IS NULL
          OR next_row.price_scope IS NULL
          OR prior_row.currency IS NULL
          OR next_row.currency IS NULL
            THEN 'context_missing'
        WHEN prior_row.store_number IS DISTINCT FROM next_row.store_number
          OR prior_row.store_zip IS DISTINCT FROM next_row.store_zip
          OR prior_row.price_scope IS DISTINCT FROM next_row.price_scope
          OR prior_row.currency IS DISTINCT FROM next_row.currency
            THEN 'context_changed'
        WHEN prior_row.observed_at IS NULL
          OR next_row.observed_at IS NULL
          OR next_row.observed_at <= prior_row.observed_at
            THEN 'time_unusable'
        WHEN prior_row.price IS NULL OR next_row.price IS NULL
            THEN 'price_unusable'
        WHEN prior_row.price <> next_row.price THEN 'price_changed'
        ELSE 'price_unchanged'
    END AS event
FROM paired;
""")

The timestamp check prevents an equal or reversed observation time from becoming a forward price movement. Retaining product-level timestamps also avoids pretending that every product was observed simultaneously.

Notice that a context transition takes precedence even when the price is unchanged. Otherwise, an unchanged amount could conceal a break in the series.

Separate price events from catalogue noise

The worked example uses Lowe's Price, Stock & Specification Scraper, but the analytical boundary applies anywhere a requested location can differ from the location supplying an observation.

Now produce separate frames for comparable price movements, context reviews and event counts:

price_changes = con.sql("""
SELECT
    prior_row.item_id AS item_id,
    prior_row.requested_store AS requested_store,
    prior_row.store_number AS price_store,
    prior_row.currency AS currency,
    prior_row.observed_at AS before_at,
    next_row.observed_at AS after_at,
    prior_row.price AS before_price,
    next_row.price AS after_price,
    next_row.price - prior_row.price AS price_delta
FROM snapshot_diff
WHERE event = 'price_changed'
""").df()

context_review = con.sql("""
SELECT *
FROM snapshot_diff
WHERE event IN ('context_missing', 'context_changed')
""").df()

event_counts = con.sql("""
SELECT event, count(*) AS product_observations
FROM snapshot_diff
GROUP BY event
ORDER BY event
""").df()

Those counts belong beside the price chart. Without them, a shrinking comparable set can look like a quieter market. With them, you can distinguish fewer price movements from fewer observations eligible for comparison.

For a store-switch review, inspect the before and after store_number values inside context_review. The category also catches scope, currency and returned ZIP changes, so its total should never be labelled solely as store switches.

Other fields need their own interpretation. A changed position describes placement, not a product price event. A changed title may warrant catalogue review without breaking identity. Differences in stock or fulfilment deserve separate measures rather than being treated as incidental row churn.

Likewise, left_snapshot means absent from that capture. It doesn't establish that a product was withdrawn from sale. Search and category discovery return candidate sets, so membership changes in those modes carry an additional coverage question.

The gaps between observations stay gaps

Matching product and price context makes subtraction interpretable. It doesn't establish why the amount changed, which price a customer paid, or what happened between captures.

A returned-store transition also doesn't reveal why Lowe's selected that store. The records preserve the transition, not its cause.

Your report can defend an observed price difference within a retained context. When that context changes, the useful finding is the break itself.

A tidy line chart shouldn't get to choose the store.

Your city chart can name a filter that never took effect

Dataspryva — Mon, 02 Nov 2026 08:05:35 +0000

Your regional seller chart can carry a city label for a filter that never took effect.

You're preparing the comparison, and DKI Jakarta looks perfectly respectable above a bar. The awkward question is whether that label came from the submitted dropdown or from the location selector that actually governed the query.

cityId overrides city in the Actor's explicit location inputs. If you preserve the dropdown as your reporting label while submitting an ID override, the chart can misstate its sampling frame.

That leaves a useful debugging decision: did you query the wrong geography, or did you label the query incorrectly? There's another possibility too: the labels describe overlapping areas, so the comparison was never a partition.

Which location did you actually request?

For a Tokopedia seller comparison, start with a narrower question than “How big is each regional market?”

“How many distinct sellers appear among the returned listings for this keyword and location selection?” is something these records can support. It names the retrieval mechanism instead of quietly promoting search results into a seller census.

Example inputs below keep the keyword, ordering and product cap fixed while changing the dropdown selection.

import os
from itertools import combinations

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_input = {
    "mode": "search",
    "searchQueries": ["smartphone"],
    "searchUrls": [],
    "sortBy": "relevance",
    "city": "DKI Jakarta",
    "maxItems": 100,
}

plans = {
    "metro": {**run_input, "city": "Jabodetabek"},
    "province": run_input,
    "district": {**run_input, "city": "Jakarta Selatan"},
}

Those query identifiers are labels we add locally. They aren't fields returned by the Actor.

The keyword excludes sellers without matching listings. The product cap can leave matching listings outside the pull, and relevance ordering makes inclusion dependent on ranking rather than random selection.

A seller with many matching listings has more opportunities to appear than a seller with a narrow matching assortment. Distinct-seller counting removes repeated seller IDs after retrieval; it doesn't undo that selection mechanism.

A dropdown isn't a geographic partition

The location menu presents broad areas and their constituents at the same visual level. That makes choosing an option easy and adding the resulting counts dangerous.

Selection	Geographic relationship	Consequence for a comparison
`Jabodetabek`	Includes Jakarta and surrounding cities	Can overlap with constituent-place queries
`DKI Jakarta`	Contains the named Jakarta districts	Cannot be added to district counts as a separate bucket
`Jakarta Selatan`	A constituent area within Jakarta	Represents a different geographic scale

These relationships explain why the labels can't be assumed to form mutually exclusive buckets. They don't establish the source's exact filter boundaries.

The distinction is easier to see as a containment sketch:

Jabodetabek
└── DKI Jakarta
    └── Jakarta Selatan

Geographic containment ≠ guaranteed retrieval containment

That last line matters. Separately capped and ranked queries need not return nested sets, even when their geographic labels describe nested areas.

A product missing from the broader pull may simply sit outside its retrieved window. Absence from that pull doesn't redraw the map.

Keep the selector decision beside the pull

A useful query manifest preserves what you submitted and how you interpreted it. Renaming a spreadsheet tab after retrieval is a surprisingly effective way to lose both.

The following cell parses comma-separated IDs without converting them to numbers. It records the dropdown separately from the selector that takes precedence.

manifest_rows = []

for query_id, actor_input in plans.items():
    submitted_city = actor_input.get("city", "any")
    submitted_ids = actor_input.get("cityId", "")
    parsed_ids = tuple(
        token.strip()
        for token in submitted_ids.split(",")
        if token.strip()
    )
    submitted_urls = actor_input.get("searchUrls", [])

    explicit_label = (
        "cityId=" + ",".join(parsed_ids)
        if parsed_ids
        else "city=" + submitted_city
    )
    explicit_location = bool(parsed_ids) or submitted_city != "any"

    manifest_rows.append({
        "query_id": query_id,
        "city": submitted_city,
        "cityId": submitted_ids,
        "parsed_city_ids": parsed_ids,
        "searchUrls": submitted_urls,
        "explicit_location_label": explicit_label,
        "mixed_location_review": bool(submitted_urls) and explicit_location,
        "effective_input_label": (
            "unresolved URL/input interaction"
            if submitted_urls
            else explicit_label
        ),
    })

query_manifest = pd.DataFrame(manifest_rows)

Search URLs need their own review because filters and sorting carried in the URL are applied. Their precedence relative to explicit location inputs is unspecified.

The flag is deliberately conservative: a supplied URL might contain no location filter at all. Inspect the retained URL before deciding. Guessing a query parameter's meaning would just move the labelling problem into Python.

The manifest also separates an omitted cityId from an inferred location. It records the input decision; it doesn't certify the source's geographic interpretation.

Attach each pull to its query identifier and retain its dataset identifier before counting anything.

client = ApifyClient(os.environ["APIFY_TOKEN"])
pull_rows = []
pull_log = []

for query_id, actor_input in plans.items():
    run = client.actor(
        "dataspryva/tokopedia-search-category-and-price-drop-scraper"
    ).call(run_input=actor_input)

    if run["status"] != "SUCCEEDED":
        raise RuntimeError(f"{query_id}: {run['status']}")

    pull_log.append({
        "query_id": query_id,
        "dataset_id": run["defaultDatasetId"],
    })
    pull_rows.extend(
        {**item, "query_id": query_id}
        for item in client.dataset(
            run["defaultDatasetId"]
        ).iterate_items()
    )

pull_manifest = query_manifest.merge(
    pd.DataFrame(pull_log),
    on="query_id",
    validate="one_to_one",
)

Streaming through the dataset avoids making the comparison depend on a listing response's page size. Rejecting an unsuccessful run also prevents partial retrieval from quietly becoming a regional result.

Count sellers without manufacturing identities

Empty fields are omitted from records. For this question, missing shopId is especially consequential: the listing exists in the pull, but it can't contribute a known seller identity.

Don't replace that missing ID with a shared placeholder and count the placeholder as a seller. Don't substitute shopLocation either; a place label isn't an identity.

This cell preserves missing values and converts identifiers to text before pandas can infer a numeric column.

def clean_text(value):
    if value is None:
        return pd.NA
    cleaned = str(value).strip()
    return cleaned if cleaned else pd.NA

identity_fields = ("productId", "shopId", "shopLocation")

records = pd.DataFrame(
    [
        {
            "query_id": row["query_id"],
            **{
                field: clean_text(row.get(field))
                for field in identity_fields
            },
        }
        for row in pull_rows
    ],
    columns=["query_id", *identity_fields],
).astype("string")

Keep returned rows, identifiable listings and identifiable sellers separate. Each answers a different question.

DuckDB can calculate those counts alongside the omissions that limit their interpretation.

counts = duckdb.sql("""
    SELECT
        query_id,
        count(*) AS returned_rows,
        count(DISTINCT "productId") AS distinct_listings,
        count(DISTINCT "shopId") AS distinct_sellers,
        count(*) FILTER (
            WHERE "productId" IS NULL
        ) AS rows_without_product_id,
        count(*) FILTER (
            WHERE "shopId" IS NULL
        ) AS rows_without_shop_id,
        count(*) FILTER (
            WHERE "shopLocation" IS NULL
        ) AS rows_without_location
    FROM records
    GROUP BY query_id
""").df()

regional_counts = (
    counts.set_index("query_id")
    .reindex(list(plans), fill_value=0)
    .rename_axis("query_id")
    .reset_index()
)

Keeping planned queries with empty results prevents them from disappearing from the report. An empty retrieved set still doesn't establish that no matching sellers exist in the underlying market.

Measure overlap before adding regions

Product overlap and seller overlap reveal different problems.

Shared productId values show duplicated listing membership across pulls. Shared shopId values can also reveal a seller appearing through different listings, which product deduplication alone would miss.

This cell calculates both intersections, retaining each side's denominator.

overlap_rows = []

for identifier in ("productId", "shopId"):
    memberships = {
        query_id: set(
            records.loc[
                records["query_id"].eq(query_id), identifier
            ].dropna()
        )
        for query_id in plans
    }

    for left_query, right_query in combinations(plans, 2):
        left_ids = memberships[left_query]
        right_ids = memberships[right_query]
        shared_ids = left_ids & right_ids

        overlap_rows.append({
            "identifier": identifier,
            "left_query": left_query,
            "right_query": right_query,
            "left_ids": len(left_ids),
            "right_ids": len(right_ids),
            "shared_ids": len(shared_ids),
            "left_shared_fraction": (
                len(shared_ids) / len(left_ids)
                if left_ids else pd.NA
            ),
            "right_shared_fraction": (
                len(shared_ids) / len(right_ids)
                if right_ids else pd.NA
            ),
        })

overlap = pd.DataFrame(overlap_rows)

Missing identifiers can't participate in these intersections. Report their omission counts beside the overlap table.

Observed overlap demonstrates shared retrieved membership. An empty intersection doesn't establish disjoint geography: ranking, truncation, query timing and missing IDs can all limit what the comparison observes.

Build comparisons you can defend

For this worked example, Tokopedia Search, Category & Price Drop Scraper supplies listing and seller identifiers; the manifest supplies the analytical meaning of each pull.

Use a single location-control route per comparison. With dropdown inputs, omit cityId and URL-carried filters. With exact IDs, retain their source mapping and label the comparison from that mapping. With filtered search URLs, preserve the URLs and inspect their location selections before assigning chart labels.

Keep the other selection conditions fixed. Changing keyword, sorting or eligibility filters alongside geography makes a regional difference harder to interpret.

Equal caps don't create equal coverage. A pull that reaches maxItems is a reason to examine truncation; a pull below it isn't proof of exhaustive coverage.

For overlapping areas, report separate query counts. If you also calculate a union, deduplicate by the identity relevant to the metric and call it the union of retrieved listings or sellers. Adding the bars gives repeated identities extra votes.

A defensible chart caption names the keyword, location-control route, ordering, cap, retrieval window and missing-ID treatment. “Distinct sellers observed in returned keyword results” is less glamorous than “regional market size”. It's also a claim the dataset can carry.

The geography stops at the seller

shopLocation describes the seller geography exposed by the source. It doesn't establish buyer demand, delivery coverage or warehouse inventory.

These pulls also can't establish the source's full geographic boundary definitions. Shared identifiers help diagnose overlap; they don't turn a search interface into an administrative boundary dataset.

Your city label is a claim about who could enter the count. Make it earn its place above the bar.

Your premium preview audit may be measuring a probe cap

Dataspryva — Sun, 01 Nov 2026 08:14:29 +0000

Your catalogue still renders, but imageUrls.smallS3 appears in one pull and disappears in another. Before you publish a delivery-path comparison, there's a boundary inside the collector to account for.

The Actor checks up to 100 premium photos for an S3 copy per detail run.

That makes an absent S3 URL ambiguous: the copy might not have responded, or the photo might not have received a check. The fallback uses the always-present plus.unsplash.com variants, so a working preview can conceal a change in probe coverage.

Keep the input beside the pull

A host comparison needs the collection settings that produced its rows. Without them, you can't distinguish a changed delivery path from a changed population or an optional pass being disabled.

Use this Example search input to keep the request beside its returned records.

import os
from urllib.parse import urlsplit

import pandas as pd
from apify_client import ApifyClient

S3_CHECK_CAP = 100

run_input = {
    "mode": "search",
    "searchQueries": ["nature"],
    "orientation": "any",
    "freeOnly": False,
    "includePhotoDetails": True,
    "maxItems": 1000,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/unsplash-photo-search-by-topic-collection"
).call(run_input=run_input)

if run["status"] != "SUCCEEDED":
    raise RuntimeError(f"Collection ended with {run['status']}")

items = client.dataset(run["defaultDatasetId"]).list_items().items
pulls = [{"pull": "catalogue-a", "input": run_input.copy(), "items": items}]

For comparisons, retain each collection in pulls with a distinct label, its actual input and its returned items. A label identifies a pull; it doesn't establish that its settings match another pull.

Keep this distinction in view:

premium photo
    |
    +-- checked, copy responds ------------------> smallS3 included
    |
    +-- no responding copy returned ------------> plus variants

returned URL ------------------------------------> metadata evidence
successful image transfer -----------------------> separate measurement

A cap tells you a maximum, not a sampling design. It gives you no basis for treating the checked photos as a random sample or inferring check order from exported row order.

That matters if you were about to turn the share of records containing smallS3 into an estimate of storage availability. The denominator includes photos whose probe opportunity you can't establish.

What population can reach the catalogue?

This is public website photo data. Keyword searches, curated topics, collections, portfolios, specific photos and related-photo feeds define different populations. Illustrations are excluded.

These boundaries matter before any host is counted:

Coverage dimension	Boundary	Consequence for the audit
Retrieval depth	`maxItems` limits emitted photos across all supplied sources; keyword searches also have a 4,000-row window	A returned catalogue isn't a census of matching photography
Filters and ordering	Orientation applies in every mode; dominant colour filters keyword searches; `freeOnly` excludes premium photos	Changing an input can change the population behind the host shares
Geography	Detail location fields depend on photographer-supplied information	Missing coordinates don't establish where a photo was taken
Time	Each run reads live data, without caching between runs	Repeated discovery pulls aren't guaranteed to return a fixed population
Optional fields	Details add tags, EXIF, location and free-photo counts when available; empty fields are omitted	An absent field needs a different interpretation from a measured zero

There isn't a publication-date range input. Newest-first ordering is an ordering choice, not a bounded time window.

Premium per-photo views and downloads are suppressed. Photographer lifetime totals don't fill that gap: they describe a photographer, not the individual asset you're comparing.

For this audit, geography and category are population descriptors rather than explanations of delivery behaviour. A host difference between topics could reflect different assets, different premium composition or different probe opportunity. The category label alone doesn't distinguish those explanations.

Count identifiable premium photos first

A missing premium marker shouldn't quietly become “free”. Nor should a non-empty plus value become a Boolean merely because Python considers it truthy.

The following cell accepts explicit Boolean markers and recognised licence values, leaves disagreements unresolved, and preserves the original flags for inspection.

def premium_class(item):
    signals = [
        item.get(key)
        for key in ("isPremium", "premium", "plus")
        if type(item.get(key)) is bool
    ]
    licence_signal = {
        "Unsplash License": False,
        "Unsplash+ License": True,
    }.get(item.get("license"))
    if licence_signal is not None:
        signals.append(licence_signal)
    if not signals or len(set(signals)) != 1:
        return "unresolved"
    return "premium" if signals[0] else "free"

photo_columns = [
    "pull", "record", "id", "premium_class", "license",
    "isPremium", "premium", "plus", "imageUrls",
]
photos = pd.DataFrame([
    {
        "pull": pull["pull"],
        "record": position,
        "id": (
            item["id"].strip()
            if isinstance(item.get("id"), str) and item["id"].strip()
            else None
        ),
        "premium_class": premium_class(item),
        "license": item.get("license"),
        "isPremium": item.get("isPremium"),
        "premium": item.get("premium"),
        "plus": item.get("plus"),
        "imageUrls": item.get("imageUrls"),
    }
    for pull in pulls
    for position, item in enumerate(pull["items"])
], columns=photo_columns)

Count distinct identifiable premium photos separately from emitted records. Duplicate rows shouldn't manufacture evidence that the probe ceiling was crossed.

This cell flags detail-enabled pulls whose returned premium population alone exceeds that ceiling.

photo_counts = (
    photos.assign(
        premium_record=photos["premium_class"].eq("premium"),
        premium_id=photos["id"].where(
            photos["premium_class"].eq("premium")
        ),
        unresolved_record=photos["premium_class"].eq("unresolved"),
        unidentified_record=photos["id"].isna(),
    )
    .groupby("pull")
    .agg(
        emitted_records=("record", "size"),
        premium_records=("premium_record", "sum"),
        identifiable_premium_photos=("premium_id", "nunique"),
        unresolved_records=("unresolved_record", "sum"),
        unidentified_records=("unidentified_record", "sum"),
    )
    .reset_index()
)

manifests = pd.DataFrame([
    {
        "pull": pull["pull"],
        "mode": pull["input"]["mode"],
        "details_enabled": pull["input"].get("includePhotoDetails", False),
        "requested_max": pull["input"].get("maxItems", 20),
    }
    for pull in pulls
])

coverage = manifests.merge(photo_counts, on="pull", how="left")
count_columns = list(photo_counts.columns.drop("pull"))
coverage[count_columns] = coverage[count_columns].fillna(0).astype(int)
coverage["probe_constraint"] = (
    coverage["details_enabled"]
    & coverage["identifiable_premium_photos"].gt(S3_CHECK_CAP)
)

coverage is a boundary check, not a probe log. An unflagged pull doesn't prove every premium photo was checked, and unresolved identities can hide part of the population.

Don't interpret the flag as a completion percentage. Dividing the allowance by the returned premium count wouldn't identify which photos were checked or how those checks ended.

The collector doesn't expose a per-photo check outcome here. Missingness can't supply one by sheer determination.

Preserve missing renditions in the denominator

Counting only URLs that exist makes delivery coverage look unusually tidy. Every missing rendition disappears before the chart gets a chance to notice it.

Instead, create a slot for each named rendition on every emitted record. Preserve the photo ID and record position so variants don't become separate photos.

This cell also keeps an unexpected imageUrls shape distinct from an omitted object.

renditions = ("raw", "full", "regular", "small", "thumb", "smallS3")

def url_host(value):
    if not isinstance(value, str) or not value.strip():
        return None
    try:
        parsed = urlsplit(value.strip())
        return parsed.hostname if parsed.scheme in ("http", "https") else None
    except ValueError:
        return None

slots = []
for photo in photos.itertuples(index=False):
    variants = photo.imageUrls if isinstance(photo.imageUrls, dict) else {}
    map_state = (
        "object" if isinstance(photo.imageUrls, dict)
        else "omitted" if photo.imageUrls is None
        else "unexpected"
    )
    for rendition in renditions:
        value = variants.get(rendition)
        host = url_host(value)
        slots.append({
            "pull": photo.pull,
            "record": photo.record,
            "id": photo.id,
            "premium_class": photo.premium_class,
            "license": photo.license,
            "rendition": rendition,
            "map_state": map_state,
            "url_state": (
                "present" if host
                else "missing" if value is None or value == ""
                else "unparsed"
            ),
            "host": host,
        })

url_slots = pd.DataFrame(slots, columns=[
    "pull", "record", "id", "premium_class", "license",
    "rendition", "map_state", "url_state", "host",
])

A host identifies the hostname written in a URL. It doesn't establish the physical serving infrastructure or whether a request would succeed.

The separate shape and URL states also prevent a parsing problem from masquerading as a delivery change. If the export contains something other than the expected object, that needs investigation before it becomes a missing-image statistic.

For premium photos, an absent smallS3 slot describes missing optional URL metadata. It doesn't mean the application has no preview to display.

Compare like renditions

A thumbnail URL and a full-size URL aren't interchangeable observations. Combining their hosts gives you a mixture of requested renditions rather than a useful delivery comparison.

This cell computes host shares within each pull, premium classification, licence and rendition, retaining missing and unparsed slots.

coverage_dimensions = ["pull", "premium_class", "license", "rendition"]

host_coverage = (
    url_slots.assign(host=url_slots["host"].fillna("<no parsed host>"))
    .groupby(
        coverage_dimensions + ["map_state", "url_state", "host"],
        as_index=False,
        dropna=False,
    )
    .agg(emitted_records=("record", "size"))
)

host_coverage["record_share"] = (
    host_coverage["emitted_records"]
    / host_coverage.groupby(
        coverage_dimensions, dropna=False
    )["emitted_records"].transform("sum")
)

host_coverage counts emitted record slots. Repeated photo IDs remain repeated records; the distinct-photo count in coverage answers a different question.

That choice depends on what you're investigating. Record weighting describes the metadata your ingestion received; weighting each photo equally describes the asset population. Silently deduplicating before deciding which question matters can hide repeated ingestion just as easily as it can remove duplication.

Keep the unresolved classification visible during comparisons. Dropping it changes the population to photos you could classify, which may differ between pulls.

Give follow-up pulls the same targets

Discovery pulls are useful for finding assets. They're awkward controls for investigating whether the same assets receive different URL metadata.

The worked source is Unsplash Photo Search by Topic, Collection & Creator. Its smallS3 field is conditional, while imageUrls.full is a rendition name. Neither field is a probe-status flag.

Generate a fixed target list from identifiable premium photos, then reuse each batch unchanged across follow-up pulls. Keeping the entire batch within the probe allowance removes population size as a reason for exceeding that allowance. It doesn't guarantee a responding S3 copy.

This cell prepares inputs without launching additional runs.

premium_targets = sorted(
    photos.loc[
        photos["premium_class"].eq("premium") & photos["id"].notna(),
        "id",
    ].unique()
)

follow_up_inputs = [
    {
        "mode": "byPhoto",
        "photoIds": premium_targets[start:start + S3_CHECK_CAP],
        "includePhotoDetails": True,
        "orientation": "any",
        "freeOnly": False,
        "maxItems": len(premium_targets[start:start + S3_CHECK_CAP]),
    }
    for start in range(0, len(premium_targets), S3_CHECK_CAP)
]

Before comparing those follow-ups, join returned records by id and account for requested targets that didn't return. Matching inputs aren't evidence of matching outputs.

A comparison restricted to IDs returned in every pull answers whether URL metadata changed among those surviving targets. It doesn't answer whether target retrieval changed, so keep missing targets beside that comparison rather than losing them in an inner join.

This follow-up population also excludes unresolved premium classifications and missing IDs. Keep that exclusion visible rather than quietly promoting it into catalogue-wide coverage.

Put the boundary on the chart

A defensible chart label is “Returned URL hosts by rendition”, with the population described as emitted photo records under the saved inputs.

Keep the premium classification and missing-rendition share visible. Place the detail-pass setting and probe_constraint beside any comparison involving S3 metadata.

If you publish matched follow-ups, name that narrower population in the caption. “Identifiable premium targets returned in every compared pull” tells the reader what survived the join without pretending it represents all premium photography.

Avoid “S3 availability” unless you've check outcomes. Avoid “download success” unless you measured transfers. A denominator deserves a name that survives a sceptical code review.

A working preview doesn't establish full-size access

Premium full-size files require a separate Unsplash+ plan, and premium CDN images are watermarked. A reachable downloadUrl doesn't erase that distinction.

The Actor returns metadata and URLs, not image binaries. This audit therefore establishes neither transfer latency nor binary integrity, successful decoding or access to an original asset.

A preview can work perfectly while your explanation of why it worked is wrong.