Your release count may give featured episodes extra votes

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

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

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

Which population does your count describe?

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

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

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

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

import os

import duckdb
import pandas as pd
from apify_client import ApifyClient

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

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

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

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

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

A page can show the same release again

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

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

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

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

The identity rules differ too:

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

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

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

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

Keep the section before flattening

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

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

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

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

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

extracted = []
section_audit = []

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

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

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

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

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

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

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

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

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

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

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

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

Match URLs without inventing an ID join

Different identifier names don't establish an identity mapping.

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

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

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

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

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

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

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

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

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

Let the audit challenge the headline

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

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

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

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

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

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

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

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

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

Change the input before changing the claim

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

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

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

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

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

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

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

The missing history stays missing

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

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

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

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