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 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 for customers on a paid plan. Learn more about ActivConnect here.
Contents
Pre-setup requirements
- ActivTrak paid plan subscription and the ActivConnect API (Add-on). Learn more here.
- ActivConnect user name and password will be in the format of [account_number]@activtrak.us. Your account number is located in the Account Information menu in ActivTrak. To access it, click the circle icon in the top right corner.
- Valid Qlik Cloud license
Getting started
ActivTrak data can be brought into Qlik through ActivConnect, powered by BigQuery. In this guide, we’ll demonstrate how to create a Working Hours report using 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. 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
- Enable Advanced Options.
- Navigate to the total_duration_seconds metric
- 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.