There are many types of calculations that you can perform using the B Impact Data dataset that we have available on Data.World. First, we will cover how to access and download data from the database, and then cover some examples of questions that can be answered using it. You will need to perform SQL queries to export data, but it isn't that tricky! We'll go over some basics in this tutorial.
Sign up for a Data.World Account
In data.world, hit the “Explore this dataset” button. This will open up the dataset in a workspace where you may perform SQL queries.
2a. Alternatively, you can hit the download button at the top-right corner of the page and filter the dataset using Excel. Please read up on the correct filters to include based on different calculations in step 3.
To start you off, we included a SQL query called “All B Corps”, which filters down to currently certified B Corps. This is done using the two filters:
WHERE current_status = 'certified'
and certification_cycle = 1
This dataset includes previously certified B Corps along with all current B Corps.
To view only current B Corps, make sure to filter:
current_status = ‘certified’
If you only want one row of data per company, make sure to include the filter:
certification_cycle = 1
This variable is a numerical indicator that ranks how recent the company's assessment was rated (i.e. certified). When certification_cycle = 1, that would indicate it is the company's most recent assessment. A company's second most recent assessment would get labeled a 2, etc.
To execute the SQL query and download this dataset, hit “Run Query” and then hit “Download” and download in whichever format you’d like.
Let’s say we want to see a list of companies, along with their size and website, that are based in Lancaster, PA. We would run the following SQL query:
select company_name, size, website from b_corp_impact_data WHERE current_status = 'certified' and certification_cycle = 1 and state = "Pennsylvania" and city = 'Lancaster'
Notice that we added two lines to the WHERE statement:
and state = "Pennsylvania"
and city = 'Lancaster'
They are both filters. Note that we have to type both words exactly as it is shown in the database.
Now, instead, what if we wanted to see companies that were based in Lancaster in the Service industry?
We can add a filter that uses regex to search the variable sector and return instances where the value contains the word “Service”. Here are some tips:
- By using a lower() function, the variable sector turn into all lowercase letters, so it is easy to search.
- Type in your search key in between these characters ‘%SEARCH GOES HERE%’
select company_name, size, website from b_corp_impact_data WHERE current_status = 'certified' and certification_cycle = 1 and state = "Pennsylvania" and city = 'Lancaster' and lower(sector) LIKE ‘%service%’
Next, let’s say we wanted to see the number of B Corps in Lancaster in the service industry broken down by size. Here are some tips:
- We can run a query that includes a GROUP BY, allowing us to group different variables together and perform calculations.
- For those familiar with Excel Pivot Tables, these are similar. For more info, check out this help guide.
Select size, count(company_name) from b_corp_impact_data WHERE current_status = 'certified' and certification_cycle = 1 and state = "Pennsylvania" and city = 'Lancaster' and lower(sector) LIKE ‘%service%’ GROUP BY size
For the final example, let’s say we want to see how well these companies are doing on the BIA.
This dataset contains all of the impact topic, impact area, and overall scores for all of these companies. If we were interested in how well these companies performed on the impact topic “Land, Office, Plant”, then we could easily bring that datapoint in and run it.
Include a filter to only show companies that have data for the impact topic for a simple output. We can also use an ORDER BY filter, so that scores that are the highest show up at the top. Here is an example:
Select company_name, ia_environment_it_land_office_plant from b_corp_impact_data WHERE current_status = 'certified' and certification_cycle = 1 and state = "Pennsylvania" and city = 'Lancaster' and lower(sector) LIKE ‘%service%’ and ia_environment_it_land_office_plant IS NOT NULL ORDER BY ia_environment_it_land_office_plant DESC
Notice the naming convention of ia_environment_it_land_office_plant. This naming schema reflects the hierarchy of the BIA, allowing you to see that the impact topic (land_office_plant) is located within the impact area of Environment.