Fee_Delta_and_Impact_Simulation

Fee Delta and Impact Simulation

Three question types appear in this category:

1. Rate-change delta: "In [month/year] what delta would [merchant] pay if the relative fee of fee ID=[N] changed to [new_rate]?"
2. Account-type impact: "During [year], if fee ID=[N] was only applied to account type [X], which merchants would have been affected?"
3. MCC-change delta: "If merchant M had changed its MCC to X before [year], what delta would it pay?"

Data Sources

• fees.json — ~1000 fee rule objects
• merchant_data.json — merchant properties (account_type, capture_delay, acquirers, MCC)
• payments.csv — transaction records (issuing_country, acquirer_country per row)
• manual.md — field definitions and fee formula (read first)

Fee Formula

fee = fixed_amount + rate * transaction_value / 10000

"Relative fee" = the rate field. Delta = new_total_fee − old_total_fee.

Fee Rule Field Semantics

[] means "no restriction" (match all), NOT "match nothing".

Helper Functions (reuse across all question types)

import json, pandas as pd

with open('fees.json') as f: fees = json.load(f)
with open('merchant_data.json') as f: merchants = json.load(f)
payments = pd.read_csv('payments.csv')

MONTH_RANGES = {
    1:(1,31),2:(32,59),3:(60,90),4:(91,120),5:(121,151),6:(152,181),
    7:(182,212),8:(213,243),9:(244,273),10:(274,304),11:(305,334),12:(335,365)
}

def day_to_month(d):
    for m,(s,e) in MONTH_RANGES.items():
        if s <= d <= e: return m
    return 12

def capture_delay_matches(fee_cd, merch_cd):
    if fee_cd is None: return True
    if fee_cd in ('immediate', 'manual'): return merch_cd == fee_cd
    try:
        days = float(merch_cd)
        if fee_cd == '<3':  return days < 3
        if fee_cd == '3-5': return 3 <= days <= 5
        if fee_cd == '>5':  return days > 5
    except: return False
    return False

def fraud_level_matches(rule, rate_pct):
    if rule is None: return True
    if rule == '<7.2%':      return rate_pct < 7.2
    if rule == '7.2%-7.7%': return 7.2 <= rate_pct < 7.7
    if rule == '7.7%-8.3%': return 7.7 <= rate_pct < 8.3
    if rule == '>8.3%':     return rate_pct > 8.3
    return False

def volume_matches(rule, vol):
    if rule is None: return True
    if rule == '<100k':   return vol < 100000
    if rule == '100k-1m': return 100000 <= vol < 1000000
    if rule == '1m-5m':   return 1000000 <= vol < 5000000
    if rule == '>5m':     return vol >= 5000000
    return False

Question Type 1: Rate-Change Delta

Algorithm

1. Find target fee by ID. Get merchant's properties.
2. Filter payments for merchant + time period.
3. Apply all fee matching criteria as filters.
4. delta = (new_rate - old_rate) * matching_df['eur_amount'].sum() / 10000

fee = next(r for r in fees if r['ID'] == target_fee_id)
merchant = next(m for m in merchants if m['merchant'] == merchant_name)

# Filter for merchant and time period
s, e = MONTH_RANGES[month_num]  # or use all rows for full year
df = payments[(payments['merchant'] == merchant_name) &
              (payments['day_of_year'] >= s) & (payments['day_of_year'] <= e)].copy()

# card_scheme (always required)
df = df[df['card_scheme'] == fee['card_scheme']]

# is_credit (null = both)
if fee['is_credit'] is not None:
    df = df[df['is_credit'] == fee['is_credit']]

# aci ([] = all)
if fee['aci']:
    df = df[df['aci'].isin(fee['aci'])]

# merchant_category_code ([] = all)
if fee['merchant_category_code']:
    if merchant['merchant_category_code'] not in fee['merchant_category_code']:
        df = df.iloc[0:0]

# account_type ([] = all)
if fee['account_type']:
    if merchant['account_type'] not in fee['account_type']:
        df = df.iloc[0:0]

# capture_delay (None = all)
if not capture_delay_matches(fee['capture_delay'], merchant['capture_delay']):
    df = df.iloc[0:0]

# intracountry (None = all; 1.0 = domestic; 0.0 = international)
if fee['intracountry'] is not None:
    is_intra = df['issuing_country'] == df['acquirer_country']
    df = df[is_intra == (fee['intracountry'] == 1.0)]

# monthly_fraud_level / monthly_volume — process month by month
if fee['monthly_fraud_level'] is not None or fee['monthly_volume'] is not None:
    df['_month'] = df['day_of_year'].apply(day_to_month)
    keep = []
    for mo, mdf in df.groupby('_month'):
        s2, e2 = MONTH_RANGES[mo]
        m_txns = payments[(payments['merchant'] == merchant_name) &
                          (payments['day_of_year'] >= s2) & (payments['day_of_year'] <= e2)]
        total = m_txns['eur_amount'].sum()
        fraud_pct = (m_txns[m_txns['has_fraudulent_dispute'] == True]['eur_amount'].sum()
                     / total * 100) if total > 0 else 0
        if (fraud_level_matches(fee['monthly_fraud_level'], fraud_pct) and
                volume_matches(fee['monthly_volume'], total)):
            keep.append(mdf)
    df = pd.concat(keep) if keep else df.iloc[0:0]

old_rate = fee['rate']
delta = (new_rate - old_rate) * df['eur_amount'].sum() / 10000
print(round(delta, 6))

Question Type 2: Account-Type Impact Simulation

"If fee ID=X was only applied to account type Y, which merchants would have been affected?"

Affected = merchants whose status changes (currently get the fee but wouldn't, or vice versa).

fee = next(r for r in fees if r['ID'] == target_fee_id)
merchant_lookup = {m['merchant']: m for m in merchants}

# Find all merchants who have at least one transaction matching fee's non-account_type criteria
mask = 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'])
# Note: intracountry, MCC, capture_delay are per-merchant and checked below
candidate_merchants = payments[mask]['merchant'].unique()

def merchant_matches_fee(m_name, acct_type_list):
    if m_name not in merchant_lookup: return False
    merch = merchant_lookup[m_name]
    if acct_type_list and merch['account_type'] not in acct_type_list: return False
    if not capture_delay_matches(fee['capture_delay'], merch['capture_delay']): return False
    if fee['merchant_category_code'] and merch['merchant_category_code'] not in fee['merchant_category_code']: return False
    return True

current_set = {m for m in candidate_merchants if merchant_matches_fee(m, fee['account_type'])}
new_set = {m for m in candidate_merchants if merchant_matches_fee(m, [new_account_type])}

affected = sorted((current_set - new_set) | (new_set - current_set))
print(', '.join(affected))

Question Type 3: MCC-Change Delta

"If merchant M had changed its MCC to X before [year], what delta would it pay?"

Compute total fees under original MCC and under the new MCC; delta = new_total − original_total.

Key rule: For each transaction, find the best-matching fee = the matching fee rule with the highest specificity score. Specificity = 1 point per constrained field (binary, not length-based):

def specificity(fee):
    """Count fields that actively constrain (non-null / non-empty list)."""
    return sum([
        fee['card_scheme'] is not None,          # always non-null
        bool(fee['account_type']),               # non-empty list
        fee['capture_delay'] is not None,
        fee['monthly_fraud_level'] is not None,
        fee['monthly_volume'] is not None,
        bool(fee['merchant_category_code']),     # non-empty list
        fee['is_credit'] is not None,
        bool(fee['aci']),                        # non-empty list
        fee['intracountry'] is not None,
    ])

Complete Implementation

import json, pandas as pd

with open('fees.json') as f: fees = json.load(f)
with open('merchant_data.json') as f: merchants = json.load(f)
payments = pd.read_csv('payments.csv')

MONTH_RANGES = {
    1:(1,31),2:(32,59),3:(60,90),4:(91,120),5:(121,151),6:(152,181),
    7:(182,212),8:(213,243),9:(244,273),10:(274,304),11:(305,334),12:(335,365)
}
def day_to_month(d):
    for m,(s,e) in MONTH_RANGES.items():
        if s<=d<=e: return m
    return 12

merchant_name = 'TARGET_MERCHANT'
new_mcc = TARGET_NEW_MCC

merchant = next(m for m in merchants if m['merchant'] == merchant_name)
orig_mcc = merchant['merchant_category_code']
acct_type = merchant['account_type']
cap_delay = merchant['capture_delay']

df = payments[payments['merchant'] == merchant_name].copy()
df['month'] = df['day_of_year'].apply(day_to_month)

# Compute monthly fraud rate and volume once
monthly_stats = {}
for mo, (s, e) in MONTH_RANGES.items():
    m_txns = df[df['month'] == mo]
    total = m_txns['eur_amount'].sum()
    fraud = m_txns[m_txns['has_fraudulent_dispute'] == True]['eur_amount'].sum()
    monthly_stats[mo] = {
        'vol': total,
        'fraud_rate': (fraud / total * 100) if total > 0 else 0.0
    }

def calc_total_fees(mcc_code):
    total_fee = 0.0
    for _, txn in df.iterrows():
        mo = txn['month']
        fr = monthly_stats[mo]['fraud_rate']
        vol = monthly_stats[mo]['vol']
        best, best_spec = None, -1
        for fee in fees:
            # Merchant-level filters
            if fee['account_type'] and acct_type not in fee['account_type']: continue
            if not capture_delay_matches(fee['capture_delay'], cap_delay): continue
            if fee['merchant_category_code'] and mcc_code not in fee['merchant_category_code']: continue
            # Monthly filters
            if not fraud_level_matches(fee['monthly_fraud_level'], fr): continue
            if not volume_matches(fee['monthly_volume'], vol): continue
            # Transaction-level filters
            if fee['card_scheme'] != txn['card_scheme']: continue
            if fee['is_credit'] is not None and fee['is_credit'] != txn['is_credit']: continue
            if fee['aci'] and txn['aci'] not in fee['aci']: continue
            if fee['intracountry'] is not None:
                same = txn['issuing_country'] == txn['acquirer_country']
                if fee['intracountry'] == 1.0 and not same: continue
                if fee['intracountry'] == 0.0 and same: continue
            # Track best by specificity
            spec = specificity(fee)
            if spec > best_spec:
                best_spec = spec
                best = fee
        if best:
            total_fee += best['fixed_amount'] + best['rate'] * txn['eur_amount'] / 10000
    return total_fee

fee_orig = calc_total_fees(orig_mcc)
fee_new  = calc_total_fees(new_mcc)
delta = fee_new - fee_orig
print(round(delta, 6))

Execution tip: Run calc_total_fees for both MCCs in a single code block. Do NOT test individual transactions before running the full calculation — it wastes turns and provides no benefit.

Common Mistakes

1. [] means "no match": Wrong. [] and null both mean "applies to all". An account_type: [] rule applies to every merchant.

2. Wrong specificity scoring: Each constrained field adds exactly 1 point regardless of list size. Do not add len(list) — add 1 if non-empty.

3. Wrong delta sign: delta = fee_new_MCC − fee_original_MCC. Negative = merchant saves money.

4. Missing field checks: Check ALL non-null fee fields: card_scheme, is_credit, aci, account_type, capture_delay, MCC, intracountry, monthly_fraud_level, monthly_volume.

5. Intracountry check: Use payments['issuing_country'] == payments['acquirer_country'] directly (both columns exist per row in payments.csv). No join needed.

6. Monthly metrics computed wrong: Monthly fraud rate and volume must be computed over the full natural month for the merchant, not just the filtered transactions.

7. Capture delay numeric: Merchant capture_delay = '7' is a string. Parse as float to compare with '>5'. Values 'immediate' and 'manual' are string-matched directly.

8. Output precision: Round to the decimal places specified in the question (usually 6). Use round(delta, 6).

9. Impact simulation: Also check merchants who gain the fee (not just lose it). Use symmetric difference: (current_set - new_set) | (new_set - current_set).