Sales Data Needed to Compute Royalties - Revision history

PubGizmoSysop: /* Quantity Returned */

2025-08-02T19:55:51Z

Quantity Returned

← Older revision		Revision as of 15:55, 2 August 2025
Line 16:		Line 16:
	# '''Quantity Sold:''' the number of books sold or returned (expressed as a negative value). Note that some sheets separate counts of books sold and books returned into individual columns, and may offer an additional net sales column, that adds these values together. Be sure you check all available columns, and define this column, and '''Quantity Returned''' (see below) appropriately.		# '''Quantity Sold:''' the number of books sold or returned (expressed as a negative value). Note that some sheets separate counts of books sold and books returned into individual columns, and may offer an additional net sales column, that adds these values together. Be sure you check all available columns, and define this column, and '''Quantity Returned''' (see below) appropriately.

			=== Quantity Returned ===
			Quantity returned may be represented on an import sheet in three different ways; here's what to do in each case:

			# '''as a negative value in the quantity column:''' import under the Quantity heading. PubGizmo will import positive amounts as sales, and negative amounts as returns. (If there is ''also'' a quantity return column, ignore it – do not import; see item '''3''', below).
			# '''in a dedicated "quantity returned" column:''' ensure there are no negatives (returns) in the "Quantity" column, and select "Quantity Return" for this column. These values may be positive or negative; in either case, PubGizmo will treat these amounts as returns, and represent them as a negative quantity.
			# '''both with negatives in the quantity column, ''and'' a "quantity returned" column:''' in this case, your sheet contains redundant info (quantity returned is represented ''both as a negative in the quantity column, and a value in the quantity returned column''). Be careful not to import both columns. Choose option '''1''', above: import the quantity column under Quantity, and ignore the column with quantity returned values.
	== Required Data, in Either a Column or Defined for the Entire Sheet ==		== Required Data, in Either a Column or Defined for the Entire Sheet ==
	The following data is required, but may be unavailable in a column on the sheet. When that happens, you can set at import a value to apply to all the sales on the sheet.		The following data is required, but may be unavailable in a column on the sheet. When that happens, you can set at import a value to apply to all the sales on the sheet.
Line 25:		Line 31:

	== Optional Data ==		== Optional Data ==
	This data is "nice to have," and can aid in sales reporting, but isn't required to compute royalties.		This data is "nice to have," and can aid in sales reporting and tracking, but isn't required to compute royalties.

	# Invoice number		# Invoice number
Line 31:		Line 37:
	# Province or State		# Province or State
	# End Customer		# End Customer

	~~=== Quantity Returned ===~~
	~~Quantity returned may be represented on an import sheet in three different ways; here's what to do in each case:~~

	# '''as a negative value in the quantity column:''' import under the Quantity heading. PubGizmo will import positive amounts as sales, and negative amounts as returns. (If there is ''also'' a quantity return column, ignore it – do not import; see item '''3''', below).
	# '''in a dedicated "quantity returned" column:''' ensure there are no negatives (returns) in the "Quantity" column, and select "Quantity Return" for this column. These values may be positive or negative; in either case, PubGizmo will treat these amounts as returns, and represent them as a negative quantity.
	# '''both with negatives in the quantity column, ''and'' a "quantity returned" column:''' in this case, your sheet contains redundant info (quantity returned is represented ''both as a negative in the quantity column, and a value in the quantity returned column''). Be careful not to import both columns. Choose option '''1''', above: import the quantity column under Quantity, and ignore the column with quantity returned values.

PubGizmoSysop at 19:33, 2 August 2025

2025-08-02T19:33:00Z

← Older revision		Revision as of 15:33, 2 August 2025
Line 13:		Line 13:
	== Required Columns ==		== Required Columns ==

	# '''ISBN:''' the unique [[wikipedia:ISBN\|International Standards Book Number]] that identifies each edition.		# '''ISBN:''' the unique 13-digit [[wikipedia:ISBN\|International Standards Book Number]] that identifies each edition; note that PubGizmo does not currently process the obsolete ISBN-10 format.
	# '''Quantity Sold:''' the number of books sold or returned (expressed as a negative value). Note that some sheets separate counts of books sold and books returned into individual columns, and may offer an additional net sales column, that adds these values together. Be sure you check all available columns, and define this column, and '''Quantity Returned''' (see below) appropriately.		# '''Quantity Sold:''' the number of books sold or returned (expressed as a negative value). Note that some sheets separate counts of books sold and books returned into individual columns, and may offer an additional net sales column, that adds these values together. Be sure you check all available columns, and define this column, and '''Quantity Returned''' (see below) appropriately.

Line 31:		Line 31:
	# Province or State		# Province or State
	# End Customer		# End Customer
	# '''Quantity ~~Returned:~~''' ~~this may be defined in~~ a column ~~separate from~~ '''~~Quantity Sold~~'''. ~~If you use it, ensure~~ '''~~Quantity Sold~~''' ~~contains outgoing sales only, not~~ returns, i.~~e., no~~ negative, amounts. ~~Sometimes there is a Net Sales~~ column ~~which represents~~ '''~~Quantity Sold~~''' ~~plus~~ '''~~Quantity Returned'~~''. Do not ~~use this column if you are using~~ both '''~~Quantity Sold~~''' and ~~'''Quantity Returned''' columns~~.
			=== Quantity Returned ===
			Quantity returned may be represented on an import sheet in three different ways; here's what to do in each case:

			# '''as a negative value in the quantity column:''' import under the Quantity heading. PubGizmo will import positive amounts as sales, and negative amounts as returns. (If there is ''also'' a quantity return column, ignore it – do not import; see item '''3''', below).
			# '''in a dedicated "quantity returned" column:''' ensure there are no negatives (returns) in the "Quantity" column, and select "Quantity Return" for this column. These values may be positive or negative; in either case, PubGizmo will treat these amounts as returns, and represent them as a negative quantity.
			# '''both with negatives in the quantity column, ''and'' a "quantity returned" column:''' in this case, your sheet contains redundant info (quantity returned is represented ''both as a negative in the quantity column, and a value in the quantity returned column''). Be careful not to import both columns. Choose option '''1''', above: import the quantity column under Quantity, and ignore the column with quantity returned values.

PubGizmoSysop at 15:07, 25 February 2024

2024-02-25T15:07:54Z

← Older revision		Revision as of 11:07, 25 February 2024
Line 18:		Line 18:
	== Required Data, in Either a Column or Defined for the Entire Sheet ==		== Required Data, in Either a Column or Defined for the Entire Sheet ==
	The following data is required, but may be unavailable in a column on the sheet. When that happens, you can set at import a value to apply to all the sales on the sheet.		The following data is required, but may be unavailable in a column on the sheet. When that happens, you can set at import a value to apply to all the sales on the sheet.
	# '''Invoice ~~date~~, date of sale, process date:''' the date to use to determine the royalty period within which the sales transaction falls. Note that if this date is not available, PubGizmo will allow you to define a single date for the entire import, and each imported sales record will assume that date. For example, you could use the date 31 December 2024 to ensure the sale is recorded in a royalty year that runs from 1 January 2024 – 31 December 2024.		# '''Invoice Date, date of sale, process date:''' the date to use to determine the royalty period within which the sales transaction falls. Note that if this date is not available, PubGizmo will allow you to define a single date for the entire import, and each imported sales record will assume that date. For example, you could use the date 31 December 2024 to ensure the sale is recorded in a royalty year that runs from 1 January 2024 – 31 December 2024.
	# '''Amount ~~remitted~~:''' the amount the vendor pays your press for the sale of the book. This is necessary only for royalties based on ''net''. To calculate royalties based on ''list price'', the Edition record must include a list price for the region where the book has been sold (defined in the '''Country of Sale''' column, below).		# '''Amount Remitted:''' the amount the vendor pays your press for the sale of the book. This is necessary only for royalties based on ''net''. To calculate royalties based on ''list price'', the Edition record must include a list price for the region where the book has been sold (defined in the '''Country of Sale''' column, below).
	# '''Country of Sale:''' [[wikipedia:ISO_3166-1_alpha-2\|the two-letter ISO code]] for the country where the sale occurred (although PubGizmo will attempt to look this up if the country is spelled out in full).		# '''Country of Sale:''' [[wikipedia:ISO_3166-1_alpha-2\|the two-letter ISO code]] for the country where the sale occurred (although PubGizmo will attempt to look this up if the country is spelled out in full).
	# '''Currency:''' the currency in which the sale occurred. ~~Llike~~ '''Country of Sale''', these must be in correct [[wikipedia:ISO_4217\|ISO format]], e.g., CAD = Canadian dollar, USD = American dollar, EUR = euro, etc. If your vendor does not provide these in the correct format (looking at you, [https://utorontopress.com University of Toronto Press]), please talk to your sales rep about using it for your exports, otherwise you will have to convert these in all your sheets before import.		# '''Currency:''' the currency in which the sale occurred. Like '''Country of Sale''', these must be in correct [[wikipedia:ISO_4217\|ISO format]], e.g., CAD = Canadian dollar, USD = American dollar, EUR = euro, etc. If your vendor does not provide these in the correct format (looking at you, [https://utorontopress.com University of Toronto Press]), please talk to your sales rep about using it for your exports, otherwise you will have to convert these in all your sheets before import.
	# '''Exchange Rate:''' (only if the sheet contain sales in alternate currencies) for sales not in your native currency, an '''Exchange Rate''' needs to be defined, to convert the funds. If this column is not defined, PubGizmo will look up the exchange rate in effect on the '''Invoice ~~date~~, date of sale, process date''' (see item #1), and convert it to your native currency.		# '''Exchange Rate:''' (only if the sheet contain sales in alternate currencies) for sales not in your native currency, an '''Exchange Rate''' needs to be defined, to convert the funds. If this column is not defined, PubGizmo will look up the exchange rate in effect on the '''Invoice Date, date of sale, process date''' (see item #1), and convert it to your native currency. If your sheet includes a column for the sale in your native currency, you can use that for the Amount remitted field, and omit the '''Exchange Rate'''.

	== Optional Data ==		== Optional Data ==

PubGizmoSysop at 23:59, 30 January 2024

2024-01-30T23:59:59Z

← Older revision		Revision as of 19:59, 30 January 2024
Line 24:		Line 24:
	# '''Exchange Rate:''' (only if the sheet contain sales in alternate currencies) for sales not in your native currency, an '''Exchange Rate''' needs to be defined, to convert the funds. If this column is not defined, PubGizmo will look up the exchange rate in effect on the '''Invoice date, date of sale, process date''' (see item #1), and convert it to your native currency.		# '''Exchange Rate:''' (only if the sheet contain sales in alternate currencies) for sales not in your native currency, an '''Exchange Rate''' needs to be defined, to convert the funds. If this column is not defined, PubGizmo will look up the exchange rate in effect on the '''Invoice date, date of sale, process date''' (see item #1), and convert it to your native currency.

	== ~~Totally~~ Optional Data ==		== Optional Data ==
	This data is "nice to have," and can aid in sales reporting, but isn't required to compute royalties.		This data is "nice to have," and can aid in sales reporting, but isn't required to compute royalties.

PubGizmoSysop at 23:51, 30 January 2024

2024-01-30T23:51:39Z

← Older revision		Revision as of 19:51, 30 January 2024
Line 31:		Line 31:
	# Province or State		# Province or State
	# End Customer		# End Customer
	# '''Quantity Returned:''' this may be defined in a column separate from '''Quantity Sold'''. If you use it, ensure '''Quantity Sold''' is contains outgoing sales only, not returns, i.e., no negative, amounts. Sometimes there is a Net Sales column which represents '''Quantity Sold''' plus '''Quantity Returned'''.		# '''Quantity Returned:''' this may be defined in a column separate from '''Quantity Sold'''. If you use it, ensure '''Quantity Sold''' contains outgoing sales only, not returns, i.e., no negative, amounts. Sometimes there is a Net Sales column which represents '''Quantity Sold''' plus '''Quantity Returned'''. Do not use this column if you are using both '''Quantity Sold''' and '''Quantity Returned''' columns.

PubGizmoSysop at 21:07, 30 January 2024

2024-01-30T21:07:47Z

← Older revision		Revision as of 17:07, 30 January 2024
Line 4:		Line 4:
	You will generally receive sales data in spreadsheet form (Excel or CSV) from various sources:		You will generally receive sales data in spreadsheet form (Excel or CSV) from various sources:

	* vendors like Ingram, eBound, etc.		* vendors like Ingram, eBound, iTunes, etc.
	* from your sales platform, e.g., Square, WooCommerce		* from your sales platform, e.g., Square, WooCommerce
	* exported from your accounting software, if the sales are initially recorded there		* exported from your accounting software, if the sales are initially recorded there
	* sheets you generate yourself, e.g., at a sales event		* sheets you generate yourself, e.g., at a sales event

	Regardless of the source, a minimum set of data is required to make the computations. When we are discussing spreadsheets, we mean the columns in the sheets, or, if that data is not provided in a column, as a definition to apply to the entire, e.g., if all the sales are in the US, you don't need a Country column; you indicate at the moment of import that all sales ~~are~~ Country = US.		Regardless of the source, a minimum set of data is required to make the computations. When we are discussing spreadsheets, we mean the columns in the sheets, or, if that data is not provided in a column, as a definition to apply to the entire import, e.g., if all the sales are in the US, you don't need a Country column; you indicate at the moment of import that for all sales Country = US.

	== Required Columns ==		== Required Columns ==
Line 21:		Line 21:
	# '''Amount remitted:''' the amount the vendor pays your press for the sale of the book. This is necessary only for royalties based on ''net''. To calculate royalties based on ''list price'', the Edition record must include a list price for the region where the book has been sold (defined in the '''Country of Sale''' column, below).		# '''Amount remitted:''' the amount the vendor pays your press for the sale of the book. This is necessary only for royalties based on ''net''. To calculate royalties based on ''list price'', the Edition record must include a list price for the region where the book has been sold (defined in the '''Country of Sale''' column, below).
	# '''Country of Sale:''' [[wikipedia:ISO_3166-1_alpha-2\|the two-letter ISO code]] for the country where the sale occurred (although PubGizmo will attempt to look this up if the country is spelled out in full).		# '''Country of Sale:''' [[wikipedia:ISO_3166-1_alpha-2\|the two-letter ISO code]] for the country where the sale occurred (although PubGizmo will attempt to look this up if the country is spelled out in full).
	# '''Currency:''' the currency in which the sale occurred~~; like~~ '''Country of Sale.'''		# '''Currency:''' the currency in which the sale occurred. Llike '''Country of Sale''', these must be in correct [[wikipedia:ISO_4217\|ISO format]], e.g., CAD = Canadian dollar, USD = American dollar, EUR = euro, etc. If your vendor does not provide these in the correct format (looking at you, [https://utorontopress.com University of Toronto Press]), please talk to your sales rep about using it for your exports, otherwise you will have to convert these in all your sheets before import.
	# '''Exchange Rate:''' (only if the sheet contain sales in alternate currencies) for sales not in your native currency, an '''Exchange Rate''' needs to be defined, to convert the funds. If this column is not defined, PubGizmo will look up the exchange rate in effect on the '''Invoice date, date of sale, process date''' (see item #1), and convert it to your native currency.		# '''Exchange Rate:''' (only if the sheet contain sales in alternate currencies) for sales not in your native currency, an '''Exchange Rate''' needs to be defined, to convert the funds. If this column is not defined, PubGizmo will look up the exchange rate in effect on the '''Invoice date, date of sale, process date''' (see item #1), and convert it to your native currency.

	== Totally Optional Data ==		== Totally Optional Data ==
	This data is "nice to have," and can aid in sales reporting:		This data is "nice to have," and can aid in sales reporting, but isn't required to compute royalties.

	# Invoice number		# Invoice number

PubGizmoSysop at 17:13, 29 January 2024

2024-01-29T17:13:46Z

← Older revision		Revision as of 13:13, 29 January 2024
Line 14:		Line 14:

	# '''ISBN:''' the unique [[wikipedia:ISBN\|International Standards Book Number]] that identifies each edition.		# '''ISBN:''' the unique [[wikipedia:ISBN\|International Standards Book Number]] that identifies each edition.
	# '''Quantity ~~sold~~:''' the number of books sold or returned (expressed as a negative value).		# '''Quantity Sold:''' the number of books sold or returned (expressed as a negative value). Note that some sheets separate counts of books sold and books returned into individual columns, and may offer an additional net sales column, that adds these values together. Be sure you check all available columns, and define this column, and '''Quantity Returned''' (see below) appropriately.

	~~== Required Columns~~, ~~or Defined for the Entire Import ==~~

			== Required Data, in Either a Column or Defined for the Entire Sheet ==
			The following data is required, but may be unavailable in a column on the sheet. When that happens, you can set at import a value to apply to all the sales on the sheet.
	# '''Invoice date, date of sale, process date:''' the date to use to determine the royalty period within which the sales transaction falls. Note that if this date is not available, PubGizmo will allow you to define a single date for the entire import, and each imported sales record will assume that date. For example, you could use the date 31 December 2024 to ensure the sale is recorded in a royalty year that runs from 1 January 2024 – 31 December 2024.		# '''Invoice date, date of sale, process date:''' the date to use to determine the royalty period within which the sales transaction falls. Note that if this date is not available, PubGizmo will allow you to define a single date for the entire import, and each imported sales record will assume that date. For example, you could use the date 31 December 2024 to ensure the sale is recorded in a royalty year that runs from 1 January 2024 – 31 December 2024.
	# '''Amount remitted:''' the amount the vendor pays your press for the sale of the book. This is necessary only for royalties based on ''net''. To calculate royalties based on ''list price'', the Edition record must include a list price for the region where the book has been sold (defined in the Country column, below).		# '''Amount remitted:''' the amount the vendor pays your press for the sale of the book. This is necessary only for royalties based on ''net''. To calculate royalties based on ''list price'', the Edition record must include a list price for the region where the book has been sold (defined in the '''Country of Sale''' column, below).
	# Country of Sale: the two-letter ISO code for the country where the sale occurred.		# '''Country of Sale:''' [[wikipedia:ISO_3166-1_alpha-2\|the two-letter ISO code]] for the country where the sale occurred (although PubGizmo will attempt to look this up if the country is spelled out in full).
			# '''Currency:''' the currency in which the sale occurred; like '''Country of Sale.'''
			# '''Exchange Rate:''' (only if the sheet contain sales in alternate currencies) for sales not in your native currency, an '''Exchange Rate''' needs to be defined, to convert the funds. If this column is not defined, PubGizmo will look up the exchange rate in effect on the '''Invoice date, date of sale, process date''' (see item #1), and convert it to your native currency.

	== Totally Optional Data ==		== Totally Optional Data ==
	This data is "nice to have," and can aid in sales reporting.		This data is "nice to have," and can aid in sales reporting:

	# Invoice number~~: [optional]:~~		# Invoice number
	~~# Currency∆~~
	~~# XR~~
	# City		# City
	# ~~Prov~~		# Province or State
	# ~~Country∆~~		# End Customer
	# ~~MidC~~		# '''Quantity Returned:''' this may be defined in a column separate from '''Quantity Sold'''. If you use it, ensure '''Quantity Sold''' is contains outgoing sales only, not returns, i.e., no negative, amounts. Sometimes there is a Net Sales column which represents '''Quantity Sold''' plus '''Quantity Returned'''.
	~~# EndC~~
	~~# QReturn~~
	~~# Title~~
	~~# SoldFor~~
	~~# SalesImportID (~~not a column ~~– tacked onto the end of the export/import)~~

PubGizmoSysop: Created page with "To compute royalties, PubGizmo requires sales data. == Sources == You will generally receive sales data in spreadsheet form (Excel or CSV) from various sources: * vendors like Ingram, eBound, etc. * from your sales platform, e.g., Square, WooCommerce * exported from your accounting software, if the sales are initially recorded there * sheets you generate yourself, e.g., at a sales event Regardless of the source, a minimum set of data is required to make the computatio..."

2024-01-29T16:57:03Z

Created page with "To compute royalties, PubGizmo requires sales data. == Sources == You will generally receive sales data in spreadsheet form (Excel or CSV) from various sources: * vendors like Ingram, eBound, etc. * from your sales platform, e.g., Square, WooCommerce * exported from your accounting software, if the sales are initially recorded there * sheets you generate yourself, e.g., at a sales event Regardless of the source, a minimum set of data is required to make the computatio..."

New page

To compute royalties, PubGizmo requires sales data.

== Sources ==
You will generally receive sales data in spreadsheet form (Excel or CSV) from various sources:

* vendors like Ingram, eBound, etc.
* from your sales platform, e.g., Square, WooCommerce
* exported from your accounting software, if the sales are initially recorded there
* sheets you generate yourself, e.g., at a sales event

Regardless of the source, a minimum set of data is required to make the computations. When we are discussing spreadsheets, we mean the columns in the sheets, or, if that data is not provided in a column, as a definition to apply to the entire, e.g., if all the sales are in the US, you don't need a Country column; you indicate at the moment of import that all sales are Country = US.

== Required Columns ==

# '''ISBN:''' the unique [[wikipedia:ISBN|International Standards Book Number]] that identifies each edition.
# '''Quantity sold:''' the number of books sold or returned (expressed as a negative value).

== Required Columns, or Defined for the Entire Import ==

# '''Invoice date, date of sale, process date:''' the date to use to determine the royalty period within which the sales transaction falls. Note that if this date is not available, PubGizmo will allow you to define a single date for the entire import, and each imported sales record will assume that date. For example, you could use the date 31 December 2024 to ensure the sale is recorded in a royalty year that runs from 1 January 2024 – 31 December 2024.
# '''Amount remitted:''' the amount the vendor pays your press for the sale of the book. This is necessary only for royalties based on ''net''. To calculate royalties based on ''list price'', the Edition record must include a list price for the region where the book has been sold (defined in the Country column, below).
# Country of Sale: the two-letter ISO code for the country where the sale occurred.

== Totally Optional Data ==
This data is "nice to have," and can aid in sales reporting.

# Invoice number: [optional]:
# Currency∆
# XR
# City
# Prov
# Country∆
# MidC
# EndC
# QReturn
# Title
# SoldFor
# SalesImportID (not a column – tacked onto the end of the export/import)