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

• 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

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

Transaction ID: 3297501

15% Range: +- 4 Days from 29th

Example Recurring Fixed

Transaction ID: 4193202

15% Range: +- 4 Days from 28th

10% Range: 2363.04 to 2888.16    

Example Infrequent Fixed

Transaction ID: 5893413

10% Range: 2493 to 3047

Example Single

Transaction ID: 5874277

This is defined as single because there are only 2 occurrences of this transaction within the date tolerance

Observations

1.  Transactions tagged as single that should be recurring fixed because of the 90 day window ie Transaction ID 5874277
2. Infrequent fixed and recurring variable are difficult to tease apart