<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class=""><div class=""><div class=""><div class=""><div><div></div><br class=""><blockquote type="cite" class=""><div class=""><br class=""><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Aug 22, 2019 at 7:06 PM Stephen DeSanto <<a href="mailto:rachidian@gmail.com" class="">rachidian@gmail.com</a>> wrote:<br class=""></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr" class="">FYI, added most of you as editors on the spreadsheet I'm working on, in case anyone has time for tedious data tasks (or a quick way to do tedious data tasks). I'm currently adding in data from the TRU survey, from respondents whose employers offer transit benefits. Eventually, we'll need these tagged with industry and fix the neighborhoods data? And add in any other company data we have from the other research spreadsheets on the wiki? And eventually some subset of this data ends up on the website?<br class=""></div><br class=""><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Aug 20, 2019 at 3:03 PM Tom Chartrand <<a href="mailto:tmchartrand@gmail.com" target="_blank" class="">tmchartrand@gmail.com</a>> wrote:<br class=""></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr" class="">Oh you're right, sorry for the confusion everyone! was just fairly hidden in the view i looked at. Column S!<br class=""></div><br class=""><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Aug 20, 2019 at 3:00 PM Katie Wilson <<a href="mailto:katie@transitriders.org" target="_blank" class="">katie@transitriders.org</a>> wrote:<br class=""></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div class="">I think the spreadsheet with PII removed still does include the Employer column, no?<div class=""><br class=""></div><div class="">Sorry I’m being slow to respond to all this good stuff, I am still digging myself out from being away last week and I’m at an all-day thing today… but I should have time to pay more attention before the end of the week!<br class="">
<div class=""><br class=""><blockquote type="cite" class=""><div class="">On Aug 19, 2019, at 6:26 PM, Stephen DeSanto <<a href="mailto:rachidian@gmail.com" target="_blank" class="">rachidian@gmail.com</a>> wrote:</div><br class="gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959Apple-interchange-newline"><div class=""><div dir="ltr" class=""><div class="">I have time to go through the survey data and find the reported transit benefits per employer, though I'll need the data set that contains that data. :)</div><div class=""><br class=""></div><div class="">Otherwise, I am going to be trying to match CTR neighborhoods to the employers already in our spreadsheet, as well as adding any employers mentioned in other sources/sheets on our wiki.<br class=""></div></div><br class=""><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Aug 19, 2019 at 6:02 PM Tom Chartrand <<a href="mailto:tmchartrand@gmail.com" target="_blank" class="">tmchartrand@gmail.com</a>> wrote:<br class=""></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr" class=""><div class="">This is looking great, Stephen!</div><div class="">I had put myself down to organize the survey data with respect to employers for this, but I just realized that info was removed as PII (of course)! So either Mike will need to take that on (I think Mike did the original PII removal) or we'll need to figure out an appropriate way of sharing that.</div><div class="">I'm feeling pretty swamped myself lately, so if you (Stephen) were down to help him with the task that could be great. I can certainly still take on some of it if needed though, once we get this sorted out.<br class=""></div><div class="">Katie, maybe you could help coordinate this and make sure Mike sees this sooner rather than later?</div><div class=""><br class=""></div><div class="">Also, do let me know if you have any more specific spots in the report where some backup from the PSRC dataset could be useful!<br class=""></div></div><br class=""><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Aug 18, 2019 at 3:59 PM Stephen DeSanto <<a href="mailto:rachidian@gmail.com" target="_blank" class="">rachidian@gmail.com</a>> wrote:<br class=""></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr" class=""><div class="">I've added the list of industry categories to the Google Sheet, so that should help validate the data we add there, though it's going to likely be a manual task to fill in industries for all the employers.</div><div class=""><br class=""></div><div class="">I've also added a "citation" column, which can be the public representation of where we got the data to make our claim. We can fuss with the wording later.</div><div class=""><br class=""></div><div class="">I should have time this week to go through our survey data and other wiki tables to add or modify employers in the Google Sheet. Agree that it'll be good to have solid information on our primary targets and champions.<br class=""></div></div><br class=""><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Aug 13, 2019 at 10:48 PM Harry Maher <<a href="mailto:harryb.maher@gmail.com" target="_blank" class="">harryb.maher@gmail.com</a>> wrote:<br class=""></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr" class=""><div class="">Just a quick update with regard to qualitative data analysis: I made a "Commute Survey Qualitative Data Analysis" folder on pbworks and put a doc with some quotes in it for the report. I tried to pull out the main relevant themes that I noticed discussed in the two qualitative questions currently in the file with a couple of quote options for each theme/category of response to the question.</div><div class=""><br class=""></div><div class="">-Harry<br class=""> </div></div><br class=""><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Aug 11, 2019 at 12:54 PM Tom Chartrand <<a href="mailto:tmchartrand@gmail.com" target="_blank" class="">tmchartrand@gmail.com</a>> wrote:<br class=""></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div class="">
<div class="gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959gmail-m_5129038793314189063gmail-m_480154240735209042gmail-m_-1556232740971559146gmail-m_-1504815786589700739gmail-m_427202386815225712gmail-m_-704421609936693401moz-cite-prefix">Regarding where to have this discussion
- I'm just gonna continue the email chain cause I haven't followed
where to put the discussion on the wiki, but someone feel free to
steer it over there if we want to!</div>
<div class="gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959gmail-m_5129038793314189063gmail-m_480154240735209042gmail-m_-1556232740971559146gmail-m_-1504815786589700739gmail-m_427202386815225712gmail-m_-704421609936693401moz-cite-prefix"><br class="">
A brief update regarding establishing a larger list of employers
to include in the dataset: basic contact information for all
seattle businesses, sorted by the North American Industry
Classification System, is available at <a href="https://web6.seattle.gov/fas/slimbizsearch/ResultsPage.aspx?NAICList=Top100" target="_blank" class="">https://web6.seattle.gov/fas/slimbizsearch/ResultsPage.aspx?NAICList=Top100</a>,
but it's a huge list of course, with no info on number of
employees or revenue to filter out the smaller ones. Still, I did
send off an email about getting a copy of the database just for
purposes of cross-referencing names and such.<br class="">
On 8/10/19 6:42 PM, Katie Wilson wrote:<br class="">
</div>
<blockquote type="cite" class="">
For “neighborhood” I think it makes sense to use the <a href="https://www.seattle.gov/transportation/projects-and-programs/programs/transportation-options-program/commute-trip-reduction-program/draft-2019-2023-networks-and-targets" target="_blank" class="">“CTR Network Areas” as defined
here</a>.<br class="">
<div class=""><br class="">
</div>
<div class="">For “industry” I think it makes sense to use the <a href="https://www.seattle.gov/Documents/Departments/SDOT/TransportationOptionsProgram/CTR_Draft_Strategic_Plan_Jan2019.pdf" target="_blank" class="">“Employment Sector” categories
listed on Page 12 of this CTR strategic plan.</a></div>
<div class=""><br class="">
</div>
<div class="">
<div class="">On the ratings, I think it does make sense to
lump "piggy bank" and "brown tortoise" in the same rating (0),
and then add a tortoise badge for employers that aren’t even
doing the pre-tax thing.</div>
<div class=""><br class="">
</div>
<div class="">Another simplification option to consider would be
to lump together 3 and 4 leaves. But let’s leave them separate
for now and depending on how things shake out we can easily
combine them later.</div>
</div>
<div class=""><br class="">
</div>
<div class="">We don’t have any major sources of data on what
benefits employers provide other than:</div>
<div class="">— <a href="https://seattletransitpasses-research.pbworks.com/w/page/133438080/First%20Public%20Records%20Request" target="_blank" class="">Metro public disclosure
request spreadsheet</a></div>
<div class="">— Our commute survey</div>
<div class="">— Info gleaned online from company websites, asking
around, glassdoor etc (what I’ve found I’ve added to the <a href="https://seattletransitpasses-research.pbworks.com/w/page/132177123/Employers" target="_blank" class="">relevant tables in the wiki</a>,
on CTR employers and “potential poster children” and “likely
target assessment” and “hotels”)</div>
<div class=""><br class="">
</div>
<div class="">Maybe it makes sense to have another string
indicating sufficient certainty — when we have two sources, or
one very reliable source, we enter an X or whatever, and that
gives us the green light to display that data. Also it may not
make sense to put a lot of work into categorizing employers into
Network Area and Employment Sector until we have reliable data
on what benefits they’re offering.</div>
<div class=""><br class="">
</div>
<div class="">Speaking of Seattle Coffee Works, I spoke with their
HR person a few months ago and actually employees have to pay
$20/month (pre-tax $) if they want an ORCA card. Still a great
deal but not 100% subsidy as reported in the Metro data— which,
I then learned, is self-reported by the company. Metro only
knows that all those companies are signed up for the Passport
program. I noted the real situation <a href="https://seattletransitpasses-research.pbworks.com/w/page/133439169/Potential%20Poster%20Children" target="_blank" class="">on this page</a>. Anyway, the
point is we should probably crosscheck the Metro data as much as
we can with our survey or other sources of information.
<div class=""><br class="">
</div>
<div class="">(Also speaking of Seattle Coffee Works they have
locations in <a href="https://www.seattlecoffeeworks.com/our-cafes.aspx" target="_blank" class="">Capitol Hill & Cascade
too</a>. From talking with the HR person I’m pretty sure all
are include in their passport program, and the employees swap
around a lot from location to location. They probably use the
Ballard location as home base for transit pass purposes since
that’s the least expensive zone.)</div>
</div>
<div class=""><br class="">
</div>
<div class="">One project would be to come up with a list of
employers that have name recognition (or that we are interested
in for some other reason) and put a little work into attaining
sufficient certainty. If we posted the list to a page and put a
call out on social media and email I bet we’d get some answers.</div>
<div class=""><br class="">
</div>
<div class="">
<div class="">
<div class="">
<blockquote type="cite" class="">
<div class="">On Aug 8, 2019, at 5:26 PM, Stephen DeSanto
<<a href="mailto:rachidian@gmail.com" target="_blank" class="">rachidian@gmail.com</a>>
wrote:</div>
<br class="gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959gmail-m_5129038793314189063gmail-m_480154240735209042gmail-m_-1556232740971559146gmail-m_-1504815786589700739gmail-m_427202386815225712gmail-m_-704421609936693401Apple-interchange-newline">
<div class="">
<div dir="ltr" class="">
<div class="">Hi everyone,</div>
<div class=""><br class="">
</div>
<div class="">I've taken a first pass at the data
schema for showing employer transit benefits in our
upcoming web app. In this draft, each employer
record is represented as follows:</div>
<div class=""><br class="">
</div>
<div class="">{<br class="">
"employer": string,<br class="">
"industry": [string],<br class="">
"neighborhood": [string],<br class="">
"alias": [string],<br class="">
"rating": int,</div>
<div class=""> "description": string<br class="">
</div>
<div class=""> "badges": [string]<br class="">
}</div>
<div class=""><br class="">
</div>
<div class=""><b class="">Employer</b> is a plain text
string.</div>
<div class=""><b class="">Industry</b> is a list of
strings (or a single string, if we want to limit one
employer = one industry).</div>
<div class=""><b class="">Neighborhood</b> is treated
similarly to industry<br class="">
</div>
<div class=""><b class="">Alias</b> is a list of other
names for the same company. For example, <br class="">
</div>
<div class=""><b class="">Rating</b> is a numerical
scale that represents the "worker's monthly cost of
an unlimited transit pass". The scale provided
during the meeting went from "4 leaves" to "brown
tortoise"; aligning to the leaves, that gives us a
scale of [-1, 0, 1, 2, 3, 4]. We could adjust this
up to 0-5, or lump "piggy bank" and "brown tortoise"
in the same rating.</div>
<div class=""><b class="">Description</b> is a string
that describes the employer's transit benefits, i.e.
why they got the rating they did.<br class="">
</div>
<div class=""><b class="">Badges</b> is a list of
strings that represent any additional categories we
want to assign to a company (e.g. "industry leader",
"polluter").</div>
<div class=""><br class="">
</div>
<div class="">We can make changes to this schema if it
makes it easier to work with our underlying data
visualization platform (e.g. Tableau? DataTables?),
but hopefully this is a suitable starting place.</div>
<div class=""><br class="">
</div>
<div class="">As an example, take a hypothetical
record for Seattle Coffee Works.</div>
<div class=""><br class="">
</div>
<div class="">{</div>
<div class=""> "employer": "Seattle Coffee Works",<br class="">
"industry": ["restaurant"],<br class="">
"neighborhood": ["cbd", "ballard"],<br class="">
"alias": ["Ballard Coffee Works"],<br class="">
"rating": 4,</div>
<div class=""> "description": "Provides 100% ORCA
Passport subsidy."<br class="">
</div>
<div class=""> "badges": ["leader"]</div>
<div class="">}<br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><b class="">Where Our Data Lives (For
Now)</b><br class="">
</div>
<div class=""><br class="">
</div>
<div class="">I've also taken a rough chop at getting
started with the data. Here, I've just taken the raw
list of ORCA Business Passport employers and
assigned a score based on their subsidy percentage,
as an example:</div>
<div class=""><br class="">
</div>
<div class=""><a href="https://docs.google.com/spreadsheets/d/1HmOcG7hJLD1G0unCMPcsDnXr4RIA_PMKEE5ne-hhQR8/edit?usp=sharing" target="_blank" class="">https://docs.google.com/spreadsheets/d/1HmOcG7hJLD1G0unCMPcsDnXr4RIA_PMKEE5ne-hhQR8/edit?usp=sharing</a></div>
<div class=""><br class="">
</div>
<div class="">The spreadsheet contains columns for
each item of the employer record, as well as some
additional columns to record the raw data we have on
file for that employer, so we can use that data to
automatically or manually determine an employer's
rating.<br class="">
</div>
<div class=""><br class="">
</div>
<div class="">If we have data from other sources not
listed (e.g. survey data, City of Seattle data), the
"source_" columns can be renamed or added to
represent that source's data. For example, if I want
to add data from the TRU survey, I might rename
"__source_b" to "__TRU Survey", then include results
from that survey in that column for each company.
(The columns beginning with two underscores are ones
I don't expect to be publicly available.)</div>
<div class=""><br class="">
</div>
<div class="">PBworks feels really inadequate for
editing large data sets, and I don't know where else
to put it, so it's living in Google Sheets for now.
Set to read-only with the link, for now, but please
request editing permissions so you can add stuff to
the sheet.<br class="">
</div>
<div class=""><br class="">
</div>
<div class="">Currently, my expectation is that the
spreadsheet will be hand-edited in Google Sheets,
and then when we're ready to put live data in the
web app, we can export the sheet to a flat file,
which we can then import into a format appropriate
for the website (big ol' JSON file, database,
whatever). Manual process, but probably fine for a
project of this scale; I'm open to alternatives. </div>
<div class=""><br class="">
</div>
<div class=""><b class="">Things To Do Next</b><br class="">
</div>
<div class=""><br class="">
</div>
<div class="">Aside from the ORCA Passport data and
the data we collected through TRU survey / legwork
(on PBworks), do we have any other data sources that
would provide context for a score?</div>
<div class=""><br class="">
</div>
<div class="">For the data sources we have, we'll have
to start filling out the rest of the spreadsheet, I
guess?<br class="">
</div>
<div class=""><br class="">
</div>
<div class="">Also, we will need to determine:</div>
<div class="">a) master list of "industries" we want
to support, and</div>
<div class="">b) "industry" field(s) for each employer</div>
<div class="">c) "neighborhood" field(s) for each
employer we don't have one for (or being more
precise than what I have now)<br class="">
</div>
<div class="">d) which companies get tagged with which
badges</div>
<div class=""><br class="">
</div>
<div class="">Hope that helps.<br class="">
</div>
<div class=""><br class="">
</div>
<div class="">In solidarity,</div>
<div class=""><br class="">
</div>
<div class="">Stephen<br class="">
</div>
<div class=""><br class="">
</div>
</div>
<br class="">
</div>
</blockquote>
</div>
<br class="">
</div>
</div>
<br class="">
<fieldset class="gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959gmail-m_5129038793314189063gmail-m_480154240735209042gmail-m_-1556232740971559146gmail-m_-1504815786589700739gmail-m_427202386815225712gmail-m_-704421609936693401mimeAttachmentHeader"></fieldset>
<pre class="gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959gmail-m_5129038793314189063gmail-m_480154240735209042gmail-m_-1556232740971559146gmail-m_-1504815786589700739gmail-m_427202386815225712gmail-m_-704421609936693401moz-quote-pre"></pre>
</blockquote>
</div>
<br class="">
</blockquote></div>
<br class="">
</blockquote></div>
</blockquote></div>
</blockquote></div>
<br class=""></div></blockquote></div><br class=""></div></div></blockquote></div>
</blockquote></div>
</blockquote></div>
</div></blockquote></div><br class=""></div></div></div></body></html>