Introduction:
This document describes the design of updating the DRUID query in Analytics script for the District Level Report.
PRD Link : District Level Report
Implementation Design Link for Derived Location : Implementation design for Location Capture: Device and User
Jira Ticket :
SB-15589
-
Getting issue details...
STATUS
Currently, District level report uses device_loc_state and device_loc_district which are resolved from the IP address of the device from the device register API. As per the requirement mentioned in the above Jira ticket, the district and state details have to be taken by following priorities.
[Priorities mentioned in the ticket]: System should use the following data in the given order of priority to get the location data. This applies to both Mobile App and Portal:
If the user is an anonymous user: Use the state, district information explicitly provided by the user
If usage is by a logged-in user: Use the state, district information in the user profile
In either of the above two cases, if there is no state, district information: Use IP based state/town information from MaxMind + (City to District Mapping given by States)
The derived location data will be having the district and state details for the particular device as per passing the priorities. So, In the Druid query, the district and state details fields will be updated as mentioned below.
- device_loc_state → derived_loc_state
- device_loc_district → derived_loc_district
JTBD
- Jobs To Be Done: Update the DRUID query to take District from Derived location details for District level report.
Existing Design
Data Source: Druid
start_date, end_date State Name are programmatically replaced in the script.
{
"queryType": "groupBy",
"dataSource": {
"type": "table",
"name": "telemetry-events"
},
"intervals": {
"type": "intervals",
"intervals": [
"start_date/end_date"
]
},
"filter": {
"type": "and",
"fields": [
{
"type": "selector",
"dimension": "actor_type",
"value": "User",
"extractionFn": null
},
{
"type": "in",
"dimension": "context_pdata_id",
"values": [
"prod.diksha.app",
"prod.diksha.portal"
],
"extractionFn": null
},
{
"type": "selector",
"dimension": "device_loc_state",
"value": "state_name",
"extractionFn": null
}
]
},
"granularity": {
"type": "all"
},
"dimensions": [
{
"type": "default",
"dimension": "device_loc_district",
"outputName": "District",
"outputType": "STRING"
},
{
"type": "default",
"dimension": "context_pdata_id",
"outputName": "Platform",
"outputType": "STRING"
}
],
"aggregations": [
{
"fieldName": "context_did",
"type": "distinctCount",
"name": "Unique Devices"
}
],
"postAggregations": [],
"having": null,
"limitSpec": {
"type": "NoopLimitSpec"
},
"context": {},
"descending": false
}
{
"queryType": "groupBy",
"dataSource": {
"type": "table",
"name": "telemetry-events"
},
"intervals": {
"type": "intervals",
"intervals": [
"start_date/end_date"
]
},
"filter": {
"type": "and",
"fields": [
{
"type": "selector",
"dimension": "eid",
"value": "SEARCH",
"extractionFn": null
},
{
"type": "not",
"field": {
"type": "selector",
"dimension": "edata_filters_dialcodes",
"value": null
}
},
{
"type": "in",
"dimension": "context_pdata_id",
"values": [
"prod.diksha.app",
"prod.diksha.portal"
],
"extractionFn": null
},
{
"type": "selector",
"dimension": "device_loc_state",
"value": "state_name",
"extractionFn": null
}
]
},
"granularity": {
"type": "all"
},
"dimensions": [
{
"type": "default",
"dimension": "device_loc_district",
"outputName": "District",
"outputType": "STRING"
},
{
"type": "default",
"dimension": "context_pdata_id",
"outputName": "Platform",
"outputType": "STRING"
}
],
"aggregations": [
{
"type": "count",
"name": "Number of QR Scans"
}
],
"postAggregations": [],
"having": null,
"limitSpec": {
"type": "NoopLimitSpec"
},
"context": {},
"descending": false
}
{
"queryType": "groupBy",
"dataSource": {
"type": "table",
"name": "summary-events"
},
"intervals": {
"type": "intervals",
"intervals": [
"start_date/end_date"
]
},
"filter": {
"type": "and",
"fields": [
{
"type": "in",
"dimension": "dimensions_pdata_id",
"values": [
"prod.diksha.app",
"prod.diksha.portal"
],
"extractionFn": null
},
{
"type": "selector",
"dimension": "dimensions_mode",
"value": "play"
},
{
"type": "selector",
"dimension": "dimensions_type",
"value": "content"
},
{
"type": "selector",
"dimension": "device_loc_state",
"value": "state_name"
}
]
},
"granularity": {
"type": "all"
},
"dimensions": [
{
"type": "default",
"dimension": "device_loc_district",
"outputName": "District",
"outputType": "STRING"
},
{
"type": "default",
"dimension": "dimensions_pdata_id",
"outputName": "Platform",
"outputType": "STRING"
}
],
"aggregations": [
{
"type": "count",
"name": "Number of Content Plays"
}
],
"postAggregations": [],
"having": null,
"limitSpec": {
"type": "NoopLimitSpec"
},
"context": {},
"descending": false
}
Proposed Design
Data Source: Druid
Start Date, End Date and State Name are programmatically replaced in the script.
{
"queryType": "groupBy",
"dataSource": {
"type": "table",
"name": "telemetry-events"
},
"intervals": {
"type": "intervals",
"intervals": [
"start_date/end_date"
]
},
"filter": {
"type": "and",
"fields": [
{
"type": "selector",
"dimension": "actor_type",
"value": "User",
"extractionFn": null
},
{
"type": "in",
"dimension": "context_pdata_id",
"values": [
"prod.diksha.app",
"prod.diksha.portal"
],
"extractionFn": null
},
{
"type": "selector",
"dimension": "derived_loc_state",
"value": "state_name",
"extractionFn": null
}
]
},
"granularity": {
"type": "all"
},
"dimensions": [
{
"type": "default",
"dimension": "derived_loc_district",
"outputName": "District",
"outputType": "STRING"
},
{
"type": "default",
"dimension": "context_pdata_id",
"outputName": "Platform",
"outputType": "STRING"
}
],
"aggregations": [
{
"fieldName": "context_did",
"type": "distinctCount",
"name": "Unique Devices"
}
],
"postAggregations": [],
"having": null,
"limitSpec": {
"type": "NoopLimitSpec"
},
"context": {},
"descending": false
}
{
"queryType": "groupBy",
"dataSource": {
"type": "table",
"name": "telemetry-events"
},
"intervals": {
"type": "intervals",
"intervals": [
"start_date/end_date"
]
},
"filter": {
"type": "and",
"fields": [
{
"type": "selector",
"dimension": "eid",
"value": "SEARCH",
"extractionFn": null
},
{
"type": "not",
"field": {
"type": "selector",
"dimension": "edata_filters_dialcodes",
"value": null
}
},
{
"type": "in",
"dimension": "context_pdata_id",
"values": [
"prod.diksha.app",
"prod.diksha.portal"
],
"extractionFn": null
},
{
"type": "selector",
"dimension": "derived_loc_state",
"value": "state_name",
"extractionFn": null
}
]
},
"granularity": {
"type": "all"
},
"dimensions": [
{
"type": "default",
"dimension": "derived_loc_district",
"outputName": "District",
"outputType": "STRING"
},
{
"type": "default",
"dimension": "context_pdata_id",
"outputName": "Platform",
"outputType": "STRING"
}
],
"aggregations": [
{
"type": "count",
"name": "Number of QR Scans"
}
],
"postAggregations": [],
"having": null,
"limitSpec": {
"type": "NoopLimitSpec"
},
"context": {},
"descending": false
}
{
"queryType": "groupBy",
"dataSource": {
"type": "table",
"name": "summary-events"
},
"intervals": {
"type": "intervals",
"intervals": [
"start_date/end_date"
]
},
"filter": {
"type": "and",
"fields": [
{
"type": "in",
"dimension": "dimensions_pdata_id",
"values": [
"prod.diksha.app",
"prod.diksha.portal"
],
"extractionFn": null
},
{
"type": "selector",
"dimension": "dimensions_mode",
"value": "play"
},
{
"type": "selector",
"dimension": "dimensions_type",
"value": "content"
},
{
"type": "selector",
"dimension": "derived_loc_state",
"value": "state_name"
}
]
},
"granularity": {
"type": "all"
},
"dimensions": [
{
"type": "default",
"dimension": "derived_loc_district",
"outputName": "District",
"outputType": "STRING"
},
{
"type": "default",
"dimension": "dimensions_pdata_id",
"outputName": "Platform",
"outputType": "STRING"
}
],
"aggregations": [
{
"type": "count",
"name": "Number of Content Plays"
}
],
"postAggregations": [],
"having": null,
"limitSpec": {
"type": "NoopLimitSpec"
},
"context": {},
"descending": false
}
Queries:
No queries