Report example: Campaigns ROI by Last Click and Funnel Based attribution models

With this report you can get number of sessions and transactions, costs and revenue grouped by traffic source, medium and campaign, 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
  • min_cost — minimal sum of costs
  • min_revenue — minimal revenue by Last Click model
  • min_sesssions — minimal number of sessions

Example of results visualisation

SQL query


SELECT
    -- select traffic sources, number of sessions and transactions, costs and revenue by two attribution models
     d.Source as Source, d.Medium as Medium, d.Campaign as Campaign, DATE_ADD(TIMESTAMP("2015-12-28 00:00:00"), d.week*7, "day") as Date, Sessions, Transactions, Cost, Revenue_GA, v.Value as Revenue_Funnel_Based
    from
    -- sselect traffic sources, number of sessions and transactions, costs and revenue by Last Click model
    (SELECT 
        trafficSource.source as Source, trafficSource.medium as Medium, trafficSource.campaign as Campaign, count(unique(sessionId)) as Sessions,             count(unique(hits.transaction.transactionId)) as Transactions, SUM(trafficSource.adCost) as Cost, WEEK(DATE) as Week, 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="2016-02-07" type="datetime"}'), 
    TIMESTAMP('{endDate default="2016-02-25" type="datetime"}')))
         group by Source, Medium, Campaign, Week
        HAVING SUM(trafficSource.adCost) > {min_cost default="0.1" type="input"}
        order by Week desc, Sessions desc) as d
left join
    -- select traffic sources, number of sessions and transactions, costs and revenue by Funnel Based model
    (SELECT
     source as Source, medium as Medium, campaign as Campaign, SUM(value) as Value,WEEK(date) as Week
    from 
    (TABLE_DATE_RANGE([{dataSetAttributionName default="Attribution" type="input"}.{tableAttributionName default="values_" type="input"}],
    TIMESTAMP('{startDate default="2016-02-07" type="datetime"}'), 
    TIMESTAMP('{endDate default="2016-02-25" type="datetime"}')))
    group by Source, Medium, Campaign, Week
    order by week desc, Value desc) as v

on d.Source = v.Source and d.Medium = v.Medium and d.week = v.Week and d.Campaign = v.Campaign
where d.week > 1 and d.Revenue_GA > {min_revenue default="0" type="input"}  and v.Value > 0 and Sessions > {min_sessions default="100" type="input"}
order by d.Week desc, d.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.