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

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

Pre-Setup Requirements

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

Getting Started

ActivTrak data can be brought into Qlik through ActivConnect powered by BigQuery.

In this guide, we’ll demonstrate the ability to create a Working Hours report utilizing Qlik Cloud & ActivConnect. 

• On the dropdown menu, navigate to + Add New > Data Connection 

• Search for BigQuery and select the Google BigQuery connector from the list of Data Sources

• Ensure User Authentication is selected under the OAuth Mechanism
• Check the User-defined credentials option under the Account properties

Click Authenticate

This will open a Google Authentication prompt. 

• Use your [account_number]@activtrak.us credentials to authenticate.
• Click Continue when prompted.

• Upon successful authentication, an authentication code will be generated. 
• Copy the Authentication Code and return to Qlik to complete the account configuration

• Paste the Authentication Code in the Google BigQuery Credentials configuration and click Verify
• You’ll see the Status change to Successfully Authenticated once completed.

• Scroll down to the Database Properties section and click on Get Catalog
• Select us-activtrak-ac-prod from the catalog list

• Click Test connection and if everything tests successfully, click Create and analyze to start building your ActivTrak reports.

Creating a Working Hours Report

• From the BigQuery Data Manager window, select your Account ID from the Owner dropdown. All the information required for the Working Hours report is contained within the daily_user_summary table.
• Select daily_user_summary  from the table list.
• Click Next when done

Note: Refer to ActivConnect’s Data Glossary to learn more about all the available fields.

• Click load data from the right menu options and click Go to Sheet when completed

• Select Create new analytics from the list of options

• From the properties menu, click +Add to add the required fields.
  For this exercise, we’ll use the following fields: 

Local_date
User_name
First_activity_datetime
Last_activity_datetime
Total_duration_seconds

Additional formatting and data transformation can be done through Qlik’s Advanced Options. 

• Enable Advanced Options. 
• Navigate to the total_duration_seconds measure
• Copy and paste the expression below into the Expression field. This will change the measure from seconds to hours and convert the format to a more user-friendly format. 

=TIME(([total_duration_seconds]/3600),'hh:mm:ss')

• Change the Label to Total Duration (Hrs).

Sorting content by Date

• Navigate to the Properties menu and scroll down to the Presentation option
• Select Sorting and select local_date 
• Select By dimension (Largest to Smallest)
• Click Edit Sheet to exit Edit Mode

Your completed report should look like the screenshot below. 

For additional assistance, please reach out to ActivTrak Support.