Three months ago I got tired of reading claims about AI and hiring that couldn't be checked. "AI is taking entry-level jobs." "Employers want AI skills." "Junior roles are disappearing." Every claim was either a headline or a vibe, and none of them came with the actual data.
So I built a spreadsheet. It's not sophisticated. It's a Google Sheet with 47 columns, 1,200 job postings, and a growing list of fields I wish I'd added before I started collecting.
I'm sharing the structure here because I think it's useful, and because I want help making it better. If you've built something similar — or if you've tried and given up because the data got messy — I want to compare notes. What fields am I missing? What am I measuring that doesn't matter? What would make this useful beyond my own curiosity?
Here's the spreadsheet, the fields, the method, and the problems I haven't solved.
Why I Built It
The trigger was a viral chart. You've probably seen a version of it — a steep line showing entry-level hiring collapsing right after ChatGPT launched. I wanted to know if the underlying data supported the claim, so I went looking for the source.
What I found was frustrating. The Stanford Canaries data is real and rigorous — payroll records from ADP covering roughly one in six American workers. It shows a 12% relative decline in employment for ages 22–25 in the most AI-exposed occupations over the 10 quarters after ChatGPT's release. That's a meaningful finding.
But the viral chart didn't show that. It showed a cliff. And when I tried to replicate the chart's claim using public data, I couldn't — because the public data sources measure different things. Lightcast measures AI skills via a 173-skill taxonomy. Indeed measures AI mentions via ~600 keywords. Revelio measures AI exposure at the activity level using an undisclosed LLM. None of them are directly comparable, and none of them are fully public.
So I decided to build my own small dataset. Not to replace the commercial providers — I can't compete with 5 billion postings. But to have a transparent, reproducible, local sample I could actually interrogate. If I collect 500 postings from my target roles and track them myself, at least I know what's in the data.
That's the goal: a small, honest, well-documented sample. Not a benchmark. Not a proxy for the national labor market. Just a transparent instrument for asking better questions.
The Spreadsheet: Current Fields
Here's what I have so far. It's organized into seven groups. I'll explain what each field is for and where I'm unsure.
Group 1: Identification
Field | Type | Purpose | Notes |
|---|---|---|---|
| Auto-increment | Unique row identifier | Simple, works fine |
| URL | Where I found it | Indeed, LinkedIn, company site |
| Categorical | Which board | Indeed, LinkedIn, company careers page, other |
| Date | When I saved it | Needed for time-series |
| Date | When employer posted it | Often missing or unreliable |
| Date | When I ran the collection | Different from date_collected if I batch-process |
Problem I'm having: I started with date_collected only, then realized I needed date_posted to track how postings age. But most boards don't show the original post date — only "30+ days ago" or "reposted 3 days ago." I've started using the date I first saw it as a proxy, but that's imprecise.
Group 2: Employer
Field | Type | Purpose | Notes |
|---|---|---|---|
| Text | Who's hiring | Standardized manually |
| Categorical | 1–50, 51–200, 201–1,000, 1,001–5,000, 5,000+ | From LinkedIn or company site |
| Categorical | NAICS-style | Manually assigned |
| Text | Headquarters | From company site |
| Boolean | Does the company mention AI on its own site? | Manual check |
| Boolean | Does the company sell an AI product? | Distinguishes buyers from sellers |
Why company_ai_signal matters: Indeed's research found that 90% of AI-related job postings come from just 1% of companies. I want to know whether the postings I'm seeing come from AI-native firms or from traditional companies adopting AI. Those are different labor market signals.
Group 3: Role
Field | Type | Purpose | Notes |
|---|---|---|---|
| Text | As written | Kept verbatim |
| Text | Normalized title | E.g., "Customer Support Specialist" |
| Categorical | Marketing, ops, support, engineering, etc. | Manually assigned |
| Categorical | Entry, mid, senior, lead, exec | Based on title + years required |
| Integer | Minimum years | From description |
| Categorical | None, HS, associate, bachelor's, master's+ | From description |
| Boolean | Does it show pay? | Increasingly common due to state laws |
| Integer | Lower bound | When listed |
| Integer | Upper bound | When listed |
| Categorical | Hourly, monthly, annual | Needed for comparison |
| Categorical | Onsite, hybrid, remote | From description |
| Text | City, state | From posting |
Problem I'm having: job_level is subjective. A "Marketing Coordinator" at one company is entry-level; at another it's mid-level. I've started cross-referencing with years_experience_required, but the two don't always agree. I'd like a more objective standard but haven't found one.
Group 4: AI Mentions
This is the core of the spreadsheet. It's also the messiest part.
Field | Type | Purpose | Notes |
|---|---|---|---|
| Boolean | Does the posting mention AI at all? | Keyword scan |
| List | Which keywords appeared | Verbatim list |
| Integer | How many distinct keywords | Crude frequency measure |
| Categorical | Required skill, preferred skill, tool mention, buzzword, responsibility | Manual classification |
| Text | Surrounding sentence(s) | Verbatim quote |
| Categorical | ML engineering, prompt writing, AI oversight, AI strategy, AI tool use, other | Manual classification |
| Boolean | Is AI central to the job? | Manual judgment |
| List | Specific tools mentioned | ChatGPT, Copilot, Midjourney, etc. |
| Percentage | Rough % of responsibilities that are AI-related | Manual estimate |
Why the classification matters: A posting that says "familiarity with AI tools a plus" is not the same as "requires 5+ years of ML engineering experience." Indeed's own analysis found that only about half of AI-related postings mentioned building new AI tools or directly using AI models. The rest were vague or aspirational.
Problem I'm having: My ai_mention_type categories overlap. A posting can require AI skills and mention specific tools and describe an AI-related responsibility. I've been forcing a single category, which loses information. I'm considering splitting it into multiple boolean flags instead.
Group 5: Task Content
This is where I'm trying to go beyond keywords and look at what the job actually involves.
Field | Type | Purpose | Notes |
|---|---|---|---|
| Text | What does the job do? | 2–3 sentence summary |
| Percentage | Rough % of routine tasks | Manual estimate |
| Percentage | Rough % of judgment tasks | Manual estimate |
| Percentage | Rough % of tasks AI could do | Manual estimate |
| Percentage | Rough % of tasks AI could assist | Manual estimate |
| Percentage | Rough % of tasks AI can't do | Manual estimate |
Why this matters: This is my attempt to apply the five-level exposure framework I wrote about. I want to see whether AI-mentioned postings are actually more routine-heavy, or whether AI mentions are orthogonal to task composition. That would be a real finding.
Problem I'm having: These percentages are gut estimates. I'm one person reading job descriptions and guessing. I've tried to anchor them by re-scoring a random sample of 30 postings twice, and my scores varied by 10–15 percentage points. Not great.
Group 6: Provenance
Field | Type | Purpose | Notes |
|---|---|---|---|
| Boolean | Did someone else check this row? | Mostly no |
| Text | What they disagreed with | Empty for now |
| Categorical | Manual, semi-automated, API | Manual for now |
| Integer (1–5) | How confident am I in this row? | Self-assessed |
Why this matters: I want the data to be auditable. Right now it isn't. I'm the only reader, and I'm the only one who knows what I meant by each classification. That's a problem.
Group 7: Source Snapshot
Field | Type | Purpose | Notes |
|---|---|---|---|
| Text | Full posting text at time of collection | For reproducibility |
| Hash | SHA-256 of the description | To detect edits |
| Boolean | Is the posting still up? | Re-checked monthly |
| Date | When it disappeared | When applicable |
| Boolean | Has the same posting appeared again? | Manual check |
Why snapshots matter: Job postings change. A company can edit a description after posting it, add a salary range, remove a requirement, or repost the same role under a new title. If I don't snapshot the description, I can't know what I actually collected.
Problem I'm having: Storing full descriptions makes the spreadsheet enormous. I'm considering moving descriptions to a separate database or file store and keeping only hashes and URLs in the main sheet. But that complicates analysis.
No feedback yet — submit the first.