Formulas Syntax & Functions

In This Article

This reference details the syntax used for Merch Jar's Formulas and automation features. Explore available properties, functions, operators, and data types to build powerful segments and workflows.

Data Types

Every value used in Merch Jar's Formulas syntax has a specific Data Type, such as Number, String, or Timestamp. Understanding these types helps ensure your comparisons and expressions are valid. This section lists the available data types and how they are used.

Type	Description
Number	Represents numeric values. Integers (like `10`) and percentages (like `25%`) are treated as decimals internally. Used for properties like `clicks`, `spend`, `acos`, or `bid`.
Timestamp	Represents a specific point in time, stored internally as a timestamp (seconds since Unix epoch). Used for properties like `last bid change` or returned by `now()`. Compare using date operators.
String	Represents text values. Must be enclosed in double quotes when used as a literal value in comparisons.
List	Represents a predefined list of accepted string values for certain properties like `state` or `match type`. Values must be enclosed in double quotes.
Time Period	Represents a span of time used for time-based properties or variables. Defined using relative (e.g., `30d`), literal (e.g., `2024-01-01..2024-01-31`), or offset (e.g., `7d..14d`, `..14d`) formats.
Variable	Represents a user-defined variable created using the `let` keyword. Can hold the result of expressions, calculations, `case` statements, or references to other variables, encompassing various data types (Number, String, List, Date, Time Period).

Type

Description

Number

Represents numeric values. Integers (like 10) and percentages (like 25%) are treated as decimals internally. Used for properties like clicks, spend, acos, or bid.

Timestamp

Represents a specific point in time, stored internally as a timestamp (seconds since Unix epoch). Used for properties like last bid change or returned by now(). Compare using date operators.

String

Represents text values. Must be enclosed in double quotes when used as a literal value in comparisons.

List

Represents a predefined list of accepted string values for certain properties like state or match type. Values must be enclosed in double quotes.

Time Period

Represents a span of time used for time-based properties or variables. Defined using relative (e.g., 30d), literal (e.g., 2024-01-01..2024-01-31), or offset (e.g., 7d..14d, ..14d) formats.

Variable

Represents a user-defined variable created using the let keyword. Can hold the result of expressions, calculations, case statements, or references to other variables, encompassing various data types (Number, String, List, Date, Time Period).

Built-Ins

Built-ins are the core operators, symbols, and keywords fundamental to the Formulas syntax language. Use them to perform comparisons, combine conditions with logic, do math, work with strings, and handle dates.

Built-In	Examples
Comparison Operators `=!=>>=<<=`	`clicks(7d) = 0` `state != "archived"` `bid > 0.50` `spend(90d) <= 100.00`
String Operators `=!=containsdoes not containstarts withends with`	`ad group name != "Test Group"` `campaign name contains "Branded"` `campaign name starts with "High Priority"`
Logical Operators `andor`	`clicks(14d) > 5 and spend(14d) > 2.00` `state = "enabled" or state = "paused"` `(orders(7d) = 0 and clicks(7d) > 10) or acos(30d) > 60%`
List Operators `=!=containsdoes not contain`	`state = "effectively enabled"` `state != "paused"` `match type contains "broad"` `match type does not contain "loose match"`
Date Operators `=!=>>=<<=`	`last bid change >= now() - interval(3d)` `last budget change <= now() - interval(7d)`
Math Operators `+-*/`	`bid > cpc(7d) + 0.15` `budget - spend(1d) < 5.00` `clicks(30d) * 0.01 > 1` `sales(14d) / orders(14d) >= 20`

Built-In

Examples

Comparison Operators

=!=>>=<<=

clicks(7d) = 0

state != "archived"

bid > 0.50

spend(90d) <= 100.00

String Operators

=!=containsdoes not containstarts withends with

ad group name != "Test Group"

campaign name contains "Branded"

campaign name starts with "High Priority"

Logical Operators

andor

clicks(14d) > 5 and spend(14d) > 2.00

state = "enabled" or state = "paused"

(orders(7d) = 0 and clicks(7d) > 10) or acos(30d) > 60%

List Operators

=!=containsdoes not contain

state = "effectively enabled"

state != "paused"

match type contains "broad"

match type does not contain "loose match"

Date Operators

=!=>>=<<=

last bid change >= now() - interval(3d)

last budget change <= now() - interval(7d)

Math Operators

+-*/

bid > cpc(7d) + 0.15

budget - spend(1d) < 5.00

clicks(30d) * 0.01 > 1

sales(14d) / orders(14d) >= 20

Properties

Properties represent the data fields and settings available in Merch Jar. Use them in filters and rules to analyze performance or check configurations. Some properties are time-based (requiring a time period like clicks(30d)), while others are static (like campaign name). Combine properties with functions and operators for advanced logic. Learn more about Time Periods →

Filter Properties

Property	Type	Description	Applies To	Account Types
acos	Number	Advertising Cost of Sale. Ad spend relative to ad revenue (`spend / sales`). `acos(30d) > 40%` `acos(7d..14d) <= 0.35`	CampaignsAd GroupsTargeting	KDPSellerVendor
ad group name	String	The current name of the Ad Group. Use with string operators like contains, starts with, etc., and quoted values. `ad group name contains "Exact Match"` `ad group name does not contain "Test"`	CampaignsAd GroupsTargeting	KDPSellerVendor
adjusted estimated royalties	Number	Estimated KENP royalties adjusted by the KENP Impact Multiplier (`estimated royalties * KENP Impact Multiplier`). Relies on Ad Account Settings. `adjusted estimated royalties(60d) > 25.00` `adjusted estimated royalties(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
adjusted orders	Number	Number of orders adjusted by the Order Impact Multiplier (`orders * Order Impact Multiplier`). Relies on Ad Account Settings. `adjusted orders(30d) > 5` `adjusted orders(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
adjusted page reads	Number	KENP pages read adjusted by the KENP Impact Multiplier (`pages read * KENP Impact Multiplier`). Relies on Ad Account Settings. `adjusted page reads(30d) > 5000` `adjusted page reads(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
adjusted sales	Number	Sales revenue adjusted by the Order Impact Multiplier (`sales * Order Impact Multiplier`). Relies on Ad Account Settings. `adjusted sales(14d) > 200.00` `adjusted sales(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
aov	Number	Average Order Value. Average revenue per ad order (`sales / orders`). `aov(60d) > 15.00` `aov(lifetime) > 0`	CampaignsAd GroupsTargeting	KDPSellerVendor
bid	Number	The current bid set for a Keyword or Target. `bid > 0.75` `bid <= cpc(7d) * 1.2`	Targeting	KDPSellerVendor
blended acos	Number	Blended Advertising Cost of Sale (`spend / blended profit`). Relies on Ad Account Settings. `blended acos(60d) < 35%` `blended acos(30d) > 0`	CampaignsAd GroupsTargeting	KDP
blended aov	Number	Blended Average Order Value (`blended profit / adjusted orders`). Relies on Ad Account Settings. `blended aov(30d) > 5.00` `blended aov(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
blended cac	Number	Blended Customer Acquisition Cost (`spend / adjusted orders`). Relies on Ad Account Settings. `blended cac(90d) < 15.00` `blended cac(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
blended cvr	Number	Blended Conversion Rate (`adjusted orders / clicks`). Relies on Ad Account Settings. `blended cvr(30d) > 8%` `blended cvr(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
blended profit	Number	Total estimated profit from ads, combining adjusted sales profit and adjusted KENP royalties. Relies on Ad Account Settings. Formula: `(Adjusted Sales * Profit Margin) + Adjusted Estimated Royalties`. `blended profit(30d) > 50.00` `blended profit(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
blended roas	Number	Blended Return on Ad Spend (`blended profit / spend`). Relies on Ad Account Settings. `blended roas(30d) > 2.0` `blended roas(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
blended rpc	Number	Blended Revenue (Profit) Per Click (`blended profit / clicks`). Relies on Ad Account Settings. `blended rpc(14d) > 0.40` `blended rpc(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
budget	Number	The current daily budget set for a Campaign. `budget > 10.00` `budget <= 50.00`	Campaigns	KDPSellerVendor
cac	Number	Customer Acquisition Cost. Average ad spend per ad order (`spend / orders`). `cac(90d) < 25.00` `cac(30d..60d) > cac(0..30d)`	CampaignsAd GroupsTargeting	KDPSellerVendor
campaign name	String	The current name of the Campaign. Use with string operators and quoted values. `campaign name contains "Brand - SP"` `campaign name ends with "- UK"`	CampaignsAd GroupsTargeting	KDPSellerVendor
clicks	Number	Total number of times ads were clicked. `clicks(7d) >= 5` `clicks(30d..60d) < clicks(0..30d)`	CampaignsAd GroupsTargeting	KDPSellerVendor
cpc	Number	Cost Per Click. Average cost per ad click (`spend / clicks`). `cpc(14d) < 0.80` `cpc(90d) >= 1.10`	CampaignsAd GroupsTargeting	KDPSellerVendor
default bid	Number	The default bid set at the Ad Group level. `default bid > 0.50` `default bid <= 1.25`	Ad Groups	KDPSellerVendor
estimated royalties	Number	Estimated royalties from KENP pages read attributed directly to ads. `estimated royalties(30d) > 10.00` `estimated royalties(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
impressions	Number	Total number of times ads were displayed. `impressions(14d) > 1000` `impressions(lifetime) > 0`	CampaignsAd GroupsTargeting	KDPSellerVendor
last bid change	Timestamp	Timestamp of the most recent bid update for a target/keyword. Compare against now() using interval(Xd). `last bid change < now() - interval(7d)`	Targeting	KDPSellerVendor
last budget change	Timestamp	Timestamp of the most recent Campaign budget update. Compare against now() using interval(Xd). `last budget change < now() - interval(30d)`	Campaigns	KDPSellerVendor
match type	List	The keyword match type or auto-targeting type. Accepted values (must be quoted): "exact", "broad", "phrase", "product exact", "similar", "close match", "loose match", "complements", "substitutes" `match type = "exact"` `match type contains "close match"`	Targeting	KDPSellerVendor
orders	Number	Total number of orders directly attributed to ads. `orders(30d) > 1` `orders(2024-01-01..2024-03-31) = 0`	CampaignsAd GroupsTargeting	KDPSellerVendor
pages read	Number	Total Kindle Edition Normalized Pages (KENP) read attributed directly to ads. `pages read(60d) > 2000` `pages read(lifetime) > 0`	CampaignsAd GroupsTargeting	KDP
roas	Number	Return on Ad Spend. Ad revenue relative to ad spend (`sales / spend`). `roas(30d) > 3.0` `roas(7d..14d) <= 1.5`	CampaignsAd GroupsTargeting	KDPSellerVendor
roi	Number	Return on Investment. Profit relative to cost (`(sales - spend) / spend`). `roi(60d) > 50%` `roi(lifetime) < 0%`	CampaignsAd GroupsTargeting	KDPSellerVendor
sales	Number	Total revenue from orders directly attributed to ads. `sales(14d) > 50.00` `sales(30d) < sales(30d..60d) * 0.9`	CampaignsAd GroupsTargeting	KDPSellerVendor
state	List	The current status of the entity (Campaign, Ad Group, Target, etc.). Accepted values (must be quoted): "effectively enabled", "enabled", "paused", "archived" `state = "enabled"` `state != "archived"`	CampaignsAd GroupsTargeting	KDPSellerVendor
target acos	Number	The desired Advertising Cost of Sale (ACOS) percentage you aim for. Target ACOS is set at the Ad Account level by default and inherited by Campaigns, Ad Groups, and Targets unless a specific override value is set at a lower level. `target acos < 30%` `target acos <= 0.3` `acos(14d) > target acos * 1.2`	CampaignsAd GroupsTargeting	KDPVendorSeller

Functions

Function	Description	Syntax	Examples
`let`	Defines a named variable within the scope of the current Formula. Variable name must start with a $ sigil and the definition must end with a semicolon ;. Variables can store various data types (Number, String, Time Period, Properties etc.) and be referenced later.	`let $variableName = expression;`	`let $threshold = 45%; acos(30d) > $threshold` `let $target_cpc = cpc(14d) * 1.1; bid > $target_cpc` `let $lookback = 2d..30d; clicks($lookback) > 10` `let $grace_period = now() - interval(7d)`
`case`	Evaluates conditions sequentially and returns the value associated with the first true condition. An else clause must be provided to specify a default value if no when conditions are met. Can return Properties (e.g. acos(30d)) or Number, String, or List values. Cannot return Date values.	`case(when condition1 then value1, when condition2 then value2, ..., else defaultValue)` (long form) `case(condition1 => value1, condition2 => value2, ..., else defaultValue)` (short form)	`case( when state = "enabled" then "Active", else "Inactive")` `let $bid_multiplier = case( acos(14d) < 20% => 10%, acos(14d) > 50% => -10%, else 0% );` `clicks(30d) > case( match type = "exact" => 20, else 30)`
`now`	Returns the current timestamp (seconds since the Unix epoch). Used for time-based comparisons, often with the interval function.	`now()`	`last bid change < now() - interval(7d)` `campaign start date > now() - interval(90d)`
`interval`	Converts a duration (like "3d") into its equivalent value in seconds. Primarily used for date/timestamp arithmetic with the now() function.	`interval(Xd)`	`now() - interval(14d)` `let $two_weeks_ago = now() - interval(14d); last bid change < $two_weeks_ago`

Formatting Notes

Here are key rules and conventions to remember when writing syntax for Formulas and Workflows:

Case-Insensitivity: Property names, function names, operators (and, or), and list and string values (like "enabled", "broad") are case-insensitive. campaign name contains "brand" is the same as Campaign Name contains "BRAND".
String Literals: Text values used for comparison must be enclosed in double quotes ("). Example: campaign name contains "Brand".
Numeric Values: Standard integers and decimals are used. Percentages (25%) are automatically treated as decimals (0.25). Currency symbols ($) are ignored; values are based on the ad account's base currency.
Time Periods: Time-based properties require a time period specification within parentheses, like sales(14d) or clicks(7d..14d). See the Time Periods documentation for all formats.
Comments: Use // to add single-line comments. Anything after // on that line is ignored. Example: clicks(7d) > 10 // Check for minimal activity
Variables (let): Variable names must start with a $ sigil and definitions using let must end with a semicolon ;. Example: let $high_cpc = 1.50;
Operator Precedence: Logical and operations are evaluated before or operations. Use parentheses () to explicitly control the order of evaluation for complex conditions. Example: clicks > 10 and spend > 5) or acos > 50%