Your enrichment join is losing newer registrations, and the report still needs a company count you can defend. Before blaming registry coverage, look for this: str.replace(r"\D", "", regex=True).
A digits-only cleanup can damage a valid alphanumeric CNPJ before a lookup begins.
Our CNPJ lookup Actor accepts numeric and alphanumeric identifiers, validates their check digits, and skips invalid inputs before making any request. An ingestion bug can therefore exclude companies before source availability becomes relevant.
The cleanup changes more than formatting
Removing punctuation and removing everything except digits used to look like equivalent operations. Letters make the difference expensive.
Keep the original beside each transformation; the Example identifiers below cover both accepted forms.
import os
import duckdb
import pandas as pd
from apify_client import ApifyClient
def normalise_cnpj(values):
return (
values.astype("string")
.str.strip()
.str.replace(r"[./-]", "", regex=True)
)
original = pd.Series(
["18.236.120/0001-58", "00.000.000/E08G-12"],
dtype="string",
)
audit = pd.DataFrame()
audit["original_text"] = original
audit["cnpj_key"] = normalise_cnpj(audit["original_text"])
audit["digits_only_key"] = audit["original_text"].str.replace(
r"\D", "", regex=True
)
audit["integer_key"] = (
pd.to_numeric(audit["cnpj_key"], errors="coerce")
.astype("Int64")
.astype("string")
)
audit["cleanup_changed_key"] = (
audit["digits_only_key"].ne(audit["cnpj_key"]).fillna(True)
)
The integer conversion makes unparseable identifiers missing and discards leading zeroes from numeric identifiers. The digits-only replacement leaves a string, which can look reassuring while representing a different identifier.
Neither transformation belongs in an identity join. Arithmetic offers nothing useful here.
When investigating your pipeline, substitute the untouched ingestion column for original. Compare its normalised value with the actual key saved immediately before your existing lookup call. Recreating the old cleanup is useful, but preserving what it really submitted is better evidence.
If your first surviving copy is already numeric, converting it back to text can't reconstruct discarded characters or leading zeroes.
Which companies does your join represent?
Since July 2026, new registrations can include letters. Damage correlated with identifier format can therefore become correlated with registration cohort.
It doesn't follow that every newer registration has letters, or that every unmatched company suffered this bug. That's precisely why the audit needs separate stages.
The selection path makes the potential exclusion visible:
your original company list
-> identifier transformation
-> validation and deduplication
-> lookup processing cap
-> source response
-> returned company records
Your intended population is the company list you started with. Your enriched dataset represents the subset that survives this path.
Suppose your question is whether newer registrations have a different industry mix. Excluding damaged identifiers changes which registrations contribute to that comparison, even when every returned industry field is correct.
The direction of the distortion depends on the excluded companies. The identifier bug alone can't establish it.
There's another unit-of-analysis trap: cnpjRaiz groups establishments sharing a root. Using it instead of the full cnpj changes which records you count as distinct.
A tidy join can still answer the wrong question. Tidy joins are quite accommodating that way.
Keep an audit boundary before validation
The normalised key should preserve the full 14-character identifier. A character-pattern check is useful for locating damage, but it can't establish valid check digits.
This cell labels identifier format, flags structural failures and builds a distinct submission list without deleting the audit rows.
audit["shape_ok"] = audit["cnpj_key"].str.fullmatch(
r"[A-Za-z0-9]{14}", na=False
)
audit["identifier_format"] = pd.Series(
"unclassified", index=audit.index, dtype="string"
)
audit.loc[
audit["shape_ok"]
& audit["cnpj_key"].str.fullmatch(r"[0-9]{14}", na=False),
"identifier_format",
] = "numeric"
audit.loc[
audit["shape_ok"]
& audit["cnpj_key"].str.contains(r"[A-Za-z]", na=False),
"identifier_format",
] = "alphanumeric"
request_keys = (
audit.loc[audit["shape_ok"], "cnpj_key"].drop_duplicates().tolist()
)
audit["sent"] = audit["cnpj_key"].isin(request_keys)
Keep structural failures in the denominator and in a review queue. Dropping them before calculating retention makes the cleanup grade its own homework.
The Actor performs check-digit validation after this screening. Passing shape_ok means only that the string has the expected character shape.
The useful distinctions are:
| Audit boundary | What it establishes |
|---|---|
| Original text versus normalised key | Whether removing formatting preserved identity |
| Normalised key versus saved submission key | Whether upstream handling changed the identifier |
Submitted key versus returned cnpj
|
Whether that submitted identity received a record |
Make the cap match the lookup list
In lookup mode, maxItems limits CNPJs processed, rather than promising a matching number of returned records. Its default is 1000.
A capped run adds another selection boundary. If you don't know which keys were processed, you can't classify every unmatched key as a source failure.
This cell sets the cap to the distinct submission count and refuses a list outside the permitted range.
if not 1 <= len(request_keys) <= 100000:
raise ValueError("Use a non-empty batch within the lookup limit.")
run_input = {
"mode": "lookup",
"cnpjs": request_keys,
"maxItems": len(request_keys),
}
client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
"dataspryva/brazilian-cnpj-bulk-lookup-and-partner-records"
).call(run_input=run_input)
if run["status"] != "SUCCEEDED":
raise RuntimeError(f"Lookup ended with status {run['status']}")
items = []
for item in client.dataset(run["defaultDatasetId"]).iterate_items():
items.append(item)
For a larger list, split distinct keys into disjoint batches and retain batch membership alongside each key. Silent slicing is another way to manufacture a sample without admitting you've sampled.
cnpjText can extract, validate and deduplicate identifiers from pasted text. It can't recover letters your spreadsheet export has already removed.
Locate losses with an outer join
Empty output fields are omitted. Build the expected columns explicitly, normalise returned identifiers consistently, and keep records without a key separate.
This cell reconciles original input rows with returned identities and parses available activity-start dates.
returned = pd.DataFrame(items).reindex(
columns=["cnpj", "cnpjFormatado", "dataInicioAtividade", "source"]
)
returned["cnpj_key"] = normalise_cnpj(returned["cnpj"]).fillna(
normalise_cnpj(returned["cnpjFormatado"])
)
returned["activity_start"] = pd.to_datetime(
returned["dataInicioAtividade"],
format="%Y-%m-%d",
errors="coerce",
)
returned["returned_record"] = True
audit["input_record"] = True
unkeyed_returns = returned.loc[returned["cnpj_key"].isna()].copy()
reconciliation = audit.merge(
returned.loc[returned["cnpj_key"].notna()],
on="cnpj_key",
how="outer",
validate="many_to_one",
)
reconciliation["input_record"] = (
reconciliation["input_record"].astype("boolean").fillna(False)
)
reconciliation["returned_record"] = (
reconciliation["returned_record"].astype("boolean").fillna(False)
)
The merge preserves unmatched input rows and unexpected returned identities. Its validation also prevents duplicate returned keys from quietly multiplying your input rows.
An unmatched input row still needs interpretation. A structural failure never entered this submission list; a submitted key without a returned record needs further reconciliation.
The run summary separates invalid, not-found, duplicate and unavailable counts. Keep those categories separate when explaining the gap. Aggregate counts don't assign a cause to each missing key, and a duplicate submission isn't an additional missing company.
Report retention with its denominator
For the Brazilian CNPJ Bulk Lookup & Partner Records Scraper, the useful first comparison is retention by identifier format.
This cell calculates row-level retention against the original input rows, including those excluded by structural screening.
input_rows = reconciliation.loc[
reconciliation["input_record"]
].copy()
input_rows["matched"] = input_rows["returned_record"]
format_retention = duckdb.sql("""
SELECT
identifier_format,
count(*) AS original_rows,
count(*) FILTER (WHERE sent) AS submitted_rows,
count(*) FILTER (WHERE matched) AS matched_rows,
avg(CAST(matched AS DOUBLE)) AS row_retention
FROM input_rows
GROUP BY identifier_format
ORDER BY identifier_format
""").df()
Repeated identifiers remain repeated in this metric. That's appropriate for measuring enrichment of your input rows, but a distinct-registration measure needs a denominator of distinct, reviewed identities.
Report the unit, the submission boundary, structural exclusions and unresolved submitted keys alongside retention. A percentage without those details leaves your manager guessing what survived long enough to be counted.
A format gap is a diagnostic signal. It doesn't establish that the Actor or the registry treats either format differently.
A returned date can't date the missing companies
dataInicioAtividade lets you describe activity-start cohorts among records that returned a usable date.
This cell counts that observable subset without assigning dates to missing records.
dated_returns = returned.loc[returned["activity_start"].notna()].copy()
observed_cohorts = (
dated_returns.groupby(
dated_returns["activity_start"].dt.to_period("M")
)
.size()
.rename("returned_records")
)
That's cohort composition, not cohort retention. Calculating retention by dates learned only through successful enrichment would exclude the failures you're trying to explain.
For a date-based retention comparison, you need dates available independently before enrichment. Limit that comparison to input records with those dates, disclose how many lack them, and keep activity-start date distinct from identifier issuance.
The mirrors reflect a registry published monthly, so very recent changes may also be absent. A missing return alone can't distinguish snapshot lag from other lookup failures.
Before explaining who disappeared, establish which identifiers your pipeline allowed to arrive.
Drafted with AI assistance and reviewed for accuracy.
Top comments (0)