Eighty-Five Survey Responses and a Ten-Day Board Deadline
The district's 42-item staff climate survey got 85 responses out of 210 staff. The assistant superintendent wants a board presentation in 10 days that breaks results down by grade band (K-2, 3-5, 6-8, 9-12), by role (teacher versus paraprofessional), and by years of service. The survey was built in Google Forms and the response Sheet has 85 rows, 42 columns, inconsistent free-text role entries (Teacher, teacher, Tchr, Para), and one grade band with only 7 respondents. The previous year's deck had 14 static charts that nobody could update. The board has asked for no identifying information and the district lawyer has said small cells are a risk. The communications director wants a one-page summary and an appendix.
1. Design the data pipeline from Form to board deck so it can be updated next year.
Read the worked answer
Keep three layers: raw, clean, and presentation. Raw: the Form's response Sheet, protected and never edited; 85 rows, 42 columns. Clean: a separate tab with a mapping table for role (Teacher, teacher, Tchr to Teacher; Para to Paraprofessional) and XLOOKUP or VLOOKUP to normalize, plus a grade-band column from a roster join or the survey item. Add a pivot table for counts by grade band and role; pivot tables refresh but need a manual refresh before each board meeting. Presentation: if the deck must be a PDF board packet, build Sheets charts and paste them into Slides as linked charts, so next year's update is update data, click Update rather than 14 new static images. If the board will browse online, build a Looker Studio report connected to the clean tab; that is more interactive but adds a tool the communications director may not maintain. Given a 10-day deadline and a one-page request, choose linked charts and a pivot table, not a new BI tool. Name the tabs and add a README tab with refresh steps.
2. The lawyer is concerned about small cells. What do you suppress and how do you avoid back-solving?
Read the worked answer
Set a suppression rule before looking at the data: do not report a cell with fewer than 10 respondents, and say so in a footnote. The K-2 grade band has 7, so it should not be reported separately. Options: collapse K-2 with 3-5 into Elementary if the combined n is large enough, or report the grade-band breakdown only for bands that meet the threshold and mark K-2 as too few to report. Do not leave a blank; blanks invite guessing. Also suppress complementary cells: if a row total is known and one cell is suppressed, a reader can subtract to recover it, so suppress a second cell in the same row or report only the total. For role, Paraprofessional may also be small; check before publishing. For free-text comments, remove names, school names, and combinations like the only physics para in the building. The lawyer's concern is re-identification, not just cell size. Have the assistant superintendent sign off on the suppression table before the deck is built.
3. The communications director wants one page. What goes on it, and what belongs in the appendix?
Read the worked answer
One page, three claims, one caveat. Page one: response rate (85 of 210, about 40%) and who responded; two or three headline percentages with the n beside them; one disaggregation that changes a decision (for example, paraprofessionals report lower access to planning time than teachers); and one commitment with an owner and date. Put the caveat on the page, not in the appendix: 40% response and 7 K-2 respondents mean the board should not treat small differences as real. Appendix: full frequency tables, the exact question wording, the suppression note, and the method (Form, dates, reminders). Do not paste 42 charts; the board will not read them and the communications director will spend the week formatting. If a board member asks for a cut that was suppressed, offer the collapsed version and explain the threshold. Finally, archive the clean Sheet and the deck together so next year's team can rerun the same pipeline rather than rebuild it.