fee-delta-and-impact-simulation

name

Fee_Delta_and_Impact_Simulation

description

Solve dabstep questions about fee deltas from rate changes and fee impact simulations. Use for: (1) computing the monetary delta a merchant would pay if a fee's relative rate changed to a new value in a specific time period; (2) determining which merchants are affected when a fee's account_type restriction changes; (3) computing the fee delta if a merchant changed its MCC code. Trigger whenever a question involves "delta", "relative fee", "fee changed", "affected merchants", "MCC code changed", or simulating how a fee rule change impacts merchant payments.

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

Field	Null/empty meaning
account_type (list)	[] → applies to all account types
merchant_category_code (list)	[] → applies to all MCCs
aci (list)	[] → applies to all ACI values
is_credit (bool)	null → applies to both credit and debit
capture_delay, monthly_fraud_level, monthly_volume, intracountry	null → applies to all values

[] 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 ():
     fee_cd  :  
     fee_cd  (, ):  merch_cd == fee_cd
    :
        days = (merch_cd)
         fee_cd == :   days < 
         fee_cd == :   <= days <= 
         fee_cd == :   days > 
    :  
     

 ():
     rule  :  
     rule == :       rate_pct < 
     rule == :   <= rate_pct < 
     rule == :   <= rate_pct < 
     rule == :      rate_pct > 
     

 ():
     rule  :  
     rule == :    vol < 
     rule == :   <= vol < 
     rule == :     <= vol < 
     rule == :      vol >= 
     

---

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[]:
        df = df.iloc[:]


  capture_delay_matches(fee[], merchant[]):
    df = df.iloc[:]


 fee[]   :
    is_intra = df[] == df[]
    df = df[is_intra == (fee[] == )]


 fee[]     fee[]   :
    df[] = df[].apply(day_to_month)
    keep = []
     mo, mdf  df.groupby():
        s2, e2 = MONTH_RANGES[mo]
        m_txns = payments[(payments[] == merchant_name) &
                          (payments[] >= s2) & (payments[] <= e2)]
        total = m_txns[].()
        fraud_pct = (m_txns[m_txns[] == ][].()
                     / total * )  total >   
         (fraud_level_matches(fee[], fraud_pct) 
                volume_matches(fee[], total)):
            keep.append(mdf)
    df = pd.concat(keep)  keep  df.iloc[:]

old_rate = fee[]
delta = (new_rate - old_rate) * df[].() / 
((delta, ))

---

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[]   fee[]:  
     

current_set = {m  m  candidate_merchants  merchant_matches_fee(m, fee[])}
new_set = {m  m  candidate_merchants  merchant_matches_fee(m, [new_account_type])}

affected = ((current_set - new_set) | (new_set - current_set))
(.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 = (m  m  merchants  m[] == merchant_name)
orig_mcc = merchant[]
acct_type = merchant[]
cap_delay = merchant[]

df = payments[payments[] == merchant_name].copy()
df[] = df[].apply(day_to_month)


monthly_stats = {}
 mo, (s, e)  MONTH_RANGES.items():
    m_txns = df[df[] == mo]
    total = m_txns[].()
    fraud = m_txns[m_txns[] == ][].()
    monthly_stats[mo] = {
        : total,
        : (fraud / total * )  total >   
    }

 ():
    total_fee = 
     _, txn  df.iterrows():
        mo = txn[]
        fr = monthly_stats[mo][]
        vol = monthly_stats[mo][]
        best, best_spec = , -
         fee  fees:
            
             fee[]  acct_type   fee[]: 
              capture_delay_matches(fee[], cap_delay): 
             fee[]  mcc_code   fee[]: 
            
              fraud_level_matches(fee[], fr): 
              volume_matches(fee[], vol): 
            
             fee[] != txn[]: 
             fee[]     fee[] != txn[]: 
             fee[]  txn[]   fee[]: 
             fee[]   :
                same = txn[] == txn[]
                 fee[] ==    same: 
                 fee[] ==   same: 
            
            spec = specificity(fee)
             spec > best_spec:
                best_spec = spec
                best = fee
         best:
            total_fee += best[] + best[] * txn[] / 
     total_fee

fee_orig = calc_total_fees(orig_mcc)
fee_new  = calc_total_fees(new_mcc)
delta = fee_new - fee_orig
((delta, ))

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).