Using Time Periods

Many properties within Merch Jar Formulas reflect performance over a specific duration (like clicks, sales, acos). To use these time-based properties, you must specify a Time Period directly after the property name, enclosed in parentheses (). Static properties like campaign name or bid do not use time periods, and always use the current settings or values for the property.

Time Period Syntax: property(time_period)

Formulas support several flexible ways to define the time_period:

1. Relative Time Periods

Format: Xd

Looks back X number of days from the moment the formula is evaluated.

Examples:

clicks(7d)    // Looks at clicks over the last 7 days.
sales(30d):   // Looks at sales over the last 30 days.
acos(1d):     // Looks at ACOS for the most recent complete day (yesterday).

2. Literal Date Ranges

Use exact calendar dates for precise analysis of specific historical periods from the start date to the end date, inclusive of the dates specified. A start and end date are required.

Format: YYYY-MM-DD..YYYY-MM-DD

Examples:

spend(2024-01-01..2024-01-31)      // Spend during January 2024.
orders(2023-12-15..2023-12-25)     // Orders placed between Dec 15th and 25th, 2023.

3. Offset Relative Date Ranges

These formats define time periods relative to the evaluation date ("today"), allowing you to compare distinct periods or exclude recent data.

Format: Xd..Yd

Looks at a period starting Y days ago and ending X days ago, inclusive of the days specified. Y must be greater than X.

Examples:

sales(7d..14d)                    // Looks at sales from 14 days ago through 7 days ago.
orders(30d) > orders (31d..60d)   // Compares orders over the last 30 days to orders over the previous 31 to 60 days ago.

Format: ..Xd

Looks at data from the time of evaluation ("now") through X days ago. Equivalent to the simple Xd format (e.g., ..30d is the same as 30d).

Example:

clicks(..60d)      // Looks at clicks over the last 60 days, including today (same as clicks(60d)).

Format: Xd..

Looks at data starting from X days ago back to the beginning of available history.

Example:

spend(30d..)     // Looks at all spend recorded from 30 days ago to the beginning of available history.

4. Lifetime Date Ranges

Represents the entire period for which Merch Jar has synced data for your account.

Format: lifetime or ..

Includes all available historical data from the earliest sync point up to the most recent data.

Examples:

orders(lifetime) = 0     // Find items that have never had an order recorded in Merch Jar.
spend(..) > 10.00        // Find items that have incurred at least $10 spend historically (same as spend(lifetime)).

Choosing the Right Time Period

• Use Relative (Xd) for recent performance analysis (e.g., last 7, 14, 30, 60, 90 days).
• Use Offset Relative (Xd..Yd) for comparing current vs. previous periods.
• Use Literal (YYYY-MM-DD..YYYY-MM-DD) for specific date-bound analysis (e.g., analyzing Q1 performance).
• Use Lifetime (..) for historical checks (e.g., has it ever had sales?).

Refer to the Syntax Reference for a full list of time-based properties that require these time periods.

‍