Building reporting templates from Triton data

There is significant value in setting up regular, recurring reporting based on Triton data. An example: Monthly dashboard-style reporting, showing aggregated Triton measurement metrics (Total Listening Hours, Sessions Starts, Unique Listeners) for all of your stations, station markets, format groups, and an all-company view, showing a 13 month historical trend. Such reporting enables you to properly evaluate your streaming performance over time, assessing where growth and value are being generated for your business.

But how can you implement this reporting if your business operates without data warehousing and analytics tools?

This article provides suggestions for creating easily updatable templates in commonly used spreadsheet software such as Microsoft Excel and Google Sheets that will enable you to produce regular, repeatable, insightful reporting.

Suggestion 1: Create "data vault" tab(s) in your spreadsheets into which you dump raw data from Triton

Build your model around a tab (or tabs) into which you paste data in exactly the same format as you export it from Triton. When new data is available (for example a new week or a new calendar month of data) it should be added to this "data vault" tab: Pasted in underneath the existing data, without deleting the old data, in order to preserve your data history.

Paste new data into rows below the existing data: Do not overwrite the history!

Any required calculations and transformations to the original data should be undertaken in the data vault tab(s) using formulas; the original data from Triton should not be modified.

• To add metadata, or categories, you should use vlookups.

• To undertake calculations, you should use formulas.

The aim is to be able to simply pull down your formulas in the columns adjacent to the new data data to make it ready for reporting.

Transformations to the data (in the case of this simple example, the addition of Market and Format fields) should occur in the data vault tab, to the right of the data that is exported from Triton every reporting period (in this example, monthly), and should be driven by formulas to make updating easy and quick.

The goal of this technique for inputting source data into your model is for you to not have to undertake any time consuming manual transformations on that source data. You paste the data into data vault tabs, pull down formulas that do the transformations for you, and then you're ready to update your report. Easy and quick!

Suggestion 2: Use station-level data in your report, and use station callsign as the common field for vlookups to enable rolling up

By exporting data from Triton at the station level, you can create a flexible report that can be used to show different hierarchies easily and efficiently: station, market, format, and all company views can be shown by rolling the data up. By using station callsign as the lookup key, you can apply categories such as market and format to your station level data with vlookups in a way that is easy to update (see illustration, below).

Why not just export market and format groupings from Triton? Because station metadata can and will change at different points in time. For example, a station might change format. In order to action a change in format in your model, if you are using a vlookup to populate "Format," you can just edit the format of the station in your station attributes tab from which your Format vlookup formula populates, and your model will be updated. This method is easy, faster, and less error-prone than if you had to edit and change many rows of historical data.

This example shows "Format" updated from an attributes tab (called "Station Properties"), using a lookup from callsign. Using a lookup like this to attribute metadata to each station enables changes in future to be made quickly and efficiently.

Suggestion 3: Use a single tab as the source for pivot tables, use those pivot tables as the source for the graphs, and use slicer buttons on your pivot tables to give your report interactivity

Using pivot tables and slicer buttons is the easiest ways to make your report full of informative, interactive graphs.

Once your model is setup using "data vault" tabs, and you are joining and populating metadata by callsign, it's time to build out what your report will look like.

It's recommended to use pivot tables as the basis for all graphs and charts, working from your single data vault tab as the source of all pivot tables. Using pivot tables that draw data from a single tab allows you to (1) construct graphs very easily, and (2) control all of those graphs with a single slicer button set. .

Make a new pivot table for each graph on your report. The pivot tables don't have to be visible; they can be hidden away on tabs that aren't ssen by the report viewer, with only the slicer buttons and graphs that the pivot tables control being visible.

In the simple example in the illustration below:

• A pivot table has been built based on the data vault table;

• Slicer buttons are linked to the pivot table on the "Market" field;

• The user can now view all of the Markets combined, or cycle through the different markets, comparing their performance over the same metric of TLH by Quarter.

In a real report, this principle would be expanded to encompass many more graphs, displayed in a dashboard style layout on an Excel tab, with the Slicer controlling all of the pivot tables that the graphs are derived from.

Using a single data tab as your report's source allows you to create pivot tables that group and sum data (in this simple example, TLH by quarter by market), generate graphs from those pivot tables, and then apply slicer buttons to the graph: The report user can then toggle through views of TLH by quarter by market on the graph. When extrapolated to a complicated dashboard style graph layout, a user could toggle through a multitude of displayed metrics by Station, Market or Format.

Suggestion 4: Automate the production of PDF slicer views of your report using a Visual Basic script

You might wish to create PDF versions of your Excel reports for distribution. For example, you might want to produce four different PDFs to show a view of each Market from a report like the one in the illustration above. This method of reporting avoids the problems that you might encounter when trying to distribute a large and complex Excel file to multiple users.

With a relatively simple Visual Basic script, Excel can automatically run through the process of switching slicer selections and saving-off a PDF of that view, repeating until selections in the slicer have an accompanying PDF produced. The generated PDFs can then be distributed to your audience. If you are a radio publisher with tens or hundreds of stations, automating this process of creating and saving-off a PDF dashboard report for each station, market and format will save a lot of time spent on an otherwise laborious manual task.