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

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)

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

Create 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

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.

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.

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.

This completes the tracker setup!