Existing TE high-value opportunity base data set (Underutilized)

9/24 - See te_high_value_targets.sql in “High Value TE Targets” folder in data-analysis-desktop

• datalake_eft.center.centerid = datalake_eft.accounts.accountid (left join and checking for null returns nothing)

  • datalake.center gets you corporateid, dpiaccountpos, dpiaccount, centerid, and bamid
    Acceptance Criteria

Andrew Top Requirements:
Underutilized:

• Those with TE assets, but never boarded

  • TE Asset not blank and > 0, TE Status Never Signed Up

    • Since TE assets can be at account or child level we cannot clearly display and calculate the difference between total assets vs total potential

• Those with TE and boarded and have processed a minimum amount, or none at all

• Those who have used TE in the past, but are no longer using it (more than 30 days since last use?)

• PT 2.: Those processing a small amount comparitively thtrough TE. ACH and CC mix to see how many are charging with ACH

• Grain is Account, based on the “TE account” (not the software account), which is essentially the EFT.Centers table

• Indicator that Account has been boarded but has not processed within 90 days

  • There is a boolean in the sql above for this.

  • This should be te_latest_boarding_date in the last 90 days with a null latest_processing_date

  • Check that we don’t have tiny “test” transactions throwing off that latest_processing_date

• Indicator that Account has gone through training (optional, probably from a Salesforce Case)

  • Ask Mary Croy about this

• Total amount of Non-TE CC processing (center payments data), 0 if we have payment data but no CC processing, null if not available

  • Aggregate all Centers using same TE Account

  • This is in the sql above

• Total amount of Non-TE ACH processing (center payments data), 0 if we have payment data but no ACH processing, null if not available

  • Aggregate all Centers using same TE Account

  • This is in the sql above

• Indicator that Account that uses TE for ACH but has no CC processing

  • There are columns in the sql above for nonte amounts and booleans for whether they use te to process ACH and CC

• Indicator that Account processes multiple Centers under same TE merchant account

• Identify Accounts that are paying monthly TE maintenance fee but not processing (optional, no clue how to do this)

  • This might be one of them

Tech Notes

This data will go in Principal, as a new table called “account_te_stats”. Start with just a view though as you POC this, then I can help turn it into a table. Work out of data-principal repo.

I think most of this can be derived from existing data in principal, with “daily processing” being a key table, and “center payments” being the other one. The “account has gone through training” reach out to Mary Croy to figure out how to tell this. The “paying monthly TE maintenance fee” is another one I have no clue on. Start with Mary then expand to David Beckman.

To figure out multiple Centers under the same TE account, just count the number of Centers that share the same related_te_company_id and have a latest_processing_date within 90

Remember, that all of this is based on the “related_te_company_id” not where they get the software.