Reporting Analytics Queries

Related Topics

A standard set of queries reside in Reporting Analytics for each email campaign. The standard queries include some credit union-designated parameter prompts such as member special birthday age, past due loans number of days past due, new member follow-up number of days, and loan types to include in the number of loan payments remaining and past due loan queries. You can add filters to queries, but you cannot change the output file.

New Member Welcome Criteria

The New Member Welcome Reporting Analytics query contains the following parameters:

  • Member Number
  • Member Branch
  • Member Name
  • Join Date: This query is based on the member’s join date. If the member’s join date equals today’s month, date, and current year, the member will receive an email tomorrow.
  • Email Address: The system uses the first email address located on the Contact Information tab with the default checkbox selected for the campaign. If you do not select a default, and then the system uses the first email address located on the Contact Information tab.
  • Must have a valid email address (@sign required)
  • Select the E-Mail Messaging checkbox on the Stmts/Reporting/Notices tab.
  • Verify the Deceased Date field is blank. Do not include if you selected this checkbox.

New Member Follow-up Days Criteria

The New Member Follow-up Reporting Analytics query contains the following parameters:

  • Member Number
  • Member Branch
  • Member Name
  • Join Date
  • Credit union enters the number of follow-up days.
  • Join Date must equal follow-up date (join date + number of days entered = follow-up date).
  • Email Address: The first email address located on the Contact Information tab with the default checkbox selected is the email address used for the campaign. If you do not select a default, and then the system selects the first email address located on the Contact Information tab.
  • Must have a valid email address (@sign required).
  • Select the E-Mail Messaging checkbox located on the Stmts/Reporting/Notices tab.
  • Do not include the member if you selected the Deceased checkbox.
  • Verify the Deceased Date field is blank.

The Reporting Analytics prompt allows you to enter multiple follow-up days. For example, your credit union can send an email to the new member at 30, 60, 90 days etc. Enter the number of follow-up days in the text box and select Insert to add them to the list. Choose Select all to include all the follow-up day intervals in the New Member Follow-up query.

Email Messaging New Member Followup Days

Member Birthday/Member Special Birthday Criteria:

The Member Birthday Reporting Analytics query contains the following parameters:

  • Member Number
  • Member Branch
  • Member Name
  • Your credit union enters the member’s special birthday age.
  • The current month and day must equal the member’s birthday month and day.
  • Email Address: The first email address located on the Contact Information tab with the default checkbox selected is the email address used for the campaign. This occurs if you did not select a default, and you did not select the first email address located on the Contact Information tab.
  • The member must have a valid email address (@sign required).
  • Select the E-Mail Messaging checkbox located on the Stmts/Reporting/Notices tab.
  • Do not include if you selected the Deceased checkbox.
  • Verify the Deceased Date field is blank.

If a primary joint owner exists on the account and you list the joint owner’s email address as one of the three emails on the Contact Information tab, you can send a happy birthday email to an email address that does not belong to the name associated to the Birth Date field.

Member’s Credit Union Anniversary Day Campaign Criteria:

The Member’s Credit Union Anniversary Day Reporting Analytics query contains the following parameters:

  • Member Number
  • Member Branch
  • Member Name
  • Join Date: Verify the current month and day equals the member’s anniversary month and day.
  • Email Address: The first email address located on the Contact Information tab with the default checkbox selected is the email address used for the campaign. If you do not select a default, the system uses the first email address located on the Contact Information tab.
  • Verify the member's email address (@sign required).
  • Select the E-Mail Messaging checkbox on the Stmts/Reporting/Notices tab.
  • Verify the Deceased Date field is blank. Do not include if you selected this checkbox.

Dormant Member Campaign Criteria:

The Dormant Member Campaign Reporting Analytics query contains the following parameters:

  • Member Number
  • Member Name
  • Member Branch
  • Select the Dormant checkbox.
  • Verify the nonfinancial dormant date in Reporting Analytics is today’s date minus one.
  • Email Address: The first email address located on the Contact Information tab with the default checkbox selected is the email address used for the campaign. If you do not select a default, the system uses the first email address located on the Contact Information tab.
  • Verify the member's email address (@sign required).
  • Select the E-Mail Messaging checkbox located on the Stmts/Reporting/Notices tab.
  • Verify the Deceased Date field is blank. Do not include if you selected this checkbox.

The credit union must have the nonfinancial history database in Reporting Analytics to use the Dormant Member campaign.

Loan Payments Remaining Initial Load Campaign Criteria:

This query must run one day prior to the Loan Payments Remaining Daily query. The output file from this query updates the Loan Relational Table in SilverPop. The following day the Portico implementer must run the Loan Payments Remaining daily query.

The Loan Payments Remaining initial load query contains the following parameters:

  • The credit union selects which loan types to include in the query
  • Credit union enters the Number of Payments Remaining
  • Member Number
  • Member Branch
  • Member Name
  • Due Date
  • Note Number
  • Original Loan Date
  • Payment Amount
  • Your credit union selects the loan payment frequencies to be included in the campaign. You can include all frequencies or specific frequencies.
  • Your credit union enters the balance range.
  • Your credit union can select an option to Include Loans in Collections.
  • Your credit union can select an option to Include Delinquent Loans.
  • Bankruptcy Indicator - Do not include if Bankruptcy indicator is Y.
  • Charge Off Date - Do Not Include Charge Off Loans.
  • If the loan balance equals zero, do not include.
  • Email Address: The first email address located on the Contact Information tab with the default checkbox selected is the email address used for the campaign. This occurs if you do not select a default, and you do not select the first email address located on the Contact Information tab.
  • Select the E-Mail Messaging checkbox on the Stmts/Reporting/Notices tab.
  • Verify the Deceased Date field is blank. Do not include if you selected this checkbox.
  • Enter a credit union defined maximum past due days. This prompt only applies to the Loan Payments Remaining Initial Load. After the initial load, the system schedules the Loan Payments Remaining Daily query, which looks at the loans last payment amount and payment date, and if it greater than or equal to the scheduled payment amount, and the last payment date equals the current date minus one, include them in the output file as long as the loan meets all other query parameters.

Loan Payments Remaining Initial Load Prompt

Member Loan Payments Remaining Initial Load Campaign Criteria:

This query must run one day prior to the Member Loan Payments Remaining Daily query. The output file from this query updates the Member database in SilverPop. The following day the Portico implementer must run the Member Loan Payments Remaining daily query.

The Member Loan Payments Remaining Initial load query contains the following parameters:

  • Credit union enters the number of loan payments remaining
  • The credit union selects which loan types to include in the query
  • A credit union selects the loan payment frequencies to be included in the campaign. You can include all frequencies or specific frequencies.
  • Member Number
  • Member Branch
  • Member Name
  • Due Date
  • Note Number
  • Original Loan Date
  • Payment Amount
  • Credit union enters the balance range
  • Credit union can select an option to Include Loans in Collections
  • Credit union can select an option to Include Delinquent Loans
  • Bankruptcy Indicator - Do not include if Bankruptcy indicator is Y
  • Charge Off Date - Do Not Include Charge Off Loans
  • If the loan balance equals zero, do not include.
  • Email Address: The first email address located on the Contact Information tab with the default checkbox selected is the email address used for the campaign. If you do not select a default, the system selects the first email address located on the Contact Information tab.
  • Select the E-Mail Messaging checkbox on the Stmts/Reporting/Notices tab.
  • Verify the Deceased Date field is blank. Do not include if you selected this checkbox.

This query does not include a prompt for the number of days past due. The Loan Payments Remaining Initial Load query excluded the loans that are past due based on the credit union-defined number of days.

Member Loan Payments Remaining Initial Load Prompt

Loan Payments Remaining Daily Load Criteria:

The system schedules this daily query one day after the Loan Payments Remaining Initial Query, and looks at the loan last payment amount and payment date. If the last payment amount is greater than or equal to the scheduled payment amount, and the last payment date equals the current date minus one, the system includes the loan in the output file, as long as it meets all other query parameters.

The Loan Payments Remaining Daily Load query contains the following parameters:

  • Your credit union enters the number of loan payments remaining
  • Your credit union selects which loan types to include in the query
  • Your credit union selects the loan payment frequencies to include in the campaign. You can include all frequencies or specific frequencies.
  • Member Number
  • Member Branch
  • Member Name
  • Due Date
  • Note Number
  • Original Loan Date
  • Credit union can select an option to Include Loans in Collections
  • Credit union can select an option to Include Delinquent Loans
  • Credit union enters the balance range
  • If loan balance equals zero, do not include.
  • Bankruptcy Indicator - Do not include if Bankruptcy indicator is Y
  • Charge Off Date - Do Not Include Charge Off Loans
  • Email Address: The first email address located on the Contact Information tab with the default checkbox selected is the email address used for the campaign. If you do not select a default, the system selects the first email address located on the Contact Information tab.
  • Select the E-Mail Messaging checkbox on the Stmts/Reporting/Notices tab.
  • Verify the Deceased Date field is blank. Do not include if you selected this checkbox.

Member Loan Payments Remaining Daily Load Criteria:

The system schedules this daily query one day after the Member Loan Payments Remaining Initial Load Query. The output file from this query updates the member database in SilverPop.

The Member Loan Payments Remaining Daily Load query contains the following parameters:

  • Your credit union enters the number of loan payments remaining.
  • Your credit union selects which loan types to include in the query.
  • Your credit union selects the loan payment frequencies to include in the campaign. You can include all frequencies or specific frequencies.
  • Member Number
  • Member Branch
  • Member Name
  • Due Date
  • Note Number
  • Original Loan Date
  • Payment Amount
  • Credit union can select an option to Include Loans in Collections
  • Credit union can select an option to Include Delinquent Loans
  • Credit union enters the balance range
  • If the loan balance equals zero, do not include.
  • Bankruptcy Indicator - Do not include if Bankruptcy indicator is Y
  • Charge Off Date - Do Not Include Charge Off Loans
  • Email Address: The first email address located on the Contact Information tab with the default checkbox selected is the email address used for the campaign. If you do not select a default, the system selects the first email address located on the Contact Information tab.
  • Select the E-Mail Messaging checkbox located on the Stmts/Reporting/Notices tab.
  • Do not include, if you selected the Deceased checkbox.
  • Verify the Deceased Date field is blank.

Past Due Loan Campaign Criteria:

For the Past Due Loan campaign, the Reporting Analytics query contains the following parameters:

  • Member Number
  • Member Branch
  • Member Name
  • Email Address: If you specified an email address, and selected the default checkbox on the Contact Information tab, the system uses this email address used for the campaign. If you do not select a default, the system selects the first email address located on the Contact Information tab.
  • Specify a valid email address (@sign required).
  • Select the e-Mail Messaging checkbox located on the Stmts/Reporting/Notices tab.
  • Do not include if you select the Deceased checkbox.
  • Verify the Deceased Date field is blank.
  • Due Date.
  • Number of days past due
  • Note Number
  • Payment amount
  • Balance
  • Loan Type
  • Bankruptcy Indicator - Do not include if Bankruptcy indicator is Y
  • Credit union can select an option to Include Loans in Collections
  • Credit union can select an option to Include Delinquent Loans
  • Generate Notices Flag (optional parameter)
  • Charge Off Date - Do Not Include Charge Off Loans
  • Credit union can select an option to Include Co-Borrowers
  • Coborrower names 1 – 10
  • Coborrower number 1 – 10
  • Owner of Loan
  • For Type of account, specify L for Loan. The system excludes account types K – Credit Card, M – Mortgage, and E – Education.

If a loan’s due date is 10/13/14, and the number of days past due is 5, the system counts 10/13/14 as day 1. Instead, the system begins with 10/14/14 and adds 5 days which are 10/18/14, and send email on 10/18/14. The system does not send a past due email again until the payment frequency advances the due date. To receive a past due email, the loan’s due date must be past due by the credit union-defined number of days. If the past due number of days is 5, the loan must be exactly 5 days past due.

The system excludes vendor loans and credit cards from loan delinquency email messaging. Past Due Loan emails are independent of loan delinquency notices and do not replace the 440/441 Report notices, currently available as an e-notice.

Determining Owner of Loan and Co-Borrower Information

The primary name on the account may not be the owner of the loan; therefore, the three existing email addresses on the Contact Information tab may not belong to the owner of the loan. To determine the owner of the loan, the system looks for a borrower record. If a record does not exist, Portico assumes the primary name on the account is the owner of the loan and uses the first defaulted email address on the Contact Information tab. If you do not select a default email address, the system uses the first email address found on the Contact Information tab.

If a borrower record does exist, the system looks at the Owner checkbox.

  • If you do not select the Owner checkbox for any of the borrower/co-borrowers, the system assumes the primary name on the account is the owner of the loan and uses the selected email address on the Contact Information tab.
  • If you select the Owner checkbox for a borrower/co-borrower, the system uses the email address on the Borrower record.

The system uses the email address on the borrower record to send coborrower past-due email notifications.

Loan Borrower/Reference