UTM Clicks and Weighted Revenue Report
- Hunter Schaeffer
- Chris Torigian
- Jonathan Tew
Overview
This data range report provides details of the collected UTM click collected within your account, and the weighted revenue from the resulting sales.
Learn about UTM here: UTM parameters
Report Details
Optional Scheduling Report settings (Click the ‘Schedule Report’ for scheduling options.)
This report has two configurable options:
Date Range
Schedule Report
Report Worksheet and Column Descriptions
The report is composed of 4 worksheets:
source_campaign_and_term
source_and_campaign
source
per order
Source_campaign_and_term Columns
Column | Description |
|---|---|
order_id | Order ID |
total_clicks | Total Clicks related to the Order |
total | Total revenue for the order |
creation_dts | Creation date/time of the order |
utm_source1 | Click Source (most recent) |
utm_campaign1 | Click Campaign |
utm_term1 | Click Term |
ts1 | Click Timestamp |
click_weight1 | Click Weight (percent of the revenue allocated to the click) |
click_revenue1 | Click Revenue (total multiplied by the weight) |
utm_source2 | Same as above, but the next oldest click |
utm_campaign2 | Same as above, but the next oldest click |
utm_term2 | Same as above, but the next oldest click |
ts2 | Same as above, but the next oldest click |
click_weight2 | Same as above, but the next oldest click |
click_revenue2 | Same as above, but the next oldest click |
utm_source3 | Same as above, but the next oldest click |
utm_campaign3 | Same as above, but the next oldest click |
utm_term3 | Same as above, but the next oldest click |
ts3 | Same as above, but the next oldest click |
click_weight3 | Same as above, but the next oldest click |
click_revenue3 | Same as above, but the next oldest click |
utm_source4 | Same as above, but the next oldest click |
utm_campaign4 | Same as above, but the next oldest click |
utm_term4 | Same as above, but the next oldest click |
ts4 | Same as above, but the next oldest click |
click_weight4 | Same as above, but the next oldest click |
click_revenue4 | Same as above, but the next oldest click |
utm_source5 | Same as above, but the next oldest click |
utm_campaign5 | Same as above, but the next oldest click |
utm_term5 | Same as above, but the next oldest click |
ts5 | Same as above, but the next oldest click |
click_weight5 | Same as above, but the next oldest click |
click_revenue5 | Same as above, but the next oldest click |
Source_and_campaign columns
Column | Description |
|---|---|
utm_source | Source |
utm_campaign | Campaign |
weighted_revenue | Amount of Weighted Revenue. This column represents the total revenue allocated to each source or campaign, taking into account the weight or percentage of revenue attributed to each click. This allows for a more accurate measurement of the revenue generated by each source or campaign, rather than just counting the number of clicks or conversions |
total_click_count | Total clicks count for the funnel |
conversion_click_count | Represents the total number of clicks that result in a sale or conversion. This is likely the click that directly leads to the purchase being made. |
middle_of_funnel_click_count | Represents the total number of clicks that occur after the initial click (top of the funnel) but before the sale is made (i.e., the middle of the sales funnel). This could include clicks on product pages, pricing pages, or other pages that occur after the initial click but before the purchase is made. |
top_of_funnel_click_count | Represents the total number of clicks at the top of the sales funnel for each order. This is likely the first interaction with the product or service, and is often the initial click that drives a potential customer to the website. |
Source worksheet columns
Column | Description |
|---|---|
utm_source | Source |
weighted_revenue | Amount of Weighted Revenue |
total_click_count | Total click count |
conversion_click_count | Total conversion count |
middle_of_funnel_click_count | Total middle funnel click |
top_of_funnel_click_count | Total top of funnel click |
Per order worksheet columns
Column | Description |
|---|---|
order_id | Order ID |
total_clicks | Total Clicks related to the Order |
total | Total revenue for the order |
creation_dts | Creation date/time of the order |
utm_source1 | Click Source (most recent) |
utm_campaign1 | Click Campaign |
utm_term1 | Click Term |
ts1 | Click Timestamp |
click_weight1 | Click Weight (percent of the revenue allocated to the click) |
click_revenue1 | Click Revenue (total multiplied by the weight) |
utm_source2 | Same as above, but the next oldest click |
utm_campaign2 | Same as above, but the next oldest click |
utm_term2 | Same as above, but the next oldest click |
ts2 | Same as above, but the next oldest click |
click_weight2 | Same as above, but the next oldest click |
click_revenue2 | Same as above, but the next oldest click |
utm_source3 | Same as above, but the next oldest click |
utm_campaign3 | Same as above, but the next oldest click |
utm_term3 | Same as above, but the next oldest click |
ts3 | Same as above, but the next oldest click |
click_weight3 | Same as above, but the next oldest click |
click_revenue3 | Same as above, but the next oldest click |
utm_source4 | Same as above, but the next oldest click |
utm_campaign4 | Same as above, but the next oldest click |
utm_term4 | Same as above, but the next oldest click |
ts4 | Same as above, but the next oldest click |
click_weight4 | Same as above, but the next oldest click |
click_revenue4 | Same as above, but the next oldest click |
utm_source5 | Same as above, but the next oldest click |
utm_campaign5 | Same as above, but the next oldest click |
utm_term5 | Same as above, but the next oldest click |
ts5 | Same as above, but the next oldest click |
click_weight5 | Same as above, but the next oldest click |
click_revenue5 | Same as above, but the next oldest click |
Customization
This sample report is built upon the BigQuery Data Warehouse. The source to the queries is visible by clicking on the Data Warehouse Developer section of the report screen. Every merchant’s marketing reporting needs are different. For that reason, we drive all of this information into the Data Warehouse. Creating custom reports in SQL off the data within BigQuery using tools like Google Looker (Data Studio) or Microsoft Power BI that are specific to your own business is how you are going to unlock true value in your data.
If you would like to hire someone to build BigQuery reports for you, please contact ScaleLean: