<div dir="ltr">I'm also available Tues evening. Otherwise I'll keep making updates from home. :)<br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Aug 25, 2019 at 4:43 PM Katie Wilson <<a href="mailto:katie@transitriders.org">katie@transitriders.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div style="overflow-wrap: break-word;">This is awesome, thank you Stephen. I put some thoughts in-line<font color="#ff2600"> in red </font>below, and attached a hard-to-interpret spreadsheet with info about Business Choice participants.<div><br></div><div>I will try to schedule some time this week to start completing rows for the businesses I’m sure about based on the info we have. If anyone wants to get together for a spreadsheet workparty this week let me know, I have time Tuesday and Friday evenings.<div><br></div><div>Katie<br><div><br></div><div><div><blockquote type="cite"><div>On Aug 25, 2019, at 3:37 PM, Stephen DeSanto <<a href="mailto:rachidian@gmail.com" target="_blank">rachidian@gmail.com</a>> wrote:</div><br class="gmail-m_2768466561937619549Apple-interchange-newline"><div><div dir="ltr"><div>Hi everyone. Made a few updates to the <a href="https://docs.google.com/spreadsheets/d/1HmOcG7hJLD1G0unCMPcsDnXr4RIA_PMKEE5ne-hhQR8/edit?usp=sharing" target="_blank">master list of employers</a> for the upcoming website:</div><div><ul><li>Added companies and transit benefits raw descriptions from TRU survey data<br></li><li>Added "Likely CTR Targets": <a href="https://seattletransitpasses-research.pbworks.com/w/page/133438365/Likely%20Target%20Assessment" target="_blank">https://seattletransitpasses-research.pbworks.com/w/page/133438365/Likely%20Target%20Assessment</a></li><li>Added "Potential CTR Targets": <a href="https://seattletransitpasses-research.pbworks.com/w/page/133437828/Potential%20CTR%20Targets" target="_blank">https://seattletransitpasses-research.pbworks.com/w/page/133437828/Potential%20CTR%20Targets</a></li><li>"Likely" / high-profile targets (hotels, banks) are highlighted in ORANGE</li><li>Added "Potential Poster Children": <a href="https://seattletransitpasses-research.pbworks.com/w/page/133439169/Potential%20Poster%20Children" target="_blank">https://seattletransitpasses-research.pbworks.com/w/page/133439169/Potential%20Poster%20Children</a></li><li>Poster children are highlighted in GREEN</li></ul><div>Highlight colors are just to make it easier to find rows that a) someone said should be included in the list, and b) probably needs benefits data incorporated</div><div><br></div><div>Things to be done:</div><div><ul><li>Normalize locations data <font color="#ff2600">For this and the “leaf scores” and the <span>“polluter</span>” columns, I’d be inclined to do this after we’ve got enough info to check the “publish” box. I could be wrong, but I feel like it will be less work that way.</font></li><li>Assign "leaf scores" to all companies that don't have one<br></li><li>Assign "polluter" etc badges to companies we want to name&shame </li><li>Add all of the hotels? <a href="https://seattletransitpasses-research.pbworks.com/w/page/133666440/Hotels" target="_blank">https://seattletransitpasses-research.pbworks.com/w/page/133666440/Hotels</a> <font color="#ff2600">Yeah let’s go ahead and add them...</font></li><li>Add Choice participants? <font color="#ff2600">Good question. I did get info back from Metro on what products the choice participants are buying, I can’t remember whether I shared that with you all. Anyway, it’s attached. It’s actually a little hard to interpret (I got a tutorial from a Metro staffer) so I can try to explain by phone or in person if someone wants to dig throughthat. Maybe it makes sense to look through that info and add businesses selectively as we feel like we have a grasp on their programs.</font></li><li>Add column for Commute Seattle participants? <a href="https://seattletransitpasses-research.pbworks.com/w/page/133438167/Commute%20Seattle%20List%20of%20Passport%20Participants" target="_blank">https://seattletransitpasses-research.pbworks.com/w/page/133438167/Commute%20Seattle%20List%20of%20Passport%20Participants</a><font color="#ff2600"> I don’t think we need to do this, because this info is most likely duplicative with what we learned from Metro about passport participants. The Commute Seattle list doesn’t tell us how much of a subsidy they provide, so it’s not going to add much.</font></li></ul></div></div></div></div></blockquote><blockquote type="cite"><div><div dir="ltr"><div><div><ul><li>Citations, descriptions of benefits, etc. for companies that need it</li></ul><div>There's still a lot we're not 100% sure about for employer benefits, but we can do the best we have with what we've got, and make changes as we get new information.<br></div><div><br></div><div>My thinking was, we can use the "master employer list" to get as much information about the companies we're interested in. When we're satisfied that a row is finished and ready for publication, check the checkbox in the "__publish" column. Then, when we export this data to the website, we can only get the rows where "__publish" is checked. This hopefully will ensure that someone manually reviewed and verified all the data for an employer before it gets published, and that unfinished rows won't be accidentally exported.</div><div><br></div><div>Is this helpful? Am I just spinning my wheels in the mud?<br></div></div></div></div></div></blockquote><div></div></div></div></div></div></div><div style="overflow-wrap: break-word;"><div><div><div><div><div></div><br><blockquote type="cite"><div><br><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" target="_blank">rachidian@gmail.com</a>> wrote:<br></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">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></div><br><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">tmchartrand@gmail.com</a>> wrote:<br></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">Oh you're right, sorry for the confusion everyone! was just fairly hidden in the view i looked at. Column S!<br></div><br><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">katie@transitriders.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>I think the spreadsheet with PII removed still does include the Employer column, no?<div><br></div><div>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>
<div><br><blockquote type="cite"><div>On Aug 19, 2019, at 6:26 PM, Stephen DeSanto <<a href="mailto:rachidian@gmail.com" target="_blank">rachidian@gmail.com</a>> wrote:</div><br class="gmail-m_2768466561937619549gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959Apple-interchange-newline"><div><div dir="ltr"><div>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><br></div><div>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></div></div><br><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">tmchartrand@gmail.com</a>> wrote:<br></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"><div>This is looking great, Stephen!</div><div>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>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></div><div>Katie, maybe you could help coordinate this and make sure Mike sees this sooner rather than later?</div><div><br></div><div>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></div></div><br><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">rachidian@gmail.com</a>> wrote:<br></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"><div>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><br></div><div>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><br></div><div>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></div></div><br><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">harryb.maher@gmail.com</a>> wrote:<br></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"><div>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><br></div><div>-Harry<br> </div></div><br><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">tmchartrand@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<div class="gmail-m_2768466561937619549gmail-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_2768466561937619549gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959gmail-m_5129038793314189063gmail-m_480154240735209042gmail-m_-1556232740971559146gmail-m_-1504815786589700739gmail-m_427202386815225712gmail-m_-704421609936693401moz-cite-prefix"><br>
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">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>
On 8/10/19 6:42 PM, Katie Wilson wrote:<br>
</div>
<blockquote type="cite">
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">“CTR Network Areas” as defined
here</a>.<br>
<div><br>
</div>
<div>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">“Employment Sector” categories
listed on Page 12 of this CTR strategic plan.</a></div>
<div><br>
</div>
<div>
<div>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><br>
</div>
<div>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><br>
</div>
<div>We don’t have any major sources of data on what
benefits employers provide other than:</div>
<div>— <a href="https://seattletransitpasses-research.pbworks.com/w/page/133438080/First%20Public%20Records%20Request" target="_blank">Metro public disclosure
request spreadsheet</a></div>
<div>— Our commute survey</div>
<div>— 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">relevant tables in the wiki</a>,
on CTR employers and “potential poster children” and “likely
target assessment” and “hotels”)</div>
<div><br>
</div>
<div>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><br>
</div>
<div>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">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><br>
</div>
<div>(Also speaking of Seattle Coffee Works they have
locations in <a href="https://www.seattlecoffeeworks.com/our-cafes.aspx" target="_blank">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><br>
</div>
<div>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><br>
</div>
<div>
<div>
<div>
<blockquote type="cite">
<div>On Aug 8, 2019, at 5:26 PM, Stephen DeSanto
<<a href="mailto:rachidian@gmail.com" target="_blank">rachidian@gmail.com</a>>
wrote:</div>
<br class="gmail-m_2768466561937619549gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959gmail-m_5129038793314189063gmail-m_480154240735209042gmail-m_-1556232740971559146gmail-m_-1504815786589700739gmail-m_427202386815225712gmail-m_-704421609936693401Apple-interchange-newline">
<div>
<div dir="ltr">
<div>Hi everyone,</div>
<div><br>
</div>
<div>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><br>
</div>
<div>{<br>
"employer": string,<br>
"industry": [string],<br>
"neighborhood": [string],<br>
"alias": [string],<br>
"rating": int,</div>
<div> "description": string<br>
</div>
<div> "badges": [string]<br>
}</div>
<div><br>
</div>
<div><b>Employer</b> is a plain text
string.</div>
<div><b>Industry</b> is a list of
strings (or a single string, if we want to limit one
employer = one industry).</div>
<div><b>Neighborhood</b> is treated
similarly to industry<br>
</div>
<div><b>Alias</b> is a list of other
names for the same company. For example, <br>
</div>
<div><b>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><b>Description</b> is a string
that describes the employer's transit benefits, i.e.
why they got the rating they did.<br>
</div>
<div><b>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><br>
</div>
<div>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><br>
</div>
<div>As an example, take a hypothetical
record for Seattle Coffee Works.</div>
<div><br>
</div>
<div>{</div>
<div> "employer": "Seattle Coffee Works",<br>
"industry": ["restaurant"],<br>
"neighborhood": ["cbd", "ballard"],<br>
"alias": ["Ballard Coffee Works"],<br>
"rating": 4,</div>
<div> "description": "Provides 100% ORCA
Passport subsidy."<br>
</div>
<div> "badges": ["leader"]</div>
<div>}<br>
</div>
<div><br>
</div>
<div><b>Where Our Data Lives (For
Now)</b><br>
</div>
<div><br>
</div>
<div>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><br>
</div>
<div><a href="https://docs.google.com/spreadsheets/d/1HmOcG7hJLD1G0unCMPcsDnXr4RIA_PMKEE5ne-hhQR8/edit?usp=sharing" target="_blank">https://docs.google.com/spreadsheets/d/1HmOcG7hJLD1G0unCMPcsDnXr4RIA_PMKEE5ne-hhQR8/edit?usp=sharing</a></div>
<div><br>
</div>
<div>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>
</div>
<div><br>
</div>
<div>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><br>
</div>
<div>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>
</div>
<div><br>
</div>
<div>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><br>
</div>
<div><b>Things To Do Next</b><br>
</div>
<div><br>
</div>
<div>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><br>
</div>
<div>For the data sources we have, we'll have
to start filling out the rest of the spreadsheet, I
guess?<br>
</div>
<div><br>
</div>
<div>Also, we will need to determine:</div>
<div>a) master list of "industries" we want
to support, and</div>
<div>b) "industry" field(s) for each employer</div>
<div>c) "neighborhood" field(s) for each
employer we don't have one for (or being more
precise than what I have now)<br>
</div>
<div>d) which companies get tagged with which
badges</div>
<div><br>
</div>
<div>Hope that helps.<br>
</div>
<div><br>
</div>
<div>In solidarity,</div>
<div><br>
</div>
<div>Stephen<br>
</div>
<div><br>
</div>
</div>
<br>
</div>
</blockquote>
</div>
<br>
</div>
</div>
<br>
<fieldset class="gmail-m_2768466561937619549gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959gmail-m_5129038793314189063gmail-m_480154240735209042gmail-m_-1556232740971559146gmail-m_-1504815786589700739gmail-m_427202386815225712gmail-m_-704421609936693401mimeAttachmentHeader"></fieldset>
<pre class="gmail-m_2768466561937619549gmail-m_-6181029562201362709gmail-m_-5685287437219027194gmail-m_-8703738528191962959gmail-m_5129038793314189063gmail-m_480154240735209042gmail-m_-1556232740971559146gmail-m_-1504815786589700739gmail-m_427202386815225712gmail-m_-704421609936693401moz-quote-pre"></pre>
</blockquote>
</div>
<br>
</blockquote></div>
<br>
</blockquote></div>
</blockquote></div>
</blockquote></div>
<br></div></blockquote></div><br></div></div></blockquote></div>
</blockquote></div>
</blockquote></div>
</div></blockquote></div><br></div></div></div></div></blockquote></div>