UTM Clicks and Weighted Revenue Report
About
This data range report provides details of the collected UTM click collected within your account, and the weighted revenue from the resulting sales.
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 |
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 clicks |
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:
Related UTM Documentation
https://ultracart.atlassian.net/wiki/spaces/ucdoc/pages/2594865153