Table of Contents
Integrating Google Forms, Numverify API, and Google Sheets for Efficient Data Management
Accuracy in customer contact information is crucial for any business. With the advent of no-code platforms like Make.com, you can now automate the verification of phone numbers provided by customers through Google Forms, ensuring the data’s reliability without writing a single line of code.
In this detailed how-to guide we will show you how to:
- Setup a Google Form so that you can collect user phone numbers online into a Google Sheet
- Setup a Numverify account and get a free Numverify API key.
- We will show you how to link your Numverify API key to the Make.com platform to access the Numverify API, and update the Google Sheet with data verifying your user phone numbers.
By the end of this tutorial, you will have a fully automated system in place. This system will not only capture user phone numbers through a Google Form but also verify their authenticity via Numverify and record the results in a Google Sheet. The entire process is streamlined and automated, offering you a hassle-free way to maintain the integrity of your customer contact data.
Step-by-Step Guide:
Step 1: Capturing Data with Google Forms
Our process kicks off with a Google Form, a versatile tool that allows businesses to gather information directly from customers. Once a form is submitted, the data is automatically passed to a connected Google Sheet, ready to be processed.
You can easily set up a form that collects user information using Google Forms ( https://docs.google.com/forms/ ) and parses the data collected from the form to a spreadsheet on Google Sheets.
Setup a form to collect a ‘Phone Number’ as a short answer, add other fields that you desire to collect.
Navigate to the responses tab, and click on ‘Link to Sheets’ so that the responses can be parsed to a Google sheets spreadsheet.
Once you have chosen ‘Link to Sheets’, you will have various options. Choose the option that works for you. For this demonstration we will select the option ‘Create a new spreadsheet’.
You can rename the Google Form or the Google Sheet at any stage, but they will still be connected to each other. On the Google Sheet created below when a phone number is submitted on the Google form, the Phone Number will appear under column B.
If you are new to ‘Google Forms’ note that you can share the link to the form you have created by clicking on the ‘Send’ button in Google Forms.
Select the link icon to get the link address, anyone who has access to this link address can fill out your form.
Preparing the Google Sheet
In our example for demonstrating the capabilities of the Numverify API, we shall rename the Google Sheet to ‘PhoneNumbers’ and expand the descriptions in the first row columns to include the following fields:
| Timestamp |
| What is your phone number? |
| Valid |
| Number |
| Local Format |
| International Format |
| Country Prefix |
| Country Code |
| Country Name |
| Location |
| Carrier |
| Line Type |
Next we shall signup to Make.com to create an automated scenario that will check the Numverify API and analyze phone numbers that have been parsed to the spreadsheet and automatically populate the details in the adjacent columns.
Step 2: Monitoring New Entries with Make.com
Signup for Make.com to create a scenario, look for the Scenario icon in the menu
Then click the ‘+ Create a new scenario’ button
In the ‘Empty integration screen’, click the ‘+’ button.
Search for ‘Google Sheets’
Set the Google Sheet to ‘Watch New Rows’ of data, it acts as the initial trigger for our automated workflow.
Connect your Google Account by clicking ‘Add’ to add your Google connection.
From this point you should be able to link into your spreadsheet, and automatically indicate which sheet in the spreadsheet is collecting the phone responses, e.g. Sheet 1 (or the sheet name might be named after your Google form).
Select the options in the image below, e.g. Table contains headers, and indicate the columns that have named headers in ‘Row with headers’.
A limit of 1 is used for testing purposes, but if you want to bulk update a spreadsheet with multiple phone numbers to be verified you can set the limit higher (note that this should also be within your Numverify API rate limit).
Step 3: Making the Numverify API Call
Before proceeding any further with the Make.com platform, you’ll need to sign up for a Numverify account at https://numverify.com . This step is essential before you can add the HTTP module to Make.com in the following part of the process.
You will find your Numverify API key in your Numverify dashboard.
To test the Numverify API paste this url into a new browser tab after adding your API key and a phone number
http://apilayer.net/api/validate?access_key=YOUR_API_KEY&number=PHONE_NUMBER
You will need this URL and the output later.
You are now ready to add and use the ‘HTTP’ and JSON module to make.com
Make.com HTTP module
Add the HTTP module to make.com
Next you will need to click ‘+’ button and search for the HTTP module to add it to your scenario.
Click on HTTP, and at next screen under ACTIONS select ‘Make a request’.
When you open the HTTP module, in the URL section paste this url into it (editing it with your API key)
http://apilayer.net/api/validate?access_key=YOUR_API_KEY&number=
After ‘number=’ when you click on the white space, the spreadsheet fields will appear, click on the field that represents the user phone number on the spreadsheet to add it to the url tab.
In the Method section select ‘GET’.
Upon detecting a new phone number entry, Make.com’s “HTTP” module steps in to perform a GET request to the Numverify API. This module sends the phone number for validation and waits for a response. This interaction is secured with your Numverify API key, which authenticates the request.
Step 4: Parsing and Analyzing the Data
The data returned from Numverify is in JSON format, containing details about the phone number’s validity, its international format, country code, and more.
So for the next step you will need to add another module and find the “JSON” module in Make.com to be able to parse this information to other modules.
Click the ‘+’ button and search for the JSON module to add it to your scenario.
After selecting the JSON module, at the next screen choose Parse JSON.
In the ‘Data structure section’ click the ‘Add’ button, this will open another window.
In this window, click the ‘Generate’ button.
Remember Step 3, you opened a new tab in your browser and pasted http://apilayer.net/api/validate?access_key=YOUR_API_KEY&number=PHONE_NUMBER
with your API key any phone number. Check the results in the new browser tab now as you will need to paste them into the next screen. If you didn’t create a new browser tab at step 3 or it got closed, repeat Step 3 to generate API data results.
Paste the information returned in the browser to the Sample data section, and click ‘Generate’.
Next you will need to update the JSON string section, referencing it to the ‘Data’ field from the HTTP module.
Step 5: Updating the Google Sheet with Validation Results
The heart of our workflow is the Google Sheet module “Update a Row”. It automatically updates the Google Sheet with the verification results from Numverify, appending information such as whether the number is valid, its standardized format, and country specifics.
You will need to add a new Google sheets module, select the action ‘Update a Row’.
Populate the next form with the following options that reference the spreadsheet fields so that it links back to the Google sheet.
Populate the column sections of the form referencing it to the JSON module fields (these are the empty columns of the spreadsheet that you want to parse the JSON fields to),skip columns such as A and B which will have a time stamp and the phone number.
Having successfully filled out the ‘Update a Row’ action form, which links back to the necessary spreadsheet and JSON fields, Make.com is now equipped to refresh the spreadsheet with the API results for each phone number whenever a scenario is executed.
Your final scenario screen will look like this:
Click ‘Run once’ to run the scenario.
The empty columns on your spreadsheet should update with the validation results
Step 6: Scheduling Regular Updates (Advanced)
For businesses that require regular data updates, Make.com offers scheduling options. By using a timer, you can set the workflow to run at regular intervals, ensuring that even entries not immediately processed are verified in a timely manner.
Step 7: Bulk Update of Google Sheet
Should your Google Sheet be receiving a high volume of phone numbers and you wish to update several rows simultaneously each time a scenario is run, consider increasing the ‘Limit’ size.
Let’s take a moment to reflect on what we’ve accomplished together in this guide. Through using Make.com, you’ve now mastered the art of creating an automated, no-code workflow that brings together Google Forms, Numverify, and Google Sheets. This isn’t just about phone number verification; it’s about making your business processes more efficient and your data management more reliable.
Cost Savings & Boosting CRM Efficiency with Numverify Phone Validation
Some online resources suggest that each invalid or incorrect phone number in your dataset could potentially cost your business up to $25 a month. This estimate takes into account various factors such as lost sales opportunities, wasted resources in outreach efforts, marketing costs directed towards non-functional numbers, and the administrative expenses involved in maintaining and cleansing data. For instance, if you have a database of 10,000 phone numbers which are regularly called and approximately 5% of them are bad or inaccurate, this could translate into a significant financial impact. In such a scenario, with 500 (5% of 10,000) ineffective numbers potentially costing $25 each, the total cost incurred from these bad numbers could be as high as $12,500. This figure highlights the importance of maintaining an accurate and up-to-date phone number database to minimize financial losses and maximize operational efficiency.
Using a service like Numverify for phone number validation can lead to significant cost savings, particularly when managing customer data in a hosted CRM system. By ensuring that all phone numbers are valid, Numverify helps in reducing the presence of redundant or incorrect numbers. This not only streamlines the database but also avoids potential revenue loss associated with contacting invalid numbers.
Effective validation reduces the time and resources spent on managing incorrect data, thus contributing to overall efficiency and cost-effectiveness in customer relationship management.
Having correct phone numbers can save costs in various ways, even if not using a paid hosted CRM system:
1. Efficient Communication: Valid numbers ensure effective and timely communication with customers, reducing the time and resources spent on unsuccessful contact attempts.
2. Marketing Campaigns: Correct numbers improve the success rate of marketing campaigns, preventing wastage of resources on non-existent or incorrect contacts.
3. Customer Satisfaction: Accurate contact information leads to better customer service and satisfaction, enhancing the company’s reputation and potentially leading to more business.
4. Data Analysis: Reliable data contributes to more accurate analytics and business decisions.
5. Compliance and Legal Costs: Avoiding calls to numbers on Do Not Call lists or in regions with strict regulations helps prevent legal issues and potential fines.
In summary, validating phone numbers with a tool like Numverify can lead to operational efficiency, improved customer relationships, and avoidance of unnecessary legal and compliance costs.
Taking Automation to the Next Level!
By following the steps we’ve outlined, you’re well on your way to implementing a similar system in your own business. Imagine the time and resources you’ll save, not to mention the boost in confidence you’ll have in your customer data.
Now that you’ve got the hang of using the HTTP module on Make.com and integrating it with services like Numverify, why stop there? API Layer offers a wide range of APIs that you can explore and integrate into your workflows. Each API opens up a new possibility, a new solution to a problem you might not even know you had.