Technical Details

Current Release Version: 2023.1.5

Release Date: 16 Oct 2023

Report Location: PowerBI / Apps / SPS Leadership, SPS Admissions

Security Roles: Leadership, Admissions

Requested Features

Current Cycle 

Release 2024.1.0 [January 2024]

Features to Complete

• Not started

Features to Consider

• Leads
  • The underlying data pipeline is not refreshing with changes that take place since the initial load of the record
    • For example, if a person starts an application after the data has been pulled, the app data will not be part of the row, so it will not count the application
    • Need a mechanism to refresh the data after the initial load, otherwise the pipeline will need to do a full reload of the data with the weekly import
• Add a report-wide filter approach to strip away particular touchpoints
  • This is primarily intended to keep the internal emails from skewing the results across the other channels
  • Will require a lookup table for all sources that are linked to the various views that contain source data (unified_marketing, slate_pings)
• CEREPORTS
  • Create weekly export and pipeline for RPT_MKT_ORIGINS table
• Add a filter for country of citizenship and current country to all report pages that could use it
  • Event Outcomes, Events (unified_recruitment)
    • Citizenship Country already present in query
    • Current Country needs to be added to data
  • Touchpoints (journey)
    • Both fields will need to be added to query
  • Origins (origins)
    • Both fields will need to be added to query
  • Pings (slate_pings)
    • Both fields will need to be added to query
  • Ads, Emails, Interactions (unified_marketing)
    • Both fields will need to be added to query
  • Leads (slate_lists)
    • Citizenship Country already present in query
    • Current Country needs to be added to data
• Incorporate tuition revenue into the outcomes, particularly around commits
• Is there a way to get the overall outcome totals into the data views to show how much of the final counts were impacted in some way by marketing and recruitment efforts?
• Note: This was completed for the Touchpoints page; use this as a model for other locations
• Recruitment Outcomes
• Add a global map to show where the attendees are coming from
• Look into options to ensure that outliers are not skewing the average calculations
  • Median, or a scrub of outlier cases in source data
• Web Traffic
  • Add a global map to show where the traffic is sourced
• Summary / Dashboard
• Stack the outcomes bar by the Term of Entry in the chart on the outcomes detail page
• Add Commit Date to the APPL_SLATE export and table to allow a more precise date for each application commit [dependent on Slate team implementing the value]
• NOTE: In many cases, the decision date is the same as the commit date
• Demographics
  • Build a lookup table for individual demographics to allow filtering and sorting on the key indicators (domestic, country, student status)
  • The data model needs to use this table across all of the datasets that can support the demo data
  • Add filters for all available demographics to the page
• From the detail pages, create a drill-down report page that can look at a selected week with more details behind the data for that week
• Show additional KPI metrics
• Make this page more narrative and dashboard-like since the data is much more specific
• Budget Extension Table
  • May require updates to the way the data is collected and imported
  • Tie the rate of spend against the allocated budget into the summary section
  • The budget can be for any area of spend, including marketing and recruitment
• Include the cost of prospects added through a Purchase List in the marketing spend
• Split the shared recruitment budget data across the supported programs
• Include a budget for each program per fiscal year
• Performance quality metrics
  • Create a dataset with the key indicators in the Slate data for the applicant profile
    • Work experience, test scores, previous institutions, others?
  • Create a dataset with educational outcomes
    • Graduation rates, GPAs, others?

Features Ready for Release

• Not started

Previous Cycles

Release 2023.1.5 [October 2023]

• Update web traffic query and report to pull from GA4 with UA discontinued
  • Remove Organic Searches, Unique Pageviews, and Time on Page (unsupported)
• Update web traffic pages to account for columns that are no longer part of the underlying dataset
  • Summary, Web Traffic

Release 2023.1.4 [August 2023]

• Touchpoints
  • Cosmetic changes to charts to align with Event Outcomes page
  • Recalculating all touchpoint indicators to key off of outcomes as the primary table, then matching against journey data points
    • All counters use filters on outcomes and accurately reflect distinct counts on PID and EventIDs
• Event Outcomes
  • Add Participant Status filter (attend, noshow, etc.)
  • Add Enrollment Term from ASR to underlying data and include a filter
  • Use new dual axis chart for synced y-axis values to make the chart more readable
  • Ensure that all of the counts are now using unique PIDs and eliminating double counting applicants that attend multiple events
  • Include total Starts/Submits/Commits counts to show the juxtaposition against event-driven totals

Release 2023.1.3 [July 2023]

• Remove the recruitment spending from the summary page; new data has not been provided since April 2023
  • From Suzanne Wolfinger:
    "It would be our preference not to include our spending totals in PowerBi, as our spreadsheet is a working spreadsheet and not the final, officials numbers for many of our team activities or the programs who also use our budget for their recruitment activities."
• Ensure year selector on each page is on top of the text label to allow selection
• Select the FY2024 filter as the default view

Release 2023.1.2 [April 2023]

• Summary
  • Separate the Recruitment Cost to make it clear it is not specific for events, but rather an overall spend indicator
• BUG: The started apps are always one more than they should be due to NULL values included in the distinct counts
• SQL: Add index to RPT_RCT_EVENTS to improve performance and reduce timeout occasions

Release 2023.1.1a [February 2023]

• BUG: Attendee and no-show counts were inflated in cases of multiple applications per prospect

Release 2023.1.1 [February 2023]

• Interactions
  • Change filter to show new "Admissions Interactions" category
  • SQL-ONLY: a2_mkt_unified
    • Change the "Recruitment Interactions" category in the underlying data to "Admissions Interactions"
    • Filter out all of the "Other" interactions
      • Take a closer look at what these actually represent
    • Reclassify all of the "External" messages from the Email bucket to the Interactions bucket

Release 2023.1.0 [January 2023]

• Interactions
  • SQL-ONLY: a2_mkt_unified
    • Change the "Recruitment Interactions" category in the underlying data to "Admissions Interactions"
    • Filter out all of the "Other" interactions
      • Take a closer look at what these actually represent
    • Reclassify all of the "External" messages from the Email bucket to the Interactions bucket
• Event Outcomes
  • Align both Y-axis values in the Events Over Time visual to make it easier to see how the values compare to each other

Release 2022.1.6 [November 2022]

• Leads
  • Add outcomes view to the underlying procedure to allow for matriculants in the PBI report page
  • Add Matrics to the Leads data grid and graph
  • Split App Starts and Submits into separate columns

Release 2022.1.5 [October 2022]

• Leads
  • BUG: It looks like there is something wrong with the way that the Apps are being calculated for some of the lists
    • Some lists have the same number of prospects and apps, some much larger that the number of leads
    • FIX: Checking for BLANK app_id, added cases where the value = "" instead of NULL
    • FIX: Strip some duplicate app counts when there are multiple unsubmitted applications
  • Add Apps to the Leads Volume By Program visual
  • Updated underlying data to fix issues with some uploaded data not having a creation date
  • Added citizenship country to dataset and filter panel
• Personal Information
  • Because the report has been updated to allow for details to be pulled, all personal info other than the Slate ref id has been pulled from the underlying procedures

Release 2022.1.4 [September 2022]

• BUG: On the Leads page, the Apps column is counting NULL as one of the distinct values, so there will always be one more than there should be
• Remove hover icons from visuals that do not need them for all pages
  • Event Outcomes
• Update bookmark to insights to include the stealth apps filter in the narrative.
• Event Outcomes
  • Include the split of attend vs. no-shows in the data table section
  • Add Attended to the available filters to show how attendance changes the numbers
  • Commit counts are off for all of the MS programs due to a change in the way the commit status is stored in Slate
    • Update the underlying query to pull the post-decision status value from Slate
    • Update the procedures to use the ASR view to align commit status across the reports

Release 2022.1.3 [June 2022]

• BUG: Unified marketing table linked to the wrong program code, causing issues when filtering on the summary and ads pages.
• Revised spend per submit and commit to focus solely on ad spending and changed the label to reflect the new value.

• SQL-ONLY: Complete after the next push to production

• Remove the Week column from the SQL procedures that were calculating it upstream (outcomes)

Release 2022.1.2 [March 2022]

• Standardize the FY selector buttons across all pages
• Touchpoints
  • Ensure that the blue totals area keeps all of the filters EXCEPT the journey[touchpoints] to accurately reflect the counts when filters or selections are applied
  • Clear the default Origins selection from the Touchpoints page (retained by mistake from testing)
• Add hidden Infographics page for an interactive analysis of the value of Information Sessions in acquiring commits

Release 2022.1.1 [March 2022]

• Origin Source
  • Requires a new Slate export and CEREPORTS import  table (RPT_MKT_ORIGINS)
  • Include all channels for entry, but separate into two major categories: Hot Leads (with a program of interest or study) and Leads (any interaction with the system)
  • Include date, program, degree type
  • Include filters for lead types, origin source, and outcome
• All Pages
  • Remove filters on individual visuals
• Touchpoints
  • Add the new origins data and allow selection of origins to filter the report
  • Add the ability to export the Applicant Details matrix
  • Add a new outcome called Event Attendance to the underlying data so it can be used to filter the results
• SQL-ONLY: Convert nvarchar fields to varchar to improve build performance
• Decrease font size in the FY slicer to accommodate a new year

Release 2022.1.0 [January 2022]

• Create a bridge between the program code and the events matrix to show the name of the program whenever there is a matching program code
  • This coding change will require socialization with the data entry people to ensure they know the appropriate program codes
    • Michael Falconieri is working on establishing this standard within the Recruitment/Admissions teams
• Cosmetic updates to bring the report in line with the latest SPS design standard

Release 2021.1.6 [November 2021]

• Summary / Dashboard
• Remove Lead Lists and Interactions charts
• Add the application trend visuals from the recruitment outcomes page with less granularity
• Add comma format to Outcomes visual totals
• Marketing Outcomes / Touchpoints
  • Blend events, pings, and outcomes into a universal "journey" table to use for comparing touchpoints across all three areas in the trend visuals
  • Use a similar approach as with Recruitment to show how long it takes for leads to perform an action (start, submit, commit)
    • Break into five categories: 1, 2-3, 4-7, 8-10, 11+ touchpoints
    • Include overall totals and the average # of touchpoints to reach an each outcome
  • Need a way to understand how much of the applicant outcomes are through marketing/recruitment efforts vs those that occur with no touchpoints
    • Show the overall pipeline milestone counts from the complete pool of started applications
  • BUG: In cases where there are zero actual starts, submits or commits, the counter always registers one
• Recruitment Outcomes
  • BUG: The commit counts are over-represented, and includes declines; update the calculation to reflect true commits
  • Add the internal name and second folder for Slate events to the data pipeline and warehouse
  • Include the second folder as a middle tier for the grouping in the events list
• Web Traffic
  • Backload the FY2020 web traffic data to the warehouse
• Update info link to point to new documentation

Release 2021.1.5 [October 2021]

• Change fonts to Segoe UI throughout for consistent rendering
• Add the Key Milestones dates for 2022
• Centralize all of the Fiscal Date calculations into the FY lookup table, including the Key Milestone dates for each year
• Remove the individual calculated columns from the related tables
• Summary
• Replace tables with charts for the following visuals: Direct Emails, Lead Lists, Interactions, Pings
• Recruitment Outcomes
  • SQL-ONLY: This is a problem in the SQL extract not providing the EventID from Slate
• Remove extraneous filters from all visualizations
• Event count is always one in the matrix

Release 2021.1.4 [September 2021]

• New report page for Recruitment Outcomes
  • Include in the marketing report build documentation
  • Strip out any test import sources and records where possible
  • Clean up any noticed errors in the session titles on the Slate metadata side
  • Consolidate session titles where possible on the Slate metadata side
  • The data should include both prospects and applicants to allow for a view of all interactions regardless of their position in the funnel
  • Include indicators of the time to action (1-Day, 7-Days, Within 30 Days, >30 Days) for application starts, submit, and commits
  • Include an indicator of the average number of days to an outcome from the event date
  • Include a selector for a range of dates across all event dates
  • Calculate Leads as prospects that have interacted with Slate in some way by cross-referencing to the PINGS table, and/or checking for RFIs or an application
  • Add the total count of Prospects and RFIs to the Leads page
  • Update the loading process to be incremental due to the size of the dataset and add to the build documentation
• Create a scheduled export of the new recruitment query to the SFTP folder for pipeline to CEREPORTS
• Create a query in Slate that includes all of the data points required for a more detailed recruitment event view
• Using the new data, create a view of events that displays the name of the events, and the details of attendance and outcomes over time
• Sync the FY and Event Date slicers to reflect the same dates
• Update the RPT_MKT_SLATE_LISTS pipeline to include all the prospects that were loaded to show volume
• Add the total count of starts, submits and commits to the performance visuals
• Add the country, citizenship and student status fields to the available filters
• Show the number of events by week in the Events subreport matrix
• Add to the top level count metric to the dashboard view
• Requires adding the unique session ID to the Imports query from Slate and then backloading all of the historical data to the RPT_MKT_SLATE_IMPORTS table
• Update the unified query build procedure to pull a unique ID for events that were not imported through uploads
• Fix issue with Per Submit and Commit spend calculations introduced with new data structure for outcomes
• Sort Month filter drop down by month number
• Filters
• Add the country and student status fields to Marketing Outcomes
• Add the country field to Web Traffic

Release 2021.1.3 [August 2021]

• SQL-ONLY: Add WEB categories for News, Events, Ideas, and Blog pages
• Remove the outcomes from the unified marketing procedure
• Add a mechanism to view outcomes against all of the potential data points that can be tracked at the PID level
• Purchased Leads (e.g., which list sources produce the most commits, etc.)
• Ping Sources (e.g., Google, Facebook, etc.)
• Add matriculants to the outcomes procedure
• Sort all outcomes based on the funnel position
• Rename the report to “Communications and Marketing”
• Update the title on the individual pages

Release 2021.1.2 [July 2021]

• Add external emails that are BCC’ed to the Slate gateway for logging purposes to the underlying message dataset
• Update the a2_mkt_unified procedure to add in the external emails to the aggregated counts for the emails section of the dashboard and detail report page
• Requires a separate join on the email address to pull all records, since most do not have a direct map to the person id in the message table
• Purchased Prospect Lists
• Add Purchase List data to the model
• Add a report page similar to other data tables (see Pings)
• Create an import pipeline and update the marketing report build documentation
• Create a new procedure to aggregate the purchase list data for viewing in PowerBI (a7_mkt_slate_lists)
• Add a table view of aggregate info to the dashboard page
• Change report behavior to cross-filter instead of cross-highlight when selected on the dashboard page
• Allow export on all the tables and visualizations
• Add comma to the Actions counter for Pings on the dashboard summary report page
• Selecting a week in the web traffic visual filters the dashboard elements to show the week’s impact
• Clean up the decimal indicators on the dashboard
• Remove the Marketing Strategy text area from the dashboard
• Since not all emails being displayed are for marketing, rename to “Email Communications” for the top-level category in the underlying dataset
• Convert server procedure to production naming convention and document in the procedure repository (from a7 to a2)
  • a2_mkt_*
• Standardize related procedures with a groupable naming convention
• Add all procedures to official active procedure list

Release 2021.1.1 [July 2021]

• Update the fiscal year slicer to allow for three buttons now that FY22 is available
• Add initial ping activity data into the report
• Create an export query in Slate; build a pipeline to import to CEREPORTS
• Add to the data model in the PowerBI report; create relationships to date and program code
• Include a report page with the details
• Include high level visualization(s) for the dashboard page
• Build an aggregated table to improve the build performance so the report does not fail to refresh daily due to oversized tables
• RPT_MKT_UNIFIED
• Remove social media visualization from the dashboard, and the report page
• The underlying spreadsheet is no longer kept up to date by Caroline, and has not been updated for over three months

Release 2021.1.0 [January 2021]

• Fiscal week calculation is not correct at the turn of the year
• Change value display from millions to thousands for paid media cost metric card
• Remove decimal places for any count fields that are always whole numbers (e.g., web traffic pageviews)

Release 2020.1.3 [December 2020]

• Add Google Analytics web traffic data to the dataset (RPT_MKT_WEB_TRAFFIC table)
• Include organic search traffic in the web statistics
• Add a visualization to the summary dashboard for web traffic
• Add new report page that ties in the Google Analytics traffic from the external web site
• Relate page sections to the appropriate programs by Pool Code
• Exclude partial weeks to keep the charts clean
• Combine SHGEN1/2/7 into a single Summer Immersion category
• Include a new general category for data alignment called ‘APPLY’ that will group all elements associated with the Slate application website traffic
• Add it to the exposed categories that are not directly related to programs through SIS (e.g., BRAND, GENERAL, SUMMER, etc.)
• Consolidate the landing page names into user friendly groups
• Strip out the server name in the text
• Logically shorten the names when possible
• Add visualizations to the report detail pages
• Ads: Engagement / Spend line chart
• Ads: Spending by channel
• Emails: Weekly email Volume by type
• Emails: Overall volume by type
• Events: Attendance / RSVP by week
• Events: Overall Volume by type
• Interactions: Weekly volume by type
• Interactions: Overall volume by type
• Social: Weekly volume by type
• Social: Overall volume by type
• Outcomes: Weekly volume by type
• Outcomes: Overall volume by type

Release 2020.1.1 [September 2020]

• Data architecture
  • Paid Media (Marketing ads → RPT_MKT_PAID_MEDIA)
• Get updated versions of the following data sources from stakeholders and update the data warehouse
• Ensure the milestone dates are specific for the selected FY; currently showing the FY2020 statements regardless of year
• Need to get the dates and descriptions for FY21 from Allison
• Align the title and FY slicer across all of the pages

Release 2020.1.0 [September 2020]

• Data architecture
  • Recruitment Budget (RPT_PAID_RECRUITMENT)
  • Social Media (RPT_MKT_SOCIAL)
• Get updated versions of the following data sources from stakeholders
• Fix the new categories for RFI forms in the FY21 data from Slate in the unified SQL procedure (Training, Pre-College)
• Cause by using “event” instead of “Event”; make the query case-insensitive
• Allow the user to deselect the FY slicer to show all available data
• Add a fiscal calendar table to the PBI to allow joining based on the fiscal year
  • This will allow one report to service any given FY
• Update the PBI to allow user to choose the FY to view; make it a toggle with a required selection
• Normalize the social media data to consolidate similar naming
• Create new unified versions of the Slate queries that include all records
• Ensure that record tracking is enabled to only pull records that have been changed since the last extract so they can be appended during the data warehouse import

Release 2020.0.9 [August 2020]

• User Interface
  • Include high level rollup counts for the entire FY (based on filtering)
  • Marketing Spend, Visits, Cost per Visit
  • Total Emails Sent by category
  • Recruitment Attendance vs RSVPs, Spend
  • Counselor Interactions
  • Social Media
  • Outcomes: Starts, Submits, Commits, RFIs
  • Cost Per SUBMIT, COMMIT
  • Direct emails
  • Recruitment events
  • Recruitment interactions
  • Social media
  • Outcomes
  • The week calculation should be adjusted for the FY beginning on 7/1 of each year
  • Slate launch, Blackboard launch, deadlines, etc.
  • Create a table for this information to centralize the data source
  • Sync the filters across all pages
• Summary
• Show all data points in a single set of grouped rows
• KPIs should be laid out across the page horizontally and grouped by week
• Use a measure to add in special notes for any given Week header to mark key date milestones
• Add Month as a filter option to isolate for a specific range of weeks
• Provide a narrative block of text for each program (provided by Debbie) and display it on the Summary page (based on filtering)
• Separate the channels into separate pages to make it more user friendly
• For sessions that are always attended (e.g., Idealist Fairs) account for the attendance in the calculations by setting Attend = RSVP values
• Data architecture
  • Recruitment COST
  • Created standalone table with aggregate spend by program; shown only on the summary page for this release
  • External Marketing: Print, DoubleClick, Adwords, LinkedIn, Facebook
  • Print, DoubleClick from marketing vendor sheet
  • Others from data exports per platform for previous year, with new data integrated into marketing vendor sheet
  • For External Marketing, show Spend and Clicks as the KPIs
  • Internal Marketing: Slate email campaigns
  • Slate_Messages_FY2020 (Slate query imported table)
  • Slate_Mailings_FY2020 (Slate query imported table)
  • Recruitment: Slate events (native and external imports), interactions
    • Includes form responses, including the standard RFI
  • Slate_Events (Slate query imported table)
  • Slate_Responses_FY2020 (Slate query imported table)
  • Slate_Event_Imports_FY2020 (Slate query imported table)
  • Social Media: Previous year data in a manual spreadsheet
  • Social_FY2020 (Excel file imported table)
  • Outcomes: Use APPL_SLATE to include the primary outcomes that we measure from Admissions
    • These are combined into a single data point called “RFI Forms”
  • Application submits, commits, and starts
  • Online RFI submits, Recruitment RFI intakes (imports)
  • Ensure each dataset has a relational column that equals the Program Pool Code for proper alignment across the full data model
  • For Slate sourced data: If there is an application, use the Program of Study; if a no app prospect, use the Program of Interest; finally, leave empty when there is no clear program
  • All other sources require a normalized code mapped to the data whenever possible (marketing, social media)
  • SIS lookup table for official Program Name
  • Direct emails = #Sent (emails)
  • Recruitment interactions = #Interactions (calls, emails, zooms)
  • Recruitment events = #Attendees (events)
  • Social media = #Posts
  • Outcomes = RFI Submits, App Starts, Submits, Commits
• Create a unified procedure to combine data for the following segments
• KPIs