With Sassafras AllSight, importing purchase data is flexible and easy: simply map source data fields from a spreadsheet into the purchase database, without having to do extensive manipulation and massaging. In fact, if you have multiple data sources that are formatted differently, you can define and save a separate field map for each. In this post we will show how. Note that if you happen to have any 19770-3 ent files, you can import those directly, and the spreadsheet instructions below will not apply (for those purchases).
Mapping UI
After dragging a csv file containing purchase records into the KeyConfigure Purchase Window, you will be prompted to choose a set of “instructions” to use for import. From this dialog, you can also create New instructions, which will bring up the following UI. On the left are the AllSight internal fields, and on the right are the source fields, showing sample data from the first import row. From here you can drag each Source row on the right underneath to one or more destination columns on the left, and then save.
Data fields
In order to calculate an accurate Current License Position (CLP) for each software product, certain data fields are required. Some will certainly exist in your source data, but others may not. For example, you might not have values indicating the license metric or normalized product name; instead, these two properties might be recorded together in a freeform purchase description. In this case, you won’t be able to automate those particular fields. Instead, you may be able to extract or concatenate the data as needed in Excel, or you might have to record this information manually after the bulk of the import happens. The template linked here can be used to get an understanding of what fields are most important. If you will do any initial work gathering data from multiple sources, have a look at it first, in case you are able to easily align the source data with it. However, if all the purchase data is already in a system, you will likely just match up available data as best you can. Most field names in this interface will be very similar to the UI, but two fields in particular are quite different: for PO Number, look for “purchaseOrderID,” and for Comment, locate “orderNotes.”
Essential Entitlement Data
The following fields constitute the central set of data that you will probably want to record for purchases. If you are using your own format for purchase data, you should include values for each of these fields – but note that the fields in your .csv file can have any name you want. If you don’t already have a text file, you can use the standard column names below, and then simply use the “Default” instructions instead of setting up your own mapping.
- x-productName: Product name, which will be used to search/map to the appropriate Product during import (if recorded as distinct fields you can also use x-productPublisher and/or x-productVersion).
- purchaseType: Software License (“Maintenance” is a type of “Software License”), Media, Tech Support, Other
- purchaseEntitlementType: Original, Addendum, Upgrade, Exchange
- purchaseMetric: Site, Node (Device), Concurrent, User, Lease, PVU, Core, Socket (CPU), Special, Custom
- purchaseStartDate: Start date, if not perpetual.
- purchaseEndDate: If not perpetual (i.e. this is a “subscription”).
- purchaseRenewDate: The date until which upgrade rights are included for this purchase. Generally for a Subscription, this will match the purchaseEndDate.
- purchaseQuantity: Number of licenses.
Note that “subscription” is not a license “metric” but it is a limiting condition that can be applied to any metric. Thus, it is identified by an End Date on the entitlement itself. Similarly, the Renew Date does not impact the entitlement, but it affects only the upgrade rights that typically accompany a maintenance agreement.
Although none of the above fields are “required” to create a record, they all provide essential information for product identification and entitlement quantification, and all are necessary for successful reconciliation.
Normalizing products
Once software purchases exist in AllSight, each one will need to be tied to a product. When importing purchases there are two main approaches. The first is simple: just import the purchase description, which might be something like “My Statistics v15, 5 user subscription.” After importing all purchases, sort and filter by description; then, having found purchases of “My Statistics v15,” associate them with the appropriate product record. (You can select multiple purchases and then drag them onto the appropriate product row.)
The second approach allows you to use one or more fields in the source data to match against a product record. For example, if you have a field that has just a product name in it, like “My Statistics v15,” you can leverage this field while importing. The first time that exact string is seen during import, a window will prompt you to choose the correct product. From there, if you have 10 more purchases with that exact text, they will all be connected to the same product automatically.
The approach you take will depend on how your data is structured. If you have a discrete field with just the product name and many purchases for the same product(s), the second approach might be ideal. However, if you only have a non-normalized description field — where the same product has many different descriptions for different purchases — then remembering a single mapping won’t necessarily help.
With the first approach, after the initial mapping, you can likely do the bulk of even a large import in a matter of minutes. Then, you can spend time on product mapping whenever you like, pausing and picking that process up as needed. With the second approach, because you cannot stop the import once it has started, you will have to handle all of the prompts in one sitting. Therefore, depending on the number of records, it may be helpful to break the data into multiple files/imports, instead of trying to import everything at once.
Other Mapping Prompts
During the import, you may be prompted to clarify a data format or choose from a discrete list of options for certain fields, such as date formats, currency types, license metrics, etc. As with product references mentioned above, you can choose to have the system “remember” your selection for recurring entries. So, if you map your “Device” license to AllSight’s “Node” option in the Metric field, the system can automatically associate remaining occurrences, without forcing you to choose an option each time.
Purchase Item Number
If possible, import a line item number for each purchase. In other words, PO 1234 might have two line items, and those should have item numbers 1 and 2. If you don’t have this number in your purchase data, but every purchase has a single line item, you can simply create an additional column that is populated with the number “1” in every row.
By including line items in your initial import, it will be easy to re-import updated data at any time, modifying existing line items instead of creating new ones under each PO. If you don’t add them, a second import of the same data will result in a duplicate purchase item.
Purchase Folders
A good practice, especially when you are initially testing your purchase mapping, is to ensure that all existing purchases are in purchase folders before initiating an import. Since imported purchases will initially land in “Uncategorized,” this precaution will let you easily separate newly imported purchases from those that were already present (except when importing a purchase a second time with an item number as described above; re-imported purchases will be left in the folder you have already placed them in).
Entitlement Type
Entitlement Type includes choices for some subtle but important differences. An “Original” Entitlement generally represents the first time a software product license was purchased, or a subsequent purchase made that is not an upgrade, addendum, or exchange. An “Addendum” modifies the entitlement rights originally granted by another purchase. For example it could extend a subscription (which can also include new version rights). An “Upgrade” grants rights to a newer version (while often also extending “downgrade” rights to continue using the older version). And an “Exchange” grants entitlement to a product in exchange for licensing for another (possibly competitive) product.
How to Enter “Maintenance” Purchases
If you purchase perpetual entitlements you will often buy “Maintenance” separately for access to free upgrades for some period of time. A Maintenance purchase should have a purchaseType of “Software License” and a purchaseEntitlementType of “Addendum.” Finally, a Maintenance purchase should have a purchaseRenewDate (the date until which you get free upgrades) but no purchaseStartDate or purchaseEndDate (since the entitlements themselves would be granted by a different purchase). For an initial purchase of perpetual entitlements, you might have a second line item on the same PO for the maintenance.
URLs
If your source data includes a URL, it can be mapped to the “urls” column in the import UI. Once the import completes, if you open the purchase details and navigate to the “Documents” pane, you will see an item listed which can be double-clicked to access the URL. You can also drag other document files into this pane such as PDF or Word files containing POs or invoices.
Install Codes
Install codes and serial numbers can be mapped to the “serialNumbers” field. This single field can contain multiple values, as long as they have double quotes around them (i.e. valid .csv format). After the import, these will appear in the “Install Codes” pane in the Purchase Details window. Note that in KeyConfigure, you can edit each Install Code to set a Maximum (number of installations allowed for each code), Platform, etc. You can also enter computer or user names where those codes have been assigned. These properties cannot be imported at this time.
SKUs
If a purchase has an SKU it can be imported to purchaseManufacturerSKU or purchaseResellerSKU. We are often asked if we can leverage a SKU to determine the appropriate product and license metric; so far, the answer is no. While we are always looking for ways to automate, there is a significant chance that costly errors would be introduced into the data. With hundreds of thousands of SKUs in a knowledge base, if we got just a single one wrong, we could do more harm than good.
Full Source Data
If you have extensive import data, you may find that some values don’t have a logical place to import to. Note that multiple source values can map into a single target in AllSight. For example, if you have “Internal Code”, “Completion Status”, and “Override”, you could map all of these into the “Notes” field of a purchase. Also, even if you don’t provide a mapping for a source field, you will always be able to access a file that includes all of the source values for an imported purchase item. This document will be in the Documents pane. Essentially it has all of the data from that single row of the import – just reorganized into one (labeled) row per value instead of all values on a single row.
“Reconcile” purchase records with Policies
It’s too big a topic to discuss at length here, but after you add purchase record(s), you can make sure they are reconciled against Manage polices, with or without enforcement, to maintain compliance, as discussed in our documentation here.
Subscribe
