Content Comparison

...

H:\Systems and Data Analysis\DATA_IMPORTS\Marketing

All of the manual Excel data sources have been deprecated (see below).

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 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

...

Paid marketing data must be requested from the Marketing team and only appears in the report for historic years (FY23 and earlier).

Excel files sent via email from Situation [vendor] for weekly marketing metrics; with discontinuation of Situation contract, this data is no longer provided.

...

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 file on H:File 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

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 coverNOTE: 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.

Info
title Deprecated Data Sources
Note: The following elements are no longer used, but the steps are retained in case they are resumed in future instances.

Recruitment (DEPRECATED)

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

...