Post Survey Processing - Done

overview pulsechecks are recurring surveys distributed on a scheduled basis to collect feedback this document details the post processing system that runs after each survey response is submitted, ensuring that data is properly calculated, stored, and ready for dashboard visualization data flow and processing levels pulsecheck data is processed and stored at three distinct levels respondent level individual survey responses and metrics group level aggregated data for defined groups (e g , by company) instance level overall results for the entire survey period (e g , january 2025) each survey period (instance) maintains its own set of metrics, calculations, and historical data post processing workflow when a respondent completes a pulsecheck survey, the system performs a series of post processing operations across all three data levels 1\ respondent level processing question response storage store individual responses (positive, neutral, negative, n/a) assign numerical scores (100, 50, 0, 1 respectively) store comments and their sentiment analysis ai sentiment analysis analyze comments using ai to determine sentiment (0 100 scale) determine if the comment is positive or negative store justification for the sentiment score use sentiment scores to override default question scores when available pulse score calculation calculate overall pulse score using weighted formula 40% face ratings (positive/neutral/negative selections) 40% nps rating (0 10 scale, converted to 0 100) 20% comment sentiment (when available) record previous metrics store previous pulse score and nps rating for trend calculations 2\ group level processing if a pulsecheck is configured to aggregate by a specific group type (e g , company) aggregate response data calculate average scores for each question across group members count positive, neutral, and negative responses by question exclude n/a responses from all calculations calculate group metrics determine group pulse score (average of member pulse scores) calculate group nps score (% promoters % detractors within the group) store previous metrics for trend calculations 3\ instance level processing overall metrics calculate instance pulse score (average of all respondent pulse scores) determine instance nps score (% promoters % detractors) calculate response rate percentage and trend indicator question statistics for each question, count positive, neutral, and negative responses calculate average score per question determine trend indicators (up, down, no change) comment metrics count positive and negative comments track unread comments top 5 statistical categories most happy highest current pulse scores most improved largest positive pulse score change new advocates shifted from ≤8 to ≥9 nps rating most at risk lowest current pulse scores most declined largest negative pulse score change lost advocates shifted from ≥9 to ≤8 nps rating key calculations pulse score calculation the pulse score is calculated as a weighted combination of three components face rating component (40% weight by default) nps component (40% weight by default) comment sentiment component (20% weight by default) if no comments are provided, the weights are redistributed proportionally nps calculation net promoter score is calculated as the percentage of promoters minus the percentage of detractors trend calculation trend indicators (up, down, or no change) are determined by comparing current values against previous values group aggregation setup administrators can define custom group types (e g , company, department) individual respondents are associated with specific groups each pulsecheck can be configured to aggregate by one specific group type aggregated scores use arithmetic means of individual scores special handling n/a responses stored but excluded from all calculations empty comments skipped during sentiment analysis first time surveys trend calculations default to "no change" technical details and pseudocode tables structure pulsecheckinstance field type description id primary key name string e g , "january 2025" pulsecheckid foreign key references pulsecheck configuration startdate date enddate date isactive boolean pulse score float (0 100) previous pulse score float (0 100) nps score float ( 100 to 100) previous nps score float ( 100 to 100) promoters count integer passives count integer detractors count integer response rate float (percentage) response trend enum "up", "dn", "nc" positive comments count integer negative comments count integer aggregation group type id foreign key, nullable references group type for aggregation pulsecheckquestionstats field type description id primary key pulsecheckinstanceid foreign key references pulsecheckinstance questionid foreign key references question positive count integer neutral count integer negative count integer average score float (0 100) trend enum "up", "dn", "nc" respondentresult field type description id primary key pulsecheckinstanceid foreign key references pulsecheckinstance respondentid foreign key references respondent completed boolean completedat timestamp pulse score float (0 100) previous pulse score float (0 100) nps rating integer (0 10) previous nps rating integer (0 10) respondentquestionanswer field type description id primary key respondentresultid foreign key references respondentresult questionid foreign key references question answer enum "positive", "neutral", "negative", "na", null numerical score float (0 100, 1 for n/a, null) comment text, nullable comment sentiment float (0 100), nullable comment is positive boolean, nullable sentiment justification text, nullable ai's explanation for sentiment score comment read boolean, default false groupresult field type description id primary key pulsecheckinstanceid foreign key references pulsecheckinstance groupid foreign key references group grouptypeid foreign key references grouptype pulse score float (0 100) previous pulse score float (0 100) nps score float ( 100 to 100) previous nps score float ( 100 to 100) member count integer groupquestionstats field type description id primary key groupresultid foreign key references groupresult questionid foreign key references question positive count integer neutral count integer negative count integer average score float (0 100) topstatistics field type description id primary key pulsecheckinstanceid foreign key references pulsecheckinstance category enum "most happy", "most improved", "new advocates", "most at risk", "most declined", "lost advocates" entity type enum "respondent", "group" entity id foreign key references respondent or group current value float previous value float difference float rank integer (1 5) post processing workflow 1\ on survey completion when a respondent completes a pulsecheck survey, execute the following steps // pseudocode function processsurveycompletion(respondentid, pulsecheckinstanceid) // 1 process individual responses faceratings = \[] comments = \[] foreach question in survey answer = getanswerforquestion(respondentid, question id) store answer in respondentquestionanswer if answer has comment // ai sentiment analysis takes the comment, question text, and face rating // returns three values // 1 sentiment score (0 100) // 2 boolean indicating if comment is positive // 3 justification text explaining the analysis sentiment, ispositive, justification = performaisentimentanalysis( answer comment, getquestiontext(question id), answer === "positive" ? 100 answer === "neutral" ? 50 answer === "negative" ? 0 1 ) // store all sentiment analysis results store sentiment in respondentquestionanswer comment sentiment store ispositive in respondentquestionanswer comment is positive store justification in respondentquestionanswer sentiment justification comments append(answer comment) if answer is "positive" numerical score = 100 else if answer is "neutral" numerical score = 50 else if answer is "negative" numerical score = 0 else if answer is "na" numerical score = 1 // store 1 to preserve n/a selection in records if sentiment exists numerical score = sentiment update respondentquestionanswer numerical score // only include non n/a responses in pulse score calculation if answer is not "na" and not null faceratings append(numerical score) // 2 calculate pulse score npsrating = getnpsrating(respondentid) pulsescore = calculatepulsescore(faceratings, npsrating, comments) // 3 update respondent result previouspulsescore = getpreviouspulsescore(respondentid) previousnpsrating = getpreviousnpsrating(respondentid) update respondentresult set completed = true completedat = current timestamp pulse score = pulsescore previous pulse score = previouspulsescore nps rating = npsrating previous nps rating = previousnpsrating // 4 check for group aggregation grouptypeid = getpulsecheckaggregationgrouptype(pulsecheckinstanceid) if grouptypeid is not null groupid = getrespondentgroupid(respondentid, grouptypeid) if groupid is not null updategroupresults(groupid, grouptypeid, pulsecheckinstanceid) // 5 update pulsecheck instance stats updatepulsecheckinstancestats(pulsecheckinstanceid) // 6 calculate top statistics calculatetopstatistics(pulsecheckinstanceid) 2\ group results processing when updating group results // pseudocode function updategroupresults(groupid, grouptypeid, pulsecheckinstanceid) // get all completed respondent results for this group respondentresults = getcompletedrespondentsingroup(groupid, pulsecheckinstanceid) if respondentresults length == 0 return // calculate aggregate metrics sumpulsescore = 0 promoters = 0 passives = 0 detractors = 0 foreach respondent in respondentresults sumpulsescore += respondent pulse score if respondent nps rating >= 9 promoters++ else if respondent nps rating >= 7 passives++ else detractors++ avgpulsescore = sumpulsescore / respondentresults length npsscore = (promoters 100 / respondentresults length) (detractors 100 / respondentresults length) // get previous scores previousgroupresult = getpreviousgroupresult(groupid, pulsecheckinstanceid) previouspulsescore = previousgroupresult ? previousgroupresult pulse score 0 previousnpsscore = previousgroupresult ? previousgroupresult nps score 0 // create or update groupresult upsert groupresult set pulse score = avgpulsescore previous pulse score = previouspulsescore nps score = npsscore previous nps score = previousnpsscore member count = respondentresults length // process each question's stats for the group foreach question in getquestions(pulsecheckinstanceid) positivecount = 0 neutralcount = 0 negativecount = 0 sumscore = 0 validanswers = 0 foreach respondent in respondentresults answer = getrespondentquestionanswer(respondent id, question id) // explicitly omit n/a responses from all calculations if answer is not null and answer is not "na" if answer is "positive" positivecount++ else if answer is "neutral" neutralcount++ else if answer is "negative" negativecount++ sumscore += answer numerical score validanswers++ avgscore = validanswers > 0 ? sumscore / validanswers 0 upsert groupquestionstats set positive count = positivecount neutral count = neutralcount negative count = negativecount average score = avgscore 3\ instance stats processing update overall instance statistics // pseudocode function updatepulsecheckinstancestats(pulsecheckinstanceid) // get all completed respondent results completedresults = getcompletedrespondentresults(pulsecheckinstanceid) allinvitedrespondents = getallinvitedrespondents(pulsecheckinstanceid) // note throughout this function, n/a responses (numerical score = 1) are excluded from all calculations // calculate response rate responserate = (completedresults length / allinvitedrespondents length) 100 // get previous instance for trend comparison previousinstance = getpreviousinstance(pulsecheckinstanceid) previousresponserate = previousinstance ? previousinstance response rate 0 // calculate response trend if responserate > previousresponserate responsetrend = "up" else if responserate < previousresponserate responsetrend = "dn" else responsetrend = "nc" // calculate nps breakdown promoters = 0 passives = 0 detractors = 0 foreach result in completedresults if result nps rating >= 9 promoters++ else if result nps rating >= 7 passives++ else detractors++ // calculate nps npsscore = promoters > 0 || detractors > 0 ? ((promoters 100 / completedresults length) (detractors 100 / completedresults length)) 0 // calculate pulse sumpulse = 0 foreach result in completedresults sumpulse += result pulse score avgpulse = completedresults length > 0 ? sumpulse / completedresults length 0 // comment counts positivecomments = countpositivecomments(pulsecheckinstanceid) negativecomments = countnegativecomments(pulsecheckinstanceid) // previous scores previouspulse = previousinstance ? previousinstance pulse score 0 previousnps = previousinstance ? previousinstance nps score 0 // update instance stats update pulsecheckinstance set pulse score = avgpulse previous pulse score = previouspulse nps score = npsscore previous nps score = previousnps promoters count = promoters passives count = passives detractors count = detractors response rate = responserate response trend = responsetrend positive comments count = positivecomments negative comments count = negativecomments // process each question's stats foreach question in getquestions(pulsecheckinstanceid) positivecount = 0 neutralcount = 0 negativecount = 0 sumscore = 0 validanswers = 0 foreach result in completedresults answer = getrespondentquestionanswer(result id, question id) if answer is not null and answer is not "na" if answer is "positive" positivecount++ else if answer is "neutral" neutralcount++ else if answer is "negative" negativecount++ sumscore += answer numerical score validanswers++ avgscore = validanswers > 0 ? sumscore / validanswers 0 // get previous stats for trend previousstats = getpreviousquestionstats(question id, pulsecheckinstanceid) previousavg = previousstats ? previousstats average score 0 // calculate trend if avgscore > previousavg trend = "up" else if avgscore < previousavg trend = "dn" else trend = "nc" upsert pulsecheckquestionstats set positive count = positivecount neutral count = neutralcount negative count = negativecount average score = avgscore trend = trend 4\ top statistics calculation calculate the top 5 statistics for each category // pseudocode function calculatetopstatistics(pulsecheckinstanceid) // clear previous top statistics for this instance deletetopstatistics(pulsecheckinstanceid) // get aggregation type grouptypeid = getpulsecheckaggregationgrouptype(pulsecheckinstanceid) // prepare data array that will hold respondents and/or groups entities = \[] // get all completed respondent results completedresults = getcompletedrespondentresults(pulsecheckinstanceid) // add respondents to entities array foreach result in completedresults // if we're aggregating by group, only include respondents without a group if grouptypeid is null or getrespondentgroupid(result respondentid, grouptypeid) is null entities push({ type "respondent", id result respondentid, current pulse result pulse score, previous pulse result previous pulse score, current nps result nps rating, previous nps result previous nps rating }) // if aggregating by group, add groups to entities array if grouptypeid is not null groupresults = getgroupresults(pulsecheckinstanceid) foreach groupresult in groupresults entities push({ type "group", id groupresult groupid, current pulse groupresult pulse score, previous pulse groupresult previous pulse score, current nps groupresult nps score, previous nps groupresult previous nps score }) // most happy (highest current pulse score) mosthappy = entities sort((a, b) => b current pulse a current pulse) slice(0, 5) for i = 0; i < mosthappy length; i++ insert into topstatistics category = "most happy" entity type = mosthappy\[i] type entity id = mosthappy\[i] id current value = mosthappy\[i] current pulse previous value = mosthappy\[i] previous pulse difference = mosthappy\[i] current pulse mosthappy\[i] previous pulse rank = i + 1 // most improved (largest positive pulse difference) mostimproved = entities filter(e => e previous pulse > 0) // ensure there's a previous score to compare with map(e => ({ e, diff e current pulse e previous pulse})) sort((a, b) => b diff a diff) slice(0, 5) for i = 0; i < mostimproved length; i++ insert into topstatistics category = "most improved" entity type = mostimproved\[i] type entity id = mostimproved\[i] id current value = mostimproved\[i] current pulse previous value = mostimproved\[i] previous pulse difference = mostimproved\[i] diff rank = i + 1 // new advocates (was ≤8 nps, now ≥9, sorted by pulse) newadvocates = entities filter(e => (e type == "respondent" && e previous nps <= 8 && e current nps >= 9) || (e type == "group" && e previous nps <= 0 && e current nps > 0)) sort((a, b) => b current pulse a current pulse) slice(0, 5) for i = 0; i < newadvocates length; i++ insert into topstatistics category = "new advocates" entity type = newadvocates\[i] type entity id = newadvocates\[i] id current value = newadvocates\[i] current pulse previous value = newadvocates\[i] previous pulse difference = newadvocates\[i] current nps newadvocates\[i] previous nps rank = i + 1 // most at risk (lowest current pulse score) mostatrisk = entities sort((a, b) => a current pulse b current pulse) slice(0, 5) for i = 0; i < mostatrisk length; i++ insert into topstatistics category = "most at risk" entity type = mostatrisk\[i] type entity id = mostatrisk\[i] id current value = mostatrisk\[i] current pulse previous value = mostatrisk\[i] previous pulse difference = mostatrisk\[i] current pulse mostatrisk\[i] previous pulse rank = i + 1 // most declined (largest negative pulse difference) mostdeclined = entities filter(e => e previous pulse > 0) // ensure there's a previous score to compare with map(e => ({ e, diff e previous pulse e current pulse})) filter(e => e diff > 0) // only include those that actually declined sort((a, b) => b diff a diff) slice(0, 5) for i = 0; i < mostdeclined length; i++ insert into topstatistics category = "most declined" entity type = mostdeclined\[i] type entity id = mostdeclined\[i] id current value = mostdeclined\[i] current pulse previous value = mostdeclined\[i] previous pulse difference = mostdeclined\[i] diff // store as negative value rank = i + 1 // lost advocates (was ≥9 nps, now ≤8, sorted by lowest pulse) lostadvocates = entities filter(e => (e type == "respondent" && e previous nps >= 9 && e current nps <= 8) || (e type == "group" && e previous nps > 0 && e current nps <= 0)) sort((a, b) => a current pulse b current pulse) slice(0, 5) for i = 0; i < lostadvocates length; i++ insert into topstatistics category = "lost advocates" entity type = lostadvocates\[i] type entity id = lostadvocates\[i] id current value = lostadvocates\[i] current pulse previous value = lostadvocates\[i] previous pulse difference = lostadvocates\[i] current nps lostadvocates\[i] previous nps rank = i + 1 key calculations pulse score calculation // pseudocode function calculatepulsescore(faceratings, npsscore, comments) { // 1 calculate face score let facesum = 0; let facecount = 0; for (const rating of faceratings) { // skip null and n/a ( 1) ratings if (rating !== null && rating !== 1) { facesum += rating; facecount++; } } const facescore = facecount > 0 ? facesum / facecount 0; // 2 calculate nps score let npsscore100; switch (npsscore) { case 0 case 1 case 2 case 3 case 4 case 5 case 6 npsscore100 = 0; break; case 7 case 8 npsscore100 = 50; break; case 9 case 10 npsscore100 = 100; break; default npsscore100 = 0; // handle invalid input } // 3 calculate comment sentiment score let commentsentimentscore = 0; let commentcount = 0; if (comments length > 0) { for (const comment of comments) { const sentiment = analyzesentiment(comment); // external function if (sentiment !== null) { commentsentimentscore += sentiment; commentcount++; } } commentsentimentscore = commentcount > 0 ? commentsentimentscore / commentcount 0; } // 4 calculate overall pulse score (with re weighting) const wf = 0 4; const wn = 0 4; let wc = 0 2; let newwf, newwn; if (commentcount === 0) { wc = 0; newwf = wf / (wf + wn); newwn = wn / (wf + wn); } else { newwf = wf; newwn = wn; } const pulsescore = (newwf facescore) + (newwn npsscore100) + (wc commentsentimentscore); return pulsescore; } nps calculation // pseudocode function calculatenps(promoterscount, passivescount, detractorscount) { const totalrespondents = promoterscount + passivescount + detractorscount; if (totalrespondents === 0) { return 0; } const promoterspercentage = (promoterscount / totalrespondents) 100; const detractorspercentage = (detractorscount / totalrespondents) 100; return promoterspercentage detractorspercentage; } indexes and optimization to ensure optimal performance, create the following indexes respondentresult table pulsecheckinstanceid, respondentid (composite) completed (for filtering completed responses) respondentquestionanswer table respondentresultid, questionid (composite) groupresult table pulsecheckinstanceid, groupid (composite) grouptypeid (for filtering by group type) pulsecheckquestionstats table pulsecheckinstanceid, questionid (composite) topstatistics table pulsecheckinstanceid, category (composite) transaction management the post processing operations should be wrapped in database transactions to ensure data consistency when processing an individual survey completion, wrap all database operations in a transaction when updating group results, use a transaction when updating instance statistics, use a transaction when calculating top statistics, use a transaction this ensures that if any part of the process fails, the entire operation is rolled back, maintaining data integrity ai sentiment analysis integration the sentiment analysis function should accept a comment string, question text, and face rating as input process using the prompt template defined below return three distinct values a sentiment score between 0 100 (or null if sentiment cannot be determined) a boolean flag indicating if the comment is positive (true) or negative (false) a justification paragraph explaining the analysis reasoning include error handling for api failures or timeout situations cache results to avoid redundant processing of identical comments sentiment analysis prompt you are tasked with analyzing customer sentiment of a comment associated with a survey question your goal is to provide a sentiment score between 0 and 100, where 0 represents extremely negative sentiment, 50 represents neutral sentiment, and 100 represents extremely positive sentiment here is the customer comment you need to analyze \<survey question> \<question>{{question text}}\</question> \<face rating>{{face rating}}\</face rating> \<comment> {{comment}} \</comment> \</survey question> carefully read and consider the comment above pay attention to the words used, the overall tone, and any specific positive or negative expressions to determine the sentiment score, follow these guidelines 1\ identify key positive and negative words or phrases 2\ consider the overall context and tone of the comment 3\ evaluate any specific complaints or praises 4\ assess the intensity of the sentiment expressed 5\ take note of the face rating, but prioritize the actual content of the comment before providing the final score, explain your reasoning for the sentiment analysis consider the following questions in your justification \ what specific words or phrases influenced your analysis? \ is the overall tone primarily positive, negative, or neutral? \ are there any mixed sentiments present? \ how intense is the sentiment expressed? provide your justification and final score in the following format \<justification> \[write your reasoning here, explaining how you arrived at the sentiment score] \</justification> \<score> \[provide only the numeric score between 0 and 100, or null if sentiment cannot be determined] \</score> \<is positive> \[provide "true" if the sentiment is generally positive (score > 50), "false" if the sentiment is generally negative (score <= 50), or "null" if sentiment cannot be determined] \</is positive> remember, if the sentiment cannot be determined from the given comment, return null as the score and is positive value expected return format { score 78, // numerical score 0 100 ispositive true, // boolean indicating positive/negative sentiment justification "the comment uses positive language like 'excellent' and expresses satisfaction with the response time there are no negative elements in the feedback " } sample implementation // pseudocode async function performaisentimentanalysis(comment, questiontext, facerating) { if (!comment || comment trim() === '') { return { score null, ispositive null, justification null }; } try { // prepare the prompt with the actual values const prompt = sentimentanalysisprompt replace('{{question text}}', questiontext || 'not provided') replace('{{face rating}}', getfaceratingtext(facerating) || 'not provided') replace('{{comment}}', comment); // call the ai service with the prepared prompt const response = await aiservice analyze(prompt); // extract the relevant parts from the response const justification = extractbetweentags(response, 'justification'); const scoretext = extractbetweentags(response, 'score'); const ispositivetext = extractbetweentags(response, 'is positive'); // parse the score and ispositive values let score = null; if (scoretext && scoretext tolowercase() !== 'null') { score = parsefloat(scoretext); if (isnan(score)) score = null; } let ispositive = null; if (ispositivetext && ispositivetext tolowercase() !== 'null') { ispositive = ispositivetext tolowercase() === 'true'; } return { score, ispositive, justification }; } catch (error) { console error('sentiment analysis failed ', error); return { score null, ispositive null, justification null }; } } // helper function to extract content between xml tags function extractbetweentags(text, tagname) { const opentag = `<${tagname}>`; const closetag = `\</${tagname}>`; const startindex = text indexof(opentag) + opentag length; const endindex = text indexof(closetag); if (startindex === 1 || endindex === 1 || startindex >= endindex) { return null; } return text substring(startindex, endindex) trim(); } // helper function to convert numerical face rating to descriptive text function getfaceratingtext(rating) { if (rating === 100) return "positive (happy face)"; if (rating === 50) return "neutral (meh face)"; if (rating === 0) return "negative (unhappy face)"; if (rating === 1) return "n/a"; return null; } notes for implementation the system should handle respondents who don't complete all questions n/a responses when a respondent selects "n/a" for a question the response must be stored with answer = "na" and numerical score = 1 this preserves the n/a response for displaying individual survey results however, n/a responses are completely excluded from all statistical calculations they do not contribute to the face rating component of pulse score they are excluded from question statistics (positive/neutral/negative counts) they are excluded from averages and group aggregations dashboard and reporting views should indicate when questions have n/a responses for surveys without previous instances, trend calculations should default to "nc" (no change) empty comments should be skipped during sentiment analysis when recalculating instance or group statistics, consider implementing a queue system for large datasets add logging at key points to facilitate debugging and monitoring implement validation to prevent duplicate processing of the same survey submission