Sales Tracking for Google Spreadsheet using Google Apps Script

Editor’s Note: Guest author Brennan Morrow runs Solar Light, a Solatube Premier Dealer based in Oregon, United States. — Marcello Scacchetti

Sales Tracking for Google Spreadsheets is a simple CRM solution developed specifically for small businesses who want to track the sales process and have return of investment data informations regarding marketing activities.

The problem:
This Google Spreadsheet was developed, as we could not find a CRM that met our simple needs. We found the use of many of these existing CRM’s took too much time entering the information, and often, we had to enter it multiple times. And yet no CRM I could find that was simple, allowed for the calculation of marketing ROI. For our business we did not need leads, opportunities, customers, archive, etc. We only had one field: PEOPLE. For our product, it’s people that buy it. We are a home service based business in Oregon.
This is a primary activity for any sales tracking, because it empowers the manager to visually see what is working and what is not. It turns the process of marketing from gambling at the casinos, to a controlled measurable equation of counting the cards. In my situation it allowed me to be have higher sales with a 7% marketing budget, rather than a 15% marketing budget. This was an enormous change in our profit. Further, this sheet influences how we interact with our customers. Also constantly evolving our system with, what is working in marketing and leads follow up each new entry.

This spreadsheet achieves:

  • Simple salesperson interface using a Google Form with the intention of sales scripting.
  • Calculation of marketing ROI both for all dates, and monthly dates
  • Sales Follow-up: Notifying the salesperson of follow-up activity and communications. (using Future Reminders script)

Nuts and Bolts:
The spreadsheet is composed by five sheets and a Google Form and an adaptive script called Future Reminders.

Sheets:

  • Names: this sheet contains all the current and past sales activities or leads, this is where all statistics are generated from. This sheet is initially filled by an embedded Google Forms that allows fast recording of opportunities, and if needed sales script process.
  • ROI marketing All: this sheet tracks the overall marketing ROI
  • month before Marketing ROI: This sheet is used for comparisons with the next sheet (mentioned below). The user defines what month to look at the marketing ROI for that month only
  • month marketing ROI: this sheet tracks the user defined month as above, used to compare 2 months, or just examine current marketing effectiveness.
  • Lead Map: this sheet by using the Google Maps service allows to see all the leads coming from the Names sheet in a geographic fashion

The Form:
This is the preferred source of data for the spreadsheet.It populates the names sheet. The form is designed to be modified by the user, to perform a guided sales script, and get information necessary for the user’s business. There are a few primary fields, or questions that are needed for data compiling in later sheets:

  • name
  • address
  • sale closed?
  • source: “how did you hear about us ( primary)?”

The Script: Future Reminders
This script checks the date of when the form was filled out, and send a reminder to the person that filled out the form on a user input timetable. It is then expected that the salesperson would call, e-mail, or do some form of follow-up when they get the reminder from the script. Further it is expected that they enter any new information into spreadsheet, and at the next reminder that information will be part of the update.

Lets Get Busy:
Let’s try to use the Spreadsheet, first you have to go to the live form to input values by using the “Form -> Go to live form” menu item: (Note: you will need to copy the sheet so it is in your google apps domain, if you want to use the form entry. This is important to note if you expect entry of data from users outside of your google apps domain. There’s a high possibility there is a solution to this, however it has not been a priority yet as the system works fine for us as this)

Sales Tracking for Google Spreadsheet 1

Fill the form with the following values:

  • Customer name: Sample Customer
  • Phone Number: 555
  • Address: Via Boiardo 3, Mirandola (MO) Italy
  • What intered in?: select Holiday LIghts from the drop down item
  • Sales Chance %: select 5
  • Sales Closed?: leave empty
  • How did the customer hear about us? Primary: select Website
  • How did the customer hear about us? Secondary: select Google
  • What is the customer interested in and where?: 5 big rooms
  • Roof Type: select: Cedar shingles
  • Customer E-mail: put in your email address
  • Customers Purpose of Calling: select Price inquiry
  • Notes on Communication and sales progress.: This is the first contact
  • Sale Close Date: leave empty

And hit the “Submit” button. You should see now the spreadsheet file being populated by the inserted values:

Sales Tracking for Google Spreadsheet 2

Jump over the “ROI marketing all” sheet to see what happened:

Sales Tracking for Google Spreadsheet 3

As sources are populated, by the choices made on the form fed to the source field on the “Names” sheet (or could be manually entered). The user fills in the yellow and green areas on this sheet. The yellow “cost” represents the cost of this source, in the selected time and is manually entered (this may vary between the marketing ROI all sheet, and the monthly sheets.) Be aware, the source field populates from the “Names” sheet and the sources will be in order of first entered. If the rows in the names sheet are changed (by manually entering a row above) this will change the sources order. Ultimately, the cost associated with that source should be double checked to make sure that it is the appropriate cost for that source.

Gross profit percentage: (the green field): this represents the users average gross profit. (this is best described as the percentage retained after the cost of goods sold is removed, and before expenses). The purpose of the gross profit percentage use is to determine if an advertising source broke even or not. This is one of the primary purposes of the whole entire spreadsheet.

Total Row at Top: This row compiles this critical data:

  • Number of total leads to the time.
  • Number of total sales for the time.
  • Total $ made from all the sales
  • Average cost per lead
  • Average cost per sale
  • Lead to close percentage

Jump to the “Month Before Marketing ROI” ( This sheet is a clone to the “month marketing ROI” they are designed to be able to compare two months side-by-side)

Sales Tracking for Google Spreadsheet 4

This sheet identical to the ROI marketing all sheet stated above, however a month can be defined in the first yellow cell (A1).
A reminder here is to always check the cost and make sure they’re appropriate for the chosen month, when the month is changed the layout of the sources will change according to the first one picked for that month in the name sheet.

Jump to the “Month Marketing ROI”
This is a clone of the above sheet. Its purpose is to be able to compare two months side-by-side, without the need to re-enter the costs for the source.

Jump to the Lead Map:
This page takes the sales leads that have been entered, and puts them on a map if their address was entered. We have found this to be extremely useful in regards to addressing if our marketing is effecting certain neighborhoods, and areas. it has also given us the advantage to contact a lead when we are in their area based on the fact that we are nearby (remember, we are an in-home service-based business so much of our work happens at the customers home)

Follow-up sequences: (Future Reminder script)
One of the most critical activities this sheet will do is send the salesperson a reminder to follow up with the customer on a given sequence of days after the initial contact until the sale closes.
This is accomplished by a custom developed Google Apps Script based on the work of Romain Vialard, and revamped by the mast Marcello Scacchetti. To access the reminder configuration open the Spreadsheet menu item “Future Reminders -> Preferences”

Sales Tracking for Google Spreadsheet 5
By using the “Select sheet” drop drown menu, you can select the source sheet for the data, by default the “Names” sheet is used where all the lead informations are stored.
The second option “Check dates” is used to specify which column contains date information regarding leads, in the default Spreadsheet date is stored inside column A.
Send the reminder to e-mail address in column allows you to specify where to read email addresses for the salespersons associated to a specific lead. In our case it’s column B.
The scope of the “Stop sending reminders” options is to tell the script to stop sending reminders if the specified column has values. Column H for example is a good setting for this option since it is the sales closed flag. When a sale is closed no more reminders are needed.
Last option is “Schedule days”, basically this field takes a comma separated list of days after which it has to send the reminder. If you set the field with the example values: 5,14,21,30 and the lead date is 01/01/2013 it will send out reminders to the salesperson on 01/06/2013, 01/15/2013, 01/22/2013 and 01/31/2013. The number of days is unlimited. A sample setting is presented here:

Sales Tracking for Google Spreadsheet 6

Clicking the “Save” button will store the desired settings.
By default the script will run automatically each day, but you can run it manually by using the menu item “Future Reminders -> Run manually”.

To use and customize this Spreadsheet make a copy of: Sales Tracking V 1.2 Spreadsheet

Hope you will find this tool useful!

I am owner of Solar Light in Bend Oregon. We bring light to peoples life. We sell and install Solatube tubular skylights, and LED holiday lights. Solar light is a classic US small business, and I a small business owner. From the trials and tribulations, I have learned many interesting skills which I love to share in regards to business.

Tagged with: ,
Posted in Google Apps Script, Spreadsheet

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>