API Example Queries

Overview

If included in your subscription, you can access data about how viewers are consuming your demos via API endpoints curated by Reprise. Follow this article to learn more about the Reprise Data API. 

Once you have the API setup you can run queries to view metrics that aren't included in the Reprise analytics dashboard. Below are a few queries you can run once the data is stored in a data repository like PosgreSQL.

Replace the highlighted parts with the relevant names from your tables and demo components.

Pre-requisites

All of the examples assume that the data extracted from Reprise into a table (here called YOUR_API_TABLE), that has the same structure as the replay_session_activity data structure.

Queries

1. # of clicks on a specific guide CTA button = Number of sessions that reached a certain guide:
   
   SELECT COUNT(DISTINCT session_id)
   FROM YOUR_API_TABLE
   where published_replay_title ='REPLAY_TITLE'
   and guide_text = 'guide text';
   
   
   
2. Comparing the number of sessions that reached a certain guide versus all other sessions, cohorted by month:
   
   SELECT
   DATE_TRUNC('month', to_date(session_created_at, 'YYYY-MM-DD HH24:MI:SS')) AS month_cohort,
   COUNT(DISTINCT CASE WHEN guide_text = 'guide text' THEN session_id ELSE NULL END)
   AS count_with_condition,
   COUNT(DISTINCT session_id) AS count_without_condition
   FROM YOUR_API_TABLE
   WHERE published_replay_title = 'REPLAY_TITLE'
   GROUP BY month_cohort
   ORDER BY month_cohort;
   
   
   
3. Find out all activities that occur before and after a certain guide:
   
   WITH GuidePositions AS (
     SELECT
        session_id,
        guide_text,
        ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY create_at) AS guide_position
     FROM
        YOUR_API_TABLE
     WHERE
        published_replay_title = 'REPLAY_TITLE' -- Add your filter condition here
   ),
   SessionGuidePositions AS (
     SELECT
        session_id,
        MAX(CASE WHEN guide_text = 'GUIDE_TEXT' THEN guide_position END) AS guide_position
     FROM
        GuidePositions
      GROUP BY
        session_id
   )
   select
      gp.session_id, 
      MAX(gp.guide_position) as total_activities,
      SUM(CASE WHEN gp.guide_position IS NOT NULL AND gp.guide_position <
   sgp.guide_position THEN 1 ELSE 0 END) AS activities_before_guide,
      SUM(CASE WHEN sgp.guide_position IS NOT NULL AND gp.guide_position >
   sgp.guide_position THEN 1 ELSE 0 END) AS activities_after_guide
   FROM
      GuidePositions gp
   LEFT JOIN
      SessionGuidePositions sgp ON sgp.session_id = gp.session_id
   GROUP BY
      gp.session_id
   ORDER BY
      gp.session_id
   
   

   Note: If you create view from this query, you can select the % of sessions that didn’t go further than X activities after the guide you care about by running:
   
   SELECT
        (SUM(CASE WHEN activities_after_guide >= YOUR_THRESHOLD AND
   activities_before_guide > 0 THEN 1 ELSE 0 END)::numeric / COUNT(*)) * 100 AS percentage
   FROM
        YOUR_VIEW;
   
   
   

4. Reporting by filtering out certain IPs (e.g. internal IPs):
   
   SELECT *
   FROM YOUR_API_TABLE
   WHERE visitor_name  NOT LIKE '%YOUR_IP%'
   AND visitor_name  NOT LIKE '%YOUR_OTHER_IP%';
   
   
   
5. Number of unique sessions per link per replay:
   
   SELECT
      published_replay_title,
      link_title,
      COUNT(DISTINCT session_id) AS session_count
   FROM
      YOUR_API_TABLE
   GROUP BY
      published_replay_title,
      link_title;
   
   
   
6. Filtering out sessions that last less than X milliseconds:
   
   WITH session_durations AS (
      SELECT
        session_id,
        MIN(create_at) AS session_start,
        MAX(create_at) AS session_end
      FROM
        YOUR_API_TABLE
      GROUP BY
        session_id
   )
   SELECT
      dld.*
   FROM
      YOUR_API_TABLE
   JOIN
      session_durations sd
   ON
      dld.session_id = sd.session_id
   WHERE
      EXTRACT(milliseconds FROM (sd.session_end::timestamp - sd.session_start::timestamp)) >=
   NUM_MILLISECONDS;
   
   
   
7. Most and least viewed screens:
   
   WITH ScreenViews AS (
    SELECT
      published_replay_title,
      snapshot_title,
      COUNT(session_id) AS view_count
    FROM
      YOUR_API_TABLE
    GROUP BY
      published_replay_title,
      snapshot_title
   ),
    RankedViews AS (
      SELECT
        published_replay_title,
        snapshot_title,
        view_count,
        ROW_NUMBER() OVER (PARTITION BY published_replay_title ORDER BY view_count
    DESC) AS rank_most_viewed,
        ROW_NUMBER() OVER (PARTITION BY published_replay_title ORDER BY view_count
    ASC) AS rank_least_viewed
      FROM
        ScreenViews
   )
    SELECT
      r.published_replay_title,
      r.snapshot_title AS most_viewed_screen,
      r.view_count AS most_views,
      l.snapshot_title AS least_viewed_screen,
      l.view_count AS least_views
    FROM
      RankedViews r
    INNER JOIN
      RankedViews l
    ON
      r.published_replay_title = l.published_replay_title
      AND r.rank_most_viewed = 1
      AND l.rank_least_viewed = 1;

Related to

• API
• Reprise Data API