i360 Data Loading Guide¶

This document describes how to load i360-formatted voter data into CampaignBrain.

Overview¶

The scripts/load_i360_csv.py script loads i360 voter export data from CSV files into the CampaignBrain database. It automatically: - Maps standard fields to the person table - Creates custom field definitions for i360-specific fields - Stores additional voter data as custom field values - Handles data cleaning and validation

Quick Start¶

# Load data (append to existing)
python scripts/load_i360_csv.py ./assets/ky_race_20251113.csv

# Load data (clear existing first)
python scripts/load_i360_csv.py ./assets/ky_race_20251113.csv --clear

# Specify custom database path
python scripts/load_i360_csv.py ./assets/data.csv --db /path/to/db.db

Field Mappings¶

Direct Person Table Mappings¶

These CSV fields map directly to the person table:

CSV Field	Database Field	Description
`FirstName`	`first_name`	First name
`LastName`	`last_name`	Last name
`PrimaryAddress1`	`address1`	Primary street address
`PrimaryCity`	`city`	City
`PrimaryState`	`state`	State (2-letter code)
`PrimaryZip`	`zip`	ZIP code (5-digit)
`CountyName`	`county`	County name
`CDName`	`congressional_district`	Congressional district
`PrimaryPhone`	`home_phone`	Primary phone number

Custom Field Mappings¶

These CSV fields are stored as custom fields:

Name Fields¶

MiddleName → Middle Name
SuffixName → Suffix (Jr, Sr, III, etc.)
HHName → Household Name

Address Details¶

PrimaryZip4 → Primary ZIP+4 (4-digit ZIP extension)
PrimaryUnit → Primary Unit Type (APT, STE, etc.)
PrimaryUnitNumber → Primary Unit Number

Secondary Address¶

SecondaryAddress1 → Secondary Address
SecondaryCity → Secondary City
SecondaryState → Secondary State
SecondaryZip → Secondary ZIP
SecondaryZip4 → Secondary ZIP+4
SecondaryUnit → Secondary Unit Type
SecondaryUnitNumber → Secondary Unit Number

Political Data¶

ObservedParty → Observed Party
OfficialParty → Official Party (voter registration)
CalculatedParty → Calculated Party (i360 party score)
HouseholdParty → Household Party
LDName → Legislative District (state house)
SDName → State Senate District

Voter Identification¶

VoterKey → Voter Key (i360 unique identifier)
HHRecId → Household Record ID
StateVoterId → State Voter ID (state registration ID)
ClientId → Client ID
Moved → Moved Flag (has voter moved?)

USPS Mail Data¶

MailSortCodeRoute → Mail Sort Code Route
MailDeliveryPt → Mail Delivery Point
MailDeliveryPtChkDigit → Mail Delivery Check Digit
MailLineOfTravel → Mail Line of Travel
MailLineOfTravelOrder → Mail Line of Travel Order

Geographic Data¶

NeighborhoodId → Neighborhood ID
NeighborhoodSegmentId → Neighborhood Segment ID
RegAddressId → Registration Address ID

Data Loaded¶

Using the Kentucky race data (ky_race_20251113.csv):

Total Persons: 81,232
Custom Fields Defined: 32
Custom Field Values: 2,030,893

Custom Field Coverage¶

Field	Records	Coverage
Household Party	81,232	100%
Client ID	81,232	100%
Voter Key	81,232	100%
Calculated Party	81,232	100%
Official Party	81,232	100%
State Voter ID	81,232	100%
Legislative District	81,232	100%
State Senate District	81,232	100%
Moved Flag	81,232	100%
Primary ZIP+4	80,409	99%
Secondary Address	79,713	98%
Middle Name	78,578	97%
Primary Unit Type	3,430	4%
Secondary Unit Type	3,392	4%
Mail Line of Travel	856	1%

Usage Examples¶

Load Fresh Data¶

# Clear all existing persons and load new data
python scripts/load_i360_csv.py ./assets/ky_race_20251113.csv --clear

Append to Existing Data¶

# Add to existing person records
python scripts/load_i360_csv.py ./assets/new_voters.csv

Verify Loaded Data¶

# Check person count
python -c "
import duckdb
conn = duckdb.connect('db/pocket.db')
print(f'Total persons: {conn.execute(\"SELECT COUNT(*) FROM person\").fetchone()[0]:,}')
conn.close()
"

Query Custom Fields¶

-- Get all persons with their party affiliation
SELECT
    p.first_name,
    p.last_name,
    p.city,
    pcf.value as party
FROM person p
LEFT JOIN person_custom_field pcf ON p.id = pcf.person_id
LEFT JOIN custom_field cf ON pcf.custom_field_id = cf.id
WHERE cf.id = 'official_party'
LIMIT 10;

-- Count persons by calculated party score
SELECT
    pcf.value as party_score,
    COUNT(*) as count
FROM person_custom_field pcf
WHERE pcf.custom_field_id = 'calculated_party'
GROUP BY pcf.value
ORDER BY count DESC;

Access via API¶

Once data is loaded, it's accessible via the CampaignBrain API:

# Get person with custom fields
curl http://localhost:31314/api/persons/{person_id}

# Search persons
curl http://localhost:31314/api/persons?city=Vanceburg&state=KY

Data Notes¶

Phone Numbers¶

PrimaryPhone is loaded into home_phone (typically landline)
No cell phone or email data in standard i360 exports
These can be added later via the UI or API

Addresses¶

Primary address is always loaded into main person fields
Secondary addresses (mailing, alternate) stored as custom fields
Full USPS delivery point data preserved for mail campaigns

Party Affiliation¶

Official Party: Voter registration party (from state records)
Calculated Party: i360's proprietary party score (1-7 scale)
Observed Party: Historical voting behavior
Household Party: Household-level party tendency

Voter IDs¶

Voter Key: i360's unique identifier (use for de-duplication)
State Voter ID: State registration number (public record)
Client ID: Client-specific tracking ID
HH Rec ID: Household record identifier

Troubleshooting¶

Script Errors¶

If the script fails:

Check CSV format:
```
head -n 1 your_file.csv
```
Verify headers match expected i360 format
Check database exists:
```
ls -lh db/pocket.db
```
Run python scripts/create_schema.py if needed
Check permissions:
```
chmod +x scripts/load_i360_csv.py
```

Missing Fields¶

If some fields aren't loading: - Check CSV column names match exactly (case-sensitive) - Verify data isn't all empty/null - Check script logs for warnings

Performance¶

For large files (100K+ records): - Loading may take several minutes - DuckDB handles this efficiently - Monitor progress via script output

Next Steps¶

After loading data:

Verify in UI:
Start app: ./startit.sh
Visit: http://localhost:31313
Browse persons, search, filter
Create Tags:
Tag voters by party, district, etc.
Use custom fields for filtering
Create Segments:
Save common searches
Build target audiences
Plan Events:
Create campaign events
Invite specific voter segments

Script Details¶

Location: scripts/load_i360_csv.py

Key Functions: - setup_custom_fields() - Creates custom field definitions - load_i360_csv() - Main loading function

Options: - --clear - Clear existing data before loading - --db PATH - Specify database path

Error Handling: - Skips records with no name data - Logs warnings for problematic fields - Commits transaction only on success