Yodlee ACH/NACHA Dashboard

Yodlee_GA4_ACH_NACHA_Website_Performance_Dashboard.pdf
Note: This is a interactive dashboard on Looker Studio that will change with controls like the data range. 

BigQuery (SQL) Application 

We had a conversion event named dev_register_account which looks at sessions that completed a form and indicated that they are a developer. The issue with the event was that it only triggered on the form page so we were not able to see what landing page led them to complete the event. 

We needed a query that only counted dev_register_accounts events that came from a specific group of landing pages like financial institution pages or ACH/NACHA pages.  

-- SessionsLps is a temp table of all the session Ids on with landing pages (page location + session_start). Only Financial Institution pages are being pulled.

WITH

 sessionLps AS (

   SELECT DISTINCT concat(user_pseudo_id,

     (

       SELECT value.int_value

       FROM unnest(event_params)

       WHERE KEY = 'ga_session_id' )) AS session_ids,

     (

       SELECT value.string_value

       FROM unnest(event_params)

       WHERE KEY = 'page_location' ) AS page_location

   FROM `e-paratext-352821.analytics_304319127.events_*`

   WHERE event_name = 'session_start'

   AND (_TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE) 

   AND regexp_contains((

       SELECT value.string_value

       FROM unnest(event_params)

        WHERE KEY = 'page_location'), "/ach|/account-verification|solutions.yodlee.com/account_validation_webinar_series.html|solutions.yodlee.com/NACHA_account_verification_webinar.html|nacha"

     )

 ),

--SessionsDevResgisterAccounts is a temp table that is pulling session IDs and page location only from sessions that completed the dev_register_account event

 sessionDevRegisterAccounts AS (

   SELECT DISTINCT concat(user_pseudo_id,

     (

       SELECT value.int_value

       FROM unnest(event_params)

       WHERE KEY = 'ga_session_id' )) AS session_ids,

     (

       SELECT value.string_value

       FROM unnest(event_params)

       WHERE KEY = 'page_location' ) AS page_location

   FROM `e-paratext-352821.analytics_304319127.events_*`

   WHERE event_name = 'dev_register_account' AND (_TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE)

)

-- We join the two temp tables (using session_ids) to count all the sessions with dev_register_account events on a financial Institution pages

SELECT COUNT(sessionLps.session_ids) AS Dev_Registration

FROM sessionLps

INNER JOIN sessionDevRegisterAccounts

ON sessionLps.session_ids = sessionDevRegisterAccounts.session_ids

Where sessionLps.session_ids IS NOT NULL;