fee-delta-and-impact-simulation

name

fee-delta-and-impact-simulation

description

Solves fee delta and impact simulation problems in the dabstep payment processing dataset. Use this skill for questions about: (1) how much a merchant's fees change if a specific fee rule's rate is modified, (2) which merchants are affected if a fee's account type restriction changes, and (3) how a merchant's total fees change if its MCC code changes. Trigger whenever the question involves "delta", "fee with ID", "relative fee changed to", "account type", "affected by this change", or "MCC code changed".

Fee Delta and Impact Simulation

Dataset Files

File	Key Fields
payments.csv	merchant, year, day_of_year, card_scheme, is_credit, eur_amount, issuing_country, acquirer_country, aci, has_fraudulent_dispute
fees.json	ID, card_scheme, account_type, capture_delay, monthly_fraud_level, monthly_volume, merchant_category_code, is_credit, aci, fixed_amount, rate, intracountry
merchant_data.json	merchant, account_type, capture_delay, acquirer (list), merchant_category_code
acquirer_countries.csv	acquirer, country_code

Core Fee Formula

fee = fixed_amount + rate * transaction_value / 10000

"Relative fee" always refers to the rate field.

Fee Matching Rules

A fee rule applies to a transaction if ALL conditions match:

Rule Field	Null/Empty?	Match Logic
card_scheme	never null	exact match with transaction
account_type	[] = all types	merchant's account_type must be in list (or list is empty)
capture_delay	None = all	merchant's mapped capture_delay must equal rule value
merchant_category_code	[] = all MCCs	merchant's MCC must be in list (or list is empty)
is_credit	None = all	exact match with transaction
aci	[] = all ACI	transaction's aci must be in list (or list is empty)
intracountry	None = all	True if issuing_country==acquirer_country else False
monthly_fraud_level	None = all	merchant's monthly fraud ratio must fall in the range
monthly_volume	None = all	merchant's monthly total EUR must fall in the range

intracountry in fees.json is stored as float (0.0/1.0/None), not bool. Use bool(rule['intracountry']) when comparing.

Capture delay mapping (merchant numeric days → fee rule category):

• <3 (days < 3): merchant values "1", "2"
• 3-5 (3 ≤ days ≤ 5): merchant values "3", "4", "5"
• >5 (days > 5): merchant value "7" or higher
• immediate / manual: exact string match

def map_capture_delay(cd):
    try:
        d = int(cd)
        if d < 3: return '<3'
        elif d <= 5: return '3-5'
        else: return '>5'
    except ValueError:
        return cd  # 'immediate' or 'manual'

Monthly Condition Ranges

def check_monthly_fraud(fee_range, ratio_pct):
    """ratio_pct is percentage (already * 100)"""
    if fee_range is None: return True
    if fee_range == '<7.2%': return ratio_pct < 7.2
    if fee_range == '7.2%-7.7%': return 7.2 <= ratio_pct < 7.7
    if fee_range == '7.7%-8.3%': return 7.7 <= ratio_pct <= 8.3
    if fee_range == '>8.3%': return ratio_pct > 8.3
    return True

def check_monthly_vol(fee_range, vol_eur):
    if fee_range is None: return True
    if fee_range == '<100k': return vol_eur < 100_000
    if fee_range == '100k-1m': return 100_000 <= vol_eur < 1_000_000
    if fee_range == '1m-5m': return 1_000_000 <= vol_eur < 5_000_000
    if fee_range == '>5m': return vol_eur >= 5_000_000
    return True

Monthly stats are always computed over ALL transactions for the merchant in the period (not filtered by card scheme or other criteria). Compute once and reuse for all fee matching.

---

Question Type 1: Fee Rate Delta

Question pattern: "In [period], what delta would [merchant] pay if the relative fee of fee ID=[X] changed to [Y]?"

Algorithm:

1. Load the target fee by ID. Compute rate_change = new_rate - current_rate.
2. Load merchant attributes: account_type, mapped capture_delay, merchant_category_code.
3. Filter payments by merchant and time period.
4. For each month, compute fraud_ratio and total_volume over ALL merchant transactions for that month.
5. Filter transactions where ALL fee criteria match (including monthly conditions checked per-month).
6. delta = rate_change * qualifying['eur_amount'].sum() / 10000
7. Return rounded to 14 decimal places. If no qualifying transactions: 0.00000000000000.

Implementation:

import json, pandas as pd, datetime

fees = json.load(open('fees.json'))
merchants = json.load(open('merchant_data.json'))
payments = pd.read_csv('payments.csv')

fee = next(f for f in fees if f['ID'] == target_fee_id)
rate_change = new_rate - fee['rate']

merchant = next(m for m in merchants if m['merchant'] == target_merchant)
account_type = merchant['account_type']
mcc = merchant['merchant_category_code']
mapped_delay = map_capture_delay(merchant['capture_delay'])

df = payments[(payments['merchant'] == target_merchant) & time_period_filter].copy()
df['month'] = df['day_of_year'].apply(
    lambda d: (datetime.date(2023, 1, 1) + datetime.timedelta(days=d-1)).month)

# Check merchant-level criteria first (fast exit)
if fee['account_type'] and account_type not in fee['account_type']:
    delta = 0.0
elif fee['capture_delay'] is not None and fee['capture_delay'] != mapped_delay:
    delta = 0.0
elif fee['merchant_category_code'] and mcc not in fee['merchant_category_code']:
    delta = 0.0
else:
    mask = df['card_scheme'] == fee['card_scheme']
    if fee['is_credit'] is not None:
        mask &= df['is_credit'] == fee['is_credit']
    if fee['aci']:
        mask &= df['aci'].isin(fee['aci'])
    if fee['intracountry'] is not None:
        mask &= (df['issuing_country'] == df['acquirer_country']) == bool(fee['intracountry'])
    
    if fee['monthly_fraud_level'] is None and fee['monthly_volume'] is None:
        qualifying = df[mask]
    else:
        parts = []
        for month, grp in df[mask].groupby('month'):
            all_m = df[df['month'] == month]
            vol = all_m['eur_amount'].sum()
            fraud_ratio = all_m.loc[all_m['has_fraudulent_dispute'], 'eur_amount'].sum() / vol * 100 if vol > 0 else 0
            if check_monthly_fraud(fee['monthly_fraud_level'], fraud_ratio) and check_monthly_vol(fee['monthly_volume'], vol):
                parts.append(grp)
        qualifying = pd.concat(parts) if parts else pd.DataFrame()
    
    delta = rate_change * qualifying['eur_amount'].sum() / 10000

print(f"{delta:.14f}")

Time Period Mapping (2023 is not a leap year)

# Year: year == 2023
# Month: compute day_of_year range
import datetime
def get_month_range(month_num):
    start = datetime.date(2023, month_num, 1).timetuple().tm_yday
    end = (datetime.date(2023, month_num + 1, 1).timetuple().tm_yday - 1) if month_num < 12 else 365
    return start, end
# Jan:1-31, Feb:32-59, Mar:60-90, Apr:91-120, May:121-151, Jun:152-181
# Jul:182-212, Aug:213-243, Sep:244-273, Oct:274-304, Nov:305-334, Dec:335-365

---

Question Type 2: Account Type Impact ("Which merchants affected?")

Question pattern: "During [year], imagine if the Fee with ID [X] was only applied to account type [Y], which merchants would have been affected by this change?"

"Affected" = merchants that currently have the fee applied but would LOSE it when restricted to type Y (i.e., merchants whose account_type ≠ Y).

Algorithm:

1. Load fee by ID (typically has account_type: []).
2. Filter payments by year and fee's transaction-level criteria (card_scheme, is_credit, aci, intracountry).
3. For each unique merchant in qualifying payments, check merchant-level criteria (account_type, capture_delay, MCC).
4. Among merchants where the fee currently applies, keep those with account_type != target_type.
5. Return sorted alphabetically, comma-separated.

mask = (payments['year'] == 2023) & (payments['card_scheme'] == fee['card_scheme'])
if fee['is_credit'] is not None: mask &= payments['is_credit'] == fee['is_credit']
if fee['aci']: mask &= payments['aci'].isin(fee['aci'])
if fee['intracountry'] is not None:
    mask &= (payments['issuing_country'] == payments['acquirer_country']) == bool(fee['intracountry'])

qualifying = payments[mask]
affected = []
for merchant_name in qualifying['merchant'].unique():
    m = merchant_map[merchant_name]
    if fee['account_type'] and m['account_type'] not in fee['account_type']: continue
    if fee['capture_delay'] is not None and map_capture_delay(m['capture_delay']) != fee['capture_delay']: continue
    if fee['merchant_category_code'] and m['merchant_category_code'] not in fee['merchant_category_code']: continue
    if m['account_type'] != target_account_type:
        affected.append(merchant_name)

print(', '.join(sorted(affected)))

---

Question Type 3: MCC Change Simulation

Question pattern: "Imagine merchant [X] had changed its MCC to [Y] before 2023, what amount delta will it have to pay in fees for the year 2023?"

CRITICAL: Do NOT test with individual sample transactions before running the full computation. Many transactions will legitimately have no matching fee under either MCC scenario — this is completely normal, not an error. Some MCCs have zero specific fee rules; only empty-MCC rules (which match all MCCs) may apply. Just run the full computation directly and let it handle all cases automatically.

CRITICAL: Never return 'Not Applicable'. Always compute and return the numerical delta (even if it is 0).

Algorithm:

1. Load merchant attributes: account_type, mapped capture_delay, current merchant_category_code.
2. Filter 2023 payments for the merchant.
3. Compute monthly stats (fraud_ratio, total_volume) over ALL the merchant's transactions per month.
4. For each transaction, find the best-matching fee under both current MCC and new MCC.
5. delta = sum(new_fees) - sum(current_fees) — transactions with no match contribute 0.
6. Return rounded to 6 decimals.

Fee selection (specificity scoring): Among all matching rules, choose the one with the most specific constraints (highest specificity score). Ties broken by lower fee ID.

def specificity_score(rule):
    score = 0
    if rule['account_type']: score += 1
    if rule['capture_delay'] is not None: score += 1
    if rule['monthly_fraud_level'] is not None: score += 1
    if rule['monthly_volume'] is not None: score += 1
    if rule['merchant_category_code']: score += 1
    if rule['is_credit'] is not None: score += 1
    if rule['aci']: score += 1
    if rule['intracountry'] is not None: score += 1
    return score

Complete implementation (copy and run directly — do not build incrementally):

import json, pandas as pd, datetime

fees = json.load(open('fees.json'))
merchants = json.load(open('merchant_data.json'))
payments = pd.read_csv('payments.csv')

merchant = next(m for m in merchants if m['merchant'] == target_merchant)
account_type = merchant['account_type']
mapped_delay = map_capture_delay(merchant['capture_delay'])
current_mcc = merchant['merchant_category_code']
new_mcc = <new_mcc_value>

df = payments[(payments['merchant'] == target_merchant) & (payments['year'] == 2023)].copy()
df['month'] = df['day_of_year'].apply(
    lambda d: (datetime.date(2023, 1, 1) + datetime.timedelta(days=d-1)).month)

# Compute monthly stats over ALL merchant transactions
monthly_dict = {}
for month, grp in df.groupby('month'):
    vol = grp['eur_amount'].sum()
    fraud = grp.loc[grp['has_fraudulent_dispute'], 'eur_amount'].sum()
    monthly_dict[int(month)] = {'vol': vol, 'fraud_ratio': fraud / vol * 100 if vol > 0 else 0}

def rule_matches(rule, txn, mcc_to_use):
    if rule['card_scheme'] != txn['card_scheme']: return False
    if rule['is_credit'] is not None and rule['is_credit'] != txn['is_credit']: return False
    if rule['aci'] and txn['aci'] not in rule['aci']: return False
    if rule['intracountry'] is not None:
        if (txn['issuing_country'] == txn['acquirer_country']) != bool(rule['intracountry']): return False
    if rule['account_type'] and account_type not in rule['account_type']: return False
    if rule['capture_delay'] is not None and rule['capture_delay'] != mapped_delay: return False
    if rule['merchant_category_code'] and mcc_to_use not in rule['merchant_category_code']: return False
    stats = monthly_dict.get(int(txn['month']), {'vol': 0, 'fraud_ratio': 0})
    if not check_monthly_fraud(rule['monthly_fraud_level'], stats['fraud_ratio']): return False
    if not check_monthly_vol(rule['monthly_volume'], stats['vol']): return False
    return True

def find_best_fee(txn, mcc):
    best, best_score = None, (-1, float('inf'))
    for rule in fees:
        if rule_matches(rule, txn, mcc):
            s = (specificity_score(rule), -rule['ID'])
            if s > best_score:
                best_score = s
                best = rule
    return best  # None means no match → contributes 0

total_current = 0.0
total_new = 0.0
for _, row in df.iterrows():
    txn = row.to_dict()
    fee_c = find_best_fee(txn, current_mcc)
    fee_n = find_best_fee(txn, new_mcc)
    if fee_c:
        total_current += fee_c['fixed_amount'] + fee_c['rate'] * txn['eur_amount'] / 10000
    if fee_n:
        total_new += fee_n['fixed_amount'] + fee_n['rate'] * txn['eur_amount'] / 10000

delta = total_new - total_current
print(round(delta, 6))

---

Common Pitfalls

1. Empty list vs null: [] for account_type/aci/MCC means "all" (same as null). Don't treat it as "none match."
2. intracountry: Use payments['acquirer_country'] directly—it already contains the correct country. Do NOT look it up from acquirer_countries.csv.
3. intracountry stored as float: In fees.json, intracountry values are 0.0, 1.0, or None. Always use bool(rule['intracountry']) when comparing.
4. Capture delay categories: Merchant's numeric capture_delay (e.g., "7") must be mapped before comparing to fee rule's category (">5"). Never compare raw values.
5. Delta sign: delta = new_fees - old_fees (or new_total - current_total). Negative means merchant pays less; positive means more.
6. Monthly stats scope: Calculate fraud level and volume across ALL the merchant's transactions for that month, not just transactions matching the current fee criteria.
7. No matching fee is normal: For Type 3 (MCC change), many transactions may have no matching fee under either MCC. Treat them as 0-fee transactions. NEVER return 'Not Applicable'.
8. MCC with 0 specific rules: Some MCCs have no specific fee rules. Only empty-MCC rules (which match all MCCs) may apply. This is correct behavior — do not debug why no MCC-specific rules match.
9. "Affected" means LOSING the fee (Type 2): Merchants of type Y keep the fee — they are NOT affected. Only merchants of other types are affected.
10. Year boundaries in 2023: day_of_year=1 is Jan 1, day_of_year=365 is Dec 31 (2023 is not a leap year).