The Scenario
The campaign brief landed this morning: target customers in high-AQI markets with the air purifier bundle promotion. Marketing exported 500 customer records with IP addresses last week. The CRM has the email, the segment label, and the IP. What it does not have is local AQI — the whole reason the brief exists.
You have been handed the export and asked to add an estimated local AQI column before the segmentation model runs tomorrow.
The bad version:
- Research how IP geolocation works, find a service that can go from IP to city, figure out how that maps to an IQAir city lookup, then chain the two API calls for each of 500 rows.
- Write code that handles the IP rows where geolocation returns a country but no city, the rows where IQAir has no data for the returned city, and the rows where the IP is a corporate proxy that geolocates to an entirely wrong region.
- Finish the script, run it, and find that 80 rows came back empty for reasons that are not immediately obvious.
The segmentation model runs tomorrow. You are not a data engineer. This is a one-time enrichment pass, not a production pipeline.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook that can take IP addresses, resolve their nearest city via IQAir's geo-lookup, and write the AQI result back — handling the IP-to-city resolution and the AQI pull in a single step.
I have 200 user records in my Excel sheet with IP addresses in column E. Fetch the nearest city AQI for each IP from AirVisual and add the AQI value in column F labeled Local AQI.
What You Get
- Column F labeled Local AQI: current US AQI for the city nearest to each IP address.
- Rows where IP geolocation returns no city, or where IQAir has no data for the resolved city, get a status label rather than a blank that the segmentation model will treat as zero.
What If the Data Is Not Quite Ready
Some IP addresses are known proxies or VPNs
Before running the AQI lookup, check column E for known datacenter IP ranges and flag those rows in column G as Proxy — likely wrong location. Then run the IQAir city lookup for the remaining rows and write AQI into column F.
The export has duplicate IP addresses across multiple customers
Some IP addresses in column E appear more than once. Deduplicate the IQAir lookup by IP — only call IQAir once per unique IP — and apply the same AQI result to all rows sharing that IP in column F.
You need to join the AQI result with a second worksheet of segment definitions
Look up the nearest city AQI from IQAir for each IP in column E, write the AQI into column F, then join against my AQI Segments worksheet where column A has AQI ranges and column B has segment labels, and write the matching segment label into column G of the Customers worksheet.
Geo-resolve, pull AQI, segment, and write the campaign tag in one pass
For each IP in column E of my Customers worksheet, resolve the nearest city and AQI from IQAir, write the AQI into column F, look up the AQI band in my Segments worksheet, write the segment label into column G, and add a Campaign Tag of High AQI Bundle in column H for any customer in the Unhealthy or Very Unhealthy segments.
The full enrichment pipeline in one instruction.
Try It
Get the 7-day free trial of SheetXAI and open the customer export with the IP address column, then ask it to resolve local AQI and write back the segment label. For related workflows, see the spoke on enriching GPS coordinates with nearest-station AQI, or the hub overview on connecting IQAir AirVisual to Excel.