Analyse DropCast user data — score users for PlotLink Writer/Reader targeting

[realproject7]

Purpose

We have 8,723 Farcaster users from the DropCast database. We want to score and tag each user for PlotLink pre/launch marketing targeting — identifying potential Writers (content creators) and Readers (traders/consumers).

Setup

1. Add `/archive` to `.gitignore`

Add `archive/` to PlotLink's `.gitignore` so the CSV and scored output are never committed.

2. Input file

`plotlink/archive/dropcast-user-table-20260330.csv` — 8,723 rows with 36 columns including:

• Social signals: `follower_count`, `following_count`, `neynar_score`, `power_badge`, `is_pro_subscriber`
• X/Twitter: `twitter`, `x_followers_count`, `x_following_count`, `x_verified`
• Reputation: `quotient_score`, `quotient_rank`
• Profile: `bio`, `url`, `location`, `github`
• Spam: `spam_label`, `is_blacklisted`

Scoring Requirements

User Score (0-100)

Design a composite score using available signals. Suggested weighting (T3 can refine):

Social reach (40%):

• `follower_count` — log-scaled, higher = more reach
• `x_followers_count` — log-scaled, cross-platform presence
• `x_verified` — bonus for verified accounts

Reputation (30%):

• `neynar_score` — Farcaster social quality (0-1)
• `quotient_score` — on-chain reputation
• `power_badge` — Farcaster power user indicator
• `is_pro_subscriber` — committed Farcaster user

Profile completeness (15%):

• Has `bio` — shows engagement
• Has `twitter` — cross-platform presence
• Has `url` — has a web presence
• Has `pfp_url` — basic profile setup

Negative signals (15% penalty):

• `spam_label > 0` — reduce score
• `is_blacklisted = true` — score = 0
• `follower_count = 0` and `following_count = 0` — likely inactive/bot

User Tag — "Writer", "Reader", or "None"

Classify each user based on signals:

Writer indicators:

• Has substantial `bio` (suggests creative expression)
• High follower count relative to following (content creator pattern)
• Has `github` or `url` (builder/creator signals)
• Higher Neynar/Quotient scores (established identity)
• Keywords in bio suggesting writing/creative (nice-to-have, not required)

Reader indicators:

• Active account (some followers/following)
• Reasonable Neynar score (not spam)
• Has wallet addresses (can trade)
• Moderate engagement but not necessarily creator pattern

None:

• `is_blacklisted = true`
• `spam_label > 0`
• Zero followers AND zero following
• No profile data at all (no bio, no pfp, no twitter)
• Score below a threshold (e.g., < 10)

Output

Create a new file: `plotlink/archive/dropcast-users-scored-20260330.csv`

Same columns as input PLUS:

• `plotlink_score` — integer 0-100
• `plotlink_tag` — "Writer", "Reader", or "None"

Sort by `plotlink_score` descending (highest value users first).

Also output a summary to the ticket comments:

• Total users by tag: X Writers, Y Readers, Z None
• Top 20 highest-scored users (username, score, tag, follower count, bio snippet)
• Score distribution (how many in 80-100, 60-80, 40-60, 20-40, 0-20)

Files to modify

• `.gitignore` — add `archive/`
• New: `plotlink/archive/dropcast-users-scored-20260330.csv`

Branch

`task/645-user-scoring`

Acceptance criteria

• `archive/` added to `.gitignore`
• Scored CSV generated with `plotlink_score` and `plotlink_tag` columns
• Sorted by score descending
• Summary stats reported in ticket comments
• Blacklisted/spam users tagged as "None" with score 0
• Build passes (no archive files committed)

---

Self-Verification (T3)

• Verify archive/ is in .gitignore — git status should not show archive files
• Open the scored CSV — verify plotlink_score column exists (integer 0-100)
• Verify plotlink_tag column exists with values: Writer, Reader, or None
• Check row count matches input (8,723 rows + header)
• Verify sorted by plotlink_score descending
• Spot-check: blacklisted users have score 0 and tag None
• Spot-check: top 10 users have reasonable scores and tags
• Post summary stats (tag counts, score distribution, top 20) as ticket comment
• Run npm run build — no errors

[realproject7]

Scoring Results

Tag Distribution

Tag	Count	%
Writer	88	1.2%
Reader	2,290	31.2%
None	4,957	67.6%
Total	7,335

Note: 7,335 logical rows (CSV has 8,723 lines due to multiline bio fields).

Score Distribution

Range	Count
80-100	0
60-79	0
40-59	186
20-39	2,405
0-19	4,744

Scores cluster in 0-39 range. Top scores are ~50-57 — this user base skews toward moderate engagement (no mega-influencers).

Top 20 Users

#	Username	Score	Tag	Followers	Bio
1	mioku.base.eth	57	Writer	1,310	Developer, Ambassador
2	arifu.eth	53	Writer	1,684	/base builder, /recaster builder
3	saiful-islam	52	Writer	6,376	I love base
4	parishilton	52	Reader	1,801	(impersonator account)
5	mrarghyaa	51	Writer	826	DAOs, ML
6	0xmoti.eth	51	Reader	1,291	Hunt Town
7	warpcast-com	50	Writer	1,731	Base builder
8	mocadao	50	Reader	855	Web3/NFT enthusiast
9	druxamb	50	Writer	411	Builder @AroundTheWorld
10	royble.eth	49	Writer	459	Web3 Builder
11	afrochicks	48	None	18,024	Athlete (spam-flagged)
12	kryptocockcoin	48	Writer	593	Meme token project
13	kzl27.eth	48	Writer	1,537	Beatmaker, degen
14	anjelique	48	Reader	847	WEB3
15	presdency.eth	48	Writer	2,291	Building @Praxis-ai
16	sharkfacts.eth	47	Writer	370	AI agents for crypto trades
17	lizajovan	47	Reader	1,192	—
18	jave5672e	47	Writer	721	Artist/Designer, NFT creator
19	push-	47	None	7,790	3D artist (spam-flagged)
20	findmeows.base.eth	47	Writer	1,636	Digital art, Writer, AI & Music

Scoring Method

• Social reach (40%): FC followers (log-scaled to 100k) + X followers (log-scaled to 500k) + X verified bonus
• Reputation (30%): Neynar score + Quotient score + power badge + pro subscriber
• Profile completeness (15%): bio + twitter + url + pfp
• Spam penalty (-15): Any non-empty spam_label
• Writer tag: follower/following ratio > 2, has github/url, long bio, writer keywords, high neynar score (3+ signals)
• None tag: blacklisted, spam-flagged, zero engagement, or score < 10

Output: archive/dropcast-users-scored-20260330.csv (gitignored, local only)