# Join analytics exports without multiplying the posts behind your totals

Original constructed inputs with actual declared local checks. No real accounts, readers, customers, provider benchmark or general security certification.

Write down what one row means in each file and what one output row should mean before joining them. A matching key can legitimately produce several output rows. Preserve source totals and audit multiplicity, unmatched records and scope before calculating a combined report. A familiar engagement rate can remain unchanged even when the joined totals are wrong.

## State the unit on both sides before choosing the key

Suppose one file contains one 24-hour observation per post and another assigns editorial tags. A post can have several tags, so the second file is one row per post–tag pair. Joining them creates a useful tag-level view, but it changes the row unit. Write “one post–tag match, plus any retained unmatched post” beside the output instead of continuing to call every row a post.

Establish the account, observation window, observation age and field meanings for both files. An exact post key inside one export is not a license to mix unrelated accounts or a current lifetime observation with an earlier 24-hour value. Where the intended unit needs several fields, declare the compound key. Missing scope stays unresolved; do not infer a relationship from a similar name, title or position.

## Check multiplicity and the unmatched-row rule

PostgreSQL’s qualified-join definition creates a result for each matching pair. Its left-join definition also retains unmatched left rows with nulls on the other side. These are useful definitions for the audit, not a claim that we ran a PostgreSQL database here. The supplied exercise implements the declared relationship in local Python with original constructed files.

Audit the key frequency on each side before the merge. If the declared source unit is one observation per post, duplicate source keys need investigation. Several tag rows may be valid. Choose whether unmatched source rows should remain and record what the missing enrichment means. A missing tag is unavailable classification; it does not change an observed zero impression count into a missing impression value.

1. Name the source units, scope and intended output unit.
2. Check missing and repeated keys against each declared relationship.
3. Choose explicit matching fields and an unmatched-row rule.
4. Keep source row counts, distinct keys and metric totals before the join.
5. Inspect output multiplicity and unmatched keys before aggregating the result.

## Follow the entire three-post input

Our constructed source has P01 with 100 impressions and 5 response events, P02 with 200 and 10, and P03 with 0 and 0. All three observations have an age of 24 hours. The tag file contains P01/A, P01/B and P02/A. These are arbitrary exercise tags and opaque keys, not actual LinkedIn accounts or a population benchmark.

The source totals are 300 impressions and 15 response events across three post keys. A left join yields four rows because P01 matches two tags; P03 remains with an unmatched tag. Naively summing that joined table gives 400 impressions and 20 response events. Both ratios are 5%, so checking the percentage alone misses the inflated counts.

| Output key | Tag state | Impressions | Response events |
| --- | --- | --- | --- |
| P01 | A; matched | 100 | 5 |
| P01 | B; matched | 100 | 5 |
| P02 | A; matched | 200 | 10 |
| P03 | Unmatched | 0 | 0 |

Complete original constructed input and actual local computation; no real post performance is represented.

## Aggregate at the unit the question actually needs

For the original post total, return to the one-row-per-post source or form an explicitly validated post-level selection. For the total of posts with tag A, the matched original post set is P01 and P02: 300 impressions. Tag B has P01: 100. Adding those tag totals yields 400 because the groups overlap. That sum does not become a new all-post total or a distinct-audience count.

Do not solve a bad join by silently dropping rows after the fact. If two rows have the same post key but disagree on age or value, choosing the first row hides the unresolved observation rule. If repeated tags are duplicates, document that separate correction. Preserve the original files and the reason for each transformation so the report can be recomputed from the intended source.

| Question | Declared calculation | Exercise result |
| --- | --- | --- |
| All source posts | Sum the validated post source | 3 posts; 300 impressions; 15 events |
| Rows after tag enrichment | Count every matched/retained output row | 4 rows; not 4 distinct posts |
| Tag A posts | Distinct matched source post set | P01 and P02; 300 impressions |
| Tag B posts | Distinct matched source post set | P01; 100 impressions |
| A total plus B total | Overlapping category sum | 400; not the all-post total |

Post-level keys do not identify unique people reached.

## Resolve three different join problems

J1 — Legitimate one-to-many tags: retain P01’s two tag relationships for tag analysis, and retain the 300-impression source total for the all-post question. Store the lineage from each output row to its source key. The observed rate agreement does not certify the join; the output’s extra row is explainable only under its declared unit.

J2 — Conflicting post observations: a second P01 row appears in a file declared to contain one 24-hour observation per post. Stop that contract check. Inspect whether it is an accidental duplicate or a different snapshot requiring a time field or an explicit selection. Do not sum, merge or discard a conflicting value merely to recover a familiar total.

J3 — Missing enrichment or scope: an inner join removes P03 because no tag matches; the left join retains it with an unavailable tag and an observed zero. Separately, the supplied compound-key check leaves a constructed different-owner record unmatched. Neither result proves anything about a real exporter’s identity or scope; missing metadata in your actual files still needs a source check.

## Reproduce the bounded result and keep a useful join record

The packet includes both complete CSVs, the four-row lineage, the protocol and the Python checker. With a suitable Python already installed, run python3 check-joins.py in the extracted exercise folder. It reads only the bundled constructed inputs, computes the relationship and compares the saved lineage without overwriting it. Duplicate/blank-key guards and a constructed compound-key mismatch are also checked. No database, network call or account access is needed.

We ran the code locally on 1 November 2026. Its observed result is three source keys, four joined rows, 300 versus 400 impressions and 15 versus 20 events, with 5% in both calculations. This demonstrates this deliberately selected failure case, not its prevalence in real exports, tool superiority, reader success or customer value. The private worksheet records file references, units, scope, key relationship, unmatched policy, source/output totals and unresolved conflicts; do not put actual private exports into the public packet.

## Sources and actual result files

- [PostgreSQL: qualified joins, matching rows and unmatched outer-join rows](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-JOIN)
- [Python 3.14: CSV dictionaries, headers and newline handling](https://docs.python.org/3.14/library/csv.html)
- [Original controlled join inputs, method and observed results](https://wrapped.li/blog-assets/data-recovery/observed-workflow-results.json)