# Carto Composite Scoring

> Guides the user through building composite score workflows when they ask about composite scores, indexes, multi-variable scores, ranking areas, site scoring, market potential, resilience indexes, risk indexes, weighted scores, PCA, or supervised/unsupervised scoring.

- **Type:** Skill
- **Install:** `agentstack add skill-cartodb-agent-skills-carto-composite-scoring`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [CartoDB](https://agentstack.voostack.com/s/cartodb)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [CartoDB](https://github.com/CartoDB)
- **Source:** https://github.com/CartoDB/agent-skills/tree/master/skills/carto-composite-scoring

## Install

```sh
agentstack add skill-cartodb-agent-skills-carto-composite-scoring
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

Use this skill whenever the user wants to create a composite score, index, or multi-variable ranking in a CARTO Workflow.

**Prerequisites**: Load `carto-create-workflow` for the development process.

## Instructions

### Step 1: Determine the scoring approach

Ask the user the following decision tree:

1. **"Do you have a target/outcome variable?"** (e.g. revenue, sales, crime rate)
   - Yes → **Supervised** method using `native.spatialcompositesupervised`
2. **"No target, but do you have expert knowledge of variable importance?"** (i.e. custom weights)
   - **2a. Transparency matters more than node count** → `native.normalize` per variable + `native.selectexpression` with the weighted-sum literally in SQL (`a_norm * 0.5 + b_norm * 0.2 + c_norm * 0.3`). Inputs and weights are inspectable inline in Workflows; each normalized column is materialized; no Analytics Toolbox dependency. Use when stakeholders need to see/audit the formula, or when you're not running on AT.
   - **2b. Want the AT pipeline (scaling, aggregation, bucketing) handled for you** → `native.spatialcompositeunsupervised` with `scoring_method: CUSTOM_WEIGHTS`. One node, but the formula is hidden inside the component.
3. **"No target, no weights?"**
   - → **Unsupervised** method with `ENTROPY` or `FIRST_PC` using `native.spatialcompositeunsupervised`

**Success**: You have identified which component and scoring method to use before designing the pipeline.

### Step 2: Build the pipeline

#### Supervised pipeline (`native.spatialcompositesupervised`)

1. Load the spatial features dataset (pre-indexed at H3 or Quadbin)
2. Load the target/outcome dataset
3. Join both datasets on the spatial index column
4. Select only the relevant feature columns (drop spatial index column and geometry from feature selection — pass only actual feature variables)
5. Run `native.spatialcompositesupervised` with parameters:
   - `model_type`: `LINEAR_REG`
   - `bucketize`: `EQUAL_INTERVALS_ZERO_CENTERED`
   - `n_buckets`: `5`
   - `outlier_removal`: `true`
   - `r_squared_threshold`: `0.4`
6. Output: composite score based on regression residuals (identifies areas that over/under-perform relative to the model)

**Success**: The workflow joins features with the target variable, selects only numeric feature columns, and produces a residual-based score per spatial cell.

#### Unsupervised pipeline (`native.spatialcompositeunsupervised`)

1. Load the spatial features dataset
2. Select only the relevant feature columns
3. Encode any categorical/ordinal variables to numeric using `native.casewhen` (e.g. "Low_density_urban" → 4, "High_density_urban" → 2)
4. Optionally reverse variables where higher = worse by multiplying by -1 in the SELECT query passed to the component
5. Run `native.spatialcompositeunsupervised` with parameters:
   - `scoring_method`: `CUSTOM_WEIGHTS` / `ENTROPY` / `FIRST_PC`
   - `weights`: required if `CUSTOM_WEIGHTS`. **Wire format is a JSON-encoded string of triples `[[, , ], ...]`** — `column` is the feature column name, `weight` is a numeric weight (normalized internally to sum to 1), `reverse` is a boolean (`true` flips the variable so higher = worse becomes higher = better without needing a CASE WHEN). Example: `'[["population_density", 0.5, false], ["accident_rate", 0.5, true]]'`. **Not** an object map.
   - `scaling`: `RANKING`
   - `aggregation`: `LINEAR`
   - `output`: `RETURN_RANGE` with range `[0, 1]`
6. Output: composite index score per location

**Success**: All input variables are numeric, variable directions are aligned (higher = better for the score), and the output is a normalized score per spatial cell.

## Gotchas

- **Provider casing & SQL dialect.** This skill uses lowercase column names (BigQuery / Databricks / Postgres / Redshift convention). On Snowflake, unquoted identifiers surface UPPERCASE — reference `POPULATION_DENSITY`, `ACCIDENT_RATE`, etc. in weights, expressions, and downstream SQL. See `carto-create-workflow/references/providers/.md` for casing rules and SQL dialect equivalents.
- **All input variables must be numeric.** Ordinal strings (e.g. "low"/"medium"/"high") must be manually encoded via CASE WHEN before passing to the component.
- **Variable direction matters.** If "higher is worse" for a variable, multiply by -1 before scoring. Forgetting this inverts the score meaning.
- **Supervised R-squared threshold** (default 0.4) is permissive. If model fit is poor, the residual-based score is mostly noise. Inspect model diagnostics.
- **Custom weights are normalized internally** to sum to 1. The absolute values do not matter, only the ratios.
- **Supervised scores are residuals**, not raw values. The score identifies areas that DEVIATE from the model, not areas with the highest raw values.
- **Drop the spatial index column and geometry** from the feature selection — only pass actual feature variables to the scoring component.
- **`weights` type label is misreported by the CLI.** `carto workflows components get native.spatialcompositeunsupervised --json` lists `weights` as `type: "ColumnNumber"`, but the actual wire shape is a `ColumnAndNumber` JSON-encoded string of triples `[[, , ], ...]` (see Step 2 of the unsupervised pipeline above). Trust this skill and the reference templates over the CLI's reported type label for this input.
- **`indexcol.allowedColumns` is a UI hint, not a validator.** The schema lists `["geoid", "h3", "quadbin"]` for `native.spatialcompositeunsupervised.indexcol`, but it's a Workflows dropdown hint — any unique identifier column works at runtime (e.g. `store_id`, `cell_id`).

## Reference Templates

Both examples use Milan spatial features data at Quadbin resolution 18.

| Template | Component | File |
|---|---|---|
| Supervised — Identifying resilient neighbourhoods | `native.spatialcompositesupervised` | [composite-score-supervised.json](composite-score-supervised.json) |
| Unsupervised — Market potential scoring | `native.spatialcompositeunsupervised` | [composite-score-unsupervised.json](composite-score-unsupervised.json) |

## Common Variations

| Variation | Approach |
|---|---|
| Risk index (flood, crime, etc.) | Unsupervised with `CUSTOM_WEIGHTS`; reverse variables where higher = safer |
| Market potential / site scoring | Unsupervised with `CUSTOM_WEIGHTS` or `ENTROPY`; weight demand-side variables higher |
| Resilience index | Supervised with outcome variable (e.g. revenue change); residuals reveal over/under-performers |
| Data-driven index (no domain expertise) | Unsupervised with `FIRST_PC` or `ENTROPY` to let variance drive the weights |
| Human development / composite indicator | Unsupervised with `CUSTOM_WEIGHTS` and `RANKING` scaling for ordinal-safe aggregation |

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [CartoDB](https://github.com/CartoDB)
- **Source:** [CartoDB/agent-skills](https://github.com/CartoDB/agent-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-cartodb-agent-skills-carto-composite-scoring
- Seller: https://agentstack.voostack.com/s/cartodb
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.