Normalise
Standardise CPC values, numeric fields, spelling variants and accents.
Data analytics case study · SEO market analytics
I compared search demand, query intent and sampled competitors to recommend a launch sequence for a new bus travel vertical in Spain, France and the United Kingdom.
Bars compare monthly keyword search volume on one scale. Mint shows the non-brand portion; percentages show its share within each market.
A focused starting point for testing route-led acquisition.
Estimated monthly searches in the supplied keyword set.
Search volume for queries without a named brand or operator.
The strongest non-brand ratio in the comparison.
Business context
A professional assessment for a European online travel company asked which market should lead a new organic bus travel offering. I used supplied keyword and ranking data to compare the options and identify a practical launch focus.
My contribution
I normalised the data, classified brand queries, search intent and competitor types, built a keyword-to-ranking crosswalk and checked the summary totals. I translated those findings into market priorities and a route-led site architecture.
Working approach Excel analysis · Data quality checks · SQL examples for reproducibility
Market evidence
The UK leads on total volume in the supplied keyword set. Switch metrics to compare absolute non-brand volume with its share of each market.
Select a metric, then a market. Volumes are sums of keyword estimates, not unique users or the whole national travel market; non-brand queries do not guarantee ranking ease or traffic.
| Market | Total searches | Non-brand searches | Non-brand share |
|---|---|---|---|
| Spain | 653,900 | 348,100 | 53.2% |
| France | 534,500 | 372,300 | 69.7% |
| United Kingdom | 958,700 | 387,000 | 40.4% |
Analysis lenses
Transactional queries lead in every market. Compare their share, the non-brand query mix and the types of competitors in the ranking sample.
Transactional queries account for 68.3% of France’s total keyword volume, compared with 51.6% in Spain and 55.3% in the UK.
Volume and share of the selected market total. Source: Summary_Tables in the analytical workbook.
Method and data quality
I made the supplied tables comparable before interpreting demand. Duplicate flags protected the totals, brand classification clarified the query mix and match status kept uncertain ranking assignments visible.
Standardise CPC values, numeric fields, spelling variants and accents.
Exclude both duplicate-flagged records from market totals and retain them for inspection.
Separate brand and operator queries from non-brand demand.
Group sampled ranking records into operators, aggregators and online travel agencies (OTAs), or marketplaces.
Link normalised queries to ranking records, retain uncertain matches and validate summaries.
Market totals use 211 keyword records; two duplicate-flagged rows are excluded. The ranking crosswalk retains 117 matched, four ambiguous and four unmatched records. Ranking samples cover 50 of the 211 included keyword records.
SELECT
market,
SUM(avg_monthly_searches) AS non_brand_search_volume,
COUNT(DISTINCT keyword_norm) AS non_brand_keywords
FROM keyword_clean
WHERE brand_flag = FALSE
AND dup_keyword_market_flag = FALSE
GROUP BY market
ORDER BY non_brand_search_volume DESC;
Decision logic
The recommendation changes when headline volume is read together with brand dependence, scalable query types and the visible competitor mix.
The UK has the largest total volume (958,700) and non-brand volume (387,000). However, 59.6% of its keyword volume is brand-related, and operators account for 32 of 54 ranking records (59.3%), suggesting a more selective entry test.
France has a slightly smaller non-brand base than the UK (372,300), but the highest non-brand share (69.7%) and transactional share (68.3%). Operators account for 12 of 38 ranking records (31.6%), supporting a focused initial test.
City-to-city route queries sum to 688,300 estimated monthly searches, distributed almost evenly across the three markets. That supports a common route-page structure with local prices, schedules and transfer information.
I recommended starting with a focused route-page test, learning from performance, then adapting the model to further markets.
Test the route-led architecture in a market with a strongly transactional query mix. Use the launch to validate rankings, qualified traffic and conversion.
Adapt the tested structure to Spanish routes, schedules and booking queries, using local demand and performance to guide expansion.
Pilot selected city-to-city routes and airport-transfer clusters before committing to wider coverage in the most operator-heavy sample.
Project evidence
Review the underlying data and QA checks, or download the summary of the business question, findings and recommendation.