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 the 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 data set. It has removed duplicate entries, extracted various attributes from the comments (e.g. solicitation procedure, former public servant, etc.), and adjusted contract values where specified. Click on the API Info tab to learn more about all available columns and their corresponding types.
First things first, let’s head to Namara and apply corresponding filters on the Contract Date Earliest:
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 data sets. 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:
If you want to limit the parent agencies, to, say, top 5 by 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.
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:
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.
Learn more about how we’re driving transparency with the Canadian Government.