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
Contents
ODBC Driver
- The end user will need to download the ODBC driver from the link below, which includes 32-bit and 64-bit installers: ODBC and JDBC drivers for Big Query | Google Cloud
- After downloading and installing the MSI, navigate to Control Panel > Windows Tools, as shown below.
- Next, look for ODBC Data Sources, either 32-bit or 64-bit, 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_number]@activtrak.us). Your account number is in the Account Information menu in ActivTrak; access it via the circle icon in the top right corner. Once you have successfully logged in, a page will appear confirming that you can close the browser window. After logging in, navigate down to Catalog (Project) and Dataset.
- Catalog (Project): Select “us-activtrak-ac-prod”
- Dataset: Select your account number
- Click on Test to verify that the connection will work.
- If the test is successful, this pop-up 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 will 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 in to 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: Enabling 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 range by using a WHERE statement.