Creating "The Company" Data (pt.1)

http://dilbert.com/strip/2006-11-11

As stated previously, one of the things we wanted to work on with Koluit is something called "The Company". This is a fake company that will be using discuss different People Operations/Analytics ideas. The biggest part of this involved making fake data that we could use to make "The Company" seem more real. Here we will walkthrough how this data was created and the reasons behind some of the steps we took.


Data Creation (Basic Info): First to create the data we started with some basic information from FakeNameGenerator.com.

1. Given Name
2. Surname
3. Gender
4. StreetAddress
5. City
6. State
7. StateFull
8. Zipcode
9. Country (This was just a two letter country code)
10. CountryFull
11. Birthday
12. Age

Whats great about this site is you are able to pick Name Sets and Countries. We ran a few different lists because we did not want an equal number of each country.

We ran a large American set (4000 names) because this is where my fake company's HQ and majority of offices will be. We then ran a few smaller lists, Japanese (50 names),  Chinese (72 names), United Kingdom (250 names), and Norway (150 names).


Now while this gave us something to work with, there is still a lot more to add and some data cleaning that needs to be done.


Adding Ethnicity (US Only)

Fakenamegenerator.com was able to provide gender for the names that they gave but we did not see an option for ethnicity.  To do this we created a mapping table with the EEOC defined ethnicities (see here).  You can find the Mapping Table here. We decided to have the employees map to an ethnicity based on a random number they would be given. While we could have given them a number from 1-7 (which is the number of ethnicity options) we decided against this because this would spread the number of ethnicities equally.  This isn't very realistic. We wanted to make this company similar to your average Tech company, which currently leans very white and asian (Google, Facebook, Salesforce, LinkedIn, and etc.). We are setting up my fictions company as similar to a real company so that hopefully we can talk about some of these real life issues including diversity/inclusion.

We used the randombetween(1,100) function in a new column to give a random number between 1-100.

The we created another column called Ethnicity and added this formula, starting at cell P2.

=if(O2<=52,Mapping!$B$3,if(O2<85,Mapping!$B$6,if(O2<91,Mapping!$B$2,if(O2<96,Mapping!$B$4,if(O2<98,Mapping!$B$8,if(O2=99,Mapping!$B$5,if(O2=100,Mapping!$B$7,"ERROR")))))))

If you are new to Excel type formulas this might be overwhelming. Just know that if the random number is:

• Less than or equal to 52 the formula went to the mapping table and showed B3 which is "White"
• Less than 91 is B2  = "Hispanic or Latino"
• Less than 96  B4= "Black or African American"
• Less than 98 is B8 = "Two or More Races"
• Equal to 99 is B5 = "Native Hawaiian or Pacific Islander"
• Equal to 100 is B7 = "Native American or Alaska Native" 

*If you noticed we made a mistake in the formula, good catch! Anytime 98 was the number we got the "ERROR" as a result. It should have been <=98 not <. For those errors, I replaced them with the ethnicity "White".

Office Location Creation:

This field was pretty easy to create because most of the information was determined when we created the original data. Japan, China (which we changed to Hong Kong), UK and Norway were all given one office location each. We chose a major city in each country and all of the employees from the country were put into that location.

The tricky part was deciding how we wanted to break up the 4k US employees. We figured at first most of the work we would do would be US focused so deciding how we wanted to create this involved a little more thought. Obviously companies have reasons for opening different offices in different locations but its a little hard to make those decisions without any real situations causing us to make those decisions. To help with this (and determining start dates) we created an "About Us" post for "The Company". We decided on 3 offices for the US. One HQ in NYC, one Technology HQ in Boulder, Colorado, and finally another office in San Jose, California.

Why did we choose those locations? Honestly it was somewhat random. We just knew we wanted to have more than two offices and have our offices split by them being Corporate and Tech focused. We also did not want to have them too close together. More offices may be added later. Or some of these offices may be split.

Start Date / Termination Date:

Most of the fields we just wanted to have data in first so we could see it all and then tweak and clean the data once it was all there. This was especially true for the Start and Termination Date.

To create Start Dates we broke the data up by Office location to start. For each office we would pick one employee and manually match them to the date on the About Us page. From there we used this formula:

=[First Employee's Start Date]+(7*RANDBETWEEN(1,510))

The "First Employee's Start Date" is just a reference to the manually created date. We also changed the randomdbetween number from 510 to smaller numbers for newer office locations. We didn't want to have employees with start dates to far into the future.

So what this calculation is doing is using the start date of the fist employee and adding a random number of weeks to it to determine the start date for every other employee.

Termination Date:

For Termination Date we used this formula:

=if(RANDBETWEEN(1,10)<=3,($R2+(7*RANDBETWEEN(3,250))+RANDBETWEEN(0,4)),"")

So because we only wanted some employees to have a Termination date we randomly selected a number between 1 and 10. If that number was less than or equal to 3, it then randomly created a termination date. The Termination Date is the Start Date + 7*random number between 3 and 250. This way the termination date ranges at minimum 3 weeks after the start date and at most 2500 weeks after the start date. Which would mean the maximum is around 4.8 years after the start date. Now because we were doing it by week, this meant that an employees Termination Date was always the same day of week as their start date, which for everyone who isn't an office location founder would be a Monday currently. To fix this we added another RANDBETWEEN with a range of 0-4. This just changed what day of that week the term would occur.



More posts to follow about other fields and how they were created and the reasoning behind some of the decisions!