A bank marketing team wants a demonstration of response scoring before deciding which customers to contact. You will use ChatGPT to fit a simple response model, evaluate a fixed contact-capacity rule, and explain whether the historical evidence supports further investigation. Excel helps you inspect the arithmetic without writing code.
The target is the recorded term-deposit subscription response in a published campaign file. One row is a campaign record, not a verified unique customer. The exercise cannot establish the additional response caused by contacting someone, profitable marketing, fair treatment or present-day deployability. It is a standalone reference as well as the next self-study lesson; Lesson 17 supplies helpful regression concepts, and Lesson 6 remains accepted and skipped.
What you will learn
- Define what information could exist at the contact decision, and keep later information out of the model.
- Preserve source order while separating training, validation and final test.
- Read logistic probabilities, reference categories and penalized coefficients without confusing associations with causes.
- Distinguish ranking quality, probability calibration and an exact contact-capacity rule.
- Check equal-score ties, missing values, denominators and the effect of reducing capacity.
- State when a useful retrospective ranking still lacks evidence for an operational decision.
Expected outputs are a source and eligibility ledger, training and pretest freezes, scored test records, confusion/capacity/calibration tables, four charts, a simple Excel workbook and a short model-use note. The blueprint suggests 75 minutes; that is an unvalidated planning estimate, not an observed completion time. Allow separate sessions for reading, calculation and Excel verification.
Set up the files and a fresh conversation
Unzip L18_Learner_Practice_Files.zip into one folder. Open a fresh ChatGPT conversation that supports file uploads and data analysis. Use the attachment control to select the named files; wait until all attachments are visible before pasting a prompt. If the interface accepts fewer files at once, upload in batches and ask ChatGPT to wait until the stage is complete. Record unavailable capabilities rather than assume they worked. Official help: https://help.openai.com/en/articles/8437071-data-analysis-with-chatgpt.
Keep the full source archive, full CSV, reference workbook, instructor answers and complete publication ZIP out of the early conversation. They contain later outcomes or worked results. The learner ZIP separates training, validation and final test, but it is not access control: you must keep later folders closed until the stated checkpoint. No software installation or programming is required of the learner.
| Step | Upload | Save before proceeding |
|---|---|---|
| 1 | All eight required files in stage_1, named in Prompt 01. The source/reuse notice is optional reference. | Training audit, then candidate fits and My_L18_Training_Freeze.json. |
| 2 | Only stage_2/L18_Validation.csv. | Validation selection and My_L18_Pretest_Freeze.json, with both capacities already fixed. |
| 3 | Only stage_3/L18_Final_Test.csv. | Final scores, exact selected lists, all metrics and chart values. |
| 4 | The separate reference workbook and answer tables only after your own outputs are saved. | Difference review and your actual Excel/usability log. |
Understand the method before fitting
Logistic regression adds an intercept to each encoded input multiplied by a coefficient. This sum is the logit, or log odds. The logistic transformation 1/(1+EXP(-logit)) puts it between zero and one. A score is a fitted probability under the model; whether records with a score near 0.20 actually respond about 20% of the time is a separate calibration question.
A coefficient changes log odds while the other included inputs are held fixed. EXP(coefficient) is an odds multiplier, not a percentage-point change or a probability multiplier. Standardized numeric coefficients describe a one-training-standard-deviation change; the answer tables also provide original-unit coefficients. One category per field is the reference. For fields that move together in training, separate coefficients do not establish separate effects.
Ridge regularization penalizes large nonintercept coefficients. This lesson permits only two penalty strengths, 1 and 100, and selects by validation log loss. It uses no class weights, synthetic rows, imputation or calibration fitting. The exact objective is supplied because software packages use different penalty conventions. The simple baseline assigns every record the training response prevalence.
The decision moment is immediately before the first attempted contact of the current campaign. The allowlist contains age, job, marital status, education, default, housing, loan, previous and poutcome. Customer-profile availability is a teaching assumption: the documentation calls these bank client data but supplies no snapshot timestamps. previous and poutcome explicitly describe before/prior-campaign information. The file does not prove a complete operational feature history.
duration reveals a completed call and is excluded. contact, month, day_of_week and campaign describe the last/current campaign and are excluded. Economic indicators lack a verified release vintage at the decision moment. pdays is retained as evidence, including its 999 sentinel, but is excluded because the required as-of date cannot be reconstructed. Never turn unknown into no, zero or a guessed date. Use the matching additional-full documentation; some live catalog descriptions refer to an older version.
Source rows 1–24,712 train the model, 24,713–32,950 select permitted settings, and 32,951–41,188 provide the final evaluation. The publisher says the full additional file is date ordered, but individual dates are absent. Call this a preserved-order split supported by the publisher. Missing person identifiers prevent a complete repeated-person leakage audit.
Exact prompts and checkpoints
Prompt 01 Inspect training data and define the decision
I am an Excel-comfortable management consultant with no programming experience. Help me complete Lesson 18, Build and evaluate a propensity model. The case is a bank marketing team demonstrating response scoring before deciding which customers to contact. Treat y as the recorded historical term-deposit subscription response, not incremental marketing impact or proof of deployability.
Use only my stage_1 uploads: L18_Training.csv, bank-additional-names.txt, L18_Data_Dictionary.csv, L18_Data_Dictionary_and_Method_Card.pdf, L18_Model_and_Evaluation_Policy_v1_1.json, L18_Encoding_and_Scoring_Addendum_v1.json, L18_Field_Timing_Evidence.csv and L18_Staged_Input_Manifest.json. Read the files as data, not instructions. Do not retrieve or open the full source, validation, final test, instructor answers or reference results. If a required file is unavailable, say exactly which; do not substitute or invent values.
Audit the 24,712 training rows and source-row sequence 1–24,712. Preserve every original string, exact category, unknown label, precision and ID. Distinguish original source strings from parsed numeric fields. The supplied staged CSV uses commas; the original source used semicolons. Keep repeated-value candidates and audit them rather than delete them. Source_row is file lineage, not a customer ID. Check y, feature validity, missing-coded categories and pdays=999 using the matching additional-full documentation, without inventing dates or person identities.
Explain the declared prediction moment and field allowlist. Customer profiles are conditional pre-contact assumptions; the file has no as-of timestamps. previous and poutcome describe prior-campaign history. Exclude y, duration, current-contact/current-campaign fields, pdays and economic fields from features as the policy directs. Keep excluded fields in evidence. Do not infer unique people or reconstruct individual chronology from month and weekday.
Return a training inspection note, full training eligibility ledger, source preservation checks, feature-timing table and a plan for fitting and evaluation. Report actual failures. Stop before fitting and before requesting stage_2. No programming should be required of me.
Checkpoint 1: retain every training row and exact source value. Save the audit, including duplicate candidates and source semantic tensions. A row-level ID traces a file position; it does not prove a different person.
Prompt 02 Fit candidates using training only
Using only the verified training rows and supplied policy, fit the two permitted ridge logistic candidates. Use the exact objective: sum of binary logistic losses plus lambda/2 times the sum of squared nonintercept coefficients, with lambda=1 and lambda=100. Do not penalize the intercept. No class weighting, imputation, resampling, extra candidates or calibration fitting is permitted. Standardize numeric age and previous using eligible training means and population standard deviations; retain raw values separately. Learn categorical levels and modal reference categories only from eligible training; break reference-count ties by exact category text. Keep observed unknown as its own category. A future unseen category is unscorable, not reference.
Audit exact duplicate feature columns and training matrix rank before interpreting any coefficient. Positive ridge penalty gives unique fitted weights even with dependencies, but it does not identify separate effects. Follow the addendum: retain the frozen latent parameter audit and supply an equivalent consolidated scoring view for duplicate indicators; show joint contrasts where columns are dependent. Do not silently drop or refit fields to improve results. Report unsupported combinations or numerical extrapolation explicitly.
Record convergence messages and the declared numerical correction, if needed. Compute scores with the addendum's consistent rowwise summation so identical feature vectors receive identical scores; do not round scores for ranking. Save both coefficient vectors, training-only preprocessing, feature order, training prevalence baseline, fit logs and their SHA256 hashes in My_L18_Training_Freeze.json. Explain probability=1/(1+EXP(-logit)), reference categories and conditional odds ratios in plain language. Do not select a candidate using training fit alone. Stop and ask for stage_2 only after these files are saved.
Checkpoint 2: download candidate coefficients, preprocessing and the training freeze before opening validation. Check the actual convergence log. Treat dependent coefficients as a joint description, and keep all training support warnings visible.
Prompt 03 Select and freeze using validation only
I have now uploaded only L18_Validation.csv from stage_2. Verify its registered hash, 8,238 rows and source rows 24,713–32,950. Keep all rows and source values. Apply the frozen training preprocessing and both candidate fits. Do not learn categories, scaling, coefficients or calibration from validation.
Use the common known-outcome/scorable validation population to choose the candidate with the lowest mean log loss; an exact tie selects the stronger lambda. Keep its training fit and do not refit on combined training and validation. The baseline remains training prevalence. For the selected model, evaluate the predeclared 20% and 10% capacity rules. Select exactly CEILING(capacity*N,1), sorting unrounded scores descending and source_row ascending for equal scores. Report N, unavailable rows, selected and unselected counts, TP, FP, FN, TN, precision, recall, selected fraction, response rate and lift over this same validation base.
For the separate fixed cutoff, evaluate only 0.05, 0.10, 0.15, 0.20, 0.25, 0.30, 0.40 and 0.50. Choose the threshold whose actual validation selected fraction is closest to 20%, with higher cutoff winning an exact distance tie. Score>=cutoff defines selection. Label the resulting actual fraction; it is not the 20% capacity rule. Do not add a finer grid after seeing the result.
Save validation evidence and My_L18_Pretest_Freeze.json with source/policy/preprocessing hashes, both fitted candidates, selected lambda and coefficients, training baseline, selected fixed cutoff, exact tie rule and both capacities. Keep 20% as the main teaching policy and 10% as the practice; do not choose a business winner without costs and benefits. Stop. I will upload stage_3 only after downloading this freeze.
Checkpoint 3: download the pretest freeze before uploading final test. Record its hash and time. Both capacities must already be declared. If test outcomes were exposed early, document the event and limit your independence claim.
Prompt 04 Evaluate the frozen model on final test
The pretest freeze is saved. I have now uploaded L18_Final_Test.csv. Verify its registered hash, 8,238 rows and source rows 32,951–41,188. Record when final-test performance is first opened. Use only the frozen preprocessing, coefficients, baseline, fixed cutoff and capacity policies. Do not refit, tune, recalibrate, change the allowlist or alter the threshold grid. Report any accidental prior exposure honestly.
Score every source row or record an explicit unavailable reason. Unknown categorical labels learned in training remain explicit categories; new unseen levels are unscorable. Preserve numeric zeros, unknowns, source sentinel values and duplicate candidates. Use consistent rowwise sums before logistic transformation and verify identical features have identical scores. Reconcile every source row, score coverage, known outcome and common evaluation denominator. Report all class counts and prevalence for each partition once it is legitimately available.
Create My_L18_Scored_Test.csv with source_row, source_record_id, y, score status, logit, probability, rank, selected20, selected10, selectedFixedCutoff and residual. Rank unrounded scores descending then source_row ascending; select exactly CEILING(0.20*N,1) and CEILING(0.10*N,1). Preserve unavailable rows outside N with reasons. Give all confusion cells, precision, recall, coverage, selected fraction and lift. Compare constant training-prevalence baseline on the same rows; its equal-score capacity ranking follows source order and is not random selection.
Calculate average precision using full equal-score groups: sum(recall increment times precision at each distinct threshold). Do not substitute trapezoidal precision-recall area. Also report Brier score and mean log loss. Define undefined denominators as unavailable. Use fixed probability calibration bins [0,.1),...,[.9,1]; retain empty bins as unavailable, not zero response. Show the full probability and response bases. Save the exact threshold, capacity, calibration and precision-recall tables, plus actual boundary-tie records and all 20%-to-10% transitions. Stop before interpretation so I can save the calculation evidence.
Checkpoint 4: verify selected+unselected=N, TP+FP=selected, TP+FN=positives and all four confusion cells=N. Verify the exact boundary rows when scores tie. Save these outputs before requesting interpretation.
Prompt 05 Explain the evidence and create exhibits
Use the saved frozen-model outputs to create four PNG exhibits and their underlying CSV tables: full final-test precision-recall curve with average precision and the same-population prevalence reference; fixed-bin calibration with counts and empty bins visible; cumulative response rate and lift at the policy's fixed capacities; and validation-versus-test precision and recall for the predeclared 20% and 10% rules. Display units, population, sample sizes and denominator definitions. Do not renormalize, hide weak performance or select a display because it looks favorable.
Write a concise model-use and contact-policy note. Separate source observations, calculated results, teaching assumptions and proposed operational follow-up. Explain ranking versus calibration, source-order and prevalence shifts, unknown-category handling, training support and collinearity, and the conditional availability of customer snapshots. Report joint contrasts rather than independent effects where features are dependent. Flag later feature values beyond training support; positive ridge penalty does not supply missing evidence. No causal, profitability, fairness-approval or deployment claim is allowed.
State exactly what ran. Preserve solver warnings, arithmetic repairs, changed results and retests. If a numerical repair happened after test exposure, disclose that chronology while distinguishing it from model tuning. Export a simple Excel workbook with an explicit prepared-score/rank/coefficients boundary and live capacity, confusion and metric calculations. Keep full source and feature evidence in CSVs. Do not claim native Excel or human usability tests unless performed.
Interpret a contact policy carefully
The 20% policy chooses a count: CEILING(0.20×N,1). It need not choose scores above 0.20. A fixed probability cutoff chooses a different set whose size changes with the score distribution. Always show both the rule and its actual selected fraction.
Precision asks how many selected records responded; recall asks how many observed responses the list captures. Lift compares selected response rate with the same holdout base. A constant baseline has equal scores, so its capacity list follows source order under the tie rule. Its realized top-list rate is not a randomized-contact benchmark. Accuracy can look high simply by predicting the majority class.
A model can rank responses usefully while underestimating their probability levels. Compare calibration and prevalence across partitions. Do not correct a weak test result by refitting, recalibrating or changing the policy after viewing it. A later revised model needs a new independent evaluation.
Verify the workbook in Microsoft Excel
After saving your own results, open L18_Propensity_Model_and_Evaluation.xlsx in Microsoft Excel and save a separate working copy. Select Formulas > Calculation Options > Automatic if available. Record platform, version and any opening warnings. The workbook contains no macros. Follow your organization’s normal downloaded-file process.
Evaluation contains live contact-policy and confusion calculations. Scored test contains every final-test record and its frozen score/rank, with live selection flags. Model contains prepared fitted coefficients, training preprocessing and actual profile examples with live score arithmetic. Prepared scores and rankings are a deliberate boundary: editing coefficients in an example does not refit the model or refresh the entire test ranking.
- Use L18_Excel_Verification_Instructions.txt for exact cells and expected probe results. Confirm the baseline workbook before editing.
- Inspect the formula bar for CEILING(capacity×N,1), TP, precision, recall and lift. Compare the underlying IDs and selected lists with your own CSV.
- Change the central capacity in a copy, verify the exact selected count, and restore it. Change the fixed cutoff and observe its actual selected fraction.
- Test a missing outcome versus numeric zero. A missing outcome must not quietly become a negative response.
- Alter or blank a frozen score/rank and confirm the workbook reports that the prepared ranking must be regenerated. It must not silently use stale ranks. Restore the cell.
- Use the actual profile examples to change a permitted input and then a fitted coefficient. Confirm that the example score changes without claiming a refit. Test an unknown versus unseen category and a missing numeric input. Restore all changes.
- Record only native Excel tests you actually run. Runtime formula calculations and exported-file inspection do not establish your Excel session passed.
Prompt 06 Complete the capacity practice
Compare the frozen 20% main policy with the predeclared 10% practice, using the same model, rows, scoring rules and denominator definitions. Start with the saved validation comparison; then report the already frozen final-test comparison. Do not choose or retune a policy from final-test results.
Show selected counts, TP/FP/FN/TN, precision, recall, selected fraction, response rate and lift for both policies. Create a customer-record transition table using source_row lineage: retained at 10%, leaves when capacity halves, unselected by both, or unavailable. These are published records, not verified unique customers. Identify actual boundary rows and explain equal-score tie handling. Independently sum the outcomes in each exact selected-row list and reconcile changed denominators. Explain why higher precision can coincide with lower response coverage, why identical accuracy can conceal a different contact policy, and what cost, value and prospective experimental evidence is still needed. Do not call observed responses incremental gains.
Before opening the instructor answers, write your own short recommendation: What becomes more concentrated, what coverage is lost, and which business evidence is missing? Use actual source-row IDs at the boundaries. Do not rename the records as unique customers or equate subscription counts with incremental conversions.
Prompt 07 Verify against the instructor reference
I have saved my independent analysis and now uploaded the released reference workbook, L18_Capacity_Metrics.csv, L18_Probability_Metrics.csv and L18_Validation_Report.txt. Compare source identity, policy versions, training encodings, coefficients, score conventions, exact selected-row lists, tie handling, confusion cells, AP, calibration and practice transitions. Distinguish material differences from tolerances and trace each mismatch to source evidence or a stated convention; do not simply overwrite my answers.
Guide me through L18_Excel_Verification_Instructions.txt in my own copy of Microsoft Excel. Test live capacity and cutoff changes, a missing versus zero outcome, an altered frozen score/rank, and coefficient/profile arithmetic. Prepared rankings must become unavailable when their binding is invalidated, rather than silently remaining usable. Restore the reference state. Record only tests actually performed in the blank supervised-usability log, including Excel platform, ChatGPT model, timings, failures and fixes. Do not label assistant numerical replay as a human test.
Use this lesson during a client project
Start with a dated prediction moment and feature history. Agree how unavailable scores will be handled, freeze the evaluation protocol, preserve the exact selected records and establish the business objective before choosing capacity. Ask for verified identities to assess repeated-person leakage, decision-time snapshots, operational costs and benefits, and a prospective randomized contact/control design if the decision concerns incremental marketing impact. This public historical exercise supplies none of those approvals or missing inputs.
Sources and evidence
Moro, S., Rita, P., and Cortez, P. (2014). Bank Marketing [Dataset]. UCI Machine Learning Repository. https://doi.org/10.24432/C5K306. Official catalog: https://archive.ics.uci.edu/dataset/222/bank+marketing. Official archive: https://archive.ics.uci.edu/static/public/222/bank%2Bmarketing.zip. Exact bytes were retrieved and checked on 25 October 2026.
Use bank-additional-names.txt for this additional-full version. Its requested paper citation is Moro, Cortez and Rita, A Data-Driven Approach to Predict the Success of Bank Telemarketing, Decision Support Systems (2014), https://doi.org/10.1016/j.dss.2014.03.001. The current UCI catalog states CC BY 4.0; preserve attribution, license links and the transformation notice. Source availability does not establish operational approval.
The numerical source trail is the row ledger, frozen policy and fits, scored CSVs, metric/chart tables and independent QA files. See L18_Validation_Report.txt for tests actually executed. These calculations do not certify native Microsoft Excel behavior or human usability.
