Zapier Path to Create a Weekly Email Report

Posted on October 15, 2019   by Casey Houser

There are many paths in Zapier you can take with your Zap setups. This tutorial will build on our previous tutorials to demonstrate how to create a weekly report sent through Google Gmail.

Our previous tutorials, including Sending Voicemail Data to Google Sheets and Sending Call Data to Google Sheets link the VirtualPBX Zapier Integration with Google. Using those instructions, you can log information about your calls that will become accessible for use in reports.

Directly, this tutorial references our instructions about Using Google Sheets Formulas to Count Calls.

This tutorial grabs call data from a Google Sheet. Then it goes beyond that step by moving the information into an email. Multiple Zapier paths, taken through the various Zaps you’ve created, automating the process of gathering data and reminding you about the valuable information each Zap collects.

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

1. Action: Zapier is scheduled to send an email report each week
2. Reaction: Zapier retrieves data from a Google Sheet
3. (2nd) Reaction: Zapier delivers the data through Gmail

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 Zapier Timer)
4. Update Google Sheet Data
5. Send Email Through Gmail

Common Use Cases

Preparation

In order to process information for your report, you will need to create a Google Sheet that accepts call data. You can make a copy of our Sheet template for this project before moving to the steps outlined below.

Sheet templates are also linked as starting points within our other tutorials.

Action

You will first need to select the Make a Zap button at the top of your Zapier home screen.

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 Schedule by Zapier option.

Your Trigger Event in this Zap will be the Every Week option. This selection will tell your Zap to take further actions every week.

You can set your preferences for day of the week and time of day when you want your Zap to work.

Reaction 1: Update Google Sheet Data

As your second step – your first Zapier step taken and first Reaction to the Schedule trigger – click the plus sign on your screen and choose your App (Google Sheets) and Action Event (Update Spreadsheet Row).

Your selection of Update Spreadsheet Row here will inform Zapier about the contents of your Google Sheet.

Now choose the account where your calls are logged in a Sheet.

Select your Spreadsheet, Worksheet (the individual tab inside the chosen spreadsheet), and Row of data you want to retrieve.

The Column headlines shown in the Google Sheet screenshot at the beginning of this tutorial match the options shown here in the second step of the Zap.

Now we will update a field in our Sheet by rewriting the formula it already uses. In this case, the formula calculates the number of rows that meet a specific condition. You can learn more about this process by referring to our other tutorial about using Google Sheets to count the number of weekly calls a VirtualPBX account received.

The result of this process is an up-to-date count of the number of calls received within the past week. Updating the Google Sheet cell here lets us reference that cell properly in the next step where Gmail will send an email that reflects the count.

Note that the changes you make to cell formulas in a Zap will affect what’s shown in Google Sheets. Make sure your formulas are the same.

Reaction 2: Send Email Through Gmail

Click the plus sign again in your Zap to add another Zapier step. Select your App (Gmail) and Action Event (Send Email), and select your Gmail Account from which you will send the email.

Customize your email options by choosing recipients

Enter the Title and Body of your email. Dynamic values are available for inclusion here by clicking on the right-hand side menu of each bar. In this case, you can see the dynamic value from our Google Sheet that shows how many calls were received this week.

Your report will change weekly as new data is sent from the VirtualPBX Zapier App into your Google Sheet. The second step of this Zap, where your Sheet cell is updated, ensures that the data Gmail grabs will reflect current statistics.

Finally, you can send test data to Gmail to make sure your Zap is working properly.

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.

An Exploration of Cloud Services: SaaS, PaaS, and IaaS

Posted on October 2, 2019   by Casey Houser

Today’s guest post comes courtesy of Dmitrii B., the CEO and founder of GRIN tech – a full service digital agency in design, marketing, and web development alongside white-label agency & media outreach.

According to the IDC research organization, by 2021, companies from around the world will spend more than half a billion dollars on cloud tech. This is two times more than in 2016. At the same time, there is a clear tendency: the public cloud workloads are growing, while the situation is exactly opposite for the private cloud.

Such a shift is partly caused by cloud providers, who offer users of public clouds more and more features that simplify the development of services.

Let’s take a deeper look at the key concepts of SaaS, PaaS, IaaS. You can link to any of the sections below by following this navigation:

1. Introduction
2. Software as a Service (SaaS)
3. Platform as a Service (PaaS)
4. Infrastructure as a Service (IaaS)

The Essence of Cloud Solutions

Clouds have appeared as a reflection of the general trend of the shared consumption economy, which is focused on use instead of ownership. This has already given us the exchange of housing, car sharing, and even wedding dresses for rent.

There are more and more things and services that can be used and paid for only by their actual use, without large capital expenditures for the purchase.

In the case of IT infrastructure, capital costs can be significant. Paying for this expensive infrastructure can unnecessarily weigh down business, so adoption of a rental model (the cloud model) is often justified.

In addition to potential savings in capital costs, there are other significant advantages:

• Speed: Leading suppliers can get a cloud in minutes/hours.
• Flexibility: Visualization technologies allow you to get as many computing resources as you need here and now. Missing and buying too slow a server is not a problem.
• Saving on associated costs: Including uninterrupted power supply to the server
• In certain cases, savings on maintenance personnel (more details below)

Sounds promising, huh? Just keep in mind the drawbacks:

• Connection Dependency. You need a stable internet connection for cloud services to work.
• Limited Features & Lack of Control. Say you implement a cloud-based CRM system. It’s all good, but then you need just this one new feature… but you don’t own the product to make the decision.
• Security. For businesses that desire strong security practices they control, it can be worthwhile to hire an in-house team rather than leave security detail to a third party.
• Technical Issues. You cannot fix your cloud computing problems in-house, and some providers do not offer around-the-clock technical support.

There are three main formats of cloud solutions by the service model to explore: Software as a Service (SaaS), Platform as a Service (PaaS), and Infrastructure as a Service (IaaS).

Software as a Service – SaaS

The most user-friendly clouds. You just work in the necessary programs via the internet (without installing them on your device).

Advantages

• Technical details are completely hidden. You connect through the internet to the ready-made program.
• Update, performance, and other technical issues and problems are handled by the service provider.
• Lightning-fast onboarding (mostly). It is enough to order a service and you can use it in a few minutes.

Drawbacks

• Not all programs are available in this format for technical reasons.
• The possibilities of setting up and changing the program to suit your requirements are limited.
• Strong dependence on the service provider and the quality of their work.
• You often have limited control over your own data.
• It may be more expensive than other types of clouds or traditional applications, especially for large customers.

Examples of SaaS

As a rule, SaaS is provided by the developers of these very applications. There’s often a need for businesses to adopt multiple vendors to see all their needs met for cloud software use.

• Microsoft Office 365
• VirtualPBX providing “best in VoIP services”
• Various CRM systems, which are often cloud-only
• Google Analytics, etc

Platform as a Service – PaaS

You rent a certain computing platform consisting of virtual computing services (analog of the central processor and server RAM), data storage services (analog of the server hard disk), data transmission services (traffic consumption), etc.

You can design arbitrary virtual computers with a variety of system and user software. Suppliers – the leading IT companies with a worldwide reputation – provide stable operation of these computers and programs. Solutions in this area are popular with corporate customers and developers.

Advantages

• Amazing flexibility – you can build a computer of any capacity (from a micro server comparable in power to a smartphone to a cluster of hundreds of thousands of servers), and install on it a variety of applications.
• Major suppliers have huge geographically distributed networks, which allows you to easily deploy fast, mass and fault-tolerant applications.
• There is an opportunity to additionally connect advanced services, in which Oracle, Microsoft, Google have unique competencies – the use of artificial intelligence, analysis of large amounts of information, etc.
• You can often pay only for consumed resources rather than a block of allowed resources

Drawbacks

• To use it, it is necessary to “assemble” the system of virtual components. This requires specific knowledge and skills.
• Each platform imposes its own restrictions on implementation, there is no complete freedom in implementation.
• There is a high base cost.

Examples of PaaS

This table notes a number of major PaaS providers and their product offerings in a range of capacities such as web hosting and database hosting:

The total cost of PaaS use considers the consumed computing resources, space in storage, and the amount of data transferred over the network. Idle servers are cheap whereas often-used servers can become expensive. This Amazon Web Services calculator lets you input these considerations to get an idea of the costs you might incur.

Infrastructure as a Service – IaaS

You rent a server/computing resources. The service provider is guaranteed to work at the server level (electronic filling of the server, internet, power supply, etc.) / virtualization technology (see below).

There are three formats for providing the service:

• Dedicated server: Renting a separate physical server that can be used only by you. It has a relatively high cost but guarantees that all the computing resources of the server are fully at your disposal. The provider provides full administrative access to the server, and your specialist sets it up the way you want it.
• IaaS for a pure virtual data center: The provider installs special virtualization programs on one or more servers and then passes access to the management interface of these programs to you. This approach is very flexible, as you can create and configure any number of virtual servers for yourself.
• VPS/VDS as a virtual private/dedicated server: This is when the IaaS from the previous point is configured by the vendor for you and only transfers access to virtual servers that you cannot change. Important differences are the virtualization technology used (e.g. KVM / XEN / OpenVZ / Hyper-V / VMware) and the guaranteed performance. VPS/VDS can offer low cost but may also show low/unstable performance due to overselling.

Advantages

• The purchase of the server is not always reasonable, as it becomes obsolete over time. By renting a server, you can always change or update it with minimal costs.
• It is possible to choose and manage the performance and parameters of the server as you want.
• Complete freedom to install any operating systems or programs you need.

Drawbacks

• It requires an engineer to set up and maintain.
• Often, there is a specific server/virtualization specification that is tied to the characteristics of the server/virtualization technology. This may lessen the flexibility of server use.

Examples of IaaS

The market is saturated with vendors. Our personal experience at GRIN tech is with a 32GB RAM monster from Hetzner that costs us around $55 per month. It hosts approximately 15 projects and still has a lot of room left for company expansion.

To Summarize

From my experience on client projects and what I see in the industry, I’d say for pretty much every need small and medium businesses are facing there already exists some sort of cloud solution.

Even when a company decides to build something with in-house devs – the infrastructure itself can easily rely on cloud infrastructure from a 3rd party vendor.