This analysis contains the following:

An Analytics Overview of the State,

the HIV Suppression Report,

the HIV Death Report,

and the HIV Treatment Adherence Report.

BRIEF OVERVIEW OF HIV/AIDS PROGRAM.

When a person tests positive for HIV, they must be enrolled in a treatment facility as soon as possible and adhere to treatment to achieve viral load suppression, typically less than 1000 c/ml. Adherence and viral suppression are two of the three PEPFAR 95-95-95 goals: 95% of people should be tested for HIV, 95% of those who test positive should enrol in treatment and adhere to it, and 95% of those on treatment should achieve viral suppression.

Patients are expected to have a blood sample taken for viral load testing 180 days after starting treatment, and then annually thereafter. However, children and adolescents are tested every six months due to their lower suppression rates. If a patient is unsuppressed, they are enrolled in the Enhanced Adherence Counseling (EAC) program to receive counselling and get their regimen optimized.

Patients are considered "Active" if they attend their last clinic appointment to pick up their drugs. When they miss their clinic appointments, they are flagged as "Missed Appointment ," and if they don't show up within 29 days, they are labelled "Lost To Follow Up" (LTFU), indicating non-adherence to treatment which is a red flag.

OBJECTIVE.

The objective of this project is to:

• Conduct a comprehensive analysis to identify trends and insights related to HIV in the state.

• Evaluate performance in adherence and viral load suppression.

• Identify factors that may be contributing to poor performance.

• Provide recommendations for improvement.

ANALYTICAL PROCESS

The data used for this analysis was initially in a csv file, which contained some errors, inconsistencies, and blank values. To clean and transform the data, I employed Power Query in a new Excel workbook and performed database normalization by dividing the data into a fact table and dimension tables.

During the data cleaning process, I used several techniques, including imputation of missing data in the Current Status Date column. Since the Current Status column contained data such as Active, Missed Appointment, LTFU, and Dead, it was important to accurately analyze the death trend. However, some of the Current Status Date values were missing for this analysis. To overcome this challenge, I used the Last Clinic Appointment date to fill in the missing values, which provided a more precise result when analyzing by year.

Additionally, I included conditional columns for Viral Load Status, such as Suppressed and Unsuppressed, and Eligibility Status for Viral Load Testing, which were identified as either ELIGIBLE or NOT ELIGIBLE.

To optimize the performance of the Power BI report, I chose to use Power Query in Excel instead of Power BI for the ETL process. This allowed me to connect to the data in the Excel workbook where I performed the ETL in Power Query, which is different from the original source data in csv file.

Finally, the data model in Power BI utilized a snowflake schema, which allowed for the connection of dimension tables to other dimension tables. For example, there was a many-to-one relationship from Facility_Dim to LGA_Dim.

Overall, this analytical process enabled me to obtain high-quality data, accurate analysis, and optimal report performance.

INSIGHTS FROM ANALYSIS.

The analysis examined data from 96,014 HIV patients located in Hilltop State, consisting of 34,972 males and 61,042 females. Each patient was enrolled between 2000 and 2022 in one of the 37 hospitals across 14 local government areas (LGAs).

Below are some key insights from the analysis:

• In Hiltop State, the overall suppression rate is 71%, with 67,992 patients suppressed and 4,033 patients unsuppressed.

• Females make up 70% of patients aged between 30-44, which is the largest age demographic.

• Although the trend of new HIV cases remained steady at 3,000-4,000 annually between 2008-2017, it spiked to 24,000 in 2021 due to a recent intervention program that ramped up HIV testing.

• The LGA with the highest number of patients is home to 15,817 individuals, with a 71% suppression rate and 1% death rate. This LGA has 5,903 male patients and 9,914 female patients across 3 hospitals.

• The best-performing LGA has an 86% suppression rate, while the worst-performing LGA has a 59% suppression rate.

• The best-performing hospital has a 92% suppression rate, while the worst-performing hospital has a 42% suppression rate.

• The eligibility for viral load testing reveals that 24% of suppressed patients, 91% of patients without documented viral loads, 65% of unsuppressed patients, and 30% of patients awaiting viral load results are eligible for further testing.

• Of the 3,270 deaths in the state, more than half occurred in patients between the ages of 40-59, and 19% of these patients were virally suppressed at the time of their death.

• From 2007 to 2019, the death rate in the state has been on the rise, peaking at 391 deaths in 2019.

• Three hospitals have the highest death rates, each at 8%. Additionally, 69% of the deceased patients never knew their viral load status.

• Currently, there are 68,000 active patients adhering to treatment in the state, with a 91% suppression rate. Three LGAs have the highest active rates, ranging from 86%-96%.

• 2,460 patients missed their last clinic appointment, with one LGA having a 13% miss rate. However, 87% of these patients are virally suppressed.

• There are 17,000 patients flagged as lost to follow-up, with three LGAs having the highest lost rates between 12%-21%. Of these patients, only 13% were virally suppressed in their last viral load test, and 74% do not have a documented viral load. This group of patients poses a significant risk in the spread of the virus.

RECOMMENDATIONS.

• Collect blood samples from those who are eligible for viral load testing, especially the 91% who don't have a documented viral load, to improve overall suppression rates. Test samples immediately to identify unsuppressed patients and enroll them into EAC with optimized regimens.

• Give special attention to LGAs and hospitals with poor suppression rates, as there seems to be a correlation between poor suppression, poor adherence, and death.

• Provide extreme care and support to unsuppressed patients to ensure they adhere strictly to treatment and always go for new viral load tests in due time to monitor suppression levels. This is particularly important for hospitals with high death rates.

• Reduce the rate of LTFU by directly contacting patients via phone calls to remind them of upcoming clinic appointments. Implement regular counselling and check-ins to encourage strict adherence to treatment, and use every means possible to reach out to lost patients to bring them back to active treatment.

By following these recommendations, healthcare providers in Hiltop State can improve suppression rates, reduce the risk of transmission, and ensure better outcomes for patients living with HIV.