Member vs Casual Riders (Manhattan Demo) - Powered by Etherdata's Canonical US Census at H3 Resolution 8

Datasets

Methodology

-- Member vs casual riders
WITH
manhattan AS (
 SELECT county_geom AS geom
 FROM `bigquery-public-data.geo_us_boundaries.counties`
 WHERE state_fips_code = '36'
   AND RIGHT(county_fips_code, 3) = '061'
 LIMIT 1
),
manhattan_h3 AS (
 SELECT h3
 FROM manhattan m,
 UNNEST(bqcarto.h3.ST_ASH3_POLYFILL(m.geom, 8)) AS h3
),


trips_raw AS (
 SELECT
   DATE(t.starttime) AS trip_date,
   bqcarto.h3.LONGLAT_ASH3(t.start_station_longitude, t.start_station_latitude, 8) AS h3,
   LOWER(t.usertype) AS usertype_norm,
   SAFE_CAST(t.bikeid AS STRING) AS bikeid
 FROM `bigquery-public-data.new_york_citibike.citibike_trips` t
 WHERE DATE(t.starttime) BETWEEN DATE '2018-04-10' AND DATE '2018-09-10'
   AND t.start_station_latitude  BETWEEN 40.68 AND 40.88
   AND t.start_station_longitude BETWEEN -74.05 AND -73.90
   AND t.start_station_latitude  IS NOT NULL
   AND t.start_station_longitude IS NOT NULL
),


trips_manhattan AS (
 SELECT tr.*
 FROM trips_raw tr
 JOIN manhattan_h3 mh USING (h3)
),


-- Aggregate by (h3, trip_date)
trips_by_h3_day AS (
 SELECT
   h3,
   trip_date,


   COUNT(*) AS trips_total,
   COUNTIF(usertype_norm = 'subscriber') AS trips_member,
   COUNTIF(usertype_norm = 'customer')   AS trips_casual,


   COUNT(DISTINCT IF(usertype_norm = 'subscriber', bikeid, NULL)) AS distinct_bike_member,
   COUNT(DISTINCT IF(usertype_norm = 'customer',   bikeid, NULL)) AS distinct_bike_casual
 FROM trips_manhattan
 GROUP BY 1, 2
),


census_manhattan AS (
 SELECT
   c.h3,
   c.total_pop,
   c.pop_25_64,
   c.median_income,
   c.bachelors_degree_or_higher_25_64
 FROM `v3layer-pro.census_tract_h3r8.nydemo` c
 JOIN manhattan_h3 mh USING (h3)
 WHERE c.state_fips = '36'
   AND c.h3 IS NOT NULL
),


census_rollup AS (
 SELECT
   h3,
   SUM(total_pop) AS total_pop,
   SAFE_DIVIDE(SUM(median_income * total_pop), NULLIF(SUM(total_pop), 0)) AS median_income,
   SAFE_DIVIDE(
     SUM(bachelors_degree_or_higher_25_64),
     NULLIF(SUM(pop_25_64), 0)
   ) AS bachelors_or_higher_25_64_rate
 FROM census_manhattan
 GROUP BY 1
),


final AS (
 SELECT
   t.trip_date,
   t.h3,
   bqcarto.h3.ST_BOUNDARY(t.h3) AS geom,
   ST_Y(ST_CENTROID(bqcarto.h3.ST_BOUNDARY(t.h3))) AS lat,
   ST_X(ST_CENTROID(bqcarto.h3.ST_BOUNDARY(t.h3))) AS lon,


   t.trips_total,
   t.trips_member,
   t.trips_casual,


   SAFE_DIVIDE(t.trips_member, NULLIF(t.trips_total, 0)) AS member_penetration_rate,
   SAFE_DIVIDE(t.trips_casual, NULLIF(t.trips_member, 0)) AS casual_to_member_ratio,


   SAFE_DIVIDE(t.trips_member, NULLIF(t.distinct_bike_member, 0)) AS member_trips_per_bike_proxy,
   SAFE_DIVIDE(t.trips_casual, NULLIF(t.distinct_bike_casual, 0)) AS casual_trips_per_bike_proxy,


   c.total_pop,
   c.median_income,
   c.bachelors_or_higher_25_64_rate,


   CASE
     WHEN t.trips_total = 0 THEN 'NO_TRIPS'
     WHEN SAFE_DIVIDE(t.trips_member, NULLIF(t.trips_total, 0)) >= 0.70
          AND c.median_income >= 120000 THEN 'HIGH_MEMBER_HIGH_INCOME'
     WHEN SAFE_DIVIDE(t.trips_member, NULLIF(t.trips_total, 0)) < 0.40
          AND c.median_income < 80000 THEN 'LOW_MEMBER_LOWER_INCOME'
     WHEN SAFE_DIVIDE(t.trips_casual, NULLIF(t.trips_member, 0)) >= 1.5 THEN 'CASUAL_HEAVY'
     ELSE 'MIXED'
   END AS adoption_segment
 FROM trips_by_h3_day t
 LEFT JOIN census_rollup c USING (h3)
)


SELECT
 *,
 PERCENT_RANK() OVER (PARTITION BY trip_date ORDER BY member_penetration_rate) AS pct_member_penetration,
 PERCENT_RANK() OVER (PARTITION BY trip_date ORDER BY casual_to_member_ratio)  AS pct_casual_heaviness,


FROM final;
                    

Results & Interpretation (What the charts show)

What we can conclude (and what we cannot)

Why Etherdata’s canonical census at H3 R8 is the enabler