Sample EVE data questions and answers

Account Data

Q) Who only has 'view' rights to an account?

A) Using Using UserAccountData – Select * from UserAccountData where access = 2

Account Data

Q) How many users have a "Holiday Checking Account"?

A) Using UserAccountData - Select count(user_id) from UserAccountData where product_name = ‘Holiday Checking Account’

Account Data

Q) Who has a closed account?

A) Select user_id from ClosedAccountData

Customer

Q) Who is the admin for a customer I am interested in?

A) SELECT * from UserAdmin where customer_id = {customer id of interest}

DisclaimerAcceptance

Q) When did a user sign up for CPFM?

A) Using DisclaimerAcceptance - Select * from DisclaimerAcceptance where disclaimer_name like '%PFM%'.

Customer Limits

Q) What is the daily limit for "Joe Smith"?

A) Using Customer GT Limits SELECT limit_per_day from CustomerGTLimits where customer_name like ‘%Joe Smith%’

Customer Limits

Q) How much of a customer's GT limit has been used?

A) SELECT aggregate_ach_credit_limit_per_day , gt_type, as_of_date from CustomerGTLimit where customer_name = ‘Customer name’ Order by as_of_date, gt_type This will give you a history of a customer’s limits and for what generated transaction types.

DirectConnect

Q) Who is logging in to Direct Connect?

A) Select * from DirectConnectLogin

DirectConnect

Q) Who is using Direct Connect to pay bills?

A) Select user_id from DirectConnectTransactions where is_bill_payment = 1

E-statements

Q) Who has signed up for e-statements?

A) Using UserAccountData - Select user_id from UserAccountData where estatement_registration_date is not null

E-statements

Q) How do I make a daily report of the number of people who have signed up for e-statements?

A) Using UserAccountData – Select count(user_id) from UserAccountData Group By estatement_registration_date

E-statements data

Q) Who has opted in to e-statements for a specific month?

A) Select user_id from EStatementData where opt_in = 1 and 'last_change_date' between '{first day of month of interest}' and '{last day of month interest}'

E-statements data

Q) Who has opted OUT of e-statements for a specific month?

A) Select user_id from EStatementData where opt-in = 0 and 'last_change_date' between '{first day of month of interest}' and '{last day of month of interest}'

E-statements data

Q) Who made changes to the e-statement preferences for a specific month?

A) Select modified_by_user_id where last_change_date between '{first day of month of interest}' and '{last day of month of interest'}

Goals

Q) Who has opened a Q2 Goals account?

A) Select user_id from GoalsData

Goals

Q) What is the average number of Q2 Goals accounts per user?

A) Select user_id, count(host_account_id) as count from GoalsData Group by user_id

Goals

Q) What is the average dollar amount in Q2 Goals accounts?

A) Select sum(current_balance)/count(host_account_id) from GoalsData

Suspect Logins

Q) How many suspect logins were scored by Q2 Sentinel and Q2 Patrol?

A) Using AuditScore extract SELECT count(AuditID) from AuditScore where score_description like “%Login%” and session_date = ‘date of interest’ and score = 0

Successful MFA logins

Q) How many successful MFA logins per day?

A) Using LogonAuthenticationDetail extract, select count(audit_id) where multi_factor_authentication_success = 1

CSR Assist Sessions

Q) How many times a day does a CSR assist in a login attempt?

A) SELECT count(audit_id) from LogonAuthenticationDetail where csr_assist_session = 1

Browser Version

Q) What browser is being used to access the application?

A) {'HTTP_USER_AGENT': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.90 Safari/537.36'}

Total Active Users

Q) How many active enrolled users are currently on the system?

A) Using LogonActivity, Select Max(session_date) Grouping by user_id where date > now-30

Device Type

Q) Which type of device are users logging in on by month?

A) Using LogonActivity, select count(user_id) grouping by ConvertToYearMonth(session_date), sessionanalysis_category = The count of logons grouped by devices used for each month.

Total Monthly Logins

Q) How many logins occur each month?

A) Using LogonActivity, select count(user_id) grouping by ConvertToYearMonth(session_date) = The logon events counted for each calendar month.

OS Type

Q) What operating system do users have?

A) {'HTTP_USER_AGENT': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.90 Safari/537.36'} Desktop

Logins

Q) What does EVE and AVSC not match on logins?

A) SELECT * from LogonActivity where session_analysis !=’Q2Monitor' and counts_as_a_login = 1

Product

Q) What are the names of the checking accounts that Q2 is aware we have?

A) Using ProductIDs - Select * from ProductIDs where product_name like ‘%Checking%’

Themes

Q) Who has a Spanish theme?

A) Select * from UserThemes where language = 'Spanish'

Batch

Q) How many single transactions are processed in a Payroll?

A) SELECT Count(detail_id) from GeneratedACHActyivity where transaction_id = {id of interest}

Text Banking

Q) How many generated transactions happen through text banking?

A) SELECT count(transaction_id) where channel_description like ‘TextBanking’ and processed_date between {date2} and {date1}

Templates

Q) How many generated transactions use templates?

A) SELECT count(transaction_id) where template_id is not null and processed_date between {date2} and {date1}

Suspect Authorized transactions by type

Q) How many GTs were scored fraudulent by Q2 Sentinel/Q2 Patrol?

A) Using AuditScore extract, SELECT count(AuditID) from AuditScore where score_description like “%Authorize%” Group by score_description

mRDC Amount of Deposit in Session

Q) What is the total of mRDC Deposits

A) Using RemoteDepositActivity, select (txn_amount) grouping by ConvertToYearMonth(deposit_date) = Will show the total monthly deposit amount for all deposits in that month

mRDC Deposits

Q) How many mobile deposits are created each month?

A) Using RemoteDepositActivity, select count(transaction_id) grouping by ConvertToyearMonth(deposit_date) = Will show the total amount of mobile deposits each month.

mRDC Users

Q) How many mRDC users are there?

A) Using RemoteDepositActivity, select user_id, max(deposit_date) grouping by user_id = Will return all users having created a remote deposit and the date of their last deposit.

ACH - Amount of ACH

Q) What was the amount of ACH executed by month?

A) Using GeneratedTransactionActivity, - Select sum(transaction_amount), avg(transaction_amount) grouping by ConvertToYearMonth(processed_date) where gt_type = ‘desired gt grouping’ = Would return the sum and average of the amount for the selected gt_type for each month.

Domestic Wires - Number of Companies

Q) How many companies executed a wire?

A) Using UserData and linking to GeneratedTransactionActivity by user_id -

Select count(customer_id) grouping by customer_id and gt_type and ConvertToYearMonth(processed_date) where gt_type = ‘desired gt grouping' = The count of customers having executed the desired GT type with the calendar month grouping.

Domestic Wires - Number of Wires

Q) What is the number of successful wires processed by month?

A) Using GeneratedTransactionActivity, select count(transaction_id) grouping by ConvertToYearMonth(processed_date) where gt_type = ‘desired gt grouping’ = Would return the count of the selected gt_type for each month.

Domestic Wires - Amount of Wires

Q) What was the amount of Domestic Wires executed by month?

A) Using GeneratedTransactionActivity, - Select sum(transaction_amount), avg(transaction_amount) grouping by ConvertToYearMonth(processed_date) where gt_type = ‘desired gt grouping’ = Would return the sum and average of the amount for the selected gt_type for each month.

ACH = Processed by Month

Q) How many ACH Transactions were processed by month?

A) Using GeneratedTransactionACHActivity linking GeneratedTransactionActivity by transaction_id, select count(detail_id), sum(amount), avg(amount) grouping by ConvertToYearMonth(processed_date) and gt_type = Would return the counts, and sum/avg of the amounts of all ACH type generated transactions grouped by gt_type and calendar month

mRDC Sessions with Deposit

Q) What is the average or count of mRDC Deposits

A) Using RemoteDepositActivity, select average\count(txn_amount) grouping by ConvertToYearMonth(deposit_date) = Will show the average monthly deposit amount for all deposits in that month

Transfers

Q) Which FIs are users transferring money from?

A) Select from_aba from GeneratedTransactionActivity

Transfers

Q) Which FIs are users transferring money to?

A) Select to_aba from GeneratedTransactionActivity

User - Date of User Enrollment

Q) What was the enrollment date of my active users?

A) Using UserData - Select created_date where deleted_date is blank

This query provides the date of the user was created in the system.