Content planning spreadsheet

bernard

BuSo Pro
Joined
Dec 31, 2016
Messages
763
Likes
580
Degree
2
I'm building an interactive Google Sheets for my content planning, now that I've begun outsourcing for real.

I want to dynamically track every important metric regarding that content.

Here are the things I plan on including in my sheet:

Basics
  • Title
  • Published URL
  • Keywords Targeted
  • Wordcount
Author Details
  • Author
  • Author contact
Order Details
  • Wordcount
Earnings
  • Total Earnings (dynamic update from network API)
  • Earnback Period (semi-dynamic check until cost met, then add days)
Traffic Details
  • Search Traffic (dynamic from Search Console)
  • Referral Traffic (dynamic from Analytics)
  • Earnings pr. search visitor - All Time (Earnings / Search)
  • Earnings pr. search visitor - Last 30 Days

I want to 1) understand which content gives return (author, keywords, wordcount etc. and 2) check which content is underperforming to a historical average.
 
Joined
Apr 12, 2019
Messages
106
Likes
163
Degree
1
Nice plan. How long of a period are you going to give an article to determine whether or not it was worth it?
 

bernard

BuSo Pro
Joined
Dec 31, 2016
Messages
763
Likes
580
Degree
2
Nice plan. How long of a period are you going to give an article to determine whether or not it was worth it?
I'll give it a year, which is a long time, but I'll also make sure to actually market each post. In any case, more than a year and risk also sets in. I do think most will earn themselves back in no more 6-8 months considering I do proper keyword research and marketing.

Other things I got to thinking about:

Projected value:

  • Ahref traffic value for main keyword

then compare with actual value.
 
Joined
Dec 11, 2018
Messages
163
Likes
132
Degree
1
Looking good! For Google Sheets, is there like a scripting interface that you use to build this?
 

bernard

BuSo Pro
Joined
Dec 31, 2016
Messages
763
Likes
580
Degree
2
Looking good! For Google Sheets, is there like a scripting interface that you use to build this?
Yes, Google App Script, which is a javascript version and works much like VBA did for Excel back in the day.

It can do almost everything that a server side script can with some exceptions, notably dealing with proxies, callback and non-REST apis.

It's great for this kind of work though, as there are already many plugins for most webtools around, so that you can easily get Search Console data into your sheet or whatever other tool you have data for.