What You Will Learn
You will learn how to use Gemini and structured sources to enrich business rows with location context, such as city, country, region, population tier, weather condition, temperature, precipitation, and review status.
Required Dataset
Use the BrightLane_Retail_Operations_Dataset.
Use these tabs:
Stores
Weekly_Sales
Create three new tabs:
Location_Weather_Practice
Weather_Location_Check
Location_Analysis
Before You Start
Copy fields from Weekly_Sales into Location_Weather_Practice, including Week Start Date, Store ID, Product Category, Net Sales, Transactions, Foot Traffic, and Conversion Rate.
Use XLOOKUP to bring in store location fields from Stores, such as Store Name, City, State, Region, and Store Format.
Step-by-Step Instructions
Step 1. Ask Gemini to recommend enrichment fields
Use this prompt:
Act as a retail analytics consultant.
Use the Location_Weather_Practice tab.
Recommend useful weather and location enrichment fields for business analysis.
Include city, state, country, region, population tier, weather date, temperature, precipitation, weather condition, severe weather flag, source, confidence level, and review-needed status.
Explain which fields can be researched with Gemini and which require a structured API or approved dataset.
Step 2. Create a location key
Add a Location Key column:
=UPPER(TRIM(City)&”|”&TRIM(State)&”|UNITED STATES”)
Use actual cell references. This key helps match sales rows to location or weather data.
Step 3. Create a weather key
Add a Weather Key column:
=TEXT([Week Start Date],”yyyy-mm-dd”)&”|”&[Location Key]
This matters because weather depends on both location and date.
Step 4. Add weather fields
Add fields for temperature, precipitation, weather condition, severe weather flag, weather source, and source timestamp. For practice, enter sample values manually. For real analysis, use an approved weather API or historical weather dataset.
Expected Result
You should have a sales dataset enriched with store location and weather context that can support location-aware performance analysis.
Check Your Work
Confirm that store locations are joined correctly, location keys are consistent, weather keys include both date and location, weather units are clear, and weather data has a source.
Optional Extension
Ask Gemini:
Analyze Location_Analysis and identify whether sales, traffic, or conversion appear different during severe weather or by population tier. Use cautious language and do not claim causality.
