Overview

The Communications & Marketing Report is a PowerBI report made available to the leadership across the school, as well as to the Admissions operations team. This document covers the current steps required to build an updated release of this report.

Workflow for Administrators

Update the CEREPORTS tables

The data is managed across a number of teams, and is updated regularly. The sources below must be accessed manually, and the data extracted for inclusion in the data warehouse upload.

Step 1: Excel Sources

Each of the following data sources must be saved as Excel and copied to the H: drive for import into the warehouse.

The file drop location is:

H:\Systems and Data Analysis\DATA_IMPORTS\Marketing

Web Traffic

Use the Google Analytics Extract (My Workspace on PowerBI) to save the previous week’s data to XSLX; BE SURE TO REFRESH THE DATA BEFORE EXTRACTION

Save as UNDERLYING DATA and make sure the filtered date range does not overlap with the previous export

NOTE: The date range should have last calendar week as the filter criteria to ensure there is no overlap in loaded dates.

• Save as RPT_MKT_WEB_TRAFFIC.xlsx file on H:
• DELETE THE TOP TWO ROWS FROM THE FILE (Filter details)
• Remove the filters
• Rename the tab to Export
• File is automatically processed by the EBIS team every Tuesday morning at 6am
  • If the data needs to be manually processed:
    • Execute the RPT_MKT_WEB_TRAFFIC import script on the terminal server to update the table

Paid Marketing

Excel files sent via email from Situation [vendor] for weekly marketing metrics

• The source files must be normalized into the existing spreadsheet format (Marketing_Normalized_*.xlsx)
• Copy the normalized data into the RPT_MKT_PAID_MEDIA.xlsx file on H:
• File is automatically processed by the EBIS team every Tuesday morning at 6am
  • If the data needs to be manually processed:
    • Execute the RPT_MKT_PAID_MEDIA import script on the terminal server to update the table

Business = SPBCAR, College Edge = SPNDGH, Graduate Foundations = SPGFNS, “INT’L” = SPUNST

The date for each file should be the Monday of the week the ads cover

Recruitment (DEPRECATED)

https://docs.google.com/spreadsheets/d/1DO7lHVFddj9TpAtGJxSmRsS6HcveCZG8lJ7MnZ-tD1s/edit#gid=358248905

• Copy data from the link above into the RPT_MKT_PAID_RECRUITMENT.xlsx file on H:
• File is automatically processed by the EBIS team every Tuesday morning at 6am
  • If the data needs to be manually processed:
    • Execute the RPT_MKT_PAID_RECRUITMENT import script on the terminal server to update the table

Step 2: Slate Sources

Each of the following Slate queries is automatically executed and the files stored in the Slate SFTP CEREPORTS folder on Tuesday morning. The files are also downloaded from SFTP and copied to the H: drive as part of the automated ETL process.

The file drop location is:

H:\Systems and Data Analysis\DATA_IMPORTS\Marketing

Event Imports (Slate Query: Slate_Event_Imports)

• Execute the RPT_MKT_SLATE_IMPORTS import script on the terminal server to update the table
• Note: This appends data to the table with the new values

Events (Slate Query: Slate_Events)

• Execute the RPT_MKT_SLATE_EVENTS import script on the terminal server to update the table
• Note: This replaces all data in the table with the new values

Interactions (Slate Query: Slate_Interactions)

• Execute the RPT_MKT_SLATE_INTERACTIONS import script on the terminal server to update the table
• Note: This appends data to the table with the new values

Leads (Slate Query: Slate_Leads)

• Execute the RPT_MKT_SLATE_LISTS import script on the terminal server to update the table
• Note: This appends data to the table with the new values

Mailings (Slate Query: Slate_Mailings)

• Execute the RPT_MKT_SLATE_MAILINGS import script on the terminal server to update the table
• Note: This appends data to the table with the new values

Messages (Slate Query: Slate_Messages)

• Execute the RPT_MKT_SLATE_MESSAGES import script on the terminal server to update the table
• Note: This appends data to the table with the new values
• The following special conditions are built into the import script.
  • CSV, set the text qualifier to “, set the “Send Date” column type to DT_DATE, set charset to Latin 1252

Pings (Slate Query: Slate_Pings)

• Execute the RPT_MKT_SLATE_PINGS import script on the terminal server to update the table
• Note: This replaces all data in the table with the new values

Recruitment Event Imports (Slate Query: Slate_Recruitment_Events)

• Execute the RPT_MKT_RCT_EVENTS import script on the terminal server to update the table
• Note: This replaces all data in the table with the new values

Responses (Slate Query: Slate_Responses)

• Execute the RPT_MKT_SLATE_RESPONSES import script on the terminal server to update the table
• Note: This appends data to the table with the new values

Step 3: Build Unified Table

Note: This process has been automated and should only be executed manually in cases where the automation has failed to run.

After the ETL process has completed between 6-7am on Tuesdays, execute the following procedure in SQL Server Management Studio before 11am when the Power BI report is refreshed:

USE [cereports]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[vw_mkt_unified_build]
SELECT 'Return Value' = @return_value
GO

Note: This process should be run after ETL and before the daily refresh of the Power BI Communications and Marketing report.