This 3rd party integration allows you to host spreadsheet content in Google Sheets, and use it for your front-end experiences as part of Webtrends Optimize.
Marketing teams may use this, for example, to manage the content of a homepage through a series of campaigns. They can manage assets and text in the Google Sheet, while you build and manage consuming the sheet and applying those changes to the front-end of the website. The goal is then that if the Sheets data changes, your front-end is flexible enough that no additional work is required and the website updates itself in line with the new content.
Overall Process outline
How does this work?
As a central hub, we have our Sheets processor service hosted by Webtrends Optimize.
This pulls in a "config" file, which is your Controller Google sheet. This hosts all of the data about your various sheets, APIs and what you want us to do with your data.
Based on the content in this file, our service pulls in your listed Data sheets, processes them, and outputs them as APIs. For example, "turn the data in data-sheet-1 to a lookup based on ID, and output as api-1".
Details for making this happen are as follows:
Step 1: Build your controller sheet
We recommend making a copy of this sheet:
https://docs.google.com/spreadsheets/d/12ImJmDpyQRwe7ZDkJ2rsgwTO09NJsJFuco6jBIC7drs/edit?usp=sharing
In it, you'll find content similar to:
Field description:
Unique name: self-explanatory.
Sheet URL: The full URL for the data spreadsheet associated with this entry.
API name: Any name. Keep it lowercase, with no spaces or special characters.
WTO Object ID: You can find this in the Optimize UI, check the last panel in the carousel when you click into any test.
WTO Object type: We currently support
"project"
"test"
Processing type: We currently support:
"lookup by id": This creates an API where you can tag on ?id=something as a query string value, and we will return matching row/s.
"full sheet": Returns the full sheet as a nested array (rows>columns)
"sheet search": Allows you to filter down the returned rows, e.g. ?field1=foo&field2=bar
Update basis: We currently support
"Button in UI": Your Advanced Editor test will be given an "update content" button when in the Edit screen, that you can click on to action your change.
"Scheduled - every 4 hours": As described
"Scheduled - daily at 8am": As described. UTC time.
Step 2: Build your data sheet/s
These can be in any relevant shape/format, but note that:
If you're using the "lookup by id" option, we will treat the first column as your unique ID.
If you're using the "sheet search" option, any column headings you supply will be made available for your lookup.
Please keep column headings in the first row.
Please keep column headings lowercase, and without spaces.
We can support up to 1000 rows. If you need bigger data space, please reach out to us.
Example data sheet: https://docs.google.com/spreadsheets/d/1esLFK6UFjKd1TAGP8DBjZW9jq6DjLRevFf43MhxJxTA/edit?usp=sharing
Step 3: Configuration from the WTO end
Please ensure that you have invited emails as described in our placeholder Controller sheet.
Once done, let us know - we have 1-time setup that we will need to complete. This will take us no longer than 1 hour to complete.
Step 4: Use your APIs
These will typically be made available on a URL like wto-CLIENTNAME-APINAME.webtrends-optimize.workers.dev/, as a REST API.
You will be able to call these APIs from your experiments, as part of your tests in the UI, and handle the data that comes back to build your visual experiences.
Lookup API
You would call this API as:
<endpoint>/?id=lookup_item_id
You can provide a single id, or comma-separate multiple IDs, i.e.:
<endpoint>/?id=lookup_item_id1,lookup_item_id2
Expected response:
An map of id: map of attribute:value
{
"lookup_item_id1": {
field_1: "value_1a",
field_n: "value_Na"
},
"lookup_item_id2": {
field_1: "value_1b",
field_n: "value_Nb"
}
}
Full sheet API
You would call this API directly, with no arguments provided
<endpoint>/
Expected response:
An array (rows) of arrays (columns):
[
["Cell A1", "Cell B1", "Cell C1"],
["Cell A2", "Cell B2", "Cell C2"],
...
]
Sheet search API
You would call this API with query string parameters for each field you'd like the output filtered by:
<endpoint>?field_1=value1&field2=value2
Expected response
As per Full Sheet API: Jump to this