Using Google Sheets to Develop and Track Media Campaigns


This post was originally posted on JEA Digital Media on January 18, 2017.

When we plan to execute full-scale media campaigns, it requires a lot of planning. Now that we’ve partnered with our school athletic office to serve as a public relations team for our sports, we’ve had to up our game on being detailed oriented and organized. To do this, we’ve created an intricate system on Google Sheets to develop and track our campaigns.

This document is broken down by months and days, as well as “Planning and Evaluation” and “Media and Activities.” The Account Executives (AE) are asked to consider every aspect of a campaign from conception to reflection and document that at the beginning. They then use this campaign timeline to guide the program to success. This step-by-step guide is built to reflect working with a client; however, it could be modified to be used with any large-scale media campaign for a media program.

  1. Create an initial “Campaign Timeline” and title it “Sport Campaign Timeline”
  2. Start by setting a client meeting.
  3. Allow time to process and plan, then set a formal campaign pitch session with the program adviser and Public Relations Director. This will allow for them to provide feedback on the variety of activities you’ve created and media goals you’ve set.
  4. Set the formal launch day. From this point forward, the campaign is in full effect and this is where organization is really important.
  5. Identify dates where you would like to take the time to assess and evaluate message effectiveness. This means looking at insights and analytics to craft stronger, more effective messages after that point. This could be done weekly, bi-weekly, or monthly based on the length of the campaign. We attempt to complete analytics bi-weekly.
  6. Provide an end point of the campaign. This helps the AE to work within a specific time parameter, and when the campaign needs to be evaluated as a whole.

In the document, all of these events are labeled with a colored box located in conjunction with the event and the exact date. This visual representation is not only used by the AE to stay organized, but it is also used for the Public Relations Director and adviser to see what is being done, when, and how. The steps that follow are all included in the “Media and Activities” section.

  1. Any event you would like to create should be listed on the left-hand side of the Google Sheet under the “Media and Activities” header. For example, “Facebook post (text only): “Comment your favorite moment from last night’s game against ___.” This can be repeated throughout the entire campaign simply by placing a colored box on the data of the posting.
  2. Continue to develop a variety of events–as discussed previously with the coach, PRD and adviser–and include them into the document.
  3. The labeling should be specific and clear to know which media channel this is coming from, how it is being provided and where it could be seen. We use the comments feature on Sheets to provide information on the later two pieces.
  4. Print the document and save it electronically in a shared location for accountability.
  5. Make a copy of the “Campaign Timeline” and label it “WORKING Sport Campaign Timeline.” The working timeline is what you will edit and work through over the course of the campaign. The original document will be used for comparison at the conclusion of the campaign.
  6. As you track, use a specific color to denote if the campaign goal was met and another if it was missed. This will allow for further reflection at the conclusion of the campaign. These colors should not be used in the document any other time. In order to maintain consistency across AEs, the colors should be standard; we use red for missed and blue for met.
  7. At the conclusion of the formal campaign, all media and activity should be collected digitally (or in print) in the form of a media portfolio. This portfolio includes all evaluations, media messages and products, and media tracking documents. Some AEs will also link these materials using comments so that their “Campaign Timeline” is actually a live document. This helps tremendously when it comes to evaluation.
  8. A variety of reflection questions are then used to guide the AE to successful evaluation of the campaign as a whole.