The Scenario
Your team runs a monthly audience segmentation report for a media client. This month's source data landed in an Excel workbook: 900 rows of raw IP addresses pulled from nginx access logs, sitting in column A with nothing else filled in.
The report needs country, region, and timezone columns before you can build the geographic breakdown. Without them, the segmentation chart is a blank placeholder.
The bad version:
- Export the IP list as a CSV, write a Python script against BigDataCloud's bulk geolocation endpoint, debug the rate-limit handling when the script stalls at row 312.
- Parse the JSON response manually, match each result back to the original row by index, import the enriched CSV back into the workbook.
- Realize the script used a different field name for region than what the report template expects, go back through the output and rename it before the client call.
You were hired to analyze the audience data — not to maintain API scripts. The client presentation is this afternoon, and the segmentation chart is still empty.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that runs inside your Excel workbook, reads the data you're looking at, and calls BigDataCloud on your behalf. You describe the task in plain language — it handles the endpoint selection, the API calls, and the column writebacks.
Open SheetXAI from the Excel add-in panel and type:
Geolocate all IPs in my Excel sheet and add columns for country name, country code, city, and UTC offset
What You Get
- Column B: country name (e.g., "United States", "Germany", "Brazil")
- Column C: ISO country code (e.g., "US", "DE", "BR")
- Column D: city as returned by BigDataCloud
- Column E: UTC offset as a signed integer
- Rows where the IP returns no geolocation result get a blank cell; SheetXAI notes the count of unresolved rows in the add-in panel
What If the Data Is Not Quite Ready
The IP column has mixed formatting — some entries have port numbers appended
For every IP in column A (some entries have port numbers like 192.168.1.1:8080), strip the port, geolocate the base IP using BigDataCloud, and write country name, country code, city, and UTC offset into columns B through E
Some rows are internal or reserved IPs that BigDataCloud won't resolve
Geolocate every IP in column A using BigDataCloud — skip any rows where the IP is in a private or reserved range and mark those rows "internal" in column B
The source data spans two worksheets — one for APAC logs, one for EMEA
Geolocate the IPs in column A on the "APAC" worksheet and the "EMEA" worksheet using BigDataCloud, write country name, country code, city, and UTC offset into columns B through E on each sheet, then copy all rows into a combined worksheet called "Geo Results"
Full enrichment pass plus a geographic summary in one shot
For every unique IP in column A, fetch country name, country code, city, and UTC offset from BigDataCloud into columns B through E — then on a new worksheet called "Summary" write a count of rows per country, sorted descending, and flag any country that accounts for more than 20% of total sessions
One prompt handles the enrichment, the deduplication, and the summary pivot.
Try It
Get the 7-day free trial of SheetXAI and open any Excel workbook with a column of raw IPs — paste the prompt above into the add-in panel and watch the country, city, and timezone columns fill in. Then check out the hub overview for more BigDataCloud tasks, or see the IP threat scan spoke if fraud flags are next on your list.