Q2 Transacton Tagging
a promotion approach
What have we done
- Implemented rules in python with a test first approach
- Added support for ingesting a csv file
- Added database support with pyodbc
- Looked at credits for account 5000
f(x, y)
- x is the transaction
- y is the consideration set
- y is 90 days of transactions prior to x
- f(x, y) returns a tag of
- single
- recurringfixed
- recurringvariable
- infrequentfixed
Rules
- Recurring Fixed
- Within date and amount tolerance
- Recurring Variable
- Within date tolerance
- Infrequent Fixed
- Within amount tolerance
- Single
- None of the rules above apply
Date Tolerance: 15%
Amount Tolerance: 10%
Consideration Set: 90 days prior to transaction
Promotion
S
V
Legend
single
recurring variable
recurring fixed
infrequent fixed
F
I
- If promotion fails it falls back to previous promotion state
- If fail recurring variable promotion then attempt infrequent fixed promotion
Results for account 5000
Tag | Number of Occurrences | Percentage of Total |
---|---|---|
Single | 278 | 81.5 |
Recurring Variable | 53 | 15.5 |
Recurring Fixed | 9 | 2 |
Infrequent Fixed | 1 | .3 |
with 341 total credits
Query to retrieve transaction and consideration set
declare @txid bigint = 3297501
declare @accountid bigint = 5000
declare @interval datetime
select @interval = DATEADD(day, -90, (select postdate
from Q2_HostTransactionHistory
where transactionid=@txid))
select h.TransactionID, h.PostDate, h.txnAmount, t.tag
from Q2_HostAccount a
inner join Q2_Product p on a.ProductID = p.ProductID
inner join Q2_HostTransactionHistory h on a.HostAccountID = h.HostAccountID
left outer join Q2_HostTransactionHistoryTag t on h.TransactionID = t.TransactionID
where a.HostAccountID=@accountid
and h.DorC = 'C'
and h.TransactionID <= @txid
and PostDate >= @interval
order by h.PostDate
Example Recurring Variable
ID | Date | Amount |
---|---|---|
2745644 | 2010/03/31 | 2767.23 |
2745706 | 2010/04/30 | 2813.01 |
3189487 | 2010/05/28 | 2813.00 |
3297501 | 2010/06/29 | 300.00 |
Transaction ID: 3297501
15% Range: +- 4 Days from 29th
Example Recurring Fixed
ID | Date | Amount |
---|---|---|
3856647 | 2010/11/30 | 2795.05 |
4193202 | 2010/12/31 | 2830.67 |
4085552 | 2011/01/31 | 2865.05 |
4193202 | 2011/02/28 | 2625.60 |
Transaction ID: 4193202
15% Range: +- 4 Days from 28th
10% Range: 2363.04 to 2888.16
Example Infrequent Fixed
ID | Date | Amount |
---|---|---|
5676096 | 2012/02/15 | 2899.93 |
5734995 | 2012/02/29 | 2899.93 |
5808052 | 2012/03/15 | 2899.92 |
5874277 | 2012/03/30 | 2899.93 |
5893413 | 2012/04/03 | 2770.00 |
Transaction ID: 5893413
10% Range: 2493 to 3047
Example Single
ID | Date | Amount |
---|---|---|
5532610 | 2012/01/13 | 2899.92 |
5604289 | 2012/01/31 | 2899.93 |
5676096 | 2012/02/15 | 2899.93 |
5734995 | 2012/02/29 | 2899.93 |
580852 | 2012/03/15 | 2899.92 |
5874277 | 2012/03/30 | 2899.93 |
Transaction ID: 5874277
This is defined as single because there are only 2 occurrences of this transaction within the date tolerance
Observations
- Transactions tagged as single that should be recurring fixed because of the 90 day window ie Transaction ID 5874277
- Infrequent fixed and recurring variable are difficult to tease apart
Transacton Tagging
By mmann2943
Transacton Tagging
- 84