Creating Satisfaction Metrics reports for MAP, MAP2, and MEM

Satisfaction metrics for all advanced programs are due from participants on the 15th of each month. The Program Coordinator is responsible for pulling the submitted metrics via a gravity form and creating an excel report. Metrics are submitted by participants through the LMS and are tracked in six-month time frames that correspond to the period from the Annual Member Conference to the Mid-Year Member Conference and from the Mid-Year to the next Annual. The reports track each participant’s recorded satisfaction metrics and also their participation in virtual sessions, study groups, and study papers. The report is compiled into an Excel spreadsheet and sent, along with a summary of the data, to the Founders and Program Leaders as well as each participant in the program to hold them accountable to their program requirements.

The metrics are also added to the PowerPoint slides for the next virtual sessions of MAP, MAP2, and MEM.

Data for the report is pulled as a csv file and compiled into an Excel spreadsheet. It is easiest to complete a report by copying the Excel file from the prior month, saving it for the current month, and then transferring the csv data to it. That is the method explained here using the MAP report as an example. Where a file is saved in the example in the MAP folder or using the MAP name, you would substitute MAP2 or MEM for those reports.

Pulling the report data

1. Log into the IE site as an admin user and under Faculty click on the Study Paper Reports section. Each of the 3 data files (MAP, MAP2, MEM) is pulled from the Form Completion Reports box.
2. Use the Search Forms field to locate the data you want to pull. The quickest way to find each report is to search using the phrase “metrics and compliance,” though you can search in the way best for you. The 3 data files are titled:
   • MAP Satisfaction Metrics and Compliance
   • MAP2: PSTI Satisfaction Metrics and Compliance
   • MEM Metrics and Compliance
3.  Enter a Start Date and End Date and click Generate CSV. The csv file will download to your computer. Save a copy of the cvs file to the Metrics Report Pulled folder in Dropbox for the appropriate program. Example: Influence Ecology/Satisfaction Metrics Reports/MAP/ MAP 2017/Metrics Report Pulled.
   • In the Start Date field box enter the date from the previous month after the 16th day (ex: if you are pulling for September metrics, enter date August 17).
   • In the End Date field box enter a future date (ex: if pulling for September 15th metrics, and you are doing the report on the 16th, the date you enter is the 17th. This is so any late metrics already submitted will appear on the report).
   • After pulling the data, you can look at the Entry Date and Month columns to determine if all of the submitted entries are for the current month. Note that some people will submit late and you may pull a few of those from last month, and some will not remember to update the Month column—check the Entry Date field to clarify the submit date. Delete any entries that are clearly from the previous month.

Preparing the new report Excel file

It is easiest to create a new report by copying the Excel file from the prior month and transferring the new data to it (the formula for metrics is already in the previous report, and not every column of data is transferred to the final report, so this keeps you from reinventing the wheel). Simply open last month’s report, rename it for the current month using the Save As command in the format M## Report YYYYMMDD, e.g. MAP Report 20180115, MAP2 Report 20180115. Then remove the current data and cut and paste your sorted data into the spreadsheet. Completed reports are filed in Dropbox under folders for each program, MAP, MAP2, and MEM: Influence Ecology/Satisfaction Metrics Reports/MAP/ MAP 2018.

1. Open the metrics report Excel doc from the previous month and rename it for the current month using the Save As command.
   • If you want to use the previous month’s file as a guide in completing the form, reopen it. Just be careful to make changes only to the new file!
2. Clear the data from all columns and remove all color-coding from the form using the No Fill command. Be sure to remove ONLY the data, NOT the Health/Money/Income calculations below the data.
3. Compare the participants who have submitted metrics with the participants listed in the program management (PM) tab to see who has not submitted. Update the data file to include the names of all participants who did not submit.
   • Delete the first column in the report (the title of the report).
   • Sort the participant entries alphabetically by First Name.
   • Create two new columns next to the Last Name (B) column.
   • Copy and paste the first name and last name of participants from the PM tab into these columns. Ideally, each name should match up on the same row. If participants did not submit their metrics, it will not.
     • Check the Start Date on the PM tab for participants who just started the program on the first of the current month. They will likely not have a submission for the month and will not be included in the report. Those on hold may also not submit. If they did not submit, delete them from the names you copied over from the PM tab.
     • If a participant is in the PM columns and should have submitted metrics but did not, add them to the First Name, Last Name columns (A and B).
     • Remove the two columns you added to check participants names.
     • If needed, resort the file alphabetically by First Name.
4. Remove any columns that are not included in the Excel file (likely this will the “Entry Date” column and, for MAP and MAP2, the “Please select which applies” column).
5. Copy and paste the data from the cvs file into the Excel report using the formatting of the Excel file (“use destination formatting”). Note you may need to add columns before copying or remove excess columns after copying as there should be no space between the table and the percentage calculations for metrics below it. If you need to format data:
   • Select the entire document by clicking the box between 1 and A in the toolbar. This will highlight all cells.
   • Increase the columns by 2x the width. Clicking and dragging the first column to the right border of the second column increases all columns to 2x the width.
   • Top align and left align all text except for Metrics and Yes/No answers. Those are centered (see a previous report for examples).
   • Wrap all text.
   • Format: Autofit row height.
   • Highlight row 1. Change the background to black and the text to white.
6. Close the data filed after you have copied the data from the cvs file to the Excel report. To maintain data that you originally pulled do not save changes.

Formatting the Report for completion

We use color-coding to denote who has not submitted metrics, who is out of compliance with their study papers and other program commitments, errors (missing info or measures), and who has met the standard measure for the month in Health/Money/Income, and who has submitted late. The first column of the report shows this color-coding and what it denotes.

1. Highlight rows:
   • No Metrics (red). If a participant did not submit their metrics for the month, highlight their report column red.
   • Error (yellow). If a participant left any columns blank or answered incorrectly (e.g., entered the wrong month) highlight those spaces yellow.
   • Out of Compliance (purple). If a participant did not keep their program agreements (e.g., attended 2 virtual sessions, is in a study group), or is behind with their study paper, highlight those spaces purple.
     • Consult with the program registrar (Liz Smiley) to determine who is out of compliance with study papers. The current method of tracking these participants each month is by highlighting the participant number (column A or B) red or pink on the PM tab.
   • Met Measure (green). The metrics standards for Health and Money in the advanced programs is as follows. Highlight in green any Health and Money metrics that meet or exceed the current measure. Highlight any Income metrics that are above 0 (1 or above).
     • First Month Report 17% / 33%
     • Second Month Report 33% / 50%
     • Third Month Report 50% / 66%
     • Fourth Month Report 66% / 83%
     • Fifth Month Report 83% / 100%
     • Sixth Month Report 100% 17%
   • Late (beige). The report should be re-run before the next virtual session to see if there are any late submissions. Add these to the report and highlight the column beige.
2. Record who has and has not submitted metrics on the MEM, MAP, MAP2 PM tabs.
3. Save the report one last time and alert the Register that it is complete by sending an email with the report as an attachment. The Registrar will send the report to the Founders, Program Leaders, and participants. You will use the summary data for metrics in the email to compiles slides for the upcoming MAP, MAP2, MEM virtual sessions.

Adding the report data to the Virtual Session Slides

Virtual slides for the Advanced Programs are updated like those in FOT and PIP using the Heads and Tails, though there are several additions to the procedure. These are outlined below.

MEM/MAP/MAP2 Slide Prep

Copy the previous month’s slides and rename for the current month. Variances from the FOT/PIP slides are listed below:

MEMBERSHIP

1. Add new members to Welcome slide.
2. Advanced Program Measures slide: Sub-title should be AMC2108 to MYC2018. Add the latest Health/Money/Income measures from the report for MEM/MAP/MAP2 and compute the average for Health and Money (total/3).
3. Leader Board slide: Update with the current met H/M measures from the metrics report (those highlighted green), and the corresponding Income if above zero.
4. Membership Program slide: Updated the Compliance / Non-compliance for the items listed there.

MAP2

• Add new members to Welcome slide.
• Leader Board slide: Update with the current met H/M measures from the metrics report (those highlighted green), and the corresponding Income if above zero, and the current study paper.
• Study slide: list those participants not on the leader board and their current study paper.
• PM slide: Add current Health, Money, Income and make sure Last 6 Month column represents the last period between Annual and  Mid-Year or Mid-Year and Annual (updated Jan/Feb and July/Aug: Ask Registrar).
• Presentation slide: Ask Registrar if there are any presentations and update the Presentation slide.
• Excel Study Paper Report: Update and attach to the progam email for Kirkland’s use. REMEMBER to add any new members who will be on SP1 (but will not have submitted metrics for the month).

MAP

• Add new members to Welcome slide.
• Program Measures page: Add current Health, Money, Income and make sure Last 6 Month column represents the last period between Annual and  Mid-Year or Mid-Year and Annual (updated Jan/Feb and July/Aug: Ask Registrar). Add the MAP 2 measures from the current MAP2 report.
• Leader Board slide: Update with the current met H/M measures from the metrics report (those highlighted green), and the corresponding Income if above zero, and the current study paper.
• Study slide: list those participants not on the leader board and their current study paper. REMEMBER to add any new members who will be on SP1 (but will not have submitted metrics for the month).

Emailing the Slides to the Program Leader

Use the template in your email program for the MEM/MAP/MAP2 and send the slides to the following program leaders:

1. MEM: John Patterson, Drew Knowles. CC: Liz Smiley
2. MAP: Kirkland Tibbels. CC: John Patterson, Liz Smiley. Include Excel Study Paper report.
3. MAP2: Kirkland Tibbels. CC: John Patterson, Liz Smiley. Include Excel Study Paper report.