Vendor Management

The first thing you want to do when you get some spend data is look at it grouped into meaningful categories. I typically categorise data using a 5-stage process:

Standard spend categorisation

1. First, categorise every row as “Other”. (This allows you to claim that you have categorised 100% of your data and you’re only on step 1!)
2. Second, categorise each row according to a GL/Category mapping table. (This ensures you get the tail spend.)
3. Third, categorise each row according to a vendor mapping table for your significant vendors. (This ensures you’re not embarrassed by a PA who has for reasons unknown placed a KPMG invoice into “Kitchen Supplies”.)
4. Fourth, categorise each row according to a mapping table for vendor and GL. (This ensures that you split your Staples spend into stationery and office equipment etc.)
5. And finally, categorise each row according to key phrases in the description. (This ensures you pull out PWC legal spend from the Audit category.)

Categorising Campaign Data

Fortunately for me, the OpenSecrets campaign data is expense coded and a mapping table was posted to the OpenSecrets Google Group yesterday.

This allows me to change my Qlikview load script to include a second table that I have called expensecodes.csv. I make sure that my expense code column label matches the label in my spend data csv file (expends08.csv) and Qlikview does the rest.

Viewing Categorised Data

Now, I want to get a quick view of spend by SectorName and DescripLong (the 2-level categorisation assigned by OpenSecrets.

My preferred way to view this type of data is as a tree map:

Creating the Tree map takes only a couple of minutes in Qlikview. We can see that Administrative cost is the single largest cost category running at about 25% of expenditure with Contributions and Transfers together comprising another 25%.

Drilling into the data

Qlikview’s speed of drilling down  into data (filter) is its biggest drawcard. By default, every chart you create is linked to the filters you select on any other chart. For example, if we are interested in seeing our spend v number of suppliers chart above for Internet Media, we need only click on the Internet Media square above and then view the chart.

Now let’s look at the same chart for Print Media. We remove the Internet Media filter and click on Print Media.

Interestingly, you can see that the Print Media supply base is much more fractured than Internet Media. This is probably because, as the candidates start travelling faster from state to state, they start dealing with local Print Media suppliers in each state; whereas they can continue to deal with the same Internet Media providers.

Qlikview’s speed is impressive. We are looking at 2.9 million rows of data on a three-year old laptop computer. Each drill-down completes in under a second.