Advertising Analytics

The Data Analyst monitors several advertising procedures to ensure that the Marketing Department has access to reliable campaign data that can be queried for future campaign performance reports. To maintain the integrity of this database, multiple processes need to be followed exactly.

Naming Conventions

The Rinck Agency runs several hundred digital advertisements yearly on the University’s behalf. In order to keep track of how ads perform, we need to assign a unique identifier to each ad and have created a formula that does this, in addition to defining the function of said ad. Unique names describe each ad and are created by linking 8 components with underscores. The 8 components are: recruiting period, objective, vendor, medium, audience, product, interactivity, and the ad set’s index number.

The Data Analyst will assign names to individual ads, based on a very specific protocol, upon request from the Ad agency Project Manager (PM) prior to going live. The protocol can be found on Google Docs.

Tools Required:

Time Required: Up to 1 hour

Step 1. Open Project in Wrike

Step 2. Open Reference sheet linked above.

Step 3. Open Ad Spreadsheet sent from the Ad agency PM.

Step 4. Following NC principles, assign each ad a unique name.

Step 5. If there is a question about the audience, recruiting period, etc, reply to the comment in Google tagging PM.

Step 6. Assign name.

Step 7. Complete task and tag PM and TSP.

Weekly Ads Data Retrieval for Archiving to FileMaker

The Filemaker Application is an extract, transform, load (ETL) system that was created with the assistance of Jon Barker, Director of the Department of Analysis, Applications, and Institutional Research. After the data is tidied and standardized, it is loaded into a database of weekly advertising vendor reports that can be queried for analysis of ad and web performance. Raw data needs to be pre-processed before import in order operate smoothly. Each raw vendor report is handled slightly differently and requires careful inspection of field order and data types prior to upload.

Time Required: 1.25 hrs - 2.5 hours

Tools Required:

  • Ad Campaign Manager Websites

  • Excel

  • Filemaker

  • RStudio for Google Analytics query

  • Wrike

AdWords:

Retrieve AdWords data here

  1. Download last 7 days.
  2. Preprocess Data in Excel
  3. Save to C:>FileMaker>Uploads>AdWords.csv
  4. Note Necessary Fields
Column Field Name
A Day
B Campaign
C Campaign.type
D Ad.group
E Ad
F Impressions
G Clicks
H Cost
I Views
J Video.played.to 100.
K Gmail clicks to website

Facebook

Retrieve Facebook data here

  1. Select FY19_Facebook report.
  2. Download last 7 days
  3. Preprocess in Excel by selecting the following columns:
  4. Save to C:>FileMaker>Uploads>Facebook.csv
Column Field Name
A Ad Name
B Reporting Ends
C Impressions
D Clicks (All)
E Amount Spent (USD)
F Video Plays
G ThruPlays
H Link Clicks
I Reach
J Purchases
K Purchases [1 Day After Viewing]
L Purchases [28 Days After Clicking]
M Landing Page Views
N Page Engagement
O Page Engagement [1 Day After Viewing]
P Page Engagement [28 Days After Clicking]
Q ~
R ~
S Platform

Google Analytics

  1. Run this in R Studio Cloud. Script name is GA.R script.
  2. Or run script in R Studio called Google Analytics.Rmd.
  3. Both scripts will query the vendor, collect the data, preprocess it, and download the report. Sometimes have to go through R Studio Cloud because API won’t work on desktop version
# install.packages("googleAnalyticsR")
# library(googleAnalyticsR)
# ga_auth() 
# my_accounts <- ga_account_list()
# View(my_accounts)
# ga_id <- 63145830
# start_date <- "2020-12-29"
# end_date <-  "2021-01-04"
# campaign_filter <- dim_filter(dimension="campaign",operator="REGEXP",expressions="FY20")
# my_filter_clause <- filter_clause_ga4(list(campaign_filter))
# GA_thisWk <- google_analytics(ga_id, 
# date_range = c(start_date, end_date), 
# metrics = c("uniquePageviews", 
# "bounces", 
# "timeOnPage", 
# "sessions", 
# "newUsers", 
# "pageviews", 
# "exits", 
# "sessionDuration"), 
# dimensions = c("date", 
#                "landingPagePath",
#                "source", 
#                "medium", 
#                "adContent", 
#                "campaign", 
#                "region", 
#                "city"), 
#dim_filters = my_filter_clause,
# anti_sample = TRUE
# )
# install.packages("googlesheets4")
# library(googlesheets4) 
# random_ss <- sheet_write(GA_thisWk)
  1. Change date parameters in script to pull the last 7 days
  2. The week’s raw data appears on a google sheet which has to be downloaded to Filmmaker. The filepath is C:>FileMaker>Uploads>GoogleAnalytics.csv

Programmatic:

aka DoubleClick or Goodway Group

  1. Retrieve data here

  2. Or, retrieve data here

  3. Select the columns shown below in this order

  4. Because dates can only be selected by month, the download will be redundant.

  5. Delete all of the records that do not apply.

  6. Delete the totals row.

  7. Save data to: C:>FileMaker>Uploads>Goodway.csv

Column Field Name
A Creative
B Date
C Impressions
D Clicks
E Cost
F Video Completions

Snapchat

  1. Pull data from here.
  2. Go to previous dashboard page.
  3. Make sure it’s the correct week.
  4. Save the following columns in order shown below.
  5. Note Clicks and Swipe Ups are the same.
  6. Save data to: C:>FileMaker>Uploads>Snapchat.csv
Column Field Name
A Date
B Ad
C Impressions
D Clicks aka Swipes
E Cost
F Completions
G Swipes
H Views

LinkedIn

  1. Pull data from here. Make sure it’s the correct week.
  2. Make sure to select “Ad Performance” for Report Type and a “Daily” aggregation. Otherwise the data will be summarized.
  3. Select the columns listed below. The easiest way to do this, is to eliminate all percentage fields and cost. DO NOT ELIMINATE SPENT .
  4. Save data to C:>FileMaker>Uploads>LinkedIn.csv
Column Field Name
A Date aka Start.Date
B Campaign.Type
C Ad aka Creative Name
D Impressions
E Clicks
F Likes: aka Reactions
G Comments
H Shares
I Follows
J Other.Clicks
K Total Social Actions
L Engagement
M Cost
N Viral.Impressions
O Viral.Clicks
P Viral.Likes
Q Viral.Comments
R Viral.Shares
S Viral.Follows
T Viral.Other.Clicks
U Sends
V Opens
W Clicks..Sponsored.InMail.
X ClickstoSite aka Button Clicks
Y Banner.Clicks
Z Link.Clicks
AA Conversions
AB Post.Click.Conversions
AC View.Through.Conversions
AD Conversion.Rate
AE Total.Conversion.Value
AF Leads
AG Lead.Forms.Opened

Pinterest

Pending

Column Field Name
A Creative
B Date
C Impressions
D Clicks
E Cost
F Video Completions

Pending

Other Vendor

Column Field Name
A Creative
B Date
C Impressions
D Clicks
E Cost
F Video Completions

Other Vendor

Pending

Column Field Name
A Creative
B Date
C Impressions
D Clicks
E Cost
F Video Completions

Uploading Preprocessed Data into Filemaker Database:

  1. Once all of the available raw data has been saved to the C:/ drive, it has to be uploaded into Filemaker via the Filemaker App installed by the Dept. of Applications and Data for the Marketing Data Analyst. There is a desktop shortcut.

  2. Open Marketing (brown square), go to Data Center (this takes a few minutes to load)

  3. Select Import and check the data sets that will be uploaded from the C drive. Note: There will always be AdWords, Facebook, Goodway/Doubleclick, Google Analytics. Snapchat and LinkedIn ads are usually in market as well.

  4. Note that formatting the raw data exactly as it is in these steps is critical for it to map correctly in the database

  5. If the upload is successful, a message that says the files will be uploaded overnight will be displayed (shown below).

  6. Finally, mark tasks as complete in Wrike.

  7. The Wrike tasks are set to recur weekly.

Preparing Data for Ad and Web Reporting:

Once the vendor data is uploaded, it goes through another processing stage where the various data types and field names are standardized so that the vendor tables can be merged into one clean set, ready for aggregations and calculations.

Tools Required: R Studio and Tableau

Time Required: 1 hour per week.

  1. The data sets can merged with a simple R script, C:/Users/christine.iyer/OneDrive - University of Maine System/FY21_Ad_Vendor_Reporting/MergingFilemakerVendorReports.Rmd, and saved as a new csv.

  2. Once the new csv is ready, it can be loaded into a tableau template with preconfigured calculated fields for quick analysis. C:/Users/christine.iyer/OneDrive - University of Maine System/FY21_Ad_Vendor_Reporting/Marketing_Output.twb

Weekly Reporting Dashboard on Inquiries and Applications for Rinck:

This dashboard was created to provide inquiry and application information to the Rinck Agency segmented to each “Audience/Product” they promote for us. This can provide them with feedback on whether new campaigns are having any traction. It also keeps us informed about which audience/product campaigns may need some intervention. It is a visual representation of John Tabor’s weekly reports, distilled for our specific agency campaigns.

The report is linked here

Time Required: 1 - 2 hours

Tools: Salesforce Website, R Studio, RPubs Website for Rinck, Wrike Project

  1. DAA Report

  2. Download and Save to C:>Users>christine.iyer>OneDrive>FY21WeeklyAdmissions>Reports>DAADate.csv Date needs to be changed

  3. Tidy and Standardize Data using this report in R Studio: C:>Users>christine.iyer>OneDrive>FY21WeeklyAdmissions>TrialReport>DataTidyingScript.Rmd Line 60

  4. Note this will need a new ifelse clause for FY22

  5. Running this file creates other files__ C:>Users>christine.iyer>OneDrive>FY21WeeklyAdmissions>TidiedInqAppReports>InqAndAppsDate.csv__ raw and C:>Users>christine.iyer>OneDrive>FY21WeeklyAdmissions>FinalDatatoAppend>AllGood.csv__ (aggregated and updated)

  6. Create updated visualizations and re-publish report as html for Rinck’s viewing. Report script is here C:>Users>christine.iyer>OneDrive>FY21WeeklyAdmissions>YoYTrend>FY21YoYEnrollmentCycleTrend.Rmd Line 30

  7. Confirm that it was updated by looking at the date field in the final report linked here.

Campaign Reporting

In addition to the standard agency campaigns, several times throughout the year we run additional campaigns. These require a final written evaluation to make sure that viewers engaged with the media to visit the website, make inquiries, and/or fill out applications. Two such campaigns include Spring and Fall “Travel” initiatives, but others are assigned ad hoc. I outline the process for travel, but it can be modified as needed for any agency campaign.

Travel Campaigns

  • Spring Travel

  • Fall Travel

2 Parts:

  • Initial Review: Final report due 4-6 weeks after campaign completion to give time for all of the raw data import to be completely retrieved and imported.

  • Refresh Review and Agenda Planning: Final report due 6 weeks before the new campaign is scheduled to launch.

Time Required: 16 hours over 2 weeks

Tools Reguired: Wrike, Excel, Tableau, R, Git for version control and file storage.

Initial Review

Data required: Salesforce report called which pulls new inquiries and applicants an by Initial Source Date falling between the Spring Travel Campaign with a 2 week cushion on either side. The linked SF report is called SpringTravelAdsTime.

Step 1: Create project in Wrike from Analytics Blueprint for Project Template and review dates.

Step 2: Create a folder in the USM Drive with sub-folders for Raw Data, Data Tidying Scripts, Report

Step 3: Create project in R and initialize git repository within the above folder structure. This is for version control.

Step 4: Find previous year’s schedule. This may be called “FA20 Spring Travel Schedule for Digital Ads” (FY19), “FA21 (and beyond) Spring Travel Schedule for Digital Ads” (FY21). Names get very confusing. Make sure it’s the correct schedule.

Step 5: Identify with as much specificity as possible areas visited/where we had a media presence they year before. Reconcile this with new inquiries and applicants during the in-market time span. (Reconciliation 2 hours)

Step 6: Tableau for first pass processing:

  • Ads Tidying template can be found here. C:>Users>christine.iyer>Box>FY21SpringTravel>Data Tidying Scripts>CleaningSpringAds.twbx

  • Inquiries and Applications tidying template can be found here. C:>Users>christine.iyer>Box>FY21SpringTravel>Data Tidying Scripts>SFIandATidy.twb

  • Grouping Stealth vs Non-Stealth

  • Inquiries & Applicants

  • City, State, Zip, County, geo-target, if necessary. Geo-targeting data here

  • Run dataset through the zipcode package to clean up.

Step 7: Draft report using the Basic Description, Findings, and Conclusions format. Use title, date, and page numbers. Multiple reporting formats available:

  • Can be done in R markdown if want tidying and visualization as part of the report.

  • Can also be done in Microsoft or Google Docs. See sample report here. Another report can be found at this file path. C:>Users>christine.iyer>Box>FY20SpringTravel>FA20SpringTravelV5.pdf

Step 8. Using the previous year’s schedule, make sure to hone in on inquiry and application numbers in these specific areas. Be careful not to include areas that have the same names, but different zip codes. Ex: while ads were served to Springfield, MA, there are also Springfields in other states, and including these numbers will be inaccurate.

Step 9: Submit completed report to Tracy St. Pierre for review via Wrike by tagging here in the task. Change rough draft task from “In Progress” to “In Review”

Step 10: Once TSP has reviewed the draft and made edits and suggestions in Wrike, complete the edits and resubmit, by tagging her in Wrike with the final draft task.

Refresh Review and Agenda Planning

Tools required: as outlined in part 1, initial review.

Time Required: as outlined in part 1, initial review.

Step 1: Access initial review report.

Step 2: Visualize raw data from vendor reports in Z drive in Tableau to verify that the data is consistent with the initial report.

Step 3: Update application conversions from initial review. Most likely some of the inquiries generated during the campaign will have converted to applicants.

Step 4: Amend report.

Step 5. Agenda planning. This is a complex step that requires a lot of back and forth to determine is territories need to be added or removed based on many factors, including budget. It’s best to start with the campaign schedule, identifying areas that performed well in terms of inquiries and applications.

Step 6: Summarize this data in tabular format. See sample report on page 3.

Special Campaigns

As stated above, several times throughout the year, Marketing creates campaigns for special programs within the University. These may include Public Health, School of Business, Cybersecurity, etc. A large component of such initiatives includes digital advertising. When these campaigns are in the planning phase, the Data Analyst will play a role. The degree of involvement will vary, from assigning naming conventions to ad hoc final reports. Note: The seven day report, possible mid-term report, and ongoing day to day campaign optimization will be the Agency’s responsibility.

Tools Required: Google Sheets for naming convention assignment, vendor data which will be captured by default from the ads retrieval process, Google Analytics for web performance data, Wrike for ongoing project status.

Time Required: Varies.

  • Naming Conventions: Up to 1 hour

  • Reporting: When a final report is required, set plan on 12-16 hours over 2 weeks.

Steps: Follow workflow for Travel Campaign Initial Review outlined above.

Search Names

2-3 Part Process:

  1. Analysis of Previous Year Conversions.
  2. Purchasing Search Names
  3. Analysis of Creative Performance (Email): The reporting schedule and format is a work in progress.

1. Analysis of Previous Year Conversions

Analysis of previous years’ Search Name campaign (run 2 weeks after the final broadcast in the campaign. Inquiries and applicants starting day of first send and ending 2 weeks after the last send.

Tools Required: Salesforce, Salesforce API, Wrike, Git for archives, R or Tableau for tidying and analysis

Time Required: 4 - 6 hours

  1. Create a subfolder of the Search Folder specific to the upcoming purchase called Previous Year Search Performance. (10 minutes)

  2. Locate original lead names that were put into csv form at the time of upload. Note this has already been deduped. (Folder FY__Search > Search Number > Salesforce Uploads > csv (10 minutes)

  3. The csv should be named with FY, Search number, classes contained in the data upload, and DeDuped.csv for clarity.

  4. Locate the conversion query in Salesforce. This will be in the same folder and the name tidy should be in the script name. There will also be an R script that can pull an updated conversion report. (1 hour in case query needs editing or API needs updating)

  5. The above report will query Salesforce through the API with the most recent inquiries and applicants from that particular import.

  6. Be sure to include a time frame for the inquiries and applicants to capture how inquiries and apps converted during the Email Campaign. (This should be a task in Wrike Query period start of campaign until 2 weeks after). It should be printed as a stand alone pdf report.

  7. Regular pdf report to assess conversions over time (Is there a specific cadence of conversion reporting that we would like?)

  8. Share conversion performance with Tracy St Pierre as the first step in planning the upcoming purchase.

  9. Stop here for the day.

  10. Compare the results of this search with final reports of past searches. (1 - 1 1/2 hours)

  11. Stop here for the day.

  12. Analyze findings as is and then in a historical context.

  13. Stop here for the day.

  14. Write draft 1 using report template.

  15. Stop here for the day.

  16. Run through report check list.

  17. Stop here for the day.

  18. Edit report for final report.

  19. Stop for the day.

  20. Run through checklist.

  21. Stop for the day.

  22. Read report for clarity.

  23. Stop for the day.

  24. Final read through.

  25. Stop for the day.

  26. Submit to TSP via Wrike.

2. Buying Search Names

Availability & Recommendations

Tools:

  1. For CB availability.

  2. For NRCCUA Availability.

  3. Reference Sheet Search Criteria: This includes standardized test parameters for in-state and out-of-state as well as an outline of Primary, Secondary, Tertiary, and Quaternary territories.

  4. College Board Data Load Schedule: This has the release dates and is helpful for determining when there will be the most lead names available.

  5. Wrike: Multi-Channel Project to verify if this is an email AND print campaign. This will guide the purchase and prevent future confusion with numbers.

Steps:

  1. Create a project in Wrike from the Blueprint called Analytics Search

  2. Save this project to Data Analytics: Active Projects>FY2X Search

  3. Create Folder Structure

  4. Check Availability: In order to check availability, we have to proceed as if we were making a purchase.

  5. Here are some notes from the Segmentation Criteria document to refer to Note in SAT Scores, there are subtle differences with in-state and out-of-state

  • SAT:

    • ME SAT test takers with scores between 800 – 1400
    • MA, NH, CT SAT test takers with scores between 800 – 1200

  • PSAT

    • ME PSAT test takers with scores between 720 – 1520
    • MA, NH, CT PSAT test takers with scores between 720 – 1300

  • AP SCORES 3+

  • Primary Market

    • All Maine Counties

  • Secondary Markets

    • Massachusetts

      • Middlesex County

    • New Hampshire

      • Hillsborough County
      • Rockingham County
      • Strafford County

  • Tertiary Markets

    • Massachusetts - All Other Counties
    • New Hampshire - All Other Counties
    • Connecticut - all counties (if cuts necessary, cut less populated and/or non-border towns first)

  • Quaternary Markets

    • Vermont

    • New York: Westchester which borders Fairfield, CT, an area with a lot of travel fairs.

Calculating Final Costs for Search Campaign:

After the print job for the search campaign is complete, TSP needs to calculate the total cost of the campaign. She wants it broken down by class. This requires accessing the original CB downloads, merging them, creating 2 fields, Class (SR and JR/PH) and Cost (Class * 0.47, at the time of this search), and summarizing when requested. There is an R script that does all of this. The original downloads will be in the download sub-folder of the Search Campaign. Because NRCCUA, Niche, and Cappex were different purchases, they are not included in this task.

Add to the task description and respond to the task by tagging the parties involved.

Note: The cost requested is the total cost of the purchase, not the cost of the number of names downloaded.

Time required: 1-1.5 hours

The task is completed in Wrike and will resemble this.

Search Campaign Final Cost Task

Sample:

KPI

Checking the Quarterly Ad and Web KPI is a screening tool of sorts to assess the health of our campaigns. We have selected a variety of metrics that provide a high level view. Campaigns are grouped by Product and Audience. This report only applies to display, video, and email ads; it does not include Search (CPC) ads.

Product/Audience

  1. HSUG: In-state high school students seeking an undergraduate degree as traditional first-years.

  2. TLUG: Out-of-state high school students seeking an undergraduate degree as traditional first-years.

  3. WADC: In-state working adults seeking to complete (or start) an undergraduate degree.

  4. CUTR: Mostly In-state (and sometimes out-of-state) current undergraduates seeking a transfer.

  5. CUGR: Mostly in-state current undergraduate students seeking s graduate degree.

  6. WAGR: In-state working adults seeking a graduate degree.

  7. AllUNI: Umbrella campaign promoting the university to all audiences. Rinck also uses this audience for re-targeting purposes based on user click data.

  8. AllOL: All audiences seeking a graduate or undergraduate degree online. This is relatively new.

Metrics

  1. Impressions: An indication of our in-market presence. When looked at against cost, can also indicate budget optimization.

  2. Click-through-Rate (CTR): This is very high level because will vary depending on the quarter’s ad portfolio. Each vendor and medium has its own benchmark CTR. However, we do like to look at the overall CTR because the portfolio balance is fairly consistent.

  3. Sessions:

Quarterly KPI

Tools Required:

  • Filemaker Vendor reports available on Z drive,

  • R Studio data merge and data tidying script (see here Users>christine.iyer>OneDrive - University of Maine System>FY21_Ad_Vendor_Reporting>MergingFilemakerVendorReports.Rmd),

  • Tableau Workbook (Users>christine.iyer>OneDrive - University of Maine System>FY21_KPI_Initial_Setup>FY21_Quarterly_Web_Form_Inquiries_from_CLP>fy21_quarterly_web_form_inquiries_from_campaign_landing_pages/QKPI.twb

Time Required: 10-16 hours over 1 1/2 weeks

Step 1: Open project in Wrike and follow all steps.

Step 2: Tidy data.

Step 3: Refresh dataset in above referenced Tableau workbook.

Step 4: Following Marketing Analytics Report structure, arrange report by campaign.

Step 5: Edit rough draft.

Step 6: Wait a day, edit final report.

Monthly Web Activity

Tools: Tableau, Wrike

Time Required: 3-6 hours over 4 days

Audience Builder

Tools:

,

Select any report to view the query

Building Audiences for Print Marketing

The Marketing Data Analyst will also need to customize “Reports” for Print campaigns. There will often existing reports for prior year campaigns which can be cloned and modified. The DA’s responsibility varies, sometimes reports will be created in SF by John Tabor, and the DA will just need to tidy it up and dedupe. In that case, the report will often be located in the “Needs to be Proofed” folder.

Time Required: 1-4 hours over 2 days (max)

Tools Required: Salesforce, Excel, Wrike, Microsoft One Drive

Step 1: Open request in Wrike which will explain all of the parameters for a particular report.

Step 2: Reach out to Harrison Warren in Print Marketing for clarification if needed.

Step 3: When building reports, include mandatory First Name, Last Name, Primary Address 1, Primary Address 2, Primary City, Primary State/Region, Primary State Code, and Primary Zip Code.

Step 4: Be careful when deleting duplicates. Should not be done on email address and students who have 2 addresses may require correspondence sent to both places.

Step 5: Proof read list.

Step 6: Save to Direct Mail folder.

Step 7: Complete task in Wrike by tagging Harrison with the attachment.

Building Marketing Cloud Data Extensions

Time Required: 2-4 hours over 2 days

Step 1: Open 4 tabs on screen

  • to create new DE

  • to create a new automation

  • that was used previously

  • May also reference that provides a description of each campaign.

Step 2: Salesforce: copy and paste query into Salesforce Reports to Marketing Cloud Conversion. Make note of the number of records in this list/report Keep tab open for field names in SF

Step 1: When creating a new audience, the workflow begins by initiating documentation for this new report in the Salesforce Reports to Marketing Cloud Conversion

Step1: Find most recent query that defines the audience in Salesforce.

Step 2: Navigate to Email Studio > Create New Data Extension. When naming the new DE, use the exact name given by Wrike task, replacing spaces with underscores. Select same fields used in a SF report. May create new fields if necessary. This is particularly helpful when exploring why some fields don’t work. If the DE will be filtered, create a new filter, name it, ans save.

Step 3: Be mindful of data types, character lengths, primary keys, nullable fields, and contact ID and subscriber ID. Fill out all of these fields.

Step 4: Keeping Step 3 tab open, open a new MC tab and navigate to Journey Builder> Automation Studio. Create new automation.

Step 5: Drag schedule icon and set to daily refresh for 9:30 am

Step 6: Drag SQL to next step. Sample queries are here. Create New Query Activity FA21 HS Yield query has an example of creating a new filed, which should have been added to the DE in Email Studio.

Step 7: Validate query. Save and run.

Step 8: Make sure DE automation is ACTIVE.

Step 8: If wish to add a filter activity, a filter has to be created as a separate activity and saved with a name.

Step 9: Go back to Email Studio and wait for it to populate.

Note: If this DE is the provides the entry source for a “Journey”, the Journey MUST be paused when building or editing the DE, otherwise an email could go out to an unintended audience. Pausing is done in coordination with the Email team in the Department of Marketing and Brand Management.

Filtering Data Extensions:

There are 2 ways to filter DE, although both require building the DE as outlined above and creating a filter/filters.

Create a Data Filter:

  1. . This step can be postponed until after a DE is created, if the FDE will be refreshed manually.

  2. Choose Create on the upper right.

  3. Choose Data Extension option.

  4. Select the Source Data Extension.

  5. Drag and drop fields to filter and define.

  6. Save, name, choose folder. Note exact name and location because you’ll need to refer back to it.

Manually Refreshed Filtered Data Extension: As mentioned above, a data filter can be made as the next step of the Data Extension creation process (before or after DE has been created and populated). Follow the above steps when creating the filter activity later.

If making the filter as part of the DE, once the DE has been populated, choose the filter activity on the upper right and follow the same process as above. Save as an FDE.

Auto Refreshed Filtered Data Extension: Recall, an automation is required to populate a data extension. A filter activity can be incorporated into this automation process of populating the DE, making it a Filtered Data Extension that will be refreshed on the schedule set in step 1 of the automation. After completing the SQL query for the DE, from left side menu, choose Filter Activity. Select the filter that was built in the Create a Data Filter step above. Schedule, save, name, select folder location, and run the automation. In the DE folders, there will be a DE and an FDE with the same name. Verify that the number of records in each is correct.

Data Extensions in Journey Builder:

All Journeys require a data entry point which will usually require some kind of filter activity. This activity may be part of the automation that populates the DE, or may be part of the Journey. This will be determined in conjunction with the email team.

Important Principals

Deleting Contacts

Time Required: 4-5 hours over 3 days

Important Concepts:

All Subscriber vs. Contact List

The All Subscriber List in Email Studio is a running ledger of the Subscribers who have been sent an email communication from Marketing Cloud. If the subscriber lives within a Data Extension that is deleted or removed, the Subscriber will still live on the All Subscriber List.

The All Contact List within Contact Builder takes Subscribers from Email Studio along with synchronized Contact and Leads, as well as Subscribers from Mobile Connect. Essentially, All Contact will take the various “Contacts” across the channels utilized within Marketing Cloud and combine them into one list – the All Contact List.

All Subscribers from the All Subscriber List will be found on the All Contact List but there is a potential that not every Contact on the All Contact List to be found on the All Subscriber List.

  1. Monthly check on contacts On left menu, choose Email. A parenthetical number will reveal the number of active subscribers/contacts.

  2. When contacts > 180,000, make plan to delete contacts.

  3. Notify Marketing Cloud channel on Slack and John Derouche

  4. In order to begin the process of deleting Contacts from Marketing Cloud, it is important to first establish a way to identify those Contacts who should be removed. Typically, these are inactive Contacts, who no longer are showing interest or are not engaging with email communications. It may also be Subscribers who have globally unsubscribed from all communications.

  5. Review

  6. Once the criteria for identifying Contacts to be removed has been removed, enable Contact Delete option within the Parent BU. To enable this feature, follow these steps:

  • Navigate to Audience Builder > Contact Builder.

  • Along the top navigation, click Contacts Configuration.

  • Under the Contact Delete section, select On.

    • Click Manage Settings to adjust how long the Contacts remain in a suppressed status. Contacts in a suppressed status will not be deleted from Marketing Cloud until the number of days is up. They will, however, be suppressed from ALL email communications during the suppression period. The default is set to 14 days but can be as little as zero days or as many as 30 days.

Note: in order to delete a Contact from Marketing Cloud, it must be done within the Parent BU Contact Builder Deletion In order to follow the below Contact delete process below, a Data Extension will need to be created to hold the Contacts that should be deleted. How this Data Extension is populated will be determined by the strategy that is established. It is important to note that the Data Extension must be saved as a Standard Data Extension.

  1. The Data Extension will need to include the Subscriber Key in order to properly delete Contacts.

  2. There is a template for this DE that can be reused. It has most, if not all of the required fields to identify who will be deleted and the fields required for deletion. Once the Data Extension is created and populated with the Contacts to delete, follow these steps:

  3. Navigate to Audience Builder > Contact Builder.

  4. Once in Contact Builder, navigate to All Contacts. Here, a trash can icon will be in the top right corner. Do not select any contact but click directly on the trash can and select Delete contacts from data extension.

  5. Find the Data Extension that storing the Contacts to delete and select it. Do not check the option to delete both contacts and data extension as this would also delete the structure of the Data Extension and would need to be rebuilt with each data cleansing done. Click Delete Contact.

  6. Once Contacts are deleted from the All Contact List, they are also removed from the All Subscriber List.

Email Reporting

This is a work in progress. We are still looking for ways to streamline pulling monthly reports with all of the metrics we want. Until this is done, there are many workarounds.

Whether an email campaign is created through Email Studio or Journey Builder, the measurements on which performance is based are the same, however in order to retrieve email results from Marketing Cloud, we have to look in different places.

Tools: i.e., (MC Dashboard > Analytics > Reports > Catalog)

There are various routes to obtaining Email Performance Reports from the Marketing Cloud platform and it can be quite confusing to determine which path is best. For the most part, the reports have the same metrics.

Time required: 16 hours, TBA as steps are finalized.

Here are some of the most useful.

Email Reports in Marketing Cloud

Email Studio: Tracking Report

From the MC dashboard, The tracking link gives the following message… “The available Reports in Email Studio are no longer updated. Use Analytics Builder Reports for up-to-date Email Studio reports, Content Builder email reports, and more enhanced functionality.” From this path, locate the campaign on the left side menu for which you need a report. If the campaign is ongoing, there will be an up to date report on each send in the campaign. Below are sample snap shots. If this is the only way to retrieve the metrics for each email in a campaign, use a workaround by copying and pasting the tracking reports below.

The disadvantage of this route is that it is not exportable as a csv.With persistence, data can copied and pasted multiple times onto an excel spreadsheet, and then tidy up the sheet by removing images, check boxes and links. The process for doing this is:

Delete all pictures in active worksheet with Go To command

  1. Press F5 key to open the Go To dialog box in Excel.

  2. Click the Special button, Go To Special dialogue box Object

  3. And then click OK button, all pictures are selected in active worksheet, press Delete key to delete all of them.

  4. Remove hyperlinks by highlighting the boxes and right clicking.

This is a sub-optimal solution for a preventable problem. If an email campaign is properly labeled in Marketing Cloud, then all of the performance data is exportable in a csv format.

Email Studio: Sends Tracking

Each single email can be clicked for a drill down of performance. Within this drill down, there are 2 key reports, shown below.

The first can be exported as a pdf and provides visualizations of KPIs.

The second, which can be exported as a csv, provides the metrics of each of the links clicked within the single email send.

Email Studio: Email Send Report

This report can be done for a selected a date range, but will just give an overall summary of the sends as a single aggregate. It is not particularly useful.

Analytics Builder: Campaign Email Tracking Summary:

Gives an overview of the campaign as a whole, not each individual send. Disadvantage is that it has to be run separately for each individual campaign.

Browser view

Download view

Analytics Builder: Campaign Email Job Tracking Summary:

This is probably the best report, but again, it is campaign specific.

Browser view

Download view

Analytics Builder: Campaign Email Tracking Report:

This gives individual emails during the specified time frame.

Browser view

Browser view

Automation Studio: Data Views

The Marketing Data Analyst can create a broad report that summarizes all campaigns as far back as 6 months. This is a complex process and requires all of the same steps outlined above in Building Marketing Cloud Data Extensions with some additional steps referencing MC schema building and finally table joins and data tidying in R Studio. To access all KPIs, we use 8 different data views.

Tools: Marketing Cloud, Marketing Cloud Help Documentation, SQL, FTP, R Studio

Time Required: 2-3 hours to build a single data view.

Step 1: Build Data Extension as outlined in Building Marketing Cloud Data Extensions. Follow the mandatory schema for each view. A list of the schema available are available in the help docs linked above.

Step 2: Create a new automation for the individual Data View using SQL. Because it will include all available fields, the query might be as simple as SELECT * FROM _Placeholder. Tables available are outlined in the link previously referenced.

Step 3: Schedule the automation to run monthly on the 15th day of the month.

Step 4: Verify that the DE has populated.

Email Metrics Dataset

Step 1: Export all Data Views Data Extensions

Step 2: Using the R script here, tidy the data. Users>christine.iyer>OneDrive - University of Maine System>MigratedFromBox>FY21_Email>FY21_DataViews>DataViewsTidy.Rmd

Step 3: Upload new csv to Tableau for dashboard visualizations. Still being built.

Email Metrics Dashboard

Tools: Data Views, R Studio, Tableau

Time Required: In development

This is still a work in progress. Mock up report is available

3rd Party Inquiries

Each agency typically each gets its own report, but I combining them for the purposes of this document.

Tools: Salesforce Reports, Tableau, R, Wrike, Gmail, Excel

Time Required: 16 hours over 2 weeks

Step 1: Create new Project in Wrike from Blueprint with due dates.

Step 2: Create new project folder structure.

Step 3: Review past documentation in Wrike and Gmail for contract dates and times.

Contracts should have been saved here

\(\text{\underline{C:>Users>christine.iyer>Box>FY21 Inquiries>FY21ThirdPartyInquiriesContracts}}\)

Step 4: Gather raw data (2 different reports) from Salesforce using initial referral source and any referral source

We use the initial source to measure conversions, but very often the vendor uses any referral, so we may have to refer back to the any referral of that vendor. Save in File Path:

\(\text{\underline{C:>Users>christine.iyer>Box>FY21 Inquiries>FY21ThirdPartyInquiries>Raw Data}}\)

Note: Very important to de-dupe. Referral reports have tons of duplicates.

Step 5: Tidy Data using either script template in R-Studio or Tableau.

Step 6: Cross Tab various sheets tidied in Tableau and join together. Calculate proper number of Inqs, Apps, Admits, Confirms, Enrolls using the SUM Operator across the row.

Step 7: Draft report using the Basic Description, Findings, and Conclusions format. Use title, date, and page numbers.

Step 8: Be sure to include contract details as part of the project description Report can be done in R markdown if want tidying and visualization as part of the report. Report Example: Users>christine.iyer>Box>FY20_Inquiries>FY20_SpecialUpdate_3rdPartyInquiries>CPPC HBSN Comparison>CPPXHBSNComparison_BEST.pdf

Can also be done in Microsoft or Google Docs. A sample report can be found at this file path.

Step 9: Submit completed report to Tracy St. Pierre for review via Wrike by tagging here in the task. Change rough draft task from “In Progress” to “In Review”.

Step 10: Once TSP has reviewed the draft and made edits and suggestions in Wrike, complete the edits and resubmit, bu tagging her in Wrike with the final draft task.

Note: Managing NRCCUA Declared Student Inquiries

Tools: Gmail, Wrike, One Drive

Time required: 15 minutes

If there is an existing contract with NRCCUA for declared students, there will be regular emails from NRCCUA throughout the contract period. It’s important to ensure they are uploaded into the SF database without delay. When an email arrives from NRCCUA with Declared Student Inquiry in the subject line, deal with it immediately.

Reference

Data Automations

The data extensions used for ongoing email broadcasts are all scheduled to update daily. However it is very easy to pause an automation of the scheduled update and happens inadvertently on a regular basis. Therefore, the Data Analyst needs to check for paused automations weekly, on Mondays. To check for paused automations, go to the MC dashboard>Automation Studio>All Automations. Set filter for Paused Automations. Scroll carefully to note any current paused campaigns.

Tableau Calculated Fields

Below are some of the most frequently used calculated fields.

Calculated Field Name Calculated Field Query
Bounce Rate (SUM([Bounces])) / (SUM([Sessions]))
Average Session Duration SUM([Session Duration])/SUM([Sessions])/86400
Average Time on Page [Step 1]/ 86400
Pages per Session (SUM([Pageviews]))/(SUM([Sessions]))
Step 1 (SUM([Time on Page]))/((SUM([Pageviews]))- (SUM([Exits])))

There is a Tableau workbook with preconfigured calculated fields which can be copy and pasted into any Tableau workbook. It’s here, “C:/Users/christine.iyer/OneDrive - University of Maine System/Data Analytics Workflow/Resources/Tableau Calculated Fields.twb”

Version Control

  1. Go to BitBucket
  2. Create New Repository on Bitbucket
  3. Open Bash
  4. CD to folder that will house the directory locally
  5. Clone repository into this folder using copied command from remote repository site.
  6. Add files using command “git add .”
  7. Commit files to the repository using git commit -m “Add updated commit message.”
  8. Commit changes using “git push”

FTP

  1. Locate user name, password, and url for the file site: MC Dashboard>Setup>Data Management>FTP Accounts

  2. Open FileZilla

  3. Quick Connect using the information from previous step. Make sure url is preceded with sftp.

  4. Verify files have downloaded into local FTP folder that has already been set up.

Data Analyst Report Structure

Title:

Author: Dept. of Marketing and Brand Management

Date: FY21 …

Revised Date: if necessary

Basic Description:

Findings: tables, plots, text

Conclusions:

Include page numbers