Report example: Revenue by device category attributed by Last Click and Funnel Based models

With this report you can get number of sessions and transactions, costs and revenue grouped by device category, and ROI compared by two attribution models: Last Click and Funnel Based.

Dynamic parameters

  • startDate — start date of a time frame you want to analyze
  • endDate — end date of a time frame you want to analyze
  • dataSetName — name of a dataset with Session Streaming data
  • tableName — name of table with Session Streaming data
  • dataSetAttributionName — name of a dataset with Funnel Based attribution data
  • tableAttributionName — name of a table with Funnel Based attribution data

Example of results visualisation

SQL query

	
		SELECT
    -- select traffic sources, number of sessions and transactions, costs and revenue by two attribution models grouped by device category
    d.deviceCategory as deviceCategory, count(unique(d.sessionId)) as Sessions, count (unique(d.Transactions)) as Transactions, SUM(d.Cost) as Cost, SUM(d.Revenue_GA) as Revenue_GA,
    SUM(v.Value) as Revenue_Funnel_Based FROM
    -- select device category, costs and revenue for each session
    (
    SELECT 
        device.deviceCategory as deviceCategory, sessionId, hits.transaction.transactionId as Transactions, SUM(trafficSource.adCost) as Cost, SUM(hits.transaction.transactionRevenue) as Revenue_GA from 
      (TABLE_DATE_RANGE([{dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="session_streaming_" type="input"}],
        TIMESTAMP('{startDate default="2015-12-25" type="datetime"}'),
        TIMESTAMP('{endDate default="2016-01-11" type="datetime"}')))
        where device.deviceCategory is not null
        group by deviceCategory, sessionId, Transactions
        ) as d
left join
  -- select attributed revenue for each session
   (
       SELECT 
        session_id, SUM(value) as Value FROM
        (TABLE_DATE_RANGE([{dataSetAttributionName default="Attribution_results" type="input"}.{tableAttributionName default="values_" type="input"}],
        TIMESTAMP('{startDate default="2015-12-25" type="datetime"}'),
        TIMESTAMP('{endDate default="2016-01-11" type="datetime"}')))
        group by session_id
        order by Value desc) as v
on d.sessionId = v. session_id
group by deviceCategory
order by Sessions desc
	
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.