Menu
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
- wecantrack account (medium affiliate price plan)
- At least one affiliate network or website fully connected
- Gmail account
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.
| Field | Description | Provided by | Data Set Level |
| account_name | The name of the affiliate network account. | WCT | Conversion |
| account_tag_1 until 5 | The first, second, third, forth or fifth tag placed in the affiliate network account. | WCT | Conversion |
| advertiser_id | The ID of the advertiser provided by the affiliate network. | AN | Conversion |
| advertiser_name | The name of the advertiser provided by the affiliate network. | AN | Conversion |
| click_date | The date and time of the click that led to the conversion. Many networks do not report this field. | AN | Conversion |
| click_id | The click ID generated by wecantrack when the click occurred. | WCT | Click |
| click_metadata | A string containing all click metadata sent to wecantrack via the clickout API. Via regular expressions separate fields can be created for the values. | WCT | Click |
| click_reference | The wecantrack (or publisher’s) click ID that was placed in the affiliate URL when the click occurred. | WCT | Click |
| click_timestamp | The date and time of the click, registered by wecantrack. | WCT | Click |
| clickout_url | The URL of the page where the click occurred. | WCT | Click |
| commisison_amount | The commission amount provided by the network, often also referred to as earnings, payout amount or revenue. | AN | Conversion |
| currency_id | The currency of the original conversion value. | AN | Conversion |
| custom_index_1 until 5 | The 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. | WCT | Click |
| exchange_rate_base_euro | The exchange rate used to calculate convert the currency (based on Euro). | WCT | Conversion |
| ingoing_url | The affiliate link coming into the wecantrack system when it was not modified yet. | WCT | Click |
| modified_date | The date and time when the transaction was last modified by the affiliate network, provided by the affiliate network. | AN | Conversion |
| network_id | The affiliate network ID set within the wecantrack system (e.g. awin, commission-factory, tradedoubler, rakuten). | WCT | Click |
| network_name | The affiliate network name set within the wecantrack system (e.g. Awin, Commission Factory, Tradedoubler, Rakuten). | WCT | Click |
| order_date | The date and time of the conversion, provided by the affiliate network. | AN | Conversion |
| order_ref | The order reference ID provided by the network. | AN | Conversion |
| outgoing_url | The affiliate link including the wecantrack click ID. | WCT | Click |
| page_title | The page title of the landing page. | WCT | Session |
| paid_to_publisher | An 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. | AN | Conversion |
| product_title | The 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. | AN | Conversion |
| redirect_url | The redirect URL used to send the user to the affiliate URL. | WCT | Click |
| referrer | The referrer URL from where the website visitor came. | WCT | Session |
| sale_amount | The total amount of paid to the advertiser. | AN | Conversion |
| sale_clickout_url | The URL of the page where the click occurred, provided by the network. | AN | Conversion |
| sale_id | The Sale ID generated by wecantrack. | WCT | Conversion |
| sale_reference | The Reference ID used to attribute the conversion to a click. | WCT | Conversion |
| session_id | The Session ID generated by wecantrack. | WCT | Session |
| session_timestamp | The date and time of the session, registered by wecantrack. | WCT | Session |
| status | The conversion’s status (approved, declined, pending), provided by the network. | AN | Conversion |
| status_change_date | The date the status was changed. | AN | Conversion |
| sub_id_1 until 6 | SubID1 to SubID6 values. | AN | Conversion |
| timestamp_ocs | The date and time of the row, based on last event (order date, click date, session date). | WCT | Session |
| timestamp_sco | The date and time of the row, based on first event (session date, click date, order date). | WCT | Session |
| transaction_id | The transaction ID of the conversion, provided by the network. For some networks this ID gets altered by wecantrack to ensure uniqueness. | AN | Conversion |
| url | The landing page URL of the session. | WCT | Session |
| user_agent | The user agent of the visitor. | WCT | Session, Click |
| user_network_account_id | The ID generated by wecantrack for the affiliate network account. | WCT | Conversion |
| user_website_id | The ID generated by wecantrack for the connected website. | WCT | Session, Click |
| user_website_url | The domain of the connected website. | WCT | Session, Click |
| validation_date | The date when the conversion was validated (not always provided). | AN | Conversion |
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 |
