Data & Analytics
Data & Analytics
Embedded Files
Sarah ensures accurate data capture and integrity of existing data for successful regulator and legal audits and informed decision making.
Table of Contents
Click a top below to jump to that section or scroll down.
Reporting
Reporting
Sarah holds vendors to high data capture and analysis standards.
SharePoint
SharePoint
Engagement of the company's intranet doubled, according to SharePoint's modern "Communication" site's analytics. Usage data is available at 7, 30, 60, or 90 day intervals. Downloading the data on a regular basis allows comparisons year over year for trend analysis at a site or page level. Reports also indicate which content users find the most helpful, which is great feedback; it can give clues as to hot topics, needs, or usefulness.
Source: This graphic is an example from Microsoft and for illustrative purposes only.
Source: This graphic is an example from Microsoft and for illustrative purposes only.
Learning Management Systems (LMSs)
Learning Management Systems (LMSs)
Sarah designed complex reports that analyzed the training and user data. She regularly audited for data integrity. She partnered to have HR data from Kronos/UKG and ADP auto-fed into the LMS via xAPI integration or sFTPs (secure file transfer protcols) and updated either daily or in real-time.
The data reporting supported successful regulator audits by Joint Commission and CMS and was used in legal cases that had positive outcomes for the organization.
Insights from LMS reporting included but were not limited to the following:
Time spent on training vs time anticipated, sometimes leading to the realization that the reading level of the material was too high and/or needed to be translated
Validation of new hire and annual training feedback that length and number of assignments needed adjusting
Need for more scenario-based, real-world application activities and current examples to improve relevance and users' ability to apply the concepts
Correlations between job performance and training success
Cornerstone SBX (Saba)
Cornerstone SBX (Saba)
Here is an example of Cornerstone SBX (formerly Saba)'s reporting admin interface. Sarah was the super-admin who architected all reporting and data analysis from this LMS for six years.
Source: Microsoft Fabric Community
SkyPrep
SkyPrep
Here is an example of SkyPrep's reporting admin interface. Sarah was the super admin of this LMS platform for over a year. She rigorously selected this LMS during an RFP and executed a change management process, migrating the organization from MediTech and ADP Learning.
Source: SkyPrep
Bulk Imports
Bulk Imports
Sarah did the work of multiple LMS Admins in less time by utilizing the LMS's templates and bulk importing the changes needed. For example, she imported the following:
5,000+ AICC/Scorm content packages
2,000+ course and class creations and discontinues
500+ ILTs
1,000s of training completions and transcript adjustments
Partial screenshot of a Cornerstone SBX (Saba) bulk import file that Sarah used to import thousands of Scorm files.
Data Processing
Data Processing
To support a learning database for 50,000 staff globally, Sarah processed large amounts of data efficiently in Excel. Here are some formulas Sarah used.
Click the formula to learn more about when Sarah used it.
=IF(ISNA(MATCH(A2,B:B,0)),0,1)
Vlookup can be used to confirm if an item A is in list B. However, if using vlookup in a column of 100,000s of training records, Excel will crash. Sarah used the Match formula instead in these cases.
=(TEXT(A2,"0000.00000"))&","
Exporting data from an LMS can cause leading or trailing zeros to be lost. Those zeros can be required for later uploads to the system. Sarah used this formula to add the zeros back in and create text strings. In this example, department numbers had a specific number of place values before and after the decimal point. The formula filled in leading and trailing zeros to meet the requirement.
=LEN(G2) or =AND(LEN(G2)>32765,RIGHT(G2,3)<>") )")
Sarah exported data from the LMS and needed to ensure that Excel's maximum cell length did not cut off any of the data. She used one of these formulas to check if any of the cells were at the max length of 32,765 characters. If so, those cells were likely to be missing the rest of the data.
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0
One field feeding over from the HR system was only valid if it contained a number. If it was feeding over values without numbers, those profiles were not valid and needed attention. To find these invalid records, Sarah used this formula to detect which cells contained any numbers.
=COUNTIF(A:A,A2)
Sarah used this simple but incredibly helpful formula to get counts of many times an item appeared in the list. Perhaps she needed to treat items that appeared more than once differently than the rest. Once the formula populated, she could filter down to focus on items with a particular count.
=IF(COUNTIF(A:A,A2)=SUMIF(A:A,A2,D:D),"true","false")
=IF(AND(COUNTIF(Sheet2!B:B,A2)<>SUMIF(Sheet2!B:B,A2,Sheet2!E:E),SUMIF(Sheet2!B:B,A2,Sheet2!E:E)>0),"true","false")
Sarah uses SUMIF formulas to add up data from different cells, only if they meet certain criteria. For example, for all people who have position Y, sum their training records.
=LEFT(H204,FIND("~",SUBSTITUTE(H204," ","~",LEN(H204)-LEN(SUBSTITUTE(H204," ",""))))-1)
See below for details on what each of these formulas does. Sarah regularly had to isolate parts of text strings. This was crucial for her to be able to update 7,000 course titles when the naming conventions were updated, for example.
=LEFT(A1,(FIND(" ",A1,1)-1)) returns everything before the first space
=MID(A1,FIND(" ",A1)+1,256) returns everything else except the first word and first space
=LEFT(H204,FIND("~",SUBSTITUTE(H204," ","~",LEN(H204)-LEN(SUBSTITUTE(H204," ",""))))-1) returns everything but the last word
=LEFT(A1,FIND(" ",A1)) &TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))) returns first and last word
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))) returns only the last word
Macros and Visual Basic
Macros and Visual Basic
Sarah records macros in Excel and edits the Visual Basic code. She shares them with team members and trains them on using and updating them. She thereby improves efficiency and accuracy for repeated workflows, especially when converting data between systems.
For example, some systems had different naming conventions for site location names than the LMS had. The Excel macro could quickly convert the names so bulk imports into the LMS would be accepted. For hundreds of site locations and the weekly use of the macros, the set up time quickly paid off.
Sarah did not stop there. She built training on creating macros and editing them in the Visual Basic Editor for hundreds of mid-level admins, improving productivity org-wide.
Source: Microsoft Learning
Page updated
Google Sites
Report abuse