We’ll make the contextual advertising even more relevant and personalized using the ads modifiers in Google Ads and Google Spreadsheets.
Today () returns today’s date to the cell.
This function is useful when you need to use a specific date in your ad text that needs to be updated daily.
For example, we used this function when we indicated to which specific date we can set up the advertising campaign, instead of using the cliche "up to 5 days".
The setup algorithm is pretty simple:
1. Create a table in Google Spreadsheets for 2 attributes:
Target campaign – write the name of the campaign in which we will use the substitution.
5days is a user attribute, in this attribute we will write the function = Today () + 5 to get the date, which will be in 5 days.
Example – https://docs.google.com/spreadsheets/d/1rENN7OjAcEPchuTaQ7ogEUTaHvEZjiKjPDW-SJXTKtc/edit?usp=sharing.
2. Create an empty .xlsx file and copy the data from Google Spreadsheets into it
3. We save it and uploading it to Ads by opening Settings -> Commercial data -> Data feeds -> Download feed "Ad modifier data" -> Name 5days.
4. Open the uploaded feed and go to the "Schedules" tab. Here you need to add a new schedule with the source of Google Sheets, select the table for which we created the Today () function to open access to the table in Google and set the refresh rate "Every 6:00".
5. Save and click the "Update" button. Checking feed updates in the "Download history" tab.
Important!
Automatic feed update is not always set up correctly through the new interface, so if the update didn’t work in the new interface, follow the previous 3-5 steps in the old one.
6. We add ads with the ad modifier {= NameFeed. Attribute: Default text} – in our case it is {= 5days.5days: 5 days};
Profit! 🙂
This function returns to the cell course with Google Finance.
For example, using = GOOGLEFINANCE ("Currency: BTCUSD"), we can pull the bitcoin exchange rate into the table and use it in our ads.
Import data from sources that are available in XML, HTML, CSV, TSV, and RSS and ATOM XML.
The simplest example is importing data from the Privat Bank API to adjust prices in ads based on the exchange rate.
In order to add the card selling rate $ to the cell, use the function in this form
=SUBSTITUTE(IMPORTXML("https://api.privatbank.ua/p24api/pubinfo?exchange&coursid=11";"//exchangerates/row[1]/exchangerate/@sale");".";",")
In this case, the SUBSTITUTE () function replaces the "." to "," so that the course can be used in further formulas.
Example – https://docs.google.com/spreadsheets/d/1c10njKmGAgtsdPH2yZvXPc8Jnqr4B7LYZPPXgZAUgQE/edit?usp=sharing.
A list of all Google table functions can be found here – https://support.google.com/docs/table/25273?.
