1. ActivTrak Help Center
  2. Integrations & API
  3. Google Looker Studio (GLS)

ActivTrak's Google Looker Studio Template Setup Guide

ActivTrak Product Team

ActivTrak Product Team

Unlock the power of ActivConnect and access your ActivTrak data without the need for advanced SQL or BI knowledge. ActivConnect (Powered by BigQuery) leverages the native integration between Google BigQuery and Google Looker Studio (formerly called Google Data Studio) to simplify access to your data. 

You’ll be able to access, analyze and visualize millions of rows of data in a simple way allowing you to leverage the tools and techniques you are already familiar with such as formulas, charts and pivot tables while maintaining the data current through scheduled refresh options. 

Note: ActivConnect is available as an add-on to customers on a paid subscription plan. Learn more about ActivConnect here.

Contents

Pre-Setup Requirements

  • ActivTrak paid subscription plan and the ActivTrak ActivConnect Add-on. Learn more here.
  • ActivConnect user name and password will be in the format of [account_number]@activtrak.us]

Note: While this template is designed to work with all ActivTrak subscription plans, the data available for each report will vary based on your specific subscription plan type. Reports containing the Premium or Professional label on the report title indicate that the data required for those reports are only available to those license tiers. 

Pages 8 - 16 contain sample data as screenshots and require a Premium or Professional ActivTrak subscription. If you have any of these subscriptions delete the sample image before completing the configuration.

Initial Setup Steps

Validate ActivConnect credentials: 

NOTE: This step is only required for new customers and to validate access to ActivConnect.

  1. Navigate to https://console.cloud.google.com 
  2. Enter your [accountnumber]@activtrak.us email address
  3. Enter your temporary password (you may be prompted to change it)
  4. Move to the next steps if you are able to log in.

Choose_account.png

Take template ownership

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

GLS-1.png

  • 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_id@activtrak.us. Your permissions will look like the image below.

GLS-2.png

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_id@activtrak.us. Then, navigate to https://lookerstudio.google.com
  • Select the Shared with Me tab from the left navigation menu and open your copy of the ActivTrak for Looker Studio Master report.

GLS-3.png

  • Enter Edit Mode by clicking the blue Edit button.

GLS-4.png

  • Click the Add Data button from the menu options
    Note: This process will repeat three times, for each data type required for this report
    • Productivity Data
    • Application Data
    • Environment Health

GLS-5.png

Productivity Data: 

  • Select BigQuery from the list of data connectors

BigQuery.png

  • Select Custom Query 
  • Select us-activtrak-ac-prod as the billing project
  • In the Custom Query editor, copy and paste the query provided below 
  • Replace xxxxx with your account number. This needs to be done in Lines 10, 23 and 27.
  • Click Add when done

GLS-6.png

------------------ Productivity Data Query Start --------------------

SELECT

  dus.*,

  ug.primary_group_name

FROM (

  SELECT

    user_name,

    group_name AS primary_group_name,

    user_id

  FROM

    `us-activtrak-ac-prod.xxxxxx.premium_user_groups`

  WHERE

    user_name NOT IN ('SYSTEM',

      '')

    AND is_primary_group IS TRUE

    AND CONCAT(user_name,user_id) IN (

    SELECT

      CONCAT(user_name, user_id)

    FROM (

      SELECT

        user_name,

        MAX(user_id) AS user_id

      FROM

        `us-activtrak-ac-prod.xxxxxx.premium_user_groups`

      GROUP BY

        1)) ) ug

LEFT JOIN

  `us-activtrak-ac-prod.xxxxxx.daily_user_summary` dus

ON

  ug.user_name = dus.user_name

------------------ Productivity Data Query End --------------------

Best Practice: BigQuery Custom SQL connections will be named as such, we advise renaming the data source once added in order to better identify it while configuring the rest of the report. The following steps will guide you through that process.

  • Navigate to Resources > Manage added data sources
  • Edit your newly-created data source. The default name for Custom SQL connections is BigQuery Custom SQL
  • Double click on the connection name and give it a friendly name, in this case, we like to call this Productivity Data

  • Click Done and Close when complete

GLS-7.png

Repeat this process when adding additional data sources. 

Application Data: 

  • Click the Add Data button from the menu options
  • Select BigQuery from the list of data connectors 

BigQuery.png

  • Select My Projects and select us-activtrak-ac-prod as the billing project
  • Select your account number under data sets
  • Select daily_application_summary from the list of available tables
  • Click Add when done

GLS-8.png

 

Environment Health: 

  • Select BigQuery from the list of data connectors 
  • Select Custom Query and select us-activtrak-ac-prod as the billing project
  • In the Custom Query editor, copy and paste the query provided below 
  • Replace xxxxx with your account number. This needs to be done in Lines 13 and 15.
  • Click Add when done

------------------ Health Data Query Start --------------------

 

SELECT

  app.user_name,

  comp.computer_name,

  MIN(DATE_DIFF(CURRENT_DATE(), app.local_date, day)) AS days_since_last_activity,

  MAX(app.local_date) AS last_active_date,

  CASE

    WHEN MIN(DATE_DIFF(CURRENT_DATE(), app.local_date, day)) <= 15 THEN 'Healthy'

    else 'Unhealthy'

END

  AS health_status,

  app.accountid

FROM

  `us-activtrak-ac-prod.xxxxxx.daily_application_summary` AS app

JOIN

  `us-activtrak-ac-prod.xxxxxx.premium_computer_groups` AS comp

ON

  app.computer_id = comp.computer_id

WHERE

  app.local_date >= DATE_SUB(DATE(CURRENT_DATE()), INTERVAL 90 day)

GROUP BY

  app.user_name,

  app.computer_id,

  comp.computer_name,

  App.accountid

------------------ Health Data Query End --------------------

 Assign productivity data to reports

  • From the main menu navigate to File > Report Settings
    • Select the Productivity Data as the data source
    • Add local_date to the Date range dimension

report_settings.png

The report setting should look like the image below.

report_settings_2.png

Assign application data to reports

This process needs to be repeated for pages 3-6:

  • Technology Usage
  • Technology Usage - Adoption
  • Technology Top Charts
  • Application & Sites Usage Report
  • Navigate to the desired page from the list above
  • Change the individual page setting by navigating to Page > Current page settings

current_page_setting.png

  • Change the data source to the Application Data data source. The default name is daily_application_summary
  • Repeat this process for the rest of the pages. 

The page setting should look like the image below.

settings.png

Personal Insights: Technology usage visual

The Personal Insights dashboard contains visualizations from multiple data sources. This will cause one of the graphs to not load properly with the default configuration. Follow these steps to correct this issue: 

  • Navigate to Page 13, Personal Insights
  • Select the Technology Usage visual on the report. 
  • Change the data source to your Application Data or daily_application_summary 

Environment Health

This process only applies to Page 17 - Environment Health:

  • Navigate to the Environment Health Report
  • Change the individual page setting by navigating to Page > Current page settings  

current_page_setting.png

  • Change the data source to your Health Data data source
  • Remove the Date Range Dimension 

The current page settings should look like the screenshot below.

GLS-9.png

  • Enter the View mode to start using your report.
  • You also have multiple options to start sharing the content across your organization.

GLS-10.png

You can now revert to the original Google account for continued access and analysis of the data.

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

GLS-11.png

  • Once you complete your desired configuration, add the individuals you want to share the report with and their appropriate permissions.

GLS-12.png

Scheduling Reports

To configure a scheduled delivery of a report, click on the drop-down arrow next to the view/share tab and select Schedule email delivery, as shown in the image below.

GLS-13.png

  • 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. 

GLS-14.png

Click Schedule when finished.

Learn more:

 

 

Was this article helpful?

0 out of 1 found this helpful

Comments

No comments