Good morning, afternoon or evening, everyone. Hello, my name is Mark. I’m a consultant here at Passerelle. Today for you, I have a discussion and a demo, we’ll be talking about the Oregon Super Bank. And this will be a fictional narrative about a fictional small to midsize bank, but it’s based on some non fictional experiences that we’ve had with our clients over the years.
We’ll be talking about the bank’s analytics platform, where it was and where it is now after teaming with Passerelle. And there will be a quick tech demo showing what the Data Rocket and dynamic ingestion can look like in Snowflake. And we’ll conclude with a presentation of a market share analysis that we developed, showing the bank exactly where prospects are for them. This is a neat analysis partly powered by the Data Marketplace in Snowflake and Equifax data.
So the bank believes that data-driven decisions and digital marketing capabilities are going to be important to growing its business, because FinTech and automated lending platforms have been a new and unique competitor to them. And so its objectives with its analytics tools would be to support digital transformation, digital marketing, and get insights on how to grow business and market share. Specifically, the first analysis that they’re looking for, is to understand their position in the regional home loan market.
So the bank already has a data warehouse from their core banking system, but it has its limitations. So the bank decided to team up with Passerelle and implement the Data Rocket. Data Rocket is an end-to-end architecture that modernizes the data infrastructure, and it puts industry best data technology in the hands of businesses of any size. And we unlock things like data ingestion, data, mastering third-party data, ML and AI applications. And it’s powered by a proven stack of Talend and Snowflake, with analytics by Power BI or Tableau.
In this analysis, we’re going to need components of data from the core banking system that you can see in the bottom left there, and then we’re going to need components from the third-party data share, we’re going to get everything into Snowflake where we’re going to do the transformation. And we’re going to do the blending. And then we’ll bring that into Tableau for the visualization.
What do we need for this regional market share, we’re going to need some sort of location identifier. In this case, let’s use zip code. And we’re going to need measures like counts of loans and loan amounts. And we’ll need ways to segment those loans based on length the loan type or the segmentation. And data is going to be needed for both the bank share and the total addressable market. So the bank share part is easy, they have the core banking system, and we’ve plugged into that with data that Data Rocket has brought into Snowflake.
That part is easy. What isn’t so easy, and is kind of fun to think about the challenges of what this looked like before Equifax is getting the total addressable market. And so maybe, maybe what you might have done before this is maybe you would have taken some census data, made some interpolations about wealth centers there, maybe you would have done some customer surveys, and hoped that you would get some customers with some of your competitors to answer some questions about themselves. Or you would have relationship managers to interview customers and get the results and then put it into a CRM. And then once it’s there, again, you’d be making a lot of interpolations and getting really variable results.
With Equifax, you get a standard product that is scientific and reliable. And so this product that I’m talking about from Equifax, is called the Credit Trends Data Set. And this is a suite of anonymous data at the zip code level – credit data, specifically attributed for trending market analysis, and benchmarking.
There are a lot of use cases for this and there are a lot of target audiences for this database. But we’re going to be focused on this green one up here, we’re going to be looking to directly measure the market. And of course, we’re not the first folks to use this. There’s a lot of success stories around this data set. But it’s hard to leave out that the Federal Reserve Bank has published research based on Equifax data. So this really is an important data set.
We’re going to get this data through a Snowflake Share. Snowflake data sharing is a very important feature of Snowflake- it makes data live and ready to query with no ETL pipelines to manage, and it gets you access to the most current data available. The Data Marketplace is where data providers make their data available to customers, and there are several ways you can share data. You can do it through the Marketplace or you can do direct share. And I love this – in reader accounts, you can share data with companies not yet on Snowflake, I just think that’s really cool.
So we understand where the data is, we understand how we’re going to get it, let’s, let’s take a dive in and see what it actually looks like. So we’re in Snowflake, right now, you can see that I’m logged in as the Super Bank admin. And we have access to four databases here. Let’s take a look at the metadata database. Because this is one, this is the database that’s driving the configuration for the Data Rocket using Talend to dynamically ingest several data objects. And this is the configuration table. But I have to tell you, this is part of our secret sauce. So this is really distilled down to, you know, a couple of core features, but it’s more or less as easy to configure.
So we have some source systems here and we have the core banking system and the FTP. And then we have two tables coming from the data warehouse at the core banking system. And then we have a file name for territory coming from the FTP. And so now that we’ve configured that, and the Data Rocket is running, the data is going to land in this staging database. And we have persistent and transient schemas for each system. And in the persistent data, for instance, for the FTP persistent, we can see this very simple territory dem, which is just a postal code to a territory name.
In the core banking, we can take a look at the accounts dim here. We can see on the account level, that’s where we’re going to get the postal code that’s going to be important. In the loan fact, we can see that this is where we can get information on the loan origination date, because we’re going to pick loans from a certain time window, and all of the different loan types and loan amounts.
Using all three of these sources, we’re going to relate this to the Equifax dataset, Credit Trends. So if we look at what the table looks like here, the table is going to be the mailing zip and the count of first lien mortgages. This count is limited to the second half of year 2020. So that’s where that origination date from the loan factor is going to come in. We’re going to have to pare it down to a specific time window here. We can see that the count of first lien mortgages, we have the average balance, and then we have some other segmentations of first lien mortgages greater than 500k. And then we also have one where the credit score was greater than 700, at the time of the application. And then we also have count of home equity line of credit. With all of the data in the Data Warehouse, we’re going to do all of our transformation. We have several views here, one, which just relates the accounts to the loans, the loans in the loan fact, we have another one, which is the aggregation of that data on the banks level. And then we have another view here, where that data is then combined with the Equifax data. If we look at this one, we can see that the data is not so analytics friendly, in that we get the database name, so it’s all capitals, it’s all underscores. But in our analytics schema, here we have, we have the loan market share, which is in you know, in friendly case, and all of the data in here is going to have friendly names ready for reporting purposes.
If we jump into Tableau, which is where we’ve connected this database, we can look at all of the clusters here, we can look at all zip codes for all of these different clusters from the territory. And what we’re looking at here is we’re looking at the aggregates based on liens, and HELOCs again for the second half of 2020. The postal code color here is attributed by the volume of market activity for each segment. So we have different sections here for the first lien and then different cuts for the first lien, which for this cut would be understanding what types of credit scores we’re getting for these first liens and then what type Have some value these first liens are worth. We have he locks down here.
So, up here though we see the aggregate market share for these different segments, and all clusters combined are first lien is at 11%. And our Home Equity Line of Credit business is at 37%. And that’s really very strong. So let’s just go through here and see if any of these different clusters are different from this average. So East Portland seems to have a stronger than average. Hillsborough is right around average. Salem is at 6%. And West Portland is at 11%.
For me, what sticks out is that Salem is 5% lower in terms of its market share across all of the clusters combined. And when I look at Salem, there’s really some interesting things here. Salem has a lot of first liens in it. If we hover over the zip code, we can get information about the market share, and then the actual counts that calculate it. So we can see, in 97304, there was 901st liens in that six month period, and the Super Bank only got 29 of them. And we can also see the average original balance of these first liens.
This right here, I think that this is a powerful insight that Salem is actually a hot market and the Super Bank is not involved in it as much as it could be. And there are some other good insights – I mean another 900 Count of first liens, we have almost 500 in this one. So that is a powerful insight that the super banks visibility, you know, digitally or on the ground in this area might need some might need some attention.
On the other side, when we look at the HELOC product, on average, you know, 37% across all of the geographies, that’s really strong. So in terms of the bank, if you’re going to be if you have the opportunity to present your narrative and your story to new customers, you may not need to be focused on the home equity line of credit products so much as you may want to talk about first liens and see if there are ways to connect with customers that are taking out their first mortgage for their first home purchase. So this concludes the demo. I hope you found this interesting and if you have any questions at all, please go and visit us on our website at pastoral data.com Thanks so much and have a great rest of your day.
