Yodlee ACH/NACHA Dashboard
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;