DataPalooza 2021

DataPalooza 2021

November 2 -4, 2021

@ www.csbs.org � @csbsnews

CONFERENCE OF STATE BANK SUPERVISORS 1129 20th Street NW / 9th Floor / Washington, DC 20036 / (202) 296-2840

Elizabeth Rychlinski DataPalooza Master of Ceremonies

Senior Data Analytics Specialist CSBS Team Member for 7 years Non ‐ bank Supervision

How to have the best experience

Use the Help Desk (anytime) 1

Watch the chat box (anytime) 2

Asks the Experts (12:30 ‐ 1:00 ET) 3

Let’s test the chat…

Use the Help Desk (anytime) 1

Watch the chat box (anytime) 2

Asks the Experts (12:30 ‐ 1:00 ET) 3

Tell me in the chat the significance of this number… 47

Answer

There are 47 states & territories participating in DataPalooza

Tell me in the chat…

321

Answer

There are 301 individuals registered for DataPalooza

“I’m an analytics super user”

Your fellow attendees

“I want to get my team more involved in using data”

“I’m just getting started”

DataPalooza: Can we boost your data journey? • Accelerate data culture • Choose data champions

Let’s test the chat one more time…

Use the Help Desk (anytime) 1

Watch the chat box (anytime) 2

Asks the Experts (12:30 ‐ 1:00 ET) 3

Tell me in the chat…

Number of Active MLOs for State ‐ Licensed Companies

For how many consecutive quarters has the number of Mortgage Loan Originators grown?

Thousands

2016

2017

2018

2019

2020

2021

Tell me in the chat…

Number of Active MLOs for State ‐ Licensed Companies

9 For how many consecutive quarters has the number of Mortgage Loan Originators grown?

100 120 140

0 20 40 60 80

Thousands

2016

2017

2018

2019

2020

2021

Tell me in the chat…

51% 21% 11%

State chartered banks provided what percentage of total PPP funding?

Tell me in the chat…

51%

State chartered banks provided what percentage of total PPP funding?

Kris Rowley Chief Data Officer CSBS Information Technology

Enterprise Data Platform

Objective – A technical platform to collect and manage data assets in support of analytical and decision ‐ making priorities.

Enterprise Data Platform

Objective – A technical platform to collect and manage data assets in support of analytical and decision ‐ making priorities.

Data Standards

Data Governance

Policy

Technical

Enterprise Data Platform

Objective – A technical platform to collect and manage data assets in support of analytical and decision ‐ making priorities.

Data Ingest/Access

Data Analytics

Data Standards

Data Transformation

Data Governance

Policy

Technical

Technical

Visual

Enterprise Data Platform

Objective – A technical platform to collect and manage data assets in support of analytical and decision ‐ making priorities.

Data Ingest/Access

Data Analytics

Data Standards

Decision Making

Data Transformation

Data Governance

Story Telling

Policy

Technical

Visual

Presentation

Technical

Visual

EDP Roadmap CSBS Priorities

1. Enhance and Expand on CSBS.org/data. 2. Migrate the NMLS analytical products to MicroStrategy. 3. Improve data distribution by understanding your needs and use cases. 4. Learn from your success! 5. Continuously looking to improve our analytical products and introducing emerging technologies where it makes sense.

EDP Security and Privacy

The EDP is being developed in partnership and with guidance from our Security team

We will develop an Enterprise Data Catalog

All of the EDP data will be classified based on the CSBS Information Classification Policy

We are in the process of developing “roles” to ensure the right people have access to the right data.

This Photo by Unknown Author is licensed under

Tom Harlow Executive Vice President CSBS Products & Solutions

Emil Phillips Senior Vice President CSBS Research & Analytics

Data Culture and Data Champions (November 2021)

Two things to remember… • Don’t let data analytics feel intimidating • Anyone can be a data champion

Benefits / Value of Data Analytics • Supervision • Exam prep • Surveillance & offsite monitoring • Engagement with supervised institutions • Risk-based resource allocation & exam planning • Policy analysis • Industry performance • What else?

How do you get there… • Identify a data champion[s] • Build a roadmap • Understand and manage change / the transition

Data Champion • Who are they? • Where should they sit in the organization? • What should they do? • Make a plan • Distill analytics • Share analytics with others

Roadmap • Value of roadmap • Take it slow • 30 – 60 minutes once a quarter • Internal discussions about how to leverage data • Training and education • Incorporating analytics into monitoring and examination protocols • Internal data, analytics, innovation working groups • Get involved with CSBS • Incorporate into new positions • Incorporate analytics into examiner trainings, other annual events

Sample Roadmap

• Identify data champion/owner •Connect with CSBS

Q122

•Data champion attend data working group

Q222

•Data champion takes training on one analytics tool •Data champion identifies internal analytics contacts

Q322

•Data champion organizes 1 ‐ hour overview of CSBS analytics products

Q422

Q123 •Data champion organizes 1 ‐ hour internal meeting to discuss analytics product overview and discuss use cases

•Examiners start using data analytics for exam preperation

Q223

Understand and manage change / the transition • Acknowledge that this is overwhelming • The importance of awareness – to understand why data analytics • Why are we changing • What’s in it for me • Understand and address resistance

• Leadership involvement / top-down approach • Reinforcement, reinforcement, reinforcement

How do you get there… • Identify a data champion[s] • Build a roadmap • Understand and manage change / the transition

Questions

Member Sponsors for the Networked Priorities (updated 11/2/21) Member Sponsors have been identified for Network Supervision priorities who serve as an advocate or “change champion” for the initiatives. This role actively communicates, supports and actively advocates for adoption of initiatives into their own and other agencies. They also partner directly with internal CSBS change teams and sponsors to lead and enable the achieve the results. Networked Priority Member Sponsors CSBS Change Practitioner

There will be a “One Company One Exam” for 75 MSB firms conducted and/or accepted by all states that regulate MSBs.

Kevin Hagler

Melissa Puccinelli

All states with licensing authority over money service businesses will adopt the Multi ‐ State MSB Licensing Agreement. *

Charlie Clark

Jeff DeArment

Albert Forkner – NMLS TBD ‐ MSB Model Law

Jeff DeArment

CSBS will build a modernized NMLS to one standard that implements the MSB Model Law and Multistate MSB Licensing Agreement. States will adopt operational and legal standards based on the MSB Model Law and Multistate MSB Licensing Agreement to participate in the modernized NMLS. CSBS will form an industry advisory group of MSB companies. –this is currently done. Its Just Lunch occurred in 2021, unless work needed in 2022 State regulators will pilot “One Company One Exam” for a mortgage company to reduce regulatory burden and create new efficiencies.

Melissa Puccinelli

Bret Afdahl

Margaret Liu

Melanie Hall

Janine Bjorn

State regulators will finalize and approve model regulatory prudential standards for nonbank mortgage servicers.

Charlie Clark

Janine Bjorn

Cyber ‐ risk examinations will be coordinated for nonbank entities to ease regulatory burden, provide more consistency, and enhance effectiveness. Participate in a community of large bank examiners to benefit from a more horizontal perspective regarding current practices and trends at large banks.

Tom Fite

Mike Bray

Lise Kruse

Joey Samowitz

1

Data Palooza Busting Buzzwords: De-Mystifying Data Terminology

Resources

https://www.merriam ‐ webster.com/dictionary/buzzword

Jargon

TLA Definition: Three letter acronym.

What buzzwords, jargon, or acronyms are you interested in learning about today? Link: _________________

Buzzword Buckets

Data Management and Analytics

Data Science

Technology and Programming

Tip #1

hairsplitting

Data Management and Analytics

 Structured data  Unstructured data

 ETL – Extract, Transform, Load  ELT – Extract, Load, Transform

Structured Data

Unstructured Data

ETL Software

Structured Data

Transform

Load

Unstructured Data

ELT Software

Extract

Load

Transform

Tip #2

Look for a definition, not the definition.

Data Management and Analytics

 Analytics  Descriptive Analytics  Predictive Analytics  Prescriptive Analytics

Analytics:

The process of finding insights in data.

Types of Analytics

 Descriptive Analytics: what has already happened?  Predictive Analytics: what may happen?  Prescriptive Analytics: what should we do?

Descriptive Analytics What has already happened

https://www.csbs.org/cbindex

Predictive Analytics – What may happen

Interested in Predictions?

Prescriptive Analytics – What should we do?

https://kanbanize.com/kanban ‐ resources/kanban ‐ analytics/monte ‐ carlo ‐ simulation

Technology and Programming

 Cloud computing  AWS/Azure/GCP  Software as a Service (SAAS)  API – Application Programming Interface

Tip #3: De ‐ mystifying a new term 1. What is a definition of it. 2. What bucket or category does this term fit into? 3. Is it in contrast to something else? 4. What is the purpose, goal, or value?

Example: Cloud computing 1. What is a definition? 2. What bucket or category does this term fit into? 3. Is it in contrast to something else? 4. What is the purpose, goal, or value? Running company data centers or local servers (“on premise”) Technology and Programming Using servers on the Internet to store data and run programs

Pay someone else to manage the hardware, only pay for what you use, quickly scale up

Cloud computing

Your Own Servers

Cloud Servers

This Photo by Unknown Author is licensed under CC BY ‐ SA

Amazon Web Services

(GCP)

https://www.parkmycloud.com/blog/aws ‐ vs ‐ azure ‐ vs ‐ google ‐ cloud ‐ market ‐ share/

Software as a Service (SAAS) Timecard Software

Your Own Servers

Cloud Servers

Software as a Service

API = Application Programming Interface

UI = User Interface

User Interface: Users Directly Access Your Application

Your Application

User Interface: Users Directly Access Your Application

Application Programming Interface (API):

Applications Access Your Application

Your App API

Applications

Application Programming Interface (API): Applications Access Your Application

API As A Data Source

Transform

Load

API Data

Data Science

Statistics

Data Science

Coding

Analytics

Artificial Intelligence (AI) & Machine Learning (ML)

https://towardsdatascience.com/cousins ‐ of ‐ artificial ‐ intelligence ‐ dda4edc27b55

Interested in AI & ML?

A Few of Your Buzzwords

Questions?

• What you think of the approach shared for handing buzzwords? • How do you handle jargon and new acronyms? • What additional buzzwords would you like to discuss?

Resources

https://towardsdatascience.com/

https://landing.umetrics.com/glossary ‐ data ‐ analytics ‐ terms https://www.statistics.com/data ‐ analytics/ Glossaries of statistics and data analytics terms:

Extra Slides

Types of Machine Learning

 Unsupervised  Supervised  Reinforcement  Transfer

Advanced Analytics terms

 NLP  Algorithm  Model  Regression  Classification  Neural Networks  Decision tree

Data Management

 Big data  Data mining  Data wrangling  Dashboards

Data Management

Data Management

 Data warehouse  Data lake  Business Intelligence

Cryptocurrency

 Blockchain  Bitcoin  NFT  Other currencies

Statistics

• Probability • Significance • Confidence • Prediction/Estimate/Inference/Forecast • Null hypothesis

Databases

 Table  Row  Column  View  SQL  Query

Other terms

 Report  Dashboard  Dossier

ELT Software

Structured Data

Extract

Load

Unstructured Data

Transform

Extract

Data Analytics Career Planning: Training and Certification

Presentation Outline

Workforce of Tomorrow

Training Opportunities

How to Get Certified

Questions

Workforce of Tomorrow Objective: Develop the workforce of tomorrow, becoming a training organization for state and federal regulators for the regulator of the future Strategies: 1. Study and identify the most important areas of focus needed to develop the workforce of tomorrow 2. Research and develop learning pathways to increase state regulators' level of competency in the identified areas of focus 3. Drive innovation to increase speed (or reduce time) to competency

1. Data Analytics 2. IT/Cybersecurity 3. Leadership Development Workforce of Tomorrow Areas of Focus:

Presentation Outline

Workforce of Tomorrow

Training Opportunities

How to Get Certified

Questions

Training Opportunities

Proficiency Levels: 1. Novice: Minimal knowledge, needs guidance, Has little or no idea of how to deal with complexity 2. Competent: Good working and background knowledge, Is able to achieve most tasks using own judgement, Copes with complex situations 3. Expert: Authoritative knowledge and deep tacit understanding, holistic grasp of complex situations, sees overall picture and alternative approaches.

Training Opportunities

Training Opportunities

Advanced Data Analytics Training

Presentation Outline

Workforce of Tomorrow

Training Opportunities

How to Get Certified

Questions

Certification

Certified Data Analytics Specialist (CDAS)

Certification Qualifications

Certified Data Analytics Specialist (CDAS) • Role Experience: Participation in at least five examinations during a minimum of one year of employment by a state regulatory agency. • Core Education: Examiners who meet the one-year employment requirement above are assumed to have adequate basic financial analysis training. • Active involvement in the development of data strategy. • Expertise in the area of data analytics must be outlined in the application.

Presentation Outline

Workforce of Tomorrow

Training Opportunities

How to Get Certified

Questions

Visit www.csbs.org/data for all training and certification information.

Certified Data Analytics Specialist | CDAS Your special skillset as a data analyst may be recognized through the CSBS Examiner Certification Program’s CDAS credential. CDAS offers two tracks for both examiners and non-examination staff: • Examination staff: one year on the job and participation in five examinations. Already CSBS-certified? No additional core education requirements. Apply today! • Non-examination staff: one year on the job providing value to the examination or off-site monitoring process. Completion of a CSBS Day 1 examiner training course is required (FDIC basic examiner education is acceptable alternative). To enroll in the Day 1 program, contact lmsadmin@csbs.org. All candidates must outline active involvement in the development of data strategy or data government for a state agency. Successful applicants must complete 40 hours of continuing education during each three-year term of certification. If you are already certified through an industry association, you may qualify to waive into the CDAS program. Contact certification@csbs.org for more information. To apply, activate an account in the certification portal – send your name, email address, and agency employment start date to certification@csbs.org to get started.

Data Palooza CSBS Analytics Platform Demo - An Introduction

Topics to cover during the demo:

Data Analytics landing page logging into Analytics Platform Introduction to the home page

      

Running reports Saving reports Changing filters

Navigation

DATA UPLOAD WORKSHOP

CONTENTS

Introduction................................................................................................................................................................... 2

Getting Started .............................................................................................................................................................. 2

Create New dossier....................................................................................................................................................2 Contents Panel........................................................................................................................................................... 3 Adding Existing Metrics .............................................................................................................................................3 Downloading External Data .......................................................................................................................................4

Upload Data................................................................................................................................................................... 5

New Dataset .............................................................................................................................................................. 5 Prepare ...................................................................................................................................................................... 6 Wrangle ..................................................................................................................................................................... 7

Creating a Visualization .................................................................................................................................................8

Editor ......................................................................................................................................................................... 8 Linking to Other Datasets ..........................................................................................................................................9

1 | P a g e

DATA UPLOAD WORKSHOP

INTRODUCTION

Here we will go over some of the basic concepts we will be going over in this user manual. In Figure 1 we have highlighted three important features for the lesson. 1. Contents – This section of your dossier is where you can control your chapters and pages. Each chapter contains its own filters that are applied to all pages and visualizations in that chapter. 2. Attributes provide a context to your groups of metrics. Bank name would be an example of an attribute. 3. Metrics are objects that represent business measures and key performance indicators. Total assets would be an example of a metric.

Figure 1 - Intro

GETTING STARTED

CREATE NEW DOSSIER To Start we will want to go to https://data.csbs.org/ and use

to login. Load up Bank Data and

start a new Self-Service Analytics dossier.

Figure 2 - Blank Dossier

2 | P a g e

DATA UPLOAD WORKSHOP

In today’s project we will focus on creating a basic grid and some advanced visualization functionality. Once you are logged in and have your new dossier started it should resemble the image above in Figure 2.

CONTENTS PANEL Dossiers are organized into chapters and pages. Each chapter can contain one or multiple pages. Each page can contain multiple visualizations. This is useful when narrating a story or by simply breaking up key metrics. Go ahead and add titles to your chapter and pages to easily refence them later. I would suggest doing this for each topic we cover. ADDING EXISTING METRICS To add metrics, click on the three dots located near Bank Data and Edit Dataset.

Figure 3 - Edit Dataset

In the top right of the Edit Dataset section there is a drop-down menu where one can switch the search form Attributes, Metrics, Public Object to Personal Object. Just below the drop-down is a search bar where you can type-in your key words to find the desired metrics. On the right side of the window are the attributes and metrics already in your dossier dataset. Add metrics to your dataset by double-clicking on the metric name or by dragging the metric into your dataset objects. After selecting the desired metrics update the dataset. For most of these sections we will use the following metrics: K447, 7414, and E004.

3 | P a g e

DATA UPLOAD WORKSHOP

Figure 4 - Edit Dataset II

DOWNLOADING EXTERNAL DATA Navigate on your browser to https://cdr.ffiec.gov/public and we will download two CSV files from this site. We will download 2018Q4 and 2019Q4 data for peer group 17. Under Report select “List of Banks in Peer Group Report”. Select 2018Q4 for the Report Date and for Peer Group choose 17. Search & save the data as a CSV. Repeat for 2019Q4. Please refer to figure 5 if you have any questions.

Figure 5 – FFIEC

4 | P a g e

DATA UPLOAD WORKSHOP

We will be downloading CSBS’ District CSV which is located at https://data.csb.org/resources/csbs_districts.csv. Eventually we will also need to copy and paste a chart from Wikipedia containing State Codes. The link is https://en.wikipedia.org/wiki/FIPS_state_code.

UPLOAD DATA

NEW DATASET Go ahead and start a new Dossier. Once it is created navigate to the top left of your screen and add a new dataset.

Figure 6 - New Data Set

Because we downloaded the data earlier and saved it, we can connect to our data via file from our disk. Select the top left option and select your saved 2018Q4 Peer 17 CSV file.

5 | P a g e

DATA UPLOAD WORKSHOP

Figure 7 - File from Disk

Once our file is selected click Prepare Data. This is how we will be able to clean our data, so it is ready to be analyzed. Our prepare window should look like figure 8.

Figure 8 - Prepare Window

PREPARE Our first step in preparing the data is to select FDIC Cert which is currently a metric and convert it to an Attribute. This will allow us to link it to our existing dataset later in the lesson.

6 | P a g e

DATA UPLOAD WORKSHOP

Figure 9 - Convert to Attribute

Now that FDIC Cert is an Attribute, we will click on the Wrangle option seen in figure 9.

WRANGLE Wrangle allow us to clean up the data we are working with before we move it into our dossier. Take notice that all the text data in column Name are in all CAPS. This doesn’t really follow the format form our existing dataset and looks messy. Pick the Name column and select the Title Case function. Title Case will make it so that each word starts with a capital letter but everything else is lower case. Next, we notice that the Name column still has a small error in it - “Of” is capitalized. To fix this select the Name column again and use the Find and Replace Function to replace “Of” with “of”. These are small fixes but it is still important to clean and prepare all of your data, so it is ready to be analyzed. Our final step for Wrangling the text data is fixing the column City. Like the Name column all the Cities are in all CAPS. Again, we’ll use the same Title Case function.

7 | P a g e

DATA UPLOAD WORKSHOP

Figure 10 – Wrangle

Now Apply the edits made and Finish the Preview.

CREATING A VISUALIZATION In our Datasets panel we can scroll down and eventually see the dataset we just added. While creating this visualization we will pull data from the existing dataset and the one we just added. EDITOR From our newly created data set add FDIC Cert and Name to the Rows. We will also want to add Net Income and Average Assets to the Metrics section. From our existing dataset we will add K447 which we should have added in the Getting Started section. Refer to figure 11 for finished Visualization.

8 | P a g e

DATA UPLOAD WORKSHOP

Figure 11 - Visualization

LINKING TO OTHER DATASETS Recall, when we were preparing our data, we changed FDIC Cert in our new dataset to an attribute. Now find that attribute, Right-click, and press “Link to Other Dataset…”. We will want to link it to Bank Cert from our existing dataset.

Figure 12 - Link to Other

When linked our grid will be updated to mostly blank cells. This is nothing to worry about. The datasets defaults to Outer Join which means that it combines all the rows from the two tables. The downside of this is some of the rows don’t match up and are returning blank cells.

9 | P a g e

DATA UPLOAD WORKSHOP

To fix this we will want to scroll up to our existing dataset. There should be the three dots by the name, see figure 13. We’ll want to click the dots and select the Inner Join option.

Figure 13 - Inner Join

Inner Join will combine all the rows with the same key records from each table. For our example the two datasets will be connected by Bank Cert/FDIC Cert.

Figure 14 – Outer & Inner Join

UPLOADING MULTIPLE DATASETS

CSBS + WIKI Here we will download the two documents discussed in the Downloading External Data Section. This is the CSBS District and State Fips form Wiki. Follow the steps in the Upload Data section for uploading a single file. Refer to

10 | P a g e

DATA UPLOAD WORKSHOP

Figure 7 and 8. Upload the CSBS District CSV and get to the prepare data section. As we see in Figure 16 there is an add a new table section on the top left of the preview window. We will want to click that an navigate to clipboard so we can paste our wiki data, see figure 15.

Figure 15 – Clipboard

Once done we will have our two datasets displayed in Figure 16.

Figure 16 - Preview

WRANGLING In our wiki data we will want to clean the text. Select Remove Row(s) Where Cell Is Empty and select Alpha Code. This will remove empty cells from our state data. See Figure 17.

11 | P a g e

DATA UPLOAD WORKSHOP

Figure 17 – Wrangling

ATTRIBUTE MAPPING & LINKING You can map imported attributes within a dataset to an existing attribute and its corresponding attribute forms. In our case it will be useful to link our two new datasets based on a common attribute. Note the attribute names might be different for you but you simply want to link State abbreviation in both datasets. You do so by clicking and dragging one to the other.

Figure 18 – Mapping

12 | P a g e

DATA UPLOAD WORKSHOP

We will then want to update the dataset and it will load into our dossier. Once loaded we will want to link our datasets. Let’s use State Name to Bank State.

Figure 19 - Attribute Linking

CREATING A VISUALIZATION (PAGE 2) Start off by making a new page in your dossier. We are going to create a new chart using the newly uploaded district data. EDITOR Add the following attributes to the rows section; District ID, District Desc, Bank State. In the metric section drag and drop Row Count and Count of Banks.

Figure 20

13 | P a g e

DATA UPLOAD WORKSHOP

ADDING EXISTING DATASETS Here we will focus on adding an existing dataset and explore the mapping functionality.

We will want to click on the Add Dataset button located near the top left of the dossier.

Select the Existing Dataset option and navigate to the following file location… Shared Reports >> Supporting Datasets >> Workshops >> Office locations Cube.

Figure 21

CREATING A VISUALIZATION (PAGE 3) In this visualization we will be preparing a grid containing latitude and longitude to plot on a map of the US.

EDITOR With the newly added Dataset add Latitude, Longitude, FDIC Cert., and Name to the Rows section. Then in the Metric section add 7414, E004, and Net Income. Refer to Adding Existing Metrics if you don’t have 7414 and E004. See Figure 22 as a reference.

14 | P a g e

DATA UPLOAD WORKSHOP

Figure 22

MAP In the Gallery section there is a build in option for a map. With our Visualization 1 select pick the map option. Now that our data is mapped navigate to the editor tab and drag Net Income to the color by section. Then move to the Format tab and select the color fill you would like to use. Our map will display Office locations colored by Net Income throughout the US.

15 | P a g e

DATA UPLOAD WORKSHOP

Figure 23 – Map

UPDATING DATASETS Recall how we downloaded peer group 17 for 2018 and 2019? Let’s see how to update this data. We will want to find our 2018 Data set in our current dossier. There are the 3 dots next to it and an option to republish cube. If we click this, we can do a file upload of 2019 data and because it is the same format everything will update.

Figure 24 - Updating

16 | P a g e

ADVANCED METRIC FUNCTIONS

CONTENTS Introduction................................................................................................................................................................... 3 Getting Started .............................................................................................................................................................. 3 Create New dossier....................................................................................................................................................3 Contents Panel........................................................................................................................................................... 4 Adding Existing Metrics .............................................................................................................................................4 DERIVED METRICS..........................................................................................................................................................5 Building a Visualization..............................................................................................................................................5 Editor ..................................................................................................................................................................... 5 Filter....................................................................................................................................................................... 6 Creating a Derived Metric..........................................................................................................................................7 Percent to Total .....................................................................................................................................................7 Metric Editor - Formula .........................................................................................................................................8 LEVEL METRICS .............................................................................................................................................................. 9 Building a Visualization..............................................................................................................................................9 Editor ..................................................................................................................................................................... 9 Filter.....................................................................................................................................................................10 Creating a Metric .....................................................................................................................................................11 Median.................................................................................................................................................................11 Difference ............................................................................................................................................................12 Abs .......................................................................................................................................................................13 Thresholds ...............................................................................................................................................................13 LAG METRIC FUNCTION .........................................................................................................................................14 Building a Visualization............................................................................................................................................14 Editor ...................................................................................................................................................................14 Filter.....................................................................................................................................................................15 Creating a Metric .....................................................................................................................................................15 Lag Metric Function .............................................................................................................................................16 Difference ............................................................................................................................................................17 CASE FUNCTION...........................................................................................................................................................18 Building a Visualization............................................................................................................................................18

1 | P a g e

ADVANCED METRIC FUNCTIONS

Editor ...................................................................................................................................................................18 Filter.....................................................................................................................................................................18 Creating an Attribute...............................................................................................................................................18 Case Function ......................................................................................................................................................18 Getting Started ............................................................................................................................................................20 Create New dossier..................................................................................................................................................20 Contents Panel.........................................................................................................................................................21 Adding Existing Metrics ...........................................................................................................................................21 Gallery......................................................................................................................................................................22 Individual Visualization Filtering..................................................................................................................................23 Creating a Visualization ...........................................................................................................................................23 Editor ...................................................................................................................................................................23 Filter.....................................................................................................................................................................24 Color By................................................................................................................................................................25 Targeting Visualization ................................................................................................................................................25 Targeting..................................................................................................................................................................25 Attribute Groups..........................................................................................................................................................28 Creating a Visualization ...........................................................................................................................................28 Attribute Groups......................................................................................................................................................28 Reference Line / Trend Line / Forecasting...................................................................................................................30 Reference Line .........................................................................................................................................................30 Editor & Filter ......................................................................................................................................................30 Plot.......................................................................................................................................................................30 Trend Line / Forecasting ..........................................................................................................................................32 Editor ...................................................................................................................................................................32 Plot.......................................................................................................................................................................32

2 | P a g e

ADVANCED METRIC FUNCTIONS

INTRODUCTION

Here we will go over some of the basic concepts we will be going over in this user manual. In Figure 1 we have highlighted three important features for the lesson. 1. Contents – This section of your dossier is where you can control your chapters and pages. Each chapter contains its own filters that are applied to all pages and visualizations in that chapter. 2. Attributes provide a context to your groups of metrics. Bank name would be an example of an attribute. 3. Metrics are objects that represent business measures and key performance indicators. Total assets would be an example of a metric.

Figure 1 - Intro

GETTING STARTED

CREATE NEW DOSSIER To Start we will want to go to https://data.csbs.org/ and use

to login. Load up Bank Data and

start a new Self-Service Analytics dossier.

3 | P a g e

ADVANCED METRIC FUNCTIONS

Figure 2 - Blank Dossier

In today’s project we will focus on creating a basic grid visualization and creating new metrics. Once you are logged in and have your new dossier started it should resemble the image above in Figure 2.

CONTENTS PANEL Dossiers are organized into chapters and pages. Each chapter can contain one or multiple pages. Each page can contain multiple visualizations. This is useful when narrating a story or by simply breaking up key metrics. I went ahead and added titles to my chapter and page 1 to easily refence them later. I would suggest doing this for each topic we cover. ADDING EXISTING METRICS To add metrics, click on the three dots located near Bank Data and Edit Dataset.

Figure 3 - Edit Dataset

In the top right of the Edit Dataset section there is a drop-down menu where one can switch the search form Attributes, Metrics, Public Object to Personal Object. Just below the drop-down is a search bar where you can type-in your key words to find the desired metrics. On the right side of the window are the attributes and metrics already in your dossier dataset. Add metrics to your dataset by double-clicking on the metric name or by dragging the metric into your dataset objects. After selecting the desired metrics update the dataset. For most of these sections we will use the following metrics: 5584, 5586, 5588, and E013.

4 | P a g e

ADVANCED METRIC FUNCTIONS

Figure 4 - Edit Dataset II

DERIVED METRICS

BUILDING A VISUALIZATION

EDITOR Start off by dragging over the Quarter and Bank State Chartered Attributes into the Rows. As you bring the data over, the Visualization 1 panel will populate. Next, let’s add the Count of Banks and Total Assets Metrics to the Metrics section of our Visualization. Please refer to Figure 5 if you need assistance.

5 | P a g e

ADVANCED METRIC FUNCTIONS

Figure 5 - Building A Visualization

FILTER Step 1: Toggle over to the Filter tab. We will now filter this chapter by Quarter and Bank State.

Step 2: Drag Quarter over to your filter panel. Once there change the display to Check Boxes and select a quarter. Step 3: Here we show the step by step process. Drag Bank State to the filter panel. Click the three dots on the right and change the display setting to Check Boxes.

Figure 6 – Filter

6 | P a g e

ADVANCED METRIC FUNCTIONS

Once done select a state. For this example, we used Alabama.

CREATING A DERIVED METRIC

PERCENT TO TOTAL Our Visualization panel now has the Quarter, Bank State Chartered, Count of Banks, and Total Assets.

Our next step will be creating our own user defined metrics – Percent to Total – which will show the percentages of State Chartered vs Not State Chartered Banks in our selected State. To do this, right click on the Count of Banks column header and navigate to Shortcut Metric >> Percent to Total >> Automatic.

Figure 7 - Shortcut Metric >> Percent to Total >> Automatic

Our new metric will be added to our visualization.

Let’s re-do the same steps but for Total Assets. Right Click Total Assets >> Shortcut Metric >> Percent to Total >> Automatic.

7 | P a g e

ADVANCED METRIC FUNCTIONS

METRIC EDITOR - FORMULA We are also able to create our own metric. To do so simply right click in the metric section of your dataset and select Create Metric (Figure 8). This will take you to the Metric Editor window. From this window we will want to switch over to the Formula Editor, located on the bottom left of Figure 9.

Figure 9 - Metric Editor

Figure 8 - Create Metric

In Figure 9, we show some of the operations available in the Metric Editor. Feel free to explore these features but for this demonstration we will focus on building our own formula. In Figure 10, we simply add 5584 Number of Loans To Finance Agricultural Production And Other Loans To Farmers Less than $100k + 5586 Number of Loans To Finance Agricultural Production And Other Loans To Farmers More than $100k to 250k + 5588 Number of Loans To Finance Agricultural Production And Other Loans To Farmers Greater than $250k to solve for Total Number of Loans for Agricultural Production. To simplify I will refer to them by their code: . = 5584 ( >100 ) + 5586 ( 100 −250 ) + 5588 ( <250 )

8 | P a g e

ADVANCED METRIC FUNCTIONS

If you are having trouble finding these metrics refer to the ADDING EXISTING METRICS section. Once finished our visualization panel should resemble the table below.

We will start this new section off by creating a new Chapter. Housing all our tutorials under one dossier will allow for easier refence later. Chapter 2 – Metrics will focus on Level Metrics. Level Metrics are metrics that are evaluated at a set level of data, regardless of what is contained on the dossier they’re placed in.

BUILDING A VISUALIZATION

EDITOR Start by pausing your data retrieval, located on the top left next to the save icon. This will allow us to drag and drop rows and columns without the visualization updating ever time. Once paused let’s start by dragging over Bank Cert, Bank Name, Bank State and Bank State Chartered Attributes into the Rows. Next, let’s add the E013 to the

9 | P a g e

ADVANCED METRIC FUNCTIONS

Metrics section of our Visualization. Then resume your data retrieval and Visualization 1 will populate. Please refer to Figure 11 if you need assistance.

Figure 11 - Building A Visualization

FILTER For this exercise we will use the filter panel to display only Q1 2019. We do this by adding quarters to the filter tab, changing the display to check boxes, and selecting 2019Q1 only.

Figure 12 – Filter

10 | P a g e

ADVANCED METRIC FUNCTIONS

CREATING A METRIC

MEDIAN Right click and create new metric. Once in the Metric Editor screen we will create a function that takes the Median of E013. We have addition options below our function - function parameters, levels, and filters. The leveling option is the most important part to take away from this exercise. Note: Any metric in the Dataset can be right clicked and you are able to edit the Number format to change decimals if need be.

Part 1 – Bank State Making our first level equal to Bank State this would take the median of E013 by each state. Each state would have a unique median, but our visualization would show the same median for State Chartered and Not State Chartered banks. To avoid this issue let’s add a second level. Part 2 – Bank State Chartered For our second level we add Bank State Chartered. Our return will now have a unique median for State by State Chartered vs Not State Chartered.

Figure 13 – Level Metric

11 | P a g e

ADVANCED METRIC FUNCTIONS

Figure 14 – Median Solution Chart

DIFFERENCE Now we will create a new derived metric called E013 Median Diff. In the formula bar simply solve E013 – Median E013 . The reason we are doing this is to see if any bank stands out from their competitors on a State and Bank Chartered Level.

Figure 15 – Difference Formula

12 | P a g e

ADVANCED METRIC FUNCTIONS

ABS To end this section, we will create the a new metric called Abs(E013 – Median E013). Recall, our latest function E013 Median Diff is just that. So, let’s take the absolute value of the diff we just calculated. Essentially, our sort will now find the largest differences from the median (positive or negative).

Figure 16 – Abs()

THRESHOLDS You can highlight metric data in a visualization by applying formatting to the visualization when data fulfills a specific condition. This formatting is called a threshold. Thresholds can make analyzing large amounts of data easier because images are easy to locate, and different colors are quickly identified. We will use thresholding to identify and large differences from E013 vs the Median. To do so right click on the newly created absolute function and select Threshold. Once in the Threshold menu you can change the colors, create new threshold levels and change the desired value. Once you have the settings to your liking select Apply and then OK. Now you will be able to visually see the differences.

Figure 17 – Threshold Menu

13 | P a g e

ADVANCED METRIC FUNCTIONS

Figure 18 – Largest Differences from Median

LAG METRIC FUNCTION Create a new chapter.

This section will focus on the lag metric function. The Lag function is useful to display a set of metric values in an order determined by another metric or attribute on the report. For our example we will use the lag function to find total assets last year and compare them to the current total assets.

BUILDING A VISUALIZATION

EDITOR Start by pausing your data retrieval. Once paused let’s start by dragging over Bank Cert, Bank Name, Bank State and Bank State Chartered Attributes into the Rows. Next, let’s add Total Assets to the Metrics section of our Visualization. Then resume your data retrieval and visualization 1 will populate. Please refer to Figure 19 if you need assistance.

14 | P a g e