Create formula columns

When you need to apply your own calculations to data from DoubleClick Search (DS), instead of downloading the data and using spreadsheet functions, you can create a formula column directly in DS. A formula column applies spreadsheet-like functions to data from existing DS columns. You can add formula columns to DS reports, create bid strategies that optimize to the formula, or use them in other formula columns.

See the list of functions you can use in formula columns, and the list of columns that can appear in formula columns.

For example, you can use formula columns to:

  • Create custom metrics. For example, instead of cost per action, you can create actions per cost, which displays the total number of conversions (to create this column, you add Actions and Trans).

  • Rename a column. For example, your vertical may actually refer to CPA as CPL; you can rename the column so reports are more relevant.

  • Sum up Floodlight columns with weights. You can refer to already-created formula columns in your formulas. So, if you created a column named KPI that weighs Floodlight activities and clicks, you could then set up a column for cost per KPI.

  • Apply fixed values. For example, you could set up a formula for a Q3 budget goal of $30,000. You could also set up two different CPA goals for brand and generic: CPA goal (brand) of $30 and CPA goal (generic) of $50. It might also be helpful to set up a column with the number of days for this month, which you could then use in other formulas.

  • Create custom alerts using logical operations (If, And, and Or) and string concatenation. For example, make bid changes, pause ad copy, or set certain alerts when certain conditions or thresholds are met.

  • Date handling: You can specify the values in formula columns based on a date range that you set.

You can use all the same features on formula columns that are available on standard DS columns, including sorting, charting, downloading, filtering, and date comparison. Also, data in formula columns is backfilled and calculated in real time (not just from the time of creation onwards).

Example formulas for common use cases

Use case Example
Column family with fees
  • Fee = To_money(Clicks * 0.02) or To_money(cost * 1%)
  • Cost with Fee = c:"Fee" + Cost
  • CPA (with fee) = c:"Cost with Fee" / Actions
Column family KPIs
  • KPI = c:"Paid download" + 40% * c:"Free download" + 5% * Clicks + 1% * Impr
  • Cost per KPI = Cost / c:"KPI"
  • Target Cost per KPI: To_money(5.00)
  • Diff from Target = c:"Cost per KPI" - c:”Target Cost per KPI"
Budget
  • Q3 budget = To_money(10000)
  • Contribution to Q3 budget = To_percent( Cost / c:"Q3 budget")
Total conversions Actions + Trans
Difference from target CPA - 30
Rename a column

To rename CPA to CPL, simply name the column CPL and use the following formula:

CPA

Daily cost Cost / Num_days()
Test whether a daily cost is above some threshold Cost / Num_days() >= c:"Daily cost threshold"

Formula columns can also integrate perfectly with other DoubleClick Search (DS) features like rule-based automation, web queries, and bid strategies. Check out some of our more advanced use cases.

Known issues

  • Currency of To_money() can change: The To_money() operation converts a number into the currency the report is using. Therefore, using a constant value inside a To_money() operation results in a column with different meanings in different contexts.

    For example, To_money(Cost + 5) means "cost plus $5" in a USD report, and "cost plus £5" in a GBP report.

    We recommend you include the intended currency in the name of any column that uses a constant value inside a To_money() call.

  • Can’t use commas in numbers: The formula language is not internationalized, so even users that otherwise use a comma as a decimal separator in DS must use a period in formulas. For example, a quantity of five-and-a-half percent would be 5.5%, never 5,5%.

  • Formula columns that contain dates don’t appear properly in the summary page that lists all Budget pacing reports. However, these columns will work if you click on a Budget pacing report to see more detailed information.

Steps to create a formula column

  1. Click the Columns button in the toolbar above the performance summary graph.
    The column selection tool appears.

  2. Click Formula columns under the Available columns header.
    A list of your existing Formula columns appears.

  3. Click Create a new column.
    The pop-up with the settings for your new column appears.

  4. Name your column.
    This is the name that will appear in the reporting table, so make sure it’s meaningful to all users who will see it.

  5. In the Look up variable field, start typing an existing column name (or a word in the name). As you type, DS displays the list of columns that match the name you're typing.
    You can also search on existing formula columns, which will appear as c:”Formula_column_name”.

    See the list of all available columns.

  6. To add a column to the equation, select it and click Add variable.
    The column will appear in the Enter equation box. You can also enter a column directly in the Enter equation box.

  7. Repeat steps 5 and 6 for each variable you want to add.

  8. In the Enter equation box, add the desired functions.
    See the list of functions you can use in formula columns.

  9. Click Save.
    • If an error occurs, click Select error to highlight the specific error, attempt to fix it, and click Save again.
    • Once you have resolved all errors, the column will automatically appear in the list of selected columns. If you’re editing an existing column, you’ll need to manually add it.
  10. Under Selected columns, drag and drop the new column to the desired location. If you’re editing an existing formula column, you’ll first need to manually drag and drop it to the selected columns.

  11. In the When displaying aggregated data list, specify how summary rows in a reporting table aggregate data from the formula column:

    • Apply formula to totals: summary rows sum the raw data in the individual rows and then apply the formula to the sum.
      For example, for a formula column that divides keyword cost by the number of days in a report, this option configures a summary rows to add the total cost and then divide by the number of days.
    • Apply formula first, then total the results: summary rows apply the formula to each row and then sum the formula results.
      For example, for a formula column that divides keyword cost by the number of days in a report, this option configures a summary row to divide each keyword’s cost by the number of days and then add up the results.
  12. Click Apply.
    The new column appears in your reporting table.