Name	AHSat
Type	View
Description	Resolved claims and attributes for measuring accountholder satisfaction metrics, such as pay rate, autopay rate, PC rate, days to PC, days to resolution, reopen requests, reclassifications, and additional information requests.

Fields

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

Data Category	Event Field/Column Name	Data Type	Description	Applicability	Sample Value
Key	TenantId	VARCHAR(32)		ALWAYS	qvo-stg
Key	Tenant	VARCHAR(64)		ALWAYS	Default
Key	ClientId	VARCHAR(64)		ALWAYS	Default
Key	Client	VARCHAR(64)		ALWAYS	Default
Claim Details	ClaimType	VARCHAR(64)		CONDITIONAL	Card-Pinless
Claim Details	ClaimCategory	VARCHAR(64)		CONDITIONAL	Fraud
Claim Details	ClaimReason	VARCHAR(64)		CONDITIONAL	Stolen
Compliance Details	RegulatoryCoverage	VARCHAR		CONDITIONAL	Reg E
Claim Details	ClaimChannel	VARCHAR(32)		ALWAYS	Online Banking
Key	ClaimId	VARCHAR(32)		ALWAYS	2306060014C
Claim Details	CreateDate	DATE		CONDITIONAL	2023-09-09
Claim Details	ContactDate	DATE		CONDITIONAL	2023-09-09
Claim Details	TotalClaimAmount	NUMBER(20,2)		CONDITIONAL	123.45
Card Details	CardType	VARCHAR(16)		CONDITIONAL	Debit
Claim Details	Decision	VARCHAR(64)		CONDITIONAL	Paid, Denied
Claim Details	DiscoveryDate	DATE		CONDITIONAL	2023-09-09
Claim Details	InvestigationDate	TIMESTAMP_NTZ(9)		CONDITIONAL	2023-09-09 21:00:00
Claim Details	ResolutionDays	NUMBER	Number of days between claim creation date and a final decision being applied.	ALWAYS	10
Claim Details	ResolvedDate	TIMESTAMP_NTZ(9)		CONDITIONAL	2023-09-09 21:00:00
Risk Details	PotentialAbuse	BOOLEAN		CONDITIONAL	FALSE
Risk Details	EmployeeClaim	BOOLEAN		CONDITIONAL	false
Claim Details	PCDecision	VARCHAR(16)		CONDITIONAL	Grant
Claim Details	PCDate	TIMESTAMP_NTZ(9)		CONDITIONAL	2023-09-09 21:00:00
Claim Details	PCDays	NUMBER	Number of days between claim creation date and provisional credit execution date.	CONDITIONAL	10
Claim Details	DisputeStatusDate	TIMESTAMP_NTZ(9)		CONDITIONAL	2023-09-09 21:00:00
Claim Details	DisputeStatusDays	NUMBER	Number of days between claim creation date and the date when disputes were placed in dispute status.	CONDITIONAL	10
Claim Details	Reclassified	BOOLEAN	Indicates whether the claim was ever reclassified.	ALWAYS	TRUE, FALSE
Claim Details	PCWithheld	BOOLEAN	Indicates whether provisional credit was withheld by a user during claim intake.	ALWAYS	TRUE, FALSE
Claim Details	AutoPay	BOOLEAN	Indicates whether the claim was automatically paid during the initial interaction.	ALWAYS	TRUE, FALSE
Claim Details	ReopenRequested	BOOLEAN	Indicates whether one, or more, previously denied disputes on the claim had reopen requests made.	ALWAYS	TRUE, FALSE
Claim Details	RequiredDocuments	BOOLEAN	Indicates whether one, or more, documents were required from the accountholder.	ALWAYS	TRUE, FALSE
Claim Details	AdditionalInfoRequested	BOOLEAN	Indicates whether one, or more, requests for additional information were made on the claim.	ALWAYS	TRUE, FALSE

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
    c.tenantid,
    t.name,
    c.clientid,
    cl.commonname,
    c.claimtype,
    c.reasontype,
    c.reason,
    (CASE
        WHEN c.rege45datetime IS NOT NULL THEN 'Reg E'
        WHEN c.rege90datetime IS NOT NULL THEN 'Reg E'
        WHEN c.regz90datetime IS NOT NULL THEN 'Reg Z'
        WHEN c.isfcra = TRUE THEN 'FCRA'
        ELSE 'No Coverage'
    END) AS RegulatoryCoverage,
    c.cardtype,
    c.channelorigin AS ClaimChannel,
    c.claimid AS ClaimID,
    c.totalclaimamount AS TotalClaimAmount,
    c.customerdiscoverydate AS DiscoveryDate,
    c.customercontactdate AS ContactDate,
    TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000') AS CreateDate,
    TO_TIMESTAMP_TZ((SELECT MAX(executedon) FROM accounting AS a WHERE a.tenantid = c.tenantid AND a.claimid = c.claimid AND a.reason IN ('Provisional Credit'))::varchar || ' +0000') AS PCDate,
    DATEDIFF(d, c.createdatetime, PCDate) AS PCDays,
    TO_TIMESTAMP_TZ((SELECT MIN(executedondatetime) FROM disputestatusmaintenance AS a WHERE a.tenantid = c.tenantid AND a.claimid = c.claimid)::varchar || ' +0000') AS DisputeStatusDate,
    DATEDIFF(d, c.createdatetime, DisputeStatusDate) AS DisputeStatusDays,
    TO_TIMESTAMP_TZ(c.investigationcompletedatetime::varchar || ' +0000') AS InvestigationDate,
    DATEDIFF(d, c.customercontactdate, c.investigationcompletedatetime) AS ResolutionDays,
    TO_TIMESTAMP_TZ(c.resolveddatetime::varchar || ' +0000') AS ResolvedDate,
    c.ispotentialabuse,
    c.isemployeeclaim,
    (CASE WHEN c.status = 'Resolved-Denied' THEN 'Denied' ELSE 'Paid' END) AS Decision,
    c.pcdecision AS PCDecision,
    (CASE WHEN c.waspcmanuallywithheld = TRUE THEN TRUE END) AS PCWithheld,
    (CASE WHEN c.pcdecision = 'AutoPay' THEN TRUE END) AS AutoPay,
    (CASE WHEN c.reclassificationcount > 0 THEN TRUE END) AS Reclassified,
    (CASE WHEN EXISTS (SELECT 1 FROM reopenrequest AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid) THEN TRUE END) AS ReopenRequested,
    (CASE WHEN c.requireddocumentcount > 0 THEN TRUE END) AS RequiredDocuments,
    (CASE WHEN EXISTS (SELECT 1 FROM correspondence AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND purpose = 'Additional Information') THEN TRUE END) AS AdditionalInfoRequested,
FROM claim AS c
JOIN tenant AS t ON c.tenantid = t.tenantid
JOIN client AS cl ON c.tenantid = cl.tenantid AND c.clientid = cl.clientid
WHERE c.status IN ('Resolved-Paid', 'Resolved-Denied')
ORDER BY
    c.createdatetime DESC;

Change Log

Date	Change Summary

Date	Change Summary
12/26/2024	Added

QFD Knowledgebase

Accountholder Satisfication View

Fields

SQL

Change Log