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:
- Go to
File > Make a copy
Network Codeon the
Ad Manager Reportssheet to your own network code
- Change cell
- 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.
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
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
Now let's take a look at the formula in cell
=iferror(sparkline(query(indirect($A$1&"!$A:$G"),"select "&B$1&" where A = '"&$A3&"'")))
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.