Content Calendar & Google Sheets - Step-by-Step Guide

Required:

Google Sheets
Google Search Console
SEMRush API

Create the a Google Sheet file with the following named sheets:

Calendar
Content Approved
Content Idea
Snippets
Stats
Page Analysis
scratch pad
KW Data

https://bristolseo.com/wp-content/uploads/2019/06/Screenshot-2019-06-20-at-11.30.25.png

Calendar - Column names

Publish Date
Day
Author
Title
URL
Content Type
Description
Keywords
Social Media
Published

=CHOOSE(weekday(A4), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

=iferror(vlookup($A4,'Content - Approved'!$A:$AF,columns($D4:D4)+2,FALSE),)=iferror(vlookup($A4,'Content - Approved'!$A:$AF,columns($D4:D4)+3,FALSE),)=iferror(if(REGEXMATCH(F4,"Email|Slide|Social|Video"),"N/A",(CONCATENATE("http://www.domain.co.uk",(vlookup($D4,'Content - Approved'!D:P,columns($E$34:$E$130)+10,FALSE))))),)=iferror(vlookup($D4,'Content Ideas'!$A:$Z,columns($F18:F18)+1,FALSE),)

=iferror(vlookup($D4,'Content Ideas'!$A:$Z,columns($F18:F18)+5,FALSE),)

=iferror(vlookup($D4,'Content Ideas'!$A:$Z,columns($F4:F4)+8,FALSE),)Snippets!G4:G

U25pcHBldHMhSDQ6SA==

Content - Approved

Publish Date
Days Left
Sign Off
Author
Title Tag
Content Type
Category
SERP Feature
Description
Resources
Content Length (Words)
H1
H2
Subheadings
URL
Internal Link
Internal Link Keyword
Images Required
Twitter Description
Twitter Title
Facebook Description
Facebook Title
LinkedIn Description
LinkedIn Title
Published State

=if(ISBLANK(A3),"",(networkdays(Snippets!$M$4, A3))-5)Snippets!B5:B

U25pcHBldHMhSzQ6Sw==

=iferror(vlookup($D3,'Content Ideas'!$A:$Z,columns($D3:$D)+1,FALSE),)=iferror(vlookup($D3,'Content Ideas'!$A:$Z,columns($D3:$D)+2,FALSE),)=iferror(vlookup($D3,'Content Ideas'!$A:$Z,columns($D3:$D)+4,FALSE),)=iferror(vlookup($D3,'Content Ideas'!$A:$Z,columns($D3:$D)+5,FALSE),)=iferror(vlookup($D3,'Content Ideas'!$A:$Z,columns($D3:$D)+10,FALSE),)

=iferror(vlookup($D3,'Content Ideas'!$A:$Z,columns($D3:$D)+8,FALSE),)=iferror(vlookup($D3,Calendar!$D$34:$J$1030,columns($X3:X)+6,FALSE),)

Content Ideas

Title
Content Type
Category
Buyers Stage
SERP Feature
Description
Resources
Comments
Keywords
Suggested by
Word Count
Date
Status

U25pcHBldHMhRTQ6RQ==U25pcHBldHMhRDQ6RA==U25pcHBldHMhRjQ6Rg==U25pcHBldHMhUjQ6Ug==J0NvbnRlbnQgSWRlYXMnIUFBNDpBWQ==U25pcHBldHMhTjQ6Tg==U25pcHBldHMhSTQ6SQ==

Snippets

Content Creators
Cost per word
Categories
Type
Buyer Stage
Social
Published
Suggested
Content Approved
Not Selected Content
API
Date
Suggest By
Keywords Chosen
Dates as Strings
SERP Feature

=unique(filter('Content Ideas'!A4:A,'Content Ideas'!M4:M="Approved"))=iferror(filter(J4:J1000,isna(match(trim(J4:J1000),trim('Content - Approved'!D3:D),0))))

PXRvZGF5KCk=

=FILTER(Calendar!H38:H, NOT(Calendar!H38:H = "") )

Stats

Month
Content Cost
Category
Published
Content Type
Published

=Snippets!D4=COUNTIF('Content - Approved'!$F$4:$F, D5)=Snippets!E4=COUNTIF('Content - Approved'!$E$4:$E, G5)

Page Analysis

URL
Last Modified
Frequency
Priority
Title
Title Length
H1
H1 Length
More Info

=IMPORTXML("https://www.domain.co.uk/sitemap.xml", "//*[local-name() ='url']/*[local-name() ='loc']")

PXN1bShjb3VudGlmKCdDb250ZW50IElkZWFzJyEkSSQ0OkksQjMpKQ==PXN1bShjb3VudGlmKCdDb250ZW50IElkZWFzJyEkSSQ0OkksRDMpKQ==PXN1bShjb3VudGlmKCdDb250ZW50IElkZWFzJyEkSSQ0OkksRjMpKQ==PXN1bShjb3VudGlmKCdDb250ZW50IElkZWFzJyEkSSQ0OkksSDMpKQ==PXN1bShjb3VudGlmKCdDb250ZW50IElkZWFzJyEkSSQ0OkksSjMpKQ==PXN1bShjb3VudGlmKCdDb250ZW50IElkZWFzJyEkSSQ0OkksTDMpKQ==PXN1bShjb3VudGlmKCdDb250ZW50IElkZWFzJyEkSSQ0OkksTjMpKQ==PXN1bShjb3VudGlmKCdDb250ZW50IElkZWFzJyEkSSQ0OkksUDMpKQ==PXN1bShjb3VudGlmKCdDb250ZW50IElkZWFzJyEkSSQ0OkksUjMpKQ==