Analyze SoundExchange reports in NPR format with a spreadhseet

This topic is for Admin and Editor users at stations that report to SoundExchange via NPR. Sometimes NPR gives an error about a Playlist Log File that you sent from Spinitron. If you want to examine the data you sent and try and find what might be causing the error, you can look at it using a spreadsheet app such as Microsoft Excel or Google Sheets.

(Note: What NPR calls a Playlist Log File, we call a report or SoundExcnage report.)

You can create and recreate reports in Spinitron as often as needed. The reports aren’t saved or sent anywhere unless you take that extra step. So it is safe to experiment. For example, you might create a report, examine it, discover something you need to fix in your playlists in Spinitron, and then, after you made those changes, you create a new report and discard the previous one.

1. Prepare a spreadsheet

In your spreadsheet app, create or open an empty spreadsheet and keep it ready for copy-paste.

2. Create a report

As Admin or Editor user logged in to Spinitron:

  • In the Report menu, choose SoundExchange.

  • Choose a duration for the report period, e.g. 2 weeks.

  • Choose an end date for the report.

    (Note: The report includes all of the spins played on the end date. A 2-week report that ends on the 30th of the month therefore includes all the spins on the 17th of that month and none from the 16th.)

  • Click the blue button: Preview Report.

3. Copy the report to clipboard

4. Paste into your empty spreadsheet

Locate the spreadsheet you prepared in 1. above, select a cell, e.g. A1 (the top left cell), and paste. The result might look something like this.

Check that you have the right number of rows. There should be one more than Spinitron reported because the first one is a row of column headings.

5. Extra fun with dates and times

The values for Start Time and End Time in the pasted data are just strings in the spreadsheet. If you want to use the spreadsheet to calculate with them as date-time values you need to convert them using a function. I found that a function like this in a new column works.

=DATE(MID(E2,1,4), MID(E2,6,2), MID(E2,9,2)) + TIME(MID(E2,12,2), MID(E2,15,2), MID(E2,18,2))

Now you can use those to perform comparisons or calculate differences between two different date-time values.

1 Like