30:T665,viewof interviews = lever(` select u.name as interviewer, i.id, case when max(f.fields->2->'value'->>0) like 'Other%' then 'Other' else max(f.fields->2->'value'->>0) end as question, max(f.fields->0->>'value') as rating, bool_or(f.fields->0->>'value' in ('3 - Hire', '4 - Strong Hire')) as pass, s.text as stage, ar.text as archived, case when bool_or(s.text in ('Reference Check', 'Offer')) then 'true' when c.archived is null then null when ar.text in ('Withdrew', 'Timing', 'Unresponsive', 'Cleanup') then 'ghosted' else 'false' end as offer, c.name as candidate, c.urls->>'show' as url, o.name as owner, i.subject, date_trunc('week', i.date) as week, i.date, p.text as posting from interviews i join interviewers ii on ii.interview = i.id join users u on ii.user = u.id join candidates c on i.candidate = c.id join users o on c.owner = o.id join stages s on c.stage = s.id join postings p on (i.postings->>0)::uuid = p.id left join feedback f on f.interview = i.id left join archive_reasons ar on (c.archived->>'reason')::uuid = ar.id where i.feedback_template IN ('2be4e745-1471-45f7-bd66-42f91ae4e7eb', '9f85463e-5739-4057-968b-79bf6042a76b', 'cd12166a-4f57-4a3a-bde2-ba28cbad65ff') and ('${interviewer}' = '' or u.name = '${interviewer}') and i.canceled_at is null and i.date between now() - interval '${weeks-3} weeks' and now() + interval '3 weeks' group by u.name, i.id, s.text, ar.text, c.name, c.urls->>'show', o.name, c.archived, p.text order by date_trunc('week', i.date) desc, u.name asc `)31:[] 2e:{"title":"Phone screens","cells":[{"id":0,"value":"md`# Phone screens`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1297,"value":"viewof interviewer = select({\n title: 'Interviewer',\n description: 'Filter to a specific interviewer.',\n options: [ { label: 'All', value: '' }, ...interviewers.rows.map(i => {\n return { label: `${i.name} (${i.count})`, value: i.name }\n })],\n value: ''\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1612,"value":"viewof timeUnit = select({\n title: 'Time aggregation',\n description: null,\n options: [{ value: '', label: 'Weekly' }, { value: 'yearmonth', label: 'Monthly' }],\n value: ''\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":27,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":18,"value":"md`## Hours per hire\n\nTODO`","pinned":false,"hidden":false,"mode":"ojs"},{"id":7,"value":"md`## Predictive ability\n\nOf candidates who pass the phone screen, how many of those get an offer? Ghosted candidates never made it to an on-site.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1879,"value":"viewof includeGhosted = checkbox({\n options: [{ value: 'true', label: 'Include ghosted?' }],\n value: 'true',\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1881,"value":"interviewsPredictive = interviews.rows.filter(i => i.pass && (includeGhosted ? true : i.offer !== 'ghosted'))","pinned":false,"hidden":false,"mode":"ojs"},{"id":767,"value":"vegaEmbed({\n ...common,\n data: { values: interviewsPredictive },\n transform: [\n { calculate: `indexof(['true', 'ghosted', 'false', null], datum.offer)`, as: 'offer_order' },\n ],\n height: 100,\n mark: 'bar',\n encoding: {\n x,\n y: { aggregate: 'count', axis: { orient: 'right' } },\n color: {\n field: 'offer', type: 'nominal',\n scale: { domain: ['true', 'ghosted', null, 'false', 'false'], range: 'diverging' },\n },\n order: { field: 'offer_order', type: 'quantitative' },\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":878,"value":"vegaEmbed({\n ...common,\n data: { values: interviewsPredictive },\n transform: [\n { calculate: `indexof(['true', 'ghosted', 'false', null], datum.offer)`, as: 'offer_order' },\n ],\n height: 100,\n mark: 'bar',\n encoding: {\n x,\n y: { aggregate: 'count', stack: 'normalize', axis: { orient: 'right', title: 'Percentage', format: '%' } },\n color: {\n field: 'offer', type: 'nominal',\n scale: { domain: ['true', 'ghosted', null, 'false', 'false'], range: 'diverging' }\n },\n order: { field: 'offer_order', type: 'quantitative' },\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1602,"value":"table(interviews, 100, i => i.pass, ['interviewer', 'candidate', 'rating', 'offer', 'date'])","pinned":false,"hidden":false,"mode":"ojs"},{"id":5,"value":"md`## Volume`","pinned":false,"hidden":false,"mode":"ojs"},{"id":678,"value":"md`### Phone screens per week\n\nHow many phone screens are we conducting per week? How many are scheduled?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":50,"value":"vegaEmbed({\n ...common,\n data: { values: interviews.rows },\n height: 100,\n encoding: {\n x,\n y: { aggregate: 'count', type: 'quantitative', axis: { orient: 'right' } },\n },\n layer: [{\n mark: 'bar',\n encoding: {\n // size: { value: (width-190)/weeks - 2 },\n color: { aggregate: 'count', type: 'quantitative', scale: { range: 'heatmap' }, legend: false },\n }\n }, {\n mark: { type: 'text', dy: -4 },\n encoding: {\n text: { aggregate: 'count', type: 'quantitative' },\n } \n }, {\n transform: [\n { filter: `toDate('${nowWeek[0]}') < datum.week && datum.week < toDate('${nowWeek[1]}')` },\n { sample: 1 },\n ],\n mark: 'text',\n encoding: {\n y: { value: -2 },\n text: { value: 'now' },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1143,"value":"md`### Source\n\nWhere are the candidates coming from?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1145,"value":"vegaEmbed({\n ...common,\n data: { values: interviews.rows },\n encoding: {\n x,\n y: { field: 'owner', type: 'nominal', sort: { op: 'count', order: 'descending' }, axis: { orient: 'right' } },\n },\n layer: [{\n mark: 'square',\n encoding: {\n size: { aggregate: 'count', type: 'quantitative', scale: { range: [40, 500] }, legend: false },\n color: { aggregate: 'count', type: 'quantitative', legend: false },\n }\n }, {\n mark: { type: 'text', dy: 4 },\n encoding: {\n text: { aggregate: 'count', type: 'quantitative' },\n color: { value: 'white' },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":680,"value":"md`### Interviewers\n\nWho is performing the phone screens?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":176,"value":"viewof atLeastTotal = slider({\n min: 0,\n max: _.max(_.values(interviewTotals))-1,\n step: 1,\n value: 10,\n title: 'Minimum interviews',\n description: 'Include interviewers who have conducted at least these many interviews in this time period.'\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":42,"value":"vegaEmbed({\n ...common,\n data: { values: interviews.rows.filter(i => interviewTotals[i.interviewer] > atLeastTotal) },\n encoding: {\n x,\n y: { field: 'interviewer', type: 'nominal', sort: { op: 'count', order: 'descending' }, axis: { orient: 'right' } },\n },\n layer: [{\n mark: 'square',\n encoding: {\n size: { aggregate: 'count', type: 'quantitative', scale: { range: [40, 500] }, legend: false },\n color: { aggregate: 'count', type: 'quantitative', scale: { range: 'heatmap' }, legend: false },\n }\n }, {\n mark: { type: 'text', dy: 4 },\n encoding: {\n text: { aggregate: 'count', type: 'quantitative' },\n color: { value: 'white' },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":140,"value":"interviewTotals = _.countBy(interviews.rows, 'interviewer')","pinned":false,"hidden":false,"mode":"ojs"},{"id":1551,"value":"md`### Posting\n\nWhat postings are we screening for?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1568,"value":"vegaEmbed({\n ...common,\n data: { values: interviews.rows.filter(i => interviewTotals[i.interviewer] > atLeastTotal) },\n encoding: {\n x,\n y: { field: 'posting', type: 'nominal', sort: { op: 'count', order: 'descending' }, axis: { orient: 'right' } },\n },\n layer: [{\n mark: 'square',\n encoding: {\n size: { aggregate: 'count', type: 'quantitative', scale: { range: [40, 500] }, legend: false },\n color: { aggregate: 'count', type: 'quantitative', scale: { range: 'heatmap' }, legend: false },\n }\n }, {\n mark: { type: 'text', dy: 4 },\n encoding: {\n text: { aggregate: 'count', type: 'quantitative' },\n color: { value: 'white' },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":13,"value":"md`## Ratings\n\nCan indicate quality of incoming sourcing. \"Strong hire\"s indicate increased sourcing bar. \"Strong no hire\"s indicate lowering sourcing bar.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":305,"value":"md`### Pass rate\n\nRate at which candidates pass the phone screen. \"3 - Hire\" and \"4 - Strong Hire\" are successes. The dotted line is the percentage of candidates who pass the phone screen.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1771,"value":"viewof includeUnresolved = checkbox({\n options: [{ value: 'true', label: 'Include candidates without ratings yet?' }],\n value: 'true',\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1773,"value":"interviewsPassRate = interviews.rows.filter(i => includeUnresolved || i.rating)","pinned":false,"hidden":false,"mode":"ojs"},{"id":290,"value":"vegaEmbed({\n ...common,\n data: { values: interviewsPassRate },\n height: 100,\n encoding: {\n x,\n },\n layer: [{\n mark: 'bar',\n encoding: {\n y: { aggregate: 'count', axis: { orient: 'right' } },\n color: { field: 'rating', type: 'ordinal', scale: { domain: ratings, range: 'diverging' } },\n }\n }, {\n mark: { type: 'text', dy: -4 },\n encoding: {\n y: { aggregate: 'count', stack: 'zero' },\n text: { aggregate: 'count', type: 'quantitative' },\n detail: { field: 'pass' },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":724,"value":"vegaEmbed({\n ...common,\n data: { values: interviewsPassRate },\n height: 100,\n encoding: {\n x,\n y: { aggregate: 'count', stack: 'normalize', axis: { orient: 'right', title: 'Percentage', format: '%' } },\n },\n layer: [{\n mark: 'bar',\n encoding: {\n color: { field: 'rating', type: 'ordinal', scale: { domain: ratings, range: 'diverging' } },\n }\n }, {\n mark: { type: 'line', interpolate: 'cardinal', tension: 0.5, strokeDash: [ 5, 3 ] },\n encoding: {\n color: { field: 'pass', type: 'nominal', scale: { domain: [true], range: ['gray'] }, legend: false },\n size: { value: 1 },\n }\n }],\n resolve: { scale: { color: 'independent' } },\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":311,"value":"md`### Score distribution`","pinned":false,"hidden":false,"mode":"ojs"},{"id":213,"value":"vegaEmbed({\n data: { values: interviewsPassRate },\n height: 100,\n hconcat: [{\n layer: [{\n mark: 'bar',\n encoding: {\n y: { field: 'rating', type: 'ordinal', scale: { domain: ratings }, axis: { orient: 'right', offset: 20 } },\n x: { aggregate: 'count', type: 'quantitative' },\n color: { field: 'rating', type: 'ordinal', scale: { domain: ratings.filter(r => r), range: 'diverging' } },\n }\n }, {\n mark: { type: 'text', dx: 12, dy: 4 },\n encoding: {\n x: { aggregate: 'count', type: 'quantitative' },\n y: { field: 'rating', type: 'ordinal', scale: { domain: ratings } },\n text: { aggregate: 'count', type: 'quantitative' },\n }\n }]\n }, {\n mark: 'bar',\n encoding: {\n x: { aggregate: 'count', type: 'quantitative', stack: 'normalize', axis: { format: '%' } },\n color: { field: 'rating', type: 'ordinal', scale: { domain: ratings.filter(r => r), range: 'diverging' } },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":390,"value":"ratings = ['4 - Strong Hire', '3 - Hire', null, '2 - No Hire', '1 - Strong No Hire']","pinned":false,"hidden":false,"mode":"ojs"},{"id":1329,"value":"md`## Questions\n\nWhat questions are being asked? How often? TODO: What are the results?`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1344,"value":"vegaEmbed({\n ...common,\n data: { values: interviews.rows.filter(i => i.question) },\n encoding: {\n x,\n y: { field: 'question', type: 'nominal', sort: { op: 'count', order: 'descending' }, axis: { orient: 'right' } },\n },\n layer: [{\n mark: 'square',\n encoding: {\n size: { aggregate: 'count', type: 'quantitative', scale: { range: [ 40, 500 ] }, legend: false },\n color: { aggregate: 'count', type: 'quantitative', legend: false, scale: { range: 'heatmap' } },\n }\n }, {\n mark: { type: 'text', dy: 4 },\n encoding: {\n text: { aggregate: 'count', type: 'quantitative' },\n color: { value: 'white' },\n }\n }]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1110,"value":"md`## Feedback\n\nCandidates we should review with recruiters and sourcers to calibrate on people we shouldn't and should be sending to phone screen.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1027,"value":"md`### 1 - Strong No Hire\n\nCandidates we should try to filter out earlier, if reasonably possible.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1021,"value":"candidateReview(interviews.rows.filter(i => i.rating == '1 - Strong No Hire'))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1102,"value":"md`### 4 - Strong Hire\n\nCandidates we should look for more.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1105,"value":"candidateReview(interviews.rows.filter(i => i.rating == '4 - Strong Hire'))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1179,"value":"md`TODO: same for on-site feedback to phone screen`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1295,"value":"viewof interviewers = lever(`\n select u.name, count(*)\n from interviews i\n join interviewers ii on ii.interview = i.id\n join users u on ii.user = u.id\n where i.feedback_template = '2be4e745-1471-45f7-bd66-42f91ae4e7eb'\n and i.canceled_at is null\n and i.date > now() - interval '${weeks} weeks'\n group by u.name\n order by count(*) desc\n`)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1099,"value":"function candidateReview(rows) {\n let ago = d3.timeWeek.offset(new Date(), -4)\n let ones = rows.filter(i => new Date(i.date) > ago)\n let grouped = d3.nest()\n .key(d => d.week)\n .entries(ones)\n let timeFormat = d3.timeFormat('%b %e, %Y')\n \n return html`\n \n \n ${grouped.map(({ key: date, values: interviews }) => html`\n \n \n ${interviews.map(interview => html`\n \n \n \n \n \n `)}\n \n `)}\n

${timeFormat(new Date(date))}
${interview.candidate}	${interview.interviewer}	${interview.owner}

\n `\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":956,"value":"md`### Common configuration`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1796,"value":"continuousBandSize = 20","pinned":false,"hidden":false,"mode":"ojs"},{"id":1306,"value":"weeks = Math.round(width / (continuousBandSize * 2))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1747,"value":"common = ({\n // autosize: 'fit-x',\n width: width - 200,\n config: {\n axisY: { minExtent: 60, maxExtent: 60, title: null },\n bar: { continuousBandSize },\n },\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":218,"value":"x = ({\n field: 'week',\n type: 'temporal',\n timeUnit,\n axis: {\n grid: false\n },\n scale: {\n padding: 20,\n domain: d3.extent(interviews.rows, i => i.week).map(Date.parse),\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1926,"value":"nowWeek = [d3.timeWeek(new Date()), d3.timeWeek.ceil(new Date())]","pinned":false,"hidden":false,"mode":"ojs"},{"id":954,"value":"md`### Imports`","pinned":false,"hidden":false,"mode":"ojs"},{"id":68,"value":"d3 = require('d3')","pinned":false,"hidden":false,"mode":"ojs"},{"id":142,"value":"_ = require('lodash')","pinned":false,"hidden":false,"mode":"ojs"},{"id":2043,"value":"vegaEmbed = require('vega-embed@3')","pinned":false,"hidden":false,"mode":"ojs"},{"id":23,"value":"import { lever, table } from '@opendoor/lever'","pinned":false,"hidden":false,"mode":"ojs"},{"id":1894,"value":"import { checkbox, select, slider } from '@jashkenas/inputs'","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W31","stdlib":"1","titling":"auto"}