Importing Sales Data

PubGizmo allows you to import sales records from spreadsheets. These may arrive from vendors such as Ingram, eBound, iTunes, etc., or you may generate these yourself, for example from sales you conduct at a live event, or exported from your your website (e.g., WooCommerce) or accounting (e.g., QuickBooks). These sheets can be imported directly from Excel files (XLSX, or the older XLS format), or CSVs, which represent universal formats – all data software can save in one of these formats. If given the choice, use XLSX; it's the most modern, data-rich format.

These sheets must include a minimum set of data to be viable for computing royalties; you can find a list of requirements at Sales Data Needed to Compute Royalties.

Basic Process

The full process is outlined in detail below, but conceptually, here's what you will be doing:

1. Point to the file you want to import.
2. Select which columns contain which data, or allow them to load automatically, if you've performed this import previously.
3. Tell PubGizmo to process the import.

Step-by-step Process

Where to Begin

You can begin an import from the Home screen, or from within Sales Imports module.

1. On the Home screen, click the + icon on the Sales Import button; in the Sales module, click the Import button at the upper-right.
2. You will be prompted to locate your file; navigate to the appropriate folder on your computer. Note that only files with an extension of .xlsx, xls., or .csv will be available for import.
3. Double-click the file.

Selecting Sheets

1. If your file includes multiple sheets, you will be prompted to pick the one containing relevant sales data. This may or may not be the first sheet. The file may contain multiple sheets, some with importable data, and others with irrelevant summary info. It can help to open the file in Excel first, to identify which sheets contain sales data.
2. If the file contains multiple sheets of sales data, you'll need to perform multiple imports of the document, one for each sheet – see below, Multiple Sheets.
3. Double-click the appropriate sheet.

Multiple Sheets

Some vendors store sales from different countries and/or in different currencies in separate sheets (tabs) within a single Excel document. As PubGizmo processes only one sheet per import, multiple imports from the same document, with a different sheet selection each time, will be necessary.

Duplicate Protection

PubGizmo prevents you from importing the same sheet twice, even if the copy has a different filename, as long as the sheet data itself remains intact. It does this by recording the "signature" of the sheet and comparing it to the signatures of existing imports. If you try to import a sheet that is already in PubGizmo, you will receive a warning, plus an invitation to navigate to the existing import.

Click Through These Steps – Don't Change Anything

PubGizmo will show you some screens which you just need to click through – don't change anything on them.

1. Disregard the Specify Import Order screen. Changing any setting here may cause the import to fail. Click the Import button at the bottom-right.
2. Click Okay on Import Summary. Disregard the record count; it may seem too high, because FileMaker doesn't know exactly where the rows end in your sheet. Empty rows will be discarded at processing.

Mapping Columns

1. You will now see the import mapping screen. Here is where you tell PubGizmo which columns on the sheet contain which elements of the required data. There may be many columns, but you need only a few, the required ones, for a complete sales import that will contribute to the data required to compute royalties.
2. Scroll left and right through the data to locate your columns. When you spot the data you need, for example the ISBN, click the header above that column (where it might say Unassigned, or it might correctly or incorrectly display a header) to select the data it contains, e.g., ISBN.
3. Be careful during this stage; many sheets contain excessive data, often with inscrutable headings. Two or more columns may contain similar data; be persistent as you scan for the actual data you need.
4. PubGizmo will help you determine if you've selected all the data necessary for a successful import. Keep an eye on the Column Mapping area at the upper-left of the screen; it will show you what's missing.
5. Some data is optional, for example, the Province or State of sale; if you ever want to do analysis of your data, you should specify as much information as possible.

Apply Data to the Entire Import

Note that some sheets lack columns for data that applies to the entire sheet. For example, if the currency of all the sales in the sheet is in US Dollars, the sheet may not include a Currency column. The same is true for the Exchange Rate (required only if the Remit Amount column contains non-domestic currencies), Country of sale, and the Invoice Date. Using the areas along the top, you can specify global data values that encompass the entire import.

Some Things to Note

Process Button Greyed Out

The import cannot proceed if any of the required selectors are not set. This prevents you from importing useless sales data.

No Harm in Deleting and Importing Again

You can delete and reimport a sales sheet an unlimited number of times with no ill effects. So if you make a mistake at import, don't worry; you can reset your selections and try again.

Warning: do not delete imports for sales periods for royalty periods that are complete! Doing so will skew the numbers for those periods – you will corrupt your previous sales history.

Name the Import Process

1. You must enter a name for the Process Import. This is because PubGizmo saves your column "map," and automatically tries to apply it the next time you import from this vendor, since the location of columns tends to remain static. If they do change, you can update the map to reflect the new column order.
2. The best name for a Process Import is a single word unique to this particular vendor, and which will always be found in the filename. For example, name your import "Ingram" if it comes from Ingram, and contains "Ingram" in the filename.

Start Processing

1. The Process button will remain grey until you complete all the required data, including the Process Name.
2. Once you click the button, PubGizmo will find all the records with a valid ISBN in the column you specified as ISBN; any line on the sheet without one will be discarded.

Start Your Next Import

1. After that, you will be sent to the Home screen. PubGizmo hands the processing off to the server. It may take many minutes to process; while that is happening, the import will show in your Session History with the name and timestamp of the import, and the word PENDING. When the process is done, PENDING will be replaced with the number of records imported (this may not update until you "hover" over the entry). You can click on the import at any time to navigate there, but until the process is finished, the data may look weird and incomplete.
2. You don't have to wait to perform your next import; PubGizmo is immediately ready to receive your next one.

Inspecting the Import

It's a good idea to check that your import worked properly. A link will appear as an entry in your Session History on the Home screen. Click to view the import.

Is it Complete?

The first thing to consider is if the data imported looks complete – are all the fields for which you mapped data filled in (note some optional fields may be blank, if the sheet didn't contain that data, or you opted not to map those fields).

Is it Reasonable?

Do the numbers and amounts make sense? If there is a exchange rate (e.g., from CAD to USD), does the local currency amount look like a correct conversion from the foreign currency (hint: for USD conversion to CAD, the CAD amount should be greater than the USD amount, by a factor of about 1.35).

Are There Missing ISBNs?

If the sheet contained ISBNs with no match to your data – that is, the sheet reports sales for editions not found in PubGizmo, either because you have missed recording those editions, or they were erroneously included by the vendor in your sales data – the affected sales records will be shown in red, and you will see a "Missing ISBNs" button at the top of the Sales Import.

What to Do

1. Examine the red rows; if you mapped a Title field at import (optional), the title will appear in this field, otherwise it will be blank.
2. If these are legitimate ISBNs from your list (see the Tip below), update Title records by adding those missing editions with that ISBN.
3. If these are not ISBNs from your list, inform the vendor/distributor that they are sending erroneous data.

Tip

Don't recognize an ISBN? Click the row and a popover will open showing the sales data. Click the "Title From ISBN" button and PubGizmo will search the internet for the title associated with that number. You're probably the publisher!

Add the Edition to the Title record, and PubGizmo will refresh the sales record to connect it to the edition. You can confirm by going to the Sales Import; the line should no longer be red, and if the title field was blank, it should now be populated with the title.

Yellow Rows

While rows in red are likely critical (sales are being reported for books not recorded in PG), yellow rows are more informational. They represent a sale for an edition that is not covered by any contract recorded in PG. For example, an author's contract for an edition specifies North American sales only, but on the imported sheet there's a sale in Japan. PG is faithful to the terms of contract; that sale is not included on the royalty statement, and earnings from that sale are not computed and paid.