DV360 Bulk Editor Using Sheets BigQuery And A Colab

There are hundreds of values that can be edited for LineItems and Insertion Orders in DV360. SDF files do a great job but are difficult to automate due to manual uploads and can be accidentally corrupted. To improve upon SDF limitations, our gTech team collaborated with partner engineers to create an API based DV360 Bulk Editor.

DV360 Bulk Editor screen shot.

Scope

The DV360 Bulk Editor currently ONLY edits at the InsertionOrder and LineItem layers, although the pattern could be expanded to Campaigns, Advertisers, and even Partners. In addition, due to significant differences in the targeting API, all the bulk operations for targeting were built into separate DV360 Bulk Targeting Editor and DV360 Bulk Targeting Audit tools.

Features

• Sheets based editing with Colab / UI based controls.
• Loads all partners, advertisers, and campaigns under a user profile.
• Filtering which InsertionOrders and LineItems to edit.
• Prior value column (green) and new value column (white).
• Using formulas in the sheet to adjust values.
• Drop downs for all enumerated types.
• Adding or editing values, for example new segments.
• Simultaneous changes at InsertionOrder and LineItem layers.
• Pre-commit audit that shows all errors and warnings.
• Pre-commit audit is a BigQuery view and is customizable as SQL.
• Writes ONLY changes to the API.
• Sheet shows log of all changes written and all errors encountered.
• Re-entrant, reset filters and re-run load operation to re-use same sheet.
• Massive scale, it will edit campaigns of any size, time permitting.

Instructions

Please see the one pager for instructions, various deployments and a sample. The list of commands is as follows:

• Clear Partners — Reset partners sheet filters.
• Clear Advertisers — Reset advertiser filters.
• Clear Campaigns — Reset campaign filters.
• Clear Insertion Orders And Line Items — Clear all loaded editing sheets.
• Clear Preview — Clear all audit logs.
• Clear Update — Clear all commit logs.
• Load Partners — Load Partner with with all accessible partners for user.
• Load Advertisers — Load from Partner sheet filter (blue column).
• Load Campaigns — Load from Advertiser sheet (blue column).
• Load Insertion Orders And Line Items — Load from advertiser and campaign filters (blue columns).
• Preview — Run audit logic on all changes.
• Update — Commit all changes to DV360.

Performance

The bulk editor shines for large complex changes across multiple LineItems or InsertionOrders because it can audit those changes prior to submission. It ensures consistency and accuracy for bulk changes. It is built for scaled time consuming changes where human error or omission are a risk. The UI is still much faster and safer for one-off changes. Time wise, the tool does take at least 5 minutes to load all the filters, run all the validation, and perform the API calls.

To reduce the load times, reduce the number of campaigns and advertisers being loaded using the filters. For large accounts, a large edit may take as long as an hour, however, the user does not need to be present for the final write step as it is automated.

Architecture

To reduce the coding complexity of the editor, all of the core logic exists as SQL views. This allows teams to customize almost all parts of the editor with only SQL modifications within BigQuery.

The solution leverages a very thin python task to move data between the DV360 API and BigQuery. Every API endpoint has its own script, with these basic functions, only InsertionOrder and LineItem have the last three:

• clear — erases all sheets and BigQuery data affiliated with the endpoint.
• load — moves data from DV360 API to BigQuery and then to the sheet.
• audit — executes a view query and writes errors and warnings to sheet.
• patch — loads BigQuery view and assembles a patch call for the API.
• insert — loads BigQuery view and assembles an insert call for the API.
• commit — writes data from Bigquery into DV360 patch.

For account safety the audit is run even on a commit, and if an ERROR is returned by the audit, the resulting object will not be written to the API.

As the user executes various commands the above code builds a BigQuery dataset and a Sheet, both of which are editable. The sheet is designed to be used by the end user, the BigQuery views are designed to allow customizing the audit, patch, and insert rules. The resulting BigQuery dataset will look as follows:

Tables and Views created by the DV360 Bulk Editor.

Customizing It

This solution was designed to be completely customizable using only SQL and BigQuery UI. After running the solution once to load all the data, visit the resulting BigQuery dataset and customize any of the views available. The solution will use the new logic in all subsequent edits.

• Audit Logic — customize for accounts to prevent errors.
• Add custom limits for budgets.
• Build complex dependencies.
• Enforce names.
• Catch segment misconfigurations or conflicts.
• Return severity as ERROR to prevent commit, and WARNINGS to notify.
• Commit Logic — customize commits to enforce policy.
• Remove ability to edit values even if set in sheet.
• Add hard coded values.
• Add complex logic to construct derived value, reduce user stress.

The controller and sheet can also be extended to edit campaign, advertiser, or partner values.

Deployment

First, be sure to use the Get Access link to join the group that grants access to ALL StarThinker assets including this dashboard.

If your organization has a centralized StarThinker UI, check the list of solutions for DV360 Bulk Editor or ask the engineers to synchronize it with the latest GitHub release.

The quickest deployment is the Colab Notebook. For all other deployments, check the specific instructions.