Credit card commissions are constantly shrinking. Small margins require careful planning and constant monitoring to keep business profitable and expand in the right direction. See how an OLAP model can provide analytics to respond to those needs.

Credit card operations pertain to two categories:

1. Issuing: distribute cards and enable card-holders to pay. Main revenues are yearly fee and interest rates eventually charged to card-holders (revolving for example) and part of interchange fee.

2. Acquiring: enable merchants to accept cards. Main revenues are commissions: usually a rate plus possibly a fixed fee per transaction. Specific rates and policies are applied to different merchants. Merchants dispose of one or more POS equipments to enter transactions.

When you make a payment with your credit card, two different entities are involved (as well as two different application system): the issuer (your bank) that provided you with the card, and the acquirer (supplier's bank) that authorized the merchant to charge it. The acquirer will charge to merchant a commission. Part of that commission will goes to brand (Visa, Mastercard...) and is called "Interchange Fee" and usually a small part of that interchange fee will return to issuer (your bank). In the event that issuer and acquirer are the same organization the incidence of interchange fee is very little if any). This situation (usually called “on us”) is obviously the ideal one.

The Need

The model shown on this post applies exclusively to application 2 (acquiring) and shall typically respond to questions of like these:

• How much are we growing our customer base?

• How much money and how many transactions are we handling?

• What is our profit on those transactions?

• How is our commission composed (fixed, variable) and distributed (interchange fee, costs, net)?

• What is the performance of our sales reps and their business units?

• How is our business and profit geographically distributed?

• What's the impact of client aging (time merchant is active) on risk?

• Which is the profitability of different market segments?

• What each client represents in terms of margin and risk?

• Which are main sources of transactions and profits (card brand, product, issuer and country)? This information is important to expand issuing network in the right direction.

The model

To support such dynamic inquiries users shall be able to dynamically combine multiple dimensions and measures. An OLAP model is a perfect fit. Here is a typical multidimensional model where arrow represent possible drill-down:

Apart the apparent complexity, that model can be implemented by a single star schema.

Loading data

Once your multidimensional model and star schema is ready, it is time to capture data.

Loading data is probably the most challenging task for this case. Credit card transaction volumes are huge and, even using column databases, you shall probably consider pre-aggregation.

Main source of data are transactions that shall be enriched with POS and merchant data, calendar data (festivities and recurring events are important to identify cycles), and product data (whatever you can get from a card number). This is the main load and you will possibly consume a good amount of resources to perform it, but it is a one-shot program:

In addition you can collect (and aggregate as profit/costs) many other expenses and usage of resources that will allow you to exactly compute the profitability of each merchant, for example:

• Setup cost

• Sales rep time spent at cost rate

• Travel expenses

• Case management

• Write-off

• Paid charge-backs

• POS rental fee

Retrieving ad hoc information

Once data has been loaded, enriched and possibly pre-aggregated you are ready to get the analytic information you need, as shown in the following screen-cast that uses PISA as self-service front end to provide users with ad-hoc information.