Forum Discussion
Payment Gateway Decline Analysis
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!
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,
KellyDid I answer your question? Mark my post as a solution!
2 Replies
v-kelly-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!
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)