Use Google Sheets Formulas to Count Weekly Calls

Posted on October 10, 2019   by Casey Houser

In today’s blog, we’ll dig into Google Sheets formulas and show you how to calculate your weekly calls for use in other reports.

This post will act as a supplement to our other full Zapier tutorials, including Learn to Log VirtualPBX Calls in Google Sheets and Send Voicemail Data to Google Sheets.

Although we’ve provided you with a fully-working Sheet in the Preparation section of this blog, you can follow along with the directions below to understand how its formulas work. Then you can make use of these principles to customize other Zaps you make and reports you create.

Jump to any subsection of this blog with the following internal links:

1. Common Use Cases
2. Preparation of Materials
3. Configure Your Google Sheet Cells

Common Use Cases

Preparation

Copy Our Public Sheet

You can get started immediately by copying this Google Sheet. Open the link, then at the top of your screen, click File and then Make a copy to save the file to your own Google Drive.

Configure Your Google Sheet

Open the “Statistics” Worksheet of your Google Sheet. We’ll work through Columns B to G before addressing our count of weekly calls in Column A.

Column B (The now() Function)

Finding the current timestamp in Google Sheets is easy. You can have a cell output that information by running the now() function, as shown in this example. Be aware that the timestamp shown here is the number days since Dec. 30, 1899. We will convert that figure into seconds and make other manipulations in future equations.

Column C (Current Unix Timestamp)

The Unix timestamp is a standard that counts the seconds since Jan. 1, 1970. It’s necessary to find the Unix time in order to understand the timestamps associated with VirtualPBX calls. Of all the Google Sheets formulas shown here, this may be the most confusing, but it’s definitely essential to the process.

Use this formula in Column C to create the Unix time from the Google Sheets time:

The inner-most parenthesis here tell your Sheet to count the days between 1970 and 1899. Then it subtracts that number of days from the current time we found in Column B. After that, it multiplies the number of days by 86400, which is the number of seconds in a day.

Column D (Year 0 to 1970)

The way VirtualPBX outputs timestamps is by counting the seconds since Jan. 1, 0001 — the first day of A.D. In order to mix VirtualPBX logged values with the Unix timestamp (discussed in the previous section of this blog), we’ll need to add the number of seconds between that start date and Jan. 1, 1970.

This number of seconds — 62167219200 — is what’s shown in Column D of our Sheet.

Column F (Seconds Per Week) & G (Timezone)

Jumping ahead one column, to Column F, the value of 604800 is shown.

This is exactly what the column’s headline says: It’s the number of seconds in a week.

Next, Column G includes a formula (=3600*4) to equal 14400. This value represents the shift of the number of seconds in each timezone. VirtualPBX logs calls in the GMT-4 timezone, so the number of seconds in one timezone (3600) is multiplied by 4 to reach the desired value.

Both these values will be used to calculate the Week Ago Timestamp shown in Column E.

Column E (Week Ago Timestamp)

Now the Week Ago Timestamp formula can make sense:

It adds Columns C and D to produce the current timestamp (in seconds) when the log is accessed. This is analogous to the now() function shown in Column B but with a few transformations.

That value then subtracts the number of seconds in a week and compensates for the time zone change. You can change the value that Cell F2 is multiplied by to see the number of calls in 2, 3, 4… weeks. It would look something like (F2*2) to generate a timestamp from two weeks ago.

Column A (Calls Started Past Week)

Finally, we can see how many calls were started in the past week. The Google Sheets formula used here refers to the Calls Worksheet (tab) of the documents we provided in the Preparation section of this tutorial:

This formula tells your Sheet to look at Rows 2 through 6 in the Calls Worksheet. Those cells contain the timestamps of your logged calls. You can change the second value, D6, to analyze more calls. To that end, this formula would count the number of logged calls down to row 1000:

No matter how many rows you count, this formula will compare each timestamp with the value of Cell E2 in our current Worksheet — Week Ago Timestamp. If the date of the call is newer than E2, it is counted. If the date is older, it isn’t counted.

The outputted value is an integer like you see in the screenshot here.

How Zapier Works: Sending Voicemail Data to Google Sheets

Posted on October 8, 2019   by Casey Houser

Part of our goal for this month’s blogs is to teach you how Zapier works alongside our Business Phone Plans.

Zapier lets you link web-based services together — such as connecting VirtualPBX to Google Sheets like you’ll see in this tutorial. Today, we’ll break down the process of logging voicemail data in a spreadsheet.

Zapier uses an Action-Reaction format. In this Zap:

1. Action: Your VirtualPBX account receives a voicemail
2. Reaction: Zapier prints that voicemail’s data into a Google Sheet

You can follow along in this tutorial by starting from the top or moving to the individual sections below:

1. Common Use Cases
2. Preparation of Materials
3. Start a Zap (Configure VirtualPBX)
4. Choose Zap Output (Configure Google Sheets)

Common Use Cases

Preparation

You will need to create a Google Sheet that’s labeled to accept voicemail data like Call ID and Timestamp. The quickest way to do this is by copying our Google Sheet template.

Open the file linked above. Then click File in the menu at the top of your screen. Click Make a copy in the following drop-down menu to save the Sheet to your own Google Drive.

Action

On your Zapier home screen, you will first need to select the Make a Zap button to create a new project.

A new screen will load. This is where you begin working through the steps of your Zap. As your first step, you can select the VirtualPBX Zapier integration.

Make sure you select the VirtualPBX 1.0.6 integration version. The Choose App & Event section of this step can always be changed.

Your Trigger Event in this Zap will be the New Voicemail option. This selection will tell your Zap to trigger a reaction when a caller leaves a voicemail on your account.

Then enter your VirtualPBX Account credentials and hit Continue to proceed to the next step (adding your Google Sheet).

Reaction

As your second step – your Reaction – click the plus sign on your screen and choose your App (Google Sheets) and Action Event (Lookup Spreadsheet Row).

Your selection of Lookup Spreadsheet Row here will allow you to search for existing data and complete new lines if that data isn’t found. Here, you will search for an existing call by its unique ID. If that call isn’t listed in your spreadsheet log, Zapier will output the new call’s data in your Sheet.

Just as you did with the VirtualPBX integration, you will need to select your Google Sheets account before you can access an individual spreadsheet.

Next, select your Spreadsheet and Worksheet (the individual tab inside the chosen spreadsheet) you want to use.

The Lookup Value you select should be the Call ID option. In the drop-down menu for this selection, Zapier will list the VirtualPBX app and all the data options available for an inbound call. Call ID works well here because you will search your Google Sheet for that unique identifier which accompanies every call associated with your VirtualPBX account.

Make sure you check the box labeled Create Google Sheets Spreadsheet Row if it doesn’t exist yet. Your window will load with the options similar to those shown here.

The options loaded in this window will be titled with the same column headlines you placed in Row A of your spreadsheet. As seen in the screenshot above, the call data of Call ID is mapped to the Call ID spreadsheet column. You can change the mapping by clicking on any of the drop-down menus. Zapier will output any of the inbound call data you select into the columns you specify.

If you add new headlines, be sure to check your mapping in Zapier. As you get more comfortable with how Zapier works, one particularly useful data field you can configure is Voicemail Box ID, which lets you log the voicemail box that received a particular message. This field is included in our Google Sheet Template in the Preparation section of this tutorial.

Now you can Test & Continue to send sample data to your spreadsheet.

When you’re ready, you can turn on your Zap and begin processing your voicemails.

Learn to Log Calls in This Google Sheets Zapier Tutorial

Posted on October 3, 2019   by Casey Houser

VirtualPBX is excited to extend its Zapier integration to customers of all its Business Phone Plans. This month, like with this Google Sheets Zapier Tutorial, our blogs will focus on the basics of application connections so you can get started with your own projects.

Today’s tutorial will show you how to use Zapier to log call data in a Google Sheet.

Zapier uses an Action-Reaction format. In this Zap:

1. Action: Your VirtualPBX account receives a call
2. Reaction: Zapier prints that call’s data into a Google Sheet

You can follow our Google Sheets Zapier tutorial by starting from the top or moving to the individual sections below:

1. Common Use Cases
2. Preparation of Materials
3. Start a Zap (Configure VirtualPBX)
4. Choose Zap Output (Configure Google Sheets)

Common Use Cases

Preparation

You will need to create a Google Sheet that’s labeled to accept call data like Call ID and Timestamp. The quickest way to do this is by copying our Google Sheet template.

Open the file linked above. Then click File in the menu at the top of your screen. Click Make a copy in the following drop-down menu to save the Sheet to your own Google Drive.

Action (Configure VirtualPBX App)

On your Zapier home screen, you will first need to select the Make a Zap button to create a new project.

A new screen will load. This is where you begin working through the steps of your Zap. As your first step, you can select the VirtualPBX Zapier integration.

Make sure you select the VirtualPBX 1.0.6 integration version. The Choose App & Event section of this step can always be changed.

To determine a received call, you can either select Call Started or Call Ended for your trigger event. We’ll be using Call Ended here.

Then enter your VirtualPBX Account credentials and hit Continue to proceed to the next step (adding your Google Sheet).

Reaction (Configure Google Sheets)

As your second step of this Google Sheets Zapier Tutorial – your Reaction – click the plus sign on your screen and choose your App (Google Sheets) and Action Event (Lookup Spreadsheet Row).

Your selection of Lookup Spreadsheet Row here will allow you to search for existing data and complete new lines if that data isn’t found. Here, you will search for an existing call by its unique ID. If that call isn’t listed in your spreadsheet log, Zapier will output the new call’s data in your Sheet.

Just as you did with the VirtualPBX integration, you will need to select your Google Sheets account before you can access an individual spreadsheet.

Next, select your Spreadsheet and Worksheet (the individual tab inside the chosen spreadsheet) you want to use.

The Lookup Value you select should be the call_id option. In the drop-down menu for this selection, Zapier will list the VirtualPBX app and all the data options available for an inbound call. call_id works well here because you will search your Google Sheet for that unique identifier which accompanies every call associated with your VirtualPBX account.

Make sure you check the box labeled Create Google Sheets Spreadsheet Row if it doesn’t exist yet. Your window will load with the options similar to those shown here.

The options loaded in this window will be titled with the same column headlines you placed in Row A of your spreadsheet. As seen in the screenshot above, the call data of call_id is mapped to the Call ID spreadsheet column. You can change the mapping by clicking on any of the drop-down menus. Zapier will output any of the inbound call data you select into the columns you specify.

Now you can Test & Continue to send sample data to your spreadsheet.

When you’re ready, you can turn on your Zap and begin processing inbound calls.

Beyond This Google Sheets Zapier Tutorial

What we’ve introduced here is only a glimpse of Zapier’s capability. You can output many other data fields, including Duration and who an inbound call came From. Then it’s just a matter of watching your log fill with data for later analysis.

Stay tuned to our blog the whole month of October. We have several further tutorials in store about processing other forms of call data and manipulating fields to generate reports.

Earn Rewards While Advocating for VirtualPBX: The All-New Customer Referral Program

Posted on October 1, 2019   by Jennifer Merrigan

VirtualPBX always has its ear to the ground with the latest and greatest in emerging technologies. That’s why we have so many happy customers! Because of that, we want to make it as easy as possible to allow our community to share their satisfaction with their peers.

We are pleased to introduce the all-new, VirtualPBX Customer Referral Program.

Earn Rewards With Our Customer Referral Program

Here are VirtualPBX, we make it easy to sign up. You can have your entire Dash business phone system ready and running in a matter of minutes! So why not make it easy for our amazing community of customers to share their great experience with their coworkers and friends while also earning rewards in the process?

Becoming an advocate in easy! All you need to do is fill out a form to get your very own customer referral code to offer your friends and then watch the rewards come in. Here’s what those rewards will look like:

Redeem Your Gift Card Through TangoCard

Once someone uses your referral code to sign up for VirtualPBX, you will be notified immediately. Then, once they have completed their onboarding process, you will receive an email from our rewards partner, TangoCard, will a voucher to redeem a gift card of your choosing! What’s more? We understand that being a part of the VirtualPBX community is much more than just “making money”, so if you choose to not accept a reward for referring, there are tons of option with TangoCard to instead donate your reward to a charity of your choosing.

Start Advocating Today!

Are you excited to jump right into advocating for VirtualPBX? Check out our Referral Program page to get started and you will be well on your way to sharing all that VirtualPBX has to offer with your very own Customer Referral Code.

Happy advocating!

Our Own Design Sprint: We Re-Worked Our Phone Tree

Posted on September 24, 2019   by Casey Houser

We re-worked our phone tree in our own design sprint so you don’t have to do it alone.

In our blog this month, we have discussed the concept of sprint planning as it applies to tasks like editing your own phone tree. This goal of these sprints is to complete a meaningful project within 30 days.

In those posts, we’ve challenged you to complete your own design sprints. Now, to help you even further, we want to share the results of the sprint VirtualPBX used to improve the menu of its Automated Attendant. We called our own phone tree, analyzed its menus, and cleaned up the language which you can now hear when calling our main company phone number.

What is a Design Sprint?

The idea behind a sprint – whether it’s called a design sprint or referred to as sprint planning – is to complete a significant project at your company in a reasonable amount of time. The typical scope of a project should encompass no more than 30 days of work.

Sprints were originally developed for businesses that completed a lot of smaller tasks. Software development firms, for instance, could use sprints to release versions 5.02, 5.03, and 5.04 of their broader version 5 software across consecutive months. Each of the iterations would build upon the last and might include small feature releases or security improvements.

Notably, the sprints would avoid major improvements like a graphic redesign of the whole software package. That would take more than a month to complete; therefore, it would defeat the idea behind smaller iterations that combine into a greater whole.

At your own company, you can begin with design sprints by setting a goal, examining the goal’s pace and expected results, and being open to new information as you proceed. This type of analysis is discussed in more depth in the preceding link above and will be shown in specific examples below as you see how we re-worked our phone tree.

Our Phone Tree Project

Setting a Goal

Our goal for this design sprint was to optimize the messages contained within our Automated Attendant. We had multiple menus that were long-winded, so we knew that the journey through our phone tree could be much quicker and more efficient for inbound callers.

Pace and Expected Results

We knew this project would reach the edge of the 30-day mark. In summary, our steps within the overall project were as follows:

1. Create a transcript of our phone tree menus
2. Edit the transcript to tighten the language
3. Record the new language into the Automated Attendant

We expected each step to take about a week. The project involved several departments – including several members of Marketing who approved the new language and company managers who fact-checked the material – and required the use of a third party to complete professional voice recordings of our transcript.

Our Progression

Creating the transcript of our phone tree menus was a relatively straightforward task. A member of the VirtualPBX staff simply called our main business phone number and listened to the presented messages. The various scripts were then placed into a shared document that others could edit.

We ended up with a starting group of a main menu, after-business-hours menu, and various sub-menus for departments like Sales and Services.

Editing the transcript proceeded similarly throughout the various menus. As an example, consider the main menu text that started our journey:

“Hello and thank you for calling VirtualPBX. Your call is very important to us. If you know your party’s extension, you may dial it at any time. For Sales, press 1. For Technical Support, press 2. For general account queries including plan changes, press 4. For Billing, press 5. And for all other questions or to reach an operator, please press 0.”

We took a heavy pen to the text by removing unnecessary language like the initial “Hello” and the following “your call is very important to us.” We knew that, by simply saying “Thank you…” that we were already greeting the caller and asserting that their call is important to us.

The paring of that greeting landed us with this final script:

“Thank you for calling VirtualPBX. If you know your party’s extension, you may dial it at any time. For Sales, press 1. For Technical Support, press 2. For general account queries including plan changes, press 4. For Billing, press 5. And for all other questions or to reach an operator, please press 0.”

By sticking to the bare essentials, we cut down the time it took to hear our menu options while keeping the language polite and informative for customers. We re-worked all of our menus with this same mindset and remained open to new information when the demands of one sub-menu were not identical to others.

We weren’t derailed when a sub-menu, for instance, required more explanation to callers about who they could reach or about the nature of a group (like addressing the nature of our 24/7 Support). On the contrary, we held steadfast to the ideal that each menu should be as short but as informative as possible. Your own transcript revisions should always ask, “What does the customer need here?” But that question shouldn’t force you into lengthy passages that contain information better addressed by an individual associate.

Recording the transcript allowed us to involve Snap Recordings. We sent our final transcript, including all our sub-menus, to the professionals at Snap who returned crisp audio files for use as we saw fit.

In this case, we uploaded our greetings into our Automated Attendant and made sure that the stated options in the audio matched our departments.

This final step in our design sprint was made easier through the Dash Phone System interface we offer to customers and which we use ourselves. Uploading was easy, and configuration of the menu options involved only a few clicks.

Get Started on Your Project

If you haven’t yet called your own phone tree, we hope you’ll begin soon.

The process of completing a design sprint, as we have shown here, can move smoothly if you prepare well and stick to your mission. We completed our own sprint in less than a month, but not because we practice this task often. Our phone tree was well overdue for a rewrite!

We stuck to the guidelines of the design sprint to achieve our goal. You can do it too.