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, then decide whether to debug or just leave them blank and explain it later.
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 Google Sheet 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.
For each IP address in column B of my Customers sheet, use IQAir to look up the nearest city's current AQI and write the city name and AQI into columns C and D.
What You Get
- Column C: nearest city name resolved from each IP address.
- Column D: current US AQI for that city at time of pull.
- 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 B for known datacenter IP ranges and flag those rows in column E as Proxy — likely wrong location. Then run the IQAir city lookup for the remaining rows and write city and AQI into columns C and D.
The export has duplicate IP addresses across multiple customers
Some IP addresses in column B appear more than once in the Customers sheet. Deduplicate the IQAir lookup by IP — only call IQAir once per unique IP — and apply the same city and AQI result to all rows sharing that IP.
You need to join the AQI result with a second tab of segment definitions
Look up the nearest city AQI from IQAir for each IP in column B, write the AQI into column D, then join against my AQI Segments tab where column A has AQI ranges and column B has segment labels, and write the matching segment label into column E of the Customers sheet.
Geo-resolve, pull AQI, segment, and write the campaign tag in one pass
For each IP in column B of my Customers sheet, resolve the nearest city and AQI from IQAir, write city into column C and AQI into column D, look up the AQI band in my Segments tab, write the segment label into column E, and add a Campaign Tag of High AQI Bundle in column F 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 city and 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 Google Sheets.