Name	Merchant_Collaboration
Type	View
Description	A normalized view of all merchant collaboration attempts and outcomes, based on the Recovery Action table, with additional claim and dispute attributes for filtering/analysis.

Data Category	Event Field/Column Name	Data Type	Description	Applicability	Sample Value	Notes

Data Category	Event Field/Column Name	Data Type	Applicability	Sample Value	Notes
Key	TenantId	VARCHAR	ALWAYS	qvo-stg
Key	Tenant	VARCHAR	ALWAYS	Default
Key	ClientId	VARCHAR	ALWAYS	Default
Key	Client	VARCHAR	ALWAYS	Default
Claim Details	ClaimType	VARCHAR	CONDITIONAL	Card-Pinless
Claim Details	ClaimCategory	VARCHAR	CONDITIONAL	Fraud
Claim Details	ClaimReason	VARCHAR	CONDITIONAL	Stolen
Transaction Details	Network	VARCHAR	CONDITIONAL	Mastercard, Visa
Key	ClaimId	VARCHAR	ALWAYS	2306060014C
Key	DisputeId	VARCHAR	ALWAYS	2306060014D
Collaboration Details	CollaborationNetwork	VARCHAR	ALWAYS	Ethoca
Transaction Details	MCC	VARCHAR	CONDITIONAL	3000
Transaction Details	Merchant	VARCHAR	CONDITIONAL	American Airlines
Dispute Details	ResolvedDate	Date	CONDITIONAL	2024-07-16 18:34:03.696
Collaboration Details	Eligibility	VARCHAR	ALWAYS	Eligible, Not Eligible
Collaboration Details	AttemptDate	Date	CONDITIONAL	2024-07-16 18:34:03.696
Collaboration Details	ResponseStatus	VARCHAR	CONDITIONAL	Response Received
Collaboration Details	Response	VARCHAR	CONDITIONAL	Collaboration Request Accepted - Full: Other - Refunded
Collaboration Details	ResponseReceivedOn	Date	CONDITIONAL	2024-07-16 18:34:03.696
Collaboration Details	ResponseTime	Number	CONDITIONAL	1	Hours
Collaboration Details	Outcome	VARCHAR	CONDITIONAL
Collaboration Details	RecoveryStatus	VARCHAR	CONDITIONAL	Recovered
Collaboration Details	CreditReceived	Number	CONDITIONAL	123.45
Collaboration Details	CreditReceivedOn	Date	CONDITIONAL	2024-07-16 18:34:03.696
Collaboration Details	CreditTime	Number	CONDITIONAL	1	Hours

SQL

For greater transparency, and to provide clients with a “jumping off” point for their own queries, the SQL used to generated this view is provided below.

(SELECT 
    d.tenantid AS TenantId,
    t.name AS Tenant,
    d.clientid AS ClientId,
    cl.commonname AS Client,
    c.claimtype,
    c.reasontype,
    c.reason,
    d.claimid AS ClaimID,
    d.disputeid AS DisputeID,
    (CASE
        when d.acquirernetwork = 'MasterCard' THEN 'Mastercard'
        when d.acquirernetwork = 'VISA' THEN 'Visa'
        else d.acquirernetwork
    END) AS Network,
    e.recoveryassociation AS CollaborationNetwork,
    d.merchantcategorycode AS MCC,
    d.merchantname AS Merchant,
    TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000')AS ResolvedDate,
    INITCAP(e.actionname) AS Eligibility,
    TO_TIMESTAMP_TZ(r1.performedondatetime::varchar || ' +0000')AS AttemptDate,
    (CASE
        WHEN r2.actionname IS NOT NULL THEN 'Response Received'
        WHEN AttemptDate IS NOT NULL THEN 'No Response'
    END) AS ResponseStatus,
    r2.actionname AS Response,
    TO_TIMESTAMP_TZ(r2.performedondatetime::varchar || ' +0000')AS ResponseDate,
    (CASE
        WHEN AttemptDate IS NOT NULL AND ResponseDate IS NOT NULL THEN DATEDIFF(h, AttemptDate, ResponseDate) -- only evaluate response time when there is an attempt and a response
    END) AS ResponseTime,
    (CASE
        WHEN AttemptDate IS NOT NULL and r2.actionname IS NULL THEN 'No Response'
        ELSE m.outcome
    END) AS CollaborationOutcome,
    (CASE
        WHEN CollaborationOutcome = 'Accepted' AND (SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid AND mc.appliedamount !=0) !=0 THEN 'Recovered' -- only associate credits with collaboration requests that are accepted.  For clients that use both networks, there will typically only be an "Accept" response via one of the two networks
        WHEN CollaborationOutcome = 'Accepted' THEN 'Not Recovered'
    END) AS RecoveryStatus,
    (CASE
        WHEN CollaborationOutcome = 'Accepted' THEN (SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid AND mc.appliedamount !=0) -- only associate credits with collaboration requests that are accepted.  For clients that use both networks, there will typically only be an "Accept" response via one of the two networks
    END) AS MerchantCreditAmount,
    (CASE
        WHEN CollaborationOutcome = 'Accepted' THEN (SELECT MAX(mc.eventdatetime) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid AND mc.appliedamount !=0) -- only associate credits with collaboration requests that are accepted.  For clients that use both networks, there will typically only be an "Accept" response via one of the two networks
    END) AS MerchantCreditDate,
    (CASE
        WHEN ResponseDate IS NOT NULL AND MerchantCreditDate IS NOT NULL THEN DATEDIFF(h, ResponseDate, MerchantCreditDate) -- only evaluate credit time if there was a response and a merchant credit
    END) AS CreditTime
FROM dispute AS d
JOIN (select row_number() over (partition by tenantid, disputeid, stage, actionname order by performedondatetime asc) as rn,* from recoveryaction) as e on e.tenantid = d.tenantid AND e.disputeid = d.disputeid AND e.recoveryassociation = 'Verifi' AND e.stage = 'Collaboration Eligibility' AND e.rn = 1
JOIN tenant AS t ON t.tenantid = d.tenantid
JOIN client AS cl ON cl.tenantid = d.tenantid AND cl.clientid = d.clientid
JOIN claim AS c ON c.tenantid = d.tenantid AND c.claimid = d.claimid
LEFT JOIN recoveryaction AS r1 ON r1.tenantid = d.tenantid AND r1.disputeid = d.disputeid AND r1.stage = 'Collaboration Initiated' AND r1.recoveryassociation = 'Verifi'
LEFT JOIN recoveryaction AS r2 ON r2.tenantid = d.tenantid AND r2.disputeid = d.disputeid AND r2.stage = 'Collaboration Response' AND r2.recoveryassociation = 'Verifi'
LEFT JOIN collaborationlookup AS m ON m.response = r2.actionname
ORDER BY d.tenantid ASC, d.claimid DESC, d.disputeid DESC)

Change Log

Date	Change Summary

Date	Change Summary
11/26/2024	Added TransactionDate column Added SQL Reference
12/11/2024	Added SQL Reference Converted timestamps from NTZ to TZ format for compatibility

QFD Knowledgebase

Merchant Collaboration View

SQL

Change Log