ID776 - Address Correction

This new process will provide a simple, reusable client-side master dataset, independent of any single use case, that includes (Table A) location coordinates for all customer, ship to, and service addresses and (Table B) distance between all relevant warehouse and ship to ZIP pairs. This bolt on set can power many additional projects (warehouse assignment, routing, MIF mapping, service radius checks, freight audits, territory realignment, etc.) and improve existing processes with minimal rework.

  Example Report | Variables | Requirements | Notes | Troubleshooting
 

Goal: Keep your ship-to addresses clean so shipments arrive correctly and carriers don’t add correction fees.

When it runs (Local Time in Your Organization's Tenant):

• 3:00 AM (weekdays): Build/refresh the master address list and validate new/recent addresses.
• 8:00 AM (weekdays): Send the Address Validation Report email with a CSV.

What happens

1. We collect unique addresses from your ARCustomers table and any ship-to addresses used on a sales order in the last 90 days. All of these addresses are validated on the initial run.

2. If an address has been used in the past 90 days but not validated in that same period, it is rechecked to ensure USPS has not updated or changed it (for example, when USPS renumbers or standardizes a street).

3. Google checks each address and returns a standardized version, confidence level, and USPS delivery status.

4. If Google is very sure (Rooftop accuracy), we save the map pin (lat/long) and update coordinates back to your customer record.

5. Your morning email lists anything that needs attention.

Why Google Validation API

The Google Address Validation API verifies both USPS mailing standards and real-world delivery accuracy. While USPS ensures an address is valid for mail, Google’s data goes further—it uses geolocation and carrier data to confirm that the address can be reached by UPS, FedEx, and other private carriers. This makes it a stronger choice for businesses shipping packages, not just mail.

Example Report
What you do

1. Open the morning email (example below).

2. The attached CSV only lists items that need attention (everything except ACCEPT).

Follow the Next Step for each row:
• ACCEPT – All good. No action. (ACCEPT items won’t appear in the CSV.)
• FIX – Google corrected something (spelling/format). Use the corrected address.
• FIX – Confirm – ID776 has already updated this address for you automatically.
• CONFIRM – Looks unusual. Quickly double-check before using.
• CONFIRM_AD – Missing/unclear unit (Apt/Suite). Add it to avoid carrier fees.

Variables
Auto update rule (Variable 3)

• By default, we auto-update customer addresses for FIX items using Google’s standardized values.

• If your subscription’s Variable 3 = "No", we won’t auto-update; you’ll update the record yourself.

• TL;DR:FIX = we fix it for you unless Variable 3 says not to.

Google Budget Confirmation (Variable 4)

Before this process can install, clients must confirm they’ve set a budget in their Google Cloud Billing account or explicitly opted out. 

• Variable 4 = “Yes” means you’ve set up a billing budget or chosen to opt out and understand that charges may occur.
• Variable 4 = “No” (default) will prevent installation until confirmation is complete.

This safeguard ensures no unexpected billing or credit card charges occur on your Google Cloud account.

Note: It may say that this process is active, but it has not been fully installed until you receive your first daily report.

Requirements

Requirements: O365 account with Power Automate Premium ($15/mo - paid to Microsoft) and Azure AD App Registration

Documentation on Requirements: Power Platform Set Up – CEO Juice, but we do not need the listed Power Apps or Power BI Pro licenses for this process.

Google Address Validation API Key Creation:

Address Validation API – Pricing Overview

1. Free Credit

• Google gives every account a $300 starting credit that covers Address Validation and other Maps products.
• This is usually more than enough for the first month, especially during the initial setup.

2. Free Ongoing Usage

• Each month includes a built-in free tier of requests.
• For most clients, regular day-to-day use will fall inside this free allowance.

3. Paid Usage (If Needed)

• If usage goes beyond the free tier, requests are billed per use.
• Costs are very low — around 50–60 requests for $1 once you’re past the free credit.
• Pricing automatically adjusts with discounts if usage is very high.

4. How We Keep Costs Low

• We only validate each address once.
• After the initial run, only new or unvalidated addresses are checked.
• This keeps monthly requests small enough that most clients will never see extra charges.
• For large data sets, we can also cap usage (e.g. 5,000 per month) to stay inside free limits on request.

Bottom line: Most clients won’t pay anything for this service. Even high-volume users will see only minimal charges, and we can set caps if needed.

1. Sign in or create a Google account

If they already have a company Google account, skip ahead.

Otherwise go to https://accounts.google.com/signup and create one under a shared address (e.g., maps@yourcompany.com) so multiple admins can manage it later.

2. Create a Google Cloud project

1. Visit https://console.cloud.google.com/.
2. At the top bar, open the project selector and click New Project.
3. Name it something like “Address Validation – <Company>” and click Create.
4. Make sure the new project is selected in the top bar.

3. Link billing (one time per project)

1. In the left menu, click Billing.
2. If prompted, Link a billing account (add a card if needed).
   • The API won’t work without a billing account, but charges depend on usage. You can set up budgets/alerts after.
3. Set budgets & alerts in Billing → Budgets & alerts. (Required, change Variable 4 to "Yes" after this is complete so that we know this is okay to install.)
   • Google Documentation on Settings Budgets and Capping API Usage HERE.

4. Enable the Address Validation API

1. In the left menu, go to APIs & Services → Library.
2. Search for “Address Validation API”.
3. Click it, then click Enable.

5. Create an API key

1. Go to APIs & Services → Credentials.
2. Click + Create Credentials → API key.
3. You’ll see a new API key. Copy it, but don’t share it yet — restrict it first.

6. Restrict the key

• Click the key’s name to edit.
• Under API restrictions, select Restrict key and choose only Address Validation API.
• Under Application restrictions, select None (because Power Automate HTTP calls come from dynamic Microsoft IPs).
• Click Save.

(This combination — API restricted but application unrestricted — is the only viable setup for Power Automate HTTP calls.)

Why it matters

• Power Automate’s built-in HTTP action executes on Microsoft’s servers.
• Those servers don’t have fixed IP addresses you can whitelist.
• That means you cannot use IP-restricted API keys.
• Instead you must:
  • Create a normal API key,
  • Restrict it to only the Address Validation API,
  • Leave the application restriction as None (or, if you ever proxy through your own server, add IP restrictions then).
     

This is exactly what Google themselves recommend for server-to-server calls when you don’t have fixed IPs. The API restriction still prevents the key from being abused for other Google APIs.

7. Share the key securely

Add the restricted API key to the subscription under External Credentials on our website.

8. Best Practice

• Set budgets & alerts in Billing → Budgets & alerts. 
• Periodically review the APIs & Services → Dashboard to see usage.
   

Notes
Notes

• Rooftop = exact building. Only then do we sync map pins back to the customer.
• One address, many customers: The master table stores a single clean copy per unique address.
• 90-day recheck: Addresses used recently but not validated in 90 days are resent for validation to ensure USPS hasn’t changed anything.
• USPS bits: You may see DPV codes (e.g., missing apartment). They explain why something needs CONFIRM or CONFIRM_AD.

Troubleshooting

Troubleshooting

• Not getting the email? Ask your admin to add you to the subscription recipients.

• Same address keeps returning? It likely needs a unit number or USPS doesn’t recognize it yet—confirm and update.
• Edited an address in ARCustomers? The next nightly run detects the change, creates or reuses the correct address in the master table, and revalidates it.

Need Help?

Click the Help link in the email or contact CEOJuice support (include the Address ID from the CSV).