Have more questions? Visit the community forum or raise a ticket

Docs > How-to > Import

1. Add data to the sales template spreadsheet

  • Each row represents one sale of one product in one channel. A single row can include sale and return information.
  • Do not change the column headers. They must be exactly the same as the template.
  • Do not add any other columns.
  • You may delete unnecessary columns, but you must have the columns Product ID type, Product ID, Channel ID, and Sales date to identify the spreadsheet as an import of sales. Do not delete these columns.
  • Each row must have data in all four of those columns as well as one of Sales value, Return value, Original sales value or Original return value to be valid.
  • The spreadsheet can contain sales for multiple works and multiple sources.

New sales only

One or two new sales transaction record(s) is/are created for each row in your spreadsheet. If a row includes both sales and return data, two sales transaction records will be created. Currently, you cannot update existing sales using the importer.

The sale source must be a contact

The sale will be associated with a source, which needs to be a company contact with the same organisation name in your system. If a company contact with the name you use in the Source column does not yet exist, one will be created.

2. Sale columns

Product ID type

  • Select one of four product ID types from the spreadsheet template’s dropdown menu: ISBN, ASIN, Internal reference, Consonance ID.
  • Product ID type defines the validations performed on the product ID.

Product ID

  • Must be a valid value of the selected product ID type.
  • Bulk sets of ASINs, ISBNs, product internal references and Consonance IDs can be created using custom reports.

Channel ID

Sales date

  • The date must be in the format YYYY-MM-DD for absolute clarity.

List price

  • Must be a positive decimal
  • List price is a manual entry field because of the possiblity of historical price changes. However, this field is only required if the product in that channel will have royalties connected to the list price.

Source

  • The name of the company that sold the products (e.g. Waterstones)
  • If a company contact with the name exists, the sale will be associated with that contact.
  • If no company with the name exists, one will be created automatically.
  • This field does not affect royalty calculations but can be useful for reporting.

Sales quantity

  • Must be a positive integer.
  • Required if there is a sales value.

Sales value

  • Must be a positive decimal.
  • Must be in your default currency. (See original currency section below.)
  • Required if there is a sales quantity.

Return quantity

  • Must be a positive integer. This will convert to a sale transaction with a negative quantity.
  • Required if there is a return value.

Return value

  • Must be a positive decimal. This will convert to a sale transaction with a negative value.
  • Must be in your default currency. (See original currency section below.)
  • Required if there is a return quantity.

Negative units, positive value? Or negative value, positive units? If you are importing aggregated transactions, it’s possible that you will have sales records to import which are not both positive or both negative. To work around this, reformat your data as in the images below.

If you have mixed positive and negative numbers like this:

Reformat your data like this:

Country code

  • To track the sale’s country of origin, use the appropriate ONIX country code. View ONIX country codes. This field is not required and can be left blank.

Original currency group

  • Sales value and return value column entries, as detailed above, must be in your default currency. If you have sales income in a different currency, use the original currency group columns to either track your manual conversion rate or to have Consonance convert for you.
  • These are optional fields for sales income that is not in your default currency.
  • Consonance conversion: If you include an original currency code and an original sales value or original return value amount, with no amount in sales value or return value column, Consonance will convert the amount in the original value column using the sales date.
  • Manual conversion: If you include an original currency code and an amount in the sales value or return value columns – including an amount of 0 – no conversion will take place. This means the amount in the original value column will be for your historical record only, and will not be used in royalty calculations. Royalty calculations happen in your default currency only.

Original currency

Original sales value

  • Must be a positive decimal.
  • Without a corresponding amount in the Sales value column, the amount in the Original sales value column will be used as the basis for Consonance’s conversion for the amount that will be imported into the Sale value column.
  • With a corresponding amount in the Sales value column, the amount in the Original sales value column is for your historical records only.

Original return value

  • Must be a positive decimal.
  • Without a corresponding amount in the Return value column, the amount in the Original return value column will be used as the basis for Consonance’s conversion for the amount that will be imported into the Return value column.
  • With a corresponding amount in the Return value column, the amount in the Original return value column is for your historical records only.

3. Sales importing notes

Historical sales

The goal for importing historical sales is to present accurate total historical sales on royalty statements and to trigger any unit- or sale-value-based escalators. That can be accomplished with one all-time sales aggregation per product or broken down in whatever way is easiest with your existing records.

Deleting a work with sales

Once you have created a sale for a work, the work cannot be deleted. More details here. If a sale exists for a book, it is a historical record that should be preserved.

Automated sales ingestion via FTP

It is possible for us to custom-build automated sales ingestion, but this will heavily depend on the automated sales reports generated by your customers and is a chargeable activity. At this time, there is no way to automate sales importing from Amazon. We know this hurts and hope to relieve the pain in the future.

Retrieving imported Excel sheets

Any import’s data is available to view within the system in a grid replicating the Excel document, unless you delete the import. However, the Excel document itself is not immediately available for re-downloading. It is stored deep in our third party asset storage so, in rare circumstances, it could be retrieved by raising a support ticket.

Deleting imported sales

In exceptional circumstances, it is possible to delete all of the sales generated from an approved sales import, but must be done by us. Raise a support ticket.

All docs about Import