> ## Documentation Index
> Fetch the complete documentation index at: https://data-foundation.rockerbox.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Session, Visitor, or Traffic Analysis

> Analyze how marketing channels drive site traffic, sessions, and user engagement using the Clickstream dataset.

# 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:

| Field                                            | Description                                                                        |
| ------------------------------------------------ | ---------------------------------------------------------------------------------- |
| `date`                                           | Event date used to aggregate traffic metrics and filter the analysis window        |
| `uid`                                            | Rockerbox user ID cookie used to count unique users                                |
| `session_start`                                  | Binary flag indicating the first event of a session. Used to count unique sessions |
| `tier_1`, `tier_2`, `tier_3`, `tier_4`, `tier_5` | Marketing channel hierarchy used to segment traffic attribution                    |

***

# Key Metrics

| Metric          | Description                                         |
| --------------- | --------------------------------------------------- |
| `users`         | Count of unique visitors (`COUNT(DISTINCT uid)`)    |
| `sessions`      | Total number of sessions (`SUM(session_start)`)     |
| `event_count`   | Total number of on-site events (`COUNT(*)`)         |
| `session_count` | Number of sessions attributed to marketing channels |

***

# Example Queries

## Attributed Sessions by Marketing Channel

The count of unique sessions attributed to click-based marketing touchpoints.

```sql theme={null}
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.

```sql theme={null}
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;
```