Custom formula columns allow you to access and leverage a wide variety of metrics computed from Search Ads 360 data to build your own columns. You can layer in references to other column formulas and spreadsheet functions to make your columns more nuanced. Just like standard Search Ads 360 columns, most custom columns can be filtered, sorted, downloaded, and used to customize the performance summary chart above your reporting table.
In this article:
How to create a custom formula column
- Sign in to your new Search Ads 360.
- From the page menu on the left, click a page with a reporting table, such as Campaigns, Ad groups, Ads, or Keywords.
- In the upper right corner of the table, click the columns icon
.
- Click+ Custom column in the upper right, and select Custom formula column.
- Provide the following information for your column:
- Owner: Select a manager or a sub-manager account.
If you select a manager account as the owner, the column is available to anyone who is signed in at the manager account level. The column can include any other columns that are owned by the manager account.
If you select a sub-manager account as the owner, the column is available to anyone who is signed in to the sub-manager account and the manager account above it. The column can include other columns that are owned by the same sub-manager account.Learn more about owner accounts. - Name: Enter a name that will be used as the column header.
- Description (optional): Enter a description of the column that you’ll see when you hover over the column header.
- Data format: Select the type of data in the column.
- Formula:
- Click + Column, then select a column such as Conversions, Clicks, Cost, Custom Columns, or Custom Dimensions.
- For some metrics, you can narrow down the metric data by adding filters. For example, to show only clicks from mobile devices, select Clicks as your metric. Then, in the 'Filter' section that appears on the right, click Device and select Mobile phones.
- Use the maths operators to create an expression for your column. For example, to calculate the percentage of mobile clicks in a campaign, select Clicks with the segment 'Mobile phones' as your first metric. Then, click the ÷ operator and select Clicks as your second metric.
- Instead of clicking buttons, you can enter your formula by typing in the box under '+ Column' and '+ Function'.
- You can also apply text functions. For example, you can apply the upper or lower function to change the column text to all upper or lowercase. If you apply a function to a custom dimension, you select the function first, for example, upper("text"). Then select a custom dimension to replace "text", for example, upper(cd:"Furniture (furniture categories):Furniture (furniture categories)").
- Owner: Select a manager or a sub-manager account.
- When you’ve finished entering column information, click Save.
- On the “Modify columns” page, select your new custom column, then click Apply to add it to the table.
.Building the formula
A custom formula column is composed of a formula, which is written just like a formula in a spreadsheet. You can add columns, operators, constants, other custom columns, filters, and functions to your formula.
Adding columns
To add a column to your formula, you can either click the + Column button and select the column, or start typing the name of the column and then select it from the dropdown that appears.
In visual mode, any column that is added will be converted into an oval. To add filters to that column, click the oval.
In text mode, added columns remain as text, but will be highlighted in blue if the formula is valid. Although column names are case-insensitive, you must type a column’s name exactly as it’s listed in the column list.
Adding other custom columns
You can also add references to other custom columns to your formula. If you change the formula of the other custom column, the update will be automatically reflected in custom columns that refer to it. You can only add references to other custom columns that are owned by the same account.
In visual mode, add references to other custom columns in the same way as standard columns. These references will appear with a link icon beside the name of the custom column.
In text mode, references to other custom columns are prefixed with c: and surrounded by double quotes, unlike standard columns. For example, to double the value of a custom column named Mobile Clicks, write c:"Mobile Clicks" * 2.
Similar to standard columns, note the Data Format of the custom column you reference to ensure it’s used appropriately to avoid errors. For example, you can’t multiply a text custom column.
There are limits to the use of custom column references.
Adding operators
Operators are common arithmetic symbols used to perform a function within a formula. You can use operators to customize functions and calculate results by creating the column conditions that will yield the output you want to find. You can add operators (such as the plus sign for addition) between two numeric values by typing it on your keyboard, or clicking the corresponding button on the formula toolbar, if available. To control the order in which operators are used, use parentheses.
| Sign | Description | Result type |
|---|---|---|
+ |
Add (Plus) | Number |
- |
Subtract (Minus) | Number |
* |
Multiply | Number |
/ |
Divide | Number |
< |
Less than | True/False |
> |
Greater than | True/False |
<= |
Less than or equal to | True/False |
>= |
Greater than or equal to | True/False |
= |
Equal to | True/False |
!= |
Not equal to | True/False |
>=) are a combination of two characters.Adding constants
A constant is a fixed value that doesn’t change. Because a constant doesn’t change, you can type numbers directly into the formula. Use a period as the decimal separator. For example, a quantity of five-and-a-half would be 5.5 instead of 5,5. A thousands separator is not used in formulas, so a quantity of 3,320 is written as 3320.
You can also add text values in formulas if it’s appropriate given the surrounding context. Text values are always wrapped in double quotes. If you want to include the double quote character in your text, learn how to include them. In addition, you can write the text "true" and "false" in the formula directly to represent a true or false constant, respectively.
For example, the following formula includes numeric and text constants: length("My text") * -3.45
Adding filters
Filters modify the behavior of columns in a formula. To add a filter (such as Device or Conversion Action filters), first add a column to the formula. View available filters.
In visual mode, click on the oval that represents the column you want to filter. Available filters are listed in the filter panel beside the formula area. Expand a category to add one or more filters in that category. If nothing is selected within a filter category, then that category is not filtering the metric.
In text mode, type a period immediately after a column name (with no space) and add the filter name. Put filter values inside parentheses immediately following the filter name. For example, to filter Clicks to only clicks from mobile devices and tablets, write: clicks.device(mobile, tablet).
Adding functions
To add a function to a formula, either click the + Function button or start typing the name of a function and select one from the suggestions.
Functions produce an output called a return value (which could be a number, text, true/false value, or date type, depending on the function). Most functions require parameters. Parameters are the inputs into the function, and are specified in parentheses after the function name and separated by commas. The order of parameters and data format of each parameter is important. When you insert a function from a menu, placeholder text for each parameter will be inserted. You must replace the placeholder text for each parameter to complete the function.
Refer to the function list for a complete listing of all available functions and their parameters.
Selecting a data format
If the formula produces a numeric value, you can select between three values for “Data format”:
Number(default): The custom column will be displayed to 2 decimal places.Percent: The custom column will be multiplied by 100 and displayed with a percent symbol (%) to 2 decimal places.Currency: The custom column will be displayed with the currency symbol of the data in the row in which it’s displayed, regardless of whether the formula actually represents a currency value.
If the formula produces text, a true/false value, or a date, you can select only the corresponding data format (Text, True/False, or Date, respectively).
Once you save the formula, you can not change the data format.