Tutorial: Create a monthly track royalty report using Google Sheets

This tutorial assumes that you a a basic knowledge of pivot tables in google sheets and have imported your CSV data into Google Sheets

Use a pivot table and build the track listings

1. Import your CSV data and format dates by month in a table in Google Sheets. If you haven’t done that see this tutorial for details
   Tutorial: work with CSV reports in Google Sheets

2. Select any cell in your data table

3. Choose Insert > Pivot table. Create a new sheet

4. Click Add button next to Values in the Pivot table editor. Select total_royalty_revenue (that is the total royalties generated after Labelcaster’s commission) You should see a total royalty amount in the table

5. Click Add button next to Rows again the Pivot table editor, choose track_title You should now see a table of tracks and royalty amounts

6. Click Add button next to Rows again the Pivot table editor, choose ISRC Uncheck Show totals for IRSC. This keeps the track and ISRC on the same line

7. Click Add button next to Rows in the Pivot table editor, choose track_artist Uncheck Show totals for track_artist
   

You should have a pivot table that looks something like this

Adding a filter to see just a single month

You probably want to the report to only show data for a single month. Set up a filter to do that.

1. Click Add button next to Filters in the Pivot table editor

2. Choose report_date

3. Click the pop-up menu under report_date in the Filters section. It says “Showing all items”
   1. Click the Clear text to deselect all values

   2. Pick one month that you want to the report to show, for example 2024-12

   3. The data in the pivot table just shows values for your chosen dates

Cleaning it up and formatting

1. Adjust the column widths to see all the data
   1. Select columns A, B, C and D

   2. To auto-expand the column wide to fit the contents, double click the divider line between any of the columns, for example between A and B

2. Make the numbers easier to read. Format the numbers to be Euros.
   1. Select cells with number values (column D)

   2. Choose menu item: Format > Number > Custom currency

   3. Choose the Euro sign and two decimal places and Apply

   When you format like this the values round to 1/100ths, so they can round up or down a bit depending. Some tiny amounts will round to zero.

An alternative— show tracks grouped and summed by artist

You can rearrange the rows in the pivot table to group tracks by artist with jsut a few clicks to the above chart.

1. In the rows section of the Pivot table editor, drag on the track_artist card and drag it up above the track_title card
   1. You select the card by clicking a dragging on the text title section (right on track_title)

   2. Make sure you drag it above the other cards and not on top of them as it will replace them if you do. The other card turn green if you are about to replace them

2. You ‘ll want to resize the columns to fit the new data order. Repeat step 1 in Cleaning it up above

3. You can click on the minus sign (-) next to the artist name to fold away the detail data.