Google Ads - Transferring Non-Aggregable Metrics into Explore Element

Search impression rate, search_Lost_IS (rank) and Search_Lost_IS (budget) are selectable in the settings and pullable via the Google Ads API (on campaign and account level only). However, these are non-aggregable metrics, and cannot be displayed in Adverity on another level of granularity  - Very prone to error.

As a result, to be able to visualize these data correctly in the Adverity Platform and on different levels of granularity, these metrics need to be calculated within the platform.

DEFINITION OF KPIs

- Search impression share: Impression share (IS) is the percentage of impressions that your ads receive compared to the total number of impressions that your ads could get.

 =>Impression share = impressions / total eligible impressions

- Search Lost IS (rank): The percentage of time that your ads weren't shown on the Search Network due to poor Ad Rank in the auction. 

- Search Lost IS (budget): The percentage of time that your ads weren't shown on the Search Network due to insufficient budget.

Search Impression Share + Search Lost IS (rank) + Search Lost IS (Budget)
      = Total Eligible Impressions

FIRST STEPS

1.  We need to pull from the API the fields Search_impressions_share, Search_lost_IS (rank) and Search_lost_IS(budget). There is no need to export these values (as they are percentages) but we will use them to calculate later the potential impressions and the absolute values for impressions lost (Budget)  and impressions lost (rank). 
2. As these fields are coming through the API with values such as "<10%" or ">90%", we needed to transform these fields to take away the signs "<" and "%".

"convertx",

 "field": "Search Impr. share",
 "expression": "'10' if {Search Impr. share} == '< 10%' else '90' if {Search Impr. share} == '> 90%' else {Search Impr. share}",

"convertx",

 "field": "Search Lost IS (budget)",
 "expression": "'10' if {Search Lost IS (budget)} == '< 10%' else '90' if {Search Lost IS (budget)} == '> 90%' else {Search Lost IS (budget)}",

"convertx",

 "field": "Search Lost IS (rank)",
 "expression": "'10' if {Search Lost IS (rank)} == '< 10%' else '90' if {Search Lost IS (rank)} == '> 90%' else {Search Lost IS (rank)}",

"convertx",

"field": "Search Impr. share",
"expression": "'0' if {Search Impr. share} in ('--','', None) else float({Search Impr. share}.replace('%',''))/100",

"convertx",

"field": "Search Lost IS (rank)",
 "expression": "'0' if {Search Lost IS (rank)} in ('--','', None) else float({Search Lost IS (rank)}.replace('%',''))/100",

"convertx",

 "field": "Search Lost IS (budget)",
"expression": "'0' if {Search Lost IS (budget)} in ('--','', None) else float({Search Lost IS (budget)}.replace('%',''))/100",

3. We can then use the value of Search_Impression_share to calculate Potential Impressions.

"extendmissing",

 "fieldnames": 
 "Search Lost IS (rank)",
 "Search Lost IS (budget)"
 "missing": "0.0"

"convertnumbers",

 "fields": [
 "Impressions",
 "Search Impr. share",
 "Search Lost IS (rank)",
 "Search Lost IS (budget)"
 "default": "0.0",
 "locale": "en"

"addfieldx",

 "field": "potential_impressions",
 "expression": "round({Impressions} / {Search Impr. share}) if {Search Impr. share} > 0 else '0'",

4. Once potential impressions is calculated, we can get value for lost_impr_budget ​ and lost_impr_rank ​which correspond to the absolute values of impressions lost.
   => In a nutshell, lost_impr_budget ​ +lost_impr_rank ​+impressions ​= Potential impressions.

"addfieldx",

 "field": "lost_impr_budget",
 "expression": "{potential_impressions}*{Search Lost IS (budget)}",

"convertx",

 "field": "lost_impr_budget",
 "expression": "round({lost_impr_budget})",

"addfieldx",

 "field": "lost_impr_rank",
 "expression": "{potential_impressions}*{Search Lost IS (rank)}",

"convertx",

 "field": "lost_impr_rank",
 "expression": "round({lost_impr_rank})",

5.  After we got all these values calculated through transformation, map them in Schema Mapping and Import everything into Insight.
6. Use the newly created and weighted KPI lost_impr_budget ​, lost_impr_rank ​ and Potential impressions ​to create the percentage value in insight:
   • Impression Share (%): (impressions)/(potential_impressions).
   • Search Lost Impression Budget (%): (lost_impr_budget)/(potential_impressions).
   • Search Lost impression Rank (%):(lost_impr_rank)/(potential_impressions).

KEEP IN MIND

• These calculation for lost_impr_budget ​, lost_impr_rank ​ and Potential impressions  will show correct data in Insight only if this is filtered on Datastream (where the transformation was applied). When combining data from different streams (one where we calculated everything mentioned above with one where we didn't), the results won't be correctly displayed. So if you want to have aggregated data for all your campaigns, you should apply the steps above with the other connected Google Adwords stream importing these data.
• As we didn't import exact value but approximate for the rows with value <10%, you might experience slight discrepancies in potential impressions in the results in Insight - I believe we cannot do differently as these values come from the API.

Full transformation here: Ask for the Source code to your Support Team