We are an independent music label and need a tool that processes earnings reports (tab delimited text files), stores them in a database, matches them with our music catalogue and then shows the statistics a user friendly way. A sample report and mockups of the different screens are attached in this job posting. If you feel the budget is too low, please make us an offer.
The reports contain earnings from mp3 sales and streams from itunes, spotify and so on. We receive two reports for each month - downloads report and streams report. So there has to be different upload options for downloads and streams.
1. So first of all we would have an ARTISTS tab, where we would create profiles of all our artists (See Mockup 1). We click create new profile and enter artist name, email, and default payout share (Mockup 2).
2. Then we have SONGS tab (Mockup 4), where we add the information for the song - Artist (dropdown menu with the artists from the artists tab), featuring artists (every artist in new field and separatly saved in the DB; option to add more then one featuring artist - may be a + sign next to the box or something like that), Title, ISRC, UPC, SHARE (the share field is filled automatically with the default share of the respective artist, but is editable). The ISRC field would generate automatically but is editable.
An ISRC looks like this: BGA741600001. The first component is always the same: "BGA74", then comes the year "16", and then the number of the track "00001". The first track, that is entered in the DB in 2016 gets number 00001 so its ISRC is BGA741600001, the second track - number 00002 - ISRC BGA741600002 and so on.
When we choose the main Artist from the dropdown menu, this song is assgined to the artists profile and is shown in it (Mockup 3).
When we enter all artists and all songs, we would be able to go to the ARTIST tab, open Artist X's profile and find there all his songs and the total earnings of every song for the respective period (Artist's earnings = Income from music store x Artist Share for the particular song)(Mockup 3). The period is adjusted from a calender selector.
When we click on a SONG, the details for that song opens and shows the information (ARTIST, TITLE, ISRC, UPC, ARTIST SHARE) and earnings (Mockup 5).
Processing the reports file and matching with the songs in the db: The information from the reports has to be matched to the information from the songs database by ISRC or UPC. This means the script has to look for the ISRC of Song 1 in the report and when it finds a match, it would import the data from the report to the respective songs database.
In the report there is only one field - ISRC/UPS. The problem is, that some stores use the UPC to identify the song and some use the ISRC. So the script should look up both ISRC and UPC from the songs db in this field.
Also in the ISRC/UPC field of the report there are some stores that put dashes between the ISRC components like this BGA7-416-00001. The script should discard the dashes and any other punctuation in this field in order to match the record with our database, where we would enter the ISRC without any punctuation.
If the script can't identify some of the records in the report, a message with the unidentified records is shown and we can manually assign each record to a specific song.
3. Then we have the downloads tab (Mockup 6), which is basicaly a big table, containing all songs and all earnings from all periods. Every coloumn has a filter. The displayed results can be exported in excel. There is a period selector. The coloumns are:
Store (matches same field from report), Sales (matches same field from report), Period (matches same field from report), UPC/ISRC (matches same field from report), Artist (matches DB), Title (matches DB), Units (matches same field from report), Country (matches same field from report), Single Price (matches same field from report), Total Income (= Unit x Single Price), Artist Share (matches field from Song DB), Artist Revenue (=Total Income x Artist Share).
4. The next tab is the stream tab. It is the same as the downloads tab, but shows only the stats from streams reports.
We would preffer bootstrap or some other nice looking framework.