Use Google Docs to Manage your Digital Projects, from Freelance to Large&nbspAgency

This post was promoted from YouMoz. The author’s views are entirely his or her own (excluding an unlikely case of hypnosis) and may not reflect the views of Moz.

In July 2012 I launched 3 Door Digital alongside my three co-founders. The launch was part of a merging process between two existing companies (Pleer, based in Manchester, UK and Matan Media based in Tel Aviv, Israel). Along with the merger came some potential operational hazards that we had to make sure we were on top of prior and post launch. Part of my responsibility was to find and apply the best project and time management process for our campaigns moving forward.

In the past few years I have used numerous task management tools and decided to put a few to the test to see which one would work for us. In the end we found that Google Docs was actually the best platform for us to use. This may seem like an uncommon choice but with the correct setup it has proved to be extremely successful.

I'm going to share how we manage to stay on top of over 30 client projects at any one time whilst making sure we don't miss tasks or lose track of the hours we've spent.

Before covering all this, there was a second choice…

In the end we had a decision between two platforms – Basecamp and Google Drive (formerly Google Docs). Basecamp was great for us but there was something missing (which I’m sure will eventually be added at some point) – the ability to quickly browse tasks, task owners and time management all in one easy-to-read page. This is where Google Docs won. Don’t get me wrong – Basecamp is a fantastic choice for some companies – it just wasn’t the best solution for us.

So, there were a few reasons why we chose Google Docs and I’ll go through why, feature by feature. As well as this I’ll share a template document that you can copy as your own to tweak as you wish.

Google Docs Spreadsheets FTW

Before 3 Door Digital was born, I had two concurrent jobs: the Head of SEO at a search agency in Manchester and another running my own company with my wife and business partner. Because, at the time, there were only two of us it was quite easy to keep track of each other’s work using an Excel document within a Dropbox shared folder. Once 3 Door Digital started trading, it became obvious that this wasn’t going to be the best solution.

To start, I simply uploaded the spreadsheet template I’d been using for Pleer into Google Documents and added the additional clients from Matan Media into the new 3 Door Digital template. Simple!

“Simple” I thought – it wasn’t so simple. Although I was well aware that I was now planning out 20+ retainer clients, 10+ one-off clients as well as other internal tasks. It was time to roll out the “super template” that would make it easy to navigate these 30+ clients whilst being easy to read for all consultants, account managers and directors. Most of all, it had to be workable so that tasks were not missed out (the main personal downfall of mine for Basecamp’s GUI).

The Super Template

Marc and I took a day out to create this super template by discussing how both teams work between offices in two countries on various tasks. Each row would represent a different task, no matter how small. What we needed to do is select columns. For each task, we covered the following:

Client Name

Platform Name (because some clients have various sites and platforms, some of which may have different account managers)

Account Manager

Task Name

Owner (if not the account manager)

Deadline

Budgets

Actual Hours Undertaken

Completed (this uses a tick, exclamation point or cross – more on this later)

Notes

The “Summary Row”

Each client and platform is split by a few empty rows and a total time allowance is provided within a chosen “Summary” row. In this row we can use equations to calculate total hours spent on a platform and time left for the month. Scripts for time management are then applied in order to enable email notifications (covered later in this post).

Using other Tools to Compliment Google Docs

Even after all this, we were well aware that more information was needed dependent on the task. There is some information that you simply can’t illustrate well in a spreadsheet. Because more information is sometimes needed, or at least you need a separate area to work with for a specific task, we used other online collaborative tools and resources to help us with specific tasks – using the notes column as the point of reference.

Before Going Elsewhere, use the Comment function

This feature is easy to use. Right click a cell and you can add a comment. You can tag a user of the doc with the @ or + sign, and they will be notified by the email and directed straight to the comment itself. Once commented, a small orange label appears in the top right of the cell. Hovering over that cell shows the comments as a popup.

You will also notice a “Resolve” button at the top right of the popup. Clicking this, tags the comment thread as resolved, removes the orange marker, and hides the comments from the cell itself. If you want to access all comments within the document you can access them by clicking the Comments button at the top right of the screen (next to the Share button):

Use other Google Docs

Sometimes the commenting feature is never going to cover everything that needs to be covered. Some projects may need a separate document or spreadsheet or have more dialogue with more limited access. Let’s say for example that this whole task document is viewable to the whole company but one task only the Account Manager and one Director want to see details of this specific task. Here, we would create a new document and only give the Account Manager and Director access rights. In the task sheet you simply link to that separate document.

For Even Larger Tasks that Require a Folder Structure

Sometimes a task or project needs its own file and folder structure consisting of a lot of different types of files. Within the company we use Dropbox for collaborative filesharing. We used the share option within Dropbox to link to the specific folder or noted down the folder location for anyone to access. Here’s an example of where an image is entered within the notes column for other people to click and view:

Tasks that Require a More Visually friendly UI

One example of this could be a web build. We use Trello – a fantastic task management tool that I have been personally using for years and can’t recommend enough. I don’t want to talk about Trello in this post, however, sign up (it’s free), and get to know it well – and be comforted in the knowledge that they have an iPhone, Android and Windows 8 app and are currently developing their iPad app.

Paddy Moogan of Distilled wrote a great post about using Trello to Manage Projects for SEO - go and check it out.

Conditional Formatting FTW

I’ve never been at a level with spreadsheet production that I would call myself advanced, but when I learn something new in it I realise how versatile it is and how it can display information. One thing I’m a fan of is colour coding as I like to see the status of multiple tasks without always reading each row in any detail. To achieve this, I used conditional formatting in two columns:

Completed

For completed tasks I knew that there were only three options that we would need to choose:

Incomplete

Hurdle

Complete

The "Hurdle" option is when something outside our control is stopping us from completing the task. When this happens, we add notes into the notes column where we can make efforts to complete the task as soon as we can. Using conditional formatting I have made the three different statuses with three different backgrounds for us to easily identify. As well as this, we used the Data Validation option to ensure that only these three option can be selected by a dropdown. This is set by right clicking the column/cells and selecting Data Validation. We used the following configuration for status:

This then allowed us to select an option rather than type the words manually. This is done when you select the cell and you will notice a dropdown option that opens a popup for you to make the relevant selection:

Originally in Microsoft Excel, I used a different system whereby each task is set to 0 by default for incomplete, 1 for hurdle and 2 for completed. In Microsoft Excel (version 2010 or newer) you can use conditional formatting using the tick/exclamation/cross Icon Set, which is what I use when not using Google Docs (if you work for Google and you’re reading this please forward that as a feature request ;)). This is the specific conditional formatting we used for the Status Column:

Deadline

This column used more sophisticated conditional formatting. We wanted to have different colour coding dependent on how many weeks or days there were to the task’s deadline. These were as follows:

If more than a week away, we’re good

If within the next week, turn orange

If within the next 2 days, turn red

If in the next 24 hours, turn even deeper red

If deadline has passed, turn black

This conditional formatting is somewhat limited in Google Docs when it comes to timescales (another feature request :P) so we used the conditions above, the setup of which looks like this:

Keeping the Document Tidy

After a while you may find that the document becomes crowded. To eliminate this, you could hide rows where the tasks are confirmed as completed. Hiding a row (different than deleting a row) means that all data is still stored for future reference; as well as it keeping other formula data such as time management.

We choose not to hide rows, as we personally like to see all tasks regardless of its completion. Instead, we split our tasks into worksheets separated by month. At the end of each month, we copy the existing month’s worksheet as a new worksheet and delete all completed rows. This then leaves us with only incomplete tasks, which are then added to as the month progresses. If we want to refer to a previous month, we simply refer to the relevant month’s worksheet. This way we can easily see every task over the course of the whole year.

Getting More Technical with Time Management Scripts

One thing I found that Basecamp lacked apart from its UI was the lack of time management connectivity. Relevant Managers want to be notified via email if a client is running out of management time for that month. Within the email we can include information from anywhere else in the spreadsheet. In our example, we have used the Client Name and the amount of hours left.

If this method isn’t your cup of tea, other online tools such as Toggl and Harvest will help you with time management.

Managing Account Hours

To begin, let’s use a sample client. This client has three platforms and has a number of tasks within each platform. Each platform therefore has its own set time allowance for the month. We now have the following time data:

Total allowance for the month

Total estimated time for each incomplete task (if you want to be that detailed)

Actual Hours Undertaken

We use this data to perform the following equation to find how many hours left there are for a platform:

Time left=SUM(Allowance-(SUM of all actual hours undertaken))

Here’s one example:

Column I, the number of hours left, is updated as the document is edited and is the number we need in order to see how many hours are left for the month. What we needed was a way to be emailed once this approached a low number.

Creating Scripts

David Sottimano of Distilled shared a script with me that emailed a chosen recipient if the number of a specific cell reached a specific number. From here, I took that script and integrated it into our Task Document.

The script runs every time the document is edited (or opened, depending on how you configure it) and is triggered if the “Time Left” figure for the platform reaches 2 or less than 2 (i.e. 2 hours). Once triggered, the script collects the Platform name and emails the person responsible for receiving the alerts with an email that the platform has run out of account hours. To install the script you will need to configure

The column that contains the Platform name

The column that contains the “Time Left” formula

An integer that triggers the alert (we chose 2)

The email(s) to send the alert to

The column to set the notification integer (otherwise you’ll get a LOT of emails :P)

To include the script, you can use the script manager tool and set the trigger in there:

And here is the script itself (highlighting the five variables you can configure above):

Internally, we also created a similar function to email us when budgets were too low using the same template as above and changing the variables at the beginning to focus elsewhere in the document.

Triggering the Script

Once you’ve created your script you can set triggers for each one. To do this, select the “Current script’s triggers...” within the script editor:

Here, we have decided to trigger the scripts on edit so we have live notifications sent to us and not just when the next person opens the document:

Too Much for You? No Problem!

This post either may be too technical for you, or will take you too long to implement. That’s fine – I’m a nice guy. I’m a guy who’d be kind enough to share a canvas template for you with the script installed, conditional formatting set up, scripts running (with a fake email) and even included some sample data :)

Known Bugs

Sometimes we have found when adding rows or editing the doc with new information, some of the rows or columns begin to look messed up. A simple refresh of the document usually fixes this and your updates should be all good!

An Added Note

This is an awesome document and works for us better than anything else we’ve used, but this may not be for you or your agency. We all have our own preferences, although I am pretty sure at some point we may need to invest in our custom management toolset.

This doc isn’t the only thing I use to manage my own time – I also have my own personal Trello board and can’t live without the traditional email inbox to keep me from going insane.

I’d love to know what other tools and resources you use for your own project management…

About alexmoss —
Alex is the Director of FireCask, a digital agency based in Manchester, specialising in Search, Creative Content Production and WordPress development. Alex is also the Co-Founder of Peadig, an extensive WordPress theme framework powered by Bootstrap.

Hi Alex,Great post, and very timely. I have been using Google Drive to store lots of documents recently, but I have found that my conditional formatting created in Excel gets stripped out once I upload to Google. Do you know if conditional formatting is added in Google Drive, it is kept, if downloaded and opened in Excel?Some other great tips in there though.Dan

Hi Dan,Thanks for liking :) Re: conditional formatting not being imported - I know and there was no workaround for this that I found (which is quite annoying). However, downloading a document from Google Drive to your computer and opening in Excel does keep formatting intact.

Thanks for this awesome post. I thought I'd throw another tool in the mix:Unless I missed it, no one has yet mentioned Swydo as an alternative to Basecamp and Drive. It is a project management tool that is specialized for online marketing agencies. I've been testing it for the last week or so and am impressed.The best part is that integrates with Google Drive, Analytics, Adwords, so you can pull documents and data into your projects really easily. I don't fancy the logistical nightmare of moving off of basecamp but I may have to.

Very nice write-up on getting more value from Google Docs. You get better bang for your buck using Docs+Dropbox (assuming you need the extra space, which you most likely do if you juggle several projects at once) than any project management service I've found.We use a mixture of Google Docs (Spreadsheet) and a private message board (via open source CMS) to do all our project management.

I still feel there is far too much missing from MS Excel for me to shift over - I love the more advanced conditional formatting for example and I do love a lot of the pivot table functionality that is missing in GDocs. I love the publishing capabilities of gDocs - for example using a secure url, people can see relatively protected data - or at least a view of a sheet of it, this can be integrated into Google Sites for instance which is another vastly underused area! My next "job" when I get some spare time is to learn scripting in Google docs, it looks far more powerful for developing

I agree - there are some features of Excel that I'd love to have within Google Docs, but then I understand that Google Apps is free and just appreciate the advantages it has over Excel.I also agree about scripts - it's something I want to explore more.

Nice article Alex, thanks for sharing. We've just looked at the whole PM tools issue and settled on a product called MyIntervals. We only moved everything over to it a few weeks ago, but I'd definitely recommend that anyone looking at tools puts it on their list to review.

Nice post Alex.Personally, I don't use Google Docs, but after I saw it's possibilities, I will consider using it.It is very helpful that you only need internet access to use Google Docs and access your documents, but I have never used it because I tough you can only make very basic documents with it.Thank you for educating me with your post.I hope it will be promoted;)

Alex, congrats the well-deserved promotion of your SEOmoz post. Thanks for generously sharing your template to help some of us jump start the value of Google Docs for managing our SEO projects. Very cool!

Awesome post and loads of good info. Personally when I started my agency I'd looked at Basecamp etc and decided they were better for single projects, and thought Google Drive would get a little complicated, so instead went for Liquid Planner, which I've found really good... But the structure and layout of the client management documents is a reminder to definitely get some of my spreadsheets in order!

Hi Dan, thanks for the comment.We also used Liquid Planner. The odd thing we found with all platforms is that there was just something missing that lost the sale for me. Looking back on the process we had, not only have we saved huge amounts of time as well as a lot of money from not paying for subscriptions any more :)

Hey Alex, Great tips here and thanks for sharing the actual Gdoc sheet.Made a copy and will be sure to test it out! I'll let you know how it goes and may have a couple of questions for ya. Thanks again!

Hey Alex,Thanks for sharing the Google Docs sheet. We use Google docs internally (in house at a start up) for the same reason you mentioned - it's all centrally located and easily shareable. I also love that you can create folders and keep things organized that way.Best of luck with 3 Doors!

Great article on maximizing the potential of Google Docs. I believe it would be important to implement this from the start and not only when your transitioning to becoming a large agency. Setting up the system from the start will ease that transition.

Great post Alex, we use Google Drive (same as Docs) at my work for pretty much everything it's fantastically free & means you aren't going to get an angry Microsoft breathing down your neck.. 5gb of Space is generally more then enough for 5-6 employee's and even if it isn't, it's extremely cheap to upgrade and a lot less expensive then other Management tools on the Market!

Hi, Alex! Congrats on your first SEOmoz post! I'm a big fan of Google Drive and try to use it extensively for most of the projects, but the way you do it is fascinating :) Trello is an awesome app too. My personal list also includes Workflowy.com - looks very simple, but I already can't live without it for about a year.

hey Alex, some really neat usage of Drive there! I increasingly use Drive with this extension that allows right click saving of links/images to Drive and also work the RSS events feed of Dropbox into a private twitter bot account that keeps me notified of project amends.Do you therefore invoice or client present task time directly from this big doc?

Hey Paul,That looks like a great extension, I've just installed it and will use it over the coming weeks. Re: invoicing - our Director responsible for invoicing in in charge of the budget data areas in the document and manually invoices accordingly but do use the doc as reference internally.

Thanks Alex, I never know that Google Drive would be so much useful to manage projects that you can replace it with Basecamp. Yes, to be honest, it is little bit difficult to implement for a newbie like me but I'm grateful to you that you shared the template for people like us. I use Google Drive only to store files, sheets & uploading files which I check at home. This is something I'll definitely share with my team.

I have used google docs for a lot of my work but I have started using Smartsheet recently and it is fantastic. I highly recommend it. Its really easy to use, has way more functionality than google spreadsheets and you can comment, share and link to other spreadsheets very easily.They also have hundreds of templates that make it easy to start using quickly.. Its basically Google spreadsheets on steroids.

I was keeping my mouth shut since I just started working there, but yeah, Smartsheet is pretty cool. It's not as web 2.0 looking as Trello, but the functionality, sharing, and file hosting features are amazing.

Yes, I have used Smartsheet and it's great, but there were still some features I wanted that Google Drive offered.The other issue is cost. The method I have written about costs nothing, which is useful for many

I like good doc, I am really a fan of fill up form and its excel sheet data, good on and that form can also send by email. I think more useful for email marketing campaigns, ( but I dont do email marketing )

This is such an outstanding gift to a new freelancer--I'm a freelance teacher, though. (Is that weird?) Anyway, Alex, how long did it take you to put just the post itself together? Thanks a bundle for being a member of the new economy! I'm planning to run my business based on a pay-what-you-want model!

This is exactly what we are in the midst of building, we are moving away from basecamp to just simplify everything and started down the path blind. This is a great help, I picked up a bunch of new thoughts and value! Thank you!

It's funny how whenever I am discussing a change we need, I check this blog and there is always a recent post covering the topic. Thank you for this post, looking into changing our procedures this week because of it.

I just spent the weekend researching project management platforms and TeamBox was the winner simply because it had the easiest integration with Google Docs. TeamBox allows my team to track their time and projects and then upload any documents through Google Drive. We will see how it works out as we role it out across my 3 man team. Your strategy may be a great secondary option though. Google Docs is amazing!

Have any of you guys tried Zoho though? Seems like they had tons of features and according to what I heard, they're supposed ot be the best.
Now that I didn't see Alex mention it, I'm starting to wonder if is as good as they say it is.
Great post! Cheers!

Google docs no doubt manage the things to go in a better way but at certain point of time it doesn't work well with. And at that point of time we have to look forward for another resource that is paid one. The same instance was there with me as well. I have been working with the cloud based task management software from Replicon (

http://www.replicon.com/olp/task-management-software.aspx

) which is managed well with helping with the tasks to be carried and featured well enough.

Robert, do you have an affiliation with Replicon? There are many forum comments across the web where you are recommending their software. If you have a relationship of some type, it's courteous to disclose it. Thanks!

It is difficult to share the thing with a client when work for them online, but Google have the excellent feature of Google Doc which will help to share the file or folders with the client in an efficient way. The Google doc feature is very helpful for the peoples who are working freelance. Thanks Alex for sharing the knowledgeable post.

Hi Alex, this looks promising and could be useful to our team. I have duplicated the sheet but am failing at doing something quite basic. Whenever i copy and paste a list of tasks with it's header description and footer/hours summary, it messes up the equations behind the cells. Is there a quicker way to duplicate a group of tasks inside a project, without having to rewrite the equations?

I've been struggling to create such a management tool for our products here SEO Marketing. Till now, we were using two google docs spread sheets and Basecamp with this new Google Doc, my project management problems are over.

Love this. I'm trying to get into managing teams and larger projects, this seems incredibly helpful on that front. Will definitely refer to this in the future. Even without completely copying the format of this spreadsheed/doc, this article is an awesome framework in thinking about the process behind creating a document to manage your projects. There's so much to think about and even if we don't follow your method exactly, you go through almost everything that we'll need at some point. Thanks!

We have been using Google Drive for shared documents in conjunction with Dropbox and Highrise, but I love your system for project management using Google Drive. It obviously took a lot of trial and error on your part to perfect it and I appreciate your generosity in sharing it with the group. I can see setting up some project timelines/ templates for our team using this system.

Good post Alex. Most of the people use Basecamp like I do but if Google docs is that awesome..why should we use Basecamp anymore. BTW, have you ever tried Smartsheet? It is very good and I like it most and it's almost similar to Google docs.

Yes it's now called Google Drive, but people still resonate with Google Docs as a brand. I actually found that Basecamp was the same, where there were too many tasks. I'm personally a fan of the "one-pager" which is why the template I made for this post fits well with me.

Hi Sisshuos.Thanks for the feedback. You should be able to link to other spreadsheets - click the "share" button at the top and you will be given the URL. So long as the person(s) you share with have access to it, it shouldn't be a problem.

Wow, wow and another wow. I am building a Link Building Agency and the truth is that organization is completely headache, especially when you work with people in several countries and with Freelancers. I will definitely use many of your documents.

True! I started using the Google Docs back in my university days. I ease myself by working on assignments and projects from anywhere. Not to forget in my country there is a lot of power issue, in back 2007 when we started facing this issue, many students face the data loss as not many are in the habbit of saving files after every minute. So we discovered the Google docs. It helped a lot, because on every fluctuation lab computers restarts. Any way,later we do have remedies and issues resolved for the powers and obviously the easy access to laptops, but Google docs really helped through out my jobs and a freelance company I have started. Amazing thing!