Using SQL in B Corp Impact Data (Tutorial)

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.

1. Sign up for a Data.World Account

2. 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.

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.

4. To execute the SQL query and download this dataset, hit “Run Query” and then hit “Download” and download in whichever format you’d like.

Examples

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.

Running a B Corp Claim:

Are you interested in determining if your company is the first B Corps in a given geographic location or industry? 

Let’s go through an example to see how one would determine this. 

First, identify the metrics in which the company believes they are the First B Corp. For example, let’s say I believe I’m the first in the Hospitality industry. To confirm this, I would run the following SQL code:

select company_name,

     industry,

     date_first_certified

from b_corp_impact_data

WHERE certification_cycle = 1

AND industry = 'Hospitality'

ORDER BY date_first_certified ASC

Untours appears to be the company at the top of our list, and so therefore, they are the first B Corp in the hospitality industry. They were certified on May 17, 2007.

Now, let’s say I want to claim I’m the first in the Hospitality industry in Africa. To confirm this, I would run the following SQL code:

select company_name,

     industry,

     country,

     date_first_certified

from b_corp_impact_data

WHERE certification_cycle = 1

AND industry = 'Hospitality'

ORDER BY date_first_certified ASC

As you can see, I simply added the country field into view, so that I could scan all companies in the Hospitality industry’s geographic location. Now, Asilia Africa Ltd. comes up on top. They were certified in June 25, 2012.

To see a list of all of the industries and geographies that can be used for first B Corp claims, please reference this SQL:

select DISTINCT industry_category,

     industry,

     country,

     sector,

     size,

     state,

     city

from b_corp_impact_data

WHERE certification_cycle = 1

You can also check out this video for more assistance: