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

The file drop location is:

H:\Systems and Data Analysis\DATA_IMPORTS\Marketing

The files that exist in the drop folder need to remain as the ETL process from EBIS is expecting to pick up data files, but they do not need to be updated or changed in any way. The remain to keep the ETL process from failing due to missing data files.

All of the remaining steps are automated in documented beginning with Step 2 below.

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

A Google Sheet is refreshed every Friday, and a ticket is sent to spsanalytics@columbia.edu notifying the team that the data is ready for import. The sheet is linked in the email, but it can also be access through this link.

The steps to get the data from the Google Sheet into the file format required for the EBIS import process are noted below:

• Download a copy of the Google Sheet in Excel format
• Open the created Excel file and filter the data in the export tab on activity_date to include only dates greater than the last date value from the previous import of the data file to CEREPORTS
  • For example, if you imported the the file previously, and the last activity_date value in the records was 12/14/23, you need to filter everything from 12/15/23 forward
  • NOTE: There is a columbia_sps_powerbi_export tab in the file – DO NOT USE THIS TAB
• Using only filtered new rows in the file, copy and paste that data into the RPT_MKT_PAID_MEDIA.xlsx in the Marketing folder
  • NOTE: Overwrite any existing data in this file to ensure that no duplicate rows are loaded
  • When copying the data, note the following mapping for the columns in the Google Sheet to the import sheet
    • Google: activity_date = RPT_MKT_PAID_MEDIA: Day
    • Google: campaign = RPT_MKT_PAID_MEDIA: Campaign
    • Google: platform = RPT_MKT_PAID_MEDIA: Category
    • Google: clicks = RPT_MKT_PAID_MEDIA: Clicks
    • Google: spent = RPT_MKT_PAID_MEDIA: Media Cost
  • There is one column in the RPT_MKT_PAID_MEDIA file that needs to be manually updated with SPS Program Codes
    • Using the value in the campaign column, insert the appropriate Program Code in the Program column
      • For example, a campaign value of 'FY24 - Actuarial Science' would require a Program value of SPACTU
      • Leave the Program column empty in cases where a single program code does not apply, such as 'Certificates & Certifications'
• The 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

NOTE: The RPT_MKT_PAID_MEDIA.xlsx file is processed every Tuesday, and the only method for updating the file is this manual process. If for some reason the file cannot be updated for any given week, the rows should be cleared out of data, and a file with only the headers row should be left in the pickup folder for EBIS. If this is not done, the previous week's data will be loaded again, and must be manually removed from the tables after the import to remove the duplicate records.

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.