Category: Managed AI Function (Preview)

Description

Ranks and scores inputs based on natural language criteria. Returns a FLOAT64 value representing the score. BigQuery automatically rewrites your prompt to create a consistent scoring rubric. Commonly used with ORDER BY clauses to rank items by quality, relevance, or other criteria.

Use Cases

Customer Feedback: Rank reviews by negativity to prioritize responses

Hiring: Score resumes by qualification for a role

Content Quality: Rank articles or images by professional quality

Lead Scoring: Prioritize sales leads by likelihood to convert

Product Ranking: Score products by appeal or relevance

Syntax

AI.SCORE(
  ('SCORING_CRITERIA', INPUT),
  [, connection_id => 'CONNECTION' ]
  [, endpoint => 'ENDPOINT' ]
)

AI.SCORE(
  ('SCORING_CRITERIA', INPUT),
  [, connection_id => 'CONNECTION' ]
  [, endpoint => 'ENDPOINT' ]
)

AI.SCORE(
  ('SCORING_CRITERIA', INPUT),
  [, connection_id => 'CONNECTION' ]
  [, endpoint => 'ENDPOINT' ]
)

Parameters

SCORING_CRITERIA: Natural language description of how to score the input

INPUT: Text data from columns OR ObjectRefRuntime values for multimodal data

CONNECTION (optional): Connection ID format: project.location.connection_id

ENDPOINT (optional): Gemini model endpoint (auto-selected if omitted)

Code Examples

Example 1: Find Most Negative Reviews

SELECT 
  review_id,
  review_text,
  AI.SCORE(
    ('Rate negativity from 1-10: ', review_text),
    connection_id => 'us.my_vertex_connection'
  ) AS negativity_score
FROM product_reviews
ORDER BY negativity_score DESC
LIMIT 5

SELECT 
  review_id,
  review_text,
  AI.SCORE(
    ('Rate negativity from 1-10: ', review_text),
    connection_id => 'us.my_vertex_connection'
  ) AS negativity_score
FROM product_reviews
ORDER BY negativity_score DESC
LIMIT 5

SELECT 
  review_id,
  review_text,
  AI.SCORE(
    ('Rate negativity from 1-10: ', review_text),
    connection_id => 'us.my_vertex_connection'
  ) AS negativity_score
FROM product_reviews
ORDER BY negativity_score DESC
LIMIT 5

Example 2: Score Resume Quality

SELECT 
  candidate_name,
  AI.SCORE(
    ('Rate qualification for senior backend engineer role from 1-10: ', resume_text),
    connection_id => 'us.my_vertex_connection'
  ) AS qualification_score
FROM job_applications
WHERE position = 'Senior Backend Engineer'
ORDER BY qualification_score DESC
LIMIT 10

SELECT 
  candidate_name,
  AI.SCORE(
    ('Rate qualification for senior backend engineer role from 1-10: ', resume_text),
    connection_id => 'us.my_vertex_connection'
  ) AS qualification_score
FROM job_applications
WHERE position = 'Senior Backend Engineer'
ORDER BY qualification_score DESC
LIMIT 10

SELECT 
  candidate_name,
  AI.SCORE(
    ('Rate qualification for senior backend engineer role from 1-10: ', resume_text),
    connection_id => 'us.my_vertex_connection'
  ) AS qualification_score
FROM job_applications
WHERE position = 'Senior Backend Engineer'
ORDER BY qualification_score DESC
LIMIT 10

Example 3: Score Image Appeal

SELECT 
  STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url) AS url,
  AI.SCORE(
    ('Rate product appeal from 1-10: ', OBJ.GET_ACCESS_URL(ref, 'r')),
    connection_id => 'us.my_vertex_connection'
  ) AS appeal_score
FROM product_images
ORDER BY appeal_score DESC
LIMIT 20

SELECT 
  STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url) AS url,
  AI.SCORE(
    ('Rate product appeal from 1-10: ', OBJ.GET_ACCESS_URL(ref, 'r')),
    connection_id => 'us.my_vertex_connection'
  ) AS appeal_score
FROM product_images
ORDER BY appeal_score DESC
LIMIT 20

SELECT 
  STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url) AS url,
  AI.SCORE(
    ('Rate product appeal from 1-10: ', OBJ.GET_ACCESS_URL(ref, 'r')),
    connection_id => 'us.my_vertex_connection'
  ) AS appeal_score
FROM product_images
ORDER BY appeal_score DESC
LIMIT 20

Example 4: Lead Scoring

SELECT 
  lead_id,
  company_name,
  AI.SCORE(
    ('Rate likelihood to purchase enterprise software from 1-100: ',
     CONCAT('Company: ', company_name, 
            ', Industry: ', industry,
            ', Size: ', employee_count,
            ', Recent activity: ', recent_activity)),
    connection_id => 'us.my_vertex_connection'
  ) AS purchase_likelihood
FROM sales_leads
WHERE status = 'active'
ORDER BY purchase_likelihood DESC
LIMIT 50

SELECT 
  lead_id,
  company_name,
  AI.SCORE(
    ('Rate likelihood to purchase enterprise software from 1-100: ',
     CONCAT('Company: ', company_name, 
            ', Industry: ', industry,
            ', Size: ', employee_count,
            ', Recent activity: ', recent_activity)),
    connection_id => 'us.my_vertex_connection'
  ) AS purchase_likelihood
FROM sales_leads
WHERE status = 'active'
ORDER BY purchase_likelihood DESC
LIMIT 50

SELECT 
  lead_id,
  company_name,
  AI.SCORE(
    ('Rate likelihood to purchase enterprise software from 1-100: ',
     CONCAT('Company: ', company_name, 
            ', Industry: ', industry,
            ', Size: ', employee_count,
            ', Recent activity: ', recent_activity)),
    connection_id => 'us.my_vertex_connection'
  ) AS purchase_likelihood
FROM sales_leads
WHERE status = 'active'
ORDER BY purchase_likelihood DESC
LIMIT 50

Example 5: Content Quality Scoring

SELECT 
  article_id,
  title,
  AI.SCORE(
    ('Rate article quality and professionalism from 1-10: ', article_text),
    connection_id => 'us.my_vertex_connection'
  ) AS quality_score,
  views,
  published_date
FROM blog_articles
ORDER BY quality_score DESC
LIMIT 25

SELECT 
  article_id,
  title,
  AI.SCORE(
    ('Rate article quality and professionalism from 1-10: ', article_text),
    connection_id => 'us.my_vertex_connection'
  ) AS quality_score,
  views,
  published_date
FROM blog_articles
ORDER BY quality_score DESC
LIMIT 25

SELECT 
  article_id,
  title,
  AI.SCORE(
    ('Rate article quality and professionalism from 1-10: ', article_text),
    connection_id => 'us.my_vertex_connection'
  ) AS quality_score,
  views,
  published_date
FROM blog_articles
ORDER BY quality_score DESC
LIMIT 25

Data Output Examples

Review Negativity Scores

Resume Qualification Scores

Best Practices

Be clear about the scale: Specify the scoring range (1-10, 1-100, etc.)

Let BigQuery optimize: The prompt is automatically rewritten for consistency

Use with ORDER BY: Designed for ranking operations

Combine with LIMIT: Get top N results efficiently

Test on samples: Validate scoring behavior before production use

When to Use

✅ Use AI.SCORE for ranking items by quality or relevance

✅ Use with ORDER BY to extract top K items

✅ Use when you need consistent scoring across rows

✅ Use for prioritization tasks

Alternatives

AI.GENERATE_DOUBLE: For custom prompts with full control

Traditional calculations: For rule-based scoring (cheaper)

AI.CLASSIFY: When you need categories instead of scores

AI.IF: When you need binary filtering

Platform Support

Regions: All Gemini-supported regions + US/EU multi-regions

Preview Status: Currently in Preview (Pre-GA)

Cost: Charged per Vertex AI API call

Optimization: Automatic prompt rewriting for scoring consistency

Returns

FLOAT64 value representing the score. Returns NULL if the Vertex AI call fails.