Skip to main content

​
When to Use This Analysis

  • Evaluate how marketing activity (promotions, campaigns, spend changes) impacts site traffic.
    • Example: What is the impact of my email blast in driving traffic to the site?
  • Compare channels based on traffic efficiency metrics such as click-through rate, cost per click, or click-based conversion rate.
    • Example: Between channels with similar CPA, which drives traffic more efficiently?
  • Get an early signal on the performance of new channels before conversions occur.
    • Example: A newly launched channel has not yet driven conversions — is it generating comparable traffic?
  • Understand how user engagement varies by marketing channel.
    • Example: Which channels drive more engaged sessions or product interactions?
  • Compare behavior between converters and non-converters to identify funnel drop-off patterns.
    • Example: Do users arriving from channel X abandon carts more frequently than those from channel Y?

​
Source Data

This analysis uses the following fields from the Clickstream schema:
FieldDescription
dateEvent date used to aggregate traffic metrics and filter the analysis window
uidRockerbox user ID cookie used to count unique users
session_startBinary flag indicating the first event of a session. Used to count unique sessions
tier_1, tier_2, tier_3, tier_4, tier_5Marketing channel hierarchy used to segment traffic attribution

​
Key Metrics

MetricDescription
usersCount of unique visitors (COUNT(DISTINCT uid))
sessionsTotal number of sessions (SUM(session_start))
event_countTotal number of on-site events (COUNT(*))
session_countNumber of sessions attributed to marketing channels

​
Example Queries

​
Attributed Sessions by Marketing Channel

The count of unique sessions attributed to click-based marketing touchpoints. 
SELECT 
    SUM(session_start) AS session_count,
    date,
    tier_1, 
    tier_2,
    tier_3,
    tier_4,
    tier_5
FROM `database.schema.table`
WHERE 
     date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
     AND session_start = 1
GROUP BY date, tier_1, tier_2, tier_3, tier_4, tier_5;

​
Site Traffic and Events Over Time

Tracks overall site traffic trends, including users, sessions, and event activity. 
SELECT 
    date,
    COUNT(DISTINCT uid) AS users,
    SUM(CASE WHEN session_start = '1' THEN 1 ELSE 0 END) AS sessions,
    COUNT(*) AS event_count
FROM `database.schema.table`
WHERE 
    AND date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
GROUP BY 
    date
ORDER BY 
    date;