Connect Common Queries

This article provides a collection of common queries you can use with Data Connect. You can copy and paste these queries into your BI tool or run them directly on your cluster. Some queries may require you to update property names, event names, or date ranges to fit your specific needs.

Basic Queries

Event Counts

SELECT device_type
       ,plan
       ,COUNT(session_id)
FROM main_production.sessions AS sessions
INNER JOIN main_production.users AS users ON sessions.user_id = users.user_id
GROUP BY 1, 2

Average Property Value

SELECT [Property_to_Group_By], AVG([Numeric_Property])
FROM main_production.[event_name]
GROUP BY 1
ORDER BY 1

Aggregate Metrics

In this section, you'll find various queries to measure aggregate metrics for your site, including average time, top events, and more. The value of these queries increases as you segment them by user personas, and the same logic can also be applied to calculate more specific statistics.

Top 10 Events

The top 10 events shed light on how users are interacting with your app. Looking at this list generates a lot of questions: Why are users selecting this button? Why are users viewing this page? Are these two actions correlated? Does the high event count come from users' success within the product, or is this caused by confusion?

You can modify the WHERE statement to adjust the date range. You can include the date in the SELECT statement and GROUP BY clause to measure how the most frequently performed actions change over time. You can also adjust the number of events you are looking at by modifying the LIMIT.

SELECT all_events.event_table_name AS "Event"
      ,COUNT(*) AS "Event Count"
FROM main_production.all_events AS all_events
WHERE time >= DATEADD(day, -30, getdate())
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

Average Time on Site

This query is primarily beneficial to you if you have a content site, and there aren't a lot of user interactions to capture while reading, and avid readers don't always comment or share. Although this is a proxy for engagement, drastic changes in time on site can cue you in to investigate how users are engaging with your product.

You can tweak this query to analyze average time between events, or break it down into different segments of users to get a better understanding of your site's usage.

SELECT ROUND(AVG(duration)::FLOAT, 4) "Average Session time in Minutes"
FROM (
    SELECT session_id || '-' || user_id AS "unique"
          ,MIN(time) AS "start"
          ,MAX(time) AS "last"
          ,((DATEDIFF('milliseconds', MIN(time), MAX(time))::FLOAT / 1000) / 60) AS "duration"
    FROM main_production.all_events
    -- modify your date range here
    WHERE time >= DATEADD('month', -2, getDate())
    GROUP BY 1
)

Average Time on Page

WITH ordered_pageviews AS (
    SELECT *
          ,row_number() OVER (PARTITION BY user_id, session_id ORDER BY TIME ASC) AS rn
    FROM main_production.pageviews
    WHERE TIME >= DATEADD('month', -1, getDate())
)
SELECT a.path
      ,avg(pageview_time) / 60 as avg
      ,round(percentile_cont(0.25) within group (order by pageview_time) / 60, 2) as p25
      ,round(percentile_cont(0.5) within group (order by pageview_time) / 60, 2) as median
      ,round(percentile_cont(0.75) within group (order by pageview_time) / 60, 2) as p75
      ,round(percentile_cont(0.9) within group (order by pageview_time) / 60, 2) as p90
FROM (
    SELECT p1.session_id
          ,p1.user_id
          ,p1.path
          ,datediff('seconds', p1.time, p2.time) AS pageview_time
    FROM ordered_pageviews p1
    JOIN ordered_pageviews p2
    ON p1.session_id = p2.session_id
    AND p1.user_id = p2.user_id
    AND p1.rn + 1 = p2.rn
    -- optional filter to limit paths you're analyzing
    -- WHERE p1.path !~ '/app'
    GROUP BY p1.session_id,
             p1.user_id,
             p1.path,
             p1.time,
             p2.time
) a
GROUP BY 1
ORDER BY 2 DESC;

Average Events per Session

The average events per session metric can give you insight into aggregate behavior.

This query can also be modified to analyze behavior across segments and over time, allowing you to answer questions such as: Does this number change with a product launch or tweaks to the onboarding flow? Do different types of users interact differently? Changes in the average can prompt investigation into what is really happening within your app.

SELECT Round(Count(DISTINCT event_id)::DECIMAL / Count(DISTINCT session_id), 2) AS "average"
FROM main_production.all_events
-- modify your date range here
WHERE TIME >= Dateadd('day', -30, Getdate())

Average Events per Session Over Time

To modify this query to calculate the average number of events per session over the past year or past six months, update the WHERE statement to include the time range and add a GROUP BY clause to address the granularity.

SELECT TO_CHAR(main_production.all_events.time, 'YYYY-MM') as month
      ,ROUND(COUNT(DISTINCT event_id)::DECIMAL / COUNT(DISTINCT session_id), 2) AS "average"
FROM main_production.all_events
-- modify your date range here
WHERE time >= DATEADD('month', -6, getdate())
GROUP BY 1

Average Events per Session by User Property

To calculate this, join the all events table on the user table and add a GROUP BY clause based on the user properties you want to use for segmentation.

SELECT user_property
      ,ROUND(COUNT(DISTINCT event_id)::DECIMAL / COUNT(DISTINCT session_id), 2) AS "average"
FROM main_production.all_events
JOIN main_production.users
ON main_production.all_events.user_id = main_production.users.user_id
-- modify your date range here
WHERE time >= DATEADD('day', -30, getdate())
GROUP BY 1

Average Sessions per User

The average number of sessions per user can be used in the same way as average events per session and can be modified in the same manner. To find the average number of sessions per user, divide the number of unique sessions by the number of unique users across the time period.

SELECT ROUND(COUNT(DISTINCT session_id)::DECIMAL / COUNT(DISTINCT user_id), 2)
FROM main_production.sessions
-- modify the date range here
WHERE time >= DATEADD('day', -30, GETDATE())

(or any other event)

WITH first_signup AS (
    SELECT user_id, min(time) AS first_time
    FROM main_production.sign_up
    GROUP BY 1
),
sessions_pre_signup AS (
    SELECT sessions.user_id, count(distinct session_id) AS total_sessions
    FROM main_production.sessions
    JOIN first_signup
    ON first_signup.user_id = sessions.user_id
    AND sessions.time <= first_signup.first_time
    GROUP BY 1
)
SELECT avg(total_sessions)
FROM sessions_pre_signup

Bounce Rate

WITH data AS (
    SELECT time::date AS day, session_id, count(*) AS pageviews
    FROM heap_production.pageviews
    GROUP BY 1, 2
)
SELECT day, count(CASE WHEN pageviews = 1 THEN 1 ELSE NULL END) / count(*) AS bounce_rate
FROM data
GROUP BY 1
ORDER BY 1

Month-to-Month Retention

-- get a cohort based on join date
WITH new_user_activity AS (
    SELECT activity.user_id
          ,activity.TIME AS DATE
    FROM main_production.sessions AS activity
    INNER JOIN users ON users.user_id = activity.user_id
    AND users.joindate = activity.TIME
    WHERE date_part('year', joindate) >= 2014
),
-- number of users in cohort
cohort_active_user_count AS (
    SELECT to_char(DATE, 'YYYY-MM') AS DATE
          ,count(DISTINCT user_id) AS count
    FROM new_user_activity
    GROUP BY 1
)
-- change format to change granularity
SELECT joindate
      ,period
      ,new_users
      ,retained_users
      ,retention
FROM (
    SELECT to_char(new_user_activity.DATE, 'YYYY-MM') AS "joindate"
          ,'Month ' ||
          -- change month to change granularity
          datediff('month', new_user_activity.DATE, future_activity.TIME) AS period
          ,max(cohort_size.count) AS new_users
          ,-- all equal in group
          count(DISTINCT future_activity.user_id) AS retained_users
          ,round(count(DISTINCT future_activity.user_id) / max(cohort_size.count)::DECIMAL, 2) AS retention
    FROM new_user_activity
    LEFT JOIN main_production.sessions AS future_activity
    ON new_user_activity.user_id = future_activity.user_id
    AND new_user_activity.DATE <= future_activity.TIME
    LEFT JOIN cohort_active_user_count AS cohort_size
    ON to_char(new_user_activity.DATE, 'YYYY-MM') = cohort_size.DATE
    GROUP BY 1, 2
) t
WHERE period IS NOT NULL
ORDER BY 1, 2

Funnels

This section covers Data Connect queries that enable you to conduct funnel analysis by measuring all types of funnels. Funnels are fundamental to understanding any flow through your site, from onboarding to filling out forms, to virtually any step users are expected to take within your product.

The Basic Funnel

The funnel below can be used to mirror the funnel in Contentsquare's dashboard. It calculates the number of users who have completed Step 1 within the time frame, the number of users who have completed Step 2, and then Step 3. It's both sequential and time-constrained, though there are many modifications you may wish to make, as shown below.

WITH e1 AS (
    SELECT DISTINCT user_id
          ,1 AS step_1
          ,MIN(time) AS step_1_time
    -- change to table name of event 1 in your funnel
    FROM main_production.step_1 AS e1
    WHERE time BETWEEN '2016-04-13' AND '2016-04-21'
    GROUP BY 1
),
e2 AS (
    SELECT e1.user_id
          ,1 AS step_2
          ,MIN(time) AS step_2_time
    -- change to table name of event 2 in your funnel
    FROM main_production.step_2 AS e2
    INNER JOIN e1 ON e1.user_id = e2.user_id
    WHERE time BETWEEN step_1_time AND '2016-04-21'
    GROUP BY 1
),
e3 AS (
    SELECT e3.user_id
          ,1 AS step_3
          ,MIN(time) AS step_3_time
    -- change to table name of event 3 in your funnel
    FROM main_production.step_3 AS e3
    INNER JOIN e2 ON e2.user_id = e3.user_id
    WHERE TIME BETWEEN step_2_time AND '2016-04-21'
    GROUP BY 1
)
SELECT SUM(step_1) AS "Step 1"
      ,SUM(step_2) AS "Step 2"
      ,SUM(step_3) AS "Step 3"
      ,ROUND(SUM(step_2)/SUM(step_1)::DECIMAL, 2) as step_1to2_conversion
      ,ROUND(SUM(step_3)/SUM(step_2)::DECIMAL, 2) as step_2to3_conversion
      ,ROUND(SUM(step_3)/SUM(step_1)::DECIMAL, 2) as overall_conversion
FROM (
    SELECT e1.user_id
          ,step_1
          ,step_1_time
          ,step_2
          ,step_2_time
          ,step_3
          ,step_3_time
    FROM e1
    LEFT JOIN e2 ON e1.user_id = e2.user_id
    LEFT JOIN e3 ON e2.user_id = e3.user_id
)

Funnels Grouped by an Event-Level Property

Each event has a set of properties ranging from attributes Contentsquare captures automatically, like referrer, UTM parameters, and device type, as well as any properties you set using snapshots, heap.addEventProperties, or heap.track.

To view a breakdown of the conversion rate based on these event-level properties, only a couple of modifications need to be made. In the funnel, include the event-level property in the SELECT statement and GROUP BY clause. You can also join this on the users table (JOIN main_production.users on main_production.users.user_id=e1.user_id) to analyze your conversion rates based on user-level properties as well.

WITH e1 AS (
    SELECT DISTINCT e1.user_id
          ,1 AS step_1
          ,min(TIME) AS step_1_time
          ,utm_source
    FROM main_production.define_event AS e1
    INNER JOIN (
        SELECT user_id
              ,min(TIME) AS mintime
        FROM main_production.define_event
        WHERE DATE(TIME) BETWEEN '2016-04-13' AND '2016-04-20'
        GROUP BY 1
    ) AS min_value
    ON e1.user_id = min_value.user_id
    AND e1.TIME = min_value.mintime
    WHERE DATE(TIME) BETWEEN '2016-04-13' AND '2016-04-20'
    GROUP BY 1, 4
),
e2 AS (
    SELECT e1.user_id
          ,1 AS step_2
          ,MIN(time) AS step_2_time
    -- change to table name of event 2 in your funnel
    FROM main_production.step_2 AS e2
    INNER JOIN e1 ON e1.user_id = e2.user_id
    WHERE time BETWEEN step_1_time AND '2016-04-21'
    GROUP BY 1
),
e3 AS (
    SELECT e3.user_id
          ,1 AS step_3
          ,MIN(time) AS step_3_time
    -- change to table name of event 3 in your funnel
    FROM main_production.step_3 AS e3
    INNER JOIN e2 ON e2.user_id = e3.user_id
    WHERE TIME BETWEEN step_2_time AND '2016-04-21'
    GROUP BY 1
)
SELECT utm_source
      ,sum(step_1) AS "Step 1"
      ,sum(step_2) AS "Step 2"
      ,sum(step_3) AS "Step 3"
FROM (
    SELECT e1.user_id
          ,step_1
          ,step_1_time
          ,step_2
          ,step_2_time
          ,step_3
          ,step_3_time
          ,utm_source
    FROM e1
    LEFT JOIN e2 ON e1.user_id = e2.user_id
    LEFT JOIN e3 ON e2.user_id = e3.user_id
)
GROUP BY 1

Restricting Funnels to One Session

In this funnel, user ID and session ID are aligned to make sure that funnel spans across one session. There are a couple of tweaks needed in each step of the query.

WITH e1 AS (
    SELECT DISTINCT user_id
          ,session_id
          ,Min(time) AS step_1_time
    -- change to table name of event 1 in your funnel
    FROM main_production.step_1 AS e1
    WHERE time BETWEEN '2016-04-13' AND '2016-04-21'
    GROUP BY 1, 2
),
e2 AS (
    SELECT e1.user_id
          ,e1.session_id
          ,Min(time) AS step_2_time
    -- change to table name of event 2 in your funnel
    FROM main_production.step_2 AS e2
    INNER JOIN e1
    ON e1.user_id = e2.user_id
    AND e1.session_id = e2.session_id
    WHERE time BETWEEN step_1_time AND '2016-04-21'
    GROUP BY 1, 2
),
e3 AS (
    SELECT e3.user_id
          ,e3.session_id
          ,Min(time) AS step_3_time
    -- change to table name of event 3 in your funnel
    FROM main_production.step_3 AS e3
    INNER JOIN e2
    ON e2.user_id = e3.user_id
    AND e2.session_id = e3.session_id
    WHERE time BETWEEN step_2_time AND '2016-04-21'
    GROUP BY 1, 2
)
SELECT count(DISTINCT step_1) as step_one
      ,count(DISTINCT step_2) AS step_two
      ,count(DISTINCT step_3) AS step_three
      ,round(count(DISTINCT step_3)/count(DISTINCT step_1)::decimal, 2) AS conversion_rate
-- calculate additional percentages here
FROM (
    SELECT e1.user_id AS step_1
          ,e1.session_id
          ,step_1_time
          ,e2.user_id AS step_2
          ,step_2_time
          ,e3.user_id AS step_3
          ,step_3_time
    FROM e1
    LEFT JOIN e2
    ON e1.user_id = e2.user_id
    AND e1.session_id = e2.session_id
    LEFT JOIN e3
    ON e2.user_id = e3.user_id
    AND e2.session_id = e3.session_id
    ORDER BY 1
)

Conversion Rate Over Time

This report gives the user a window to complete the funnel and tracks how your conversion changes over time.

WITH e1 AS (
    SELECT DISTINCT user_id
          ,1 AS step_1
          ,MIN(time) AS step_1_time
          ,TO_CHAR(e1.time, 'YYYY-MM') as "month"
    FROM main_production.[event_1] AS e1
    WHERE TIME BETWEEN '2015-04-01' AND '2016-04-01'
    GROUP BY 1, 4
),
e2 as (
    SELECT e1.user_id, 1 AS step_2, MIN(e2.time) AS step_2_time, e1.step_1_time
    FROM main_production.[event_2] AS e2
    JOIN e1 ON e1.user_id=e2.user_id AND e1.step_1_time <= DATEADD('day', -30, e2.time)
    WHERE time BETWEEN step_1_time AND '2016-04-01'
    GROUP BY 1, 4
),
e3 as (
    SELECT e3.user_id, 1 AS step_3, MIN(time) AS step_3_time, e2.step_1_time
    FROM main_production.[event-3] AS e3
    JOIN e2 ON e2.user_id=e3.user_id AND e2.step_1_time <= DATEADD('day', -30, e3.time)
    WHERE time BETWEEN step_2_time AND '2016-04-01'
    GROUP BY 1, 4
)
SELECT MONTH, ROUND(SUM(step_3)/SUM(step_1)::DECIMAL, 2) AS "Conversion Rate"
FROM (
    SELECT e1.user_id, step_1, e1.step_1_time, step_3, step_3_time, e1.month
    FROM e1
    LEFT JOIN e2 ON e1.user_id = e2.user_id AND e1.step_1_time=e2.step_1_time
    LEFT JOIN e3 ON e2.user_id= e3.user_id AND e2.step_1_time=e3.step_1_time
)
GROUP BY 1
ORDER BY 1

Time to Conversion

Time can be an interesting metric to analyze. The time it takes for a user to make their first purchase from the date first seen, or the time it takes a user to fill out a form from the first field to submit, or the time between two purchases can be measured and optimized. You can analyze which behaviors, user properties, or first touch properties are associated with faster conversion rates.

This example measures the time it takes users to purchase an item but can be adapted to measure a slew of different events, and segmented by a variety of user and event-level properties.

SELECT avg(duration) AS "Average hours until Purchase"
FROM (
    SELECT user_id
          ,datediff('milliseconds', start_time, end_time)::DECIMAL / 1000 / 60 / 60 AS "duration"
    FROM (
        SELECT main_production.users.user_id
              ,min(joindate) AS start_time
              ,min(main_production.purchase.time) AS end_time
        FROM main_production.sign_up
        JOIN main_production.users
        ON main_production.purchase.user_id = main_production.users.user_id
        GROUP BY 1
    )
)

Path Analysis

This query allows you to track the most common flows or 'paths' into and out of your product via Data Connect.

As is, the SQL selects the first 5 events of every session consisting of at least five events. It then aggregates the number of users who have completed each flow and displays the top 20 most common paths.

WITH event_count AS (
    SELECT event_table_name
          ,count(*) AS cardinality
    FROM main_production.all_events
    WHERE TIME >= dateadd('day', -30, getDate())
    GROUP BY 1
),
all_events AS (
    SELECT DISTINCT event_id
          ,main_production.all_events.user_id AS user_id
          ,main_production.all_events.event_table_name AS event_name
          ,main_production.all_events.TIME AS occurred_at
          ,cardinality AS number
    FROM main_production.all_events
    LEFT JOIN event_count
    ON main_production.all_events.event_table_name = event_count.event_table_name
    ORDER BY 1, 2
),
events AS (
    SELECT all_events.user_id
          ,all_events.event_id
          ,event_name
          ,occurred_at
          ,cardinality
    FROM all_events
    INNER JOIN (
        SELECT event_id
              ,user_id
              ,min(number) AS cardinality
        FROM all_events
        GROUP BY 1, 2
    ) AS event
    ON all_events.number = event.cardinality
    AND all_events.event_id = event.event_id
    AND all_events.user_id = event.user_id
    ORDER BY 1, 2, 4
)
SELECT e1, e2, e3, e4, e5
      ,COUNT(*) AS occurrences
FROM (
    -- Pivot out first five events in each session
    SELECT user_id
          ,session
          ,MAX(CASE WHEN event_number = 1 THEN event_name ELSE NULL END) AS e1
          ,MAX(CASE WHEN event_number = 2 THEN event_name ELSE NULL END) AS e2
          ,MAX(CASE WHEN event_number = 3 THEN event_name ELSE NULL END) AS e3
          ,MAX(CASE WHEN event_number = 4 THEN event_name ELSE NULL END) AS e4
          ,MAX(CASE WHEN event_number = 5 THEN event_name ELSE NULL END) AS e5
    FROM (
        -- Find event number in session
        SELECT z.*
              ,ROW_NUMBER() OVER (PARTITION BY user_id, session ORDER BY occurred_at) AS event_number
        FROM (
            -- Sum breaks to find sessions
            SELECT y.*
                  ,SUM(BREAK) OVER (ORDER BY user_id, occurred_at ROWS UNBOUNDED PRECEDING) AS session
            FROM (
                -- Add flag if last event was more than 10 minutes ago
                SELECT x.*
                      ,CASE
                          WHEN last_event IS NULL OR occurred_at >= last_event + INTERVAL '10 MINUTE'
                          THEN 1
                          ELSE 0
                      END AS BREAK
                FROM (
                    -- Find last event
                    SELECT *
                          ,LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event
                    FROM events
                ) x
            ) y
        ) z
    ) a
    WHERE event_number <= 5
    GROUP BY 1, 2
) final
WHERE e1 IS NOT NULL
AND e2 IS NOT NULL
AND e3 IS NOT NULL
AND e4 IS NOT NULL
AND e5 IS NOT NULL
GROUP BY 1, 2, 3, 4, 5
ORDER BY 6 DESC
LIMIT 20

Attribution

This section covers Data Connect queries that enable you to measure attribution to better understand where your users are coming from.

Top Referrers

This query returns the top N referrers and the number of sessions they generate.

SELECT referrer
      ,COUNT(*) AS sessions
FROM main_production.sessions
WHERE referrer IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

First Touch Properties

This query generates a table that matches Contentsquare's Users view. You can export and select all the user-level properties and all the first touch properties.

How it works

This query starts by calculating the first session for each user and joins the first sessions data with the users table based on the unique user ID. From here, each user-level property and session-level property can be selected.

SELECT all_sessions.user_id
      ,first_session.first_time AS first_seen
      ,referrer
      ,utm_source
      ,utm_campaign
      ,utm_medium
      ,utm_content
FROM main_production.sessions AS all_sessions
INNER JOIN (
    SELECT user_id
          ,MIN(time) AS first_time
    FROM main_production.sessions
    GROUP BY user_id
) AS first_session
ON all_sessions.user_id = first_session.user_id
AND all_sessions.time = first_session.first_time
INNER JOIN main_production.users AS users
ON all_sessions.user_id = users.user_id
-- [optional] insert where clause here
ORDER BY 3 DESC

First Touch Property by User Count

This query returns the number of users that fall into each category. In this case, the number of users who have come in via each UTM source will be displayed. This can be adapted and broken down by any first touch or user-level properties.

SELECT utm_source, COUNT(distinct user_id)
FROM main_production.sessions AS all_sessions
INNER JOIN (
    SELECT user_id
          ,MIN(time) AS first_time
    FROM main_production.sessions
    GROUP BY user_id
) AS first_session
ON all_sessions.user_id = first_session.user_id
AND all_sessions.time = first_session.first_time
INNER JOIN main_production.users AS users
ON all_sessions.user_id = users.user_id
GROUP BY 1

Multi-Touch Attribution

Users come in via many channels, and the campaign that drives the first touch doesn't always match with the campaign on the conversion event. Because Contentsquare automatically captures the referrer and UTM parameters for each session, you can build a query that compares the channel for each session a user has. This query compares the UTM source of the first touch with the UTM source on the session with the conversion event, and calculates the number of users who fall into each category.

SELECT initial.utm_campaign AS "INITIAL"
      ,CASE
          WHEN event_2.utm_campaign IS NULL THEN 'None'
          ELSE event_2.utm_campaign
      END AS "LAST TOUCH"
      ,count(DISTINCT initial.user_id) AS "Number of Users"
FROM (
    SELECT all_sessions.user_id
          ,CASE
              WHEN all_sessions.utm_campaign IS NULL THEN 'None'
              ELSE all_sessions.utm_campaign
          END
    FROM main_production.sessions AS all_sessions
    INNER JOIN (
        SELECT user_id
              ,MIN(time) AS first_time
        FROM main_production.sessions
        GROUP BY user_id
    ) AS first_session
    ON all_sessions.user_id = first_session.user_id
    AND all_sessions.time = first_session.first_time
) AS initial
JOIN main_production.conversion_event AS event_2
ON initial.user_id = event_2.user_id
GROUP BY 1, 2
ORDER BY 3 DESC

Revenue Analysis

One of the most powerful aspects of Data Connect is the ability to join a complete set of user interactions with other data sources to see revenue data, such as a user's order history.

ARPU per UTM Source

This query combines Contentsquare's attribution data with order history data, allowing you to compare how each source is correlated with a user's downstream behavior.

WITH first_touch AS (
    SELECT all_sessions.user_id
          ,identity
          ,first_session.first_time AS first_seen
          ,utm_source
    -- add any user level properties or session level properties here
    FROM main_production.sessions AS all_sessions
    inner join (
        SELECT user_id
              ,Min(TIME) AS first_time
        FROM main_production.sessions
        GROUP BY user_id
    ) AS first_session
    ON all_sessions.user_id = first_session.user_id
    AND all_sessions.TIME = first_session.first_time
    inner join main_production.users AS users
    ON all_sessions.user_id = users.user_id
)
SELECT CASE
          WHEN utm_source IS NULL THEN 'none'
          ELSE utm_source
      END
      ,Round(SUM(order_total)::DECIMAL, 2) AS total_revenue
      ,Round(SUM(order_total)::DECIMAL / Count(DISTINCT customer_id)::DECIMAL, 2) AS avg_per_customer
FROM heap.TRANSACTION
join first_touch
ON customer_id = identity
GROUP BY 1

Monthly ARPU per UTM Source

With a slight tweak to your query, you can see how the UTM source is correlated with the ARPU on a month-to-month basis.

WITH first_touch AS (
    SELECT all_sessions.user_id
          ,identity
          ,first_session.first_time AS first_seen
          ,utm_source
    -- add any user level properties or session level properties here
    FROM main_production.sessions AS all_sessions
    inner join (
        SELECT user_id
              ,Min(TIME) AS first_time
        FROM main_production.sessions
        GROUP BY user_id
    ) AS first_session
    ON all_sessions.user_id = first_session.user_id
    AND all_sessions.TIME = first_session.first_time
    inner join main_production.users AS users
    ON all_sessions.user_id = users.user_id
)
SELECT utm_source
      ,CASE
          WHEN Length(Substring(order_date, 0, Position('/' IN order_date))) = 2
          THEN '20' || Right(order_date, 2) || '-' || Substring(order_date, 0, Position('/' IN order_date))
          ELSE '20' || Right(order_date, 2) || '-0' || Substring(order_date, 0, Position('/' IN order_date))
      END AS order_month
      ,Count(DISTINCT customer_id)
      ,Round(SUM(order_total)::DECIMAL, 2) AS total_revenue
      ,Round(SUM(order_total)::DECIMAL / Count(DISTINCT customer_id)::DECIMAL, 2) AS avg_per_customer
FROM heap.TRANSACTION
join first_touch
ON customer_id = identity
WHERE utm_source IS NOT NULL
GROUP BY 1, 2
ORDER BY 2

ARPU by Behavior

You can also combine Contentsquare data with revenue data to determine if certain actions or if the number of times an action is performed is correlated with higher returns. This report segments users into two categories: users who have uploaded files, and users who haven't uploaded files. It then calculates the ARPU for each segment.

WITH user_info AS (
    SELECT identity
          ,users.user_id
          ,SUM(order_total) AS total_revenue
          ,Count(event_id) AS event_count
    FROM main_production.users AS users
    join heap.TRANSACTION
    ON identity = customer_id
    left join main_production.dashboard_galleries_click_add_new_files AS event
    ON users.user_id = event.user_id
    GROUP BY 1, 2
)
SELECT CASE
          WHEN event_count = 0 THEN 'has not uploaded files'
          ELSE 'has uploaded files'
      END
      ,Round(SUM(total_revenue)::DECIMAL / Count(DISTINCT user_id), 2) AS avg_revenue_per_user
FROM user_info
GROUP BY 1

Churned Customer Revenue

Data Connect allows you to get a better understanding of your churned customers as well. You can answer questions like "Where did they come from?", "What did they do in my product?", and "How much revenue did a customer bring in before churning?". The table below combines Contentsquare session data, server-side events, and revenue data to enhance your understanding of churned customers.

WITH first_touch AS (
    SELECT all_sessions.user_id
          ,identity
          ,first_session.first_time AS first_seen
          ,utm_source
    -- add any user level properties or session level properties here
    FROM main_production.sessions AS all_sessions
    inner join (
        SELECT user_id
              ,Min(TIME) AS first_time
        FROM main_production.sessions
        GROUP BY user_id
    ) AS first_session
    ON all_sessions.user_id = first_session.user_id
    AND all_sessions.TIME = first_session.first_time
    inner join main_production.users AS users
    ON all_sessions.user_id = users.user_id
)
SELECT utm_source
      ,Round(SUM(order_total)::DECIMAL, 2) AS churned_customer_total_value
      ,Round(Count(DISTINCT order_id)::DECIMAL / Count(DISTINCT user_id), 2) AS avg_orders_per_user
      ,Round(SUM(order_total)::DECIMAL / Count(DISTINCT first_touch.identity), 2) AS avg_ltv
FROM first_touch
join heap.TRANSACTION
ON customer_id = first_touch.identity
join main_production.account_modification
ON first_touch.identity = main_production.account_modification.identity
WHERE account_change = 'cancel'
AND utm_source IS NOT NULL
GROUP BY 1

Modeling Your Data

Creating an Enhanced Users Table

This query provides an example of how to recreate the users table, adding the Initial User Properties (ex: Initial Referrer, Initial Browser, etc.) that are displayed in your Contentsquare account.

BEGIN;
DROP TABLE IF EXISTS enhanced_users;
CREATE TABLE enhanced_users AS
SELECT u.*
      ,s.time AS date_first_seen
      ,s.referrer AS initial_referrer
      ,s.utm_source AS initial_utm_source
      ,s.utm_medium AS initial_utm_medium
      ,s.utm_campaign AS initial_utm_campaign
      ,s.city AS initial_city
      ,s.region AS initial_region
      ,s.country AS initial_country
      ,s.landing_page AS initial_landing_page
      ,s.browser AS initial_browser
      ,s.device_type AS initial_device_type
      ,s.platform AS initial_platform
      ,s.ip AS initial_ip
FROM main_production.sessions s
JOIN main_production.users u ON s.user_id = u.user_id
JOIN (
    SELECT user_id, min(TIME) AS first_session_time
    FROM main_production.sessions
    GROUP BY user_id
) AS first_session
ON s.user_id = first_session.user_id
AND first_session_time = s.time;
END;

Sessionizing Client-Side and Server-Side Events

BEGIN;
-- Prepending a table with # makes a temp table. Change this if you want a table to persist.
DROP TABLE IF EXISTS #sessions;
CREATE TABLE #sessions AS
SELECT user_id
      ,min(time) over (partition by calc_session_id) as session_time
      ,time as event_time
      ,calc_session_id
      ,split_part(calc_session_id, '_', 2) as visit_number_in_range
      ,event_table_name
      ,first_value(path) over (partition by calc_session_id) as landing_page
      ,first_value(utm_source) over (partition by calc_session_id) as utm_source
      ,first_value(utm_medium) over (partition by calc_session_id) as utm_medium
      ,first_value(utm_campaign) over (partition by calc_session_id) as utm_campaign
      ,path
      ,total_price
      ,subtotal_price
FROM (
    SELECT *
          ,user_id || '_' || sum(new_session) over (partition by user_id order by time rows unbounded preceding) AS calc_session_id
    FROM (
        SELECT e.*
              ,p.path
              ,p.utm_source
              ,p.utm_medium
              ,p.utm_campaign
              ,o.total_price
              ,o.subtotal_price
              -- Change your session definition here i.e. 30 * 60 = 30 minutes so 60 * 60 = 1 hour.
              ,case when extract(epoch from e.time) - lag(extract(epoch from e.time)) over (partition by e.user_id order by e.time) >= 30 * 60 then 1 else 0 end as new_session
        FROM heap_production.all_events e
        LEFT JOIN heap_production.pageviews p
        ON p.user_id = e.user_id
        AND p.event_id = e.event_id
        AND p.session_id = e.session_id
        LEFT JOIN heap_production.confirmed_order o
        ON o.user_id = e.user_id
        AND o.event_id = e.event_id
        AND o.session_id = e.session_id
        -- This example focuses on pageviews and a conversion event called `confirmed_order` from Contentsquare's Shopify integration.
        -- If you extend this model to include other tables and want properties from their tables, follow the left join pattern above and add the event_table_name to filter below.
        WHERE (event_table_name = 'pageviews' or event_table_name = 'confirmed_order')
        -- Change your date range here.
        -- AND e.time >= current_date - interval '1 week'
        ORDER BY time
    ) s
)
ORDER BY time;
END;