Looker Studio Integration Guide

The Looker Studio Integration feature is also available if you have only connected affiliate networks or websites. We will simply create a data set for you that contains all the data of your account and you can use it to set up your own dashboard and reports.

Requirements

Looker Studio Integration activation guide

  • Within wecantrack, go to Features
  • Enable โ€˜Looker Studio Integrationโ€™
  • Send us the email addresses we should provide with access (to [email protected])
  • Within 10 business days we will set up the data set and a Looker Studio dashboard template and grant you access to both.

wecantrack Looker Studio Data Set Overview

We have set up an overview with field descriptions so that you can better understand all the available dimensions and metrics. The data set contains data from three different tables: sessions, clicks and conversions. All of the data is joined and can thus be used to set up insightful reports where you can go as deep as traffic source level.

FieldDescriptionProvided byData Set Level
account_nameThe name of the affiliate network account.WCTConversion
account_tag_1 until 5The first, second, third, forth or fifth tag placed in the affiliate network account.WCTConversion
advertiser_idThe ID of the advertiser provided by the affiliate network.ANConversion
advertiser_nameThe name of the advertiser provided by the affiliate network.ANConversion
click_dateThe date and time of the click that led to the conversion. Many networks do not report this field.ANConversion
click_idThe click ID generated by wecantrack when the click occurred.WCTClick
click_metadataA string containing all click metadata sent to wecantrack via the clickout API. Via regular expressions separate fields can be created for the values.WCTClick
click_referenceThe wecantrack (or publisherโ€™s) click ID that was placed in the affiliate URL when the click occurred.WCTClick
click_timestampThe date and time of the click, registered by wecantrack.WCTClick
clickout_urlThe URL of the page where the click occurred.WCTClick
commisison_amountThe commission amount provided by the network, often also referred to as earnings, payout amount or revenue.ANConversion
currency_idThe currency of the original conversion value.ANConversion
custom_index_1 until 5The custom_index_1 (2, 3, 4, or 5) sent to wecantrack with clicks via the Clickout API. Can also be used with wecantrackโ€™s Click Aggregation API reporting endpoint to automate content sorting.WCTClick
exchange_rate_base_euroThe exchange rate used to calculate convert the currency (based on Euro).WCTConversion
ingoing_urlThe affiliate link coming into the wecantrack system when it was not modified yet.WCTClick
modified_dateThe date and time when the transaction was last modified by the affiliate network, provided by the affiliate network.ANConversion
network_idThe affiliate network ID set within the wecantrack system (e.g. awin, commission-factory, tradedoubler, rakuten).WCTClick
network_nameThe affiliate network name set within the wecantrack system (e.g. Awin, Commission Factory, Tradedoubler, Rakuten).WCTClick
order_dateThe date and time of the conversion, provided by the affiliate network.ANConversion
order_refThe order reference ID provided by the network.ANConversion
outgoing_urlThe affiliate link including the wecantrack click ID.WCTClick
page_titleThe page title of the landing page.WCTSession
paid_to_publisherAn indicator whether the transaction was paid out to the publisher or not. If paid out it will state 1, if not either 0 or null. Unfortunately, most networks do not report this back to us.ANConversion
product_titleThe title of the products that were purchased. Most networks or advertisers do not provide this information. We recommend to send us product data through the clickout API in order to deliver complete reports.ANConversion
redirect_urlThe redirect URL used to send the user to the affiliate URL.WCTClick
referrerThe referrer URL from where the website visitor came.WCTSession
sale_amountThe total amount of paid to the advertiser.ANConversion
sale_clickout_urlThe URL of the page where the click occurred, provided by the network.ANConversion
sale_idThe Sale ID generated by wecantrack.WCTConversion
sale_referenceThe Reference ID used to attribute the conversion to a click.WCTConversion
session_idThe Session ID generated by wecantrack.WCTSession
session_timestampThe date and time of the session, registered by wecantrack.WCTSession
statusThe conversionโ€™s status (approved, declined, pending), provided by the network.ANConversion
status_change_dateThe date the status was changed.ANConversion
sub_id_1 until 6SubID1 to SubID6 values.ANConversion
timestamp_ocsThe date and time of the row, based on last event (order date, click date, session date).WCTSession
timestamp_scoThe date and time of the row, based on first event (session date, click date, order date).WCTSession
transaction_idThe transaction ID of the conversion, provided by the network. For some networks this ID gets altered by wecantrack to ensure uniqueness.ANConversion
urlThe landing page URL of the session.WCTSession
user_agentThe user agent of the visitor.WCTSession, Click
user_network_account_idThe ID generated by wecantrack for the affiliate network account.WCTConversion
user_website_idThe ID generated by wecantrack for the connected website.WCTSession, Click
user_website_urlThe domain of the connected website.WCTSession, Click
validation_dateThe date when the conversion was validated (not always provided).ANConversion
Custom Field Examples
Here are some commonly used formulas for custom fields that will probably be helpful for you.
Custom Field Formula Description
sessions
count_distinct(session_id)
number of sessions
clicks
count_distinct(click_id)
number of clicks
conversions
count_distinct(sale_id)
number of conversions
utm_source
regexp_replace(regexp_extract(url,'[&|\\?]utm_source=([^&]+)'),'%20',' ')
UTM Source value
utm_medium
regexp_replace(regexp_extract(url,'[&|\\?]utm_medium=([^&]+)'),'%20',' ')
UTM Medium value
utm_campaign regexp_replace(regexp_extract(url,'[&|\\?]utm_campaign=([^&]+)'),'%20',' ') UTM Campaign value
utm_content regexp_replace(regexp_extract(url,'[&|\\?]utm_content=([^&]+)'),'%20',' ') UTM Content value
utm_term regexp_replace(regexp_extract(url,'[&|\\?]utm_term=([^&]+)'),'%20',' ') UTM Term (also referred to as Keyword by GA) value
gclid regexp_extract(url,'[&|\\?]gclid=([^&]+)') GCLID value
msclkid regexp_extract(url,'[&|\\?]msclkid=([^&]+)') MSCLKID value
fbclid regexp_extract(url,'[&|\\?]fbclid=([^&]+)') FBCLID value
device_category CASE WHEN REGEXP_MATCH(user_agent,'.*mobile.*') Then "Mobile" WHEN REGEXP_MATCH(user_agent,'.*Mobile.*') Then "Mobile" WHEN REGEXP_MATCH(user_agent,'.*tablet.*') Then "Tablet" WHEN REGEXP_MATCH(user_agent,'.*Tablet.*') Then "Tablet" Else "Desktop" End Device