Full Answer Section

Assuming I have downloaded Equity Bank’s annual income statements for the last five years (2019-2023), an area of the bank’s income statement related to customer spending would be “Net Fee and Commission Income” or specific sub-categories under it.

• Net Fee and Commission Income: This line item primarily captures revenue generated from various services charged to customers. These fees are directly tied to customer banking activities and spending behaviors. Examples include:
  • Card-related fees: Debit/credit card transaction fees, ATM withdrawal fees, point-of-sale (POS) charges.
  • Account service fees: Monthly maintenance fees, ledger fees, transaction charges.
  • Mobile banking fees: M-Pesa or other mobile money transfer charges, bill payment fees.
  • Loan processing fees: Charges for originating loans (though this is more about borrowing than spending from accounts).
  • Forex commissions: Fees from currency exchange by customers.

An increase or decrease in these specific fee incomes over time can indicate shifts in how customers use the bank’s services and where they spend their money (e.g., more card usage, increased reliance on mobile banking, changes in loan uptake).

Data Points or Variables for Customer Spending Patterns (Last Six Months)

While the annual income statement provides a high-level view of the bank’s financial performance, it does not offer the granular detail needed for a “complete picture” of customer spending patterns over a six-month period. To achieve this, I would need access to internal transactional data. The key data points or variables would include:

1. Transaction Type:

   • Debit Card Transactions: Date, time, merchant category (e.g., retail, food, transport, entertainment), amount, location (if available).
   • Credit Card Transactions: Similar to debit, but also tracking credit utilization and repayment patterns.
   • Mobile Money Transfers (e.g., M-Pesa, Pesalink): Date, time, recipient (individual/business), amount, purpose (if coded, e.g., bill payment, merchant payment, peer-to-peer).
   • ATM Withdrawals: Date, time, amount, location.
   • Online/App Bill Payments: Date, biller category (e.g., utilities, internet, school fees), amount.
   • Direct Debits/Standing Orders: Regular payments for recurring services (rent, subscriptions, insurance).
   • Inter-bank Transfers: Amounts transferred to other banks.

2. Customer Demographics and Segmentation:

   • Account Type: Savings, current, business, salaried, student.
   • Age, Gender, Location (Residential/Employment): For demographic analysis.
   • Income Level: If available from account funding patterns or loan applications.
   • Customer Segment: Retail, SME, corporate.
   • Relationship Tenure: How long the customer has been with the bank.

3. Account Activity:

   • Average Daily/Monthly Balance: Indicates financial liquidity.
   • Number of Transactions per Month: Overall activity level.
   • Frequency of Digital Channel Usage: How often mobile app, internet banking are used.

By analyzing these granular data points, we can identify specific spending categories, preferred payment methods, peak spending times, geographical spending hotspots, and how these patterns vary across different customer segments.

Other Data Sources to Understand Customer Spending Patterns

In addition to the income statement (for macro-level trends in fee income) and internal transactional data, I would leverage several other crucial data sources:

1. Customer Relationship Management (CRM) System Data:

   • Purpose: Provides a comprehensive view of customer interactions, complaints, service requests, and communication history. This qualitative data can offer context to spending behaviors (e.g., a sudden increase in spending after a positive service experience, or a decrease after a dispute).
   • Specifics: Call center logs, email correspondence, in-branch visit notes, customer feedback surveys.

2. Credit Bureau Data (with appropriate consent and regulatory compliance):

   • Purpose: Offers insights into a customer’s broader financial health, creditworthiness, and borrowing habits across different lenders. This can indirectly inform spending capacity and financial stability.
   • Specifics: Credit scores, existing loan obligations (e.g., mortgages, personal loans from other institutions), payment histories with other creditors.

3. Market Research Data / Economic Indicators:

   • Purpose: Provides macro-economic context for customer spending.
   • Specifics: Inflation rates, GDP growth, unemployment rates, consumer confidence indices, sector-specific growth (e.g., retail, tourism). These external factors can explain broad shifts in spending that individual customer data alone cannot.
   • Bank-Specific Surveys: Conducting direct surveys with customers about their financial needs, spending priorities, and satisfaction with banking services.

4. Social Media and Web Analytics Data (for digital channels):

   • Purpose: Understand customer sentiment, emerging trends, and digital engagement with the bank.
   • Specifics: Mentions of the bank’s services, common complaints or praises on social media, website traffic patterns, app usage analytics.

Steps to Prepare All These Data Sources

Preparing diverse data sources for clear and accurate information is a critical phase, often involving several iterative steps:

1. Data Collection and Extraction:

   • Identify Sources: Pinpoint specific databases and systems (e.g., core banking system, ERP, CRM, mobile banking platform, data warehouses, Mergent Online).
   • Define Scope: Specify the timeframes (e.g., last six months for transactional, five years for income statement, ongoing for CRM) and data points required.
   • Extract Data: Use SQL queries for relational databases, API calls for integrated systems, or direct downloads from external platforms like Mergent Online. Ensure data is extracted in a consistent format (e.g., CSV, Excel, JSON).

2. Data Cleaning:

   • Handle Missing Values: Identify missing data points (e.g., null values in transaction amounts, missing merchant categories). Decide on imputation strategies (e.g., mean/median imputation, removal of rows/columns) or specific handling based on data type.
   • Remove Duplicates: Identify and remove duplicate records that could skew analysis.
   • Correct Inconsistencies: Standardize formats (e.g., date formats, currency codes), correct typos, and resolve conflicting entries (e.g., different spellings for the same merchant).
   • Address Outliers: Identify extreme values that might be data entry errors or legitimate but unusual events. Decide whether to remove, transform, or cap them based on analysis goals.

3. Data Transformation and Standardization:

   • Data Type Conversion: Ensure columns are in the correct data type (e.g., numbers for amounts, dates for transaction dates, strings for merchant names).
   • Categorization/Binning: Group similar data points into meaningful categories (e.g., categorizing various retail merchants into a “Retail” spending category; binning customer ages into age groups).
   • Feature Engineering: Create new variables from existing ones to enhance analysis (e.g., “transactions per month,” “average transaction value,” “percentage of digital transactions”).
   • Normalization/Scaling: If using certain statistical models, scale numerical data to a common range to prevent features with larger values from dominating.
   • Join Data Sources: Merge data from different systems (e.g., transactional data with CRM customer demographics) using common keys (e.g., customer ID, account number).

4. Data Validation and Quality Assurance:

   • Cross-Verification: Compare data from different sources where overlaps exist to ensure consistency (e.g., total fee income from aggregated transactional data matching income statement figures).
   • Logic Checks: Implement rules to identify illogical data (e.g., negative transaction amounts, future dates).
   • Summary Statistics: Generate summary statistics (counts, sums, averages, min/max) to spot anomalies or confirm expected distributions.
   • Stakeholder Review: Share preliminary processed data with subject matter experts (e.g., department heads) for their review and feedback on accuracy and completeness.

5. Data Storage and Accessibility:

   • Centralized Repository: Store the prepared, cleaned, and integrated data in a secure and accessible data warehouse or data lake.
   • Documentation: Create comprehensive metadata, data dictionaries, and documentation explaining variable definitions, data sources, cleaning steps, and transformation logic. This ensures future usability and maintainability.