Ways to Process Public Procurement via Free BI Prozorro Module

Journalists dealing with such topics as budget and public procurement have to process significant amounts of data. Usually, they do this manually by searching for the necessary information on Prozorro. This work can be automated and facilitated by using the BI Prozorro analytics module. Ways to use it are presented hereafter.

Module description and components

BI Prozorro is a free tool for public procurement analysis provided by the Dozorro project of Transparency International Ukraine. It contains data on all procurement made via the e-system since 2015. The module updates the information every night, so today’s activities will be available in it the next day. When you see BI Prozorro for the first time, it appears to be a complicated pile of tables that would puzzle even an expert. However, it’s not that bad. The module may not look user-friendly, but it has a really well-categorized inner structure.

Read also: “Public Procurement 101: Do Not Get Lost In Prozorro”

BI Prozorro consists of unique sections named Schedules. They contain data on various components of public procurement — for example, the procurement itself, contract performance, monitoring by the State Audit Service, etc. The most recent actions are available in Schedule 12 — “Procurement Stage (for the previous two years)”. And if you need

to look deeper into the history — Schedule 13 — “Procurement Stage (all years).” By the way, Dozorro is currently working on merging these Schedules into one.

Then each Schedule is divided into sheets — just like a section is divided into pages. They allow for data analysis from different points, learning more about a specific tendering authority/tenderer, or general system indicators. It appears that journalists will most often use such sheets in Schedule 12:

• Tenders/Lots
• Indicators
• Tenderers
• Tenderer’s card

Sheets “Design View” and “Random Comparison” — for a more advanced user working with the module.

The Dozorro team almost always starts working with the module from the Tenders/Lots sheet. It has convenient filters to get a downloadable table of the procurement in questions at once.

Basic search filters

To get the procurement table, you should limit the selection, as there are millions of them. Guidelines and usage tips are at hand — click the button Selection Guidance on the right.

The most frequently used filters are located at the bottom to the right. To open the list, click on the arrow below. There are additional filters:

• The tendering authority’s region — to select procurement transactions in a specific area.
• Tendering authority — to search for procurement transactions of a certain body, institution, utility, or public enterprise.
• Tenderer and Successful bidder — to select procurement transactions with a certain company participating or receiving a contract.

Contracting authorities (that is, procuring entities) and procurement participants can be searched by EDRPOU codes. For example, a journalist needs to review tenders posted by city hospitals. They can compile a small table of their names and codes — and then copy the column with EDRPOU and insert it into the filter search field. The module will pull up the relevant procuring entities.

All filters set by the user are displayed on the top panel (selection line). On the right, it has a button with rectangles to open the entire list of available filters. There are hundreds of them, so to find the right one, check the box next to Show fields and start entering a keyword in the search field. For example, “non-price” for filters on non-price procurement criteria, “monitor” for those related to the State Audit Service monitoring, etc.

When you click on the button with rectangles, the advanced selections are collapsed and the user is back to the table view. There is a filter for the year and month of the purchase announcement underneath. Right above it, there is a line indicating the current number of lots in the table, their expected value, the initial (as of the conclusion date), and the current value of the contract.

The table columns can be rearranged, and if you click on their name, the data will be sorted in descending order. Another click will change it to ascending order. Therefore, if you click on the Expected value column, you will see the most expensive tenders at once.

The column with an ellipsis and the blue word Lot everywhere contains hidden hyperlinks. They are references to the corresponding procurement page on Prozorro.

You can save the table. To do this, right-click anywhere on it and select Table — Download as… — Data.

Search by a keyword

It is a bit more difficult to search by procurement name. Procuring entities may call the same product, work, or service differently: keywords can be in singular or plural, in different cases, and with additional information included. To search by all variations, you should use special symbols:

* — asterisk means any number of arbitrary characters. Therefore, the request *table* will include procurement transactions of a Wooden table, a Desk, a Set of ottomans and a Drawing table, etc.

? — question mark replaces one character. It should always be used instead of a space. It can also be used instead of an apostrophe or where the procuring entity could have confused a letter (like i and e in complex words).

If you need to search for procurement transactions by several words at the same time, you should put all the separate requests in brackets and divide them using vertical lines. It will look something like this: ((*NewYear*)|(*Christmas*)|(*tree*)).  Detailed search guidelines are available here.

If you search by one keyword only, a lot of irrelevant items will be pulled into the table. So, *table* request will include table tennis paddles, bed linen, and even an astilbe flower. Thus, it is recommended to add a filter on the CPV class to make the sample more relevant. CPV, also known as procurement dictionary, is a classifier of items a procuring entity purchases. It has goods, works, and services broke up into groups. If there are any doubts on the applicable code, you should use the CPV-tool by KSE. For example, for the table search case, you should have put in the following: 39120000-9 Tables, display cabinets, desks, and bookcases, and also 39130000-2 Office furniture.

A functional link

To support your news or article material with a link to the selections made, you cannot just copy the link. In the latter case, the module will simply open a blank sheet, without any of your selections. To avoid this, you should complete the link manually. In simple terms, you do the following: copy the link from the line above and add /select/Filter name/Its value to any text editor. More information is in this manual.

You can also make a standard link with filters including the related procuring entities or CPV codes, save it to your bookmarks, and open it where necessary to review new procurement transactions in the same way.

How to search for suspicious procurement transactions

Basic indicators to pay attention to in the module:

• The number of complaints — a table column where you can see the procurement businesses complained about to the Antimonopoly Committee most often.
• The number of disqualifications — this column is next to the previous one with complaints. It shows the number of tenderers the procuring entity rejected during the procurement process.
• Data about any monitoring held — this filter is in the advanced selection list.
• Price per unit — if you take the average market price of a certain product as a basis, you should choose the related procurement, choose the correct unit of measurement (so that chicken fillet is not measured in persons, yes, this is a real case example), and search for procurement transactions with a higher price.
• A procurement value close to the threshold may indicate that the procuring entity is trying to manipulate the procedure to avoid competitive bidding. More information about the current thresholds is available here.

It is really important to keep in mind that although the said indicators may prove a problematic procurement, it is not necessarily always the case. A lot depends on the journalist’s level of knowledge of procurement rules and nuances as then each suspicious deal should be carefully reviewed.

How to find out more about tenderers

The top contractors of a specific procuring entity are presented in the Tenderers sheet available via the Sheets button at the top right. If you switch between sheets within the same application, all set filters are saved.

On this sheet, pay attention to the column Number of won bids and Value of won bids. If you click on the names of the related columns, you will see the contractors who received the largest number of contracts and contracts with the largest total value.

If you wish to conduct a detailed study of a top supplier, you should click on it and check if any of the previous selections are to be removed. And then go back to the other sheet using the button on the upper right. Now it will be a Tenderer Card (Bids). Here you can see its most often procurement transactions, other competing entrepreneurs at tenders, and its areas of activity. Round colored buttons on the same page allow you to go to the company profile on other services, for example, YouControl or Opendatabot.

The data on this sheet can be presented by the number of lots or by their expected value.

More of general numbers

General data can be reviewed on the Indicators sheet. It includes the procurement geography, ranking by CPV codes and tendering authorities, total numbers about the procurement amount, cost, and the competition level. Infographics are presented by the indicator selected above.

Presenting all the module tools and the related procurement analysis may take a long time, but first things first. This text presents their main functions beneficial to a journalist. If you have any additional requests or questions about the module, please, contact the Prozorro Analytical Tools group or at [email protected].

We wish you successful analytics!

This post was prepared with the financial support of the European Union. Its content is the sole responsibility of Transparency International Ukraine and does not necessarily reflect the views of the European Union.