Exploring Government Spending with Namara and Tableau

Canadian government is the largest buyer of local goods and services with estimated $13B spent in 2016–2017 fiscal year only.

Federal agencies are required to disclose contracts over $10K issued on behalf of the government of Canada (more about contracting policy). Here, at Namara, we have put together all historical reports published individually by 47 largest departments and agencies starting as early as 2004. Our team has standardized, refined and consolidated disparate records to map them to an ideal schema in an aggregated and easily queryable dataset. It has duplicate entries removed, various attributes extracted from the comments (e.g. solicitation procedure, former public servant, etc.), and contract values adjusted where specified. A typical contract usually has a unique identifier, information about vendor and procuring agency; date the contract was signed, contract beginning and delivery dates; original, total and amendment values; information about the type of purchased services/goods; type of solicitation procedure; indication of whether the contract was given to a former public servant, as well as some other information. Click on the API Info tab to learn more about all available columns and their corresponding types.

Since most of the agencies publish their amendments as individual records, calculating any aggregates would result in overblown numbers. Hence, we have collapsed all of the relevant records to the same original contract, eliminating duplicate amendments and updating respective contract values. All of the contracts were also mapped to 23 parent agencies and assigned an economic category and a subcategory as per Object Codes classification. For the purposes of this analysis we are only going to focus on the dataset with the collapsed contracts and limit our scope to 2016–2017 FY.

First things first, let’s head to Namara and apply corresponding filters on the Contract Date Earliest:

This should result in some 55k contracts. Then click on the Download tab, Prepare filtered data set button, and finally on the Download button on the popup:

Voilà, you have a filtered version of this dataset exported as a CSV file, and ready to be imported to any of your favourite tools! In this post we’ll be using Tableau — the champion of data visualization and explorative analytics that has become an industry standard among journalists, analysts, financial experts and pretty much anyone data savvy.

First, you will need to make sure that you have a valid Tableau account for either a desktop or a web app. You can try it for free or use a free version with publicly hosted datasets. We will proceed with the desktop app here.

To load the file, click on Connect -> Text File, and provide the path to a target CSV. And then click on Update now, if prompted:

You will see that Tableau has parsed the dataset and has automatically assigned data types to every column. At this point you can change it, as well as rename and hide columns, and create new ones using existing. You are all set for a deep dive!

Let’s start with a high-level overview and see what agencies are procuring the most, both in terms of volume and dollar amount. Let’s create a new sheet “Parent Agencies” and drag Parent Agency dimension to Rows shelf and Number of Records, Current Contract Value and Amendment Value to Columns shelf. This will render three horizontal bar charts. And if you click on the Number of Records label under the first section, the data points will be sorted based on the overall volume. We can see that the top procuring agencies are pretty much aligned in terms of number of contracts and overall contract value, and that the largest spenders are: National Defence, Public Safety, Public Works, Fisheries and Oceans, and Environment and Climate Change.

If you want to limit the parent agencies, to, say, only top 5 by a number of contracts, click on a drop down on the Parent Agency pill, select Filter…, switch to the Top tab, and pick 5 by Number of Records.

What is interesting is how Public Works has quite a large dollar amount in amendments compared to other top spending departments. Let’s create a new sheet called “Projected vs Actual” and look into individual contracts for Public Works. To make it easier to spot the contracts that were amended by a lot, we will plot original contract values against the current ones.

To do this drag Current Contract Value to the Columns shelf, click on the pill drop down, and select Dimension. Drag Original Contract Value to the Rows shelf, click on the pill drop down, and select Dimension. Now drag Original Contract Value to the Columns shelf as well, and click on the drop down and select Dual Axis. Right-click on one of the horizontal axes and select Synchronize Axis to make sure that we have a perfect 45 degree line. Click on the Measure Names shelf -> Edit Colors…, in case if you want to change the default color of this line (I have it set to grey). Right-click on the plot ->
Trend Lines -> Show Trend Lines… and pick SUM(Original Contract Value) to extrapolate this trend line beyond the min and max. On the shelf Marks pick SUM(Original Contract Value) and update its opacity to 0%. Change the plot type to Circle for Current Contract Value. Let’s update the way tick values are showing up. To do this right-click on the top horizontal axis and select Format…. In Scale -> Numbers choose Currency (custom) and set decimal places to 0, and units to Millions. Repeat the same procedure for the bottom axis. Much better!

To limit data points to Public Works contracts only, drag Parent Agency to Filters shelf, and select “Public Works and Government Services Canada”. Alternatively you can select all parent agencies or those you are currently analyzing. To show the filter, click on Parent Agency pill on the Filters shelf and tick Show Filter.

We are pretty much set. Let’s add more information to every data point. To do this, drag Vendor Name, Economic Descriptor, Original and Current Contract Values to the tooltip icon on the Marks shelf. For both dollar values set their type to “Attribute”. If the attributes are not showing up when you hover over a data point, then double click on the tooltip icon on the Marks shelf and edit the template accordingly.

Last step is to color-code the contracts based on an economic category specified for procured goods/services. Move Economic Category to the color icon on the Marks shelf. Done!

Now all of the contracts that appear below the dotted line have amendments that have increased the overall contract value. Points above it ended up costing less then originally reported, and for those on the 45 degree line, the original and current contract values are the same. So now when analyzing certain contracts, you can easily get the information about the vendor, contract value and the type of service just by hovering
over the point of interest. You should have a graph that looks similar to this:

You can also access the full row that corresponds to a certain point by clicking on the point of interest -> View Data icon and switching to the Full Data tab.

By simply exploring the plot, you cannot see a specific culprit, because the majority of service-related contracts ended up having slightly higher cost.

Now let’s take a look at the data from a different angle. Let’s determine what the top vendors are both volume- and cost-wise. For this we will be using a treemap. Drag Vendor Name to the text icon, Current Contract Value to the color icon, and Number of Records to the size icon — all on the Marks shelf. This should render a pretty crowded treemap with quite a few vendors. To de-clutter it, we will set a minimum number of contracts to 20: click on a drop down on SUM(Number of Records) pill and set the lower value for the range to 20. Much better! You can adjust it, while exploring how the pattern changes across different departments: feel free to add a filter based on Parent Agency, just like we did before. To provide more information, add Number of Records and Current Contract Value to the label.

If you want to see a breakdown by economic category, just add it to the the color and Current Contract Value to the size:

These are obviously just starting points, and this dataset has so much more to reveal. So definitely make sure to explore it firsthand!

Here are some great resources for learning how to use Tableau efficiently:
- training videos
- forums
- blog
- and solutions gallery

Also check out our other datasets that cover federal expenditures: Canadian and US procurement. And feel free to reach out, if you have any questions.

Happy analyzing!