GPC Clouds

As per http://www.gs1.org/docs/gpc/gpc_unspcs_integration_update.pdf , GPC is going to be integrated with UNSPSC. That’s probably understandable since definitely all the companies don’t want to invest effort twice to product classification. Well, since that’s going to happen by end of 2007, GPC Clouds are perhaps still applicable for a while.

UNSPSC Clouds

Tag clouds have become popular in Web 2.0 and social networking. Similar ideas are also entering into the enterprise applications. How would representing unspsc codes look like as clouds? The answer to that is available at enterprise clouds. Direct link is unspsc clouds.

GPC similar to UNSPSC?

UCCNet used to use UDEX codes for product categorization in their product messages. After getting merged with Transora and formed 1Sync, UDEX is being slowly phased out to use GPC (Global Product Classification). Looking at GPC, it’s coding seems to be similar to UNSPSC as they also have segments, families and classes. However, GPC’s 4th level is called Bricks (and Brick codes) while for UNSPSC it’s Commodity.

However, one main different with GPC is that the brick codes are also supposed to define characteristics of the products belonging to those brick codes. GPC includes this standard for the product characteristics and that’s good to be part of the product exchange message. Ofcourse, writing software to get this right on both the seller and buyer side is quite challenging.

UNSPSC v9.0501 : A few statistics

I wanted to update the stats for v9.0501

* No of segments : 55 (remains same from earlier)
* No of families : 419 (up from 363)
* No of classes : 2551 (up from 2125)
* No of commodities : 21718 (up from 19038)
* Avg commodities per class : 8.5 (reduced from close to 9)
* Class with max commodities: Antibiotics (51101500) contains a total of 93 commodities (up from 92)
* Family with max commodities: Measuring and observing and testing instruments (41110000) contains a total of 638 commodities
* Segment with max commodities: Medical Equipment and Accessories and Supplies (42000000) contains a total of 2517 commodities

A sample data model for storing UNSPSC codes

Below is what I think the data model for capturing unspsc codes should look like.

table name : unspsc_codes
columns:
id number not null (internal id within your system that can be referenced in other tables)
key varchar(8 ) not null (even though it's currently 6 chars, giving extra buffer)
code varchar(8 ) not null (even though key and code are numbers, using varchar so that these codes can be stored in existing product classification data models)
level number not null (this refers to level 1, 2, 3 and 4 for the 4 levels of UNSPC)
parent_id number (this refers back to the parent's id)
title varchar(256) not null (this is the title of the code. even though titles are shorter, for multi-byte languages and for future expansion, keeping it 256)
description varchar(2048 ) (same comment as above)

For those using these codes in multiple languages, an additional column to use is

language varchar(3) not null

that captures the title and description for each language.

Apart from these above commonly known elements of unspsc codes, there are 2 more to capture. They are

value_code varchar(10) not null (segments are organized such that they reflect how the value is added progressively to the products. While this can be derived from the segment codes, storing this explicitly may come handy for certain types of analysis and even creating non-function based indexes)

business_function varchar(4) (even though this should probably not null, I have seen a mention of this business function in the unspsc basics pdf file but haven't seen any further information. Perhaps it's yet to come. I am just adding it to the data model anyway but keeping it null. Also, per UNSPC, the length is 2. Planning for the future is always good).

Now, the code itself can be stored in 3 formats

1. the entire 8 letter code for each level (1000000)
2. the entire 8 letter code with a delimiter (10.00.00.00)
3. just two letter parts at each level (10, 00, 00, 00)

I would prefer going with 1 because this is the standard while 2 is not. Also, 1 gives the code immediately for lower levels while 3 would require traversing up the hierarchy using the parent_id

Apart from all the above columns, I would like to add a few more for the purpose of audit. These are

unspsc_version varchar(10) (this indicates the unspsc version that first introduced this code)
creation_date datetime (this indicates when was the first time this code got created in the system)
update_date datetime (since UNSPSC codes could have updates, we need to track the latest update to the code)
deleted_flag char(1) (indicates if this code has been deleted by UNSPSC. You still want to keep such codes as your existing transactions might be referencing these codes)
deleted_unspsc_version varchar(1) (the unspsc version when this code got deleted)

I will be updating this article in the future based on my further study of the UNSPSC classification

UNSPSC Codes free as pdf, $$ as excel

In the past (perhaps till UNSPSC 7.xxx) UNSPSC codes used to be available as spreadsheets for free. But now, they are available for free only as pdf and non-members have to pay $$. Check out UNSPSC Codes section for more information. This seems like an annoying tactic by the UNSPSC.org in that it want to make it a free standard so that there is a wider adoption and at the same time trying to charge for a structured format. They are supposed to come with a newer version every 6 months and they are charging $50 for each language. So, essentially companies have to shell out $100 per year.

But there are open source pdf libraries out there that can extract text out of pdf files. Infact, adobe itself claims to have a pdf to text converter. All you have to do is send your pdf and get the text back in email (though this didn’t work for me at the moment when I tried it).

Searching UNSPSC Codes

Searching for unspsc codes at unspsc.org is a real pain. Why? Because, when you search for “cat” to get all the products related to cats, you also end up with products such as “Cracking catalysts”. The problem is that the search they provide searches on the title by concatenating % on both sides of the keyword. That is, “%cat%” which means, all titles that contain a word “cat” in it. Since “Cracking catalysts” contains “cat” in it, you end up seeing those also in the results.

I am thinking of working on an application that can provide a better search for these UNSPSC codes. So, if you have any particular requirement or interested in getting my application drop me a comment indicating what is a right price for such an application or what your requirement is.

UNSPSC v8.1201 : A few statistics

I thought I would jot down a few interesting statistics of the UNSPSC codes.

* No of segments : 55
* No of families : 363
* No of classes : 2125
* No of commodities : 19038 (note that, you may have 19037 in the system as I found 26101805 commodity to be dangling without a parent in the pdf files)
* Class with max commodities: Antibiotics (51101500) contains a total of 92 commodities
* Avg commodities per class : 9

Any other stat suggestions are welcome.

UNSPSC and the “Raw Material to Service” Pipeline

One interesting fact I learnt from the UNSPSC slides is the fact that the coding is divided such that the segments are numbered from low to high in the order they add value to the product. So, for example, currently

10-15 is raw material
20-27 is industrial equipment (that can process the raw material)
30-41 is components and supplies (that are produced from 1 & 2 above)
42-60 is end use products (that are produced from 1 & 2 and 3 above and available to the end user, as opposed to, say businesses as intermediate components or supplies)
70-94 is services (one manufactured, ofcourse, the end products should be sold, shipped, distributed, serviced etc. So, this is a catch all for all types of services).

Obviously, there are some gaps in the segment numbers above. Not sure why. Perhaps, something may get added in between or the existing ones can get expanded once they run of out space (which by the way, is quite big).

But, first time I read this idea, I really liked the approach.

UNSPSC? What is it?

UNSPSC stands for The “United Nations Standard Products and Services Code”. And a plenty of information can be found at unspsc.org.

UNSPSC codes are for categorizing products using a open standard. The benefits of using UNSPSC are

*. Companies don’t have to spend time and effort coming up with their own classification scheme for the products
*. It is an open standard and contributed by many people in the world. So, your business customers and suppliers are probably using the same classification.
*. It is a 4 level classification. The levels are Segment, Family, Class and Category.