Icon

IS-216 LoyaltyClubTest

00 | SOURCE DATA & CONFIGURATION

Purpose: Establish a transparent and reproducible foundation for research-informed synthetic loyalty membership modelling.

Data source: UCI Online Retail (2010–2011), accessed via Kaggle. Contains 541,909 original transaction lines from a UK-based online retailer.

Configuration: Document verified dataset information, proposed modelling assumptions, scientific references and parameters requiring empirical calibration.

Methodological control: Original transactions remain unchanged. Unverified assumptions are explicitly identified, and no synthetic membership is generated at this stage.

01 | REAL TRANSACTION DATA CLEANING

Purpose: Prepare the original Online Retail transactions for reliable customer purchasing analysis.

Method: Exclude unidentified customer records, standardize customer identifiers, separate cancellations, retain positive quantities and prices, calculate line-level sales value in GBP, and convert transaction dates.

Validation: 541,909 original transaction lines are reduced to 397,884 qualifying positive-price sales lines with identifiable customers.

Limitation: Excluded records remain part of the original source. The cleaned dataset represents qualifying identifiable customer sales, not necessarily all retailer activity. No synthetic data are introduced.

02 | ORDER-LEVEL AGGREGATION

Purpose: Transform cleaned transaction lines into individual customer orders.

Method: Group by InvoiceNo and CustomerID, sum line-level revenue and quantities, and retain the earliest recorded order timestamp and order country.

Validation: 397,884 qualifying transaction lines are aggregated into 18,532 distinct orders belonging to 4,338 customers.

Analytical relevance: Provides a reliable basis for historical purchase frequency, average order value and customer behaviour modelling. No synthetic data are introduced.

03 | CUSTOMER PURCHASE BEHAVIOUR

Purpose: Examine actual customer purchasing behaviour to establish an empirical foundation for research-informed synthetic loyalty enrolment modelling.

Method: Aggregate observed orders by CustomerID to calculate total orders, revenue, average order value, purchased quantities, recency, repeat purchasing status and observed purchasing span.

Observed findings: 4,338 customers and 18,532 qualifying orders. Of these customers, 2,845 are repeat purchasers and 1,493 are one-time purchasers. Purchase frequency and monetary value exhibit strongly right-skewed distributions.

Validation: Customer and order totals are reconciled with the order-level dataset. Descriptive statistics and a histogram are used to examine behavioural variation and extreme values.

Methodological limitation: These customer profiles summarise the complete observation period. The enrolment model must use separate historical measurements calculated only from purchases occurring before each assessment date. No synthetic membership information is introduced.

04 | HISTORICAL CUSTOMER-MONTH DATASET

Purpose: Construct historical customer purchasing features for research-informed loyalty enrolment modelling without using future transactions.

Method: Generate monthly customer assessment records and connect them to actual orders placed before each assessment date. Calculate historical purchase frequency, 90-day order frequency, revenue, average order value and recency.

Observed data: 30,280 eligible customer-month observations generated from the original Online Retail transactions.

Validation: All 30,280 observations pass the defined consistency checks, with no invalid records identified.

Methodological relevance: All behavioural features are based on observed purchases available before each assessment date. The 90-day observation window remains a proposed parameter requiring sensitivity analysis.

Limitation: The dataset contains historical purchasing behaviour, not observed loyalty programme membership. No synthetic membership information has been generated.

05 | RESEARCH-INFORMED ENROLMENT MODEL & CALIBRATION

Purpose: Develop a transparent, reproducible model for generating hypothetical loyalty-program enrolment probabilities using observed historical purchasing behaviour.

Method: Customer purchasing frequency and average order value are calculated from transactions preceding each monthly assessment (March–November 2011), using a 90-day historical window. Highly skewed variables are transformed using ln(1+x) and centred against observed March 2011 reference medians.

Scientific basis: Meyer-Waarden and Benavent (2009) and Leenheer et al. (2007) support the relationship between purchasing behaviour and loyalty-program self-selection. Singer and Willett (1993) provide methodological support for modelling event timing. Transferred behavioural coefficients are scenario assumptions, not directly estimated effects for this dataset.

Scenario design: Four behavioural scenarios (NO_EFFECT, WEAK, REFERENCE, STRONG) are combined with three hypothetical cumulative enrolment targets (5%, 15%, 30%). The targets are not observed or empirically established membership rates.

Calibration: Baseline log-hazard α is calibrated separately for all 12 combinations using numerical bisection. The calibration uses customer-specific cumulative relative exposure and targets an absolute enrolment-rate error of no more than 0.1 percentage points.

Validation: All 12 calibrated models passed the defined technical checks. Each model contains 3,974 eligible customers; probabilities remain within [0,1], and numerical calibration errors satisfy the specified tolerance.

Limitations: Successful calibration does not establish real-world predictive validity. Extreme purchasing behaviour can produce near-certain enrolment probabilities under stronger scenarios. Customers without eligible assessment periods require separate treatment. All loyalty-program outcomes remain hypothetical and cannot establish causal effects on purchasing behaviour.

Provenance: Historical transactions are observed UCI Online Retail data. Membership probabilities, behavioural-effect transfers, enrolment targets and calibration parameters are model-derived or assumed.

06 | SYNTHETIC LOYALTY MEMBERSHIP GENERATION & VALIDATION

Purpose: Generate reproducible, hypothetical loyalty membership and signup dates using observed online purchasing behaviour.

Method: A discrete-time enrolment hazard model uses historical purchasing frequency and average order value. Four behavioural-effect scenarios (NO_EFFECT, WEAK, REFERENCE, STRONG) are combined with three hypothetical uptake targets (5%, 15%, 30%). The baseline parameter is calibrated for each scenario. A fixed simulation seed (2162026) supports reproducibility.

Selected scenario: REFERENCE + MODERATE_UPTAKE (15% target).

Results: Of 4,338 observed customers, 3,974 had eligible assessment months. The selected scenario generated 580 synthetic members and 3,394 eligible non-members. The remaining 364 customers were retained as unassessed, without assigning synthetic membership status. Realised uptake among eligible customers was approximately 14.6%.

Validation: 4,338 unique customer records; no invalid membership-status combinations; all 580 simulated signup months fall within eligible assessment periods. Scenario-level counts and calibration were checked.

Provenance and limitations: Purchase histories are observed; loyalty membership and signup months are simulated. Uptake targets and behavioural-effect magnitudes are scenario assumptions, not empirically established estimates for this retailer. Simulated differences cannot demonstrate causal loyalty-program effects.

07 | TRANSACTION INTEGRATION & HISTORICAL MEMBERSHIP

Purpose: Integrate observed retail transactions with the synthetic customer-level loyalty registry and establish membership status at the time of each purchase.

Method: Join transactions by CustomerID, preserve observed purchase dates, and compare purchase month against simulated signup month. Membership is assumed effective from the beginning of the simulated signup month.

Results: All 397,884 transaction lines were preserved. Historical classification: YES 54,717; NO 329,854; UNASSESSED 13,313.

Validation: No unmatched transaction lines, no invalid historical classifications, and all 397,884 lines pass consistency checks.

Provenance: Transactions are observed; membership and signup months are synthetic. Results describe hypothetical loyalty membership, not actual program participation or causal effects.

Retains customer assessments from March to November 2011, excluding December because the dataset does not cover the complete month.
Row Filter
Retains customer IDs, monthly assessment dates, centred purchasing features and validation status for enrolment probability calibration.
Column Filter
Aggregates monthly relative enrolment intensities by customer and scenario, recording exposure periods for subsequent baseline calibration.
GroupBy
Selects the no-effect scenario to verify that cumulative relative enrolment intensity equals the number of eligible assessment months.
Row Filter
Cross Joiner
Calculates each customer-month's relative enrolment intensity using historical purchasing behaviour and scenario-specific coefficients.
Math Formula
Defines seven exploratory baseline log-hazard values for enrolment sensitivity analysis. These are scenario assumptions, not empirically estimated enrolment rates.
Table Creator
Combines each customer–scenario record with seven exploratory baseline alpha values for cumulative enrolment probability analysis.
Cross Joiner
Calculates the difference between cumulative hazard and eligible months to validate the no-effect scenario.
Math Formula
Verifies that exposure differences are zero across all eligible customers in the no-effect scenario.
Statistics
Calculates expected enrolment rates across 3,974 eligible customers for each behavioural scenario and baseline alpha value.
GroupBy
Restructures expected cumulative enrolment rates into a comparison table, showing all four behavioural scenarios across seven exploratory baseline alpha values.
Pivot
Calculates each customer's cumulative enrolment probability from scenario-specific exposure and an exploratory baseline alpha value.
Math Formula
Combines customer-level relative enrolment exposure across four behavioural scenarios with three hypothetical uptake targets (5%, 15%, 30%) to prepare twelve baseline calibration combinations. Uptake targets are scenario assumptions, not observed mem
Cross Joiner
Standardises cumulative relative enrolment exposure and eligible-month column names for numerical baseline hazard calibration across twelve scenario combinations.
Column Renamer
Renames scenario-specific expected enrolment rates for transparent comparison and reporting. All rates are model-derived sensitivity outcomes, not observed membership rates.
Column Renamer
Defines three hypothetical cumulative enrolment targets (5%, 15% and 30%) for sensitivity-based baseline calibration. Targets are explicit scenario assumptions, not empirically observed membership rates.
Table Creator
Independently validates all twelve calibrated models by checking parameter completeness, calibration error, probability bounds and eligible customer counts.
Rule Engine
Validates the twelve calibration groups by counting eligible customers and examining minimum and maximum cumulative relative hazards before numerical baseline calibration.
GroupBy
Numerically calibrates baseline log-hazard alpha using bisection for twelve combinations of behavioural effects and hypothetical uptake targets. Validates 3,974 customers per combination and reports calibrated alpha, expected enrolment, numerical err
Python Script
Imports 541,909 original transaction lines from the UK Online Retail dataset (2010–2011), sourced from UCI and downloaded via Kaggle.
CSV Reader
Documents the original data source, proposed modelling assumptions, scientific references and parameters requiring empirical calibration before synthetic membership generation.
Table Creator
Excludes transaction lines without CustomerID to enable reliable customer-level purchasing analysis.
Row Filter
Removes the trailing '.0' from CustomerID values to ensure consistent customer identification across datasets.
String Manipulation
Separates invoices starting with 'C' to exclude cancellations from qualifying sales while retaining them for separate analysis.
Row Splitter
Retains transaction lines with Quantity greater than zero, ensuring positive purchased quantities.
Row Filter
Excludes transaction lines with non-positive UnitPrice to retain qualifying positive-price sales.
Row Filter
Calculates reference medians for log-transformed purchase frequency and average order value using only customers observed by 1 March 2011.
GroupBy
Renames the March 2011 reference medians and customer count for use in behavioural feature centering.
Column Renamer
Converts assessment timestamps to date strings to isolate the first historical reference month.
Date&Time to String
Retains customer observations from 1 March 2011 to establish fixed reference values without using later assessment periods.
Row Filter
Centers log-transformed average order value around the March 2011 reference median.
Math Formula
Selects 580 simulated loyalty members to validate signup dates against their eligible assessment periods.
Row Filter
Documents model assumptions, observed parameters, scientific evidence and unresolved calibration requirements for synthetic loyalty enrolment modelling.
Table Creator
Adds the fixed March 2011 reference medians to every historical customer-month record for subsequent feature centering.
Cross Joiner
Checks that simulated signup occurs on or before the last eligible assessment.
Date&Time Difference
Centers log-transformed purchase frequency around the March 2011 reference median.
Math Formula
Checks that simulated signup occurs on or after the first eligible assessment.
Date&Time Difference
Confirms that all simulated member signup dates fall within their eligible assessment periods.
GroupBy
Validates that simulated signup dates fall within each customer's eligible assessment period.
Rule Engine
Counts verified, proposed and pending model parameters to document which assumptions are established and which still require calibration.
GroupBy
Links 397,884 observed transaction lines to the validated synthetic loyalty registry using CustomerID, preserving the original purchasing records.
Joiner
Selects observed March 2011 purchasing variables for empirical calibration reference analysis.
Column Filter
Prepares the validated synthetic loyalty customer registry for integration with observed transactions.
Column Filter
Converts purchase and signup year-month codes to numbers for chronological membership comparison.
String to Number
Extracts purchase and synthetic signup year-month values for historical membership classification.
Date&Time to String
Classifies historical purchases as YES, NO or UNASSESSED using simulated signup month and customer eligibility.
Rule Engine
Calculates empirical purchasing distributions and quantiles from March 2011 to define representative customer contrasts for calibration.
Statistics
Renames observed March 2011 purchasing quartiles for use in behavioural effect calibration.
Column Renamer
Retains the observed lower and upper purchasing quartiles used to define customer contrasts in the enrolment scenarios.
Column Filter
Calculates observed purchasing quartiles for 1,682 customers in March 2011 to define representative frequency and monetary value comparisons for enrolment model calibration.
GroupBy
Defines four behavioural effect scenarios using published Cox coefficients and explicit sensitivity multipliers. Transfer to the observed customer quartiles is a modelling assumption.
Table Creator
Calculates each transaction line's value in GBP by multiplying purchased quantity by unit price.
Math Formula
Calculates scenario-specific monetary coefficients by scaling the published Cox coefficient and mapping it to the observed March 2011 average order value quartile contrast.
Math Formula
Checks that historical membership status is consistent with customer eligibility, synthetic signup month and purchase month.
Rule Engine
Converts InvoiceDate into a Date&Time column to enable chronological purchasing analysis and time-based calculations.
String to Date&Time
Recalculates the frequency hazard ratio from each scenario coefficient and the observed purchasing quartile contrast.
Math Formula
Summarises historical membership across all 397,884 transaction lines and checks that no records remain unclassified.
GroupBy
Combines four research-informed sensitivity scenarios with observed March 2011 purchasing quartiles for reproducible coefficient calculations.
Cross Joiner
Prepares the final analytical dataset by combining observed transactions, synthetic loyalty membership and validated historical membership status.
Column Filter
Calculates scenario-specific frequency coefficients by scaling the published Cox coefficient and mapping it to the observed March 2011 frequency quartile contrast.
Math Formula
Verifies historical membership consistency across all 397,884 transaction lines. All records pass validation.
GroupBy
Exports the validated synthetic loyalty customer registry, including members, non-members, unassessed customers, simulated signup months and reproducibility metadata.
CSV Writer
Exports the validated transaction-level dataset with observed purchases, simulated loyalty membership and historical membership classifications.
CSV Writer
Recalculates the monetary hazard ratio from each scenario coefficient and the observed average order value quartile contrast.
Math Formula
Visualizes the highly right-skewed distribution of purchase frequency, showing that most customers place relatively few orders while a small minority purchase frequently.
Histogram
Verifies that all 4,338 customers and 18,532 qualifying orders are preserved after customer-level aggregation.
GroupBy
Defines ten proposed monthly assessment dates from March to December 2011 for evaluating historical customer purchasing behaviour.
Table Creator
Creates one customer-month combination for every customer and assessment date, before filtering out periods prior to the customer's first purchase.
Cross Joiner
Calculates the time between each customer's first observed purchase and the assessment date to verify chronological eligibility.
Date&Time Difference
Converts monthly assessment dates into Date&Time values for comparison with actual customer purchase timestamps.
String to Date&Time
Retains customer identifiers and first observed purchase dates to determine when each customer becomes eligible for historical assessment.
Column Filter
Calculates the time between each observed order and the assessment date to identify purchases made before customer evaluation.
Date&Time Difference
Retains only orders placed before each assessment date, preventing future purchases from influencing historical enrolment probabilities.
Row Filter
Removes customer-month records occurring before the customer's first observed purchase, preventing future information from influencing earlier assessments.
Row Filter
Matches each customer-month assessment with the customer's observed orders using CustomerID, enabling subsequent filtering to purchases made before the assessment date.
Joiner
Calculates revenue from orders placed within the 90 days preceding each assessment date, excluding older orders from the rolling revenue measure.
Math Formula
Aggregates purchases by customer and assessment month to calculate historical order frequency, 90-day purchasing activity, spending and last purchase date without using future transactions.
GroupBy
Flags observed orders within the 90 days preceding each assessment date, enabling historical purchase frequency calculations.
Rule Engine
Validates historical customer-month features by checking purchase counts, recency and revenue for logically inconsistent values.
Rule Engine
Counts valid and invalid historical customer-month records. All 30,280 records pass the defined consistency checks.
GroupBy
Renames historical customer-month purchasing measures for consistent use in the research-informed enrolment model.
Column Renamer
Calculates days since the customer's most recent purchase before each assessment date, using only historical transactions.
Date&Time Difference
Evaluates log-transformed purchasing features. Frequency skewness decreases from 9.516 to 0.759, and average order value skewness decreases from 45.788 to 0.108.
Statistics
Applies a natural log(1+x) transformation to historical 90-day purchase frequency to reduce skewness while preserving zero-order observations.
Math Formula
Applies a natural log(1+x) transformation to historical average order value in GBP to reduce the influence of exceptionally large orders.
Math Formula
Joins monthly customer-level relative enrolment hazards with the twelve calibrated baseline models using Scenario_ID. Expands each customer-month into three hypothetical uptake scenarios while preserving assessment dates and calibration provenance.
Joiner
Calculates each customer's conditional monthly enrolment probability using the calibrated baseline hazard and scenario-specific relative purchasing intensity. Probabilities represent hypothetical enrolment events and are not observed membership outco
Math Formula
Summarises technical validation results across all twelve calibrated models, documenting how many satisfy the predefined numerical accuracy, probability bounds and customer-count requirements.
GroupBy
Prepares validated customer-month enrolment exposure records from observed historical purchasing behaviour and scenario-specific relative hazards. Preserves assessment dates to support chronological, reproducible simulation of first enrolment events.
Column Filter
Simulates first enrolment month using a reproducible customer draw (seed 2162026) shared across scenarios.
Python Script
Counts synthetic members and non-members for each behavioural and uptake scenario to validate simulated enrolment outcomes.
GroupBy
Sorts monthly enrolment records chronologically within each customer, behavioural scenario and uptake scenario. Ensures deterministic ordering for subsequent cumulative hazard calculations and first-enrolment event simulation.
Sorter
Calculates and validates cumulative enrolment probabilities chronologically for each customer and scenario.
Python Script
Pivots synthetic membership counts into Yes and No columns for comparison across all twelve calibrated scenarios.
Pivot
Renames simulated membership counts for consistent enrolment-rate calculations.
Column Renamer
Calculates the realised synthetic enrolment rate for each calibrated scenario.
Math Formula
Selects the REFERENCE + MODERATE_UPTAKE scenario for the final illustrative loyalty dataset.
Row Filter
Joins simulated loyalty outcomes to all 4,338 observed customers, preserving those without eligible assessment months.
Joiner
Measures percentage-point deviations between simulated enrolment rates and hypothetical targets.
Math Formula
Calculates the total eligible customers per scenario to verify complete membership classification.
Math Formula
Verifies that all 4,338 observed customers have unique IDs in the final synthetic loyalty registry. Kopier
Duplicate Row Filter
Aggregates transaction lines into individual orders using InvoiceNo and CustomerID, calculating order value, quantity, line count and earliest purchase timestamp.
GroupBy
Validates membership status, signup month and eligibility consistency for all customers.
Rule Engine
Classifies customers by eligibility for synthetic loyalty enrolment simulation.
Rule Engine
Validates eligible and non-eligible customer counts in the complete customer registry. Kopier
GroupBy
Renames observed customer purchasing metrics to consistent column names for behavioural analysis and future enrolment modelling.
Column Renamer
Identifies repeat purchasers by classifying customers with two or more observed orders as Yes.
Rule Engine
Renames aggregated order measures to consistent and descriptive column names for subsequent customer behaviour analysis.
Column Renamer
Summarises membership consistency checks and verifies zero invalid customer records.
GroupBy
Aggregates real customer orders to calculate purchase frequency, total spending, average order value, first and last purchase dates, and total purchased units.
GroupBy
Calculates the number of days between each customer's first and last observed purchases to measure their recorded purchasing span.
Date&Time Difference
Calculates descriptive statistics, including mean, median and standard deviation, to examine customer purchasing patterns and identify extreme values.
Statistics
Counts customers classified as repeat purchasers (2+ orders) or one-time purchasers (1 order), based on observed transaction history.
GroupBy
Calculates elapsed days since each customer's last observed purchase, using 10 December 2011 as the reference date to measure customer recency.
Date&Time Difference

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: bruker
Created at: 2026-10-08
On NodePit since: 2026-10-12
Last update: 2026-10-18
Created with KNIME version: v5.12.0

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!