Let's talk about a project that tends to collect dust on every RevOps team's roadmap - building a solid ICP analysis and account scoring system. You know exactly what I'm talking about: digging through customer data, trying to get your sales leaders to reach consensus on what "good" looks like, and then figuring out how to turn all of that into something actionable. I've been there more times than I can count, but here's where this story gets interesting - I just built our entire ICP analysis and account scoring system in a single afternoon using Census's new GPT columns feature. One afternoon. The same time it would normally take just to pull together the initial customer data set. See it in action: If you are more of a video person then a reader, take a look at this quick guide on creating account fit score and ICP analysis using LLM. 👇 For the readers, continue on: Let's be real about what building an ICP scoring system usually involves:What's so difficult about fit score and ICP analysis, and how can AI help?
- Digging through customer data to find what actually matters - from industry segments to tech stacks
- Defining success metrics that go deeper than "they signed a contract"
- Getting your sales leaders to agree on scoring criteria (always an adventure)
- Building something that actually works and doesn't require a PhD to maintain
- Making it show up in your CRM where people will actually use it
- And probably a few other things I'm forgetting because these projects tend to grow arms and legs
It's a complicated process that takes a lot of up-front investment, and a lot of maintenance to keep working. Not to mention the work that goes in to getting sales reps to adopt the new tool and integrate it into their work.
The Solution: Census GPT Columns
Here’s how you can set it up to transform your account strategy.
Step 1: Set Up Your ICP Analysis
To start, you’ll create an ICP analysis in Census that pulls together customer traits, success metrics, and account data. This analysis will help identify which traits predict high-value outcomes for your business.
- Define Key Attributes: Determine which characteristics are meaningful for your ICP—these could include metrics like high LTV, short sales cycles, industry fit, or even specific investor backing.
- Create a JSON Array: Aggregate this data into a JSON format, so all relevant information is captured in a single field per customer. This will make it easy to use in a GPT prompt later.
This ICP analysis forms the benchmark that each new account will be scored against. Here's my sql:
WITH account_details AS ( SELECT id, -- traits annual_revenue_range, country, employee_size_range, funding_range, high_confidence_tech, industry, investor, medium_confidence_tech, relevant_job_titles, -- success metrics arr, ever_customer, first_opportunities_date, is_customer, ltv, months_as_customer, weeks_to_convert FROM census_on_census.production.accounts WHERE is_customer = 'True' OR (first_opportunities_date BETWEEN DATEADD(day, -180, CURRENT_DATE) AND DATEADD(day, -90, CURRENT_DATE))),customer_traits AS ( SELECT 1 AS join_key, ARRAY_AGG( OBJECT_CONSTRUCT( 'annual_revenue_range', annual_revenue_range, 'arr', arr, 'country', country, 'employee_size_range', employee_size_range, 'ever_customer', ever_customer, 'first_opportunities_date', first_opportunities_date, 'funding_range', funding_range, 'high_confidence_tech', high_confidence_tech, 'industry', industry, 'investor', investor, 'is_customer', is_customer, 'ltv', ltv, 'medium_confidence_tech', medium_confidence_tech, 'months_as_customer', months_as_customer, 'relevant_job_titles', relevant_job_titles, 'weeks_to_convert', weeks_to_convert ) ORDER BY id ASC ) AS customer_traits FROM account_details GROUP BY 1)SELECT * FROM customer_traits
Step 2: Write Your Fit Score or ICP Analysis GPT Prompt
With your ICP analysis ready, it’s time to create a GPT prompt that will evaluate each account’s fit score based on your criteria. The public version of the prompt Census uses for the ICP analysis is below.
- Customize Success Indicators: Your prompt can include attributes that align with your ICP, like revenue thresholds, employee count, or specific technologies. Each indicator can be weighted based on how strongly it correlates with success for your business.
- Generate a Fit Score: Set up a scoring system that works for your team (e.g., 0-10), with an explanation for each score. This ensures transparency, so the sales team knows exactly why an account received its rating.
With GPT Columns, you’ll receive an ICP analysis output that ranks each account against these success indicators, creating an ideal customer profile that is custom-tailored to your needs.
Prompt 1:
Using { {record['CUSTOMER_TRAITS'] } }
, perform an Ideal Customer Profile (ICP) analysis using regression. List positive and negative traits with their correlation strengths (strong, moderate, weak), noting data gaps without assuming negatives. Identify valuable traits exclusively based on regression analysis, with category weights summing to 100% for LLM scoring.
Success Metrics
Use these metrics to assess success, loyalty, and conversion:
- ARR: Annual recurring revenue, indicating customer value.
- ever_customer: If the account has been a customer.
- is_customer: Current active customer status.
- LTV: Expected revenue per customer.
- months_as_customer: Duration as an active customer.
- weeks_to_convert: Time from first interaction to conversion.
Categories (with weights)
- Technology (30%):
- Identify high/medium confidence tech with success correlations.
- Industry (15%):
- Highlight industries with positive/negative success correlations.
- Annual Revenue Range (10%):
- List revenue ranges correlating with success.
- Employee Size Range (15%):
- Specify employee count ranges with success alignment.
- Geographic Fit (10%):
- Identify country or regional correlations.
- Investors (10%):
- Highlight investor partnerships with positive or negative impacts.
- Hiring Trends (10%):
- Analyze relevant job titles and hiring volume; exclude negative indicators.
Final Output: Structure findings with headings, correlation strengths, and category weights. Note data gaps and additional traits from regression analysis for scoring and lead comparison.
Step 3: Test the Account Fit Score with Familiar Accounts
Testing helps validate the accuracy of your scoring. Start with accounts you know well:
- Select a Sample Set: Pick a handful of accounts to evaluate. This initial group lets you quickly see if the scores align with expectations.
- Fine-Tune as Needed: Adjust the prompt based on these test results until the fit scores reliably match your ICP benchmarks.
This initial testing step is crucial for fine-tuning the prompt to ensure accurate, consistent scoring.
Step 4: Score New Accounts and Customize the Prompt for Sales
With your ICP analysis in place and tested, you’re ready to score new accounts. Use your account fit score prompt to get a score for every prospect and existing customer. Here’s a public version of Census’s account fit prompt.
- Run Your Prompt: The prompt will now evaluate each new account, comparing it to your ICP benchmark.
- Provide Clear Explanations: Use a “stoplight” system (e.g., green for good fit, yellow for moderate, red for low) to make it easy for the sales team to interpret scores at a glance.
- Add Sample Summaries: Include example summaries to show the sales team how the fit score output should look, so they can quickly absorb the key insights.
This structure ensures that each account is scored and summarized in a way that’s actionable and easy to understand.
Prompt 2:
Evaluate the account fit score for { { record['COMPANY_NAME'] } } based on alignment with our Ideal Customer Profile (ICP).
Step 1: Review ICP Analysis
Reference the ICP Analysis: { { record['ICP_ANALYSIS'] } }
.
Step 2: Assess Key Traits
Consider the following:
- 🖥️ Technologies: { { record['HIGH_CONFIDENCE_TECH'] } }
- 🌍 Country: { { record['COUNTRY'] } }
- 🏢 Industry: { { record['INDUSTRY'] } }
- 💰 Annual Revenue: { { record['ANNUAL_REVENUE_RANGE'] } }
- 👥 Employee Size: { { record['EMPLOYEE_SIZE_RANGE'] } }
- 📈 Funding: { { record['FUNDING_RANGE'] } }
- 📋 Relevant Roles: { { record['RELEVANT_JOB_TITLES'] } }
- 💼 Investor: { { record['INVESTOR'] } }
Step 3: Score Categories (Stoplight System)
Assign a stoplight color for each category based on ICP fit:
- 🖥️ Technology Fit (30%)
- 🟢 Green: 6+ high-confidence tech matches or 8+ medium-confidence tech matches.
- 🟡 Yellow: 3-5 high-confidence tech matches or 6-7 medium-confidence tech matches.
- 🔴 Red: Fewer than 3 high-confidence tech matches or fewer than 6 medium-confidence tech matches.
- 🌍 Country Fit (10%)
- 🟢 Green: Matches high-priority ICP countries.
- 🟡 Yellow: Neutral country.
- 🔴 Red: Negative match.
- 🏢 Industry Fit (15%)
- 🟢 Green: Full ICP industry match.
- 🟡 Yellow: Partial overlap with ICP.
- 🔴 Red: No industry match.
- 👥 Employee Size Fit (15%)
- 🟢 Green: Matches typical ICP employee size.
- 🟡 Yellow: Close to typical ICP range.
- 🔴 Red: Outside ICP size range.
- 💰 Annual Revenue Fit (10%)
- 🟢 Green: Matches ICP revenue.
- 🟡 Yellow: Slight deviation (5-10%).
- 🔴 Red: Significant deviation.
- 💼 Investor Fit (10%)
- 🟢 Green: Matches top ICP investors (e.g., Sequoia).
- 🟡 Yellow: Overlap with other investors.
- 🔴 Red: No investor match.
- 📋 Relevant Job Matches (5%)
- 🟢 Green: Relevant roles in “Data” or “Marketing Operations.”
- 🟡 Yellow: Other relevant roles or 10+ job openings.
- 🔴 Red: No relevant roles.
Step 4: Calculate Fit Score (0-10)
- 8-10: High fit
- 6-7: Strong fit
- 4-5: Moderate fit
- Below 4: Low fit
Summary & Talking Points (for scores 6+)
If the prospect scores 6 or higher, use the following summary:
Account Fit Score Summary:
- 🔥 Fit Score: 7/10
- 🟢 Technology: { { record['HIGH_CONFIDENCE_TECH'] } } – aligned with our tech stack.
- 🟢 Country: { { record['COUNTRY'] } } – matches key ICP region.
- 🟡 Industry: { { record['INDUSTRY'] } } – partial overlap with ICP industries.
- 🔴 Open Roles: No relevant data/operations openings.
- 🟢 Investor: { { record['INVESTOR'] } } for a warm introduction.
Talking Points for High Fit (Score 6+):
- 🖥️ Tech Alignment: { { record['HIGH_CONFIDENCE_TECH'] } } aligns with our customer tech.
- 📋 Hiring: Relevant roles like { { record['RELEVANT_JOB_TITLES'] } } indicate investment in data/ops.
- 💼 Investor Connection: { { record['INVESTOR'] } } enables strategic introductions.
Step 5: Sync Data to Salesforce or Hubspot
Once your account scores and summaries are ready, set up a data sync to Salesforce (or your preferred CRM) for seamless integration with your sales process. This makes sure that your account fit data is always up-to-date and readily available where your sales team needs it most.
And that’s it! You’ve just set up an automated, data-driven ICP analysis and scoring system with GPT Columns that brings valuable insights right to your CRM.
Ready to Dive In?
What used to be a weeks-long project involving multiple teams and complex implementation can now be accomplished in an afternoon using Census GPT columns. This approach not only saves time but also provides more nuanced, AI-driven insights that can be easily updated and maintained.
The best part? As your business evolves, you can quickly iterate on your ICP analysis and scoring criteria by adjusting the prompts, making this a truly agile solution for modern sales organizations.
Whether you’re refining your ICP, prioritizing accounts, or automating manual tasks, Census’s GPT Columns gives you the tools to drive results with ease. Curious to learn more? Check out our documentation or get in touch—we’d love to help you supercharge your account strategy!