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
Key	TenantId	VARCHAR(32)	Unique ID associated with each Quavo client.	ALWAYS	qvo-stg
Key	Tenant	VARCHAR(64)	A tenant is a Quavo customer that uses QFD. Each tenant is assigned a unique Tenant ID.	ALWAYS	Default
Key	ClientId	VARCHAR(64)	A unique ID representing a single client.	ALWAYS	Default
Key	Client	VARCHAR(64)	A client is a Quavo customer that uses QFD through a relationship with a Quavo tenant. Each client is assigned a unique Client ID.	ALWAYS	Default
Claim Details	ClaimType	VARCHAR(64)	See Transaction Type.	CONDITIONAL	Card-Pinless
Claim Details	ClaimCategory	VARCHAR(64)	General categorization of the claim. For a more precise description, see Claim Reason.	CONDITIONAL	Fraud
Claim Details	ClaimReason	VARCHAR(64)	Identifies the specific reason associated with the claim.	CONDITIONAL	Stolen
Compliance Details	RegulatoryCoverage	VARCHAR	Identifies the specific reason associated with the claim.	CONDITIONAL	Reg E
Claim Details	ClaimChannel	VARCHAR(32)	The Claim Channel defines the intake channel where the claim originated. This channel can include various avenues such as online banking, back office users, contact centers, or external 3rd party contact centers.	ALWAYS	Online Banking
Key	ClaimId	VARCHAR(32)	Unique ID associated with a claim. Format: YYMMDD<Ordinal Number>"C" Example: 2405230003C	ALWAYS	2306060014C
Claim Details	CreateDate	DATE	The datetime that a record was created.	CONDITIONAL	2023-09-09
Claim Details	ContactDate	DATE	Represents the date that the claim was received from the accountholder.	CONDITIONAL	2023-09-09
Claim Details	TotalClaimAmount	NUMBER(20,2)	The sum dispute amount of all pending and posted transactions included in a claim.	CONDITIONAL	123.45
Card Details	CardType	VARCHAR(16)	Bank Identification Number (BIN) is the first 4-8 digits of a payment card that identifies the issuing institution.	CONDITIONAL	Debit
Claim Details	Decision	VARCHAR(64)	"Paid" or "Denied". If one, or more, disputes on a claim are paid, the claim decision is "Paid".	CONDITIONAL	Paid, Denied
Claim Details	DiscoveryDate	DATE	The date provided during intake on a lost or stolen claim that represents the date the claimant discovered that the card had been lost or stolen.	CONDITIONAL	2023-09-09
Claim Details	InvestigationDate	TIMESTAMP_NTZ(9)	Date and/or datetime that a Pay or Deny decision is rendered for all disputes on a claim.	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)	Datetime that a claim or dispute is set to a "Resolved" status.. Also referred to as RESOLVEDDATETIME.	CONDITIONAL	2023-09-09 21:00:00
Risk Details	PotentialAbuse	BOOLEAN	A specified number of claims submitted by a single customer/member within an established timeframe. The output of this property is a boolean (TRUE/FALSE).	CONDITIONAL	FALSE
Risk Details	EmployeeClaim	BOOLEAN	A specified number of claims submitted by a single customer/member within an established timeframe. The output of this property is a boolean (TRUE/FALSE).	CONDITIONAL	false
Claim Details	PCDecision	VARCHAR(16)	The outcome of the initial interaction decision determined at claim submission. Possible values include AutoPay, Deny, Grant and Review.	CONDITIONAL	Grant
Claim Details	PCDate	TIMESTAMP_NTZ(9)	Datetime when provisional credit was granted on a claim.	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)	The date dispute status was applied to a dispute.	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.legalname,
    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.channelorigin,
    c.claimid,
    TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000'),
    c.customercontactdate,
    c.totalclaimamount,
    cs.state,
    c.status,
    c.cardbin,
    c.cardtype,
    c.decision,
    c.customerdiscoverydate,
    TO_TIMESTAMP_TZ(c.investigationcompletedatetime::varchar || ' +0000'),
    (CASE
        WHEN regethresholddatetime IS NOT NULL THEN regethresholddatetime
        ELSE regz90thresholddatetime
    END) AS ResolutionDeadline,
    TO_TIMESTAMP_TZ(c.resolveddatetime::varchar || ' +0000'),
    c.ispotentialabuse,
    c.isemployeeclaim,
    c.pcdecision,
    TO_TIMESTAMP_TZ(c.provisionalcreditdatetime::varchar || ' +0000'),
    c.reclassificationcount,
    c.requireddocumentcount,
    (CASE
        WHEN c.createdbyuserid= 'System' or c.createdbyuserid is NULL THEN 'System'  -- QPS-9986
        ELSE CONCAT(LEFT(u.firstname, 1), '. ', u.lastname)
    END) AS CreatedBy,
    (SELECT COUNT(*) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.status != 'Resolved-PostedDisputeCreated') AS DisputeCount,
    (SELECT SUM(d.disputeamount) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.status != 'Resolved-PostedDisputeCreated') AS DisputeAmount,
    IFNULL((SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = c.tenantid AND mc.claimid = c.claimid), 0) AS MerchantCreditAmount,
    IFNULL((SELECT SUM(d.merchanttotal) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND (d.status = 'Resolved-Paid' OR d.status = 'Resolved-Denied')), 0) * -1 AS RecoveredAmount,
    IFNULL((SELECT SUM(d.losstotal) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid), 0) * -1 AS LossAmount,
    to_date(t.golivedatetime) as TenantProductionDate
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
LEFT JOIN claim_state AS cs ON cs.status = c.status
LEFT JOIN userprofile as u ON u.tenantid = c.tenantid AND u.userid = c.createdbyuserid
ORDER BY
    c.createdatetime DESC

Change Log

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

Accountholder Satisfication View

Fields

SQL

Change Log