Microsoft Excel Connector Setup Guide

Customers with the ActivConnect API add-on can connect Excel directly to Google BigQuery, bypassing the need to use BQ or any other visualization tool.

Note: This guide applies only to the Windows OS at this time.

ODBC Driver

The end user will need to download the ODBC driver from the below link which includes 32-bit and 64-bit installers:

ODBC and JDBC drivers for BigQuery  |  Google Cloud

After downloading and installing the MSI, navigate to Control Panel > Windows Tools, as shown below.

Next, look for ODBC Data Sources, either 32bit or 64bit depending on your computer, as shown below.

Click on “System DSN” and select “Google BigQuery”, then “Configure”:

Change Authentication to “User Authentication", then click “Sign In”:

You’ll be prompted to sign in with your ActivConnect credentials (account#@activtrak.us). Once you have successfully logged in, a page will appear confirming you can close the browser window. After logging in, now navigate down to “Catalog (Project)” and “Dataset”:

• Catalog (Project): Select “us-activtrak-ac-prod”
• Dataset: Select your account number
• Click on “Test” to verify the connection will work.

If the test is successful, this popup window will appear:

Once completed, close out ODBC Data Sources and open the Excel application.

Excel

From the application navigation, select Data > New Query > From Other Sources > From ODBC, as shown below.

Change the Data Source Name (DSN) to "Google BigQuery”.

NOTE: If you renamed the DSN during the previous steps, the name here will display differently.

If you know the query you are going to use to narrow down the data being imported into Excel, you can enter it here.

For example:

SELECT * FROM `us-activtrak-ac-prod.ACCOUNT#.daily_user_summary` LIMIT 1000

This is not required, however, if you leave this blank, you will be prompted for more information.

If using the SQL statement option, after you enter a query you’ll be given a preview of the data:

• Click “Load” to load everything into Excel directly.
• Click “Transform Data” to change your query and manipulate the data in a new window.

If you opt not to use a SQL statement, on the next screen you’ll need to select the view you want to pull from. You may need to log into your Activtrak.us account again.

NOTE: You’ll need to filter to the specific project (us-activtrak-ac-prod) and your dataset (your account number).

Select the same options as before (Load and Transform Data).

Once the data is in Excel, to refresh it, simply click on Data > Refresh All.

NOTE: By default, this data will only refresh when the “Refresh All” button is clicked.

Excel does allow for an automatic refresh of the data on the open spreadsheet and can be enabled by clicking on the down arrow on “Refresh All”, as shown below.

Inside Query Properties, check the box for “Refresh data when opening the file”.

NOTE: Enable Fast Data Load will sacrifice UI responsiveness inside Excel to prioritize loading data. This is generally not required unless working with extremely large datasets.

By default, this will pull all the data for the account. To keep your spreadsheet smaller and easier to read, we recommend limiting the time from being pulled by using a WHERE statement.

Learn more:

• ActivConnect API Overview
• Contact Support