June 03, 2019
As you can see on this screenshot, the “full referrer” dimension is not available in the Assisted Conversions report. Hence, if you have more than one article posted on one particular resource, you can’t allocate the revenue between the pieces of content, and therefore you can’t determine whether some particular content works or not. Fortunately, BigQuery comes to the rescue. Please take a look at the query below.
SELECT revenue, hits.transaction.transactionId, trafficSource.referralPath, MIN(lookback_window) as lookback_window FROM (SELECT visitor_ID_pageviews, visitor_ID_purchases, revenue, trafficSource.referralPath, hits.transaction.transactionId, hit.timestamp_transaction, DATEDIFF(hit.timestamp_transaction,hit.timestamp) as lookback_window FROM (SELECT fullVisitorId as visitor_ID_pageviews, visitID, visitNumber, trafficSource.referralPath, STRFTIME_UTC_USEC(SEC_TO_TIMESTAMP(visitStartTime),"%Y-%m-%d %H:%M:%S") as hit.visitStartTime, STRFTIME_UTC_USEC(SEC_TO_TIMESTAMP(visitStartTime + hits.time/1000),"%Y-%m-%d %H:%M:%S") as hit.timestamp FROM TABLE_DATE_RANGE([pro-tracker-id.ga_sessions_], TIMESTAMP('2019-01-01'), TIMESTAMP('2019-06-01')) WHERE trafficSource.medium CONTAINS "referral" AND hits.hitNumber = 1 AND trafficSource.source CONTAINS "website_hostname" GROUP BY visitor_ID_pageviews, visitNumber, visitID, trafficSource.referralPath, hit.timestamp, hit.visitStartTime) AS table_with_visits INNER JOIN (SELECT fullVisitorId as visitor_ID_purchases, totals.totalTransactionRevenue/1000000 AS revenue, hits.transaction.transactionId, STRFTIME_UTC_USEC(SEC_TO_TIMESTAMP(visitStartTime),"%Y-%m-%d %H:%M:%S") as hit.visitStartTime, STRFTIME_UTC_USEC(SEC_TO_TIMESTAMP(visitStartTime + hits.time/1000),"%Y-%m-%d %H:%M:%S") as hit.timestamp_transaction FROM TABLE_DATE_RANGE([pro-tracker-id.ga_sessions_], TIMESTAMP('2019-01-01'), TIMESTAMP('2019-06-01')) WHERE totals.transactions > 0 GROUP BY visitor_ID_purchases, revenue, hits.transaction.transactionId, hit.timestamp_transaction, hit.visitStartTime) AS table_with_transactions ON table_with_visits.visitor_ID_pageviews = table_with_transactions.visitor_ID_purchases WHERE hits.transaction.transactionId != 'null' AND hit.timestamp_transaction > hit.timestamp ORDER BY revenue) WHERE lookback_window < 91 GROUP BY visitor_ID_pageviews, visitor_ID_purchases, revenue, hits.transaction.transactionId, trafficSource.referralPath
The query structure is similar to one we have already discussed in this article. However, there are some differences. Let’s take a closer look at them, step by step.
1) First, we are selecting an audience of users who came to our website via a particular referral source. This data forms a table called “table_with_visits” and contains everyone who clicked on the links to our site in the articles. In the very same query, we are selecting the trafficSource.referralPath field, which is equivalent to Google Analytics’ full referrer dimension.
2) Then, we join this data with the second table ("table_with_transactions"), which contains all the transactions during a chosen period. We use fullVisitorId as a key to match the two tables. It is important to mention that we are selecting only those transactions which occurred after the visit via a referral link.
3) In the next step, we calculate a lookback window. To do this we use one more SELECT statement and the DATEDIF SQL function (
DATEDIFF(hit.timestamp_transaction,hit.timestamp) as lookback_window).
4) As the very last step, we select revenue, transaction ID, referral path and minimum lookback window. The result looks approximately like this:
That is all that you need to get the revenue for the full referrer dimension. If required, you can modify the lookback window and obtain the revenue for a particular period of time (including a period of more than 90 days, which is another excellent feature of BigQuery). Thank you for your attention, and I hope this stuff will be useful.