Setup Guide: Activtrak for Google Data Studio - Financial Loss

ActivTrak Product Team

published October 02, 2025 20:59

updated May 18, 2026 15:32

The Financial Loss template provides leaders with clear visibility into suspicious work patterns that may indicate the use of work-mimicking technology. This specialized analytics tool helps you quickly identify unusual activity signals, quantify potential productivity impacts, and take informed action. Detecting and addressing potential false activity can ensure your workforce data accurately reflects genuine productivity.

ActivConnect is available as an add-on for customers on a paid plan. Learn more about ActivConnect here.

Template access requirements
Access the template
Sharing Reports
Scheduling Reports
Learn more

Template access requirements

Before you begin, ensure you have:

An ActivTrak paid plan and the ActivConnect API (Add-on)
Your ActivConnect username ([account_number]@activtrak.us) and password. Your account number is in the Account Information menu in ActivTrak; access it via the circle icon in the top right corner.

Access the template

Validate ActivConnect credentials

Navigate to Google Cloud Console
Log in with your ActivConnect username ([account_number]@activtrak.us) and password

Take template ownership

To protect your data, ActivConnect restricts data sharing outside the activtrak.us domain. The steps outlined in this section will guide you through setting up permissions to grant data access to anyone within your organization.

Navigate to https://lookerstudio.google.com and ensure you are logged in under your company’s Google account
Starting from ActivTrak for Financial Loss (available here), make a copy of the report to take ownership of it and enable configuration and editing functions

Keep the data sources on the prompt and click Copy Report to continue

Click the Share button in the upper right corner and provide Editor permissions to your ActivConnect user account. This will be in the format of [account_number]@activtrak.us.

Set up data sources

In a separate tab or incognito window, ensure you are logged in with your ActivConnect user account. This will be in the format of ([account_number]@activtrak.us). Your account number is in the Account Information menu in ActivTrak; access it via the circle icon in the top right corner.
Then, navigate to https://lookerstudio.google.com
Select the Shared with Me tab from the left navigation menu and open your copy of ActivTrak for Data Studio - Financial Loss
Enter Edit Mode by clicking the blue Edit button in the top right corner

Performance by Group

Click Resource > Manage added data sources

Click on Edit to the right of Performance by Group
Click on Edit Connection at the top

Change the highlighted “AC_Demo” into your 6-digit account number. There are 3 AC_Demo placeholders to replace.
The default date parameter is set to 2024-12-01. Update this if needed. The parameter must be in the YYYY-MM-DD format. This script has only one date parameter.

Click on Reconnect at the top right

Click Apply in the pop-up window to confirm the changes. Then click Done once complete.

Click to view code

WITH group_goals AS (
  SELECT 
    account_id, 
    user_id, 
    is_primary_group, 
    group_id, 
    group_name, 
    user_name,
    productive_hrs_day as group_productive_hrs_goal,
    focused_hrs_day as group_focused_hrs_goal,
    collaboration_hrs_day as group_collaboration_hrs_goal
  FROM (
    SELECT 
      pug.account_id, 
      pug.user_id, 
      pug.is_primary_group, 
      pug.group_id, 
      pug.group_name, 
      pug.user_name, 
      gg.metric_name, 
      gg.target_value
    FROM `us-activtrak-ac-prod.AC_Demo.premium_user_groups` pug
    INNER JOIN `us-activtrak-ac-prod.AC_Demo.group_goals` gg 
      ON gg.group_id = pug.group_id
  )
  PIVOT (
    MAX(target_value) 
    FOR metric_name IN (
      'productive_hrs_day',
      'focused_hrs_day', 
      'collaboration_hrs_day'
    )
  )
), 

daily_metrics as (
  SELECT *,
    CONCAT(local_date, "_", user_name) as date_user,
    DATE_TRUNC(local_date, MONTH) as date_month,
    CASE 
      WHEN SUM(active_day_count) IS NULL THEN
        (SUM(productive_active_duration_seconds) + SUM(productive_passive_duration_seconds)) / 3600 / COUNT(DISTINCT CONCAT(local_date, "_", user_name))
      WHEN SUM(active_day_count) = 0 THEN
        NULL
      ELSE
        (SUM(productive_active_duration_seconds) + SUM(productive_passive_duration_seconds)) / 3600 / SUM(active_day_count)
    END AS productive_hrs_day
  FROM `us-activtrak-ac-prod.AC_Demo.daily_user_summary`
  GROUP BY ALL
),

base_data AS (
  SELECT gg.*, dm.* EXCEPT (user_id, user_name)
  FROM group_goals gg
  INNER JOIN daily_metrics dm ON dm.user_id = gg.user_id
  WHERE local_date = '2024-12-01'
),

user_monthly_averages AS (
  SELECT 
    group_id,
    group_name,
    user_id,
    user_name,
    is_primary_group,
    group_productive_hrs_goal,
    date_month,
    FORMAT_DATE('%Y-%m', date_month) as monthyear,
    AVG(productive_hrs_day) as avg_productive_hrs_day
  FROM base_data
  WHERE productive_hrs_day IS NOT NULL
  GROUP BY 
    group_id, 
    group_name, 
    user_id, 
    user_name, 
    is_primary_group, 
    group_productive_hrs_goal, 
    date_month
)

SELECT 
  monthyear,
  group_name,
 COUNT(CASE WHEN is_primary_group = true THEN user_id END) as count_of_users,
  COUNT(CASE WHEN is_primary_group = true AND avg_productive_hrs_day < group_productive_hrs_goal THEN user_id END) as users_below_goal,
  COUNT(CASE WHEN is_primary_group = true AND avg_productive_hrs_day < 5 THEN user_id END) as users_below_5_productive_hrs,
  SUM(CASE 
      WHEN avg_productive_hrs_day is not null AND avg_productive_hrs_day <= group_productive_hrs_goal
      THEN (group_productive_hrs_goal - avg_productive_hrs_day) / group_productive_hrs_goal 
    END) AS unused_capacity
FROM user_monthly_averages
GROUP BY monthyear, group_name
ORDER BY monthyear DESC, group_name
;

Performance by Location

Repeat the same steps as the Performance by Group data set up, but pick the Performance by Location data source this time. There are 5 AC_Demo placeholders to replace. The default date parameter is set to 2024-12-01. Update this if needed. This script has two date parameters.

Click to view code

  WITH group_goals AS (
  SELECT 
    account_id, 
    user_id, 
    is_primary_group, 
    group_id, 
    group_name, 
    user_name,
    productive_hrs_day as group_productive_hrs_goal,
    focused_hrs_day as group_focused_hrs_goal,
    collaboration_hrs_day as group_collaboration_hrs_goal
  FROM (
    SELECT 
      pug.account_id, 
      pug.user_id, 
      pug.is_primary_group, 
      pug.group_id, 
      pug.group_name, 
      pug.user_name, 
      gg.metric_name, 
      gg.target_value
    FROM (
      SELECT * FROM `us-activtrak-ac-prod.AC_Demo.premium_user_groups`
      WHERE is_primary_group = true 
      QUALIFY ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY user_id DESC) = 1
    ) pug
    INNER JOIN `us-activtrak-ac-prod.AC_Demo.group_goals` gg 
      ON gg.group_id = pug.group_id
  )
  PIVOT (
    MAX(target_value) 
    FOR metric_name IN (
      'productive_hrs_day',
      'focused_hrs_day', 
      'collaboration_hrs_day'
    )
  )
), 

daily_metrics AS (
  SELECT 
    user_name,
    local_date,
    DATE_TRUNC(local_date, MONTH) as date_month,
    CONCAT(local_date, "_", user_name) as date_user,
    CASE 
      WHEN SUM(active_day_count) IS NULL THEN
        (SUM(productive_active_duration_seconds) + SUM(productive_passive_duration_seconds)) / 3600 / COUNT(*)
      WHEN SUM(active_day_count) = 0 THEN
        NULL
      ELSE
        (SUM(productive_active_duration_seconds) + SUM(productive_passive_duration_seconds)) / 3600 / SUM(active_day_count)
    END AS productive_hrs_day
  FROM `us-activtrak-ac-prod.AC_Demo.daily_user_summary`
  WHERE local_date = '2024-12-01'
  GROUP BY user_name, local_date
),

user_work_location_labels AS (
  SELECT 
    user_name,
    DATE_TRUNC(local_date, MONTH) as date_month,
    FORMAT_DATE('%Y-%m', DATE_TRUNC(local_date, MONTH)) as monthyear,
    ARRAY_AGG(DISTINCT location IGNORE NULLS) as locations_used,
    CASE 
      WHEN 
        COUNT(DISTINCT CASE WHEN location = 'Office' THEN location END)  0
        AND COUNT(DISTINCT CASE WHEN location IN ('Remote', 'Office/Remote', 'Unknown') THEN location END) = 0
      THEN 'Office Only'
      WHEN 
        COUNT(DISTINCT CASE WHEN location = 'Remote' THEN location END)  0
        AND COUNT(DISTINCT CASE WHEN location IN ('Office', 'Office/Remote', 'Unknown') THEN location END) = 0
      THEN 'Remote Only'
      ELSE 'Hybrid'
    END AS user_work_location_label
  FROM `us-activtrak-ac-prod.AC_Demo.daily_user_summary`
  WHERE local_date >= '2024-12-01'
    AND location IS NOT NULL 
    AND TRIM(location) != ''
  GROUP BY user_name, DATE_TRUNC(local_date, MONTH), FORMAT_DATE('%Y-%m', DATE_TRUNC(local_date, MONTH))
),

base_data AS (
  SELECT gg.*, dm.* EXCEPT (user_name)
  FROM group_goals gg
  INNER JOIN daily_metrics dm ON dm.user_name = gg.user_name
),

user_monthly_productivity AS (
  SELECT 
    user_name,
    user_id,
    group_name,
    group_id,
    is_primary_group,
    group_productive_hrs_goal,
    date_month,
    FORMAT_DATE('%Y-%m', date_month) as monthyear,
    AVG(productive_hrs_day) as avg_productive_hrs_day
  FROM base_data
  WHERE productive_hrs_day IS NOT NULL
  GROUP BY 
    user_name, 
    user_id, 
    group_name, 
    group_id,
    is_primary_group, 
    group_productive_hrs_goal, 
    date_month
),

user_performance_with_location AS (
  SELECT 
    ump.user_name,
    ump.user_id,
    ump.group_name,
    ump.group_id,
    ump.is_primary_group,
    ump.group_productive_hrs_goal,
    ump.date_month,
    ump.monthyear,
    ump.avg_productive_hrs_day,
    COALESCE(wl.user_work_location_label, 'Unknown') as user_work_location_label,
    wl.locations_used,
    CASE 
      WHEN ump.avg_productive_hrs_day IS NULL THEN NULL
      WHEN ump.avg_productive_hrs_day >= ump.group_productive_hrs_goal THEN 0
      ELSE (ump.group_productive_hrs_goal - ump.avg_productive_hrs_day) / ump.group_productive_hrs_goal
    END AS unused_capacity_fte
  FROM user_monthly_productivity ump
  LEFT JOIN user_work_location_labels wl 
    ON ump.user_name = wl.user_name
    AND ump.date_month = wl.date_month
),

work_location_metrics AS (
  SELECT 
    monthyear,
    group_name,
    user_work_location_label,
    COUNT(DISTINCT CASE 
      WHEN avg_productive_hrs_day IS NOT NULL 
           AND avg_productive_hrs_day < group_productive_hrs_goal 
           AND is_primary_group = true
      THEN user_name 
    END) as user_count_below_goal_by_location,
    COUNT(DISTINCT CASE 
      WHEN avg_productive_hrs_day IS NOT NULL 
           AND is_primary_group = true
      THEN user_name 
    END) as total_users_by_location,
    SUM(CASE 
      WHEN unused_capacity_fte IS NOT NULL 
           AND unused_capacity_fte > 0 
           AND is_primary_group = true
      THEN unused_capacity_fte 
      ELSE 0 
    END) as unused_capacity_fte_by_location
  FROM user_performance_with_location
  WHERE user_work_location_label IN ('Hybrid', 'Office Only', 'Remote Only', 'Unknown')
  GROUP BY monthyear, group_name, user_work_location_label
)

SELECT 
  monthyear,
  group_name,
  user_work_location_label,
  total_users_by_location,
  user_count_below_goal_by_location,
  unused_capacity_fte_by_location
FROM work_location_metrics
WHERE total_users_by_location > 0
ORDER BY monthyear DESC, group_name, user_work_location_label
;

Performance by Productivity Threshold

Repeat the same steps as the Performance by Group data set up, but pick the Performance by Productivity Threshold data source this time. There are 3 AC_Demo placeholders to replace. The default date parameter is set to 2024-12-01. Update this if needed. This script has only one date parameter.

Click to view code

  WITH group_goals AS (
  SELECT 
    account_id, 
    user_id, 
    is_primary_group, 
    group_id, 
    group_name, 
    user_name,
    productive_hrs_day as group_productive_hrs_goal,
    focused_hrs_day as group_focused_hrs_goal,
    collaboration_hrs_day as group_collaboration_hrs_goal
  FROM (
    SELECT 
      pug.account_id, 
      pug.user_id, 
      pug.is_primary_group, 
      pug.group_id, 
      pug.group_name, 
      pug.user_name, 
      gg.metric_name, 
      gg.target_value
    FROM (
      SELECT * FROM `us-activtrak-ac-prod.AC_Demo.premium_user_groups`
      WHERE is_primary_group = true 
      QUALIFY ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY user_id DESC) = 1
    ) pug
    INNER JOIN `us-activtrak-ac-prod.AC_Demo.group_goals` gg 
      ON gg.group_id = pug.group_id
  )
  PIVOT (
    MAX(target_value) 
    FOR metric_name IN (
      'productive_hrs_day',
      'focused_hrs_day', 
      'collaboration_hrs_day'
    )
  )
), 

daily_metrics AS (
  SELECT 
    user_name,
    local_date,
    DATE_TRUNC(local_date, MONTH) as date_month,
    CONCAT(local_date, "_", user_name) as date_user,
    CASE 
      WHEN SUM(active_day_count) IS NULL THEN
        (SUM(productive_active_duration_seconds) + SUM(productive_passive_duration_seconds)) / 3600 / COUNT(*)
      WHEN SUM(active_day_count) = 0 THEN
        NULL
      ELSE
        (SUM(productive_active_duration_seconds) + SUM(productive_passive_duration_seconds)) / 3600 / SUM(active_day_count)
    END AS productive_hrs_day
  FROM `us-activtrak-ac-prod.AC_Demo.daily_user_summary`
  WHERE local_date >= '2024-12-01'
  GROUP BY user_name, local_date
),

productive_hrs_cutoff AS (
  SELECT productive_hrs_cutoff 
  FROM UNNEST(GENERATE_ARRAY(1, 8, 1)) AS productive_hrs_cutoff
),

base_data AS (
  SELECT gg.*, dm.* EXCEPT (user_name)
  FROM group_goals gg
  INNER JOIN daily_metrics dm ON dm.user_name = gg.user_name
),

user_monthly_productivity AS (
  SELECT 
    user_name,
    user_id,
    group_name,
    group_id,
    is_primary_group,
    group_productive_hrs_goal,
    date_month,
    FORMAT_DATE('%Y-%m', date_month) as monthyear,
    AVG(productive_hrs_day) as avg_productive_hrs_day
  FROM base_data
  WHERE productive_hrs_day IS NOT NULL
  GROUP BY 
    user_name, 
    user_id, 
    group_name, 
    group_id,
    is_primary_group, 
    group_productive_hrs_goal, 
    date_month
),

monthly_cutoff_combinations AS (
  SELECT 
    ump.monthyear,
    ump.date_month,
    phc.productive_hrs_cutoff,
    ump.user_name,
    ump.user_id,
    ump.group_name,
    ump.is_primary_group,
    ump.group_productive_hrs_goal,
    ump.avg_productive_hrs_day,
    CASE 
      WHEN ump.avg_productive_hrs_day IS NULL THEN 0
      WHEN ump.avg_productive_hrs_day >= phc.productive_hrs_cutoff THEN 0 
      WHEN ump.avg_productive_hrs_day >= ump.group_productive_hrs_goal THEN 0
      ELSE (ump.group_productive_hrs_goal - ump.avg_productive_hrs_day) / ump.group_productive_hrs_goal
    END AS unused_capacity_fte
  FROM user_monthly_productivity ump
  CROSS JOIN productive_hrs_cutoff phc
),

monthly_totals AS (
  SELECT 
    monthyear,
    group_name,
    COUNT(DISTINCT CASE 
      WHEN is_primary_group = true 
      THEN user_name 
    END) as total_user_count
  FROM user_monthly_productivity
  GROUP BY monthyear, group_name
),

monthly_totals_all_groups AS (
  SELECT 
    monthyear,
    COUNT(DISTINCT CASE 
      WHEN is_primary_group = true 
      THEN user_name 
    END) as total_user_count_all_groups
  FROM user_monthly_productivity
  GROUP BY monthyear
),

productivity_threshold_metrics AS (
  SELECT 
    mcc.monthyear,
    mcc.group_name,
    mcc.productive_hrs_cutoff,
    CONCAT('< ', CAST(mcc.productive_hrs_cutoff AS STRING), ' Hrs/Day') as x_axis_value,
    COUNT(DISTINCT CASE 
      WHEN mcc.avg_productive_hrs_day < mcc.productive_hrs_cutoff 
           AND mcc.is_primary_group = true
      THEN mcc.user_name 
    END) as user_count_below_x_productive_hours,
    SUM(CASE 
      WHEN mcc.avg_productive_hrs_day < mcc.productive_hrs_cutoff 
           AND mcc.unused_capacity_fte > 0 
           AND mcc.is_primary_group = true
      THEN mcc.unused_capacity_fte 
      ELSE 0 
    END) as unused_capacity_fte_below_x_productive_hrs
  FROM monthly_cutoff_combinations mcc
  GROUP BY mcc.monthyear, mcc.group_name, mcc.productive_hrs_cutoff
),

productivity_threshold_summary AS (
  SELECT 
    ptm.*,
    mt.total_user_count,
    mtag.total_user_count_all_groups,
  FROM productivity_threshold_metrics ptm
  LEFT JOIN monthly_totals mt 
    ON ptm.monthyear = mt.monthyear 
    AND ptm.group_name = mt.group_name
  LEFT JOIN monthly_totals_all_groups mtag 
    ON ptm.monthyear = mtag.monthyear
)

SELECT 
  monthyear,
  group_name,
  productive_hrs_cutoff,
  x_axis_value,
  total_user_count,
  total_user_count_all_groups,
  user_count_below_x_productive_hours,
  unused_capacity_fte_below_x_productive_hrs
FROM productivity_threshold_summary
ORDER BY monthyear DESC, group_name, productive_hrs_cutoff
;

Recurring Low Utilization

Repeat the same steps as the Performance by Group data set up, but pick the Recurring Low Utilization data source this time. There are 3 AC_Demo placeholders to replace. The default date parameter is set to 2024-12-01. Update this if needed. This script has only one date parameter.

Click to view code

WITH consecutive_months_buckets AS (
  SELECT months_below_goal, lower_bound, upper_bound FROM UNNEST([
    STRUCT('< 2 Months' AS months_below_goal, 0 AS lower_bound, 1 AS upper_bound),
    STRUCT('2 - 4 Months' AS months_below_goal, 2 AS lower_bound, 4 AS upper_bound),
    STRUCT('5 - 8 Months' AS months_below_goal, 5 AS lower_bound, 8 AS upper_bound),
    STRUCT('> 8 Months' AS months_below_goal, 9 AS lower_bound, 999999999 AS upper_bound)
  ])
),


group_goals AS (
  SELECT 
    account_id, 
    user_id, 
    is_primary_group, 
    group_id, 
    group_name, 
    user_name,
    productive_hrs_day as group_productive_hrs_goal,
    focused_hrs_day as group_focused_hrs_goal,
    collaboration_hrs_day as group_collaboration_hrs_goal
  FROM (
    SELECT 
      pug.account_id, 
      pug.user_id, 
      pug.is_primary_group, 
      pug.group_id, 
      pug.group_name, 
      pug.user_name, 
      gg.metric_name, 
      gg.target_value
    FROM (
      SELECT * FROM `us-activtrak-ac-prod.AC_Demo.premium_user_groups`
      WHERE is_primary_group = true 
      QUALIFY ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY user_id DESC) = 1
    ) pug
    INNER JOIN `us-activtrak-ac-prod.AC_Demo.group_goals` gg 
      ON gg.group_id = pug.group_id
  )
  PIVOT (
    MAX(target_value) 
    FOR metric_name IN (
      'productive_hrs_day',
      'focused_hrs_day', 
      'collaboration_hrs_day'
    )
  )
), 

daily_metrics AS (
  SELECT 
    user_name,
    local_date,
    DATE_TRUNC(local_date, MONTH) as date_month,
    CONCAT(local_date, "_", user_name) as date_user,
    CASE 
      WHEN SUM(active_day_count) IS NULL THEN
        (SUM(productive_active_duration_seconds) + SUM(productive_passive_duration_seconds)) / 3600 / COUNT(*)
      WHEN SUM(active_day_count) = 0 THEN
        NULL
      ELSE
        (SUM(productive_active_duration_seconds) + SUM(productive_passive_duration_seconds)) / 3600 / SUM(active_day_count)
    END AS productive_hrs_day
  FROM `us-activtrak-ac-prod.AC_Demo.daily_user_summary`
  WHERE local_date = '2024-01-01'
  GROUP BY user_name, local_date 
),

base_data AS (
  SELECT gg.*, dm.* EXCEPT (user_name)
  FROM group_goals gg
  INNER JOIN daily_metrics dm ON dm.user_name = gg.user_name
),

user_monthly_averages AS (
  SELECT 
    group_id, group_name, user_id, user_name, is_primary_group,
    group_productive_hrs_goal, date_month,
    FORMAT_DATE('%Y-%m', date_month) as monthyear,
    AVG(productive_hrs_day) as avg_productive_hrs_day,
    CASE 
      WHEN AVG(productive_hrs_day) < group_productive_hrs_goal THEN 1 
      ELSE 0 
    END as is_below_goal,
    CASE 
      WHEN AVG(productive_hrs_day) IS NOT NULL 
           AND AVG(productive_hrs_day) < group_productive_hrs_goal 
      THEN (group_productive_hrs_goal - AVG(productive_hrs_day)) / group_productive_hrs_goal
      ELSE 0
    END as unused_capacity_fte
  FROM base_data
  WHERE productive_hrs_day IS NOT NULL
  GROUP BY 
    group_id, group_name, user_id, user_name, is_primary_group, 
    group_productive_hrs_goal, date_month
),
user_streak_groups AS (
  SELECT 
    *,
    SUM(CASE WHEN is_below_goal = 0 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY user_name ORDER BY date_month) as streak_break_group
  FROM user_monthly_averages
),

user_consecutive_months AS (
  SELECT 
    monthyear,
    date_month,
    group_id,
    group_name,
    user_id,
    user_name,
    is_primary_group,
    group_productive_hrs_goal,
    avg_productive_hrs_day,
    is_below_goal,
    unused_capacity_fte,
    streak_break_group,
    CASE 
      WHEN is_below_goal = 1 THEN
        ROW_NUMBER() OVER (
          PARTITION BY user_name, streak_break_group, is_below_goal
          ORDER BY date_month
        )
      ELSE 0
    END as consecutive_months_below_goal
  FROM user_streak_groups
  QUALIFY ROW_NUMBER() OVER (PARTITION BY user_name, date_month ORDER BY group_id) = 1
),

user_monthly_status AS (
  SELECT 
    monthyear,
    date_month,
    group_id,
    group_name,
    user_id,
    user_name,
    is_primary_group,
    group_productive_hrs_goal,
    avg_productive_hrs_day,
    is_below_goal,
    unused_capacity_fte,
    consecutive_months_below_goal,
    MAX(consecutive_months_below_goal) 
      OVER (PARTITION BY user_name, streak_break_group ORDER BY date_month) as current_streak_length
  FROM user_consecutive_months
),

users_in_buckets AS (
  SELECT 
    ums.monthyear,
    ums.date_month,
    ums.group_name,
    ums.user_name,
    ums.is_primary_group,
    ums.is_below_goal,
    ums.consecutive_months_below_goal,
    ums.unused_capacity_fte,
    cmb.months_below_goal as bucket_label
  FROM user_monthly_status ums
  CROSS JOIN consecutive_months_buckets cmb
  WHERE ums.is_below_goal = 1 
    AND ums.consecutive_months_below_goal >= cmb.lower_bound 
    AND ums.consecutive_months_below_goal <= cmb.upper_bound
),

monthly_totals AS (
  SELECT 
    monthyear,
    COUNT(DISTINCT CASE WHEN is_primary_group = true THEN user_name END) as total_users_all_groups
  FROM user_monthly_status
  GROUP BY monthyear
),

aggregated_results AS (
  SELECT 
    uib.monthyear,
    uib.date_month,
    uib.group_name,
    uib.bucket_label as months_below_goal,
    COUNT(DISTINCT CASE 
      WHEN uib.is_primary_group = true 
      THEN uib.user_name 
    END) as recurring_users,
    SUM(CASE 
      WHEN uib.is_primary_group = true 
      THEN uib.unused_capacity_fte 
      ELSE 0 
    END) as unused_capacity_fte,
    MAX(mt.total_users_all_groups) as total_users
  FROM users_in_buckets uib
  LEFT JOIN monthly_totals mt ON uib.monthyear = mt.monthyear
  GROUP BY 
    uib.monthyear,
    uib.date_month,
    uib.group_name,
    uib.bucket_label
)

SELECT 
  monthyear,
  date_month,
  group_name,
  months_below_goal,
  recurring_users,
  total_users,
  unused_capacity_fte
FROM aggregated_results
UNION ALL
SELECT 
  monthyear,
  date_month,
  group_name,
  months_below_goal,
  0 as recurring_users,
  total_users,
  0 as unused_capacity_fte
FROM (
  SELECT DISTINCT 
    ums.monthyear,
    ums.date_month,
    ums.group_name,
    cmb.months_below_goal,
    mt.total_users_all_groups as total_users
  FROM user_monthly_status ums
  CROSS JOIN consecutive_months_buckets cmb
  LEFT JOIN monthly_totals mt ON ums.monthyear = mt.monthyear
)
WHERE (monthyear, group_name, months_below_goal) NOT IN (
  SELECT (monthyear, group_name, months_below_goal) 
  FROM aggregated_results
)
ORDER BY 
  date_month DESC,
  group_name,
  CASE months_below_goal
    WHEN '< 2 Months' THEN 1
    WHEN '2 - 4 Months' THEN 2
    WHEN '5 - 8 Months' THEN 3
    WHEN '> 8 Months' THEN 4
  END
;

6-Month Productivity Trend

Repeat the same steps as the Performance by Group data set up, but pick the 6-Month Productivity Trend data source this time. There are 4 AC_Demo placeholders to replace. There are no date parameters in this script.

Click to view code

WITH latest_complete_month AS (
  SELECT 
    MAX(DATE_TRUNC(local_date, MONTH)) as last_month
  FROM `us-activtrak-ac-prod.AC_Demo.daily_user_summary`
  WHERE DATE_TRUNC(local_date, MONTH) < DATE_TRUNC(CURRENT_DATE(), MONTH)
),

six_month_range AS (
  SELECT 
    last_month as end_month,
    DATE_SUB(last_month, INTERVAL 5 MONTH) as start_month
  FROM latest_complete_month
),

trend_months AS (
  SELECT 
    FORMAT_DATE('%Y-%m', DATE_ADD(smr.start_month, INTERVAL offset MONTH)) as monthyear,
    DATE_ADD(smr.start_month, INTERVAL offset MONTH) as trend_date,
    offset + 1 as month_order
  FROM six_month_range smr
  CROSS JOIN UNNEST(GENERATE_ARRAY(0, 5)) as offset
),

group_goals AS (
  SELECT 
    user_name, 
    group_name, 
    is_primary_group,
    productive_hrs_day as group_productive_hrs_goal
  FROM (
    SELECT 
      pug.user_name, 
      pug.group_name, 
      pug.is_primary_group,
      gg.metric_name, 
      gg.target_value
    FROM (
      SELECT * FROM `us-activtrak-ac-prod.AC_Demo.premium_user_groups`
      WHERE is_primary_group = true 
      QUALIFY ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY user_id DESC) = 1
    ) pug
    INNER JOIN `us-activtrak-ac-prod.AC_Demo.group_goals` gg 
      ON gg.group_id = pug.group_id
  )
  PIVOT (MAX(target_value) FOR metric_name IN ('productive_hrs_day'))
),

daily_metrics AS (
  SELECT 
    user_name,
    local_date,
    DATE_TRUNC(local_date, MONTH) as date_month,
    CONCAT(local_date, "_", user_name) as date_user,
    CASE 
      WHEN SUM(active_day_count) IS NULL THEN
        (SUM(productive_active_duration_seconds) + SUM(productive_passive_duration_seconds)) / 3600 / COUNT(*)
      WHEN SUM(active_day_count) = 0 THEN NULL
      ELSE (SUM(productive_active_duration_seconds) + SUM(productive_passive_duration_seconds)) / 3600 / SUM(active_day_count)
    END AS productive_hrs_day
  FROM `us-activtrak-ac-prod.AC_Demo.daily_user_summary`
  CROSS JOIN six_month_range smr
  WHERE DATE_TRUNC(local_date, MONTH) >= smr.start_month
    AND DATE_TRUNC(local_date, MONTH) <= smr.end_month
  GROUP BY user_name, local_date  
),

base_data AS (
  SELECT gg.*, dm.* EXCEPT (user_name)
  FROM group_goals gg
  INNER JOIN daily_metrics dm ON dm.user_name = gg.user_name
),

user_monthly_averages AS (
  SELECT 
    group_name, 
    user_name, 
    is_primary_group, 
    group_productive_hrs_goal,
    date_month, 
    FORMAT_DATE('%Y-%m', date_month) as monthyear,
    AVG(productive_hrs_day) as avg_productive_hrs_day,
    CASE 
      WHEN AVG(productive_hrs_day) IS NULL THEN 0
      WHEN AVG(productive_hrs_day) >= group_productive_hrs_goal THEN 0
      ELSE (group_productive_hrs_goal - AVG(productive_hrs_day)) / group_productive_hrs_goal
    END as unused_capacity_fte
  FROM base_data
  WHERE productive_hrs_day IS NOT NULL
  GROUP BY group_name, user_name, is_primary_group, group_productive_hrs_goal, date_month
),

monthly_metrics AS (
  SELECT 
    monthyear,
    COUNT(DISTINCT CASE WHEN is_primary_group = true THEN user_name END) as count_of_users,
    COUNT(DISTINCT CASE 
      WHEN is_primary_group = true 
        AND avg_productive_hrs_day < group_productive_hrs_goal 
      THEN user_name 
    END) as users_below_goal,
    COUNT(DISTINCT CASE 
      WHEN is_primary_group = true 
        AND avg_productive_hrs_day < 5 
      THEN user_name 
    END) as users_below_5_productive_hrs,
    ROUND(
      SAFE_DIVIDE(
        COUNT(DISTINCT CASE 
          WHEN is_primary_group = true 
            AND avg_productive_hrs_day < group_productive_hrs_goal 
          THEN user_name 
        END),
        COUNT(DISTINCT CASE WHEN is_primary_group = true THEN user_name END)
      ) * 100, 
      2
    ) as percent_of_users_below_goal,
    SUM(CASE 
      WHEN is_primary_group = true 
        AND unused_capacity_fte > 0 
      THEN unused_capacity_fte 
      ELSE 0 
    END) AS unused_capacity
  FROM user_monthly_averages
  GROUP BY monthyear
)

SELECT 
  tm.monthyear,
  tm.trend_date,
  tm.month_order,
  COALESCE(mm.count_of_users, 0) as count_of_users,
  COALESCE(mm.users_below_goal, 0) as users_below_goal,
  COALESCE(mm.users_below_5_productive_hrs, 0) as users_below_5_productive_hrs,
  COALESCE(mm.percent_of_users_below_goal, 0) as percent_of_users_below_goal,
  COALESCE(mm.unused_capacity, 0) as unused_capacity,
  unused_capacity/count_of_users as unused_capacity_pct,
  'Last 6 Months' as chart_context,
  LAG(COALESCE(mm.percent_of_users_below_goal, 0)) 
    OVER (ORDER BY tm.month_order) as prev_month_pct,
  COALESCE(mm.percent_of_users_below_goal, 0) - 
    LAG(COALESCE(mm.percent_of_users_below_goal, 0)) 
      OVER (ORDER BY tm.month_order) as pct_change_from_prev_month
FROM trend_months tm
LEFT JOIN monthly_metrics mm ON tm.monthyear = mm.monthyear
ORDER BY tm.month_order
;

Salary

Create a Google Sheets document with two columns: group_name and average_annual_salary. Change the name of the tab to Salary by Team. The name of the document itself does not matter.

Repeat the same steps as the Performance by Group data set up, but pick the Financial Loss - Salary Example data source this time
Locate your spreadsheet and select the Salary by Team worksheet within it. Ensure that “Use first row as headers” is selected.
Click Close to exit the data source configuration window

Rejoin data blends

Group_Salary

Navigate to Resource > Manage blends

Click Edit on Group_Salary
Click the middle box that shows two circles in a Venn diagram layout (in this example, the box also contains the text "1 condition")

Select Left outer for the Join operator, and select group_name for both tables under Join conditions.

Click Save, and Save again in the bottom right-hand corner
Use the back arrow at the top to return to the list of Blends

Location_Salary

Repeat the same steps as Group_Salary, but select the Location_Salary blend

Productivity_Salary

Repeat the same steps as Group_Salary, but select the Productivity_Salary

ConsecutiveMonths_Salary

Repeat the same steps as Group_Salary, but select the ConsecutiveMonths_Salary

Click Close to exit the data source configuration window

Sharing Reports

Click the Share button to open the sharing menu
Select the gear icon in the top left corner to define access levels to download and print
Once you have completed your desired configuration, add the individuals you want to share the report with and assign them the appropriate permissions

Scheduling Reports

To configure a scheduled delivery of a report, click the drop-down arrow next to the View/Share tab and select Schedule email delivery
Add the recipient's email address(es) and define your desired settings for Pages, Start Time, Repeat (frequency) and Message as shown in the image below
Click Schedule when finished

Setup Guide: Activtrak for Google Data Studio - Financial Loss

Contents

Template access requirements

Access the template

Validate ActivConnect credentials

Take template ownership

Set up data sources

Performance by Group

Performance by Location

Performance by Productivity Threshold

Recurring Low Utilization

6-Month Productivity Trend

Salary

Rejoin data blends

Group_Salary

Location_Salary

Productivity_Salary

ConsecutiveMonths_Salary

Sharing Reports

Scheduling Reports

Learn more

Can't find what you're looking for?

By use case

By role

By type

Educational