Custom Columns and Conversions

[KGuiste1]

Hi,

Is it possible to pull custom columns and conversions like 'Purchases' or 'Phone Calls' using the search function in Google Ads Scripts?

Regards

[Sigurd Fabrin]

Yes, you can find pretty much all data there. Here's a handy tool to help build the queries https://developers.google.com/google-ads/api/fields/v12/overview_query_builder

..Custom columns are probably just a lookup, so I'd be surprised if you can find those in the database, but you can easily re-create them in a script

Sigurd

[KGuiste1]

I've seen the query builder,

What do you mean by 'lookup'?

Why would you be surprised to find them in the database? And which database?

Recreate what exactly?

Can you explain a little bit clearer?

Regards

[Google Ads Scripts Forum Advisor]

Hi,

Thank you for raising your concern to the Google Ads Scripts.

I understand that you'd like to retrieve custom columns. Please be informed that the Google Ads Scripts utilizes the features of Google Ads API. Unfortunately, adding custom columns is not yet supported in the Google Ads API Reporting and is also not available for Google Ads Scripts. However, there's an existing feature request for this and for any updates, kindly keep an eye on our blog: https://ads-developers.googleblog.com/search/label/google_ads_scripts

Regarding conversion, do you mean custom conversion variable? If yes, then since this is supported via Ads API reporting, refer here: https://developers.google.com/google-ads/api/fields/v14/conversion_custom_variable, then you can use Google Ads Scripts to pull this report. However, if this is not what you're referring to, kindly provide the full and uncropped screenshot of the Google Ads UI showing the data you want to retrieve. Send this privately via the reply to author option. If this option is not available on your end, you may send it through our email (googleadsscr...@google.com) instead.

Let me know if you have any questions.
 

This message is in relation to case "ref:_00D1U1174p._5004Q2mWLSz:ref"

Thanks,
 

Google Ads Scripts Team

[KGuiste1]

Hi, the custom columns aren't as important as the conversions.

The member who replied to me previously pointed me to the API reporting resource. I'm looking at it now, but I'm not sure how to pull conversion metrics.

Could you support me in utilising the Query Builder to retrieve conversion metrics for specific conversion types?

[Sigurd Fabrin]

"What do you mean by 'lookup'?

Why would you be surprised to find them in the database? And which database?

Recreate what exactly?"

So, all data is stored in databases. And when you wish to retrieve some data, you query (look something up) in a database.

A custom column in Google Ads is just a calculation of some metrics or a saved specific query of some dimensions and metrics. It is not data that is necessarily stored separately in the database. So when you query the database,I'd be surprised if you find it.

However, that doesn't matter because in a script, you can just grab the raw data and perform (re-create) the custom ratio or whatever, that you'd normally see in your custom column.

Here's how to query the Google Ads database with a script https://developers.google.com/google-ads/scripts/docs/reference/adsapp/adsapp#search_2

Sigurd

[KGuiste1]

Thanks Sigurd,

Everything you said now makes sense. I now realised I referenced custom columns and thought I was referring to custom conversions.

If you have any insight into pulling custom conversions that would be a great help

Regards

[Sigurd Fabrin]

Sure, that was what linked to in the first post

Example: This will log the count and value of the different conversions you had yesterday

let query =
    `SELECT
        conversion_action.category,
        metrics.all_conversions,
        metrics.all_conversions_value
    FROM
        conversion_action
    WHERE
        segments.date DURING YESTERDAY`;
 let response = AdsApp.search(query);
while (response.hasNext()) {
      let row = response.next();
      console.log(row.conversionAction.category+', '+row.metrics.allConversions+', '+row.metrics.allConversionsValue)
}

See https://developers.google.com/google-ads/api/fields/v12/conversion_action_query_builder

Sigurd

[Rich Hogg]

Hi Sigurd, 

Firstly, thank you for your example, i was looking for something similar and this may just do what i need with a little tweak. 

We use a number of custom columns that look at conversions and break them out into different actions, but it seems you cant use custom columns in scripts, but you maybe able to replicate the formula. 

Using the pre-built Google script "Account summery report" - id like to add 1 particular conversion action to the metrics / daily output.

looking at your code above, id need to run a separate query then merge them together, so i assume the code to get the conversion action data will look something like this. 

SELECT conversion_action.name FROM conversion_action WHERE segments.date DURING YESTERDAY

Im wanting to count and record the number of daily conversions from a particular action (say "contact form 1")  

What would i need to do to the above to specify that conversion action name? is that possible?  and instead of it outputting the name, how do i count the number of instances? 

Hope that makes sense and you can help. 

Kind Regards,  

Rich.

[Sigurd Fabrin]

Hi Rich,

So, you can just add the name to the WHERE clause. More on syntax here https://developers.google.com/google-ads/api/docs/query/structure

You can use this tool to help you build queries https://developers.google.com/google-ads/api/fields/v12/conversion_action_query_builder

Here's an example that will print yesterday's conversions and value for any conversions named something with 'adwords' regardless of casing etc

    let convNameContains = 'adwords'

    let query =
    `SELECT
      conversion_action.category,
      metrics.all_conversions,

      metrics.all_conversions_value,

      conversion_action.name
    FROM
      conversion_action
    WHERE
      segments.date DURING YESTERDAY

      AND conversion_action.name REGEXP_MATCH "(?i)(.*`+convNameContains+`).*"`;

    let response = AdsApp.search(query);
    while (response.hasNext()) {
      let row = response.next();

      console.log('Name: '+row.conversionAction.name+
                  ', type: '+row.conversionAction.category+
                  ', conv: '+row.metrics.allConversions+
                  ', value: '+row.metrics.allConversionsValue)
    }

Sigurd

[KGuiste1]

Hi,

Thanks for the support so far.

I'd like to take this query further and retrieve segmented conversion action data by entity e.g., campaigns

Is this possible?

I just attempted to retrieve campaign name in the above query but apparently it's incompatible with the conversion_action resource

Please help me determine this

Regards

[Sigurd Fabrin]

"[...] apparently it's incompatible with the conversion_action resource"

Google Ads has a hierarchy: account, campaign, adGroup, ad i.e. you cannot find campaigns when you look on account level. You can only go up the hierarchy.

Use a campaign level resource instead, like this one https://developers.google.com/google-ads/api/fields/v12/campaign_conversion_goal_query_builder

Sigurd