Instead of exporting data manually to Google Sheets, you can add your DataHawk-powered BigQuery database as a data source in your Google Sheets document, allowing you to easily get the up-to-date data you need automatically.

Set up

Connecting Datasets

Estimated configuration time of less than <5 minutes.

1. Contact us to get your DataHawk-powered BigQuery database set up
2. Create a Google Sheets file
3. On the navigation menu, click Data > Data connectors > Connect to BigQuery
4. Select the BigQuery project that DataHawk created for you
5. Select the Schema you want so you can access the tables it contains; else, write a SQL query
6. Select the table you want to load entirely; else, write a SQL query
7. Click on Connect
8. Create an Extract

In case you want to load only partial data - such as a specific time frame, or specific columns - instead of the entire table, or merge data from multiple tables, you can write a SQL query as you get familiar with our datasets.

👍

Tip

To show data from your DataHawk-powered BigQuery database right in a Google Sheets document, you can either (a) load an entire table without writing any line of code simply by selecting it, or (b) you can write a SQL query.

Scheduling Updates

To ensure your file contains the most recent data, you can schedule a refresh.

We recommend setting up a daily refresh. There's no need to select a faster frequency as we update data once a day. The fastest frequency you may wanna select is perhaps every 12 hours.

1. Click Schedule refresh
2. Select repeat every 1 day, or repeat every 12 hours
3. Select the time window
4. Click Save

Creating an Extract

If you loaded the data successfully, you're only seeing a preview of the results of your query.

What you want to do next is create an Extract.

Creating an Extract will allow you to

• get the full results of your query, with a limit of 25,000 rows,
• select only specific columns,
• filter based on specific conditions,
• sort data

To create an Extract

1. In your query results, click the Extract button
2. Select whether you want to insert it in a New sheet, or an Existing sheet
3. In the Extract Editor, optionally select the Columns you need, add Filters and Sorting then hit Apply

The Extract is automatically synced from the results of your query.

You can then easily reference the data contained in the extract if needed, such as by using formulas.

Examples

Getting Sales Rank

Sales Rank or Best Sellers Rank data for products that you're tracking on your DataHawk workspace is available in the PRODUCT_SALES_RANK table in the PRODUCT schema.

This table gives you daily BSR data for every single category where a product that you're tracking has ranked.

You can either load the entire table then create an Extract based on specific columns you need and filters, or write a simple SQL query to pull only specific columns you need from it, while optionally applying filters such as on the time range.

While you can select specific columns and use various filters when creating an Extract without having to write a single line of code, you can alternatively elect to write a SQL query to load only parts of a table using specific conditions.

SELECT 
  OBSERVATION_DATE,
  CHANNEL_PRODUCT_ID,
  BROWSE_NODE_NAME,
  RANK,
 FROM `sales-datahawk-bigquery.PRODUCT.product_sales_rank`
  WHERE OBSERVATION_DATE > CURRENT_DATE()-30
 ORDER BY OBSERVATION_DATE DESC

The example above allows you to pull information from only four columns in the PRODUCT_SALES_RANK table and shows data from the past 30 days solely, ordered in descending order by date.

Note how

• We used lower cases when selecting the BigQuery project - _sales-datahawk-bigquer_y in this example
• We used upper cases when selecting the schema - _PRODUCT _in this example
• We used lower cases when selecting the table - product_sales_rank in this example
• We used a backtick ` surrounding the FROM operator

Getting Keywords Ranks

Organic search results data for keywords that you're tracking on your DataHawk workspace is available in the SEO_PRODUCT_ORGANIC_RANK table in the SEO schema.

This table gives you daily organic search results for every single keyword that you're tracking, hence products that appear in the results along with their ranking and some additional information. This means that you don't even need to track a product to get its keyword ranks; you'd just track keywords.

Note that using this table without enough filters will make you easily pull more than 25,000 rows of data, and as such hit the limit of Extracts. We advise creating enough filters or limiting data to products you're tracking, or specific products you want to analyze.

Again, you can either load the entire table then create an Extract based on specific columns you need and filters, or write a simple SQL query to pull only specific columns you need from it, while optionally applying filters such as on the time range.

SELECT 
  OBSERVATION_DATE,
  CHANNEL_PRODUCT_ID,
  KEYWORD,
  PAGE,
  RANK,
 FROM `sales-datahawk-bigquery.SEO.seo_product_organic_rank`
  WHERE OBSERVATION_DATE > CURRENT_DATE()-30
 ORDER BY OBSERVATION_DATE DESC

Merging Tables

Sometimes, you may need to create a table that combines data from multiple other tables.

One way you could do that is by creating individual Extracts for each table on separate tabs, then creating another tab where you would use Excel formulas such as VLOOKUPS, HLOOKUPS, INDEX & MATCH, and more to pull the data you need from each table.

Another way is to directly merge tables via a SQL query.

SELECT 
  RANKS.OBSERVATION_DATE,
  RANKS.CHANNEL_PRODUCT_ID,
  RANKS.KEYWORD,
  RANKS.PAGE,
  RANKS.RANK,
 FROM `sales-datahawk-bigquery.SEO.seo_product_organic_rank` AS RANKS
  INNER JOIN `sales-datahawk-bigquery.REFERENTIAL.referential_product_tracked` AS TRACKING
  ON RANKS.PRODUCT_KEY = TRACKING.PRODUCT_KEY
   WHERE RANKS.OBSERVATION_DATE > CURRENT_DATE()-30
 ORDER BY RANKS.OBSERVATION_DATE DESC

In the example above, we're limiting the search results we're getting from the SEO_PRODUCT_ORGANIC_RANK table to get the rankings of products that we're tracking solely, which we're getting from the table REFERENTIAL_PRODUCT_TRACKED.

SELECT 
  RANKS.OBSERVATION_DATE,
  RANKS.CHANNEL_PRODUCT_ID,
  TAGS.TAG_NAME,
  RANKS.KEYWORD,
  RANKS.PAGE,
  RANKS.RANK,
 FROM `sales-datahawk-bigquery.SEO.seo_product_organic_rank` AS RANKS
  INNER JOIN `sales-datahawk-bigquery.REFERENTIAL.referential_product_tracked` AS TRACKING
  ON RANKS.PRODUCT_KEY = TRACKING.PRODUCT_KEY
  LEFT JOIN `sales-datahawk-bigquery.REFERENTIAL.referential_product_tag` AS TAGS
  ON TRACKING.PRODUCT_KEY = TAGS.PRODUCT_KEY
 WHERE RANKS.OBSERVATION_DATE > CURRENT_DATE()-30
 ORDER BY RANKS.OBSERVATION_DATE DESC

In the example above, we're additionally including information on Tags we may have assigned to the products we're tracking, which can help us with easy segmentation of our results.

Live Demo

You can browse a view-only demo example here.

Limitations

Google Sheets is not built to handle large volumes of data.

For instance, Google Sheets limits you to a maximum limit of 10M cells.

While this limit can be bypassed by using the native BigQuery integration which DataHawk supports, you'll still encounter other limitations, such as

• a limit of 30,000 rows when attempting to create Pivot Tables
• a limit of 25,000 rows or 10 Mb of data when attempting to create Extracts

🚧

Note

A good way to circumvent Google Sheets and BigQuery's limitations when using this integration is to ensure you make proper use of filters. Ideally, you would want to stay under 25,000 rows on the dataset you're working on.

BigQuery Documentation

Use BigQuery data in Google Sheets