Impact Data Project
Siebert Williams Shank
My Role: Principal Consultant, Data Architect, Data Analyst, Data Designer
Project Duration: 4 Months
What was the context?
Siebert Williams Shank ("SWS") is a full service Investment Banking and Financial Services company. Its municipal finance department has underwritten and transacted on more than $1 trillion of municipal bonds on behalf of state and local governments, non-profit organizations, health systems, school districts, and more. Most people do not actually understand the significant benefit of municipal bond transactions to our society, so the firm was on a mission to be one of the first to quantify the real-world impact of its deal activity in tangible terms so that clients and the broader municipal community could better understand its contribution to the world.
What was the problem?
SWS's goal of creating and distributing an Impact Report in the first half of the year was beginning to feel out of reach. It became clear that it would be an extremely heavy lift that required the collection and management of large amounts of data not currently tracked by the Firm. The Marketing Director tasked with putting the report together did not have a quantitative background and was overwhelmed by the herculean task. Ultimately the Firm needed help with:
-
Determining the type of data they should collect
-
Developing an efficient method for collecting the data
-
Visualizing the data in a way that could serve a dual purpose and be leveraged by bankers in future proposals
-
Outlining a process to maintain the data moving forward so the report could be released annually
The Firm wanted to produce a high quality annual report to illustrate its impact on the community, but was completely unsure of how to go about it.
What was my approach?
After meeting with the Marketing Director and the other executive members of the SWS team in order to clarify their goals, I took the following steps:
1
Reasoned the most efficient method of bucketing the extensive deal list (literally thousands of deals) and determined the best quantifying factors for each bucket. For example, the impact of a school-related deal could be quantified by the number of students
2
Established the most reliable data collection source and formalized a process for gathering and cleaning the data we would need. I presented my strategy to the team and upon approval, continued my work
3
Led a weekly conversation with their marketing lead and any additional stakeholders as necessary, to provide status updates and talk through the roadblocks and challenges
4
Worked with their internal community engagement stakeholder groups to collect, clean and standardize employee volunteer hours and donation data. Designed a company survey to assist in collecting this data that could be used for this project and then annually to maintain this data long-term
5
Evaluated the best system to host their data with consideration for factors such as the data types they were working with, the lack of complex data relationships, the compressed timeline for getting the report done, and the desire for the team to use a familiar platform that could be easily maintained for years to come. Determined Power BI (with Excel as the data source) to be more than sufficient for their needs
6
Created a data model in Power BI that allowed related data types to be associated. I then built dynamic and user-friendly dashboard visualizations and graphics that could be leveraged in the report
7
Documented the full data strategy and collection methods in a formal report so the data and the dashboards could be maintained past the conclusion of the engagement. A sample of this report can be viewed here
PROJECT GALLERY*
*Please note, the gallery photos are for illustration purposes only and cannot be used or repurposed without expressed written consent.
 A sample of the dashboard built to visualize the Firm's ESG data |  The engagement survey designed to capture the volunteer and donation activity of employees |  An example of the tables built in Excel to serve as the data source for the Power BI Dashboard |
|---|---|---|
 A sample of the dashboard built to visualize the donation activity for the Firm's foundation |
What was the outcome?
Upon successful completion of the project, the Firm was able to release its first Impact Report to clients, which it displays on its main website. You can view the report here.