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.

Integrations list with the Add integration button

Select Optimise from the list of available integrations.

Integration catalog with Optimise selected

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

Collect name field filled in with the example name 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.

  1. In Optimise, open Admin > Service Accounts and click Service Account.
  2. Enter a name for the service account and click Create.
  3. On the service account page click Generate and copy the API key.

Optimise service account form with the name field and the Create button (Optimise interface)

Generate button for the API key on the Optimise service account page (Optimise interface)

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

Authorize button next to the authorization token field

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

Authorize pop-up with the Access token field filled with a placeholder value

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.

Agency drop-down open with the list of Optimise agencies

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).

Contact ID field filled with the example value 100001

Advanced settings

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

Advanced settings open with the Tables to collect field

Click Next.

Historical data and scheduling

In Historical Data enter the number of days to fetch for the first import, up to 365.

Historical Data field filled in with 365 days

A data synchronization schedule will be created automatically. More information on this topic can be found here.

Click Create to complete the configuration.

Data collection settings with the Create button

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 NameModeType
uniqueIdNULLABLESTRING
conversionIdNULLABLESTRING
advertiserRefNULLABLESTRING
agencyIdNULLABLESTRING
legacyIdNULLABLESTRING
publisherIdNULLABLESTRING
campaignIdNULLABLESTRING
publisherCampaignIdNULLABLESTRING
advertiserIdNULLABLESTRING
publisherNameNULLABLESTRING
publisherTypeNULLABLESTRING
campaignNameNULLABLESTRING
campaignTypeIdNULLABLESTRING
campaignTypeNameNULLABLESTRING
clickDateNULLABLETIMESTAMP
conversionDateNULLABLETIMESTAMP
statusNULLABLESTRING
invoiceDateNULLABLETIMESTAMP
rawConversionValueNULLABLERECORD
rawConversionValue.currencyNULLABLESTRING
rawConversionValue.amountNULLABLEFLOAT
conversionValueNULLABLERECORD
conversionValue.currencyNULLABLESTRING
conversionValue.amountNULLABLEFLOAT
origConversionValueNULLABLERECORD
origConversionValue.currencyNULLABLESTRING
origConversionValue.amountNULLABLEFLOAT
ipAddressNULLABLESTRING
itemNULLABLERECORD
item.nameNULLABLESTRING
item.volumeNULLABLESTRING
item.skuNULLABLESTRING
item.valueNULLABLERECORD
item.value.amountNULLABLESTRING
item.value.currencyNULLABLESTRING
voucherNULLABLESTRING
extendedDataNULLABLERECORD
extendedData.ex1NULLABLESTRING
extendedData.ex2NULLABLESTRING
extendedData.ex3NULLABLESTRING
extendedData.ex4NULLABLESTRING
extendedData.ex5NULLABLESTRING
extendedData.ex6NULLABLESTRING
extendedData.ex7NULLABLESTRING
extendedData.ex8NULLABLESTRING
extendedData.ex9NULLABLESTRING
extendedData.ex10NULLABLESTRING
extendedData.ex11NULLABLESTRING
costNULLABLERECORD
cost.currencyNULLABLESTRING
cost.amountNULLABLEFLOAT
cost.bandIdNULLABLESTRING
invoiceIdNULLABLESTRING
placementNULLABLERECORD
placement.idNULLABLESTRING
placement.nameNULLABLESTRING
creativeNULLABLERECORD
creative.idNULLABLEINTEGER
creative.nameNULLABLESTRING
publisherWebsiteUrlNULLABLESTRING
rejectionReasonNULLABLESTRING
rejectionCodeNULLABLESTRING
loyaltyNULLABLEBOOLEAN
countryNULLABLESTRING
custTypeNULLABLESTRING
referrerNULLABLESTRING