Free Social Content Calendar Template - Google Sheets

Had some down time at work and decided to play around with Google Sheets to create a social content calendar.

Here's the link to the file:

Link to free social calendar template in Google Sheets

Read on for the explanation as it uses some formulas to calculate the budget allocation on a per post basis.

View fullsize

Here's how the calendar works:

Input sheet:

Created lists that can be used as drop-down [via data validation] in the sheets per market [Created two sheets...AE and SA market].

• List one is for selecting the month of the year [2018]

• Two markets: AE and SA

• Focus: This applies to the post as in what's the post about...setup examples here...so, Product 1/2/3/4 or occasion post, competition or campaign post

• Objective: This will help decide what should be the post promotion objective when it's done in Facebook Ad Manager [as an example]

• Promoted? Yes/No

• Type of post: Photo/link/video

Budget sheet:

• Number of posts [per market and post type] is automatically calculated using a Countif formula. Example: AE row/Product 1 column uses this formula: =iferror(countif(AE!$C$12:$I$93,E$8),0) ..the formula checks the AE! sheet for cells where the value is equal to E$8..."Product 1"...once it does, it's a count function...if there's an error by any chance, the iferror formula converts the result to 0. Drag and drop this across rows 9/10 and it will automatically let you know how many posts are there for each product type and by market.

• Based on this, the desired product split can be entered at a market and product level. In the template, I have given 40% to AE and 60% to SA while both markets have different product splits based on what's entered in the calendar.

• Lastly, the budget per post is determined by calculating the spend [per post] and dividing it by the number of posts for that particular product type/market.

Calendar sheet [using AE market as an example]:

• Choosing a month from the drop-down will automatically change the starting date of the calendar [for that row] and the entire month. The calendar is organized by weeks.

• Automatic budget allocation based on allocation entered in the budget sheet: This uses a formula that looks like this =iferror(index(Budgets!$E$23:$K$24,match($B$4,Budgets!$A$23:$A$24,0),match(C29,Budgets!$E$22:$K$22,0)),"No content")...What it does is:

  • Index/match formula where the return values in the index bit of the formula are in budgets sheet, E23 till K24...this is the budget per post allocation.

• Row bit of the match formula looks up the row value in B4,,,that is the market name [AE] and checks this in the budget sheet column A23 till A24...

• Column bit of the match formula checks for value in C29 [in this case, 'Occasion' post] and tries to find the column in the budget sheet E22 till K22 as it has the various product names.

• Once these coordinates are found via the right row/column, it creates a cross-hair to find the right value...in this case, it's $20 that should be ideally allocated to the occasion post.

• This value is returned in the AE! sheet C39 cell. Right under it, there's an option of giving more / less budget after seeing the creative and making a judgement call on whether the post deserves slightly more/less budget. The total amount entered is then calculated and compared to the budget allocation sheet to know if there's anything that's excess/under budget in terms of actual allocation compared to the budget allocation that was calculated.

Two additional aspects via conditional formatting:

• If the post status is changed to 'Approved', then the cell turns from red to green.

• If the post status cell is not changed to 'Promoted', then the cell turns from green to red.

Please let me know via comments if you found the template helpful. If it can be improved, please do share your thoughts on how would you execute this task in Google Sheets.

-adil