CONTACT

The Client

The client is a non-governmental organization who helped children suffering from critical illnesses to fulfil their wishes with the help of volunteers. The client wanted to analyse the data related to it, so as to get an overview and track the wishes, expenditures and incomes from various sources. There were consultants assigned in different places who were using reports to analyse it.

Service Offerings

Services Provided

Architecture Design
Backend Development
Frontend Development
Quality Assurance

Technology Stack

Microsoft SSIS

MSSQL

PowerBI

Business Scenario

The client had around 250 plus reports which were used by different business consultants in different areas. A frequent analysis were required to understand the statuses of the wishes, funds raised for the wishes, donors who were funding for the wishes. A proper tracking mechanism and an updated reporting facility was what the clients were looking forward.

Business Requirements

  • Analyse the different reports based on different regions.
  • Grouping of same kinds of reports and merging reports thereby decreasing the redundant reports.
  • To change the data sources from main to staging with relevant fields.
  • Integrate data for based on the usage.
  • Identify the budgeted and actual expenses.
  • Identify and segregate donors based on various factors.
  • Integrate data in different formats for analysis.
  • Drive the marketing and fund raising efforts more effectively.
  • Discover relationships that can help to develop specific incentives.
  • Accurately measure the performance of their activities, and help them tailor their processes towards better results.
  • Real-time tracking during crisis and optimizing relief efforts.

Our Solution

The measures taken to address these challenges were:

  • User authentication different users.
  • Restricting access based on a user’s need.
  • Recording data access histories and meeting other compliance regulations.
  • Proper use of encryption on data in-transit and at rest.
  • Merging relevant reports and providing proper access

Power BI was the suggestion and the preference of the client for the visualization of data based on their KPI’s. SSIS tool was used to integrate the data from different sources to extract, transform and load data to a new database structure.

We combined multiple sources of data to analyse all donors over multiple years. The data from different sources were collected in an ETL tool (SSIS) which helped in data warehousing and cleaning the unstructured data. Upon analysing donors’ responses, we developed distinct donor segments, providing a series of general and segment-specific opportunities to improve the organization’s marketing and fundraising effectiveness.

Challenges

  • There were multiple reports which needed to be analysed in detail. The business users were used to the reports which were used from beginning and was a challenge to satisfy them with relevant details they are looking for. There were duplicate data, incorrect data and descriptions manually entered by different users which has to be grouped based on relevance. Issues with the data Quality – user input errors, duplicate data and incorrect data linking.
  • Large Data management of unstructured data – Large growing data from different sources were available and integrating the same was a big challenge .The data was available from different internal systems. Most of the data was available as SQL but in different formats. Some data and reports were in XLS format as well. Integrating this data to a uniform format was critical.

Architecture

Results

  • The solution categorizes donors based on the denominations they usually donate and the mode of communication they usually respond to. Based on that they customized communication for those groups, and this helped to increase rate of retention of donors by 6-7% in last fiscal.
  • The data from different chapters and regions were integrated and analysis was given with different views and filters. They were also empowered to allocate funds based on insights from data thus use their resources optimally.
  • The campaign effectiveness report helped them identify the ideal time, place and channel of running an effective campaign for the best fundraising results.

How can we help you?

Get in touch with us to schedule a consultation