Blog article: Analyzing Lobbyist Data

Article text

By Connie Volpe This month, we’re featuring a project that we’ve been working on in partnership with the Lobbyist Registrar for the City of Toronto. The Office of the Lobbyist Registrar ensures the public disclosure of lobbying activities and oversees the regulation of lobbyists’ conduct. The City of Toronto publishes this data through the Lobbyist Registry Disclosure Site and through the Open Data Portal.

What is the Lobbyist Registrar?

The Lobbying By-law describes lobbying as communicating with a City of Toronto public office holders (POHs) about concerns that are the subject of City government decisions. The initial communication is reported by the Lobbyist through the Lobbyist Registry System within 3 business days of the communication occurring. The information is refreshed on the Lobbyist Disclosure Site every half hour; the Open Data Portal pulls the data daily The dataset contains multiple years worth of data. After June 2010, lobbyists began reporting communication on a Registry System. After December 2018, the datasets reflects 25 wards, so Members of Council and their staff may reflect multiple wards as a result. Further after January 2019, the datasets have been ‘cleansed’ to ensure “correct spelling, divisions/offices being recorded correctly, etc.

What did we want to learn?

• What can we learn from the data?
• Who are the most lobbied City of Toronto POHs by type and division?
• What are the most lobbied divisions and offices?
• What types of lobbyists are communicating with City of Toronto POHs?
• Where are the lobbyists located?
• How is the data being used?

How we did it

Step 1. Prepare data for analysis

Prior to working with the data, it’s important that we prepare it first. This step includes data cleaning and transformation because almost 80% of data work can mean simply getting it ready for analysis. Preparation varies per dataset, but common tasks performed at this step include:

• Fix mistakes (i.e. spelling of division/office titles, Countries, Provinces/States and Cities)
• Ensure wards and divisions are reported correctly and are associated with the appropriate Councilor and their staff
• Target Time Frame e.g. consolidating data based on the time frame we’re interested in examining

Performing data quality is a large and sometimes complex process, but it’s an essential part of getting started with data analysis. Cleaning the data can be tasking if individuals report communication without the correct the Ward, division or office title. If an individual reports the incorrect information, we have to search the directory to see where that title is located and have the lobbyist correct the report of communication. One example of this error, with the changeover to the new term, was the reporting of communications with Councillors using their new ward numbers. When reporting communication on the lobbyist registry system, an individual reports the communication method and date with the public office holders they have communicated with before. With many of the previous term councillors returning however, lobbyists needed to add the returning councillors as New Public Office Holders. We audit the data to ensure we find these inconsistencies in a timely manner. When found, a Lobbyist Registry Advisor goes back to the lobbyist(s) and provides the steps and information to correct their report of communication. Extensive follow ups are sometimes required. Furthermore, the previous Registry System was only compatible with legacy browsers. Understandably, this contributed greatly to the difficulties of correcting the reports of communication. When tasked with updating the reports of communication, especially those prior to 2017, many lobbyists had difficulties as the use of modern browsers would not allow them to update the subject matter. We have since addressed the issue with the introduction of our new lobbyist registry system. This system now allows lobbyists to report their communication immediately regardless of the browser or device they are using.

Step 2. Cleaning the data

The first step in determining the most lobbied City of Toronto groups is to clean the data. This is done to ensure that the information is accurate and consistent. Wards and Divisions must have the correct titles and the number of wards may not exceed 25. To do this we pull the data and import it into a spreadsheet program, like Google Sheets or Excel, where we organize it by Public Office Holder (POH) Type. We check for inconsistencies and ensure the titles of the wards and divisions are in accordance to the Staff Directory on the City of Toronto Website. Any discrepancies or unclear information is sent back to a Lobbyist Registry Advisor to get the information corrected to ensure the data is clean and accurate for anyone viewing the disclosure site. Once we have the accurate information we upload the spreadsheet into PowerBI. We then select our visualization tool and select POH_Type for Axis under Value Count of Communication. To break the data down further, we go to Report Level Filters and insert the Communication Date Field and select advanced filtering. Under the Report Level Filters is where we would select PowerBI to show items from January 1, 2019 12 a.m. to June 30, 2019 11:59 p.m. This will bring up all communication reported between the filtered dates.

Step 3. Measuring frequency

To see frequency of lobbying activities by division, we select a new visualization tool. In the filters, we select POH_Office in the Axis, and under Value we enter Count of Communication. Under the Filter area and under visual Level Filters we bring the Count of Communication field, POH_Office (ALL) and POH_Type (Select Employees of the City). Under the Report Level Filters is where we filter the period to only show from January 1, 2019 12 a.m. to June 30, 2019 11:59 p.m. This will bring up all communication reported between the filtered dates and time.

In the above sample, we note that as of June 2019 the Information and Technology division was the most lobbied division in the City of Toronto. This information is not surprising as the City of Toronto has recently taken on many new technology initiatives.

Step 4. Identify types

To see which lobbyist types are lobbying City of Toronto POHs the most, we start by selecting a new visualization tool. In the filters, we select Type in the Axis, and under Value we would enter Count of Communication. To filter, we go under Visual Level Filters to bring up the Count of Communication field. We Type (ALL). Under the Report Level Filters, we can filter the period to only show items from January 1, 2019 12 a.m. to June 30, 2019 11:59 p.m.

Step 5. Location, Location, Location!

To see where the individuals or organizations that are lobbying City of Toronto POHs are located select a new visualization tool, (specifically a map) and, in the filters, Country under location. Under size we select the Count of Communication field. Under the Report Level Filters, we can filter the period to only show items from January 1, 2019 12 a.m. to June 30, 2019 11:59 p.m.

When we pulled the information to determine the location of individuals and organizations that lobby the City of Toronto, it was very interesting to see individuals and organizations as far as Brazil and China were lobbying City of Toronto Public Office Holders and not just North America.

Step 6. Identify use

This information is used by members of the Media, Public and the Office of the Lobbyist Registrar. Individuals in the media use the data to report on what topics are being discussed at the City of Toronto. This information has been published in a variety of newspaper publications as well as weekly newsletters where the individual/organization takes the information from Open data and explains in further detail the topics that being discussed at Council or Committee level. The Office of the Lobbyist Registrar uses open data for outreach and education. We use the tool to provide the public with a breakdown of how many communications are reported, the type of lobbyists and which offices/divisions are lobbied the most and what topics these communications concern. Our office uses open data to educate the City of Toronto POHs about lobbying and related activities that involve their offices and/or divisions. This allows the POH to understand how lobbying communications directly impact their office/division. It emphasizes the importance the POH should take to ensure the individual or organization is registered with our office.

Next Steps

The Office of the Lobbyist Registrar intends to continue using our Open data set to further tell the story of the lobbying communication that is taking place at City of Toronto. Further we are going to continue to explore different ways to use the data to make it more meaningful for City of Toronto Public Office Holders and for members of the Public. This will give them a better understanding of how lobbying activities can affect them directly.