Lab 05 · NL Analytics With Statistical Caution

Nisha is a PM at a Hyderabad edtech company, responsible for learner retention on PrepLoop, a fictional exam-prep product. Her company's analytics copilot answers questions in seconds — and she has learned that its answers are right most of the time, which is the most dangerous accuracy level there is: high enough to build trust, low enough to poison a business review. Today's exercise: a two-week event extract small enough to check every single answer by hand, so the copilot's failure patterns become something you can recognize forever.

You will need

• A spreadsheetGoogle Sheets or Excel — for the raw data and your recomputations.
• A chat AI (optional live path) — you can also paste the CSV and ask your own questions to compare behaviors.
• Timeabout 75 minutes.
• No real datathe dataset below is synthetic and obviously fictional.

The dataset

Thirty-six events, eight users, July 1–14. Note the acct column: internal marks PrepLoop test accounts.

date,user,acct,event
2026-07-01,u01,learner,lesson_view
2026-07-01,u01,learner,quiz_start
2026-07-01,u01,learner,quiz_complete
2026-07-01,u02,learner,lesson_view
2026-07-02,u02,learner,quiz_start
2026-07-02,u03,learner,lesson_view
2026-07-02,u07,internal,quiz_start
2026-07-02,u07,internal,quiz_complete
2026-07-03,u03,learner,quiz_start
2026-07-03,u03,learner,quiz_complete
2026-07-03,u04,learner,lesson_view
2026-07-04,u01,learner,lesson_view
2026-07-04,u04,learner,quiz_start
2026-07-04,u07,internal,quiz_start
2026-07-04,u07,internal,quiz_complete
2026-07-05,u05,learner,lesson_view
2026-07-05,u05,learner,quiz_start
2026-07-05,u05,learner,quiz_complete
2026-07-06,u02,learner,lesson_view
2026-07-07,u06,learner,lesson_view
2026-07-08,u06,learner,quiz_start
2026-07-08,u06,learner,quiz_complete
2026-07-08,u01,learner,subscribe
2026-07-09,u04,learner,lesson_view
2026-07-09,u07,internal,quiz_start
2026-07-10,u03,learner,lesson_view
2026-07-10,u05,learner,subscribe
2026-07-11,u02,learner,quiz_start
2026-07-11,u02,learner,quiz_complete
2026-07-12,u08,learner,lesson_view
2026-07-12,u08,learner,quiz_start
2026-07-13,u08,learner,quiz_complete
2026-07-13,u06,learner,lesson_view
2026-07-14,u04,learner,quiz_start
2026-07-14,u04,learner,quiz_complete
2026-07-14,u03,learner,lesson_view

The copilot transcript

Ten questions Nisha asked, with the copilot's answers. Two are wrong.

Q1  How many unique learners were active?           -> 7 learners
Q2  Overall quiz completion rate for learners?      -> 7 of 9 starts, 78%
Q3  How many learners completed a quiz Jul 1-7?     -> 4 users
Q4  How many subscriptions in the period?           -> 2 (u01, u05)
Q5  Did subscribers complete a quiz before
    subscribing?                                    -> Yes, both did
Q6  Quiz completions in the last 7 days (Jul 8-14)? -> 3 completions
Q7  Most common learner event?                      -> lesson_view (13)
Q8  Quiz starts abandoned (no same-day complete)?   -> 3 (one finished
                                                       next day)
Q9  Average events per active learner?              -> ~4.4
Q10 Highest-activity day for learners?              -> Jul 1 (4 events)

Steps

1. 1.

   Load the data and build your checking rig. Paste the CSV into a sheet, split into columns, and add a pivot table or COUNTIFS scratch area. Five minutes of setup makes every later check a ten-second operation instead of a squint. Confirm you see 36 rows: 31 learner events, 5 internal.

2. 2.

   Set your prior: predict where NL analytics breaks. Before checking anything, write down the three ways you expect a natural-language query engine to go wrong on this data. (Classics: forgetting to exclude internal accounts, ambiguous date windows, counting events when the question asks about users.) You are calibrating your suspicion before the audit.

3. 3.

   Verify Q1, Q2, Q4, Q5 — the identity and funnel numbers. Recompute each from the sheet. For Q2, list the nine learner quiz_starts and seven completes yourself. For Q5, check event ordering per user, not just event presence — "before subscribing" is a sequence claim, and sequence claims are where copilots quietly substitute co-occurrence. Record verdicts in a check table: Q | copilot answer | my answer | verdict.

4. 4.

   Verify Q3 with the filter question in mind. Count learners — acct = learner — with a quiz_complete between July 1 and 7 inclusive. Compare with the copilot. If the numbers differ, identify exactly which user the copilot should not have counted, and name the missing filter. This is the single most common real-world NL-analytics failure: the question said "learners," the query counted "users."

5. 5.

   Verify Q6 with the window question in mind. "Last 7 days (Jul 8–14)" defines an inclusive window. List every learner quiz_complete with a date in that window, then reverse-engineer what window the copilot must have used to get its number. Off-by-one windows — trailing-7-days computed as day 7 through day 13, or "week" resolved to Monday–Sunday vs. rolling — survive every skim because the answer is plausibly sized.

6. 6.

   Verify Q7–Q10. These require care rather than suspicion: Q8's "abandoned" definition (no same-day complete) is stated in the answer — check the copilot applied its own definition consistently, including the u08 next-day nuance. Q9 is learner events divided by active learners; confirm both numerator and denominator exclude internal accounts.

7. 7.

   Write the correction memo. For each wrong answer: the copilot's number, the correct number, the precise mechanism of the error (missing filter / wrong window), and the one-line question you could have asked the copilot to expose it — e.g., "Show me the filter and exact date range you used, and list the user IDs behind that count." That question is the take-home habit.

8. 8.

   Optional live path — replay against your own AI. Paste the CSV into your chat AI and ask Q3 and Q6 verbatim. Compare its answers and its stated filters/windows to the transcript's. Different tools fail differently; what stays constant is that you cannot tell right from wrong answers by fluency, only by inspection.

Verify your work

A complete lab has all of the following:

• •
  A check table covering all ten questions with your independently computed number for each.
• •
  Exactly two verdicts of WRONG, each with the correct number and the named mechanism (which filter, which window).
• •
  The user ID the bad filter wrongly included, identified by name.
• •
  The reverse-engineered window the copilot used on Q6.
• •
  A correction memo with the "show me your filter and window" inspection question written out.