Best Practices for Data Destinations

Learn about specific constraints on working with Singular Data Destinations.

 

Working with Data Connector ID and Name

Background:

  • Data connector ID, data connector name, etc. are new fields added to data destinations schemas in order to support a higher data refresh rate.
  • Data connectors are the integrations with ad networks through which Singular pulls campaign data such as cost.
  • By including the data connector in data destinations schemas, Singular will be able to update data destinations whenever any particular data connector is updated.
  • Instead of network metrics (such as cost) and tracker metrics (such as installs) appearing in the same row, they will be broken into separate rows, because the Data Connector ID is a network dimension only. (Learn more about how Singular works with tracker vs. network data.)

etl_differentrows.png

Impact:

  • You will see changes in your data if your schema includes network data (such as cost) as well as tracker data (such as installs and revenue).
  • Note: Most ETL schemas don't have both types of data, so there should be no or minimal impact.

What do I need to do?

  • We recommend querying the data without the data connector fields and using a group-by to get combined tracker and network data.
  • After grouping the rows, you can add calculated fields such as CPI (Cost per Install) by dividing "adn_cost" by "tracker_installs".

etl_samerows.png

Handling Ratio Metrics

Background:

  • Going forward, you will not be able to pull ratio metrics such as CPI and ROI through Singular ETL.
  • This is because tracker and network data are now split, as explained above.
  • Secondly, in order to be accurate, these values need to be calculated using aggregated metrics. Calculating these metrics on individual rows of data results in an incorrect result.
  • e.g. if your CPI from Facebook is 0.1 (100 installs with a cost of 10), and your CPI from Google Adwords is 0.4 (50 installs with a cost of 20), your actual CPI is 0.2 (150 installs with a cost of 30). But if you calculate CPI before aggregation, your CPI will be shown as 0.5 (0.1 + 0.4).

The table below shows the metrics that need to be aggregated before they're used in a ratio metric.

Category

Ratio metric

Calculation

Reports

cpi

SUM(custom_installs) / SUM(adn_cost)

Reports

ctr

SUM(custom_clicks) / SUM(custom_impressions)

Reports

cvr

SUM(custom_installs) / SUM(custom_clicks)

Reports

ocvr

SUM(custom_installs) / SUM(adn_impressions)

Reports

ecpm

(SUM(adn_cost) / SUM(adn_impressions)) * 1000

Reports

ecpm

SUM(adn_cost) / SUM(custom_clicks)

Reports

ecpi

SUM(adn_cost) / SUM(custom_installs)

Reports

roi_Xd

SUM(revenue_Xd) / SUM(adn_cost)

Reports

arpu_Xd

SUM(revenue_Xd) / SUM(custom_installs)

Reports

retention_rate_Xd

SUM(retained_users_Xd) / SUM(tracker_conversions)

Admon

ad_monetization_roi_Xd

SUM(ad_revenue_Xd) / SUM(adn_cost)

Admon

ad_arpu_Xd

SUM(ad_revenue_Xd) / SUM(custom_installs)

Admon

total_roi_Xd

SUM(total_revenue_Xd) / SUM(adn_cost)

Admon

total_arpu_Xd

SUM(total_revenue_Xd) / SUM(custom_installs)

SKAN

skan_cvr

SUM(skan_installs) / SUM(custom_clicks)

SKAN

skan_ocvr

SUM(skan_installs) / SUM(adn_impressions)

SKAN

skan_ecpi

SUM(adn_cost) / SUM(skan_installs)

SKAN

skan_conversion_values_ratio

SUM(skan_conversion_values_count) / SUM(skan_installs)

SKAN

skan_roi_Xd

SUM(skan_revenue_Xd) / SUM(adn_cost)

SKAN

skan_arpu_Xd

SUM(skan_revenue_Xd) / SUM(custom_installs)

SKAN

skan_admon_roi_Xd

SUM(skan_admon_revenue_Xd) / SUM(adn_cost)

SKAN

skan_combined_roi_Xd

SUM(skan_combined_revenue_Xd) / SUM(custom_installs)

Note:

  • Every metric containing "_Xd" at the end is a cohorted metric. The X should be replaced with the cohort period being used, e.g. 7d.
  • retained_users_Xd and ad_revenue_Xd are custom events in Singular, so their API name will contain an event UUID and the cohort period, e.g. "314305878c6c4ac19eea365cf6b7127a_7d".
  • tracker_conversions is the sum of tracker_installs and tracker_reengagements.

What do I need to do?

  • If you're using Singular's API or ETL, you are probably viewing the data internally by running aggregated queries.
  • In such queries, you should use the formulae above to compute the ratio metric using the aggregated data.
Was this article helpful?