The most common way of uploading sales data into the system is via a sales report that is sent to you from your sales channels (eg your distributor, etc). In order to upload sales data from any source, they will need to be configured as a supplier within the Title Manager system. Details demonstrating this process can be found on the Setup a Distributor page.
It is very important to note that when uploading a sales file, we expect that spreadsheet to be an itemised list of retailers/sold dates against individual title ISBNs (whether they be for returns or for sales), for one retailer or for mixed retailers. It is possible, however, that your distributor might send you a report with total sales per title with no retailer/organisation included at all. There are ways around this on the system, thought we don't recommend it. We have separate instructions regarding how to upload a file that does not differentiate between customers.
Follow these instructions for uploading a sales file that does have retailer information on it.
1) Click on the Upload Sales File, the small light-blue button towards the top of the screen, as shown here:
The following screen will then appear:
2) Using the Choose File button, browse your computer for the appropriate file and select it.
3) Choose the file type within the Input Type field. If you choose CSV or Text file, you will be asked for additional information about the delimiter being used to separate the records. This will not be the case when uploading an XLS spreadsheet, which we will demonstrate in this article. Further down, you will see a screen shot of the suggested fields to include in your spreadsheet.
Please note that if you are a small publisher, that you will need to ensure that every organisation/customer you add onto your system has been allocated a Sales Type (eg. Wholesale, Trade, Home, etc). The system will be using that information to map the rest of the data you input.
4) Once the file is located, click on Update. Your Royalties module will now take you to the sales import screen outlined below:
NB: You cannot upload documents with the same name as a previously uploaded file. It will give you this warning:
5) There are a set of fields to consider first before you map the columns in the spreadsheet. They are as follows:
- Default Date Sold: This will automatically populate with the current date
- Upload Type: Choose either Mixed, Sales or Returns
- Distributor: Select the relevant distributor from the drop-down menu. If this is an export file, perhaps it will be from your US distributor.
- Sales Organisation: Default is -Multiple Organisations- If your file only has one organisation in it, then you can select the relevant one from the drop-down menu. Please ensure that the organisation has been added in Organisation Admin.
- Sales Type: Default is -Use Organisations Sales Type-
- Currency: This should be set at your default currency. Change if necessary to the file you are uploading.
- Date Format: Decide either the US or the UK format.
If the file you are uploading has Rights sales in it, then you will want to click onto the little icon beside Sales Type, as shown in the image below. We've marked it with a red circle. This will then open up the field called Rights sales, which you will want to tick. This will ensure that when the sales file is uploaded, that the information is attached to the accurate royalty rule. Everything else should stay as is.
6) The system will read the first few lines of the file and attempt to map the fields to those in the database. To adjust them to suit your spreadsheet, select the column heading by clicking on it, as per the image below:
The following table provides information about each field so you can see what types of information you are able to upload through the Royalties module.
Ignore this Column
|Used to ignore the data and not import - you may have columns on your spreadsheet that you don't need uploaded to the system.|
Match to ASIN for Amazon sales reports
|Y - If no ISBN|
|Book Title||Book Title|
|Country||Country of Sale|
|Comment||Any Comment you wish to appear by the sale (often used for further detail for rights sales)|
|Currency||Currency that sales are being reported in. If this is used, then it will be mandatory to add in an exchange rate. (If importing a single currency then this can be set with the import options. Also if importing a single currency that is already set up as your default currency, you won't need to include this information in your spreadsheet at all).|
|Customer Type||NOT CURRENTLY USED|
|Date Sold||Date of sale - Will override the default date sold. It is recommended that you upload a spreadsheet with the column of Date Sold in place.|
|Discount||This is the sales discount if you wish to import rather than allow the system to work it out. Not many publishers do this.|
|Exchange Rate||Exchange rate to be used to convert sale amount to the base currency for the royalty calculations.|
|RRP Without this information, the system will not be able to calculate the discount given in the sale. Please note that if the price on the spreadsheet does not match the price in Title Manager, the system will flag up a warning that you can ignore.|
|Net Income Value||The total income value for the sales file. You can import negative values, so if a consolidated field is available, this should be used. The system will use this figure to work out the customer's discount percentage on the sale. If you have set the General Config up to flag warnings if an order's discount is really high (eg 60% or above), then the upload will trigger that warning if it catches something from the spreadsheet. You can still process the spreadsheet by ignoring the warning, or you can back track to make sure the information is accurate on the spreadsheet.||Y|
|Net Returns Value||If your sales partner doesn't provide a consolidated sales value, you can use this to separate out Returns.|
|Retailer||The name of the customer|
|Retailer Account Number||The account number of the customer|
|PO Nu. / Invoice Number||A reference number that can link back to sales data|
|Purchase Price||This is the net sales price of the book sold; this should exclude all costs, as it is the price the system will pay Royalties on.|
|Sales Type||The sales type that the sale should be registered against for the purposes of the value of payment to be made to the contributor(s). See Sales Types for more information.|
|Type||This is whether the item is a Sale or Return. Label the information on the spreadsheet accordingly. If you are uploading a mixed file, then you can have this information in the same column.||Y|
|Units Sold||This is the total amount of copies bought in the sale (or returned). Again, this figure will help the system to calculate the customer's discount. Also, without this information, the system may not be able to upload your data.||Y|
|Units Returned||Quantity of units returned, only to be used if no consolidated sales file and returns are reported in a different row / column.|
7) Once you are sure that your columns are correctly mapped, then click Process File.
Please note that the system stores a template per sales organisation once you've uploaded a sales file once. This means that the system will remember the column mappings next time you receive a sales spread sheet from that same organisation. Therefore you do not need to follow this process every time you upload a sales file.
Your file will be placed in a queue.
Clicking onto the View Queue button will take you here:
Click the Refresh button if you need.
The upload bar will turn one of the following colours:
- Green line - file is ok to process.
- Orange line - file can be processed, though there are some issues to review.
- Red line - the file produced errors in the check phase.
If the line is orange, then click into the View Issues button and you will be taken to a screen like this:
PLEASE DO CHECK THE COLUMN HEADINGS EVERY TIME IN CASE CHANGES HAVE BEEN MADE.
From here you have four options:
- Accept the warnings and continue - this is the final stage. The file will then take a few seconds to upload.
- Remap & reprocess the file - if for some reason you have mapped the columns incorrectly, this button will return you to the mapping area.
- Recheck the file - if you have missing isbns, add the isbns and then resubmit the file for checking.
- Delete file - to start again.
Any files that have no issues (green) will proceed onto the next step.
It is important to check every time you upload a file that the Sum Total and the Total Copies sold match what you were expecting from the imported file.
On second pass the system has now takes the checked rows and inserts the sales data. The sales value and copies sold from the spreadsheet are stored against the filename. There is now a new green button at this stage that reads: I confirm the values are correct. You may use this as a marker that you have checked the file copies and value totals between the system and your original spreadsheet.
This should be done with every file!
It serves as a check of the system against the file. Sometime values are imported with incorrect precision, which results in false numbers having royalties paid on them. Cross referencing the numbers uploaded against what is on the spreadsheet is the best way to ensure accuracy. If the figures don't match, then you must try uploading it again.
In this example, our values are accurate. The uploaded sales files: "November 2020 sales" has been processed. This is indicated with a green check mark. You can see that the user name who completed and processed this upload is indicated on the system as well. This will help with troubleshooting any issues.
If for some reason you hit the back button and try to upload the file while it's already in the queue, you will get the following error message:
Click onto View Queue to see the status of the upload.
Click onto Sales Admin to return to the sales list screen.
When the file is successful, you will be returned to the Sales screen where you should be able to see the sales listed as individual items with totals on the top.
There are three points of note in the screenshot we've cropped above in terms of ensuring that the files has uploaded accurately:
- Indicates the Sum total of the value imported.
- Indicates the Total sold copies imported.
- Shows the name of the file that you've uploaded. This will be stored here. It also gives you the option to delete the file if you need. Please note that if you cancel an upload before clicking on Process File, the item will still appear in the drop down menu listing all the uploaded files.
You can also access the queue at any time from the man sales list screen by clicking on the green Sales File Queue button on the right-hand top corner. We've circled it in the screenshot below:
Uploading a sales file can sometimes be a process of trial and error. If after several attempts you still cannot get the system to upload your spreadsheet, email: email@example.com and someone from the company will provide assistance.
When your sales file is uploaded successfully, then presumably the next step will be Item 6: to generate a pay run.