Skip to content

The Device Metrics Asset User Guide

Introduction

The Device Metrics asset is a table containing a large set of user aggregated metrics from Spectus Core Data Assets, like Device Location, Stops, etc.

The purpose of this table is to provide a unique resource of user-level aggregates in order to obtain coherent easy-to-use tables.

Data availability

Please note all the single-asset metrics have the same partitioning keys (data provider, and event date), all the metrics will be consolidated in the Device Metrics table when they are available, i.e. data partitioned by processing date, for example in Device Location, will be available when we receive all data for the considered event date, time depending on the data provider (2 to 4 days).

Table overview

The Device Metrics table contains the following information:

  • Device identification fields
  • Footprint metrics
  • Device location metrics
  • Device location coverage metrics
  • Personal area fields
  • Stop coverage metrics
  • Stop metrics
  • Stop transformed metrics

As stated above, the partition keys are data_provider and event_date.

Fields details

Partition keys
Field Type Description
data_provider string The data provider identifier code
event_date int Event date (format: 'YYYYmmdd')

Device Identification fields
Field Type Description
cuebiq_id bigint Privacy compliant device identifier
id_type string ???
os_name string Ex.: ANDROID/IOS
device_carrier_code string Carrier code identifier
device_manufacturer string Smartphone manufacturer
device_model string Smartphone model
os_version string Smartphone os version

Device Location Coverage Metrics
Field Type Description
ping_distinct_hour_count int Number of distinct hours where was collected at least one point
ping_distinct_minutes_count int Number of distinct minutes where was collected at least one point

Device location metrics
Field Type Description
ping_count int Number of device location points
accuracy_min double Minimum accuracy value
accuracy_max double Maximum accuracy value
speed_ms_max double Maximum instant speed (m/sec)
gap_between_pings_seconds_max long The maximum gap between two consecutive points in seconds. To compute this metric we consider also a first point at the start of the day and another one at the end of the day. Given that the timezone_offset of a device can be more than one (it happens ~2% of the time), we compute the start timestamp of this window considering the maximum timezone_offset and the end of the window considering the minimum timezone_offset (the worst scenario).

Stops coverage Metrics
Field Type Description
stop_daily_coverage_count_hh_{X} bigint [24 columns with 0 ≤ X ≤ 23] Number of minutes covered by stops during hour X (from X:00 to X:59 inclusive)
stop_daily_home_coverage_count_hh_{X} bigint [24 columns with 0 ≤ X ≤ 23] Number of minutes covered by stops at home during hour X (from X:00 to X:59 inclusive)

Stops metrics
Field Type Description
stop_count int Number of stops
stop_cluster_size_sum int The sum of stop clusters size
stop_dwell_time_minutes_sum double Sum of all the stop's dwell time
stop_dwell_time_minutes_at_home_sum double Sum of all the stop's dwell time when at home
stop_dwell_time_minutes_at_work_sum double Sum of all the stop's dwell time when at work

Personal Area fields
Field Type Description
home_known boolean True if home coordinates are known
home_country string Home location country
home_admin1 string Home location admin 1
home_admin2 string Home location admin 2
home_block_group_id string Home location block group id
work_known boolean True if work coordinates are known
work_country string Work location country
work_admin1 string Work location admin 1
work_admin2 string Work location admin 2
work_block_group_id string Work location block group id
transformed_work_distance_meters_from_home double Distance between home and work location

Footprint Metrics

From Device Location
Field Type Description
ping_bounding_box string WKT representation of the smallest box containing all the device location points
ping_bounding_box_diagonal_meters double Length, in meters, of the diagonal of the smallest box containing all the device location points
ping_distinct_country array The unique list of countries where the device was seen. Example: [US, CA]
ping_distinct_admin1 array The unique list of admin1 where the device was seen. Example: [US.MO]
ping_distinct_admin2 array The unique list of admin2 where the device was seen. Example: [US.MO.189, US.MO.099]
ping_distinct_geohash_{X}_count bigint [7 columns with 3 ≤ X ≤ 9] Approximate number of distinct geohashes (level X)
timezone_offset_max bigint Maximum timezone_offset
timezone_offset_min bigint Minimum timezone_offset

From Stops
Field Type Description
stop_bounding_box string WKT representation of the smallest box containing all the device stops
stop_bounding_box_diagonal_meters double Length, in meters, of the diagonal of the smallest box containing all the device stops
transformed_work_distance_meters_from_home double Distance between centroids of the representative areas containing the device's home and work locations
transformed_stop_distance_from_home_meters_min double The minimum distance between the stop transformed lat and long and the centroid of the representative area containing the device's home location
transformed_stop_distance_from_home_meters_max double The maximum distance between the stop transformed lat and long and the centroid of the representative area containing the device's home location
transformed_stop_distance_from_work_min double The minimum distance between the stop transformed lat and long and the centroid of the representative area containing the device's work location
transformed_stop_distance_from_work_max double The maximum distance between the stop transformed lat and long and the centroid of the representative area containing the device's work location
stop_distinct_country array The unique list of countries where the device made stops. Example: [US, CA]
stop_distinct_admin1 array The unique list of admin1 where the device made stops. Example: [US.MO]
stop_distinct_admin2 array The unique list of admin2 where the device made stops. Example: [US.MO.189, US.MO.099]
stop_distinct_geohash_{X}_count bigint [7 columns with 3 ≤ X ≤ 9] Approximate number of distinct geohashes (level X)