Skip to content
All work

Data automationYear

ASU AYCE Digital Data Automation

For ASU's AYCE Digital pilot program, I tied the course, completion, user and progress data kept in Excel sheets and queries, and the program's Salesforce case handling, into one automated workflow, replacing the manual typing and updates that used to happen every day.

Select any part of the data run to see what it does.

One daily run, animated in your browser: the sheets and the Salesforce cases are read, the queries merge them, and the workbook and report update. The rows and values are illustrative, not program data.

Role
Course Operations Assistant, W. P. Carey Executive Education
Timeline
Dates
Team
Solo or team size
Stack
  • Microsoft Excel,
  • Excel queries,
  • Salesforce,
  • Other tools used
Links

    Overview

    As a Course Operations Assistant at ASU's W. P. Carey Executive Education, where the team manages the AYCE Digital platform and program, I worked on its daily data. The AYCE Digital pilot program tracked its learners in several Excel sheets: the courses, who completed them, the users themselves and each user's progress, while learner cases were handled in Salesforce. I connected those sheets, their queries and the Salesforce cases into a single workflow, so one refresh brings everything up to date and the program's numbers, cases included, come out of one place.

    Problem

    Every day, someone had to type the latest course, completion, user and progress data into the sheets by hand, keep it in step with the cases in Salesforce, and then update the rest to match. It took time each day, and every hand-typed number was a chance for the sheets and the cases to disagree with each other.

    Approach

    Instead of typing the numbers in, the queries load each sheet and the Salesforce cases, merge them into one workbook and update everything downstream in a single run, so each case sits next to the user it belongs to. The daily job turns from re-entering data into refreshing it.

    How the daily update changed

    Before: by hand

    Every day, the same updates were made by hand, one sheet at a time.

    • person

    1. 01 / 04person

      Collect the latest data

      Gather the day's course, completion, user and progress numbers.

    2. 02 / 04person

      Type it in

      Enter the new numbers into each Excel sheet by hand.

    3. 03 / 04person

      Match the cases

      Check the Salesforce cases against the sheets and carry any changes across by hand.

    4. 04 / 04person

      Update the rest

      Bring every other sheet and summary in line with what was typed.

    Now: one workflow

    The same update as a single automated run.

    • tool
    • person

    1. 01 / 05person

      Refresh

      One refresh starts the run.

    2. 02 / 05tool

      Load the sheets

      Queries load the courses, completion, users and progress sheets.

    3. 03 / 05tool

      Load the cases

      The Salesforce cases come into the same run.

    4. 04 / 05tool

      Merge

      The queries join the sheets and the cases into one workbook.

    5. 05 / 05tool

      Update everything

      The workbook and the program's report update from the merged data.

    Architecture

    • Courses to Queries
    • Completion to Queries
    • Users to Queries
    • Progress to Queries
    • Salesforce cases to Queries, cases
    • Queries to Master workbook, merge
    • Master workbook to Program report, update
    Four Excel sheets and the Salesforce cases feed the queries, which merge them into one workbook; the program report updates from it.

    Gallery

    Challenges and solutions

    Challenge: Four sheets and a case system that had to agree with each other.
    Solution: Merging the sheets and the Salesforce cases in one run means each number is entered once at its source and flows to everything else, instead of being retyped in several places.
    Challenge: Another challenge you hit
    Solution: How you solved it

    Outcomes

    • Course, completion, user and progress data and the Salesforce cases now update in one automated workflow instead of being typed in by hand every day.
    • A measurable result, such as time saved per day or errors avoided