Importance of Data Measurement and Tracking for Program Success

Measurement and Data 101 Measuring and Tracking your success 1

Outline Measuring and tracking your success Tools for measuring behavior change Benchmarking Common data challenges Software considerations AAHI Workshop: Measurement 2

Why should you measure? Take a moment to write down what you think are the 3 most important reasons you need to measure your programs AAHI Workshop: Measurement 3

Why you should measure Know how many services you provided and who you served Understand who your clients are Track progress against goals Know what is working well and what isn t Be able quantify your successes to others Have solid foundation for decision making Plan for the future AAHI Workshop: Measurement 4

Data collection versus Measurement AAHI Workshop: Measurement 5

What to measure? Identifying your measures: What are the key questions you need to be able to answer about your program? What will show you that your program has been successful or not SMART Measures AAHI Workshop: Measurement 6

What to measure? Outcome Process Balance Types of Measure Your ultimate goal Longer term How your program improves life for your clients Checks for unintended consequences Can be qualitative Can be long or short term Measures activities Shorter term Tracks the steps to the ultimate goal AAHI Workshop: Measurement 7

Crafting your measures Driver Diagrams Activity Measure Secondary Driver Activity Measure Measure Activity Measure Primary Driver GOAL Measure Activity Measure Measure Secondary Driver Activity Measure Measure Activity Measure AAHI Workshop: Measurement 8

Using Driver Diagrams Program Outline: The Silver Cloud Senior Center serves adults over 50 years old, providing a variety of social activities, exercise classes, a weekly shared meal, and education classes. They have about 250 people who regularly attend the center from a variety of backgrounds. They have heard from their clients and from other local sources that loneliness and isolation are problems in their community. They have recently allocated some funds to expand the number of adults that they serve and plan to add a second shared meal each week, as well as starting a weekly coffee and chat session. How would you populate their driver diagram and identify the measures that would show whether they met their primary goal? AAHI Workshop: Measurement 9

Driver Diagrams Activity Measure Secondary Driver Activity Measure Measure Activity Measure Primary Driver GOAL Measure Activity Measure Measure Secondary Driver Activity Measure Measure Activity Measure AAHI Workshop: Measurement 10

Driver Diagrams Total meal sessions per week Total individuals attending Total meals served Second weekly meal Increase number of adults participating in weekly meal Weekly coffee and chat Total coffee sessions per week Total individuals attending Total attendance # new adults attending weekly meals Reduce isolation and loneliness in the community Activity Measure Increase utilization of center by community Loneliness Survey Total surveys administered Total people surveyed Total people reporting loneliness Total number of new adults using center Decrease number of adults self- reporting feelings of loneliness Reduction in clients reporting loneliness Activity Measure # adults reporting feelings of loneliness in prior 14 days Balance Measures: Maintain attendance levels at other programs Total attendance at other programs Total attendance at the other meal session Activity Measure AAHI Workshop: Measurement 11

Data Needs Data Element Measure Total number of new adults using center Client information Name Does this tell your whole story what else might you need to be collecting to evaluate the program? # new adults attending weekly meals Unduplicated Date of Birth # adults reporting feelings of loneliness in prior 14 days New to Center Total meal sessions per week Attendance Records Total individuals attending Date Total meals served Activity Total coffee sessions per week Name of Attendee Total individuals attending Duplicated Total attendance Loneliness Survey Total surveys administered Date Total people surveyed Client name Total people reporting loneliness Question Answers Total attendance at other programs Total attendance at the other meal session AAHI Workshop: Measurement 12

Common data challenges and solutions Duplicated and unduplicated Missing data When your data doesn t add up Validation AAHI Workshop: Measurement 13

Counting clients and services Counting happens at multiple different levels Totals may be different for the same program depending on what is being asked for Household Individual Services Encounters AAHI Workshop: Measurement 14

Counting clients and services Duplicated Unduplicated Households Individuals Services Encounters AAHI Workshop: Measurement 15

How to handle missing data? What do you do when you don t have all the information for a client? Assessing whether it invalidates your data Use your available data identify patterns in your missing data Is it from specific populations, certain activities, particular sites? Are missing fields evenly distributed? Use of unknown and chose not to report Reporting unknown as a category Increasing confidence in disclosing information Use of prompts or mandatory fields AAHI Workshop: Measurement 16

How to reduce missing data? What do you do when you don t have all the information for a client? Understand why: Client concerns Inconsistent practices between staff Different collection tools/strategies at activities/events Addressing client concerns with sharing data Explain, explain, explain. Staff training Standardized language Written materials, posted signs Newsletters and annual reports AAHI Workshop: Measurement 17

How to reduce missing data? Inconsistent practices between staff Ensuring training and materials are standardized Make sure staff understand why the data is important Use of visual prompts to assist staff highlighted or bolded fields, keeping forms easy to fill Mandatory fields Most useful when you have a case management system where these can be built, but can be utilized in some online forms. Use sparingly Have a policy on what to do if the data is not available AAHI Workshop: Measurement 18

When your data doesnt add up Knowing your data and using self-checks Validation Your unduplicated count should never be greater than a duplicated count of services Rarely should your duplicated count be equal to your unduplicated count Knowing your average number of services per person perhaps by program, should give you an expected unduplicated number you can use to flag unusual results. Knowing your attendance rate is another point of validation AAHI Workshop: Measurement 19

When your data doesnt add up Finding the errors Common sources of error Data entry especially with errors in demographics to identify unduplicated individuals without indexing, all key demographics need to match. Filters not being selected appropriately Formulas Errors in formula Not all cells included in formula Pivot tables not being refreshed AAHI Workshop: Measurement 20

What to think about when evaluating case management software First know you organization, your needs and your future plans. What to include in your evaluation of a software Complexity Ease of Use Support Integration Survey/Data Collection Functionality Results and data tracking Communication Features Security and Access Management AAHI Workshop: Measurement 21

Thank you Additional questions: Bethany Sanders, MPH bethanysanders@haii.co.uk You will be receiving the following additional documents: Driver-diagram (pdf and editable word document) CRM/Case Management System Decision Guide Data Change Management Plan Template AAHI Workshop: Measurement 22

Appendix Resource links AAHI Workshop: Measurement 23

Resources Data Collection and Analysis Further information covering what data to collect, how to collect data, understanding your data and communicating results: https://www.schusterman.org/resource/data-playbook Guidelines for improving data collection for diversity, equity and inclusion: https://cloudspn.tfaforms.net/266232 AAHI Workshop: Measurement 24

Resources Excel Cleaning data Top ten ways to clean your data https://support.microsoft.com/en-us/office/top-ten-ways-to-clean-your-data-2844b620-677c- 47a7-ac3e-c2e157d1db19?ui=en-us&rs=en-us&ad=us How to investigate missing data in excel https://www.youtube.com/watch?v=2Kp4LwoILAg Creating Tables Excel Tables - Only 1% of Excel Users Know Tables https://www.youtube.com/watch?v=Du73CPqWGQw How to create and format tables in Microsoft Excel https://www.youtube.com/watch?v=M07df44RXOM MS Excel - Basic Table https://www.youtube.com/watch?v=U1KRV9zJtUc AAHI Workshop: Measurement 25

Resources Excel Dealing with duplicates Identifying duplicates How to Find Duplicates in Excel & Highlight Duplicates: https://www.youtube.com/watch?v=Y9HF39yAjjw Find & Highlight Duplicate Rows: https://www.youtube.com/watch?v=ljNlaGuJy8U Removing duplicates 7 Ways to Remove Duplicates in Excel: https://www.youtube.com/watch?v=GoZMQLYIgMA 3 EASY Ways to Find and Remove Duplicates in Excel: https://www.youtube.com/watch?v=ADArCWLz55Y Alert for Duplicate Values Alert for Duplicate Values in the Same Row: https://www.youtube.com/watch?v=1GDgTN4lvAo Highlighting Duplicate Values in Excel - adding alerts with Dynamic Array Formulas: https://www.youtube.com/watch?v=kLhP-35Pe7k AAHI Workshop: Measurement 26

Resources Excel List all sheets in a file Excel Formula to List All Sheet Tab Names and include Hyperlinks https://www.youtube.com/watch?v=ZZ9j8m1lT6w How to Get a List of All Worksheet Names Automatically in Excel https://www.youtube.com/watch?v=1MkeZmFehhg Indexing 4 Ways to create an index/ Add Automatic Index Columns to Excel Tables https://www.youtube.com/watch?v=n0WYT2rBihg How to Create Unique IDs from Your Excel Data https://www.youtube.com/watch?v=Hk8dVdjv4j8 How to Automatically Add Numbers in Rows| Serial Auto-Numbering in Excel https://www.youtube.com/watch?v=8S0avwTJDPs AAHI Workshop: Measurement 27

Resources Excel Pivot Tables Excel Pivot Tables EXPLAINED in 10 Minutes (Productivity tips included!) https://www.youtube.com/watch?v=UsdedFoTA68 Advanced Pivot Table Techniques (to achieve more in Excel) https://www.youtube.com/watch?v=yHzT_BUggQk How to Create Pivot Tables in Excel https://www.youtube.com/watch?v=PdJzy956wo4 AAHI Workshop: Measurement 28

Resources Addresses Extracting a ZIP code from an Address in excel How to Extract the Zip Code in Excel With a Formula: https://www.youtube.com/watch?v=1Zi6- dZT5s4 Extract Zip Code from a Multi-line Address Field with Mid and Find Functions: https://www.youtube.com/watch?v=CXTBwaYdI5E Looking up a ZIP Code from an address Excel Get Postal Code or Zip Code for Address: https://www.youtube.com/watch?v=KwlaqhY4v7U PostGrid: https://www.postgrid.com/ Resource for looking up ZIP codes and counties: https://www.zip-codes.com/state/md.asp Resource to find out ZIP Codes for addresses: https://tools.usps.com/zip-code- lookup.htm?byaddress AAHI Workshop: Measurement 29

Resources Google Sheets General Tutorials Google Sheets Formulas Tutorial https://www.youtube.com/watch?v=9TVmdFtmbbo Google Sheets for Excel Users Tutorial (beginners to advanced) https://www.youtube.com/watch?v=tCVNiIvHqZM 10 Google Sheets Tips You DON'T Want to Miss https://www.youtube.com/watch?v=O0k9uE4xnD0 Duplicates Google Sheets - 6 Ways to Highlight Duplicates: https://www.youtube.com/watch?v=2vVCHn03ocA Combining Sheets Combine Multiple Worksheets to One Master Sheet in Google Sheets (Tab Names Included) https://www.youtube.com/watch?v=DDlh3EN6NV8 Google Sheets - Linking Data Between Sheets (Workbooks, Files) & Other Worksheets (Tabs) https://www.youtube.com/watch?v=iGvvK8O5BpQ Google Sheets Import Range | Multiple Sheets | Import Data | With Query Function https://www.youtube.com/watch?v=Jl964i0UuCY AAHI Workshop: Measurement 30