The Scenario
The quarterly ad targeting report is due Friday. You have 8,000 visitor IP addresses from last month's analytics export sitting in column A of your Google Sheet — raw addresses, no country, no city, nothing your media buyer can use to evaluate regional campaign performance. The expectation from the marketing director is a breakdown of traffic by country, with percentages, that fits into the slide deck she's building Thursday night.
The bad version:
- Manually run the IPs through IP2Location's web tool in batches of 1,000 — eight separate trips, eight separate CSV downloads.
- Concatenate the CSVs, discover the column order shifted between batch three and batch four, spend an hour reconciling the mismatch.
- Try to write a COUNTIF formula to produce the country breakdown table, realize the country name column has inconsistent casing from the concatenation step, fix that, then re-run the formula.
By the time the table is ready the marketing director has already built the slide with placeholder text and you're now revising a file someone else is actively editing.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads your IP column and through its built-in IP2Location integration it enriches every address with country and city data — in batches of 1,000 automatically — then builds the regional breakdown table you need in the same session.
Look up the geolocation for every IP in column A using IP2Location bulk lookup in batches of 1,000 and write country code, country name, region, and city to columns B, C, D, and E. Then add a summary table below the data showing the top 10 countries by visitor count with their percentage of total traffic.
What You Get
- Column B: ISO country code (US, DE, IN, etc.)
- Column C: full country name
- Column D: region or state
- Column E: city name
- A summary table appended below the data rows with country name, visitor count, and percentage columns — formatted and ready to copy into a slide or pivot table
What If the Data Is Not Quite Ready
The export has duplicate IPs that would skew the country counts
Before enriching, deduplicate column A so each unique IP appears only once, then run IP2Location bulk lookup writing country code, country name, region, and city to columns B through E. Build the top-10 summary table based on deduplicated visitor counts.
I need the breakdown segmented by campaign source
The sheet has a campaign source in column B and visitor IPs in column C. Enrich the IPs using IP2Location to add country code in column D, then build a pivot-style summary table on a new sheet called "By Campaign" showing visitor count by country for each campaign source.
Some IPs are internal or private-range addresses that shouldn't count
Before enriching column A with IP2Location, filter out any IPs in private ranges (10.x.x.x, 192.168.x.x, 172.16–31.x.x) — move them to a "Excluded IPs" sheet, then enrich the remaining IPs and build the top-10 country breakdown.
Full kill chain — deduplicate, enrich, filter, and summarize in one prompt
Deduplicate column A, exclude private-range IPs by moving them to "Excluded IPs", enrich the remaining IPs with IP2Location writing country code and country name to columns B and C, then add a summary table below showing the top 10 countries by visitor count with their share of total traffic.
One prompt does the deduplication, the exclusion, the enrichment, and the summary — without you orchestrating each step separately.
Try It
Get the 7-day free trial of SheetXAI and open any analytics export with a column of visitor IPs, then ask it to map them to countries and build a regional breakdown using IP2Location. You can also explore bulk enrichment with proxy detection or return to the IP2Location overview to see all the ways SheetXAI connects to this integration.