DEV Community: Dataspryva

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.

Two salary ranges do not establish an exchange rate

Dataspryva — Sat, 31 Oct 2026 08:14:55 +0000

Your salary chart is nearly ready, except salaryUsdMin / salaryMin gives you a ratio you can't explain to your manager.

The annoying part is that the columns look made for this calculation. Local minimum, dollar minimum, divide and reconcile.

The native advertised salary range and Bayt's structured USD monthly bucket are separate published measures, not an established currency conversion.

The Bayt Actor parses them separately. It never estimates pay from description text, so a missing structured salary doesn't become a number just because the description sounds generous.

That changes the question you can defend. You can compare published intervals within a clearly defined measure. Their apparent disagreement doesn't diagnose a faulty exchange rate.

One posting can carry different pay evidence

For this analysis, a record represents a job posting. It isn't an employee, an accepted offer or a payroll observation.

Even a perfectly parsed advertised interval can't establish what anyone earned.

Keep the branches visible in your working model:

jobId
  ├─ native advertised range
  │    amounts + currency + pay period
  └─ structured USD monthly bucket
       amounts + currency + pay period

Parallel measures, joined by posting identity.

The Actor removes duplicate job IDs across queries and pages. That supports a posting-level analysis within a run, but it doesn't make repeated exports independent observations.

If you later stack runs, decide whether your unit is a distinct posting or a posting observed at a particular collection time. Otherwise, persistent advertisements acquire extra votes in your salary average.

Which salary fields belong together?

The field list doesn't specify scalar types for these outputs or an exhaustive vocabulary for currency and period labels. The pandas types below are ingestion choices.

Pay fields are conditional: unpublished values are omitted. Detail enrichment also affects availability, so field absence alone can't establish that an employer withheld pay.

Field	Meaning and format	pandas ingestion type	Analytical trap
`jobId`	Posting identifier	`string`	Treating an identifier as a quantity, or losing it before a join
`salary`	Native display text, potentially containing currency labels and grouped amounts	`string`, retaining the original	Treating a formatted interval as a numeric observation
`salaryMin`	Parsed native lower endpoint	Nullable `Float64`	Comparing it across currencies
`salaryMax`	Parsed native upper endpoint	Nullable `Float64`	Calling the upper endpoint an expected offer
`salaryCurrency`	Native currency label	Nullable `string`	Inferring currency from location when absent
`salaryPeriod`	Native pay-period label	Nullable `string`	Pooling amounts with different or unknown periods
`salaryUsdMin`	Structured USD monthly lower endpoint	Nullable `Float64`	Assuming it was converted from the native minimum
`salaryUsdMax`	Structured USD monthly upper endpoint	Nullable `Float64`	Treating a bucket boundary as a precise offer
`salaryUsdCurrency`	Currency label for the structured measure	Nullable `string`	Accepting missing or unexpected currency metadata
`salaryUsdPeriod`	Pay-period label attached to the USD bucket	Nullable `string`	Assuming its supplied spelling is already normalised
`minSalaryUSD`	Alternate USD-named lower-endpoint field	Nullable `Float64`	Assuming equality with `salaryUsdMin` without checking
`maxSalaryUSD`	Alternate USD-named upper-endpoint field	Nullable `Float64`	Counting another column as another salary observation

The alternate USD names don't establish fallback precedence. We'll retain them for comparison and use the salaryUsd* family explicitly for the structured measure.

Fetch a population before filtering its pay

A salary-filtered collection answers a different question from a collection in which you subsequently inspect salary coverage.

minSalary tests the published USD monthly upper boundary and excludes jobs without published salary. It doesn't establish that a posting's lower boundary meets your threshold.

Leave it unset when the question includes how much of the collected population has usable pay evidence.

Example input: this collects accountant postings with detail enrichment enabled and bounded collection limits.

import os

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_input = {
    "mode": "search",
    "searchQueries": ["accountant"],
    "country": "international",
    "includeDetails": True,
    "maxItems": 50,
    "maxPages": 1,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/bayt-com-mena-job-listing-and-salary-scraper"
).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)

These limits define a bounded collected population, not a census of vacancies. Results follow the platform's search order, and the same title search across markets needn't select comparable roles.

A clean currency column won't fix a different mix of seniority or employment types.

Type the amounts without repairing the evidence

Keep omitted keys distinguishable from supplied values that fail parsing. Both can become nulls in pandas, but they mean different things during an audit.

This loader preserves the original payload values, accepts ordinary numeric strings and correctly grouped comma thousands, and flags supplied values that don't become finite numbers.

numeric_fields = [
    "salaryMin", "salaryMax", "salaryUsdMin", "salaryUsdMax",
    "minSalaryUSD", "maxSalaryUSD",
]
text_fields = [
    "jobId", "salary", "salaryCurrency", "salaryPeriod",
    "salaryUsdCurrency", "salaryUsdPeriod",
]
fields = text_fields + numeric_fields

raw = pd.DataFrame(items).reindex(columns=fields)
present = pd.DataFrame(
    [{field: field in item for field in fields} for item in items],
    columns=fields,
    dtype=bool,
)
jobs = raw.copy()
numeric_audit = pd.DataFrame(index=raw.index)

for field in text_fields:
    jobs[field] = (
        raw[field].astype("string").str.strip().replace("", pd.NA)
    )

for field in numeric_fields:
    token = raw[field].astype("string").str.strip()
    grouped = token.str.fullmatch(
        r"[+-]?\d{1,3}(?:,\d{3})+(?:\.\d+)?", na=False
    )
    candidate = token.mask(
        grouped, token.str.replace(",", "", regex=False)
    )
    number = pd.to_numeric(candidate, errors="coerce").astype("Float64")
    jobs[field] = number.mask(
        number.isin([float("inf"), -float("inf")])
    )
    numeric_audit[field + "_invalid"] = (
        raw[field].notna() & jobs[field].isna()
    )

if jobs["jobId"].isna().any() or jobs["jobId"].duplicated().any():
    raise ValueError("Posting analysis requires present, unique jobId values")

A currency symbol inside an endpoint field remains an audit issue. We don't silently strip arbitrary characters until something numeric survives.

Likewise, the loader doesn't invent a decimal convention, convert periods or derive endpoints from salary. That display string remains available in raw for inspection.

present records whether a key existed. numeric_audit records a supplied, non-null value that failed numeric ingestion. An explicitly supplied null is neither an omitted key nor a malformed amount.

That separation makes a much better answer to “where did these salaries go?” than a single null count.

Missing pay changes who gets averaged

Bayt publishes pay on a minority of postings, and aggregated listings usually lack it. A mean over available salaries therefore describes a salary-publishing subset of your collected postings.

It can't automatically describe every vacancy.

There's another route to missing detail: a job found in a listing is retained with listing fields when its detail page can't be loaded. Even with enrichment requested, a sparse record doesn't identify a single cause.

Build separate intervals and count eligibility against the full collected posting population. Here, our analytical rule requires complete, non-negative, ordered endpoints and supplied currency and period labels.

measure_fields = {
    "native": (
        "salaryMin", "salaryMax", "salaryCurrency", "salaryPeriod"
    ),
    "structured_usd": (
        "salaryUsdMin", "salaryUsdMax",
        "salaryUsdCurrency", "salaryUsdPeriod"
    ),
}
interval_columns = [
    "lower_amount", "upper_amount", "currency", "period"
]

interval_frames = []
for measure, (
    lower_field, upper_field, currency_field, period_field
) in measure_fields.items():
    interval_frames.append(pd.DataFrame({
        "jobId": jobs["jobId"],
        "lower_amount": jobs[lower_field],
        "upper_amount": jobs[upper_field],
        "currency": jobs[currency_field],
        "period": jobs[period_field],
        "pay_measure": measure,
    }))

intervals = pd.concat(interval_frames, ignore_index=True)
intervals["eligible"] = (
    intervals[interval_columns].notna().all(axis=1)
    & intervals["lower_amount"].ge(0).fillna(False)
    & intervals["upper_amount"]
    .ge(intervals["lower_amount"]).fillna(False)
)

coverage = intervals.groupby("pay_measure", sort=False).agg(
    collected_postings=("jobId", "size"),
    usable_intervals=("eligible", "sum"),
)
coverage["usable_share"] = (
    coverage["usable_intervals"] / coverage["collected_postings"]
)

The code creates lower_amount and upper_amount as analytical columns shared by the interval tables; they aren't additional source fields.

This deliberately excludes partial intervals from endpoint comparisons. It doesn't turn an absent boundary into an open-ended range, because absence alone doesn't justify that interpretation.

Keep coverage beside the salary statistics. A native-pay summary and a structured-pay summary can have different eligible populations even when both originated from the same collected jobs.

Put the ranges beside each other, without a verdict

Your inspection table should expose the difference without naming it a conversion error.

This creates a posting-level comparison and flags alternate USD fields only where both corresponding numeric values are available.

comparison = jobs[
    ["jobId", *numeric_fields,
     "salaryCurrency", "salaryPeriod",
     "salaryUsdCurrency", "salaryUsdPeriod"]
].copy()
comparison["original_salary_text"] = raw["salary"]

alias_audit = jobs[["jobId"]].copy()
for primary, alternate in [
    ("salaryUsdMin", "minSalaryUSD"),
    ("salaryUsdMax", "maxSalaryUSD"),
]:
    comparable = jobs[[primary, alternate]].notna().all(axis=1)
    alias_audit[alternate + "_disagrees"] = (
        jobs[primary].ne(jobs[alternate])
        .where(comparable, pd.NA)
        .astype("boolean")
    )

An unknown alias comparison stays unknown. A missing alternate value isn't agreement, and agreement doesn't create another observation.

The worked example uses Bayt.com MENA Job Listing & Salary Scraper. The reusable pattern is to preserve measure identity before normalising values.

A spreadsheet can make unrelated ranges look reassuringly compatible. Arithmetic is very accommodating that way.

Give each summary its own denominator

Keep currency and period labels exactly as ingested until you've an explicit mapping for equivalent values. Guessing that differently written labels mean the same thing is another transformation you'd need to defend.

This DuckDB query calculates separate mean lower and upper endpoints, retaining measure and unit groups alongside their usable counts.

connection = duckdb.connect()
try:
    connection.register("pay_intervals", intervals)
    summary = connection.execute("""
        SELECT
            pay_measure,
            currency,
            period,
            count(*) AS postings_in_unit_group,
            count(*) FILTER (WHERE eligible) AS usable_intervals,
            avg(lower_amount) FILTER (WHERE eligible)
                AS mean_lower_endpoint,
            avg(upper_amount) FILTER (WHERE eligible)
                AS mean_upper_endpoint
        FROM pay_intervals
        GROUP BY pay_measure, currency, period
    """).df()
finally:
    connection.close()

Those endpoint means describe published interval boundaries among eligible postings. The resulting span isn't a confidence interval.

A midpoint would add an analytical convention. It wouldn't reveal where offers fall inside the advertised interval, and averaging native midpoints with structured bucket midpoints would erase the distinction you just preserved.

Report the measure, currency, period, usable count and collection denominator with each statistic. The summary table's unit-group counts describe a narrower grouping than the collection totals in coverage.

If you restrict analysis to postings containing both measures, name that matched subset explicitly: it answers a narrower question than either available-pay population.

The claim your manager can defend

This dataset can't establish realised compensation, explain why a particular employer omitted salary, or reveal a conversion method connecting the native range to the structured bucket.

Comparing countries also requires comparable roles and collection populations. A shared dollar label supplies a unit, not a matched labour market.

Matching column names don't make matching measurements.

Your match results can put a club in the wrong relegation spot

Dataspryva — Fri, 30 Oct 2026 07:15:28 +0000

Your generator puts a club above the relegation line. The points add up, the chart looks tidy, and you're about to publish.

The calculation wins * 3 + draws answers what a team earned under that scoring rule. It doesn't establish the team's published league position.

Our Actor separates match records in mode="results" from standings records in mode="teams". The latter supplies points and rank; neither mode supplies a sanctions ledger or a tiebreaker rulebook. Administrative adjustments can therefore leave perfectly sound arithmetic disagreeing with the table.

Your generator answered a narrower question

Points reconstructed from match scores are a different measure from points recorded in the league table.

That distinction matters when your claim concerns relegation rather than match performance. A points adjustment can change the first without changing the underlying scores. Equal points also leave an ordering question that addition can't answer.

The modes draw from different populations:

Input	Records selected	What the records support
`mode="results"`	Matches from the selected competition and season	Reconstructing a team's record from returned fixtures
`mode="teams"`	Teams in the selected league table	Reading the source's standings points and position

We start with explicit, matching competition and season inputs, fetching each dataset through its full iterator.

import os

import duckdb
import pandas as pd
from apify_client import ApifyClient

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

run_input = {
    "mode": "results",
    "sport": "epl",
    "season": "2023-2024",
    "maxItems": 10000,
    "includeUpcoming": False,
    "includeOdds": False,
    "regionalFallback": True,
}
teams_input = {
    **run_input,
    "mode": "teams",
    "enrichTeams": False,
}

def fetch_records(run_input):
    run = client.actor(
        "dataspryva/multi-league-season-results-and-team-lookup"
    ).call(run_input=run_input)
    if run["status"] != "SUCCEEDED":
        raise RuntimeError(f"Collection ended with {run['status']}")
    items = list(
        client.dataset(run["defaultDatasetId"]).iterate_items()
    )
    return pd.DataFrame(items)

raw_results = fetch_records(run_input)
raw_teams = fetch_records(teams_input)

The season here's a chosen comparison target, not a claim about a particular club or penalty.

The cap chooses the fixture population

Before investigating a deduction, check whether you collected enough matches to reconstruct anything.

maxItems defaults to 20. That's a record ceiling, not a season-completeness guarantee. The run also stops when the source runs out of pages.

A truncated fixture set produces team totals from whichever matches survived collection. That can undercount clubs unevenly, distort their relative positions and make ordinary missing coverage resemble a points adjustment.

There's no defensible random-sample interpretation without establishing how those fixtures were selected. A neat table doesn't supply one.

includeUpcoming defaults to true. Leaving it enabled admits scheduled and in-progress fixtures, whose final scores are unavailable. Those records belong in a fixture calendar, not in the denominator for a completed-match reconciliation.

The distinction is easier to see as a split:

Selected competition and season
    |
    +-- results --> returned finished matches --> calculated points
    |
    +-- teams ----> source standings -----------> points and rank
                                                   |
                          reconcile coverage first, then differences

Several leagueUrls share the total record budget. They also override sport and season, so a familiar-looking selector can stop describing the actual population.

For this comparison, separate collection by competition and season keeps the join and the coverage question tractable.

Build points only from usable matches

Missing fields are omitted from records. We make that absence visible before calculating anything.

The parser accepts integer text with grouping separators, whitespace and a Unicode minus sign. Unrecognised text stays missing rather than turning into a convenient zero.

def integer_field(values):
    text = (
        values.astype("string")
        .str.strip()
        .str.replace(",", "", regex=False)
        .str.replace("\u00a0", "", regex=False)
        .str.replace("\u202f", "", regex=False)
        .str.replace("\u2212", "-", regex=False)
    )
    valid_text = text.str.fullmatch(r"[+-]?\d+", na=False)
    return pd.to_numeric(
        text.where(valid_text), errors="coerce"
    ).astype("Int64")

match_fields = [
    "eventId", "homeTeamId", "awayTeamId",
    "homeScore", "awayScore",
]
matches = raw_results.reindex(columns=match_fields).copy()

for field in ["eventId", "homeTeamId", "awayTeamId"]:
    matches[field] = (
        matches[field].astype("string").str.strip().replace("", pd.NA)
    )

for field in ["homeScore", "awayScore"]:
    matches[field] = integer_field(matches[field])

usable = (
    matches.notna().all(axis=1)
    & matches["homeScore"].ge(0)
    & matches["awayScore"].ge(0)
    & matches["homeTeamId"].ne(matches["awayTeamId"])
    & ~matches["eventId"].duplicated(keep=False)
).fillna(False)

clean_matches = matches.loc[usable].copy()

Duplicate event identifiers are excluded from the calculation and counted as a coverage problem. Silently keeping an arbitrary duplicate would conceal whether its score agreed with the discarded record.

We then give each fixture a home-team perspective and an away-team perspective.

perspectives = []

for side, opponent in [("home", "away"), ("away", "home")]:
    perspectives.append(pd.DataFrame({
        "teamId": clean_matches[f"{side}TeamId"],
        "won": (
            clean_matches[f"{side}Score"]
            > clean_matches[f"{opponent}Score"]
        ).astype("Int64"),
        "drawn": (
            clean_matches[f"{side}Score"]
            == clean_matches[f"{opponent}Score"]
        ).astype("Int64"),
        "lost": (
            clean_matches[f"{side}Score"]
            < clean_matches[f"{opponent}Score"]
        ).astype("Int64"),
    }))

team_matches = pd.concat(perspectives, ignore_index=True)
calculated = team_matches.groupby("teamId", as_index=False).agg(
    played_calc=("teamId", "size"),
    wins_calc=("won", "sum"),
    draws_calc=("drawn", "sum"),
    losses_calc=("lost", "sum"),
)
calculated["points_calc"] = (
    calculated["wins_calc"] * 3 + calculated["draws_calc"]
)

This scoring calculation is deliberately specific to the chosen football competition. It isn't a reusable standings formula for every sport the Actor supports.

Join teams before comparing points

Joining on teamName makes spelling part of your integrity check. The records provide team identifiers for this purpose, so we use those instead.

An outer join matters just as much: an inner join could erase the club with missing fixtures and leave the surviving comparison looking reassuring.

The next cell keeps coverage failures separate from points differences. It explicitly names the join indicator, a column created by the comparison rather than returned by the Actor.

table_numbers = [
    "rank", "matchesPlayed", "wins", "draws", "losses", "points",
]
standings = raw_teams.reindex(
    columns=["teamId", "teamName", *table_numbers]
).copy()

standings["teamId"] = (
    standings["teamId"].astype("string").str.strip().replace("", pd.NA)
)
for field in table_numbers:
    standings[field] = integer_field(standings[field])

team_key_ok = (
    standings["teamId"].notna()
    & ~standings["teamId"].duplicated(keep=False)
)
joinable_standings = standings.loc[team_key_ok]

join_indicator = "join_status"
audit = joinable_standings.merge(
    calculated,
    on="teamId",
    how="outer",
    validate="one_to_one",
    indicator=join_indicator,
)
audit["points_delta"] = audit["points"] - audit["points_calc"]

record_pairs = [
    ("matchesPlayed", "played_calc"),
    ("wins", "wins_calc"),
    ("draws", "draws_calc"),
    ("losses", "losses_calc"),
]
needed = table_numbers + [
    "played_calc", "wins_calc", "draws_calc",
    "losses_calc", "points_calc",
]
records_agree = all(
    audit[left].eq(audit[right]).fillna(False).all()
    for left, right in record_pairs
)

coverage_checks_pass = bool(
    not raw_results.empty
    and not raw_teams.empty
    and len(raw_results) < run_input["maxItems"]
    and len(raw_teams) < teams_input["maxItems"]
    and usable.all()
    and team_key_ok.all()
    and audit[join_indicator].eq("both").all()
    and audit[needed].notna().all().all()
    and records_agree
)
audit["coverage_checks_pass"] = coverage_checks_pass

run_review = pd.DataFrame([{
    "result_rows": len(raw_results),
    "rejected_match_rows": int((~usable).sum()),
    "rejected_team_keys": int((~team_key_ok).sum()),
    "coverage_checks_pass": coverage_checks_pass,
}])

Matching played, won, drawn and lost totals gives the points comparison a much firmer basis. A missing fixture usually changes the playing record as well as the arithmetic.

These checks still can't prove that the source itself is complete. They establish internal agreement between the returned match records and returned standings, which is a narrower and reportable claim.

A discrepancy is a lead, not a verdict

We can now isolate differences without assigning them a cause.

connection = duckdb.connect()
connection.register("standings_audit", audit)

variance = connection.execute("""
    SELECT
        teamId,
        teamName,
        rank,
        matchesPlayed,
        played_calc,
        points AS standings_points,
        points_calc,
        points_delta,
        CASE
            WHEN NOT coverage_checks_pass THEN 'coverage_review'
            ELSE 'unexplained_points_difference'
        END AS review_status
    FROM standings_audit
    WHERE NOT coverage_checks_pass
       OR points IS DISTINCT FROM points_calc
    ORDER BY rank NULLS LAST, teamId
""").df()

connection.close()

Here, points_delta is standings points minus calculated points. A negative difference is consistent with a deduction, but the sign doesn't identify a sanction.

An amended result, a source inconsistency or mismatched collection states could also require investigation. The code deliberately refuses to turn subtraction into an allegation.

Inspect run_review alongside variance. An empty variance table isn't clearance when the entire comparison population was rejected.

Choose inputs around the publication claim

For a worked example, Multi-League Season Results & Team Lookup makes the separation explicit: fixtures and standings are different record types.

Our collection design follows the claim being checked. A published-position comparison uses a fixed season, finished fixtures, matching league inputs and enough headroom to avoid the item ceiling. Reaching that ceiling keeps the extraction incomplete until coverage is established independently.

regionalFallback retries an empty league-season through the regional edition. It doesn't repair a partially populated season. That distinction matters: a recovery mechanism for an empty response isn't evidence that a non-empty response is exhaustive.

For a live table, separate runs also create a timing problem. Use scrapedAt to retain when each record was collected, but don't treat nearby timestamps as proof that both views captured the same underlying state.

A defensible methods note names the competition and season, states that fixture coverage was reconciled against the standings record, and distinguishes calculated points from source standings points. Any unresolved difference remains visible.

That wording gives your editor something more useful than “the script passed”.

The missing explanation stays missing

The returned rank preserves the source's ordering. It doesn't reveal which tiebreaker produced that ordering, and matching points doesn't validate position.

Likewise, a points discrepancy can't establish the authority, reason or effective scope of an administrative adjustment. Those require the competition's published table, rules and decisions.

We'd keep the calculated total as an audit measure and label the standings figure by its provenance. For a relegation claim, the governing competition remains the authority.

A points difference is a question for reporting, not a ready-made accusation.

Your deletion tracker may be counting collection failures

Dataspryva — Thu, 29 Oct 2026 08:23:02 +0000

Your change log says a broadcast disappeared, and that count is heading into a report. Now you've to defend whether the broadcast was deleted or your collection failed.

The suspicious line is usually innocent-looking: WHERE type = 'broadcast'. It keeps the entities you want to analyse and removes the evidence you need to explain their absence.

Our broadcast Actor emits status rows for invalid links, unsupported share links, Spaces, deleted broadcasts and rate limits. A missing broadcast row does not establish that the broadcast was deleted.

The missing row has another explanation

A broadcast-only comparison collapses different outcomes into the same empty space.

Keep the branches visible:

submitted target
    |
    +-- broadcast row --> compare the resolved entity
    |
    +-- status row ----> retain the diagnostic
    |
    +-- incomplete collection --> retain the failed attempt

This is an observation problem before it's a deletion problem. A tidy change log can still be confidently wrong.

Use a single submitted target per collection attempt here. That gives every diagnostic a reliable association with the attempted target, without assuming the output echoes its input or arrives in input order.

Example collection input and fetch, with optional enrichments disabled so the tracker concentrates on broadcast metadata:

import os
import json
from datetime import datetime, timezone
from uuid import uuid4

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_input = {
    "broadcastIds": ["https://x.com/i/broadcasts/1YGNrbPLejQGw"],
    "includeViewerStats": False,
    "includeViewerTimeline": False,
    "maxViewerTimelinePoints": 0,
    "includeReplayVideo": False,
    "includeChatHistory": False,
    "maxChatMessages": 1000,
    "includeThumbnail": False,
    "includeBroadcasterProfile": False,
}
submitted_target = run_input["broadcastIds"][0]
options_json = json.dumps(
    {k: v for k, v in run_input.items() if k != "broadcastIds"},
    sort_keys=True,
)
run_key = str(uuid4())  # Our observation identifier.
started_at = datetime.now(timezone.utc)
items = []
run_status = "CLIENT_ERROR"  # Our label, not a platform run status.
dataset_complete = False
error = None

try:
    client = ApifyClient(os.environ["APIFY_TOKEN"])
    run = client.actor(
        "dataspryva/x-broadcast-viewer-history-and-replay-link-scraper"
    ).call(run_input=run_input)
    run_status = run["status"]
    for item in client.dataset(run["defaultDatasetId"]).iterate_items():
        items.append(item)
    dataset_complete = True
except Exception as exc:
    error = str(exc)

finished_at = datetime.now(timezone.utc)

Reading the whole dataset matters. A partially retrieved dataset is another way to manufacture an absence, even when the remote run finished successfully.

The wrapper retains partial results, but it won't promote them into successful snapshots.

Keep identity separate from the route in

A submitted URL identifies your collection target. The broadcast row's id identifies the resolved broadcast.

Those are different jobs.

Legacy /v/<id> and /<user>/<id> links resolve through their trailing broadcast ID. An announcement post can also lead to a broadcast. Changing the route into an entity mustn't create a new entity in your change log.

Keep these distinctions in the stored data:

Fields	Treatment in this tracker
Broadcast `id`	Entity key, paired with the collection's `run_key` for snapshot uniqueness
`state`, `title`	Candidate substantive changes when present in comparable successful observations
Broadcast `status`	Published machine state, kept separate from diagnostic rows
`sourceTweetId`, `sourceTweetUrl`, `announcementTweetId`, `announcementTweetUrl`	Discovery and announcement context, preserved outside the entity key
`collectedAt`	Observation timestamp, excluded from substantive field comparisons
Status-row `status`, `message`	Diagnostic evidence, retained verbatim

Don't substitute broadcastId whenever id is missing. Timeline and chat rows use broadcastId; this tracker deliberately selects broadcast records.

The input route stays in a target ledger. The resolved identity belongs beside the mutable fields.

Store the attempt before interpreting the result

Use a local database that persists between notebook sessions. Store the complete returned payload alongside the collection outcome, then materialise successful broadcast snapshots separately.

The review labels below belong to our tracker. They aren't assumed Actor status values.

Create the storage and the diagnostic view:

con = duckdb.connect("broadcast-tracker.duckdb")
con.execute("""
CREATE TABLE IF NOT EXISTS target_ledger (
    submitted_target VARCHAR PRIMARY KEY,
    expected_id VARCHAR
);
CREATE TABLE IF NOT EXISTS collection_runs (
    run_key VARCHAR PRIMARY KEY,
    submitted_target VARCHAR,
    started_at TIMESTAMPTZ,
    finished_at TIMESTAMPTZ,
    run_status VARCHAR,
    dataset_complete BOOLEAN,
    error VARCHAR,
    options_json VARCHAR,
    payload_json JSON
);
CREATE TABLE IF NOT EXISTS broadcast_snapshots (
    run_key VARCHAR,
    id VARCHAR,
    collected_at TIMESTAMPTZ,
    state VARCHAR,
    state_present BOOLEAN,
    raw_json JSON,
    PRIMARY KEY (id, run_key)
);
CREATE TABLE IF NOT EXISTS status_reviews (
    run_key VARCHAR,
    row_key VARCHAR,
    category VARCHAR CHECK (
        category IN (
            'failed_observation',
            'source_reported_unavailability'
        )
    ),
    PRIMARY KEY (run_key, row_key)
);
CREATE VIEW IF NOT EXISTS status_observations AS
SELECT
    c.run_key,
    j.key AS row_key,
    json_extract_string(j.value, '$.status') AS status,
    json_extract_string(j.value, '$.message') AS message,
    j.value AS raw_json
FROM collection_runs AS c, json_each(c.payload_json) AS j
WHERE json_extract_string(j.value, '$.type') = 'status';
""")

Keeping the payload also preserves omitted keys, provenance and any returned record types your current analysis doesn't use. Future interpretation shouldn't require rewriting historical evidence.

Write the collection and its eligible snapshots in a transaction:

run_frame = pd.DataFrame([{
    "run_key": run_key,
    "submitted_target": submitted_target,
    "started_at": started_at,
    "finished_at": finished_at,
    "run_status": run_status,
    "dataset_complete": dataset_complete,
    "error": error,
    "options_json": options_json,
    "payload_json": json.dumps(items),
}])
con.register("incoming_run", run_frame)

con.execute("BEGIN TRANSACTION")
try:
    con.execute("""
        INSERT INTO target_ledger VALUES (?, NULL)
        ON CONFLICT DO NOTHING
    """, [submitted_target])
    con.execute("""
        INSERT INTO collection_runs
        SELECT
            run_key, submitted_target, started_at, finished_at,
            run_status, dataset_complete, error, options_json,
            CAST(payload_json AS JSON)
        FROM incoming_run
    """)
    con.execute("""
        INSERT INTO broadcast_snapshots
        SELECT
            c.run_key,
            json_extract_string(j.value, '$.id'),
            TRY_CAST(
                json_extract_string(j.value, '$.collectedAt')
                AS TIMESTAMPTZ
            ),
            json_extract_string(j.value, '$.state'),
            json_exists(j.value, '$.state'),
            j.value
        FROM collection_runs AS c, json_each(c.payload_json) AS j
        WHERE c.run_key = ?
          AND c.run_status = 'SUCCEEDED'
          AND c.dataset_complete
          AND json_extract_string(j.value, '$.type') = 'broadcast'
          AND NULLIF(json_extract_string(j.value, '$.id'), '') IS NOT NULL
    """, [run_key])
    con.execute("COMMIT")
except Exception:
    con.execute("ROLLBACK")
    raise

Empty fields are omitted from the Actor's records. The state_present flag therefore matters: an absent state is missing evidence, not a transition to an empty state.

TRY_CAST parses the returned timestamp string without inventing a replacement when it can't be parsed. Collection start and finish times remain available independently.

Bind identity after successful resolution

A failed first attempt can't establish a broadcast identity.

Bind the submitted target only when its completed collection resolves to an unambiguous broadcast ID. Preserve that binding on subsequent attempts so a changed resolution becomes something to investigate.

This statement establishes the initial association:

con.execute("""
UPDATE target_ledger AS t
SET expected_id = (
    SELECT min(id)
    FROM broadcast_snapshots
    WHERE run_key = ?
)
WHERE t.submitted_target = ?
  AND t.expected_id IS NULL
  AND (
      SELECT count(DISTINCT id)
      FROM broadcast_snapshots
      WHERE run_key = ?
  ) = 1
""", [run_key, submitted_target, run_key])

Different submitted links can now bind to the same canonical identity. Their discovery context stays separate, while their successful observations can share an entity history.

For a larger batch, keep diagnostics unmatched unless the returned data supplies a reliable association. A message mentioning a problem isn't permission to attach it to whichever broadcast happens to be missing.

Compare states only across usable observations

A useful diff asks whether the entity changed and whether the observations support that comparison.

The query below compares the current attempt with the last successful observation that finished before this attempt began. It also checks collection options, field presence and unexpected identity changes.

Run the comparison for the current collection:

diff_sql = """
WITH diagnostics AS (
    SELECT
        s.run_key,
        count(*) AS diagnostic_count,
        bool_or(
            r.category = 'source_reported_unavailability'
        ) AS reported_unavailable
    FROM status_observations AS s
    LEFT JOIN status_reviews AS r
        USING (run_key, row_key)
    GROUP BY s.run_key
)
SELECT
    c.run_key,
    c.submitted_target,
    t.expected_id,
    p.collected_at AS last_success_collected_at,
    p.finished_at AS last_success_finished_at,
    p.state AS previous_state,
    n.state AS current_state,
    CASE
        WHEN c.run_status <> 'SUCCEEDED' OR NOT c.dataset_complete
            THEN 'collection_failed'
        WHEN t.expected_id IS NULL
            THEN 'identity_unresolved'
        WHEN EXISTS (
            SELECT 1 FROM broadcast_snapshots AS other
            WHERE other.run_key = c.run_key
              AND other.id <> t.expected_id
        ) THEN 'resolved_identity_changed'
        WHEN n.id IS NULL AND coalesce(d.reported_unavailable, false)
            THEN 'source_reported_unavailability'
        WHEN n.id IS NULL AND coalesce(d.diagnostic_count, 0) > 0
            THEN 'failed_observation'
        WHEN n.id IS NULL
            THEN 'no_broadcast_observed'
        WHEN p.id IS NULL
            THEN 'first_success'
        WHEN c.options_json IS DISTINCT FROM p.options_json
            THEN 'configuration_changed'
        WHEN NOT n.state_present OR NOT p.state_present
            THEN 'state_not_comparable'
        WHEN n.state IS DISTINCT FROM p.state
            THEN 'observed_state_change'
        ELSE 'state_unchanged'
    END AS change_category
FROM collection_runs AS c
JOIN target_ledger AS t USING (submitted_target)
LEFT JOIN broadcast_snapshots AS n
    ON n.run_key = c.run_key AND n.id = t.expected_id
LEFT JOIN diagnostics AS d ON d.run_key = c.run_key
LEFT JOIN LATERAL (
    SELECT s.*, earlier.finished_at, earlier.options_json
    FROM broadcast_snapshots AS s
    JOIN collection_runs AS earlier USING (run_key)
    WHERE s.id = t.expected_id
      AND earlier.finished_at < c.started_at
    ORDER BY earlier.finished_at DESC, s.run_key DESC
    LIMIT 1
) AS p ON true
WHERE c.run_key = ?
"""
changes = con.execute(diff_sql, [run_key]).df()

There's deliberately no deleted category.

source_reported_unavailability requires an explicit review label attached to a particular diagnostic. Until then, the query treats a diagnostic without a successful entity observation as a failed observation.

The stored payload supports broader diffs later. It doesn't make every field a useful change signal. A new collection timestamp is bookkeeping; a different submitted post is provenance; an omitted optional field isn't evidence that the broadcast lost that attribute.

Choose the substantive fields before counting changes. Comparing every JSON byte is an excellent way to build a busy dashboard.

Put the explanation beside the last success

Your manager needs the evidence behind the label, not just another categorical column.

Build a review frame containing the previous successful observation, the current diagnostic and any adjudication:

con.register("current_changes", changes)
review = con.execute("""
SELECT
    c.*,
    s.row_key,
    s.status AS diagnostic_status,
    s.message AS diagnostic_message,
    s.raw_json AS diagnostic_record,
    r.category AS reviewed_category,
    a.run_status,
    a.dataset_complete,
    a.started_at,
    a.finished_at,
    a.error
FROM current_changes AS c
JOIN collection_runs AS a USING (run_key)
LEFT JOIN status_observations AS s USING (run_key)
LEFT JOIN status_reviews AS r
    ON r.run_key = s.run_key AND r.row_key = s.row_key
""").df()
con.close()

Populate status_reviews only after inspecting the preserved diagnostic. Use source_reported_unavailability when the message explicitly reports that condition, and retain its wording so the attribution remains inspectable.

Don't infer deletion from an unfamiliar status spelling, a substring match or the absence of a replay link.

Old pscp.tv/w share links, for example, return legacyShareLinkUnsupported. That establishes an unsupported route, not the fate of a broadcast you hoped the route would identify.

The observation still has boundaries

The worked example uses X Broadcast Viewer History & Replay Link Scraper. The reusable distinction is between a change in an entity and a change in your ability to observe it.

Even an explicit unavailable response can't establish who removed the broadcast or why. Preserve it as a source report, with the collection context attached.

The last successful observation and a later failure also don't supply an exact disappearance time. Your collection schedule bounds what you can say; an unresolved attempt may not narrow that interval at all.

A defensible report can say when you last observed the broadcast and what the next attempt returned.

Your missing row can't testify about who deleted it.

Your location join gives some job ads extra salary votes

Dataspryva — Wed, 28 Oct 2026 08:20:42 +0000

Your national salary average moved when you added regional rows. Now the market report needs an explanation for why a location transformation changed a pay statistic.

The suspicious line is jobs.explode("locations"). The job location structure is a locations[] array of objects, so expanding it repeats any parent salary across its location associations.

Exploding job locations changes the analytical unit from an advert to a job-location association. Calculate a national mean on those expanded rows and ads with more associations receive more weight.

Keep the search population attached

For Dataspryva's Willhaben Actor, mode selects the collection route. It's a string input; startUrls is an array used with byUrls, which accepts ordinary listing URLs and job URLs, including searches.

That matters because the collection route isn't the record type. A mixed URL collection needs separate job and listing logic.

This Example input collects a bounded job search with detail enrichment and explicitly disables incremental output.

import os
import math
import re
from numbers import Real

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_input = {
    "mode": "searchJobs",
    "query": "",
    "maxItems": 100,
    "includeDetails": True,
    "incrementalMode": False,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/willhaben-marketplace-cars-and-real-estate"
).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

Keep run_input with your analysis. A capped collection is a collected search population, not an estimate of the entire labour market.

Incremental output answers a different question again: later runs can contain changes rather than the standing population of ads. A salary average over changes measures the ads that changed.

Which fields belong in the parent table?

Job records have recordType: "job"; ordinary listings have "listing". Select the record family before choosing keys or interpreting geography.

For this analysis, we choose jobId as the candidate parent key. The loader will reject missing or repeated values rather than silently substitute another field.

These selected fields determine the identity, geography and salary denominators. Retain them when present: this isn't a promise that every record contains every field.

Field	What you retain	Normalisation and analytical trap
`recordType`	The `"job"` or `"listing"` discriminator	Select explicitly before applying identity rules; a shared dataset doesn't imply a shared schema.
`jobId`, `jobUrl`	The candidate parent key and a public absolute job link for traceability	Use nullable strings. Missing or repeated candidate keys block this analysis until resolved.
`jobTitle`, `description`	Text for inspecting adverts; full descriptions depend on detail enrichment and availability	Use nullable strings. Restricting text analysis to full descriptions changes its eligible population.
`companyId`	A value to retain for potential employer grouping	Use a nullable string. An omitted value means employer grouping is unresolved.
`salary`	The original salary value	Preserve it before deriving a nullable number under an explicit parsing policy.
`salaryTimeFrame`	The original pay-period label	Use a nullable string. Keep labels separate rather than guessing annual equivalents.
`locations[]`	An array of location objects	Build a child table. Its length becomes an implicit weight after expansion.
`name`	The name inside a location object	Use a nullable string. A label isn't automatically a unique geographic identifier.
`federalState`, `country`	Geographic labels inside each location object	Use nullable strings. Group them together; unresolved geography needs its own coverage count.

Source attributes that aren't carried are omitted rather than emitted as blanks or null. Detail enrichment can fill full descriptions and other detail fields where published; it doesn't promise universal completeness.

This cell separates record families, preserves raw salary values and checks the chosen parent key within the collected snapshot.

record_type_counts = pd.Series(
    [item.get("recordType") for item in items], dtype="string"
).value_counts(dropna=False)

job_records = [item for item in items if item.get("recordType") == "job"]
listing_records = [
    item for item in items if item.get("recordType") == "listing"
]

job_columns = [
    "recordType", "jobId", "jobUrl", "jobTitle", "description",
    "companyId", "salary", "salaryTimeFrame",
]
jobs = pd.DataFrame.from_records(job_records, columns=job_columns)

for column in job_columns:
    if column != "salary":
        jobs[column] = jobs[column].astype("string")

jobs["salaryTimeFrame"] = (
    jobs["salaryTimeFrame"].str.strip().replace("", pd.NA)
)
jobs["locations"] = pd.Series(
    [item.get("locations", []) for item in job_records], dtype="object"
)

bad_ids = jobs["jobId"].isna() | jobs["jobId"].str.strip().eq("")
if bad_ids.any() or jobs["jobId"].duplicated().any():
    raise ValueError("Resolve missing or duplicate jobId values before analysis")

A repeated identifier needs investigation before deduplication. Overlapping collections and conflicting versions require different decisions; keeping whichever row happened to arrive first would conceal that distinction.

Parse salary without inventing its meaning

A parser can recognise a number without establishing what the number means.

Our policy below accepts finite, non-negative numeric values, plain decimal strings, and explicitly euro-labelled strings using a comma decimal separator and optional grouped thousands. It rejects ranges, qualifiers and other formats.

That's a deliberately narrow acceptance policy. It isn't a claim that the Actor always emits these formats.

This cell keeps rejected values available in salary while deriving salaryValue.

euro_number = r"(?:[0-9]{1,3}(?:\.[0-9]{3})+|[0-9]+)(?:,[0-9]{1,2})?"

def salary_number(value):
    if isinstance(value, Real) and not isinstance(value, bool):
        return float(value) if math.isfinite(value) and value >= 0 else None
    if not isinstance(value, str):
        return None

    token = value.strip()
    if re.fullmatch(r"[0-9]+(?:\.[0-9]{1,2})?", token):
        parsed = float(token)
    else:
        match = re.fullmatch(
            rf"(?:€\s*(?P<prefix>{euro_number})|"
            rf"(?P<suffix>{euro_number})\s*EUR)",
            token,
        )
        if match is None:
            return None
        amount = match.group("prefix") or match.group("suffix")
        parsed = float(amount.replace(".", "").replace(",", "."))

    return parsed if math.isfinite(parsed) else None

jobs["salaryValue"] = jobs["salary"].map(salary_number).astype("Float64")

Blindly removing punctuation can turn a range or a qualified offer into an apparently precise salary. The decimal point rarely files a complaint.

Even successful parsing doesn't establish currency for unlabelled values, gross versus net pay, a floor versus a fixed amount, or comparable working hours. The resulting means are arithmetic diagnostics until those meanings have been checked.

Missing locations stay on the parent table

An omitted array shouldn't remove the advert from the national parent population. It should prevent that advert from acquiring an invented regional association.

We treat omission as an empty child collection. Unexpected array or object shapes raise an error rather than quietly manufacturing geography.

This cell creates the child table and exposes how many salary votes expansion would assign to each advert.

geo_fields = ["name", "federalState", "country"]
location_rows = []

for job_id, places in zip(jobs["jobId"], jobs["locations"]):
    if not isinstance(places, list):
        raise TypeError("locations must be an array when present")
    for place in places:
        if not isinstance(place, dict):
            raise TypeError("Each location must be an object")
        location_rows.append({
            "jobId": job_id,
            **{field: place.get(field) for field in geo_fields},
        })

locations = pd.DataFrame(
    location_rows, columns=["jobId", *geo_fields]
)
for column in locations.columns:
    locations[column] = (
        locations[column].astype("string").str.strip().replace("", pd.NA)
        if column != "jobId"
        else locations[column].astype("string")
    )

jobs["associationCount"] = (
    jobs["jobId"].map(locations.groupby("jobId").size())
    .fillna(0).astype("Int64")
)

The table relationship makes the competing denominators visible.

jobs: advert identity and salary
  |
  +--> locations: job-location associations
         |
         +--> regional distinct-ad counts

national salary summaries stay with jobs

An association can still lack a usable state or country. Array presence and regional assignability are separate coverage measures.

Regional ad counts aren't additive

For regional tables, count distinct adverts associated with each geography. Counting location rows instead would also count repeated associations within the same region.

This DuckDB query retains the association count beside the distinct-ad count so you can inspect that difference.

regional = duckdb.sql("""
    SELECT
        country,
        federalState,
        count(*) AS location_associations,
        count(DISTINCT jobId) AS distinct_job_ads
    FROM locations
    WHERE country IS NOT NULL
      AND federalState IS NOT NULL
    GROUP BY country, federalState
""").df()

An advert associated with several states contributes to each relevant regional count. Summing those counts doesn't recover a national distinct-ad total.

The appropriate label is “distinct job ads associated with the region”. It doesn't establish where an eventual employee will work, or how many vacancies the advert represents.

Separate extra weight from lost coverage

Expansion can change a salary mean through separate mechanisms: repeating salaries for multi-location ads and excluding ads without location associations.

Compare the full parent table, parents with associations, and expanded associations. The middle population isolates the coverage change before you examine the weighting change.

The Actor behind this worked example is Willhaben Marketplace, Cars & Real Estate Listings Scraper.

This cell builds the comparison within observed time-frame labels and records the exclusions separately.

expanded = locations.merge(
    jobs[["jobId", "salaryValue", "salaryTimeFrame"]],
    on="jobId",
    how="left",
    validate="many_to_one",
)

def salary_summary(frame, prefix):
    return (
        frame.dropna(subset=["salaryTimeFrame"])
        .groupby("salaryTimeFrame")
        .agg(
            rows=("jobId", "size"),
            parsed_salary_rows=("salaryValue", "count"),
            mean_salary=("salaryValue", "mean"),
        )
        .add_prefix(prefix)
    )

located_jobs = jobs.loc[jobs["associationCount"].gt(0)]
salary_comparison = (
    salary_summary(jobs, "ads_")
    .join(salary_summary(located_jobs, "located_ads_"), how="outer")
    .join(salary_summary(expanded, "associations_"), how="outer")
)

region_job_ids = locations.loc[
    locations[["federalState", "country"]].notna().all(axis=1), "jobId"
]
coverage = pd.Series({
    "job_ads": len(jobs),
    "ads_without_location_associations": jobs["associationCount"].eq(0).sum(),
    "ads_without_assignable_region": (~jobs["jobId"].isin(region_job_ids)).sum(),
    "ads_without_salary": jobs["salary"].isna().sum(),
    "ads_with_unparsed_salary": (
        jobs["salary"].notna() & jobs["salaryValue"].isna()
    ).sum(),
    "ads_without_salary_time_frame": jobs["salaryTimeFrame"].isna().sum(),
})

Compare the located-parent mean with the expanded-association mean to examine the effect of location multiplicity on the same located parent population.

Compare the full-parent mean with the located-parent mean to examine the exclusion of parents without associations.

If the expanded mean happens to match the parent mean, that doesn't validate the counting unit. Different weights can produce the same average.

Missing salary changes the population again

A mean excludes missing numeric values. Here, that leaves the subset whose salary passed your parser, within an observed salaryTimeFrame group.

That's narrower than “ads publishing salary”: rejected formats remain published values. Keep ads_without_salary and ads_with_unparsed_salary separate, because source availability and parser coverage are different problems.

The exclusions can overlap. An advert can lack both a usable salary and a time-frame label, so adding those coverage counts would create another misleading total.

A defensible salary table carries the collected ad count, the parsed salary count and the interpretation of the pay basis. A regional table additionally carries the number of ads that couldn't be assigned geographically.

If you group by companyId, report how many ads have a usable value. Counting distinct values alone doesn't establish the number of hiring organisations without checking how those values relate to employers and intermediaries.

An advert is still not a vacancy

These records describe public advertisements and their attached attributes. They don't establish completed hires, realised pay, unique vacancies or the full population of available work.

Location associations describe the geography attached to an advert. They don't divide its recruitment demand among those places.

Your national salary average needs a population and a pay definition. Your regional counts need a membership rule.

A longer location array isn't a larger employer payroll.

Deduplication does not refund your search result fees

Dataspryva — Tue, 27 Oct 2026 09:02:00 +0000

Your clean URL count can make the acquisition fee look wrong just before you publish the report. You've applied drop_duplicates("url"), and now the cost per retained page looks uncomfortable.

Each query is scraped independently up to maxResults, while result fees follow emitted dataset items. Overlapping queries can therefore retrieve the same destination without adding another URL to your analytical corpus.

Deduplicating destination URLs reduces the corpus, but it does not reverse result events already emitted.

The awkward part is explaining that distinction after you've thrown away the retrieval history. A tidy table is surprisingly good at hiding how much work produced it.

The acquisition needs its own ledger

We need separate records of what you requested, what came back and what you retained. The final URL table can't stand in for all of them.

For this example, we'll use the Actor's web mode with related queries. The input requests overlapping subject matter; it doesn't presume that repeated URLs will occur.

Example acquisition cell: this keeps the input manifest and fetches the dataset through the streaming interface.

import os
from decimal import Decimal

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_label = "web-month"
run_input = {
    "mode": "webSearch",
    "queries": ["人工智能", "人工智能 最新进展"],
    "maxResults": 50,
    "timeRange": "month",
    "language": "simplified",
    "includeSerpFeatures": True,
}
manifests = {run_label: run_input.copy()}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/baidu-web-image-and-news-results-extractor"
).call(run_input=run_input)
items = list(
    client.dataset(run["defaultDatasetId"]).iterate_items()
)

The stream matters because reconciliation needs the whole exported dataset. A partial export would introduce another gap before deduplication even starts.

Keep the manifest with the acquisition. A query string alone doesn't identify its retrieval frame: the same words under different filters are different requests.

The retrieval frame ends before the web does

This pull measures a slice of public search results. It doesn't enumerate every page about the topic.

These are the boundaries that affect the denominator:

Boundary	Included or excluded	Consequence for your corpus
Geography and script	There is no geographic input filter. `language` selects Chinese script and does not translate the query.	Script selection cannot establish where a publisher or subject is located.
Result surface	Web, news, images and autocomplete are separate modes.	An organic web corpus excludes articles, images or suggestions found only through another surface.
Publication window	`timeRange` filters web results; `dateFrom` and `dateTo` override it for web and news.	A publication window is not an archive of everything searchable during that period.
Retrieval ceiling	`maxResults` is a per-query maximum. Source exhaustion or a security check can stop retrieval earlier.	A short return does not establish that the topic has little coverage.
Conditional fields and rows	Dates and other unavailable fields are omitted. Enabled search features arrive as separate rows without reducing the organic result count.	Dataset rows, dated pages and organic URLs need separate counts.

Different date windows, site restrictions, file formats, script selections and the mobile view can expose different pages. The Actor falls back to the mobile view when needed; there's no mobile-selection input in the schema.

That makes partitioning useful for discovery. It also makes overlap worth measuring.

A union can grow while its acquisition efficiency falls. Neither observation, by itself, tells you whether another query was worthwhile.

Keep the retrieval trail beside the corpus

We'll retain exact URL strings as the identity rule. No tracking-parameter removal, redirect resolution or inferred canonicalisation is hidden inside this check.

This cell creates a corpus and a bridge back to the queries that retrieved each URL, while keeping every exported row available for accounting.

rows = (
    pd.DataFrame.from_records(items)
    .reindex(columns=[
        "query", "resultType", "url", "scrapedAt", "publishedAt"
    ])
    .assign(
        run_label=run_label,
        item_number=lambda frame: range(len(frame)),
        query=lambda frame: frame["query"].astype("string"),
        resultType=lambda frame: frame["resultType"].astype("string"),
        url=lambda frame: frame["url"].astype("string"),
        scrapedAt=lambda frame: pd.to_datetime(
            frame["scrapedAt"], errors="coerce", utc=True
        ),
        publishedAt=lambda frame: pd.to_datetime(
            frame["publishedAt"], errors="coerce", utc=True
        ),
    )
)

organic_mask = (
    rows["resultType"].eq("organic").fillna(False)
    & rows["url"].str.strip().ne("").fillna(False)
)
organic = rows.loc[organic_mask].copy()
corpus = organic[["url"]].drop_duplicates().reset_index(drop=True)
bridge = organic[["run_label", "query", "url"]].drop_duplicates()

row_types = (
    rows.groupby(["run_label", "resultType"], dropna=False)
    .size()
    .rename("exported_rows")
    .reset_index()
)

Missing fields become missing values in your analytical table. Missing or unfamiliar row types remain visible in row_types; they don't quietly become organic pages.

The bridge removes duplicate query-to-URL relationships. The untouched rows table preserves repeated emissions within those relationships.

Those tables answer different questions. Keeping both prevents a provenance join from quietly becoming a billing ledger.

Measure overlap without erasing empty queries

Start the coverage check from the requested queries, rather than the returned rows. Otherwise a query returning nothing disappears from the audit.

This cell measures organic retrievals, exact URL diversity and available publication timestamps for each requested query.

planned = pd.DataFrame([
    {
        "run_label": label,
        "query": query,
        "requested_cap": manifest["maxResults"],
    }
    for label, manifest in manifests.items()
    for query in manifest["queries"]
])

coverage = duckdb.sql("""
    SELECT
        p.run_label,
        p.query,
        p.requested_cap,
        COUNT(o.url) AS organic_rows,
        COUNT(DISTINCT o.url) AS unique_urls,
        COUNT(o.url) - COUNT(DISTINCT o.url)
            AS repeated_url_rows,
        COUNT(o.url) = p.requested_cap AS reached_requested_cap,
        COUNT(o.publishedAt) * 1.0 / NULLIF(COUNT(o.url), 0)
            AS published_at_share,
        MIN(o.scrapedAt) AS first_scraped_at,
        MAX(o.scrapedAt) AS last_scraped_at
    FROM planned AS p
    LEFT JOIN organic AS o
        ON p.run_label = o.run_label AND p.query = o.query
    GROUP BY p.run_label, p.query, p.requested_cap
""").df()

url_reach = (
    bridge.groupby("url")
    .size()
    .rename("retrieval_frames")
    .reset_index()
)

coverage measures repetition within each query. url_reach measures how many query frames reached each retained URL.

Don't sum the per-query unique counts to obtain corpus size. Their overlap is precisely what you're trying to preserve.

Reaching the requested cap flags possible truncation, not completeness. Falling below it also leaves an unresolved question: the source may have exhausted results, or retrieval may have been interrupted. The status message identifies affected queries when that happens.

The timestamp share measures field availability. Treating rows without publishedAt as outside the publication window would impose a new exclusion after retrieval.

Reconcile charged events before pricing the corpus

Use charged-event counts from the acquisition's billing record. Don't reconstruct them from unique URLs, and don't silently substitute exported row counts.

Start-event counts also need their own entry: charging depends on memory, with one event per GB and a minimum of one event. Batching queries may change overhead, but “one run” isn't a sufficient billing calculation.

These labelled arithmetic examples isolate event fees:

FREE tier, one start event and 100 result events: $0.10 + 100 x $0.00099 = $0.199, plus platform usage.
GOLD tier, the same event counts: $0.03 + 100 x $0.00049 = $0.079, plus platform usage.

For the collected pull, the following cell leaves billing values unknown until you supply them. Use numeric counts and, if available, the actual platform usage charge.

billing = {
    "tier": "FREE",
    "result_events": None,
    "start_events": None,
    "platform_usage_usd": None,
}
event_prices = {
    "FREE": (Decimal("0.00099"), Decimal("0.10")),
    "GOLD": (Decimal("0.00049"), Decimal("0.03")),
}
result_rate, start_rate = event_prices[billing["tier"]]

event_fees_usd = (
    None
    if billing["result_events"] is None
    or billing["start_events"] is None
    else (
        Decimal(str(billing["result_events"])) * result_rate
        + Decimal(str(billing["start_events"])) * start_rate
    )
)

A difference between charged result events and exported rows is a reconciliation question. Deduplication can't explain that difference because it happens downstream of both.

Feature rows need their own category too. They belong in the export accounting even when your research corpus retains only organic destination URLs.

Put both denominators in the report

The worked example uses Baidu Web, Image & News Results Extractor. The accounting pattern applies whenever retrieval frames overlap and acquisition charges precede analytical deduplication.

This cell puts the acquisition and corpus denominators alongside the supplied billing counts, leaving unavailable values and undefined ratios empty.

def ratio_or_missing(numerator, denominator):
    if numerator is None or denominator is None or denominator == 0:
        return None
    return Decimal(str(numerator)) / Decimal(str(denominator))

report = pd.Series({
    "run_status": run["status"],
    "exported_dataset_rows": len(rows),
    "organic_retrieval_rows": len(organic),
    "retained_exact_urls": len(corpus),
    "charged_result_events": billing["result_events"],
    "charged_start_events": billing["start_events"],
    "charged_results_minus_exported_rows": (
        None
        if billing["result_events"] is None
        else billing["result_events"] - len(rows)
    ),
    "exported_rows_per_retained_url": ratio_or_missing(
        len(rows), len(corpus)
    ),
    "event_fees_usd": event_fees_usd,
    "event_fees_per_exported_row_usd": ratio_or_missing(
        event_fees_usd, len(rows)
    ),
    "event_fees_per_retained_url_usd": ratio_or_missing(
        event_fees_usd, len(corpus)
    ),
    "platform_usage_usd": billing["platform_usage_usd"],
    "total_acquisition_usd": (
        None
        if event_fees_usd is None
        or billing["platform_usage_usd"] is None
        else event_fees_usd
        + Decimal(str(billing["platform_usage_usd"]))
    ),
})

For a chart caption, state the surface, queries, filters, acquisition window and identity rule. Name the denominator explicitly: “event fees per retained exact organic URL” carries a different meaning from “event fees per exported dataset row”.

Keep platform usage beside those figures. Without it, neither ratio represents total acquisition cost.

For a corpus assembled across acquisitions, retain distinct run labels and manifests, reconcile each acquisition at its applicable tier, then divide the combined fees by the combined exact-URL corpus. Averaging per-run ratios would give small and large acquisitions equal weight.

A URL is still a narrow unit of evidence

Exact deduplication can't establish document identity across tracking parameters, redirects or syndicated copies. It also can't tell you which relevant pages the search engine never exposed.

Nor does retrieval frequency establish authority or prevalence. A page reached through several overlapping queries has several retrieval relationships, not several independent pieces of evidence.

Your manager can reasonably ask what each retained URL cost. The clean corpus can supply the denominator.

It can't remember the acquisition bill for you.

A falling lane rank does not prove a champion nerf

Dataspryva — Mon, 26 Oct 2026 07:21:40 +0000

Before you publish that bot-lane report, look at the champion whose rank fell while the displayed win rate stayed unchanged. The chart says “decline”. Your manager will reasonably ask what declined.

tierRank is a champion's rank within its lane. roleRate is the share of that champion's games played in the lane. A changing position in the table doesn't, by itself, establish weaker performance.

A rank drop accompanied by falling role share is a lane-migration candidate, not proof of a nerf. The rows provide neither an eligibility threshold nor the ranking formula, so attributing the movement to a threshold crossing would outrun the evidence.

Did performance fall, or did the comparison move?

A relative rank can worsen because other champions moved ahead. Meanwhile, a champion's games can shift between lanes without producing a visible change in its lane-specific win percentage.

That makes the debugging question more useful than the headline: did performance change, did lane participation change, or did the comparison change underneath you?

Start by collecting every lane in the same scope. An ADC-only capture can't show whether the same champion's role share rose elsewhere.

Example collection settings keep the tier cutoff open and put maxItems above the source's maximum of 271 tier rows per filter:

import os
import json
import re
from uuid import uuid4
from datetime import datetime, timezone

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_input = {
    "mode": "tierList",
    "queueType": "ranked",
    "region": "global",
    "rankTier": "emerald_plus",
    "position": "ALL",
    "patch": "latest",
    "minTier": "all",
    "maxItems": 1000,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/op-gg-league-of-legends-tier-list-and-win-rate"
).call(run_input=run_input)

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

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

snapshot_id = uuid4().hex
captured_at = datetime.now(timezone.utc)

latest is a moving selector. Save the returned patch with each row, rather than treating the input value as the patch identity.

The join needs a champion and a lane

Use championId as the champion identifier and position as part of the observation key. Joining on the champion alone can attach a support observation to an ADC observation and manufacture a performance change.

Names belong in labels. They don't need responsibility for your joins.

The comparison key also needs the returned region, rank, queue and patch. Those fields define the population behind the percentage.

The join separates comparable observations from changes in table membership:

champion + lane + scope + patch
                 |
                 +-- present in both captures -> compare metrics
                 +-- present in either capture -> record membership change

For this tracker, the useful distinctions are:

Fields	Treatment	Analytical consequence
`championId`, `position`	Champion-lane identity	Separate a champion's lane observations
`region`, `rankTier`, `queueType`, `patch`	Comparison boundaries	Prevent population changes becoming performance deltas
`tierRank`, `winRate`, `roleRate`, `pickRate`	Compare after parsing	Separate rank movement from percentage-point changes
`rankChange`, `rankPrev`, `rankPrevPatch`	Retain as source context	Their reference periods may differ from your captures
`scrapedAt`, `imageUrl`, `championUrl`	Retain outside performance checks	Collection metadata and presentation changes are separate events

Keep roleRate and pickRate distinct. The former describes how a champion's games split across lanes; they aren't interchangeable measures.

Missing rates must stay missing

The Actor omits unavailable fields. Filling an omitted rate with zero would convert “unknown” into an apparent collapse, followed by a miraculous recovery when the field returns.

Parse percentages into percentage points, preserve missing values, and reject malformed strings. An unchanged displayed percentage only establishes stability at its displayed precision.

This cell builds the normalised batch while leaving unavailable metrics missing:

key_fields = [
    "championId", "position", "region", "rankTier", "queueType", "patch"
]
metric_fields = ["tierRank", "winRate", "roleRate", "pickRate"]

def parse_metric(value, field):
    if value is None:
        return None

    text = str(value).strip()
    if field != "tierRank":
        text = text.removesuffix("%").strip()

    if not re.fullmatch(r"[+-]?(?:\d+(?:\.\d+)?|\.\d+)", text):
        return None

    number = float(text)
    if field == "tierRank":
        return number if number >= 1 and number.is_integer() else None
    return number if 0 <= number <= 100 else None

rows = []
rejected = 0

for item in items:
    if any(
        item.get(field) is None or not str(item.get(field)).strip()
        for field in key_fields
    ):
        rejected += 1
        continue

    rows.append({
        "run_id": snapshot_id,
        **{field: str(item[field]).strip() for field in key_fields},
        **{
            field: parse_metric(item.get(field), field)
            for field in metric_fields
        },
    })

batch = pd.DataFrame(
    rows, columns=["run_id", *key_fields, *metric_fields]
)
batch = batch.astype({field: "float64" for field in metric_fields})

duplicates = batch.duplicated(key_fields, keep=False)
eligible = bool(
    not batch.empty
    and rejected == 0
    and not duplicates.any()
    and len(items) < run_input["maxItems"]
)
batch = batch.loc[~duplicates].copy()

Missing identity or scope fields hold the run out of comparisons. Duplicate identities do too: choosing whichever duplicate arrived last would give arrival order a vote in the analysis.

Malformed metrics remain missing, so later predicates can't mistake them for stable rates.

Keep the evidence with each capture

Store a run manifest alongside the champion observations. Without it, a missing champion is ambiguous: did the row disappear, or did collection never produce a usable snapshot?

The local metadata below records collection time, requested settings, dataset identity and eligibility for comparison. It also preserves the original records, including rows that could not enter the normalised table.

This creates the history database on first use and appends each capture in a transaction:

con = duckdb.connect("tier_history.duckdb")

con.execute("""
    CREATE TABLE IF NOT EXISTS runs (
        run_id VARCHAR PRIMARY KEY,
        captured_at TIMESTAMPTZ,
        input_json VARCHAR,
        dataset_id VARCHAR,
        eligible BOOLEAN,
        raw_items_json VARCHAR
    );
    CREATE TABLE IF NOT EXISTS snapshots (
        run_id VARCHAR,
        championId VARCHAR,
        position VARCHAR,
        region VARCHAR,
        rankTier VARCHAR,
        queueType VARCHAR,
        patch VARCHAR,
        tierRank DOUBLE,
        winRate DOUBLE,
        roleRate DOUBLE,
        pickRate DOUBLE,
        PRIMARY KEY (
            run_id, championId, position,
            region, rankTier, queueType, patch
        )
    );
""")

input_json = json.dumps(run_input, sort_keys=True)
con.register("incoming", batch)
con.execute("BEGIN TRANSACTION")
try:
    con.execute(
        "INSERT INTO runs VALUES (?, ?, ?, ?, ?, ?)",
        [
            snapshot_id, captured_at, input_json,
            str(run["defaultDatasetId"]), eligible, json.dumps(items),
        ],
    )
    con.execute("INSERT INTO snapshots SELECT * FROM incoming")
    con.execute("COMMIT")
except Exception:
    con.execute("ROLLBACK")
    raise

con.unregister("incoming")

Empty captures and captures reaching the requested cap stay out of the comparison. These are conservative checks, not proof that the upstream table was complete.

Run the notebook against the same database on your collection schedule. Its timestamp marks collection, not the time of the matches behind the statistics.

Compare captures, not reference periods

rankChange reports daily movement; rankPrev and rankPrevPatch provide the previous-day and previous-patch ranks. Your snapshots might instead be separated by a missed collection, an intraday rerun or a longer gap.

Compute your own delta between the latest eligible captures with identical requested settings. Preserve the source movement fields in the raw records for investigation.

This query uses a full outer join so arrivals and disappearances survive the comparison:

diffs = con.execute("""
    WITH latest AS (
        SELECT run_id, captured_at
        FROM runs
        WHERE eligible AND input_json = ?
        ORDER BY captured_at DESC, run_id DESC
        LIMIT 2
    ),
    pair AS (
        SELECT run_id,
               row_number() OVER (
                   ORDER BY captured_at DESC, run_id DESC
               ) AS sequence
        FROM latest
    ),
    old_snapshot AS (
        SELECT s.*
        FROM snapshots s JOIN pair p USING (run_id)
        WHERE p.sequence = 2
    ),
    new_snapshot AS (
        SELECT s.*
        FROM snapshots s JOIN pair p USING (run_id)
        WHERE p.sequence = 1
    )
    SELECT
        championId, position, region, rankTier, queueType, patch,
        CASE
            WHEN o.run_id IS NULL THEN 'appeared'
            WHEN n.run_id IS NULL THEN 'disappeared'
            ELSE 'matched'
        END AS membership,
        n.tierRank - o.tierRank AS rank_drop,
        n.winRate - o.winRate AS delta_win_pp,
        n.roleRate - o.roleRate AS delta_role_pp,
        n.pickRate - o.pickRate AS delta_pick_pp
    FROM old_snapshot o
    FULL OUTER JOIN new_snapshot n
        USING (
            championId, position, region,
            rankTier, queueType, patch
        )
    WHERE (SELECT count(*) FROM pair) = 2
""", [input_json]).df()

con.register("snapshot_diff", diffs)

Here, a positive rank_drop means a worse rank. That sign is deliberately different from the source's rankChange, where positive means places gained.

A patch rollover produces unmatched rows rather than a cross-patch performance delta. Likewise, an absent champion remains a membership event. It doesn't acquire an invented bottom rank.

Put lane migration on the review list

The useful flag combines symptoms. Rank movement alone is too easy to overinterpret.

Example review thresholds select a pronounced rank drop, a small displayed win-rate change and falling lane share; these are analyst choices, not source eligibility rules:

rank_loss_threshold = 5
win_tolerance_pp = 0.1

flags = con.execute("""
    SELECT *,
           CASE
               WHEN rank_drop > ?
                AND abs(delta_win_pp) <= ?
                AND delta_role_pp < 0
               THEN 'migration_candidate'
               ELSE 'unresolved'
           END AS review_label
    FROM snapshot_diff
    WHERE membership = 'matched'
""", [rank_loss_threshold, win_tolerance_pp]).df()

For each candidate, inspect the same champion's other lanes within the same region, rank, queue and patch. Falling share in the reported lane alongside rising share elsewhere makes the migration explanation more credible.

It still doesn't establish that role share caused the rank movement.

You can also compare rank movement's association with role-share movement and win-rate movement. Keep lanes separate, and use the same complete observations for both correlations:

associations = con.execute("""
    SELECT region, rankTier, queueType, patch, position,
           count(*) AS paired_rows,
           corr(rank_drop, delta_role_pp) AS rank_role_correlation,
           corr(rank_drop, delta_win_pp) AS rank_win_correlation
    FROM snapshot_diff
    WHERE membership = 'matched'
      AND rank_drop IS NOT NULL
      AND delta_role_pp IS NOT NULL
      AND delta_win_pp IS NOT NULL
    GROUP BY region, rankTier, queueType, patch, position
""").df()

con.unregister("snapshot_diff")
con.close()

Treat those correlations as investigation aids. A small paired set, constant values or related movements across the table can make them uninformative. A correlation coefficient doesn't reverse-engineer a ranking algorithm.

Where the balance claim runs out

Dataspryva's OP.GG League of Legends Tier List & Win Rate Tracker supplies the tier observations used here. It doesn't supply match-level evidence or a causal account of balance changes.

Tier-list rows also lack a denominator for the reported win rate. gamesPlayed belongs to champion statistics, whose scope needs separate scrutiny: plus ranks resolve to their base ranks, and the statistics period is another comparison boundary. Joining those counts onto tier rows wouldn't automatically give you the right sample size.

Without matching denominators and sampling windows, you can't distinguish a small underlying performance change from sampling variation. A flat displayed rate doesn't settle that question.

Your manager asked what changed in the game. First establish what changed in the table.

Your lowest-rated product list may contain highly rated items

Dataspryva — Sun, 25 Oct 2026 08:19:14 +0000

Your “lowest-rated competitor products” list can include products with excellent customer ratings.

Picture opening that slide in front of product leadership. You selected sortBy="RATING_ASC", so the heading felt defensible. Now you're explaining why the stars disagree with it.

RATING_ASC follows Bol.com's rating score, not the visible average customer rating. With a collection cap, that difference affects which products you receive, before your analysis starts.

What does ascending rating actually select?

The tempting question is straightforward: which competing laptops attract poor customer ratings, and what complaints recur?

The input answers a narrower question: which products can the Actor retrieve through a laptop search under the platform's score ordering, subject to the collection limits?

Those populations needn't match.

This Example input makes the selection choices explicit, retrieves the dataset through the streaming interface, and checks the run status.

import os
import re

import duckdb
import pandas as pd
from apify_client import ApifyClient

run_input = {
    "mode": "search",
    "searchQueries": ["laptop"],
    "marketplace": "nl",
    "language": "nl",
    "sortBy": "RATING_ASC",
    "condition": "any",
    "inStockOnly": False,
    "includeSponsored": True,
    "includeDetails": True,
    "maxItems": 100,
    "maxPages": 0,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/bol-com-product-search-price-and-stock-extractor"
).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)

The score formula isn't exposed here. We can't attribute the ordering to review-volume weighting, a confidence interval or any particular adjustment.

That uncertainty matters. “An internal score” doesn't establish that popular products systematically rise or fall. It establishes that sorting the exported rating column and selecting products upstream are different operations.

The cap turns an ordering mismatch into missing products

If you already had the complete target population, you could sort its visible ratings locally.

A capped collection makes that repair incomplete: local sorting can reorder retrieved products, but it can't recover products excluded during collection.

The selection path looks like this:

Keyword and shop settings
          |
Platform ordering and reachable listings
          |
Collection limits
          |
Retrieved products
          |
Local rating filter

Every filter at the bottom operates on the survivors above it. A tidy SQL query can't negotiate with a product it never received.

Search reach also matters independently of the rating sort. A keyword result list stops at 10 pages; for larger collections, the Actor automatically splits broad searches into narrower category slices.

Those slices extend reach. They don't establish complete category coverage or known inclusion probabilities.

The overall maxItems cap applies across the run, while maxPages limits each query or listing. In the input above, the latter delegates pagination to the source's limit. Neither setting certifies that the resulting file contains every eligible laptop.

Keep rating eligibility separate from customer sentiment

Before ranking anything, separate an unavailable rating from a poor rating.

Empty fields are omitted. A missing reviewCount is therefore unknown, and a missing rating mustn't become the bottom of your scale through a convenient fillna.

These fields carry different analytical weight:

Field or setting	Role in this analysis	Boundary
`rating`	Visible-rating screening	Doesn't reproduce the upstream score
`reviewCount`	Review-volume eligibility	Doesn't identify causes of dissatisfaction
`includeDetails`	Requests richer product evidence	Enrichment doesn't broaden the selected population
`ratingDistribution`	Shows star counts when available	Doesn't explain the complaints
`recentReviews`	Provides text for qualitative inspection	Isn't the full review history

This cell handles omitted keys, accepts decimal commas in ratings, and parses space-grouped review counts. Other punctuated count strings remain unknown rather than receiving a guessed interpretation.

def parse_measure(value, *, count=False):
    if value is None or isinstance(value, bool):
        return None

    if isinstance(value, str):
        text = re.sub(r"\s+", "", value)
        pattern = r"\d+" if count else r"\d+(?:[.,]\d+)?"
        if not re.fullmatch(pattern, text):
            return None
        value = text.replace(",", ".")

    try:
        number = float(value)
    except (TypeError, ValueError, OverflowError):
        return None

    if count:
        return number if number >= 0 and number.is_integer() else None
    return number if 0 <= number <= 5 else None

frame = pd.DataFrame.from_records(
    items, columns=["productId", "rating", "reviewCount"]
)
frame["productId"] = (
    frame["productId"].astype("string").str.strip().replace("", pd.NA)
)
frame["rating"] = frame["rating"].map(parse_measure).astype("Float64")
frame["reviewCount"] = frame["reviewCount"].map(
    lambda value: parse_measure(value, count=True)
).astype("Float64")

Out-of-range ratings become unusable values. Keep the original items available for inspection: a parser rejection and an omitted field both prevent a rating-based decision, but they have different causes.

Audit the denominator before naming the shortlist

A useful check asks how much of the retrieved data can support your proposed screen.

It doesn't try to reverse-engineer the platform score from export order. The Actor doesn't provide a source-rank field, so dataset row position isn't evidence of storefront position.

The following cell audits rating availability, flags repeated product identifiers, and builds a shortlist using explicit rating and review-volume thresholds.

connection = duckdb.connect()
connection.register("retrieved", frame)

rating_ceiling = 2.5
review_floor = 10

audit = connection.execute("""
    SELECT
        COUNT(*) AS retrieved_rows,
        COUNT(*) FILTER (
            WHERE productId IS NULL
        ) AS rows_without_product_id,
        COUNT(productId) - COUNT(DISTINCT productId)
            AS repeated_identified_rows,
        COUNT(*) FILTER (
            WHERE rating IS NULL
        ) AS rows_without_usable_rating,
        COUNT(*) FILTER (
            WHERE reviewCount IS NULL
        ) AS rows_without_usable_review_count,
        COUNT(*) FILTER (
            WHERE rating IS NOT NULL
              AND reviewCount >= ?
        ) AS review_eligible_rows,
        COUNT(*) FILTER (
            WHERE rating <= ?
              AND reviewCount >= ?
        ) AS low_rated_eligible_rows
    FROM retrieved
""", [review_floor, rating_ceiling, review_floor]).df()

shortlist = connection.execute("""
    SELECT productId, rating, reviewCount
    FROM retrieved
    WHERE productId IS NOT NULL
      AND rating <= ?
      AND reviewCount >= ?
    ORDER BY rating ASC, reviewCount DESC
""", [rating_ceiling, review_floor]).df()

connection.close()

audit gives you the denominator checks; shortlist gives you candidates to investigate. Neither asserts that these are the category's worst-rated products.

The thresholds are triage choices, not a statistical significance test. Review volume helps distinguish sparse evidence from more extensively reviewed products, but it doesn't make reviewers representative of buyers.

Don't publish a product-level percentage while repeated identifiers remain unresolved. When combining collections, check repeated products for conflicting values before deduplicating; silently retaining whichever row arrived first can conceal a changing snapshot.

Build the category frame before screening ratings

For this question, we'd start from a category definition rather than asking a rating-related ordering to discover the population.

This Example input uses the laptop category identifier and a larger collection budget. It's an alternative collection configuration, not evidence that the category fits inside the budget.

category_run_input = {
    "mode": "categoryBrowse",
    "categoryUrls": ["4770"],
    "marketplace": "nl",
    "language": "nl",
    "sortBy": "RELEVANCE",
    "condition": "any",
    "inStockOnly": False,
    "includeSponsored": True,
    "includeDetails": True,
    "maxItems": 10000,
    "maxPages": 0,
}

The worked example uses Bol.com Product Search, Price & Stock Extractor.

Changing the ordering alone doesn't remove selection bias. The improvement comes from defining the intended category, collecting broadly enough to assess reach, and applying the visible-rating screen afterwards.

If collection reaches its item cap, treat coverage as unresolved. Reaching fewer items also doesn't prove completeness: the accessible listing may have ended before the commercial population did.

Keep stock, condition and advertising choices aligned with your question. Excluding unavailable products could remove relevant complaint evidence; retaining every condition could mix product experiences you intended to analyse separately.

Those are hypotheses to check against your category, not reasons to prefer a particular setting automatically.

Also resist adding minRating to a search for poorly rated products. It's a lower bound, so it removes products below the supplied value.

Give the percentage its actual population

For an exploratory report, the defensible metric is the share of eligible retrieved products meeting your low-rating rule.

Its denominator excludes products without usable rating evidence and those below your chosen review-volume floor. Those exclusions belong beside the metric, because changing them changes the population being described.

Record the category or queries, shop settings, collection limits, collection timestamps, eligibility thresholds and unresolved coverage gaps.

A useful label is “Low-rated share among review-eligible products retrieved from the selected category.” “Competitor defect rate” asks the dataset to impersonate a different study.

If your manager wants a category-wide estimate, the missing ingredient is a defensible population frame or sampling design. A larger export can improve coverage without supplying either.

Complaints still aren't manufacturing defects

With details enabled, recentReviews contains up to six recent reviews. That's a short, recency-selected text sample, even when the rating summary reflects a longer review history.

Use it to propose complaint categories for inspection. Don't turn the frequency of a phrase in that sample into its prevalence among customers.

A low rating can concern delivery, a seller, expectations or the product itself. Establishing a manufacturing defect requires evidence that separates those explanations.

The uncomfortable question belongs before the slide: are you measuring dissatisfaction in a defined population, or merely describing the products your inputs allowed you to see?

Your salary coverage can fall before employers change

Dataspryva — Sat, 24 Oct 2026 09:44:11 +0000

Your salary-coverage chart drops after a routine refresh. Before you explain why employers became less transparent, check what happened around df.dropna(subset=["salaryMin"]).

Handshake omits empty fields, including salary fields when pay is missing. When an entire batch lacks a key, pandas can produce a frame without that column. A later column intersection, conditional load or row filter can quietly change the population behind your chart.

An omitted salary key is still a job in your denominator.

Capture the population before measuring pay

The Actor reads public campus job pages and employer profiles. For this analysis, a job record represents a retrieved public posting, identified by jobId, rather than an employer, an application or a confirmed vacancy filled.

Start with a job search that doesn't select for published pay.

import os
import json
import re

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_input = {
    "mode": "searchJobs",
    "includeDescriptions": True,
    "onlyNewJobs": False,
    "maxItems": 50,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/handshake-campus-jobs-and-employer-showcase"
).call(run_input=run_input)

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

items = client.dataset(run["defaultDatasetId"]).list_items().items
raw = pd.DataFrame.from_records(items)

Keep run_input and the dataset identifier with your reporting artefact. Discovery mode, filters and the return cap define which postings could enter the calculation.

minSalary retains jobs whose published maximum meets the threshold. salaryPeriod restricts jobs to a published pay period. Those inputs select on the information you're trying to measure.

Similarly, onlyNewJobs changes the population to previously unseen IDs. That supports monitoring new postings, but it doesn't reproduce a snapshot of previously collected jobs.

Your denominator needs a name before it needs a percentage: “retrieved public postings under these inputs” is defensible. “The campus jobs market” is considerably more ambitious.

How does an absent key change a table?

Pandas infers columns from keys present anywhere in the supplied records. A key missing from some rows becomes a missing value; a key missing from every row doesn't create a column.

Example: these batches differ in salary availability, so their inferred column lists differ too.

example_paid_batch = pd.DataFrame.from_records([
    {
        "jobId": "Example paid job",
        "salary": "$95,000 - $120,000 a year",
    }
])
example_unpaid_batch = pd.DataFrame.from_records([
    {"jobId": "Example job without published pay"}
])

example_column_sets = {
    "with_pay": example_paid_batch.columns.tolist(),
    "without_pay": example_unpaid_batch.columns.tolist(),
}

Inference alone hasn't corrupted anything. Combining frames with an outer column union can preserve the information.

The failure comes later: keeping only common columns, skipping batches that lack salaryMin, or counting only rows that survive numeric parsing.

Returned job
    |
    +-- salary key absent --> retain job, record absence
    |
    +-- salary key present --> retain job, validate value
                                  |
                                  +-- parse failure --> retain job, flag failure

A fixed schema preserves the column. A presence flag preserves why its value is missing. You need both if someone will ask what changed.

Read the pay fields as different evidence

These are the fields that matter most to this report. Empty attributes are omitted; the conditions below explain additional reasons for absence. The storage types are analytical choices, rather than guarantees about every incoming value.

Field	Contents and availability	Nullable storage	Analytical trap
`salary`	Pay as published, potentially formatted text; absent when pay is missing	pandas `string`; SQL `VARCHAR`	Text can establish that pay information was extracted without yielding a usable numeric range
`salaryMin`, `salaryMax`	Structured bounds in the listing's currency	pandas `Float64`; SQL `DOUBLE`	Requiring both bounds excludes postings with only partial pay information
`salaryCurrency`	Currency associated with the advertised amounts	pandas `string`; SQL `VARCHAR`	A symbol in free text isn't a safe substitute for this field
`paySchedule`	Whether the amount is per year, month or hour	pandas `string`; SQL `VARCHAR`	A pooled wage average can mix incompatible units
`salaryType`	An additional salary attribute without a supplied codebook	JSON text	A numeric value isn't permission to invent category labels
`ownershipType`	Employer-profile ownership: Public, Private, Nonprofit or Government; optional	pandas `string`; SQL `VARCHAR`	Missing ownership isn't a private employer, and job records aren't the ownership population
`roleGroups`	An array available through rich search cards; other discovered rows may lack it	JSON text, preserving the array	Restricting analysis to populated role groups selects on discovery coverage
`descriptionHtml`	Full job-description HTML when descriptions are enabled	pandas `string`; SQL `VARCHAR`	Missing content can reflect collection settings; populated HTML doesn't guarantee structured salary fields

Role groups appear on the 15 rich cards per search page. Their absence therefore has a collection mechanism beyond an employer choosing not to supply a category.

Don't use roleGroups.notna() as a convenient eligibility filter for the main salary chart. That creates a different population before you've compared any wages.

For an ownership comparison, collect employer profiles with includeProfiles enabled. Match them through employerId, validate the intended join cardinality and retain unmatched jobs.

Also distinguish the question being asked: a posting-weighted comparison gives employers with more retrieved postings more weight. An employer-weighted comparison requires an employer denominator.

Parse salary text without manufacturing a wage

Keep salary unchanged. Use a text parser as an additional check, rather than a fallback that silently overwrites structured values.

This deliberately narrow parser recognises a dollar-denominated range followed by an explicit period; unmatched strings remain unparsed.

amount_pattern = r"(?:\d{1,3}(?:,\d{3})+|\d+)(?:\.\d+)?"
range_pattern = re.compile(
    rf"\A\$(?P<low>{amount_pattern})\s*[-–]\s*"
    rf"\$(?P<high>{amount_pattern}) a "
    rf"(?P<period>year|month|hour)\Z"
)

salary_text = raw.reindex(columns=["salary"])["salary"].astype("string")
text_ranges = salary_text.str.extract(range_pattern)

for bound in ("low", "high"):
    text_ranges[bound] = pd.to_numeric(
        text_ranges[bound].str.replace(",", "", regex=False),
        errors="coerce",
    ).astype("Float64")

A failed match means “outside this parser's accepted format”, not “no published pay”.

The parser also doesn't identify currency from $. Nor should it annualise hourly pay: that requires assumptions about paid hours and working weeks that these fields don't supply.

For a wage comparison, retain currency and period as grouping dimensions. Treat a range midpoint as a chosen summary of an advertised interval, never as observed earnings.

Enforce types while retaining absence

The loader below preserves selected raw strings, serialises arrays without guessing their element schema, and gives structured bounds nullable numeric types.

It also records key presence before missing values become indistinguishable in a table.

sql_types = {
    "jobId": "VARCHAR",
    "employerId": "VARCHAR",
    "salary": "VARCHAR",
    "salaryMin": "DOUBLE",
    "salaryMax": "DOUBLE",
    "salaryCurrency": "VARCHAR",
    "paySchedule": "VARCHAR",
    "ownershipType": "VARCHAR",
    "roleGroups": "JSON",
    "salaryType": "JSON",
    "descriptionHtml": "VARCHAR",
    "postedDate": "TIMESTAMPTZ",
    "scrapedAt": "TIMESTAMPTZ",
}

typed = raw.reindex(columns=list(sql_types)).copy()
parse_failed = pd.Series(False, index=typed.index, dtype="boolean")

for field, sql_type in sql_types.items():
    original = typed[field]
    if sql_type == "DOUBLE":
        typed[field] = pd.to_numeric(
            original, errors="coerce"
        ).astype("Float64")
    elif sql_type == "TIMESTAMPTZ":
        typed[field] = pd.to_datetime(
            original, errors="coerce", utc=True
        )
    elif sql_type == "JSON":
        values = [record.get(field) for record in items]
        if field == "roleGroups":
            if any(v is not None and not isinstance(v, list) for v in values):
                raise TypeError("roleGroups must be an array when present")
        typed[field] = pd.Series(
            [pd.NA if v is None else json.dumps(v) for v in values],
            index=typed.index,
            dtype="string",
        )
    else:
        typed[field] = original.astype("string")

    if sql_type in {"DOUBLE", "TIMESTAMPTZ"}:
        parse_failed |= original.notna() & typed[field].isna()

typed["amount_key_present"] = pd.Series(
    [any(k in r for k in ("salary", "salaryMin", "salaryMax")) for r in items],
    index=typed.index,
    dtype="boolean",
)
typed["range_keys_present"] = pd.Series(
    ["salaryMin" in r and "salaryMax" in r for r in items],
    index=typed.index,
    dtype="boolean",
)
typed["range_values_usable"] = (
    typed["salaryMin"].notna()
    & typed["salaryMax"].notna()
    & typed["salaryMax"].ge(typed["salaryMin"])
).fillna(False)
typed["parse_failed"] = parse_failed

Keep items as the raw evidence alongside this projection. Coercion makes invalid numbers and dates queryable as missing values; the failure flag prevents that convenience from erasing a quality problem.

UTC is the loader's normalisation policy. Review timezone assumptions before treating a timezone-free timestamp as an exact instant.

ownershipType remains nullable here to support a shared projection. Its presence in the schema doesn't enrich job records with employer-profile information.

Which denominator belongs on the chart?

The worked source is our Handshake campus jobs Actor. Its public posting data supports several distinct measures, and each needs its own label.

“Any amount-bearing key present” measures extraction presence. “Both range keys present” measures structural completeness. “Usable ordered bounds” adds successful numeric conversion and a range-order check.

These aren't interchangeable definitions of transparency.

Create the table from the fixed types, insert the batch together and calculate each measure against all retrieved rows.

flag_fields = (
    "amount_key_present",
    "range_keys_present",
    "range_values_usable",
    "parse_failed",
)
sql_types.update({field: "BOOLEAN" for field in flag_fields})

connection = duckdb.connect()
column_definitions = ", ".join(
    f'"{field}" {sql_type}' for field, sql_type in sql_types.items()
)
connection.execute(f"CREATE TABLE jobs ({column_definitions})")
connection.register("incoming_jobs", typed)
connection.execute("INSERT INTO jobs SELECT * FROM incoming_jobs")

coverage = connection.execute("""
    SELECT
        count(*) AS retrieved_postings,
        avg(amount_key_present::INTEGER) AS amount_key_coverage,
        avg(range_keys_present::INTEGER) AS complete_range_key_coverage,
        avg(range_values_usable::INTEGER) AS usable_range_coverage,
        count(*) FILTER (WHERE parse_failed) AS parse_failure_rows
    FROM jobs
""").fetchdf()

connection.close()

An empty retrieval produces undefined coverage rather than a reassuring zero. Explicit nulls, if encountered, can have key presence without usable values; that discrepancy deserves inspection.

For repeated snapshots, check jobId uniqueness within each snapshot before aggregating. Across snapshots, the same job belongs to each relevant observation period, so deduplicating globally would erase its history.

Don't fill missing bounds with zero. That inserts invented wages into your distribution.

Dropping those rows has a different limitation: your median then describes postings with usable reported pay. It can't describe wages across all retrieved postings without assumptions about the missing population.

The refresh still can't explain employer intent

Public discovery isn't a census of every opportunity. The search index is cached, and newly posted jobs can take a few days to appear through keyword, role or city discovery; direct URLs return the current public record.

A changed coverage rate can therefore reflect changed posting membership, extraction presence, parsing success or collection settings. Compare retained job IDs across snapshots as well as the full retrieved populations, and report how many matched postings remain.

Missing structured pay also doesn't establish that no pay language appears in descriptionHtml. Any description-based measure needs a separate extraction definition and validation.

Public pay-field absence can't establish an employer's intent or a legal compliance finding. These records don't establish every fact needed for either conclusion.

Before you defend “employers became less transparent”, defend “these are the same kinds of postings, counted the same way”.

Your creator count may be a pagination ceiling in disguise

Dataspryva — Fri, 23 Oct 2026 09:57:13 +0000

Your creator chart looks suspiciously tidy when every category stops at the same count. Before you publish a market comparison, check whether you've measured competition or pagination.

Dailymotion's searchUsers and byPlaylist modes stop at 1,000 records per query because their endpoints don't accept date filters.

Setting maxItems to 10,000 enables date-window sweeps for video search, topics and channel uploads. It doesn't expand those creator-search or playlist endpoints. A valid input can still ask for more than the selected endpoint can return.

Keep each query attached to its rows

The distinction matters before you count anything: searching for creators returns channels; fetching a channel's uploads returns videos. More video records don't establish that you've discovered more competitors.

For a coverage audit, keep the input beside the returned records. Don't rely on reconstructing selection rules from titles or profile descriptions later.

Example input: this cell requests a creator search and retains its status, selection and records.

import os

from apify_client import ApifyClient
import pandas as pd
import duckdb

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

def fetch(run_input):
    run = client.actor(
        "dataspryva/dailymotion-video-discovery-by-topic-and-channel"
    ).call(run_input=run_input)
    items = list(
        client.dataset(run["defaultDatasetId"]).iterate_items()
    )
    return {
        "input": run_input,
        "status": run["status"],
        "items": items,
    }

run_input = {
    "mode": "searchUsers",
    "searchQueries": ["space news"],
    "sortBy": "popular",
    "maxItems": 10000,
}
directory_pull = fetch(run_input)

Streaming the dataset avoids treating a dataset retrieval page as the entire collection. Otherwise, you can accidentally audit the retrieval method's coverage rather than the Actor's.

The query also matters. Keyword discovery defines a matching population, not every account operating in a market. Accounts that don't match your search remain outside the selection, however far pagination goes.

Channel uploads answer a different question

The word “channel” does awkward double duty here. Channel discovery searches account records; a channel sweep walks an already selected account's video history.

The coverage boundary follows the endpoint, not the noun.

Selection	Returned unit	Date-window expansion
`search`, `byTopic`	Video	Supported
`byUser`	Video from a supplied channel	Supported
`searchUsers`	Channel matching a keyword	Unsupported
`byPlaylist`	Video from a supplied playlist	Unsupported

Playlist reading also starts from a known URL or ID. There's no playlist keyword-search mode, so a collection of supplied playlists isn't a discovered playlist directory.

Example input: this cell fetches uploads from a supplied channel, keeping that pull separate from creator discovery.

uploads_input = {
    "mode": "byUser",
    "usernames": ["euronews-en"],
    "sortBy": "recent",
    "maxItems": 10000,
}
uploads_pull = fetch(uploads_input)
pulls = [directory_pull, uploads_pull]

Each run here has a single query or channel seed. That makes the returned count attributable to its selection. Combining several queries inside a run would make a run-level count harder to interpret against a per-query boundary.

For larger date-window sweeps, ordering becomes chronological, newest first or oldest first. You can't treat an expanded pull as a deeper relevance ranking or a longer most-viewed leaderboard.

Geography and dates don't travel between modes

A country input isn't a universal market boundary. It filters videos in search and trending; it doesn't geographically restrict creator discovery or a channel's uploads.

For a creator-market chart, that leaves an analytical choice: classify returned channels using their reported country, or classify videos using their owners' country. Those describe different populations, and neither establishes where the audience lives.

The code below keeps those country meanings explicit. Missing country values stay missing rather than becoming an “other country” bucket with an unjustifiably confident label.

Dates need the same care. A video's publishedAt describes publication, while a channel's createdAt describes account creation. Neither can stand in for the other when you define “active creators”.

Publication filters use roughly five-minute buckets, so neighbouring content can appear around a day boundary. If your report uses strict calendar periods, apply that boundary to parsed timestamps and retain a count of excluded records.

Only publicly accessible videos are returned. Private and unlisted material sits outside that frame, rather than inside a missing-value category.

Preserve missing fields before counting

Video records carry recordType="video" and use videoId; creator-search records carry recordType="channel" and use channelId. Counting whichever identifier happens to be present would hide a record-type mismatch.

Optional metadata is another coverage dimension. Fields such as tags, description and firstFrameUrl can be omitted because the source doesn't carry them. An analysis requiring those fields selects a narrower population than the original pull.

This cell builds audit tables with explicit missing values and parses publication timestamps; the snake-case columns are analysis columns created here.

manifest_rows = []
record_rows = []

for pull_id, pull in enumerate(pulls):
    config = pull["input"]
    is_directory = config["mode"] == "searchUsers"
    selector_key = "searchQueries" if is_directory else "usernames"
    manifest_rows.append({
        "pull_id": pull_id,
        "input_mode": config["mode"],
        "selector": config[selector_key][0],
        "expected_type": "channel" if is_directory else "video",
        "status": pull["status"],
        "requested": config["maxItems"],
        "ceiling": min(config["maxItems"], 1000)
                   if is_directory else config["maxItems"],
    })
    for item in pull["items"]:
        record_rows.append({
            "pull_id": pull_id,
            "record_type": item.get("recordType"),
            "entity_id": item.get(
                "channelId" if is_directory else "videoId"
            ),
            "reported_country": item.get(
                "country" if is_directory else "ownerCountry"
            ),
            "published_at": None if is_directory
                            else item.get("publishedAt"),
        })

manifest = pd.DataFrame(manifest_rows)
records = pd.DataFrame(record_rows, columns=[
    "pull_id", "record_type", "entity_id",
    "reported_country", "published_at",
])
for column in ["record_type", "entity_id", "reported_country"]:
    records[column] = (
        records[column].astype("string").str.strip().replace("", pd.NA)
    )
records["published_at"] = pd.to_datetime(
    records["published_at"], utc=True, errors="coerce"
)

Keeping the manifest separate preserves empty pulls. Otherwise, a query returning no records disappears from the audit, which is an impressively efficient way to erase inconvenient coverage gaps.

Measure the boundary, not an imaginary missing tail

A count at the endpoint ceiling is a warning, not proof that additional matching records exist. A count below it doesn't establish a complete market either: selection, accessibility and run completion still matter.

There's no defensible missing-tail estimate available from the returned count alone.

This DuckDB check separates row volume, distinct usable identities, missing geography and observed publication range, while preserving pulls with no records.

connection = duckdb.connect()
connection.register("manifest", manifest)
connection.register("records", records)

audit = connection.execute("""
    WITH counts AS (
        SELECT
            m.*,
            count(r.pull_id) AS rows_returned,
            count(DISTINCT r.entity_id) FILTER (
                WHERE r.record_type = m.expected_type
            ) AS distinct_entities,
            count(r.pull_id) FILTER (
                WHERE r.record_type IS DISTINCT FROM m.expected_type
                   OR r.entity_id IS NULL
            ) AS unusable_rows,
            count(r.reported_country) FILTER (
                WHERE r.record_type = m.expected_type
                  AND r.entity_id IS NOT NULL
            ) AS rows_with_country,
            min(r.published_at) FILTER (
                WHERE r.record_type = 'video'
            ) AS earliest_publication,
            max(r.published_at) FILTER (
                WHERE r.record_type = 'video'
            ) AS latest_publication
        FROM manifest AS m
        LEFT JOIN records AS r USING (pull_id)
        GROUP BY ALL
    )
    SELECT *,
        rows_returned >= ceiling AS boundary_reached,
        CASE
            WHEN status <> 'SUCCEEDED' THEN 'run incomplete'
            WHEN unusable_rows > 0 THEN 'check record identity'
            WHEN rows_returned >= ceiling THEN 'boundary reached'
            ELSE 'below boundary; completeness unknown'
        END AS assessment
    FROM counts
    ORDER BY pull_id
""").df()

audit gives you measured coverage diagnostics for your pulls. Publication endpoints describe the observed span, not continuous coverage between those timestamps.

The boundary check uses returned rows because that's the collection limit being examined. Distinct identities answer a separate question: how many usable entities your dataset represents.

Put the selection rule beside the chart

The worked example uses Dailymotion Video Discovery by Topic & Channel. The reusable lesson is to carry selection metadata into the report instead of discarding it once ingestion succeeds.

For creator discovery, a defensible chart label is “Distinct channels returned by keyword search”. Alongside it, state the query, ordering, retrieval time and whether the audit reached the endpoint boundary.

With popular ordering, the search is ordered by follower count. If collection stops at the boundary, the returned set can't establish the distribution of accounts beyond that point.

For uploads, label the measure as videos from the supplied channels, with the publication window and any language, tag or duration restrictions. The input channel list is part of the population definition.

The distinction is small enough to fit beside a chart:

keyword search → returned channel IDs → observed creator set
known channel  → returned video IDs   → observed upload catalog

If you add narrower keyword queries, deduplicate channel IDs across pulls. The combined set may expand discovery, but lexical slices can overlap and omit accounts in related ways. They don't become a representative sample merely because you used more queries.

The market outside the returned rows

These records can describe public channels and videos reached through your selections. They can support comparisons within that observed set, provided missing metadata and collection boundaries stay visible.

They don't establish total creator volume, the number of commercially active competitors, or the size of an unseen tail. More uploads from known channels can deepen a catalogue without broadening creator discovery.

An unknown tail is still unknown. A round number doesn't make it a market size.

Your hiring chart may measure who posts most often

Dataspryva — Thu, 22 Oct 2026 13:13:53 +0000

Your Friday investigation needs an industry ranking. On Thursday evening, industry.value_counts() looks reassuringly tidy. The uncomfortable question is whether those bars measure hiring demand or visibility in a recent-results feed.

Totaljobs serves anonymous searches newest-first. Relevance ordering requires a signed-in session and can't be combined with the filters, so the Actor always returns newest-first results.

A capped collection therefore selects through posting recency. Recently posted adverts can enter the collection while older vacancies remain open beyond its reach. A chart can describe the collected adverts perfectly and still answer the wrong question.

Fresh adverts get more chances to appear

The tempting assumption is that matching an occupational keyword produces a representative cross-section of that occupation. Matching and representation are separate properties.

An agency might repeatedly advertise similar roles while a direct employer keeps an advert open through a longer application cycle. A recent-results feed can favour the former pattern. That's a mechanism to investigate, not evidence that agencies dominate your collection.

Keep these boundaries beside your notebook.

Coverage boundary	What you can establish
Public listing scope	UK vacancies on Totaljobs, reached through keyword and area searches, area browsing or supplied URLs
Primary key	`jobId` identifies a listing, not an underlying hiring requirement
Search ceiling	Each query exposes at most 100 jobs; larger requests trigger automatic splitting across facets
Collection ceiling	`maxItems` accepts up to 5000; requesting that ceiling doesn't establish complete coverage
Field availability	Empty fields are omitted; detail fetching and published information determine availability

Start with an explicit search boundary and retain full snapshots.

Example: this input requests nursing vacancies in Manchester, with a chosen posting window and collection ceiling.

import os

import duckdb
import pandas as pd
from apify_client import ApifyClient

run_input = {
    "mode": "search",
    "keywords": ["registered nurse"],
    "location": "Manchester",
    "postedWithin": "7",
    "includeDetails": True,
    "onlyNewJobs": False,
    "maxItems": 500,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/totaljobs-uk-vacancy-finder-by-keyword-area"
).call(run_input=run_input)

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

The input defines an observable slice. It doesn't establish its share of the underlying vacancy population.

Where the search boundary bites

Leaving location empty searches UK-wide; specifying a town, city, county or postcode narrows the search. That requested area isn't proof of a precise workplace boundary, particularly for remote or multi-site roles.

Keywords are searched separately. A keyword match doesn't establish an occupational classification, and industry carries the site's classification rather than an independently verified industry assignment.

postedWithin accepts 1, 3, 7 or 14 days. Every returned job is checked against its posting date, including where the site's date control is unavailable.

That excludes older adverts that may still be open.

Salary filtering creates another boundary: it keeps overlapping annualised advertised pay ranges and excludes jobs without published pay. An industry chart made from that collection describes salary-disclosing adverts.

The legacy employment inputs full-time, internship and apprenticeship are accepted but ignored because the site no longer offers those filters. An accepted input isn't evidence that the restriction happened.

The collection path looks like this:

matching public adverts
        ↓
newest-first searches across reachable slices
        ↓
job-ID deduplication and collection ceiling
        ↓
observed adverts available for analysis

Splitting doesn't create a random sample. Reaching your ceiling means collection stopped there; falling short doesn't establish that every matching advert was reachable.

Neither outcome supplies a population coverage percentage.

Missing dates can make a clean chart misleading

Opening job pages enables exact posting dates, descriptions, parsed salary bands and company details. Coordinates and employer profile information still depend on what's published.

When a chart drops records without industry or companyId, its denominator becomes “adverts with that field available”. That may be a different slice from the collection as a whole.

Dates need particular care. Without detail fetching, postedDate can contain a relative label instead of an ISO timestamp. Silently discarding those labels can make the remaining records look unusually well dated.

firstSeenAt records when onlyNewJobs first returned the vacancy. It isn't the employer's posting timestamp.

Keep comparable snapshots together

For repeated collection, preserve the input and keep onlyNewJobs disabled. Incremental returns suppress previously seen IDs, so they can't measure repeated visibility across full snapshots.

The next cell combines the current dataset with optional earlier dataset IDs from the comma-separated environment variable PREVIOUS_DATASET_IDS. Use earlier datasets collected with the same input.

Streaming avoids treating a response page as the entire dataset.

previous_ids = [
    value.strip()
    for value in os.environ.get("PREVIOUS_DATASET_IDS", "").split(",")
    if value.strip()
]
dataset_ids = list(dict.fromkeys(
    previous_ids + [run["defaultDatasetId"]]
))

rows = [
    {**item, "_dataset_id": dataset_id}
    for dataset_id in dataset_ids
    for item in client.dataset(dataset_id).iterate_items()
]

columns = [
    "_dataset_id", "jobId", "title", "company", "companyId",
    "companyUrl", "location", "locationPostcode", "industry",
    "postedDate", "postedDateText", "scrapedAt",
]
frame = pd.DataFrame(rows).reindex(columns=columns).astype("string")
frame = frame.replace(r"^\s*$", pd.NA, regex=True)

Names beginning with _ belong to this analysis. They aren't returned fields.

With only the current dataset, you can audit dates and field availability. Comparisons across collection times require earlier snapshots.

How old are the adverts you received?

Use scrapedAt as the record's observation timestamp, keeping exact dates separate from relative labels.

This cell parses numeric day labels without pretending they identify an exact posting instant.

frame["_posted_at"] = pd.to_datetime(
    frame["postedDate"], format="ISO8601", errors="coerce", utc=True
)
frame["_scraped_at"] = pd.to_datetime(
    frame["scrapedAt"], format="ISO8601", errors="coerce", utc=True
)
frame["_age_days"] = (
    frame["_scraped_at"] - frame["_posted_at"]
).dt.total_seconds() / pd.Timedelta(days=1).total_seconds()

relative_text = frame["postedDateText"].fillna(frame["postedDate"])
frame["_label_days"] = pd.to_numeric(
    relative_text.str.extract(
        r"(?i)^\s*(\d+)\s+days?\s+ago\s*$", expand=False
    ),
    errors="coerce",
).astype("Int64")

Other label formats remain unparsed. We'd rather expose the gap than manufacture a timestamp.

The following cell calculates field coverage per pull and separate distributions for exact posting ages and relative labels.

coverage = frame.groupby("_dataset_id").agg(
    saved_rows=("jobId", "size"),
    distinct_job_ids=("jobId", "nunique"),
    missing_job_ids=("jobId", lambda values: values.isna().sum()),
    dated_rows=("_posted_at", "count"),
    timestamped_rows=("_scraped_at", "count"),
    industry_rows=("industry", "count"),
    usable_age_rows=("_age_days", lambda values: values.ge(0).sum()),
    negative_ages=("_age_days", lambda values: values.lt(0).sum()),
).reindex(dataset_ids).fillna(0).astype("int64")

coverage["rows_without_exact_posting_date"] = (
    coverage["saved_rows"] - coverage["dated_rows"]
)

age_by_day = (
    frame.loc[frame["_age_days"].ge(0)]
    .assign(_age_day=lambda data: data["_age_days"].floordiv(1).astype("Int64"))
    .groupby(["_dataset_id", "_age_day"])
    .size()
    .rename("adverts")
)

relative_labels = (
    frame.groupby(["_dataset_id", "_label_days"])
    .size()
    .rename("adverts")
)

Inspect coverage before interpreting age_by_day. Missing timestamps never enter the age bins, and negative ages need investigation.

These calculations describe returned records. They can't reveal the posting ages of adverts excluded by the search ceiling.

Different listing IDs don't prove a repost

Deduplication removes repeated appearances of the same jobId across overlapping search slices. A replacement advert with a different ID remains a different listing.

Conversely, a familiar title doesn't prove deletion and reposting. Similar positions can represent different shifts, sites or hiring requirements.

This cell constructs repeat_candidates, a grouped review table with counts and distinct listing IDs calculated from your snapshots.

frame["_employer_key"] = (
    ("id:" + frame["companyId"])
    .fillna("url:" + frame["companyUrl"])
    .fillna(
        "name:" + frame["company"].str.casefold()
        .str.replace(r"\s+", " ", regex=True).str.strip()
    )
)
frame["_title_key"] = (
    frame["title"].str.casefold()
    .str.replace(r"\s+", " ", regex=True).str.strip()
)
frame["_place_key"] = (
    frame["locationPostcode"].fillna(frame["location"])
    .str.casefold().str.replace(r"\s+", " ", regex=True).str.strip()
)

connection = duckdb.connect()
connection.register("observations", frame)

repeat_candidates = connection.sql("""
    SELECT
        _employer_key,
        _title_key,
        _place_key,
        count(*) AS observed_rows,
        count(DISTINCT _dataset_id) AS pulls_seen,
        count(DISTINCT jobId) AS distinct_listing_ids,
        list(DISTINCT jobId ORDER BY jobId) AS listing_ids
    FROM observations
    WHERE _employer_key IS NOT NULL
      AND _title_key IS NOT NULL
      AND _place_key IS NOT NULL
      AND jobId IS NOT NULL
    GROUP BY _employer_key, _title_key, _place_key
    HAVING count(DISTINCT _dataset_id) > 1
       AND count(DISTINCT jobId) > 1
    ORDER BY distinct_listing_ids DESC, pulls_seen DESC
""").df()

Each row groups a matching advertiser, title and place. Its counts distinguish repeated observations from distinct IDs, while the ID list gives you the listings to inspect.

Employer-name fallbacks are weaker matches than profile identifiers. Missing place information excludes a record from this check, so an empty queue wouldn't establish an absence of reposting.

An agency comparison also needs a checked advertiser classification. applyType distinguishes internal from external application routes. It doesn't identify an agency, and an advertiser profile needn't identify the organisation employing the successful applicant.

A convenient field is rarely grateful when asked to do a different job.

Compare industry shares before writing the caption

The collector used here's Totaljobs UK Vacancy Finder by Keyword, Area & Pay.

Before publication, compare industry percentages from pooled feed observations with percentages after keeping each listing ID once. This measures how repeated observations affect your chart; it doesn't remove replacement adverts carrying new IDs.

This cell creates the comparison table, including counts, percentages and their difference in percentage points, while retaining unpublished industry classifications as a visible category.

identified = frame.loc[frame["jobId"].notna()].copy()
identified["_industry"] = identified["industry"].fillna(
    "(industry not published)"
)

distinct_listings = (
    identified.sort_values(
        "_scraped_at", na_position="first", kind="stable"
    )
    .drop_duplicates("jobId", keep="last")
)

industry_comparison = pd.DataFrame(
    index=pd.Index(identified["_industry"].unique(), name="industry")
)
industry_comparison["raw_observations"] = (
    identified.groupby("_industry").size()
    .reindex(industry_comparison.index, fill_value=0)
)
industry_comparison["distinct_listings"] = (
    distinct_listings.groupby("_industry").size()
    .reindex(industry_comparison.index, fill_value=0)
)
industry_comparison = industry_comparison.astype("int64")

raw_total = industry_comparison["raw_observations"].sum()
listing_total = industry_comparison["distinct_listings"].sum()

industry_comparison["raw_share_pct"] = (
    industry_comparison["raw_observations"]
    / (raw_total if raw_total else float("nan")) * 100
)
industry_comparison["listing_share_pct"] = (
    industry_comparison["distinct_listings"]
    / (listing_total if listing_total else float("nan")) * 100
)
industry_comparison["share_change_pp"] = (
    industry_comparison["listing_share_pct"]
    - industry_comparison["raw_share_pct"]
)
industry_comparison = industry_comparison.sort_values(
    "distinct_listings", ascending=False
)

Both denominators exclude missing listing IDs, which coverage counts separately. Where an ID has different industry values across observations, the distinct-listing calculation prefers its latest timestamped observation.

With comparable full snapshots, raw shares weight adverts by their observed appearances. Distinct-listing shares give each observed ID equal weight. With only the current collection, this comparison can't diagnose persistence across pulls.

Neither column is a corrected estimate of hiring demand.

A defensible caption can say:

Distinct observed vacancy adverts returned for the specified keyword, area and posting window through newest-first searches.

Beside the chart, disclose the collection ceiling, filters, observation period and missing classifications. If you pool snapshots, identify whether the bars count observations or distinct listing IDs.

Counting identified advertisers offers another unit of analysis, provided you disclose identity coverage. It doesn't repair unseen vacancies.

The hiring decision remains off the page

Public adverts don't establish headcount, filled positions, hiring urgency or whether recruitment represents expansion, replacement or an ongoing requirement.

The posting date doesn't establish when an employer first needed someone. Similar adverts with different identities can support further checking, but can't prove deliberate feed manipulation.

You can describe which industries occupy the collected feed and how that composition changes under comparable search conditions. Claiming which industries are aggressively hiring requires evidence beyond the feed.

A posting timestamp measures an advert's recency. It doesn't measure an employer's appetite.