Unlock the power of ActivConnect and access your ActivTrak data without requiring advanced SQL or BI knowledge. ActivConnect (Powered by BigQuery) leverages the native integration between Google BigQuery and Tableau to simplify data access.
You’ll be able to access, analyze and visualize millions of rows of data, leveraging the tools and techniques you are already familiar with, such as formulas, charts and pivot tables. The data will be kept up to date through scheduled refreshes.
Note: ActivConnect is available as an add-on for customers on a paid plan. You can learn more about ActivConnect here.
Contents
- Setup requirements
- Validate ActivConnect credentials
- Import your data into the Tableau desktop application
- Learn more
Setup requirements
Before you begin, ensure you have:
- ActivTrak paid plan and the ActivTrak ActivConnect API (Add-on). Learn more here.
- ActivConnect user name (in the format of [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.
- 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 is only available to those license tiers.
Validate ActivConnect Credentials
Note: This step is required for new customers to validate access to ActivConnect
- Navigate to Google Cloud Console
- Log in with your ActivConnect username ([accountnumber]@activtrak.us) and password
Import your data into the Tableau desktop application
- Within the ActivTrak app, navigate to APIs & Integrations > ActivConnect & BI
- Locate the Customizable ActivTrak Reports card and click the download icon next to Tableau
- From the zipfile, open the “ActivTrak for Tableau.twbx” file
- 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'.
- Close the browser window when you are done, and then switch back to Tableau Desktop
- A "Dashboard Unavailable" message will appear, indicating 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. Click Edit Connection inside the error message.
- Select the parameters relevant to your account
- See the image below for examples of where to find this information
- Select the following tables within your dataset in the order mentioned below 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 must be built in Tableau
- Click “New Custom SQL” in the bottom left and paste the query below into the edit box
Reminder: ACCOUNTID-REPLACE with your 6-digit account number and rename the custom SQL view with the name “Health Dashboard”
Note: We use 14 days as a default; this can be customized to meet your needs
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.
- Select the Connection on the top right as either “live” or “extract”. We recommend using “extract” to save the data locally for improved performance.
- Sorting on a few visuals might have been lost. You can revisit those visuals and set the sort type accordingly