The following document defines the tables accessible within Query for the creation of custom queries.
Clicks Tables
The following tables associated with Click data are accessible within Query.
click_identifiers:
| Table Column | Description |
|---|---|
| *click_id | This is the primary key of this table and is used to join out to other tables within Kochava Query. |
| date_received | The datetime (10-digit epoch timestamp) the click was received. |
| identifier_type | The type of identifier received from the click. |
| identifier_value | A device identifier corresponding to the identifier_type of the row. |
* Indicates the foreign key for Click Tables JOIN.
click_properties:
| Table Column | Description | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| app_name | The user-defined name for the app from which the click originated. | ||||||||||||
| appstore_id | The app-specific ID on the app store sites. | ||||||||||||
| carrier | The wireless carrier used on the device where the click originated. | ||||||||||||
| click_cost | The user-defined cost per click. | ||||||||||||
| *click_id | This is the primary key of this table and is used to join out to other tables within Kochava Query. | ||||||||||||
| cost_type | The Cost per Click, Cost per Install, or Cost per Mille. | ||||||||||||
| country_code | 2-digit country designation where the click originated, based on IP lookup. | ||||||||||||
| cp1 thru cp15 | User-defined custom parameters.
NOTE: Custom parameters available in the UI do not match the customer parameters available in Query. However, the click/UI parameters will map to the Query parameters with an offset of 1.
|
||||||||||||
| creative_id | The network-supplied network_id value received. | ||||||||||||
| creative_size | The dimensions of the served creative. | ||||||||||||
| date_received | The datetime (10-digit epoch timestamp) the click was received. | ||||||||||||
| dma | The code for the designated marketing area where the click originated, based on IP lookup. | ||||||||||||
| exchange | If an ad exchange is utilized, the name of the exchange will be displayed. | ||||||||||||
| http_referrer | The raw URL which was received from network referral. | ||||||||||||
| original_request | The query parameters and values received. | ||||||||||||
| partner_click_id | The click ID as supplied by the network and received. | ||||||||||||
| referrer | The Google Referrer parameters passed. | ||||||||||||
| site_id | Identifies the site to which the click was served within the publisher partner. |
* Indicates the foreign key for Click Tables JOIN.
clicks_primary:
| Table Column | Description | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| adid | Raw ADID received (Android specific). | ||||||||||||
| android_id | Raw Android_ID received (Android specific). | ||||||||||||
| attribution_module | The module (Kochava or SAN) in which attribution was determined. | ||||||||||||
| campaign_name | The user-defined name for the campaign to which the install was attributed. | ||||||||||||
| city | The name of the city where the click originated based on IP lookup. | ||||||||||||
| *click_id | This is the primary key of this table and is used to join out to other tables within Kochava Query. | ||||||||||||
| cost | The user-defined cost per click. | ||||||||||||
| cost_type | The Cost per Click, Cost per Install, or Cost per Mille. | ||||||||||||
| country_code | 2-digit country designation where the click originated, based on IP lookup. | ||||||||||||
| cp1 thru cp15 | User-defined custom parameters.
NOTE: Custom parameters available in the UI do not match the customer parameters available in Query. However, the click/UI parameters will map to the Query parameters with an offset of 1.
|
||||||||||||
| creative_id | The network-supplied network_id value received. | ||||||||||||
| date_received | The datetime (10-digit epoch timestamp) the click was received. | ||||||||||||
| device_marketing_name | The marketing name used to describe the device by the manufacturer (e.g. Galaxy S7). | ||||||||||||
| device_model | The model of the device that installed the app. | ||||||||||||
| device_os_name | The name of the operating system installed on the device. | ||||||||||||
| device_os_version | The version of the operating system installed on the device. | ||||||||||||
| device_ua | User Agent of the device gathered upon click. | ||||||||||||
| device_ver | The device type and version of the device received on click. | ||||||||||||
| duplicate | The click_id of the duplicate click. If the value is 0, there is no duplicate click. | ||||||||||||
| identifiers | An array of all device identifiers received. | ||||||||||||
| idfa | Raw IDFA received (iOS specific). | ||||||||||||
| idfv | Raw IDFV received (iOS specific). | ||||||||||||
| ip_address | The IP address from which the click originated. | ||||||||||||
| kochava_click_id | Click ID assigned by Kochava. | ||||||||||||
| latitude | The latitude where the click originated based on IP lookup.
Latitude values are populated based on a 3rd party IP lookup service and are associated with a general city center location. |
||||||||||||
| longitude | The longitude where the click originated based on IP lookup.
Longitude values are populated based on a 3rd party IP lookup service and are associated with a general city center location. |
||||||||||||
| matched_to_id | The click_id or impression_id of the click/impression which won attribution. | ||||||||||||
| matched_to_type | The type of event which triggered the attribution (e.g. click, impression). | ||||||||||||
| network_id | The Kochava-specific ID of the network to which the install was attributed. | ||||||||||||
| network_name | The name of the network to which the install was attributed. | ||||||||||||
| original_request | The query parameters and values received. | ||||||||||||
| partner_click_id | The click ID as supplied by the network and received. | ||||||||||||
| postal_code | The postal code of the region where the click originated based on IP lookup. | ||||||||||||
| region | An alphanumeric code identifying the geographic region where the click originated based on IP lookup. | ||||||||||||
| segment_id | The segment ID assigned by Kochava. | ||||||||||||
| site_id | Identifies the site to which the click was served within the publisher partner. | ||||||||||||
| tracker_guid | The Kochava-specific ID for the tracker from which the click originated. This field is also referred to as the campaign ID within other parts of analytics and reporting. | ||||||||||||
| tracker_name | The user defined name for the tracker to which the click originated. | ||||||||||||
| traffic_verification_fail_reason | The reason traffic has been flagged as invalid based on Traffic Verification rules. | ||||||||||||
| traffic_verified | Flag for whether the click passes the user defined Traffic Verification rules. | ||||||||||||
| qr_code | Boolean value indicating if the click was from a QR code or not. | ||||||||||||
| waterfall_level | The location within the attribution waterfall where the winning click/impression occurred. | ||||||||||||
| click_att | The device ATT status (0 or 1). | ||||||||||||
| click_att_detail | ATT status detail. If ATT Status is 0 – denied user was prompted and answered no notDetermined user was not prompted restriced user cannot be prompted due to global or app level setting blank if manufactured or not prompted via Kochava SDK. If ATT Status is 1 – authorized |
||||||||||||
| click_att_time | Timestamp at time of user response. | ||||||||||||
| click_att_duration_sec | Time between prompting and receiving a response from the user. | ||||||||||||
| type_url_forward | The install campaign tracker type. | ||||||||||||
| type_reengagement | The reengagement campaign tracker type. |
* Indicates the foreign key for Click Tables JOIN.
Click Table Join Example:
/* Click Tables JOIN Example */
SELECT *
FROM clicks_primary c
LEFT OUTER JOIN click_properties cp
ON c.click_id = cp.click_id
LEFT OUTER JOIN (
SELECT click_id,
identifier_value
FROM click_identifiers
WHERE identifier_type = 'idfa') AS ci
ON c.click_id = ci.click_id
WHERE DATE(c.date_received) = '2016-09-20'
LIMIT 10Events
event_attribution:
| Table Column | Description |
|---|---|
| attribution_model | The module (Kochava or SAN) in which attribution was determined. |
| campaign_name | The user-defined name for the campaign to which the event was attributed. |
| click_reconciliation_id | The internal Kochava ID used to identify the attribution associated with a click. |
| date_received | The datetime (10-digit epoch timestamp) the event was received. |
| *event_id | The internally generated unique ID to which the event is assigned. |
| imp_reconciliation_id | An internal Kochava ID used to identify the attribution associated with a impression. |
| lookback_seconds | The lookback window in seconds. |
| lookback_used | A flag to indicate whether a lookback window has been used. |
| **matched_to_id | The click_id or impression_id of the click/impression which won attribution. |
| matched_to_type | The type of event which triggered the attribution (e.g. click, impression). |
| network_id | The Kochava-specific ID of the network from which the event was attributed. |
| network_name | The name of the network from which the event was attributed. |
| tracker_guid | The Kochava-specific ID for the tracker from which the event was attributed. |
| tracker_name | The user-defined name for the tracker to which the event was attributed. |
| waterfall_level | The location within the attribution waterfall where the winning event occurred. |
** Indicates the foreign key for Event Table JOIN.
** Indicates the foreign key for Click or Impression Tables JOIN.
events_primary:
| Table Column | Description |
|---|---|
| adid | Raw ADID received (Android specific). |
| android_id | Raw Android_ID received (Android specific). |
| app_version | The version of the app received on the install. |
| attribution_module | The module (Kochava or SAN) in which attribution was determined. |
| campaign_name | The user-defined name for the campaign to which the install was attributed. |
| city | The name of the city where the click originated based on IP lookup. |
| cost | The user-defined cost per click. |
| cost_type | The Cost per Click, Cost per Install, or Cost per Mille. |
| country_code | 2-digit country designation where the click originated, based on IP lookup. |
| creative_id | The network-supplied creative_id value received. |
| date_occurred | The datetime (10-digit epoch timestamp) the event occurred. |
| date_received | The datetime (10-digit epoch timestamp) the event was received. |
| device_limit_tracking | The customer defined ability to receive ads. Value will be 0 or 1. |
| device_marketing_name | The marketing name used to describe the device by the manufacturer (e.g. Galaxy S7). |
| device_model | The model of the device that installed the app. |
| device_os_name | The name of the operating system installed on the device. |
| device_os_version | The version of the operating system installed on the device. |
| device_ua | User Agent of the device gathered upon click. |
| device_ver | The device type and version of the device received on click. |
| *event_id | The internally generated unique ID to which the event is attributed. |
| event_name | The name of the advertiser defined event. |
| event_value | The value of the post-install event if a revenue value is being passed. |
| identifiers | An array of all device identifiers received. |
| identity_link | A customer defined unique identifier for linking devices. |
| idfa | Raw IDFA received (iOS specific). |
| idfv | Raw IDFV received (iOS specific). |
| **install_id | The internally generated unique ID to which the install is assigned. |
| ip_address | The IP address from which the impression originated. |
| is_reengagement | Indicates whether the event is part of a reengagement campaign. |
| kochava_device_id | The device ID assigned by Kochava. |
| latitude | The latitude where the event originated based on IP lookup.
Latitude values are populated based on a 3rd party IP lookup service and are associated with a general city center location. |
| longitude | The longitude where the event originated based on IP lookup.
Longitude values are populated based on a 3rd party IP lookup service and are associated with a general city center location. |
| lookback_seconds | The lookback window in seconds. |
| lookback_used | An indicator of where the lookback window used was set (e.g. network, default). |
| match_object | A JSON object containing a variety of details about the attribution of the event. |
| matched_to_id | The click_id or impression_id of the click/impression which won attribution. |
| matched_to_type | The type of event which triggered the attribution (e.g. click, impression). |
| network_id | The id of the network to which the install was attributed. |
| network_name | The name of the network to which the install was attributed. |
| postal_code | The postal code of the region where the click originated based on IP lookup. |
| properties | A JSON object containing a variety of details about the install that may or may not be included in other columns. |
| region | An alphanumeric code identifying the geographic region where the install originated based on IP lookup. |
| sdk_version | The version of the Kochava SDK installed in the app at time of event. |
| segment_id | The segment ID assigned by Kochava. |
| site_id | Identifies the site to which the click was served within the publisher partner. |
| tracker_guid | The Kochava-specific ID for the tracker from which the click originated. This field is also referred to as the campaign ID within other parts of analytics and reporting. |
| tracker_name | The user defined name for the tracker to which the click originated. |
| traffic_verification_fail_reason | The reason traffic has been flagged as invalid based on Traffic Verification rules. |
| traffic_verified | Flag for whether the click passes the user defined traffic verification rules. |
| valid_receipt | Flag indicating where a valid receipt was received from the app store for the install. |
| waterfall_level | The location within the attribution waterfall where the winning event occurred. |
| event_att | The device ATT status (0 or 1). |
| event_att_detail | ATT status detail. If ATT Status is 0 – denied user was prompted and answered no notDetermined user was not prompted restriced user cannot be prompted due to global or app level setting blank if manufactured or not prompted via Kochava SDK. If ATT Status is 1 – authorized |
| event_att_time | Timestamp at time of user response. |
| event_att_duration_sec | Time between prompting and receiving a response from the user. |
| data_controlled | Control the transmission of first party client data to 3rd party networks |
* Indicates the foreign key for Event Tables JOIN.
** Indicates the foreign key for Install Table JOIN.
Event Table Join Example:
/* Event Tables JOIN Example */
SELECT *
FROM events_primary e
LEFT OUTER JOIN event_attribution ea
ON e.event_id = ea.event_id
WHERE DATE(e.date_occured) = '2016-09-20'
LIMIT 10Identity Link
identity_link:
| Table Column | Description |
|---|---|
| date_received | The datetime (10-digit epoch timestamp) the click was received. |
| *event_id | The internally generated unique ID to which the event is attributed. |
| identity_name | The custom-defined parameter name of the identity link. |
| identity_value | The custom parameter value associated with the identity_name. |
| **install_id | The internally generated unique ID to which the install is attributed. |
| kochava_device_id | The device ID assigned by Kochava. |
* Indicates the foreign key for Event Tables JOIN.
** Indicates the foreign key for Install Tables JOIN.
Impressions Tables
impression_identifiers:
| Table Column | Description |
|---|---|
| date_received | The datetime (10-digit epoch timestamp) the impression was received. |
| identifier_value | The value of the device identifier. |
| *impression_id | The internally generated unique ID to which the impression is attributed. |
* Indicates the foreign key for Impression Tables JOIN.
impression_properties:
| Table Column | Description | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| app_name | The user-defined name for the app from which the click originated. | ||||||||||||
| appstore_id | The app-specific ID on the app store sites. | ||||||||||||
| carrier | The wireless carrier used on the device where the click originated. | ||||||||||||
| cost_type | The Cost per Click, Cost per Install, or Cost per Mille. | ||||||||||||
| country_code | 2-digit country designation where the click originated, based on IP lookup. | ||||||||||||
| cp1 thru cp15 | User-defined custom parameters.
NOTE: Custom parameters available in the UI do not match the customer parameters available in Query. However, the click/UI parameters will map to the Query parameters with an offset of 1.
|
||||||||||||
| creative_id | The network-supplied network_id value received. | ||||||||||||
| creative_size | The dimensions of the served creative. | ||||||||||||
| date_received | The datetime (10-digit epoch timestamp) the impression was received. | ||||||||||||
| dma | The code for the designated marketing area where the click originated, based on IP lookup. | ||||||||||||
| exchange | If an ad exchange is utilized, the name of the exchange will be displayed. | ||||||||||||
| imp_cost | The Cost per Impression. | ||||||||||||
| imp_type | The type of impression that was servered. | ||||||||||||
| *impression_id | The internally generated unique ID to which the impression is attributed. | ||||||||||||
| original_request | The query parameters and values received. | ||||||||||||
| partner_impression_id | The unique ID generated by the partner for the impression. | ||||||||||||
| referrer | The Google Referrer parameters passed. | ||||||||||||
| site_id | Identifies the site to which the click was served within the publisher partner. |
* Indicates the foreign key for Impression Tables JOIN.
impressions_primary:
| Table Column | Description |
|---|---|
| campaign_name | The user-defined name for the campaign to which the click was attributed. |
| city | The name of the city where the impression originated based on IP lookup. |
| cost | The user-defined cost per click. |
| cost_type | The Cost per Click, Cost per Install, or Cost per Mille. |
| country_code | 2-digit country designation where the click originated, based on IP lookup. |
| creative_id | The network-supplied network_id value received. |
| date_received | The datetime (10-digit epoch timestamp) the impression was received. |
| device_marketing_name | The marketing name used to describe the device by the manufacturer (e.g. Galaxy S7). |
| device_model | The model of the device that installed the app. |
| device_os_name | The name of the operating system installed on the device. |
| device_os_version | The version of the operating system installed on the device. |
| device_ua | User Agent of the device gathered upon click. |
| device_ver | The device type and version of the device received on click. |
| identifiers | An array of all device identifiers received. |
| imp_type | The type of impression that was servered. |
| *impression_id | The internally generated unique ID to which the impression is attributed. |
| ip_address | The IP address from which the impression originated. |
| kochava_impression_id | The impression ID assigned by Kochava. |
| latitude | The latitude where the click originated based on IP lookup.
Latitude values are populated based on a 3rd party IP lookup service and are associated with a general city center location. |
| longitude | The longitude where the click originated based on IP lookup.
Longitude values are populated based on a 3rd party IP lookup service and are associated with a general city center location. |
| network_id | The Kochava-specific ID of the network from which the click originated. |
| network_name | The name of the network from which the click originated. |
| original_request | The query parameters and values received. |
| partner_impression_id | The impression ID assigned by the partner. |
| region | An alphanumeric code identifying the geographic region where the impression originated, based on IP lookup. |
| segment_id | The segment ID assigned by Kochava. |
| site_id | Identifies the site to which the click was served within the publisher partner. |
| tracker_guid | The Kochava-specific ID for the tracker from which the click originated. This field is also referred to as the campaign ID within other parts of analytics and reporting. |
| tracker_id | The Kochava-specific ID of the tracker from which the impression originated. |
| tracker_name | The user defined name for the tracker to which the install was attributed. |
| traffic_verification_fail_reason | The reason traffic has been flagged as invalid based on Traffic Verification rules. |
| traffic_verified | Flag for whether the click passes the user defined traffic verification rules. |
| impression_att | The device ATT status (0 or 1). |
| impression_att_detail | ATT status detail. If ATT Status is 0 – denied user was prompted and answered no notDetermined user was not prompted restriced user cannot be prompted due to global or app level setting blank if manufactured or not prompted via Kochava SDK. If ATT Status is 1 – authorized |
| impression_att_time | Timestamp at time of user response. |
| impression_att_duration_sec | Time between prompting and receiving a response from the user. |
* Indicates the foreign key for Impression Tables JOIN.
Impression Table Join Example:
/* Impression Tables JOIN Example */
SELECT *
FROM impressions_primary i
LEFT OUTER JOIN impression_properties ip
ON i.impression_id = ip.impression_id
LEFT OUTER JOIN impression_identifiers ii
ON i.impression_id = ii.impression_id
WHERE DATE(i.date_received) = '2016-09-20'
LIMIT 10Attribution Tables
install_attribution:
| Table Column | Description |
|---|---|
| attribution_module | The module (Kochava or SAN) in which attribution was determined. |
| campaign_name | The user-defined name for the campaign to which the install was attributed. |
| click_reconciliation_id | The internal Kochava ID used to identify the attribution associated with a click. |
| date_received | The datetime (10-digit epoch timestamp) the install was received. |
| imp_reconciliation_id | An internal Kochava ID used to identify the attribution associated with a impression. |
| *install_id | The internally generated unique ID to which the install is assigned. |
| lookback_seconds | The lookback window in seconds. |
| lookback_used | An indicator of where the lookback window used was set (e.g. network, default). |
| **matched_to_id | The click_id or impression_id of the click/impression which won attribution. |
| matched_to_type | The type of event which triggered the attribution (e.g. click, impression). |
| network_id | The Kochava-specific ID of the network to which the install was attributed. |
| network_name | The name of the network to which the install was attributed. |
| tracker_guid | The Kochava-specific ID for the tracker to which the install was attributed. |
| tracker_name | The user defined name for the tracker to which the install was attributed. |
| waterfall_level | The location within the attribution waterfall where the winning click/impression occurred. |
* Indicates the foreign key for Install Table JOIN.
** Indicates the foreign key for Click or Impression Tables JOIN.
Installs Tables
install_identifiers:
| Table Column | Description |
|---|---|
| date_received | The datetime (10-digit epoch timestamp) the install was received |
| identifier_type | The type of identifier received on the install. |
| identifier_value | A device identifier corresponding to the identifier_type of the row. |
| *install_id | The internally generated unique ID to which the install is attributed. |
* Indicates the foreign key for Install Tables JOIN.
install_influencers:
| Table Column | Description |
|---|---|
| *click_id | Identifier associated with the influencing click associated to the influenced install. A foreign key which can be used to join out to other tables within Kochava Query. |
| click_reconciliation_id | The internal Kochava ID used to identify the attribution associated with a click. |
| date_received | The datetime (10-digit epoch timestamp) the install was received. |
| **imp_id | Identifier associated with the influencing impression associated to the influenced install. A foreign key which can be used to join out to other tables within Kochava Query. |
| imp_reconciliation_id | The internal Kochava ID used to identify the attribution associated with an impession. |
| ***install_id | This is a foreign key of this table and is used to join out to other tables within Kochava Query. |
| rejected | The reason the install was attributed to another Network (for example, another network was credited with last-click). |
| waterfall_level | The location within the attribution waterfall where the winning click/impression occurred. |
* Indicates the foreign key for the Click Tables JOIN.
** Indicates the foreign key for Impression Tables JOIN.
*** Indicates the foreign key for Install Tables JOIN.
installs_primary:
| Table Column | Description |
|---|---|
| adid | Raw ADID received (Android specific). |
| android_id | Raw Android_ID received (Android specific). |
| app_version | The version of the app received on the install. |
| attribution_module | The module (Kochava or SAN) in which attribution was determined. |
| campaign_name | The user-defined name for the campaign to which the install was attributed. |
| city | The name of the city where the click originated based on IP lookup. |
| cost | The user-defined cost per click. |
| cost_type | The Cost per Click, Cost per Install, or Cost per Mille. |
| country_code | 2-digit country designation where the click originated, based on IP lookup. |
| creative_id | The network-supplied network_id value received. |
| date_occurred | The datetime (10-digit epoch timestamp) the install occurred. |
| date_received | The datetime (10-digit epoch timestamp) the install was received. |
| device_limit_tracking | The customer defined ability to receive ads. Value will be 0 or 1. |
| device_marketing_name | The marketing name used to describe the device by the manufacturer (e.g. Galaxy S7). |
| device_model | The model of the device that installed the app. |
| device_os_name | The name of the operating system installed on the device. |
| device_os_version | The version of the operating system installed on the device. |
| device_ua | User Agent of the device gathered upon click. |
| device_ver | The device type and version of the device received on click. |
| duplicate | The click_id of the duplicate click. If the value is 0, there is no duplicate click. |
| identifiers | An array of all device identifiers received. |
| identity_link | A JSON object containing all custom-defined unique identifiers associated with the device that drove the install. |
| idfa | Raw IDFA received (iOS specific). |
| idfv | Raw IDFV received (iOS specific). |
| *install_id | The internally generated unique ID to which the install is assigned. |
| ip_address | The IP address from which the impression originated. |
| kochava_device_id | The device ID assigned by Kochava. |
| latitude | The latitude where the event originated based on IP lookup.
Latitude values are populated based on a 3rd party IP lookup service and are associated with a general city center location. |
| longitude | The longitude where the event originated based on IP lookup.
Longitude values are populated based on a 3rd party IP lookup service and are associated with a general city center location. |
| match_object | A JSON object containing a variety of details about the attribution of the install. |
| matched_to_id | The click_id or impression_id of the click/impression which won attribution. |
| matched_to_type | The type of event which triggered the attribution (e.g. click, impression). |
| network_id | The Kochava-specific ID of the network to which the install was attributed. |
| network_name | The name of the network to which the install was attributed. |
| postal_code | The postal code of the region where the click originated based on IP lookup. |
| properties | A JSON object containing a variety of details about the install that may or may not be included in other columns. |
| region | An alphanumeric code identifying the geographic region where the install originated based on IP lookup. |
| sdk_version | The version of the Kochava SDK installed in the app at time of event. |
| segment_id | The segment ID assigned by Kochava. |
| site_id | Identifies the site to which the click was served within the publisher partner. |
| tracker_guid | The Kochava-specific ID for the tracker from which the click originated. This field is also referred to as the campaign ID within other parts of analytics and reporting. |
| tracker_name | The user defined name for the tracker to which the click originated. |
| traffic_verification_fail_reason | The reason traffic has been flagged as invalid based on Traffic Verification rules. |
| traffic_verified | Flag for whether the click passes the user defined traffic verification rules. |
| valid_receipt | Flag indicating where a valid receipt was received from the app store for the install. |
| waterfall_level | The location within the attribution waterfall where the winning click/impression occurred. |
| install_att | The device ATT status (0 or 1). |
| install_att_detail | ATT status detail. If ATT Status is 0 – denied user was prompted and answered no notDetermined user was not prompted restriced user cannot be prompted due to global or app level setting blank if manufactured or not prompted via Kochava SDK. If ATT Status is 1 – authorized |
| install_att_time | Timestamp at time of user response. |
| install_att_duration_sec | Time between prompting and receiving a response from the user. |
| data_controlled | Control the transmission of first party client data to 3rd party networks |
* Indicates the foreign key for Install Tables JOIN.
Install Table Join Example:
/* Install Tables JOIN Example */
SELECT *
FROM installs_primary i
LEFT OUTER JOIN install_attribution ia
ON i.install_id = ia.install_id
LEFT OUTER JOIN (
SELECT install_id,
identifier_value
FROM install_identifiers
WHERE identifier_type = 'idfa') AS ii
ON i.install_id = ii.install_id
WHERE DATE(i.date_occurred) = '2016-09-20'
LIMIT 10Ad Revenue Tables
user_level_ad_revenue:
| Table Column | Source | Data Type | Description | Value |
|---|---|---|---|---|
| id | Kochava | integer | Kochava System generated unique ID. | 1 |
| job_id | Kochava | string | Kochava System generated Job ID for that daily run. | 03c50e60-8c0d-4ada-99ea-5b4cbc7aaf6c |
| date_dttm | Kochava | timestamp | YYYY-MM-DD (UTC Timezone) The time the job requested the data from the network. | 1584887527 |
| days_after | Kochava | int64 | How many days after the date of the data the request was made. | 2 |
| network_id | Kochava | int64 | Kochava’s ID for the network. | 250 |
| network_campaign_id | Network | string | The campaign ID within the network. | |
| network_campaign_name*** | Network | string | The campaign name within the network. | |
| network_ad_id | Network | int64 | The ID of the Ad within the network | 0 |
| group_name** | Network | string | The group name within the network | |
| group_id | Network | string | The group ID within the network. | |
| placement | Network | string | The placement of the Ad. | Default interstitial |
| ad_size | Network | string | The size of the Ad | 250×300 |
| name | Network | string | The name of the Ad within the network. | |
| type* | Network | string | The ad unit from which the revenue was generate from. | interstitial |
| start_dttm | Kochava | timestamp | YYYY-MM-DD (UTC Timezone) The start date of the date range used the data request to the network. |
1584662400 |
| end_dttm | Kochava | timestamp | YYYY-MM-DD (UTC Timezone) The end date of the date range used the data request to the network. |
1584748800 |
| url | Network | string | The URL of the Ad. | |
| image | Network | string | The URL of the image used in the Ad. | |
| target | Network | string | The target data for the Ad | |
| keywords | Network | string | The keyword data for the Ad. | |
| gender | Network | string | The gender of the users being targeted by the Ad. | |
| device_type | Network | string | Typically the platform, model, or device_id_type of the device | |
| country | Network | string | The country code. | |
| revenue_ecpm | calculated field | float34 | Revenue Estimate Cost Per Mille (revenue * 1000)/impressions. | 21.08 |
| timezone | Kocahva or Network |
string | The UTC hour offset set on the saved credentials used to determine the date range for the data request to the network or the timezone associated with the Ad in the network. | |
| device_id | Network | string | The unique ID of the user’s device. | 4ecf488e-32c7-424d-8071-3597d85b2de7 |
| device_id_type | Network | string | The unique ID type of the user’s device. | idfa |
| user_id | Network | string | The user ID being set by the developer. | eef36aa6-2a86-4fca-8634-2c207d5 |
| revenue | Network | float64 | The Revenue generated by the user (USD). | |
| impressions | Network | int64 | The number of ads displayed to the user | |
| idfa | Network | string | Raw IDFA served an impression (iOS specific). | |
| idfv | Network | string | Raw IDFV served an impression(iOS specific). | |
| adid | Network | string | Raw ADID served an impression (Android specific). | For ironsource GAID will be mapped to ADID |
| android_id | Network | string | Raw Android_ID served an impression (Android specific). |
*Maps to ironSource (Ad Unit)
**Maps to ironSource (Segment)
***Maps to ironSource (Ad Network)
SKAdNetwork Conversion Table
skad_conversions:
| Table Column | Description | Data Type |
|---|---|---|
| count | Number of SKAdNetwork postbacks. If did-win=true, then number of SKAdNetwork attributed installs (winning partner ad signal only), if did-win=false, then number of SKAdNetwork influencer ad signals. | integer |
| k_conversion_model_name | The name of the conversion model chosen on the SKAdNetwork Settings page in Kochava. | string |
| k_conversion_model_triggered_by_event | True if the user triggered a model-detectable action, otherwise false. Examples of false are: $0 revenue generated in revenue model, none of the selected target events triggered for highest value event, user journey, or engagement models. | boolean |
| k_conversion_value_days_since_install | The number of days between the day the install occurred and when the first SKAdNetwork timer expired. | integer |
| k_conversion_value_description | The meaning of the conversion value based on the conversion model used; reflects the user’s activity in the app after install. — Highest Value Event: single event name — User Journey: list of comma separated event names — Revenue: range of revenue in USD — Engagement: number of times the target event occurred and the target event name. | string |
| k_conversion_value_measurement_window | The length of time the user’s activity was observed (in days). Values can be: Undefined, Install Only, D0, D0-1, D0-2, D0-3, D0-4, D0-5, D0-6, D0-7 D0 = within 24 hrs of install. | string |
| k_in_timestamp | The time at which Kochava ingests the row (not the SKAd timestamp). | timestamp |
| k_network_id | The numeric ID of the integrated media partner in Kochava. | integer |
| k_network_name | The name of the integrated media partner in Kochava. | string |
| k_skad_campaign_guid | The Kochava-generated guid representing our determined separation of SKAdNetwork Campaigns. | string |
| partner_campaign_start_date | The date Kochava received the first click from the SKAdNetwork Campaign. | timestamp |
| partner_slots | Populated from campaign-related parameters that the media partner enriches the SKAdNetwork data with before providing Kochava. See Kochava SKAdNetwork Partner Certification support documentation for list of supported campaign enrichment field. | string |
| skad_ad_network_id | The ID assigned to the media partner by Apple when integrating with SKAdNetwork. | string |
| skad_app_id | The App Store App ID of the target app. | integer |
| skad_attribution_signature | This is used to validate the payload. | bytes |
| skad_campaign_id | The numeric ID of the campaign reported to SKAdNetwork on the loadproduct call. Integer between 1 and 100. | integer |
| skad_conversion_value** | The value will represent the highest conversion value triggered by the user at the time of the first SKAdNetwork timer’s expiration. | integer |
| skad_date_received_utc | Date the network partner received the conversion from SKAdNetwork. | timestamp |
| skad_host | postback_forward when the media partner forwards each individual SKAdNetwork postback to Kochava. [media_partner_name]_api when the SKAdNetwork data is retrieved from the media partner’s API by Kochava. | string |
| skad_ip | The skad_ip value provided to Kochava by the media partner when forwarding SKAdNetwork data. Represents the IP of the original postback received from SKAdNetwork. If skad_ip not provided by the media partner, then the IP of the request sent to Kochava is used to populate this field. | string |
| skad_redownload | SKAdNetwork-reported installs that were flagged as redownload by SKAdNetwork. | boolean |
| skad_source_app_id** | The App Store App ID of the app where the ad was served. | integer |
| skad_transaction_id | Unique transaction ID for deduplication. | string |
| skad_useragent | Useragent Kochava received from the request. | string |
| skad_version | The SKAdNetwork version. | string |
| k_conversion_value_revenue_min_bound | Upper limit of revenue range generated by the user (bound). Only applicable to revenue models. | integer |
| k_conversion_value_revenue_max_bound | Upper limit of revenue range generated by the user (bound). Only applicable to revenue models. | integer |
| k_conversion_value_revenue_min_inclusive | Lower limit of revenue range generated by the user (inclusive). Only applicable to revenue models. | integer |
| k_conversion_value_revenue_max_inclusive | Upper limit of revenue range generated by the user (inclusive). Only applicable to revenue models. | integer |
| k_conversion_value_engagement_count | Number of times the user triggered the selected engagement event. Only applicable to engagement models. | integer |
| k_conversion_value_events | List of model-detectable events triggered by the user. Event name is the name of the event as labeled in Kochava. | string |
| k_conversion_value_partner_events | List of model-detectable events triggered by the user. Event name is the name of the event as labeled in the partner’s sytem, based on mapping defined in partner postback configuration. | string |
| k_source | Indicates the method used to provide SKAdNetwork data to Kochava. postback_json when the SKAdNetwork data is forwarded via individual postbacks for each SKAdNetwork attributed install. [media_partner_name]_api when Kochava retrieves the SKAdNetwork data from the media partner’s API. | string |
| skad_fidelity_type | Differentiates StoreKit-rendered ads from view-through ads. 1 indicates StoreKit-rendered ads and is supported on iOS 11.3 and later, 0 indicates view-through ads and is supported on iOS 14.5 and later. View-through ads are supported with SKAdNetwork version 2.2 and later. | integer |
| skad_country | The country derived from the skad_ip provided on the SKAdNetwork postback provided by the media partner. skad_ip represents to IP address in the header of the original request of the postback sent from SKAdNetwork to the media partner. Apple has communicated that this IP address will no longer represent the device’s IP address. | string |
| skad_did_win | Differentiates SKAdNetwork postbacks for the winning ad (did-win=true), vs ads that qualified, but did not win (did-win=false). Postbacks for did-win=false are supported with SKAdNetwork version 3.0+ on devices running iOS 14.6+. | boolean |
| skad_source_identifier | Known as the “campaign ID” in previous SKAdNetwork versions, this value relates to the placement detail of the ad-network served inventory. | string |
| skad_coarse_conversion_value | A coarse grained value is a type of hierarchical conversion value and can be low, medium, or high. | string |
| skad_postback_sequence_index | In SKAdNetwork v4, a total of (3) user activities can be attributed by SKAdNetwork and this value indicates which of the 3 conversion windows that conversion occurred in. | integer |
| skad_source_domain | For web ads, the postback may include a source-domain instead of a source-app-id, indicating the conversion resulted from a mobile web ad unit. | string |
**Subject to Apple’s “privacy threshold”.
