# How many base pages use origins/CDNs within the country borders? **URL:** \ **Category:** Analysis\ **Created:** [September 28, 2019, 5:43pm UTC](https://discuss.httparchive.org/t/how-many-base-pages-use-origins-cdns-within-the-country-borders/1739 "2019-09-28T17:43:52Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![saibalaji](https://yyz1.discourse-cdn.com/flex035/user_avatar/discuss.httparchive.org/saibalaji/32/1657_2.png) [@saibalaji](https://discuss.httparchive.org/u/saibalaji)\ **Post date:** [September 28, 2019, 5:43pm UTC](https://discuss.httparchive.org/t/how-many-base-pages-use-origins-cdns-within-the-country-borders/1739/1 "2019-09-28T17:43:52Z")
Hello people, I’m trying to classify the user activity of the users in India using the Crux Country dataset across the following categories based on the number of total base pages that 1. Hit ‘Direct Origin’ within the country 2. Hit ‘Direct Origin’ outside of the country 3. Hit ‘CDN’ PoP within the country 4. Hit ‘CDN’ PoP outside the country What I’m getting is very strange. Out of 298415 base pages, I see only 9 base pages that are hitting a CDN PoP within India. That’s 0.004%. I’m looking for some help to both check the correctness of the logic and tuning of this SQL query. Here’s the result - ![08%20PM](https://canada1.discourse-cdn.com/flex035/uploads/httparchive/original/2X/8/88475aab4c362b2c410827ae639cb1030646c46a.png) … And the SQL query WITH numpages\_origin\_type\_geotype\_table as ( WITH page\_ip\_origintype\_geoipdetails\_table as ( WITH page\_ip\_origintype\_table as (WITH page\_ip\_cdn\_table AS (#distinct pages list(298415 rows) SELECT DISTINCT requests.page as p, JSON\_EXTRACT\_SCALAR(payload,".\_ip\_addr") as ip, JSON\_EXTRACT\_SCALAR(payload,".\_cdn\_provider") as cdn FROM `httparchive.requests.2019_08_01_*` as requests JOIN (SELECT DISTINCT url FROM `httparchive.pages.2019_08_01_*` as pages JOIN `chrome-ux-report.country_in.201908` as crux ON CONCAT(origin, ‘/’) = url GROUP BY url) as pages\_crux ON (pages\_crux.url = requests.page) and (pages\_crux.url = requests.url)) #origin type addition (298415 rows) SELECT p, ip, CASE WHEN cdn = ‘’ THEN “Direct Origin” WHEN cdn = ‘null’ THEN “Direct Origin” WHEN cdn iS NULL THEN “Direct Origin” ELSE “CDN Enabled” END as origin\_type FROM ( SELECT p, ip, cdn from page\_ip\_cdn\_table)) #Geo details addition (298415 rows) SELECT p, origin\_type, NET.IPV4\_TO\_INT64(NET.SAFE\_IP\_FROM\_STRING(ip)) clientIpNum, TRUNC(NET.IPV4\_TO\_INT64(NET.SAFE\_IP\_FROM\_STRING(ip))/(256\*256)) classB FROM page\_ip\_origintype\_table) #Geo\_type addition (208030 rows) SELECT p, origin\_type, CASE WHEN (countryLabel = “India”) THEN “Within India” ELSE “Outside of India” END as geo\_type FROM page\_ip\_origintype\_geoipdetails\_table JOIN `fh-bigquery.geocode.geolite_city_bq_b2b` as geotable USING (classB) WHERE clientIpNum BETWEEN startIpNum AND endIpNum) #no of pages based on origin and geo SELECT COUNT§ as num\_pages, origin\_type, geo\_type FROM numpages\_origin\_type\_geotype\_table GROUP BY origin\_type, geo\_type ORDER BY num\_pages DESC; ---
**Author:** ![paulcalvano](https://yyz1.discourse-cdn.com/flex035/user_avatar/discuss.httparchive.org/paulcalvano/32/1601_2.png) [@paulcalvano](https://discuss.httparchive.org/u/paulcalvano)\ **Post date:** [September 29, 2019, 2:43am UTC](https://discuss.httparchive.org/t/how-many-base-pages-use-origins-cdns-within-the-country-borders/1739/2 "2019-09-29T02:43:43Z")
Hi @saibalaji, While you can use CrUX to find sites that receive traffic within certain countries, the HTTP Archive data does not provide any synthetic measurements from the context of a user in those countries. All measurements are all run from Redwood City, CA in the US - so ideally the IP addresses you are seeing traffic resolve to will mostly be on the US west coast. There’s a few other factors at play here that will complicate this: - Some CDNs use Anycast, and your IP geolocation database will not provide an accurate result. Traceroutes can be helpful here, but you would need to run them from India. - The accuracy of the IP Geolocation database can limit some of your results. The geolite\_city\_bq\_b2b table you are querying was last updated in 2013… ![image](https://canada1.discourse-cdn.com/flex035/uploads/httparchive/original/2X/9/92eca6f5e30e67e76892047b2e9ad2b2a165ba68.png) Paul ---
**Author:** ![saibalaji](https://yyz1.discourse-cdn.com/flex035/user_avatar/discuss.httparchive.org/saibalaji/32/1657_2.png) [@saibalaji](https://discuss.httparchive.org/u/saibalaji)\ **Post date:** [September 29, 2019, 4:30pm UTC](https://discuss.httparchive.org/t/how-many-base-pages-use-origins-cdns-within-the-country-borders/1739/3 "2019-09-29T16:30:22Z")
This really helps! Thanks for the clarification. ---
**Author:** ![geekybiz](https://avatars.discourse-cdn.com/v4/letter/g/9fc29f/32.png) [@geekybiz](https://discuss.httparchive.org/u/geekybiz)\ **Post date:** [November 25, 2019, 12:20pm UTC](https://discuss.httparchive.org/t/how-many-base-pages-use-origins-cdns-within-the-country-borders/1739/4 "2019-11-25T12:20:07Z")
Hi @saibalaji , Did you find resolution to your issues - specifically ip geolocation db that is more recent? I’m trying to find a way to check number of base pages using origins within the country border. Thanks