Our last guide showed how to build a simple dashboard which displayed your daily ad revenue broken out by integration (ie. Prebid, Ad Exchange, Open Bidding...).

In this guide, we build an ad unit-level dashboard which uses Ad Exchange data exclusively. While this doesn't give us a complete picture of revenue, we do get an at-a-glance look at ad unit performance and trends with far less work.

If you want to take a look at the end result, here's a public Google sheet.

To see your own data in this dashboard:

  1. Go to File > Make a copy
  2. Change Network Code on the Ad Manager Reports sheet to your own network code
  3. Change cell A1 on the Today sheet to =today()
  4. Go to Add-ons > Ocelot Metrics > Run reports

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

Here's how it works.

We're going to use four Google Sheets functions:

  • indirect which turns a string into a cell/range reference
  • sort which (you guessed it) sorts a range
  • unique which returns unique values from a range
  • sparkline to create a simple chart that lives in a single cell

For an in-depth look at what you can do with sparklines in Google Sheets, we highly recommend this article by Ben Collins.

Take a look at the formula in cell A3 on the Dashboard sheet.

=sort(unique(indirect(A1&"!A2:A")))

Cell A1 has the value Ad Units, so indirect(A1&"!A2:A") gives us Ad Units!A2:A, or all values in column A of the Ad Units sheet. Wrapping it in sort and unique gives us an alphabetically-sorted list of the values found in that column.

Why have we done this? So that you can easily use data from another sheet by changing the value of cell A1.

Now let's take a look at the formula in cell B3.

=iferror(sparkline(query(indirect($A$1&"!$A:$G"),"select "&B$1&" where A = '"&$A3&"'")))

  • indirect($A$1&"!$A:$G") gives us Ad Units!A:G, which in this case is all of the data in the Ad Units sheet
  • "select "&B$1&" where A = '"&$A3&"'" gives us select F where A = '1234567 » Anchor'
  • used as parameters for query, we get the Ad Request eCPM (column F) of the Anchor ad unit over the last two weeks
  • which passed in to sparkline, gives us a little graph of the Ad Request eCPM over the last two weeks

The net result is that you can change the letters in the first row of a column to easily use different columns from the data sheet.

You can probably figure out how the other formulas work at this stage. If you have any questions about Ocelot Metrics (or anything else) or feedback about these guides, feel free to get in touch with us via the form below.