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.
To start the process, select Upload Sales File, the small light-blue button towards the top of the screen, as shown here:
The following screen will then appear:
Using the Choose File button, browse your computer for the appropriate file. When you have found it, select this file and then choose the file Input Type. 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. Please note that if you scroll further down this article, 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. For information about how to allocate a sales type to an organisation, go here. Here is a screen shot of the field we mean, which can be found in Contacts Admin - Organisations - click into the particular organisation - Sales and Returns. In the example below, we've allocated 'Wholesale' for Gardners Books.
Currently, Stison will only accept the import of Excel files that are formatted in the Excel 97 - 2003, which are recognisable by the .xls on the end of the filename. Current Excel files that have an extension on the end may .xslx will result in an error on upload. To convert from xlsx files to xls all you need to do is open the file in Excel and then choose File / Save as and choose Excel 97 - 2003 Workbook (*.xls) file type that can be found in the Save as File Type drop down.
Once the file is located click on Update.
The system will read the first few lines of the file and attempt to map the fields to the relevant fields within the database.
The system will not allow you to upload documents with the same name as a previously uploaded file. It will give you the warning: This dated sales file has already been updated.
Royalties Manager will now take you to the sales import screen outlined below:
The following information could be in your spreadsheet when you upload it, bearing in mind that we are assuming that you are uploading a sales file with more than one retailer in it as supplied from your Distributor. Also in our example, we have not included Returns. If you do include Returns, then you'll need to map that with the system as well.
Retailer and Retailer Account No.
By the time you are uploading sales spreadsheets to your Sales and Royalties Manager, you have probably already entered the retailer names and account numbers within your Contacts Admin. Please note that if you add retailers on your spreadsheet that haven't yet been added to the system, the system will automatically generate new organisation records for them and take the Sales Type from your spreadsheet to populate that as the default.
ISBN 13 and Book Title
If you do not have a Date Sold column in your spreadsheet, then the system will automatically populate with the current date of upload (eg Default Date Sold). It is recommended that you upload a spreadsheet with the column of Date Sold in place.
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 red warning.
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.
Net Income Value (Net Returns Value should be added if you are uploading Returns)
This is the total payment received for the order, from which the system will work out the customer's discount percentage. 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 clicking passed the warning, or you can back track to make sure the information is accurate on the spreadsheet.
This is where you establish whether the uploaded file includes Sales, Returns or both types of transactions, i.e. Mixed. Normally this will remain at mixed - in other words you'll have both kinds of transactions on the same spreadsheet.
This relates to the different kinds of sales you can make, eg. Wholesale, Home, Export, etc. Without this information on the spreadsheet, the system may not be able to upload your data, particularly if you are a smaller publisher.
The Sales Sheet mapping
Following on from this, you will then need to map the columns in your spreadsheet to the required fields before processing it into the system.
The system stores a template per Sales organisation, so it 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.
PLEASE DO CHECK THE COLUMN HEADINGS EVERY TIME IN CASE CHANGES HAVE BEEN MADE.
To map the columns from the spreadsheet to your system, you need to select the column heading by clicking on it, as per the diagram below:
The following table provides information about each field so you can see what types of information you are able to upload through Royalties Manager. The example we showed you above only has some of these fields.
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.|
|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.|
|Net Income Value||The total income value for the sales file. This should be a default field used for values imported. You can import negative values, so if a consolidated field is available, this is should be used.||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.|
|Units Sold||The quantity of products sold.This can be a negative value, so should be the default for a majority of sales channels.||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.|
Once the file is ready and you are sure that your columns are correctly mapped, then click Process File. The file upload process will commence.
If the file is successful, you will be returned to the Sales Screen and the sales data will be imported. You will notice that at the top of the screen there will be information as shown below:
There are three points of note in the screenshot we've cropped above:
- 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.
It is important to check every time that the Sum Total and the Total Copies sold match what you were expecting from the imported file.
Information about the options available on this screen can be found at Sales Import.