Colquhoun.AI
← All guides

Research Guides

Check the Join Before Totaling a Research Export

· Colquhoun.shop editorial

Adding page metadata to an analytics export should not silently create more traffic. Yet a merge can repeat a measurement when several rows in the second file match the same key. Before trusting a combined research table, check the relationship between its inputs.

This is a manual data-review workflow for authorized exports. It is not a Colquhoun.shop import feature, and a successful merge does not verify the underlying measurements.

1. Define what one row means in each file

Write a sentence for each input. For example: “The first file contains one page per month; the second contains one metadata observation per page per crawl.” Both include a URL, but URL alone does not identify a single row in either file.

Keep source row IDs and untouched copies. Record the period, filters and relevant dimensions before matching anything. A convenient shared column is not necessarily a sufficient key.

2. State the intended matching relationship

If each traffic row should receive one metadata record, the metadata side needs at most one applicable match for that row. If several observations are relevant, decide how to represent them without pretending that repeated traffic values are new measurements.

The pandas documentation explains that a many-to-many merge produces combinations of matching records when the key repeats on both sides. It also documents validation of key uniqueness before a merge. These are useful checks when implementing a repeatable workflow in pandas. pandas: merging and merge-key validation

3. Test a deliberately small example

In a fictional export, one article has 100 visits in January and 150 in February. A second file contains three crawl observations for the same URL. Joining only on URL pairs each monthly row with all three crawl records, producing six rows.

The source traffic total was 250. Summing the repeated visit values after that merge gives 750. No new visitor was measured: the transformation repeated existing values. This example is an arithmetic check, not an observation about a live website.

Do not repair the result by deleting arbitrary repeated rows. If the research question needs the metadata observed nearest each reporting period, define and verify that selection rule. If the three crawls document changes over time, retain them as a separate history rather than forcing one into an unexplained lookup.

4. Account for unmatched records as well

Save a list of source rows that found no match and investigate a few concrete cases. The missing metadata might reflect an unvisited URL, a different address representation or a scope mismatch. It is not automatically evidence that the page disappeared.

Decide whether the final report should retain those rows with an explicit unresolved status. Do not let an output containing only matched pages quietly become a statement about the entire source population.

5. Reconcile before writing the headline

Compare source and output row counts, distinct source IDs and relevant totals. For the specific task of attaching one metadata record to each traffic row, unexpected row growth or a changed traffic total should stop publication until explained.

Some research questions intentionally produce multiple matches. In that case, label the output as a relationship table and keep measures at their original level. A larger result is not inherently wrong; treating it as the same measurement table can be.

Keep this record with the combined export. It explains why the rows belong together and which totals remain meaningful, instead of asking a later reviewer to trust a spreadsheet simply because it opens without an error.

This guide describes the built-in Web Reader and Keyword Density behavior verified for this publication. Available data depends on the page response; these tools do not measure visits or search rankings.