# Custom metrics from Google Sheets

> Define a metric backed by a Google Sheet so rules can act on numbers Meta never sees.

Source: https://adops.id/docs/custom-metrics-google-sheets/
Last updated: 2026-08-20
Publisher: AdOps (https://adops.id) — an independent product, not affiliated with Meta Platforms, Inc.

---

## Key facts

- **Supported data source:** Google Sheets (the Data Source Type select offers "Select source type" and "Google Sheets")
- **Fields a metric needs:** 4 (spreadsheet ID, Sheet Name, Column ID and Value Column)
- **Row matching key:** the campaign id (AdOps looks for the row whose lookup column equals the campaign id)
- **In-memory sheet cache:** 2 minutes since last access
- **Read order:** CSV export, then the Sheets API, then the last rows stored in MongoDB

## Steps

1. **Prepare the spreadsheet** — Put a single header row at the top of the sheet. One column holds the lookup key, one column holds the number you want AdOps to read. AdOps matches a row by the campaign id, so the lookup column should contain campaign ids.
2. **Copy the spreadsheet ID** — Take the long id from the spreadsheet URL between /d/ and /edit. The Google Spreadsheet ID field in AdOps carries the hint "Found in the spreadsheet URL".
3. **Open Custom Metrics and press + Add New** — Custom Metrics sits under Rule in the left sidebar. The list page has a "Search metrics" box, a Sort by menu offering Date Created, Name and Result Type, and a primary "+ Add New" button.
4. **Fill in Basic Information** — Enter a Metric Name (hint "Choose a unique, descriptive name", placeholder "e.g., Monthly Revenue Target"), paste the Google Spreadsheet ID, and write a Description that says what the number means and who maintains it.
5. **Configure the data source** — In Metrics Configuration set Data Source Type to Google Sheets. Three fields appear — Sheet Name (placeholder "e.g., Sheet1"), Column ID (placeholder "e.g., Date") and Value Column (placeholder "e.g., Revenue"). Column ID is the lookup column; Value Column is the number that is returned.
6. **Save and clear any validation errors** — Press Create Metric. AdOps reads the sheet and checks that both columns exist. On failure a red "Google Sheets Validation Failed" modal lists every problem, names the columns it did find, and shows a How to fix checklist.
7. **Use the metric in a rule** — In the rule builder open any metric picker and switch to the Custom metrics tab. A custom metric can be a condition metric, a compared metric, a budget value, or a token inside an Add to name template.

A custom metric lets an AdOps rule act on a number Meta never sees. You point AdOps at a Google Spreadsheet ID, a sheet name, a lookup column and a value column; at evaluation time AdOps finds the row whose lookup column matches the campaign id and returns that row's value column as the metric.

## What is this for?

Margin, cost of goods, offline revenue, a stock level, a per-campaign target your finance team maintains. Any number a human keeps in a spreadsheet can become a rule condition, or a budget amount, or a token stamped into a campaign name.

The typical shape is one row per campaign:

| campaign_id | target_roas | margin |
| --- | --- | --- |
| 23851234567890123 | 2.5 | 0.42 |
| 23851234567890456 | 3.0 | 0.31 |

With **Column ID** set to `campaign_id` and **Value Column** set to `target_roas`, a rule can compare live Purchase ROAS against a target that differs per campaign and that your team edits in a spreadsheet.

## Creating the metric

Open **Custom Metrics** under Rule in the sidebar and press **+ Add New**. The form is two cards.

**Basic Information** takes the Metric Name, the Google Spreadsheet ID and a Description. The spreadsheet field is hinted "Found in the spreadsheet URL" with the helper "Leave empty if not using Google Sheets".

**Metrics Configuration** starts with a Data Source Type select — "Select source type" or "Google Sheets". Choosing Google Sheets reveals Sheet Name, Column ID and Value Column.

Press **Create Metric** to save, or **Save as Draft** to come back to it. In edit mode the header reads **Update Custom Metrics**, the primary button becomes **Update Metric**, and Save as Draft is hidden.

## When validation fails

AdOps reads the sheet before saving and checks that both columns exist. If they do not, a red **Google Sheets Validation Failed** modal lists each problem — for example that a Column ID was not found in a named sheet, followed by the columns that actually are there — plus a **How to fix** checklist.

The two failures worth checking first are a spreadsheet AdOps cannot open, and a column name that does not match the sheet's header exactly, including case and trailing spaces.

## How the number is read at run time

AdOps fetches the sheet through Google's direct CSV export endpoint with a 30-second timeout. If that fails it retries through the Sheets API, and if Google is unreachable entirely it serves the last rows it stored for that sheet.

Rows are also held in an in-memory cache keyed by spreadsheet and sheet name, with a two-minute window measured from the last access. That is what lets hundreds of campaigns in one batch share a single spreadsheet read. It also means the window can keep extending under continuous access, so treat a custom metric as a number that updates in minutes, not seconds.

Because the MongoDB fallback exists, a spreadsheet whose sharing has been revoked can keep resolving from stored rows. After changing a sheet's permissions, re-save the metric in AdOps to confirm it still validates.

## Using it in a rule

Every metric picker in the rule builder has two tabs: **Meta Ads** and **Custom metrics**. A custom metric can be:

- the metric in a condition,
- the compared metric in a metric-to-metric condition,
- a budget action's value, by setting `value_3` to `custom_metric`,
- a token in an Add to name template, written `{custom_metric|<id>}`.

Saved rules resolve stored ids back to metric names, so a task summary reads "Blended ROAS" rather than a database id, and the same substitution happens in the execution log. An id that no longer resolves renders in red — that is your signal that a metric was deleted while a rule still refers to it.