Why?

We speak to so many publishers and consultants who copy and paste reporting spreadsheets together every day, so we decided to release a Google Sheets add-on we'd developed for internal use to the public as Ocelot Metrics. It allows you to easily build reports to pull data into your Google Sheets from the Google Ad Manager API.

This tutorial demonstrates some basic usage of Ocelot Metrics, along with some more advanced usage of Google Ad Manager and Google Sheets. At the end, you'll have a simple revenue dashboard showing daily revenue broken out by source (eg. AdX, Amazon, and Prebid).

Don't have Ocelot Metrics yet? You can use the coupon code DASHBOARD during checkout to get 90% off your first month.

TL;DR

If you want to take a look at the end result (and how we get there), here's a link to a public Google Sheet.

If you want it explained, keep reading.

Initial setup

Let's start by creating a new Google Sheet. You can visit sheet.new in a browser as a handy shortcut to do this.

Now we're going to set the timezone of our sheet to match our ad server timezone, so that we can use a custom date range for our Ad Manager API query instead of the built-in date ranges.

From the Google Sheets UI, go to File > Spreadsheet Settings and select the correct timezone from the drop-down menu.

Img

Second, we're going to use a little optimization tip.

The today() function gives us the current date in the timezone of the sheet. Very handy – with one caveat. It's what's known as a volatile function, which recalculates every time your sheet changes – and can cause major performance issues if you use it in multiple places in a sheet.

So add a new sheet using the + button in the bottom left of the screen, and rename it to Dates.

In cell A1, enter Today and in cell B1, enter =today(). Then right click cell B1, select Define named range, and create a named range called Today.

Img

Now we can use the current date anywhere in our sheet while limiting the performance hit. It's not a big deal with this tutorial, but it will make a huge difference in some future tutorials.

Setting up a custom field in Ad Manager

We're going to set up a custom field called Order Type, which can be applied to all your orders in Ad Manager. This will allow us to easily aggregate revenue from different types of orders – like Amazon, Prebid and direct campaigns.

  1. From the Ad Manager UI, go to Admin > Global settings in the menu on the left, then to the Custom fields tab.

  2. Create a custom field called Order Type with the location Order and the type Drop-down, save it, then add values for AdX, Amazon, Direct, House, and Prebid.

Img

  1. Go to Delivery > Orders in the menu on the left, then open each order, click the Settings button next to the order name, scroll down in the settings pane, add the Order Type custom field and select the relevant value.

Img

Set up an Ad Manager query using Ocelot Metrics

From the Google Sheets UI, go to Add-ons > Ocelot Metrics > Create report.

Create a report using the date range Custom Date.

...and these dimensions: Date, Order Name, and Yield Partner.

...and these columns: Total Line Item Level Impressions, and Total Line Item Level CPM and CPC Revenue.

...and (of course) the Order Type custom field we just created.

Once you've created the report, we need to add the dates for our Custom Date.

In the End Date cell, enter =Today. In the Start Date cell, enter =Today-7, which will give us the same day last week.

The Ad Manager Reports sheet should now look something like this:

Img

Then go to Add-ons > Ocelot Metrics > Run reports. You'll end up with a sheet that looks something like this:

Img

Create an intermediate sheet to clean up our data

Ocelot Metrics will clear the data in an existing report before outputting the results of a report. So if you're scheduling this report to update every hour or day (and we recommend you do!) you'll need to create an intermediate sheet to do any data cleanup.

We're going to use the query function of Google Sheets to do this. It allows you to run queries in the Google Visualization API query language (which is very similar to SQL) inside Google Sheets.

The first parameter is the data we want to query. The second parameter is our query.

So add a new sheet using the + button in the bottom left of the screen, and rename it to Source by Date Intermediate.

Then in cell A1, use the following query:

=query(
  'Source by Date'!A:H,
  "select A, C, F, G, H
   label A 'Date', C 'Yield Partner', F 'Order Type', G 'Impressions', H 'Revenue'"
)

In plain English, this query reads: from columns A through H in our Source by Date sheet, select columns A, C, F, G, and H, and change the column names to Date, Yield Partner, Order Type, Impressions, and Revenue.

You'll end up with something like this:

Img

Because Yield Partner (ie. Open Bidding) doesn't have an Order Type associated with it, we're going to calculate a new column F for Source, based on the Yield Partner and Order Type columns.

We're going to use an ArrayFormula to automatically apply a calculation to every row in the sheet.

If Yield Partner in column B is not equal to (Not applicable), then the impressions/revenue are attributable to Open Bidding. Otherwise, we can use the Order Type in column C.

ifs allows us to chain conditions together, and return a value for the first condition that is true.

We're going to use ifs in conjunction with ArrayFormula, which allows us to dynamically apply a formula to each row in a sheet.

Here's the full formula for cell F2:

=ArrayFormula(ifs(C2:C = "-", "Open Bidding", TRUE, C2:C))

In plain English, this reads:

For each row in this sheet, if column C is equal to -, make the cell in column F on the same row equal Open Bidding, and if not, make the cell in column F on the same row equal the value from column C.

Now we have something that looks like this:

Img

Creating a pivot table using the query function

We could use the built-in pivot table functionality of Google Sheets and call it a day, but for dynamic data (like the rolling date range of our Ocelot Metrics query) I prefer to use group by and pivot with query.

Here's how that's done.

Add a new sheet using the + button in the bottom left of the screen, and rename it to Source by Date Pivot.

In cell A1, use this formula:

=query('Source by Date Cleanup'!A:F, "select A, sum(E) where A is not null and F is not null group by A pivot F label sum(E) 'Revenue'")

The key parts of this query are sum(E) which gives us our cells, group by A which gives us our rows, and pivot F which gives us our columns.

Now we have something that looks like this:

Img

But we want totals for each row and column, right?

We can use multiple queries inside curly brackets to create additional cells, rows or columns. You just need to take care to ensure that you have the same number of values in each row and column.

Use , to create new columns, and ; to create new rows.

={
  query('Source by Date Intermediate'!A:F, "select A, sum(E) where A is not null and F is not null group by A pivot F label sum(E) 'Revenue'"),
  query('Source by Date Intermediate'!A:F, "select sum(E) where A is not null and F is not null group by A label sum(E) 'Total Revenue'");
  transpose(query('Source by Date Intermediate'!A:F, "select sum(E) where A is not null and F is not null group by F label sum(E) 'Total'")),
  query('Source by Date Intermediate'!A:F, "select sum(E) label sum(E) ''")
}

In this array of queries, the first query is our initial query from the previous step.

The second query is a Total Revenue column.

The third query is a Total row for each column.

The fourth query is the bottom right cell, calculating the total revenue for all cells.

And here's our final result:

Img

Subscribe for more content like this

Our next post in this series will show you how to break out Prebid revenue by partner using Custom Dimensions.

Fill out your details in the form below and we'll send you an email when it goes live.