Skip to main content

Data Lake Integration

General Definitions & Concepts​

Receeve’s data model is event based. Each event in receeve is of a given type and is represented by the MESSAGETYPE column in the views below. For example, When a claim is created we emit an event of type event.claim.created and when a claim is resolved we emit an event of type event.claim.resolved . This is an important consideration to keep in mind when trying to structure the data provided in the views below in order to provide useful information to business users.

PS Reporting Views​

As a general rule, every view prefixed with PS_REPORTING contains event data around a specific isolated domain element in receeve. For example:

  • _PS_REPORTING_SMS contains event information about SMS.
  • _PS_REPORTING_LANDING_PAGE contains event information about Landing Page related events (including payments).
  • _PS_REPORTING_PROMISES contains event information about Promises to Pay created in the system.
  • etc.

It is important to note that not all receeve events are exposed via these views. Only a fixed small subset of relevant events. We might expand the event types that we expose in the future but we will keep the view schemas backwards compatible.

The views _PS_REPORTING_CLAIMS and _PS_REPORTING_OUTSOURCED are the only views that provide already joined data in order to facilitate the processing of information. In particular:

  • _PS_REPORTING_CLAIMS provides joined data with respect to the created and resolved events
  • _PS_REPORTING_OUTSOURCED provides joined data with respect to the outsourcing of claims (outsourced, closed and reverted events)

PS Event Views​

Views which are prefixed with PS_REPORTING are event view. Event views simply contain data from a specifc event and the entire payload. Each view is described more in detail in the following sections.

Joining Views​

The views below are designed so that one can join them in order to provide a more holistic view on certain business aspects of the information. Almost all views can be joined with _PS_REPORTING_CLAIMS via EXTERNALCLAIMREF or ACCOUNTREFERENCE. Simply keep in mind that an account be can linked to multiple claims . You can read more about accounts, claims and our core data model here.

The _PS_REPORTING_CONTENT, _PS_REPORTING_STRATEGIES, _PS_REPORTING_USERS contain data that can be joined via the following foreign keys respectively: TEMPLATEID, STRATEGYID, USERID . These provide more insights into specific details like for example the SMS template name used in an SMS sent to a debtor

By joining Views in multiple levels you can built a more useful data set. For example you can join claim data with the 2 strategy related views like this:

This would give you information about the latest strategy a claim was assigned to, including the strategy name (present in the PS_REPORTING_STRATEGIES view)

Common Properties​

The following columns appear in many tables below and are defined here once:

  • ACCOUNTREFERENCE: A unique reference that represents the account
  • EMITTIME: a timestamp representing the time the event was emitted by our system. It’s a unixtime in milliseconds.
  • EMITDATE: A date in the format YYYY-MM-DD
    • In general, any property wnding with DATE, will follow this format by convention
  • MESSAGETYPE: A string identifying the event type
  • EXTERNALCLAIMREF: A unique reference that represents the claim
  • EXTERNALDEBTORREF: A unique reference that represents the debtor
  • AMOUNT: The claim’s overdue amount
  • TOTALFEES: The claim’s fees

A Note on currency​

The currency of all monetary fields is the same currency that appears in the column currency of the view _PS_REPORTING_CLAIMS

A Note on NULL Values​

Every field that is not flagged as a primary key below is nullable.

PS Reporting Views​

_PS_REPORTING_CLAIMS​

  • ACCOUNTID
  • AMOUNT
  • DUEDATE
    • The Due date of the claim. This data is provided by the client.
  • EXTERNALCLAIMREF - UNIQUE PRIMARY KEY
  • EXTERNALDEBTORREF
  • LATESTCREATEDEMITDATE
    • The date of the latest event.claim.created event registered for that given claim
  • LATESTRESOLVEDEMITDATE
    • The date of the latest event.claim.resolved event registered for that given claim
  • PRODUCTREF
  • TOTALFEES
  • CURRENCY

Events (Message Types covered by this view)​

//Combines information from the events below into 1 claim per row

  • event.claim.created

  • event.claim.resolved

Example​

#clientIdexternalClaimRefproductRefaccountIdamounttotalFeesdueDatedebtorReflastCreatedEmitDatelastResolvedEmitDate
1a6e193eb---****-b8bc355eea3b63f9b4a4---****-76dfa8c4d4e0prod193b3d7ac---****-3c765052633a352.360.02023-11-07user1@example.com2023-11-082023-11-14
2a6e193eb---****-b8bc355eea3b5312fcd7---****-015e4596c550prod15d26fe5f---****-acacf348431076.350.02023-07-29user2@example.com2023-07-30null
3a6e193eb---****-b8bc355eea3bf63a3965---****-f40b501c6944prod2c669f019---****-bc75277335a2707.60.02024-02-21user3@example.com2024-02-212024-02-27

_PS_REPORTING_CONTENT​

  • DISPLAYNAME
  • TEMPLATEID - UNIQUE PRIMARY KEY

Events (Message Types covered by this view)​

  • event.communicationTemplateBundle.set

Example​

displaynametemplateid
SMS - DPD 0665869f3-d734-4*************d026f149

_PS_REPORTING_DEBTOR_DATA​

  • ADDRESSLINE1
  • BIRTHDAY
  • CITY
  • COMPANYNAME
  • COUNTRY
  • EMAIL
  • EXTERNALCLAIMREF
  • EXTERNALDEBTORREF - UNIQUE PRIMARY KEY
  • FIRSTNAME
  • LASTNAME
  • MOBILENUMBER
  • POSTALCODE
  • STATE
  • TYPE

Events (Message Types covered by this view)​

//Contains debtor data only related to the moment of the claim being created

  • event.claim.created

Example​

refIdemailaddressline1companynamepostalcodemobilenumberstatecitylastnamefirstnametypebirthdayexternaldebtorrefcountry
a6e193eb---****-b8bc355eea3bemail@gmail.comStreet No.1Company test06546+49127000000HHHamburgSmithJohnbusiness1978-03-01a6e193eb---****-b8bc355eea3bDE

_PS_REPORTING_EMAILS​

  • EMITDATE
  • EXTERNALCLAIMREF
  • MESSAGETYPE
  • REASON
    • A text value adding more details in a human readable format for all the events related to failures (*.failed)
  • TEMPLATEID
  • TEMPLATEPATH
  • MESSAGEID - UNIQUE PRIMARY KEY

Events (Message Types covered by this view)​

//One row is an instance of a message of one of the following types

  • event.debtorEmail.queued

  • event.emailWebhook.received

  • event.debtorEmailProcessing.failed

  • event.debtorEmailAddressInvalid.marked

  • event.clientEmail.queued

  • event.debtorEmail.clicked

  • event.clientEmailProcessing.failed

  • event.debtorEmailUnsubscribe.requested

  • event.clientEmailDelivery.interrupted

  • event.debtorEmailDelivery.failed

  • event.debtorEmail.delivered

  • event.debtorEmailDelivery.interrupted

  • event.debtorEmailComplaint.marked

  • event.clientEmailDelivery.failed

  • event.debtorEmailAddressValid.marked

  • event.clientEmail.delivered

  • event.debtorEmail.opened

Example​

messageTypeemitDateexternalClaimReftemplatePathtemplateIdreason
event.clientEmail.queued2024-04-2692371425-*********-32s3://cms-receeve-prod/*******/Operations/email/ContentTemplate/******91c90335_en-US.json9589dff7-8a1d-4*****991c90335_en-US

_PS_REPORTING_LANDING_PAGE​

  • EMITDATE
  • EXTERNALCLAIMREF
  • MESSAGETYPE
  • MESSAGEID - UNIQUE PRIMARY KEY

Events (Message Types covered by this view)​

//One row is an instance of a message of one of the following types

  • event.landingPageSession.started

  • event.landingPageCallback.selected

  • event.landingPagePostponePayment.selected

  • event.landingPageTextFeedback.submitted

  • event.landingPagePayLater.canceled

  • event.landingPageInstalment.confirmed

  • event.landingPageGetSetup.failed

  • event.landingPageVerification.failed

  • event.landingPagePayLater.interrupted

  • event.landingPageInstalmentsPlan.requested

  • event.landingPageGetVerificationFields.succeeded

  • event.landingPagePayment.disrupted

  • event.landingPageSetup.succeeded

  • event.landingPagePayLater.selected

  • event.landingPagePaymentProvider.selected

  • event.landingPageCallback.submitted

  • event.landingPageGetVerificationFields.failed

  • event.landingPageInstalment.selected

  • event.landingPageTextFeedback.selected

  • event.landingPagePayment.interrupted

  • event.landingPageVerification.succeeded

  • event.landingPagePayment.succeeded

  • event.landingPagePaymentInit.succeeded

  • event.landingPagePaymentInit.failed

  • event.landingPagePayment.failed

  • event.landingPageSupportOptions.viewed

  • event.landingPageLanguage.changed

  • event.landingPageSession.stopped

Example​

messageTypeemitDateexternalClaimRef
event.landingPageCallback.selected2024-04-307ede2a5c-***********8e4248-19

_PS_REPORTING_LAST_STRATEGY​

  • EXTERNALCLAIMREF -UNIQUE PRIMARY KEY
  • STRATEGYID
    • The unique ID of the strategy last assgined to the claim. This ID can be joined with table _PS_REPORTING_STRATEGIES.

Events (Message Types covered by this view)​

// This view looks at the latest occurance of the events below per claim. One row = one claim

  • event.strategyStep.started

Example​

externalClaimRefstrategyId
90fd152e-f405-4***************c9275d21665869f3-d734-4*************d026f149

_PS_REPORTING_LETTERS​

  • EMITDATE
  • EXTERNALCLAIMREF
  • MESSAGETYPE
  • REASON
  • TEMPLATEID
  • TEMPLATEPATH
  • MESSAGEID - UNIQUE PRIMARY KEY

Events (Message Types covered by this view)​

// One row per message of the following types

  • event.debtorLetterProcessing.failed

  • event.debtorLetter.queued

  • event.debtorLetter.delivered

Example​

messageTypeemitDateexternalClaimReftemplatePathtemplateId
event.debtorLetter.delivered2023-08-07DE34******************s3://cms-receeve-prod/59632f9e-d******efc2bde/Operations/letter/ContentTemplate/ee3**********d4673_en-US.jsonee3d5d0c-06*******8d4673_en-US

_PS_REPORTING_OUTSOURCED​

  • EXTERNALCLAIMREF - UNIQUE PRIMARY KEY
  • LATESTCLOSEDEMITDATE
    • latest registered date for the event of type event.partnerClaim.closed related to the claim in question
  • LATESTOUTSOURCEDEMITDATE
    • latest registered date for the event of type event.partnerClaim.outsourced related to the claim in question
  • LATESTREVERTEDEMITDATE
    • latest registered date for the event of type event.partnerClaimOutsource.reverted related to the claim in question

Events (Message Types covered by this view)​

//This view combines the outsourcing events below with claim creation events resulting in one claim per row

  • event.claim.created

  • event.partnerClaim.outsourced

  • event.partnerClaimOutsource.reverted

  • event.partnerClaim.closed

Example​

externalClaimReflastOutsourcedEmitDatelastClosedEmitDatelastRevertedEmitDate
DE4111010**********58412023-12-04

_PS_REPORTING_PROMISES​

  • AMOUNT
  • EMITDATE
  • EXTERNALCLAIMREF
  • MESSAGETYPE
  • MESSAGEID - UNIQUE PRIMARY KEY
  • USERID

Events (Message Types covered by this view)​

  • event.promiseToPay.created

  • event.promiseToPay.resolved

  • event.promiseToPay.expired

Example​

externalClaimRefamountuserIdemitDatemessageType
3019252-ac35d2**********-836484dc53ad26.923632304-572***********b9c5d6392023-12-18event.promiseToPay.created

_PS_REPORTING_SMS​

  • EMITDATE
  • EXTERNALCLAIMREF
  • MESSAGETYPE
  • REASON
  • TEMPLATEID
  • TEMPLATEPATH
  • MESSAGEID - UNIQUE PRIMARY KEY

Events (Message Types covered by this view)​

//One message per row of the following types

  • cmd.debtorSMS.send

  • event.debtorSMS.queued

  • event.debtorPhoneNumberInvalid.marked

  • event.debtorSMSDelivery.failed

  • event.debtorSMSDelivery.temporaryFailed

  • event.debtorSMS.delivered

  • cmd.debtorPhoneNumberValid.mark

  • query.settingSMS.get

  • cmd.settingSMS.set

  • cmd.debtorPhoneNumberInvalid.mark

  • event.debtorSMS.buffered

  • event.debtorSMSProcessing.failed

  • query.debtorSMSContactData.get

  • event.settingSMS.set

  • event.debtorPhoneNumberValid.marked

  • event.SMSWebhook.received

  • event.debtorSMS.sent

Example​

messageTypeemitDateexternalClaimReftemplatePathtemplateIdreason
event.debtorSMS.buffered2023-10-261957ae0c-94**************-7058ceecf1e3-53s3://cms-receeve-prod/3e1**************4b88-b17a-c8dd1eb33628/Operations/sms/ContentTemplate/ebab6f8f-**************e94510064f_en-US.jsonebab6f8f-4c5********10064f_en-US

_PS_REPORTING_STRATEGIES​

  • STRATEGYID -UNIQUE PRIMARY KEY
  • STRATEGYNAME

Description​

This table contains rows with backoffice strategy IDs and their corresponding names configured in the Backoffice.

Events (Message Types covered by this view)​

  • event.strategyBlueprintDisplayName.updated

Example​

strategyIdstrategyName
46c36021-3512-*********6-059b31621f8bPayment Failure

_PS_REPORTING_TASKS​

  • ACCOUNTID
  • EMITDATE
  • EXTERNALCLAIMREF
  • MESSAGETYPE
  • QUEUEID
    • The ID of the Queue (Queue created in Receeve’s backoffice) associated with the Task
  • RECIPIENTID
    • The ID of the User the task has been assigned to (can be joined with _PS_REPORTING_USERS.USERID)
  • TASKID
    • The unique ID of the Task in question.
  • MESSAGEID - UNIQUE PRIMARY KEY

Description​

This table contains rows with backoffice task information. Thes eare the tasks created by users or strategies and that are related to claims or accounts in the system.

Events (Message Types covered by this view)​

//Combines information of the below events into a single claim per row

  • event.caseManagementTask.created

  • event.caseManagementTask.resolved

Example​

taskIdclaimRefaccountIdrecipientIdemitDatemessageTypequeueId
39ad2bfb-3*************d43375ff0837faa4*************45-80a19a9a9f5606e7f62b-50f0-4*************55612c99062342343-50f0-4*************55612c992023-11-20event.caseManagementTask.created97fa646c-f37f-4*************ef93427810e

_PS_REPORTING_USERS​

  • EMAIL
  • USERID - UNIQUE PRIMARY KEY

Description​

This table contains rows with backoffice user IDs and their corresponding email addresses.

Events (Message Types covered by this view)​

  • event.backofficeClientUser.created

Example​

userIdemail
a265a061-2*********b-0c6ddd18b90fiv**********a@s***********.com

Event Views​

<CLIENT****>_CLAIM_META_UPDATED****​

  • ACCOUNTREFERENCE
  • EMITTIME
  • MESSAGEID - UNIQUE PRIMARY KEY
  • MESSAGETYPE
  • META
  • RECEEVECLAIMREF

Description​

This table contains the payload of every meta property upload of a claim

Events (Message Types covered by this view)​

  • event.claimMeta.updated

Example​

ACCOUNTREFERENCERECEEVECLAIMREFEMITTIMEMESSAGEIDMESSAGETYPEMETA
66112s3849-3535-3466112s3849-3535-34171123547566112s3849-3535-34event.claimMeta.updated{“metaprop1”:”val1”, “metaprop2”:”val12,}