Goal: This automation recipe will help export your contacts to Google Sheets in order to bulk edit existing phone numbers to the E.164 international formatting.
More details:
To ensure your Aircall integration is recording each call and logging properly to an existing contact record, the phone number field of the contact should be formatted to support E.164 international formatting.
E.164 numbers are formatted [+] [country code] [subscriber number including area code] and can have a maximum of fifteen digits.
Step 1: Instructions for preparing the Google Sheet
1. Create a new Google Sheet. Name the sheet.
2. Name Column A: Email address (column name must match exactly to the email field in the contact record)
3. Name Column B: Phone (column name must match exactly to the phone field in the contact record)
4. Name Column C: Formatted
Step 2: Instructions for setting up the automation
5. Import the recipe into your account. There is no setup required for the trigger.
6. Connect to your Google Sheets account and select the sheet.
7. Map Email address to Column A. Map Phone to Column B. No mapping for Column C.
8. Turn automation status to Active
9. Click to Contacts > Edit All
10. Select “Add to an automation” >“Aircall: Update Phone Number Formatting”, then Add.
11. The contact’s email address and phone number are now in the Google Sheet
Step 3: Formatting the phone numbers in the Google Sheet
12. In cell C2, type this formula: =concat(“+”,B2). Autofill the column.
13. Select All for Column C and right click to copy
14. Click B1 and right click paste special values only
15. Rename Column B from Formatted to Phone (column name must match exactly to the phone field in the contact record)
16. Delete Column C
17. Download the sheet as an .xlsx file onto your computer
Part 4: Instructions for importing the formatted phone numbers back into ActiveCampaign
18. Click to Apps > Google Sheets
19. Click Import Contacts
20. Connect to your Google Sheets account and select the sheet.
21. Map Email address and Phone
22. Select “Import contacts and do not auto import new contacts in the future”
23. Add a Import Name
24. Click Finish.
Note: Please allow the import to run for 5-10 minutes. Larger contact lists may take longer.