Content planning spreadsheet

[bernard]

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.

 

[JamaicanMoose]

Nice plan. How long of a period are you going to give an article to determine whether or not it was worth it?

 

[bernard]

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.

 

[Poplanu]

Looking good! For Google Sheets, is there like a scripting interface that you use to build this?

 

[bernard]

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.