geo.sql

SELECT
  -- Continent (dimension)
  geoNetwork.continent AS Continent,
  -- Sub Continent (dimension)
  geoNetwork.subContinent AS Sub_Continent,
  -- Country (dimension)
  geoNetwork.country AS Country,
  -- Region (dimension)
  geoNetwork.region AS Region,
  -- Metro (dimension)
  geoNetwork.metro AS Metro,
  -- City (dimension)
  geoNetwork.city AS City,
  -- Latitude (dimension)
  geoNetwork.latitude AS Latitude,
  -- Longitude (dimension)
  geoNetwork.longitude AS Longitude,
  -- Network Domain (dimension)
  geoNetwork.networkDomain AS Network_Domain,
  -- Service Provider (dimension)
  geoNetwork.networkLocation AS Service_Provider,
  -- City ID (dimension)
  geoNetwork.cityId AS City_ID
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _table_suffix BETWEEN '20160801'
  AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  AND totals.visits = 1
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11
LIMIT
  10