ActivTrak's Tableau Template Setup Guide

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 Tableau 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
• Validate ActivConnect credentials
• Import your data in the Tableau desktop application
• Additional Resources

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]
• An active Tableau license

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. 

Initial Setup Steps

Follow the steps below to configure ActivTrak’s Tableau Template using the Tableau desktop app.

Validate ActivConnect Credentials

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

• • Navigate to https://console.cloud.google.com
  • Enter your [accountnumber]@activtrak.us email address
  • Enter your temporary password (you will be prompted to change it)

Import your data in the Tableau desktop application

Once you have completed the above steps on your Google Cloud Console, visit the ActivTrak website navigation bar “Integrations - Connect”. From there, download the “ActivTrak for Tableau v3.0.zip” file containing the Tableau Template workbook and supporting documentation.

1. Open the workbook “ActivTrak for Tableau v3.0.twbx”.

2. You will be prompted to authenticate using a Google Cloud account. Use the same credentials from Step 1. Click 'Allow to Grant Access to the ActivConnect data from Tableau'.

3. Close the browser window when done and switch back to Tableau Desktop.

Note: A "Dashboard Unavailable" message will appear stating there was a problem connecting to the data source. This occurs because Tableau does not allow you to change the pre-built Data Source Connection before attempting to log in. 

• Close the browser window when done and switch back to Tableau Desktop

Note: A message will appear saying there was a problem connecting to the data source. This is because Tableau does not allow you to change the pre-built Data Source Connection before trying to log in. 

• Click Edit Connection. Inside the error message.

4. Select the parameters relevant to your account. 

• See the image below for examples of where to find this information.

5. Select the following tables in the order mentioned below within your dataset and drag and drop them in the right-hand side area: Drag Tables Here, as shown below.

  daily_user_summary---------------premium_user_groups

• Select user name from daily_user_summary and user name from premium_user_groups when prompted to edit the daily_user_summary -premium_user_groups relationship.
• Close the window when done.

premium_user_groups---------------daily_application_summary

• Select user_id from premium_user_groups and user_id from daily_application_summary when prompted to edit the premium_user_groups-daily_application_summary relationship.
• Close the window when done.

premium_user_groups---------------group_goals

• Select group_id from premium_user_groups and group_id from group_goals when prompted to edit the premium_user_groups-group_goals relationship.
• Close the window when done.

premium_user_groups---------------Health Dashboard

• Health Dashboard is a custom view that is required to be built in Tableau.

• Click on “New Custom SQL” in the bottom left and paste the query below into the edit box.
• Reminder: Replace <@accountID> with your 6-digit account number and rename the custom SQL view with the name “Health Dashboard”.

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)) 
<= <Parameters.Healthy Device Threshold> THEN 'Healthy'

else 'Unhealthy'

END

  AS health_status,

  app.accountid

FROM

  `us-activtrak-ac-prod.<@accountID>.daily_application_summary` AS app

JOIN

  `us-activtrak-ac-prod. <@accountID>.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

• Next, select user name from premium_user_groups and user_name from Health Dashboard when prompted to edit the premium_user_groups-Health Dashboard relationship.
• Close the window when done.

6. Select the Connection on the top right as either “live” or “extract”. We suggest you use “extract” to save the data locally for faster performance.

7. Sorting on a few visuals might have been lost. You can revisit those visuals and set the sort type accordingly.

Learn more:

• ActivConnect Overview
• ActivConnect Data Glossary
• Contact ActivTrak Support