<aside> 💡
Project Statement:
Graduate admissions teams manage applications across multiple systems such as ERP, CRM, and document management platforms. During peak recruitment cycles, staff often face challenges identifying which applications are incomplete, where records are missing in the workflow, and which students require targeted follow-up communication.
Because data exists in different systems, it becomes difficult to quickly determine:
Without a clear operational view, staff may spend extra time manually reviewing records before sending communications, increasing the risk of delays or incorrect outreach.
</aside>
<aside> 💡
What I Solved:
This project simulates a graduate admissions operations workflow designed to improve data readiness and targeted outreach.
The solution included:
Workflow Monitoring
Document Readiness Tracking
Exceptions & Triage
Targeted Outreach Automation
</aside>
External Applications
ERP Admissions
CRM Slate
Documents On base
create or replace view public.vw_workflow_status as
select
e.application_id,
e.student_id,
e.program_code,
e.admit_term,
e.submission_date,
e.app_status,
case when p.application_id is null then 'NOT_IN_ERP' else 'IN_ERP' end as erp_status,
case when c.application_id is null then 'NOT_IN_CRM' else 'IN_CRM' end as crm_status
from public.grad_external_applications e
left join public.grad_erp_admissions p
on e.application_id = p.application_id
left join public.grad_crm_slate c
on e.application_id = c.application_id;
create or replace view public.vw_document_completeness as
with doc_flags as (
select
application_id,
max(case when document_type='Transcript' and received_flag='Yes' then 1 else 0 end) as has_transcript,
max(case when document_type='SOP' and received_flag='Yes' then 1 else 0 end) as has_sop
from public.grad_documents_onbase
group by application_id
)
select
application_id,
case when has_transcript=1 and has_sop=1 then 'Complete' else 'Incomplete' end as checklist_status,
has_transcript,
has_sop
from doc_flags;
create or replace view public.vw_ops_exceptions as
select
ws.application_id, ws.student_id, ws.program_code, ws.admit_term,
'ERP_LOAD_MISSING' as exception_type,
'Admissions Ops / Data Processing' as owner_team,
'Application exists externally but not loaded to ERP. Check SFTP job / load process.' as recommended_action
from public.vw_workflow_status ws
where ws.erp_status = 'NOT_IN_ERP'
union all
select
ws.application_id, ws.student_id, ws.program_code, ws.admit_term,
'CRM_RECORD_MISSING' as exception_type,
'CRM / Slate Team' as owner_team,
'Record exists in ERP but missing in CRM. Check export/query + SFTP import.' as recommended_action
from public.vw_workflow_status ws
where ws.erp_status = 'IN_ERP' and ws.crm_status = 'NOT_IN_CRM'
union all
select
d.application_id, null as student_id, null as program_code, null as admit_term,
'MISSING_REQUIRED_DOCS' as exception_type,
'Admissions Team' as owner_team,
'Transcript and/or SOP missing. Trigger student reminder, hold for review.' as recommended_action
from public.vw_document_completeness d
where d.checklist_status = 'Incomplete';
Output:
I created a workflow status view that compares external applications with ERP and CRM records. Instead of immediately labeling something as an error, it shows the stage each application has reached, which helps identify whether the issue is at intake, ERP load, or CRM sync.
Output:
The document completeness view simplifies multiple document rows into a single checklist status per application. It shows whether core requirements like transcript and SOP are complete so the admissions team can quickly identify which applications are ready for review and which need follow-up.
Output:
The exceptions feed brings together workflow stage and document readiness into a single operational list. Instead of reviewing multiple systems, the admissions team can quickly see which applications need action, whether it’s a system integration issue or a missing document follow-up.
3.1 Missing Email Ids
select application_id, student_id, first_name, last_name
from public.grad_external_applications
where email is null or email = '';
Output :
3.2 Duplicate Email Ids
select email, count(*) as email_count
from public.grad_external_applications
where email is not null and email <> ''
group by email
having count(*) > 1;
Output:
3.3 Duplicate ERP Rows
select application_id, count(*) as dup_count
from public.grad_erp_admissions
group by application_id
having count(*) > 1;
Output:
3.4 Applications with missing documents
select application_id, checklist_status, has_sop, has_transcript from vw_document_completeness
where checklist_status = 'Incomplete'
group by application_id, checklist_status, has_transcript, has_sop;