Gift Cards Breakage Analysis & Forecasting

Gift Cards Breakage Analysis

## Breakage Analysis & Forecasting

This involves taking a life cycle of a card and calculating how much money is left on the card post expiry. 
The goal is to take ALL the cards where we get breakage and calculate the breakage. 
Based on the breakage from past years, we need the ability to “forecast” the breakage for future. 

Factors adding to the complexity but not limited to these are:
* Type of card - Open / Closed / Semi Closed Loop
* Categories of cards – fashion dining etc.
* Denomination of cards
* Volume of cards and their transactions
* Revenue calculation based on contract with Meta Bank and other contracts


### CARD
GiftCardID	MerchantID	CardCategory	 CardType	          CardValue	  ExpiryDate
GC1	        M1	         Dining	       Open Loop	         $20	      12-12-2020
GC2	        M1	         Recreation	   Closed Loop	       $10	      07-31-2020
GC3	        M2	         Spa/Saloon	   Semi Closed Loop	   $50	      07-27-2020
GC4	        M3	         Clothing	     Semi Closed Loop	   $200	      07-25-2020

### CUSTOMER LOG
Customer	Card	 PurchaseDate
C1	      GC1	   01-01-2020
C1	      GC2	   01-01-2020
C2	      GC2	   02-01-2020
C3	      GC3	   05-29-2020
C2	      GC4	   05-29-2020

### TRANSACTION LOG
TxID	Customer	Card	 Redeemed	Redemption Date
T1	  C1	      GC1	   $5	      05-01-2020
T2	  C1	      GC2	   $10	    07-01-2020
T3	  C2	      GC2	   $6	      07-25-2020
T4	  C2	      GC4	   $150	    07-25-2020

Status @ Today = Jul 28

### Current Status
Customer	Status
C1	      GC1: $15, GC2: $0
C2	      GC2: $4,  GC4: -$50 (Breakage)
C3	      GC3: -$50 (Breakage)