The following tables include a sample of data-related questions and answers at various data levels, such as (but not limited to) accounts, logins, and transactions. These tables are not a comprehensive list of all the data-related questions that EVE can answer, but can help give you a starting point for additional questions.
Table 1. Sample: Accounts data-related questions and answers
Data point |
Question and answer |
---|---|
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 |
Table 2. Sample: Admin data-related questions and answers
Data point |
Question and answer |
---|---|
Customer |
Q) Who is the admin for a customer I am interested in? A) SELECT * from UserAdmin where customer_id = {customer id of interest} |
Table 3. Sample: CPFM data-related questions and answers
Data point |
Question and answer |
---|---|
DisclaimerAcceptance |
Q) When did a user sign up for CPFM? A) Using DisclaimerAcceptance - Select * from DisclaimerAcceptance where disclaimer_name like '%PFM%'. |
Table 4. Sample: Customer data-related questions and answers
Data point |
Question and answer |
---|---|
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. |
Table 5. Sample: DirectConnect data-related questions and answers
Data point |
Question and answer |
---|---|
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 |
Table 6. Sample: E-statements data-related questions and answers
Data point |
Question and answer |
---|---|
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'} |
Table 7. Sample: Q2 Goals data-related questions and answers
Data point |
Question and answer |
---|---|
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 |
Table 8. Sample: Login data-related questions and answers
Data point |
Question and answer |
---|---|
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 |
Table 9. Sample: Products data-related questions and answers
Data point |
Question and answer |
---|---|
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%’ |
Table 10. Sample: Themes data-related questions and answers
Data point |
Question and answer |
---|---|
Themes |
Q) Who has a Spanish theme? A) Select * from UserThemes where language = 'Spanish' |
Table 11. Sample: Transactions data-related questions and answers
Data point |
Question and answer |
---|---|
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 |
Table 12. Sample: User data-related questions and answers
Data point |
Question and answer |
---|---|
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. |