Syntax for formula columns in Search Ads 360

Use formula columns to add custom logic to reports and inventory plans in Search Ads 360.

For example:

• In a report, you can create a formula column that applies weighted values to conversions recorded by Floodlight.
• In an inventory plan, if you frequently prefix a product's price with the $ character and output the price with two decimal places, you can create a formula column that formats your inventory data. Then you can use the formula column in your inventory templates instead of repeating the formatting logic in each template.

Syntax for formula columns

The syntax for formula columns is similar to standard spreadsheet formulas.

A formula specifies one or more of the following items:

• Functions
  Some functions can only be used in formula columns that contain engine or conversion metrics, while other functions can only be used in formulas or formula columns that contain inventory data. When you're creating formulas, Search Ads 360 shows only the functions that are valid for the current context. 
• Static text or numbers
• Inventory attributes (applicable only for formula columns used in inventory management)
• Output from formula columns

You may need to use functions that convert data types depending on where you're using the formula and on the other types of data in the formula.

Functions

Enter the name of a function as it appears in the function list. You can nest functions (use a function as parameter for another function) as long as the nested function returns the expected data type. The following example nests right within left:
left(right("Hello World!", 6), 5)

Function names are not case-sensitive. You can use lower case, upper case, or mixed case names. For example, all of the following statements are equivalent:

left(right("Hello World!", 6), 5)
LEFT(RIGHT("Hello World!", 6), 5)
Left(Right("Hello World!", 6), 5)

Static text

Note the following when using static text in formulas:

• Surround static text with double quotes.
  This applies for text that you use as a function parameter as well as text that appears before or after a function.
  
  For example:
  title_case("shoes")
   

• Use the & character to join two strings together. This applies to both static strings and functions that return strings.
  For example:
  "Quality " & title_case("shoes")

• To output double quotes (") in a template or formula column, add a double-quote before the quote:
  """Frankenstein"" mask for only " & to_string(Item_price)
  or
  "Get """ & Item_brand & """ shoes"   

Static numbers

Do not surround static numbers with quotes (if you use quotes, the function considers the number to be a string).

Inventory attributes

In formulas used in inventory templates or on the Inventory items tab, you can insert data from any product attribute defined in your inventory feed. 

An inventory feed can contain two types of product attributes:

• Inventory attributes: These are attributes pre-defined by the Google Merchant Center feed specification, such as Item_brand and Item_price.
  
  To include a standard attribute in a formula, enter the name of a standard attribute as it appears in the Google Merchant Center feed specification.
• Custom attributes: These are attributes that you define in your Google Merchant Center account, and then include in your feed. 
  
  To include a custom attribute in a formula, enter the name as follows:
  c:”custom-attribute-name”
  
  For example:
  c:"Converted price"

Note the following about attribute names:

• Attribute names are not case-sensitive. For example, you can use any of the following to refer to the "Brand" standard attribute: ITEM_BRAND, item_brand, Item_Brand
  
  Similarly, you can use any of the following to refer to a custom attribute named c:"Converted price": c:"CONVERTED PRICE", c:"converted price", c:"Converted Price"
   
• Don't surround inventory attributes with quotes or the template will interpret the attribute name as static text.

Formula columns

To include a formula column in a formula, enter the name as follows:
c:”column-name”

For example:
c:"price_with_currency"

Keep in mind that some formula columns may not be valid for the current context. For example, if you're creating a formula column from the Keywords tab (which does not show inventory data), you can't include a formula column that includes inventory data.

Search Ads 360 shows only the formula columns that are valid for the current context.

Convert data types in a formula column

Formula columns in reporting tables can return any type of data (such as string, integer, decimal, money, or date). In inventory templates, the return type depends on which field the formula is used in. For example, in a campaign template's Start date and End date fields, the formula needs to return a date.

Search Ads 360 provides several functions to convert data from one type to the next. For example, the following formula converts the PRICE inventory attribute (which is in the money format) to a string, which would be required to output an item's price in ad copy:
to_string(Item_price)

You can also convert the output of nested formulas. For example, this formula assigns a monetary value to Clicks:
to_money(Clicks * 0.02)

The Search Ads 360 formula editors show a message when you need to convert data from one type to another.