Say you want to track your content creation, whether it be for business or personal use, but you don’t want to pay a fee to do so. I feel the same way with my personal branding. Why pay for something you can do yourself? Some say following your own DIY content calendar can be more time consuming than some of the tools out there. To be honest, that’s potentially true, but when you want total control over your calendar, there’s no doubt the extra time is worth it.
You can save time with your calendar when you set it up for easy tracking and maintenance. That’s why I created this 5,155 word guide – to help you save money by using Excel or Google Sheets for your calendar.
I’ve arranged this guide by the tabs or “sheets” I recommend. I use Google Sheets as the example for this guide, but you can still apply this to Excel if that’s the program you prefer.
Your DIY Content Calendar: The Calendar Tab
Content ID
This can be a code of sorts to help you track your content between the different tabs. It can be the creation start date or due date, such as 10-21-16, or it can be numerical in order of creation (1, 2, 3…). It depends on your preference and how much content you create on a regular basis. If you have a lot of content going out in close proximity to each other, the date approach might not work. If you’re like me and only publish once a week, the date approach might work best.
Take a look at the frequency in which you create content to determine how you will code each item.
I use dates as my coding system, but it ultimately depends on your own publishing frequency to determine how you code your content items.
Category
In your content strategy, you should have determined what your focus will be for your future content. This focus should be expanded into separate categories, which help organize your content by topic.
For example: my focus in general is marketing. My categories narrow down specific topics within marketing in general:
Blogging
Content marketing
Email marketing
General marketing
Personal branding
Social media marketing
Writing
In your DIY content calendar, you should add a column for these categories in your calendar tab. This will allow you to label each piece of content by what it’s about, which will help you and your readers find what you’re looking for with minimal effort.
I added a column for categories to my editorial calendar. This is an example with how it’s typically added and used.
Content Type
Your content type column is where you’ll select what your content is overall. This will help you find that balance between your different offerings. It will also help you with searching for a specific content type. If you want to know when the last time was that you published an eBook, you can organize your calendar by content type and add publish date as the secondary sorting parameter.
In your DIY content calendar, you can add content type as its own header in the calendar tab so that you have a place to describe each item. Use this as a way to both make searching easier and giving you a way to balance your content types.
Your content type column will be a helpful place for optimizing your items for search and reference.
Content Purpose
Why are you creating the content in the first place? Is it to bring awareness about a specific topic? Is it to get people to fill out a form? Or is it to directly drive sales?
Whatever way you choose to approach this, you want it to be a short list of end results you hope your content achieves. Your content purpose is similar to your goals from your overall content strategy. Each purpose should reflect what you set as your goals from that strategy.
If you follow the Inbound Methodology, it may be easiest to define each purpose by stage: awareness, consideration and decision. Group your content into those three stages or make your own.
Suggested Headline
When you first start your content creation process, you need to know the approach you’ll take. A suggested headline or title, depending on content type, will help you stay focused on a specific topic and not go astray.
Use this as an opportunity to write what your topic will be in just a short sentence or phrase. Add this to the suggested headline space, but remember that it is not set in stone. In fact, I recommend working on it more before deciding on a final headline / title. Use a headline analyzer to help you create something that’ll hook people’s attention.
If you don’t know how to expand your content topic into a workable headline, you have options. You can ask for help or seek it out online. I use Impact’s BlogAbout and Portent’s Content Idea Generator for inspiration.
Don’t worry if your headline text gets cut off. You don’t need it to be fully visible because you’ll want to be able to see your other columns in one view. If you want to see it fully, simply double click in the box and a preview will appear.
Focus Keyword
Similar to the headline, your focus keyword will help you stay laser-focused on your content’s direction. It’s a good idea to pick a keyword phrase rather than just one word in order to have a better understanding of what you’re creating. It of course helps with SEO, too.
You’ll want to put your well-researched focus keyword in your calendar tab so that you can have it for quick and easy reference. It’ll also ensure you don’t use the same keyword more than once.
As an aside, are you looking for help with keyword research? I recommend Google’s keyword planner via AdWords. There are great paid services out there, but if you want to keep your activity free, sign up for an AdWords account to get access to their free tool. You won’t have to spend a penny, and you’ll still get the best keywords for your needs.
I’ve added my focus keywords for two of my content pieces. Although you can’t entirely see, I used phrases that were targeted enough to help me stay on point and also rank higher in search. For this article, my keyword is DIY Content Calendar. For the eBook, I went with Social Media Marketing for Beginners.
Target Persona
These are really important. You should already have a list of at least two target personas for your content marketing efforts. They are representations of your ideal audience, and you should have them for every goal you want to achieve, every content type you create.
Sometimes, you may not know how to do this for this particular purpose: creating reader personas for your blog rather than as buyer personas. It’s basically the same process. You’re simply focusing on blog use rather than sales. To give you a better idea of how to add target personas to your DIY content calendar, take the time to review the two guides below. They’re very helpful for researching, creating and applying reader personas to your blog activity.
A Step-by-Step Guide to Creating Reader Personas by Neil Patel of Quick Sprout
How to Research and Create a Content Persona for Your Blog by Olga Rabo of SEMrush
Once you have a few personas created and the mock names for each, you’re ready to use them in the target personas column of your calendar tab.
CTA
Short for Call-to-Action, a CTA is the direct action you want your visitor to take after reading your content. It could be a free download or product demo. The options depend on the content you’re sharing.
In this column of your DIY content calendar, you’ll want to add the verbiage you’ll use in the CTA and preferably add the link to your corresponding landing page. If it helps, give the CTA two columns: one for the hyperlinked text and one for the link itself. Otherwise, you can always hyperlink the text with the landing page link within the column itself.
Your CTA column items should always include the exact wording you’ll use with your content as well as the link to each CTA’s offer.
Destination
Your destination is the online location where your content will originally appear. This might be your blog site or a custom landing page. You could be sharing this content on a third-party site. Wherever you choose to publish your content should be included in this column.
The important point here is that you should only have one location in each of these fields. Your content will appear somewhere first, and that’s what you’ll include here. If you have more than one place for this section, think about it more. If your content is an eBook, for example, it’ll appear behind your landing page before it shows up in advertising or email. You’d put landing page as your content destination in this case.
Instead of manually entering different destinations repeatedly, consider using a drop-down menu of options you typically use (explained in a later section).
Distribution channels
This column is debatable because there usually isn’t just one channel per content piece. A blog post will be promoted on social media and email. It could be syndicated to other sites as well. The problem with a distribution channels tab is that you’d have to list all your various channels in one field. It’s not the most organized approach, and a drop-down menu is definitely too restrictive.
What can you do? You can stick to the “multiple channels” or singular items in a drop-down menu if you don’t mind the restriction. Or, you can remove the menu and simply name the different channels manually, using commas or semicolons to divide them.
How you use this column is more of a preference than a rule that depends on what your answers would typically be for each content piece.
Draft Due Date
This is a rather self-explanatory column. However, it’s important to remember this is the draft due date. It’s when you should have something written out, but it’s not yet ready to be published. This is more than an outline. If it’s a blog post or eBook, you should have all the writing and design completed and ready for review. This is the content as a whole, not just bits and pieces.
This deadline should be realistic. The date should allow you enough time from its inception to create something worthy of review and final approval. When you only provide excerpts or messy copy, you’re wasting both your time and review time to the point where it’s harder to reach your publish deadline.
If you’re creating content on your own, make sure you have a decent gap between your draft due dates to allow you time to get each one done in the best quality possible.
Approval Due Date
As you can predict, this date should occur after your draft due date. It should be the date when you expect to get final approval for your content. It’s when your content is finally ready to be published.
You can choose to make this column the “approval due date,” or it can be your “publish date” if you’re blogging alone.
Client
With a company calendar, you may be creating content for various clients, such as what I do with Miles Technologies. If that’s the case, this is the column where you name which content piece belongs to which client. Depending on the number of clients you have, you may want to color-code this column as well.
If you don’t work with clients or blog alone, you can either remove this column entirely or change it to a custom name missing from this list.
Author
This is another column that works better if you’re creating a DIY content calendar for a team of content creators. You’ll want to have a place to report who created what for whatever reason, and this is the place to do it in a big-picture way. Use people’s first names with last initial in case of duplicates. It’s a great way for people to see what they’re responsible for in the future. It’ll help writers know what they need to do and how to prioritize it all.
Use each author’s first name and last initial, and it might be a good idea to include a color-coding system if you have more than three authors.
Current Status
You have options for how to use this column because it greatly depends on how you’ve organized the rest of your calendar. There are typical items you can include though, including:
Concept / Idea
Draft in progress
Review in progress
Scheduled
Published
Those are the usual items, but you might want to add or remove as needed.
Regardless of whether any other columns are color-coded already, this one needs to be labelled in such a way that everyone can clearly see the urgency between the different statuses.
Your DIY Content Calendar: The Social Reporting Tab
In your next tab, you have your social reporting information. This is easily correlated with your content tab when you use your Content IDs instead of relisting multiple calendar columns over again. When you stick to each item’s Content ID, you keep your tabs looking clean and organized.
In the above screenshot, you can see the eight columns you’ll want to have as a bare minimum, including the Content IDs. Let’s briefly go over what you need for the rest of the columns.
Promotion Date
This column’s information is never set in stone. It shouldn’t stay as the first time of promotion but rather contain your most recent campaign date. It should serve as a reminder to you that you haven’t promoted a piece of content in a while. This will help you determine what needs to be promoted again versus what was just shared.
It would be a good idea to sort this tab by the promotion dates so that you can clearly see the urgent items first.
Channel
This is where you add which social network you shared the content on. This can be a custom drop-down menu you created with the different platforms you’re on, or you can add the names manually.
Each content item might be shared on multiple platforms. If that’s the case, give each platform its own line and repeat the Content ID. Your social copy should differ per platform, so you’ll need a new row for each.
Copy
This is where you add the full text of your tweet or post. Include any hashtags you used as well. Don’t remove the link if it appeared in your tweet or post. It will be added again later, but your copy column should have your full content.
Multimedia Source
If you shared an image, video or other multimedia in your social promotion, you should have it saved, hopefully in a program such as Dropbox or Google Drive. When you’re sharing any multimedia, copy the share link to it. You’ll want to add it it in this column of your social reporting tab.
Shared URL
The rows of this tab should all be content promotion-based, so they should all have a shared URL that goes to a piece of content. Any social activity that doesn’t involve sharing your content should not be added here.
It is best if you’re using a link shortener to share your content. It will help with Twitter’s character limit and give you an opportunity to track your link clicks. If you truly want to track link clicks all the way through though, I recommend using UTM codes in your links. Add your full URL to Google’s Campaign URL Builder, get the new URL with the extra code at the end, and then use a tool such as Bit.ly to shorten and clean it up.
Content CTA
This is different from the CTA you added in the Calendar tab. Instead, your social CTA should be more concise and fit with the platform and its audience. For example: Use “Read more” with the link next to it when sharing on Google Plus. You’ll have the article title or an excerpt before it, of course. In your content itself, your CTA will be to another offer. While social will lead to your blog post, your blog CTA will lead to something else. They are not the same CTA.
Goal Metric
I don’t see this used very often, but it’s definitely a must for all your activity at all times. You should always know why you’re doing what you’re doing with your content. Your social media promotional campaigns are no different.
Whether you’re trying to get form submissions, subscribers, or simply exposure, you should have these goals labelled per content piece in your social reporting tab. You’re not publishing tweets and posts on social for no reason, and tracking your goal metrics will help you stay focused.
Your DIY Content Calendar: The Distribution Tab
Your distribution tab is for any third-party syndication you might use to get your content out there. This can include signing up for RSS feed syndication or pitching your content for guest blog opportunities. As examples, I’ve added my two top sites for these purposes in the image above. I share my articles automatically with Business 2 Community via RSS feed, and I give original blog content to Social Media Today for them to use on their site, should they choose.
It’s important to note that you should only add a row when you have a final URL with your published content. This isn’t the place for drafts.
As you can see in the image above, there’s that Content ID column. It’s the best way for you to keep things simple and organized. You can also see that there are three other columns, which I’ll briefly explain here:
Channel
I mentioned earlier that this tab is for third-party involvement. You’ll want to add the name of these external sites to this column.
URL
You’ll want to add the full, original URL that leads to your content on the third-party’s site. This is so that you can record and reference it.
Goal Metric
Just like with your social reporting, you’ll want to have a goal metric for your distribution activity. You may want traffic from their site to yours (harder), or you can want more exposure or engagement with readers (easier). Whatever your goal is, add it here.
Your DIY Content Calendar: The Finished Tab
What do you do when you’ve finally published a piece of content? Well, you can leave it in your Calendar tab and label it “published” under Current Status. You also have the option to add a Finished tab for all your completed work.
Your Finished tab would closely resemble your Calendar tab with only a few column changes:
1) No Due Dates
You don’t need these anymore because your content is obviously through the draft process and published.
2) No Status Column
Your content has gone through the creation published and is now done. The content may not be final in that you may want to refresh it from time to time, but for the sake of organization, always add published content to this tab.
3) New Final URL Column
Your content is now out there for your audience to see, which means it’s accessible by URL. This could be a blog address or your content’s landing page. Wherever your content can be found online, that website’s URL should be included here.
If you published your content in more than one place, such as on your website and LinkedIn Pulse, give it two rows: one for each location.
Your DIY Content Calendar: The Results Tab
You don’t want to create content without knowing what’s working and what’s not. You always want to know what approach you took did the best versus the worst. This will help you create even better content each time because you’ll learn from the past.
It’s important to note: any third-party content you publish, such as guest articles, won’t be measurable in this much detail. You’ll see the social shares from those sites, but the columns here are meant for content hosted on your website, which is where most of your content should be.
There are several columns to this tab that you should consider including. You can always add or remove columns to make this work best for your needs. The measurements I’ve added are important to you finding out what your audience wants (and doesn’t want) to see from you. I skip Content ID because you’ve seen enough about it by now.
Date Range
You’ll hopefully track your content results over multiple periods of time, such as within a week, month, or even quarterly. That’s why you have this column. You can add your week-long range with its metrics then add a new row when you measure after a month.
Total Visits
In Google Analytics, you can find all sorts of metric goodies. Total visits (better known as Pageviews) is just one of them. With this metric, which you can find under Behavior – Site Content – All Pages, you’ll know how many times people visited your content’s page. This number includes both new and returning visitors.
Unique Visits
You can find this metric in the same place as Total Visits / Pageviews. In this case, this number includes only new visitors. It’s a great metric for seeing how much reach your content is getting.
Bounce Rate
Your bounce rate is going to be relatively high thanks to spammy web crawlers. There are ways around that, but I’m not aware of best ways to do so. Instead of focusing on how to get rid of spam bots though, concentrate on the legitimate visitors who aren’t sticking around.
A major reason why people give up on your website is if it doesn’t load fast enough. Using images with huge file sizes can cause your load time to get longer.
When measuring bounce rate, make sure you’re looking at your content’s specific webpage in Google Analytics. It could potentially be a lot lower than your overall website (or a lot higher).
Top Referrer
In Google Analytics, you can look through your referral traffic under Acquisition – All Traffic – Referrals to see if any referral traffic landed on your content page. If any did, see which referrer had the most visits or the lowest bounce rate. Or, include all your referrers for that page in this one column field.
It’s likely that your top referrer could be a spammy website. This is especially likely if you’re dealing with a high bounce rate overall. If you’re still building your online presence, you might want to skip this metric for the time being. Once you have more backlinks and influence, you should start tracking this metric.
Top Social Source
If you look in Google Analytics under Acquisition – Social, you’ll see the different social platforms that brought in traffic to your website as a whole. To narrow it down to your singular content, choose Acquisition – Social – Landing Pages. Then, use Social Network as the secondary dimension. Look for which social network brought the most people to your specific page and add it to this column.
You can use this metric to determine which social network is bringing the highest ROI. It’ll be the platform where you have the largest audience, but it won’t necessarily tell you if they’re the most loyal. That is a harder metric to determine.
Average Time on Page
To find the Average Time spent on your content’s page, go to Behavior – Site Content – All Pages. Click on your content’s page, and it will have this number listed as one of the offered columns. You’ll want to record this number because if the number is really low, something might be wrong with your webpage or even your content itself. Alternatively, if this number is higher than you expected, you can celebrate a little. It means people stuck around on your webpage.
Drop-off Rate
This is a more page-focused metric that you can use to see which visitors are going further into your website than the page they landed on. If your drop-off rate is high, it could mean that your CTA is ineffective. It could mean your navigation is difficult for the visitor to use or find (unless they’re on an actual landing page, which shouldn’t have a navigational menu in the first place).
Use the drop-off rate percentage to determine how effective your actual content is at reaching whatever goal you set, such as form submissions.
Form Submissions
With a blog post, you might have a form to increase subscribers. Your blog post should always have a CTA button that leads the visitor to a landing page. With an eBook landing page, you should have a form before offering the download.
Form submissions means lead generation. Not all the submissions will help your business, and some may simply be spam. You can counteract that with a captcha or other service though.
The means in which you find out about submissions depends on what service you use for your landing page or maybe your email provider. However you get them, add the number of form submissions you received during your designated date range to this column.
MQLs
This number is not the same as your form submissions. Marketing Qualified Leads do not include the spammers or even the people who wouldn’t benefit from your business. This number should be strategically measured so that when you send the contacts over to sales, you’re not wasting their time.
You can learn if a contact is truly an MQL by seeing what they’ve included in the form fields on your landing page as well as what else they’ve done on your website. Track if they engage with you on social media. That also is a huge indicator.
Most Clicked Link
Be careful when you select this because I’m not talking about your content’s URL. As an example, within your blog post, you may have added a link to another blog post. Your CTA is a link. Your sidebar widgets might have links to landing pages. These are all potential metrics for this column.
You’ll want to know which link was most popular because it will help indicate what your audience is most interested in. If people clicked on another blog resource, you could promote that post more on social because of its popularity.
Social Engagement Rate
Your engagement rate per platform is typically this:
Total number of engagements (likes, shares/retweets, comments/replies) divided by your content’s total reach / impressions
Then multiply by 100.
That percentage is your social engagement rate for that platform. To combine your platforms, add all percentages and then divide by the number of platforms included.
Track this number to determine if people are engaging with your content on social media. This is a big indicator of whether people are interested in that content piece. If they aren’t engaging, try to reposition your promotional strategy or revamp the content itself.
Your DIY Content Calendar: The Ideas Tab
Congrats! You’re done with all the reporting and organization for your content! The ideas tab and data tab (next up) aren’t as time and effort-consuming. In fact, the ideas tab can be as simple as just adding notes anywhere for any reason in any format. The main point of having an ideas tab is just to get your thoughts in writing. You’ll just want it to be organized enough that you can refer to it later and know what you are working with.
If you prefer to be more organized with your ideas, you can experiment with making custom columns, such as what I did in the screenshot above. However, keep in mind that the more organized and detailed you get, the more time and effort it requires.
Your DIY Content Calendar: The Data Tab
Throughout this article, I talked about having drop-down menus for your different column options. To create these menus, you need a data dump or an extra tab for all the menu items you want to include. I use Google Sheets, so the following instructions work for that program. I’m not sure about Excel because I don’t use it.
In Google Sheets, create a tab for all your menu data. Add your column names for easier navigation, and add the different items you want included for each.
Once that’s complete, go to the tab where you want the menu to appear. Select the column, such as for “Category” and go to Data – Validation. Under Criteria, choose the table-like image at the far right of the form field. It will ask you what data you want to include, so move to your data tab. Highlight all the items in the corresponding column that you want to include as menu items in the other tab. Be sure you don’t select the column name. You’ll only want the list for menu items.
Select Okay and then Save. Your menu options will be at the new location as a drop-down feature. You’ll just have to select the arrow in each box to choose an item.
———
Whew. This was quite the article. Thanks for sticking with me! To summarize, we went over:
The Calendar Tab, including each column you should fill in with your ideas, drafts and completed projects.
The Social Reporting Tab, which is necessary so that you can track the status of your content’s promotional strategy.
The Distribution Channels Tab, where you learned how to organize your third-party content involvement.
The Finished Tab, which should host all your finished content from the moment you start, into the future.
The Results Tab, including your own website’s analytical data along with any other website data reports you have for your content.
The Ideas Tab, where you add any and all thoughts you might have or get that could be turned into content in the future.
The Data Tab, which is the dump for your other tabs’ menu items.
If this wasn’t the ultimate guide to a DIY content calendar, I’d be shocked, but I know there’s a chance I may have left something out. I welcome you to add a comment with any ideas for what I may have missed, or you might have questions.
I wish you the best as you create your own content calendar, and please reach out if you’d like more information or assistance!