fee-delta-and-impact-simulation

# Fee Delta and Impact Simulation This skill covers three question types: 1. **Rate-change delta**: How much more/less would a merchant pay if a specific fee's rate changed? 2. **Affected-merchant identification**: Which merchants would be impacted if a fee rule's applicability changed? 3. **MCC-change delta**: How much more/less would a merchant pay if its MCC code changed? ## CRITICAL: Submit Immediately After Computing **Do NOT spend extra turns verifying individual transactions or investigating unmatched transactions.** Once you have the computed delta, print it and submit. It is completely normal for 40–70% of transactions to have no matching fee rule — this is expected behavior, not a bug. ## Data Files | File | Purpose | |------|---------| | `fees.json` | 1000 fee rules with matching criteria and rates | | `payments.csv` | 138,236 transactions (all year 2023) | | `merchant_data.json` | 30 merchants with account_type, MCC, capture_delay, acquirers | | `acquirer_countries.csv` | Maps acquirer names → country codes (rarely needed) | | `manual.md` | Domain definitions (read first) | ## Key Domain Rules ### Fee Formula ``` fee = fixed_amount + rate * transaction_value / 10000 ``` **"Relative fee"** = the `rate` field. When a question says "relative fee changed to X", it means the `rate` changes from its current value to X. ### Fee Rule Matching A transaction/merchant matches a fee rule when ALL conditions hold: | Fee field | Condition | |-----------|-----------| | `card_scheme` | Exact match with transaction's card_scheme | | `account_type` | `[]` (empty list) = applies to all; else merchant's account_type must be in the list | | `merchant_category_code` | `[]` (empty list) = applies to all; else merchant's MCC must be in the list | | `capture_delay` | `null` = applies to all; else see mapping below | | `monthly_fraud_level` | `null` = applies to all; else see calculation below | | `monthly_volume` | `null` = applies to all; else see calculation below | | `is_credit` | `null` = applies to all; else must equal transaction's is_credit | | `aci` | `[]` (empty list) or `null` = applies to all; else transaction's aci must be in the list | | `intracountry` | `null` = applies to all; `1.0` = issuing_country == acquirer_country; `0.0` = issuing_country != acquirer_country | **Critical**: Empty list `[]` for `account_type`, `merchant_category_code`, and `aci` means "applies to all values". **When multiple fees match a transaction**: Use the **first matching fee** — iterate fees.json in order (fees are sorted by ascending ID) and return the FIRST fee that satisfies ALL criteria. Do NOT pick the most-specific one or sum them all. ### Capture Delay Mapping Merchant `capture_delay` values must be mapped to fee rule categories: ```python def capture_delay_matches(merchant_val, fee_val): if fee_val is None: return True if merchant_val in ('immediate', 'manual'): return merchant_val == fee_val n = int(merchant_val) if fee_val == '<3': return n < 3 if fee_val == '3-5': return 3 <= n <= 5 if fee_val == '>5': return n > 5 return False ``` ### Monthly Fraud Level Calculation Compute per merchant per natural calendar month (using day_of_year): ```python fraud_volume = sum(eur_amount where has_fraudulent_dispute == True) total_volume = sum(eur_amount) fraud_ratio = fraud_volume / total_volume * 100 # as percentage # Map to fee rule category: # '<7.2%' : ratio < 7.2 # '7.2%-7.7%': 7.2 <= ratio <= 7.7 # '7.7%-8.3%': 7.7 <= ratio <= 8.3 # '>8.3%' : ratio > 8.3 ``` ### Monthly Volume Calculation Compute per merchant per natural calendar month: ```python monthly_total = sum(eur_amount) # in euros # Map to fee rule category: # '<100k' : total < 100,000 # '100k-1m': 100,000 <= total <= 1,000,000 # '1m-5m' : 1,000,000 < total <= 5,000,000 # '>5m' : total > 5,000,000 ``` ### Month → day_of_year (2023, non-leap year) | Month | day_of_year range | |-------|-------------------| | January | 1–31 | | February | 32–59 | | March | 60–90 | | April | 91–120 | | May | 121–151 | | June | 152–181 | | July | 182–212 | | August | 213–243 | | September | 244–273 | | October | 274–304 | | November | 305–334 | | December | 335–365 | ### Intracountry Use `acquirer_country` directly from `payments.csv` (already resolved). Compare with `issuing_country`: ```python is_intracountry = (row['issuing_country'] == row['acquirer_country']) ``` --- ## Question Type 1: Rate-Change Delta **Question pattern**: "In [period] what delta would [merchant] pay if the relative fee of the fee with ID=[X] changed to [Y]?" ### CRITICAL: Do NOT Check First-Match for Rate-Change For Q1, **do NOT verify whether fee X is the first matching fee** for each transaction. The question asks for the direct impact of changing fee X's rate. Simply filter all transactions that match fee X's criteria (card_scheme, account_type, MCC, capture_delay, is_credit, aci, intracountry) and compute the delta. This is intentional — you are computing the delta on all transactions to which fee X's criteria apply. ### Step-by-step Algorithm ```python import json, pandas as pd # 1. Load fee rule with open('fees.json') as f: fees = json.load(f) fee = next(f for f in fees if f['ID'] == X) old_rate = fee['rate'] new_rate = Y # from question # 2. Load merchant properties with open('merchant_data.json') as f: merchants = json.load(f) merchant = next(m for m in merchants if m['merchant'] == MERCHANT_NAME) # 3. Load payments and filter by merchant + time period payments = pd.read_csv('payments.csv') mask = (payments['merchant'] == MERCHANT_NAME) # For a specific month: also filter day_of_year range # For a full year: no day_of_year filter needed # 4. Check merchant-level criteria (checked once, not per transaction) if fee['account_type'] and merchant['account_type'] not in fee['account_type']: print("Delta = 0 (merchant does not match fee's account_type)") elif fee['merchant_category_code'] and merchant['merchant_category_code'] not in fee['merchant_category_code']: print("Delta = 0 (merchant does not match fee's MCC)") elif fee['capture_delay'] is not None and not capture_delay_matches(merchant['capture_delay'], fee['capture_delay']): print("Delta = 0 (merchant does not match fee's capture_delay)") else: # 5. Filter transactions by fee's transaction-level 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']: # non-empty list mask &= payments['aci'].isin(fee['aci']) if fee['intracountry'] is not None: if fee['intracountry'] == 1.0: mask &= (payments['issuing_country'] == payments['acquirer_country']) else: mask &= (payments['issuing_country'] != payments['acquirer_country']) # 6. Handle monthly conditions (if fee has monthly_fraud_level or monthly_volume) # If both are null: just sum all matching transactions # If either is non-null: must filter month by month matching = payments[mask] total_amount = matching['eur_amount'].sum() delta = (new_rate - old_rate) * total_amount / 10000 print(f"{delta:.14f}") ``` ### Important: Monthly Conditions for Rate-Change When the fee has `monthly_fraud_level` or `monthly_volume` != null, these are **merchant-level, month-level** conditions. For a single-month query, calculate for that month. For a full-year query, the fee may apply in some months but not others — filter transactions month-by-month. --- ## Question Type 2: Affected Merchants (Account Type Change) **Question pattern**: "During 2023, imagine if the Fee with ID [X] was only applied to account type [T], which merchants would have been affected by this change?" ### Interpretation "Affected" = merchants whose fee X application STATUS CHANGES: - Currently: if `account_type = []` (applies to all), ALL matching merchants use fee X - After change: only account_type T merchants use fee X - Affected = merchants whose current status ≠ new status AND who have transactions matched by other fee criteria ### Step-by-step Algorithm ```python # 1. Load fee rule fee = next(f for f in fees if f['ID'] == X) # 2. For each merchant, check if fee status changes affected = [] for m in merchants: # Check merchant-level criteria (MCC, capture_delay) - excluding account_type if fee['merchant_category_code'] and m['merchant_category_code'] not in fee['merchant_category_code']: continue if fee['capture_delay'] is not None and not capture_delay_matches(m['capture_delay'], fee['capture_delay']): continue # Check if merchant has any matching transactions (transaction-level filters) m_payments = payments[payments['merchant'] == m['merchant']] mask = (m_payments['card_scheme'] == fee['card_scheme']) if fee['is_credit'] is not None: mask &= (m_payments['is_credit'] == fee['is_credit']) if fee['aci']: mask &= m_payments['aci'].isin(fee['aci']) if fee['intracountry'] is not None: if fee['intracountry'] == 1.0: mask &= (m_payments['issuing_country'] == m_payments['acquirer_country']) else: mask &= (m_payments['issuing_country'] != m_payments['acquirer_country']) if mask.sum() == 0: continue # No matching transactions # Check if account_type status changes currently_applies = not fee['account_type'] or m['account_type'] in fee['account_type'] would_apply = (m['account_type'] == T) # after the change if currently_applies != would_apply: affected.append(m['merchant']) print(', '.join(sorted(affected))) ``` --- ## Question Type 3: MCC-Change Delta **Question pattern**: "Imagine the merchant [M] had changed its MCC code to [NEW_MCC] before 2023 started, what amount delta will it have to pay in fees for the year 2023?" ### Algorithm For each transaction, find the first matching fee under the OLD MCC and NEW MCC separately. Delta = total_fees(new_mcc) - total_fees(old_mcc). **Write this as a single complete script — do not split into multiple exploration steps to avoid hitting turn limits.** ```python import json import pandas as pd # Load data 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') # Merchant info merchant = next(m for m in merchants if m['merchant'] == MERCHANT_NAME) old_mcc = merchant['merchant_category_code'] new_mcc = NEW_MCC # from question acct_type = merchant['account_type'] cap_delay = str(merchant['capture_delay']) # Filter transactions for this merchant df = payments[payments['merchant'] == MERCHANT_NAME].copy() 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(day): for m, (s, e) in MONTH_RANGES.items(): if s <= day <= e: return m return 12 df['month'] = df['day_of_year'].apply(day_to_month) # Precompute monthly fraud and volume categories def get_fraud_cat(ratio): if ratio < 7.2: return '<7.2%' if ratio <= 7.7: return '7.2%-7.7%' if ratio <= 8.3: return '7.7%-8.3%' return '>8.3%' def get_vol_cat(vol): if vol < 100000: return '<100k' if vol <= 1000000: return '100k-1m' if vol <= 5000000: return '1m-5m' return '>5m' monthly_cats = {} for month in range(1, 13): md = df[df['month'] == month]