How to build queries in OCDS Analytics

24-05-2019

How to add value to your data?

Nowadays, when volumes of data are available, people are trying to make more data-driven decisions. Since data are valuable and meaningful only when you can manipulate them according to your objectives, it is important to use a powerful analytics solution. Such tools can provide not only extremely in-depth data, but also flexible means to get results quickly, create value, and not drown in a sea of data.

Data analysis is a process of transforming and modeling data with the aim of discovering useful information and drawing conclusions from it in order to report information or supporting other business processes.

Procurement, both commercial and public, takes a great share of business resources and is constantly monitored: to stop corruption and violations in case of public procurement and to optimize processes in case of commercial procurement.

With the appearance of the Open Contracting Data Standard (OCDS) that can be used as a procurement data standard, data could be collected and presented in the unified format. Which, in turn, allows a unification in data analysis as well.

OCDS Analytics provides tools for analysis of the OCDS-based procurement data.

We had inspected, cleaned, processed, and transformed procurement data. Then we had built data analysis means that can suit multiple techniques and approaches to data analysis. Comprehensive query language and optimized response time became our main goals. So that data or business analysts could easily write custom queries and immediately receive a tailored result.

In this blog post, we would like to share some basic queries you can use while working with OCDS-based procurement data.

Currently, the OCDS Analytics service is in the Alpha version and contains data we collected from the Ukrainian ProZorro public procurement platform. Note that our API GUI contains docs and schema that help in writing the correct queries.

Check out our API here. Also see our documentation.

Aggregation count

Let’s start with something basic. For instance, you want to find out the overall number of tenders in the system. You can use count for aggregations:

{
  Tenders{
    aggregation {
      count
    }
  }
}

Similarly, you can build a query when you want to know the overall number of organizations (both procuring entities and tenderers) registered in the system:

{
  Organizations{
    aggregation {
      count
    }
  }
}

Pagination

If you expect to get a lot of data in the response, you should limit the display results without applying a limit for aggregations:

limit: 5 means ‘return a maximum of 5 records’
offset: 10 means ‘skip the first 10 records’

Example of a query will be as follows:

{
  Tenders(page: { limit: 5, offset: 10 }) {
    values {
      id
    }
  }
}

Filters

You can also add filters:

eq - equal to
neq - not equal to
gte - greater or equal than
gt - greater than
lte - lesser or equal than
lt - lesser than

Filters should always include the field which we are filtering on and its value.

filters: {filter: {field: "" value: ""}

For instance, to find out how many of the tenders are complete (see the OCDS codelist for tender status values) use the following query:

{
  Tenders (
  filters: { eq: { field: "status", value: "complete" } })
  {
  aggregation {
    count
  }
}
}

If there is a need to use multiple filters in one query, you should follow this syntax:

filters:[
{filter: {field: "" value: ""}
{filter: {field: "" value: ""}
...
]

E.g. Find out how many of the tenders are complete and their tender value is bigger than 1 000 000 UAH:

{
  Tenders(
    filters: [
      { eq: { field: "status", value: "complete" } }
      { gte: { field: "value.amount", value: "1000000" } }
    ]
  ) {
    aggregation {
      count
    }
  }
}

Tender usually starts with the enquiryPeriod - the period during which potential bidders may submit questions and requests for clarification to the entity managing procurement. So let’s find out for how many tenders the enquiryPeriod starts today (we will need to search for active tenders in this case):

{
  Tenders(
    filters: [
      { eq: { field: "status", value: "active" } }
      { gte: { field: "enquiryPeriod.startDate", value: "2019-05-24" } }
    ]
  ) {
    aggregation {
      count
    }
  }
}

If you use Tenders data collection and want to filter data based on Identifiers-related values (like procuring entity, tenderer, and winner), you can use related fields for Identifiers data collection: procuringEntityIdentifier, tenderersIdentifiers, suppliersIdentifiers. The syntax for filtering by related fields will be as follows:

filters: {filter: { 
           relatedField: {field: ""} } 
           value: ""
           } 
         }

In order to filter your data collection by a specific supplier, you need to use relatedField - suppliersIdentifiers and input desired id as a value.

{
  Tenders(
    filters: {
      eq: {
        relatedField: { suppliersIdentifiers: { field: "id" } }
        value: "3262905185"
      }
    }
  ) {
    aggregation {
      count
    }
  }
}

Let’s make this query more complicated and count only tenders with the estimated value of the procurement over 100 000 UAH:

{
  Tenders(
    filters: [
      {
        eq: {
          relatedField: { suppliersIdentifiers: { field: "id" } }
          value: "3262905185"
        }
      }
      { lt: { field: "value.amount", value: "100000" } }
    ]
  ) {
    aggregation {
      count
    }
  }
}

Information on all procurements initiated by the procuring entity

Let’s take for an example that you have a specific procuring entity you want to investigate and you know its ID in the system (usually, it is organization’s number in the United State Register of Legal Entities, Individual Entrepreneurs and Public Organizations of Ukraine).

Using instructions above you can create a query that will show all the information on tenders announced by the procuring entity you are interested in.

{
  Tenders(
    #Limit display to just 5 tenders
    page: { limit: 5 } 
    filters: [
      {
        #Use procuringEntityIdentifier to specify the organization's id
        eq: {
          relatedField: { procuringEntityIdentifier: { field: "id" } }
          value: "37501627" 
        }
      }
      #If you want to see only completed tenders:
      { eq: { field: "status", value: "complete" } } 
    ]
  ) {
    values {
      tenderID 
      title
      #Item being procured
      items { 
        description 
      }
      status
      #Estimated procurement value
      value {
        amount 
      }
      #Tender's winner
      suppliers { 
        values {
          identifier {
            id
            legalName
          }
        }
      }
    }
  }
}

Information on all procurements the tenderer participated in

You can build a similar query to get information on all procurements a specific tenderer participated in:

{
  Tenders(
    filters: [
      {
        #Use tenderersIdentifiers to specify the organization's id
        eq: {
          relatedField: { tenderersIdentifiers: { field: "id" } }
          value: "39472457" 

        }
      }
      #If you want to see only completed tenders:
      { eq: { field: "status", value: "complete" } } 
    ]
  ) {
    #Procuring entity that announced the tender
    values {
      procuringEntity { 
        id
        name
      }
      tenderID
      title
      #Item being procured
      items { 
        description
      }
      status
      #Estimated procurement value
      value { 
        amount
      }
    }
  }
}

If you find this post interesting, subscribe to our newsletter to get news and updates.

← Alpha version released! How to build queries for "Top 10" →

# How to build queries in OCDS Analytics

# How to add value to your data?

# Aggregation count

# Pagination

# Filters

# Related fields

# Information on all procurements initiated by the procuring entity

# Information on all procurements the tenderer participated in