Payment Gateway Decline Analysis

[Anonymous]

I get a csv dump of Payment Gateway with thousands upon thousand of records weekly.  Here is a sample set of transactions Where James Smith has bought 5 different products / subscriptions. James has been a customer for two plus years and on his renewal on 5/1/2021 his card declined, The system tried to rerun automatically on 5/7/2021 and got declined again. Finally, John from Accounting manually got in touch with James, made the nessasary changes to the account and ran a successful transaction on on 5/10/20221. 

Date

User

Method

CC Type

Status

Transaction ID

Response Text

Customer ID

Subscription ID

First Name

Last Name

Product ID

Product Label

Product Fee

5/1/2021

System

Credit Card

Visa

Declined

9776

Do Not Honor.  Do Not Honor

5259

9135

James

Smith

19817

Medication Magazine

$        24.95

5/1/2021

System

Credit Card

Visa

Declined

9776

Do Not Honor.  Do Not Honor

5259

4717

James

Smith

19840

LifeStyle Magazine

$        64.00

5/1/2021

System

Credit Card

Visa

Declined

9776

Do Not Honor.  Do Not Honor

5259

3972

James

Smith

19871

Management Fee

$          9.00

5/1/2021

System

Credit Card

Visa

Declined

9776

Do Not Honor.  Do Not Honor

5259

7424

James

Smith

20025

Membership Fee

$          7.95

5/1/2021

System

Credit Card

Visa

Declined

9776

Do Not Honor.  Do Not Honor

5259

5800

James

Smith

21687

Health Magazine

$     179.00

5/7/2021

System

Credit Card

Visa

Declined

2908

Do Not Honor.  Do Not Honor

5259

9135

James

Smith

19817

Medication Magazine

$        24.95

5/7/2021

System

Credit Card

Visa

Declined

2908

Do Not Honor.  Do Not Honor

5259

4717

James

Smith

19840

LifeStyle Magazine

$        64.00

5/7/2021

System

Credit Card

Visa

Declined

2908

Do Not Honor.  Do Not Honor

5259

3972

James

Smith

19871

Management Fee

$          9.00

5/7/2021

System

Credit Card

Visa

Declined

2908

Do Not Honor.  Do Not Honor

5259

7424

James

Smith

20025

Membership Fee

$          7.95

5/7/2021

System

Credit Card

Visa

Declined

2908

Do Not Honor.  Do Not Honor

5259

5800

James

Smith

21687

Health Magazine

$     179.00

5/10/2021

John Accounting

Credit Card

Visa

Approved

4513

Transaction was Approved

5259

7424

James

Smith

20025

Membership Fee

$          7.95

5/10/2021

John Accounting

Credit Card

Visa

Approved

4513

Transaction was Approved

5259

9135

James

Smith

19817

Medication Magazine

$        24.95

5/10/2021

John Accounting

Credit Card

Visa

Approved

4513

Transaction was Approved

5259

5800

James

Smith

21687

Health Magazine

$     179.00

5/10/2021

John Accounting

Credit Card

Visa

Approved

4513

Transaction was Approved

5259

3972

James

Smith

19871

Management Fee

$          9.00

5/10/2021

John Accounting

Credit Card

Visa

Approved

4513

Transaction was Approved

5259

4717

James

Smith

19840

LifeStyle Magazine

$        64.00

Now what i need to show on a dashboard / BI report is 

• Ignore all of the prior 2 years of approved transaction data.
• Find the first decline i.e. 5/1/2021 (and the associated data: what products got declined, what customer go declined, what amount got declined?)
• Then show all followup activity until the account experienced an approved transaction i.e. show the second decline trasaction on 5/7/2021 and then the approved transaction on 5/10/2021 (and the associated data: how much got approved, did all products got approved, which user got the approval done--was it the system or John from Accounting that made teh approved transaction happen.
• Then show by users how much retention each user producted i.e. total approved transactions done by john after a decline has happened, total amount of these approved transactions.

As all of this is coming in one big csv dump that is being imported straight into BI. I dont know how to go about segmenting the issue and show a decline Analysis report. Please help! 

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Create following measures:

min date = CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Customer ID]=MAX('Table'[Customer ID])&&'Table'[Status]="Declined"))

max date = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Custom]=MAX('Table'[Custom])&&'Table'[Status]="Declined"))

_check = IF(MAX('Table'[Date])='Table'[min date],1,0)

_check2 = IF(MAX('Table'[Date])<='Table'[max date]&&MAX('Table'[Date])>='Table'[min date],1,0)

Total Amount = CALCULATE(SUM('Table'[Custom]),FILTER(ALL('Table'),'Table'[Customer ID]=MAX('Table'[Customer ID])&&'Table'[Date]=SELECTEDVALUE('Table'[Date])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Kelly,

This almost got me home. 

Now as the first table shows the first time the decline happened. How can i show (Grouping by Subscribtion ID)

• When the transaction got approved (after getting decline)-from the data we see the decline for subscription 3972 happened on 5/1/2021 while the succussful approved transaction happened on 5/10/2021 (9 days to approval).  
• How many attempts were made before getting approval. --We see 2 declines (5/1/2021 and 5/7/2021)

One thing to keep in mind is that each decline and approval are to be considered as a pair. So that if the following month the card declines again we can see this subscriber ID twice in the list, the first time for 5/1/2021 decline and then a 6/1/2021 decline (if applicable)