Home / Blog / Digital PR /

How To Create An Outreach Tracker In Google Sheets For Digital PR Link Building

Carl Burton from NovosCarl Burton in Digital PR

4th October, 2021

At my recent MeasureFest talk, I gave tips to help improve efficiency using Google Sheets. One of these tips focused on time-specific triggers and included an example of creating an outreach tracker that automatically updates. Here we are going to follow through with this idea and create a fully functioning tracker using Google sheets.

With a large chunk of a Digital PR’s time being spent on outreach, it’s important to be organised. Once this tracker is up and running, you can use it to help track your outreach efforts, ensuring you don’t spam contacts and have a full history of your efforts. This can also be shared between the whole outreach team so that duplicate outreach is avoided. 

I’ll go through step-by-step so that you can understand the inner workings and feel comfortable modifying it to your liking. If you would just like the finished template, however, you can find it here.

We’re going to create 3 tabs in total

  • Tab 1: To track each conversation
  • Tab 2: To hold overall information for the client/project
  • Tab 3: For the template outreach emails


Let’s get started!

The Tracker Tab (part one)

Making digital PR outreach tracker in google sheets

The first tab will be the main input area, label this tab “Tracker”.

Add headers for nine columns:

  • Client (Drop-down) – The name of the client the outreach is for
  • Topic/Project (Open text) – The name of the topic/project
  • Name (Open text) – The name of your contact
  • Contact (Open text) – The email address of your contact
  • Last contact date (MM/DD/YY) The last time you emailed the contact
  • Stage (Drop-down) – The current step in the outreach process
  • Follow-up date (MM/DD/YY) The date of the next time to reach out (updated automatically)
  • Status (Drop-down) – The current standing of the potential link
  • Notes (Open text) – Any additional information

 

The Client Overview Tab

 

How to create digital PR outeach tracker in google sheetsCreate a new tab labelled “Client Overview”.

Add headers for three columns:

  1. Name (Open text) – The name of the client
  2. Count of Live Links – The number of live links for the client
  3. Count of Rejected Links – The number of rejected links for the client

Add in your client names in column A.

Next, we’re going to add a countifs formula into column B.

=COUNTIFS(Tracker!A:A,A2,Tracker!H:H,“Live”)

This formula will look at the tracker and pull in the number of live links for each specific client.

Column C will be very similar but looking at rejected links.
=COUNTIFS(Tracker!A:A,A2,Tracker!H:H,“Rejected”)

Copy both of these formulas down to match the number of clients entered. 

That is the client tab completed.

The Templates Tab

How to create digital PR outeach tracker in google sheets 1

Next up, create a new tab called “Templates”.

The headings for this tab are:

  • Stage – The steps in the outreach process
  • Description – A brief description of the stage
  • Content – The content template for each stage

In column A, add each stage of your outreach process in separate cells. For the template we have used:

  1. Initial
  2. Follow-up one
  3. Follow-up two
  4. Follow-up three
  5. Restart
  6. Personal
  7. Rejected

In column B, add a description of each stage of your outreach process in separate cells. For the template we have used:

  1. The first time contacted
  2. Checking in to see if they got your message
  3. Checking to see if you have found the right person, if not, ask who would be the right person?
  4. Last check before moving on
  5. Start again after a delayed period
  6. The contact replied and we are in discussion
  7. No further opportunity

In column C, add the template of your outreach process in separate cells. This is blank in the example as it should be specific to your client.

The Tracker Tab (part two)

Back to our Tracker tab. In column A, we’re going to add a drop-down so we can select the client. To do this, highlight A2:A, click Data (in the top bar), then Data Validation. This will pop up a new window.

How to create digital PR outeach tracker in google sheets 2

Within this window, we’re going to update the criteria section. Most of this is already prepared for what we need, we just need to update the target range.

Click the grid within the criteria area, go to the “Clients overview” tab and select A2:A.

Click Save.

Now you have a drop-down containing each of your clients named in the “Clients Overview” tab.

How to create digital PR outeach tracker in google sheets 3

In column F, we are going to add a similar drop-down, but this time based on the stage section in the “Templates” tab. Follow the above steps but for column F, and this time select A2:A in the “Templates” tab as the target range.

The final drop-down column will be column H. Select Data Validation as above until the additional window opens. Here we are going to change the criteria drop-down to “List of items”. This allows us to add different options to the drop-down manually. In the next box over, add “Pending,Awaiting,Live,Rejected”, then Click Save.

Next up is a switch formula in Column G.

=iferror(SWITCH(F2,”initial”,E2+3,”Follow-up one”,E2+5,”Follow-up two”,E2+7,”Follow-up three”,E2+14,”restart”,E2+180,”personal”,”Check last reply”,”rejected”,”Rejected”),””)

This formula looks at what has been selected (if anything) in column F and performs a calculation based on the selected outreach stage.

Each stage in the drop down on column F is included as an option (this will need to be customised to your specific stages). From here the additional wait time (in days) is added to the last contacted date (column E). Customise the number of wait days to match your outreach process.

Copy this formula down to the last row on the tab.

Conditional Formatting

Now for conditional formatting. We will use this to colour column G one of three colours based on the contents of the cell and today’s date.

Highlight G2:G and click “Format” > Conditional Formatting. This will open a sidebar with some additional options.

Ensure the “Single colour” tab is selected, then use the drop-down (Format Rules > Format Cells if) to select “Date is before”. An additional drop-down will appear – select “Today” from this and change the formatting style to red. This will highlight any contact which is overdue.

Click Done. The sidebar should stay open (if not, re-open using the steps above).

Click “+ Add another rule”.

Follow the steps above, but select “Date is” in the format rules and select yellow for the formatting style. This will highlight any contact which is due today.

Add one last rule with the steps from above, but select “Date is after” in the format rules and select green for the formatting style. This will highlight any contact who does not yet need to be contacted again.

How to create digital PR outeach tracker in google sheets 4

This completes the tracker setup!

Using the Tracker

  1. Select your client from the drop-down in column A
  2. Type in your project/topic identifier in column B
  3. Add the name of the contact in column C
  4. Add the email address in column D
  5. Add the last time contacted in column E
  6. Select which stage you are at with the contact in column F
  7. Select the status of the potential link in column H
  8. Add any additional notes into column I

Each time you email your contact, update the tracker.

Using the tracker

You can format and style the sheets as you like and can customise the formula to allow for your specific situations. 

That’s it! You’re now all good to get started. In case you scrolled past the link above, you can get the outreach tracker in Google Sheets here.

Carl Burton from Novos
Article by Carl Burton
With years of experience in Digital PR and oodles of passion for data, Carl is NOVOS' Digital Marketing Consultant. Carl has an unbeatable knowledge of using google sheets for SEO and Digital PR and has given talks on the subject. He has helped create some amazing data-led campaigns for our clients and continues to nurture our data team.

Let's Grow Your eCom Brand with SEO and Digital PR

We're eCommerce specialists for a reason, get in touch with us today and find out more.

Get the latest blog posts and eCommerce tips in your inbox

Copyright 2021 Statement Group Ltd