Integration of Optimise with BigQuery
Connect Optimise to Google BigQuery with WitCloud and collect affiliate conversion data (publishers, campaigns, status, value and commission cost) for analysis.
Updated
Introduction
This module connects to the Optimise affiliate platform and copies your conversions into Google Cloud BigQuery. For every conversion you get the publisher, the campaign, the click and conversion dates, the status (for example pending or approved), the conversion value and the commission cost. Use it to compare affiliate performance with your other channels and to follow commissions over time.
Configuration guide
Follow the steps below to connect Optimise with BigQuery. Once the setup is complete, your conversions are transferred to your BigQuery project automatically.
You need a service account API key from Optimise, and you need to know the agency your account belongs to and your Contact ID (the Advertiser ID in Optimise).
Module setup
Go to Integrations in the menu and click Add integration.

Select Optimise from the list of available integrations.

Enter the Collect name, e.g., "Optimise PL".

Authorization
If you have already authorized Optimise in WitCloud, pick that authorization from the Select Optimise authorization drop-down and skip to the agency selection.
To connect to Optimise you need an API key of a service account. The steps below are done in the Optimise interface, which WitCloud does not control, so the wording there may change.
- In Optimise, open Admin > Service Accounts and click Service Account.
- Enter a name for the service account and click Create.
- On the service account page click Generate and copy the API key.


Back in WitCloud, click Authorize next to the Select Optimise authorization field.

Paste the API key into the Access token field and click Create. The new authorization is selected for you.

Select the Agency your Optimise account belongs to: Optimise Australia & New Zealand, Optimise Brazil, Optimise India, Optimise MEA, Optimise Poland, Optimise SE Asia or Optimise UK. The Agency and Contact ID fields stay locked until an authorization is selected.

Enter the Contact ID, digits only. It is the identifier of the advertiser: in Optimise you find it in the Advertiser ID field of the company details (Optimise interface).

Advanced settings
Click Show advanced settings to see Tables to collect. Optimise has one table, conversions, and it is selected by default.

Click Next.
Historical data and scheduling
In Historical Data enter the number of days to fetch for the first import, up to 365.

A data synchronization schedule will be created automatically. More information on this topic can be found here.
Click Create to complete the configuration.

Done! Your Optimise conversions will now be synchronized with BigQuery.
Data schema
The integration writes one table, optimise_conversions, with one row per conversion. Expand the table below to view its full schema.
Data schema for optimise_conversions
| Field Name | Mode | Type |
|---|---|---|
| uniqueId | NULLABLE | STRING |
| conversionId | NULLABLE | STRING |
| advertiserRef | NULLABLE | STRING |
| agencyId | NULLABLE | STRING |
| legacyId | NULLABLE | STRING |
| publisherId | NULLABLE | STRING |
| campaignId | NULLABLE | STRING |
| publisherCampaignId | NULLABLE | STRING |
| advertiserId | NULLABLE | STRING |
| publisherName | NULLABLE | STRING |
| publisherType | NULLABLE | STRING |
| campaignName | NULLABLE | STRING |
| campaignTypeId | NULLABLE | STRING |
| campaignTypeName | NULLABLE | STRING |
| clickDate | NULLABLE | TIMESTAMP |
| conversionDate | NULLABLE | TIMESTAMP |
| status | NULLABLE | STRING |
| invoiceDate | NULLABLE | TIMESTAMP |
| rawConversionValue | NULLABLE | RECORD |
| rawConversionValue.currency | NULLABLE | STRING |
| rawConversionValue.amount | NULLABLE | FLOAT |
| conversionValue | NULLABLE | RECORD |
| conversionValue.currency | NULLABLE | STRING |
| conversionValue.amount | NULLABLE | FLOAT |
| origConversionValue | NULLABLE | RECORD |
| origConversionValue.currency | NULLABLE | STRING |
| origConversionValue.amount | NULLABLE | FLOAT |
| ipAddress | NULLABLE | STRING |
| item | NULLABLE | RECORD |
| item.name | NULLABLE | STRING |
| item.volume | NULLABLE | STRING |
| item.sku | NULLABLE | STRING |
| item.value | NULLABLE | RECORD |
| item.value.amount | NULLABLE | STRING |
| item.value.currency | NULLABLE | STRING |
| voucher | NULLABLE | STRING |
| extendedData | NULLABLE | RECORD |
| extendedData.ex1 | NULLABLE | STRING |
| extendedData.ex2 | NULLABLE | STRING |
| extendedData.ex3 | NULLABLE | STRING |
| extendedData.ex4 | NULLABLE | STRING |
| extendedData.ex5 | NULLABLE | STRING |
| extendedData.ex6 | NULLABLE | STRING |
| extendedData.ex7 | NULLABLE | STRING |
| extendedData.ex8 | NULLABLE | STRING |
| extendedData.ex9 | NULLABLE | STRING |
| extendedData.ex10 | NULLABLE | STRING |
| extendedData.ex11 | NULLABLE | STRING |
| cost | NULLABLE | RECORD |
| cost.currency | NULLABLE | STRING |
| cost.amount | NULLABLE | FLOAT |
| cost.bandId | NULLABLE | STRING |
| invoiceId | NULLABLE | STRING |
| placement | NULLABLE | RECORD |
| placement.id | NULLABLE | STRING |
| placement.name | NULLABLE | STRING |
| creative | NULLABLE | RECORD |
| creative.id | NULLABLE | INTEGER |
| creative.name | NULLABLE | STRING |
| publisherWebsiteUrl | NULLABLE | STRING |
| rejectionReason | NULLABLE | STRING |
| rejectionCode | NULLABLE | STRING |
| loyalty | NULLABLE | BOOLEAN |
| country | NULLABLE | STRING |
| custType | NULLABLE | STRING |
| referrer | NULLABLE | STRING |
