Harvest to BigQuery: 3 Easy Methods

Time tracking lets you make smart decisions about how you run and price projects, how you spend your day, and how you schedule tasks for your team. An eagle-eye view into your operations will allow you to boost productivity and make sure you’re focusing on the right things to provide the best service for your customers.

So, you got a time-tracking tool in place; Harvest. For analysis, you need to consolidate the data in a central data repository, like a data warehouse. 

You landed on this article, so I’m guessing you’re looking for easy ways to move your data from Harvest to BigQuery. Let’s dive in!

Replicating Data from Harvest to BigQuery

Using REST API to Connect Harvest to BigQuery

You can access data in Harvest through its REST API. Here are some instances of the data you can pull from Harvest using REST API:

• Invoice Data
• Client Data
• Data around Estimates
• Task Data
• Expense Data
• Timesheet Data
• Project Data
• User Data
• Role Data
• Report Data

Let’s take an example of expenses to illustrate how you can use REST API to extract data from Harvest.

You can return a list of time entries with the GET /v2/expenses command. The entries will be sorted by the spent_at date attribute.

Here, the GET command will return an object with an expenses property that has an array of up to per_page expenses. Every entry will be a separate expense object. Harvest includes several supplementary pagination properties to simplify pagination in your expenses.

This is what an example request would look like:

curl "https://api.harvestapp.com/v2/expenses" \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Harvest-Account-Id: $ACCOUNT_ID" \
  -H "User-Agent: MyApp (yourname@example.com)"

And this is what the JSON-format response would look like:

{
  "expenses":[
    {
      "id":15296442,
      "notes":"Lunch with client",
      "total_cost":33.35,
      "units":1.0,
      "is_closed":false,
      "is_locked":true,
      "is_billed":true,
      "locked_reason":"Expense is invoiced.",
      "spent_date":"2017-03-03",
      "created_at":"2017-06-27T15:09:54Z",
      "updated_at":"2017-06-27T16:47:14Z",
      "billable":true,
      "receipt":{
        "url":"https://{ACCOUNT_SUBDOMAIN}.harvestapp.com/expenses/15296442/receipt",
        "file_name":"lunch_receipt.gif",
        "file_size":39410,
        "content_type":"image/gif"
      },
      "user":{
        "id":1782959,
        "name":"Kim Allen"
      },
      "user_assignment":{
        "id":125068553,
        "is_project_manager":true,
        "is_active":true,
        "budget":null,
        "created_at":"2017-06-26T22:32:52Z",
        "updated_at":"2017-06-26T22:32:52Z",
        "hourly_rate":100.0
      },
      "project":{
        "id":14307913,
        "name":"Marketing Website",
        "code":"MW"
      },
      "expense_category":{
        "id":4195926,
        "name":"Meals",
        "unit_price":null,
        "unit_name":null
      },
      "client":{
        "id":5735774,
        "name":"ABC Corp",
        "currency":"USD"
      },
      "invoice":{
        "id":13150403,
        "number":"1001"
      }
    },
    {
      "id":15296423,
      "notes":"Hotel stay for meeting",
      "total_cost":100.0,
      "units":1.0,
      "is_closed":true,
      "is_locked":true,
      "is_billed":false,
      "locked_reason":"The project is locked for this time period.",
      "spent_date":"2017-03-01",
      "created_at":"2017-06-27T15:09:17Z",
      "updated_at":"2017-06-27T16:47:14Z",
      "billable":true,
      "receipt":null,
      "user":{
        "id":1782959,
        "name":"Kim Allen"
      },
      "user_assignment":{
        "id":125068554,
        "is_project_manager":true,
        "is_active":true,
        "budget":null,
        "created_at":"2017-06-26T22:32:52Z",
        "updated_at":"2017-06-26T22:32:52Z",
        "hourly_rate":100.0
      },
      "project":{
        "id":14308069,
        "name":"Online Store - Phase 1",
        "code":"OS1"
      },
      "expense_category":{
        "id":4197501,
        "name":"Lodging",
        "unit_price":null,
        "unit_name":null
      },
      "client":{
        "id":5735776,
        "name":"123 Industries",
        "currency":"EUR"
      },
      "invoice":null
    }
  ],
  "per_page":2000,
  "total_pages":1,
  "total_entries":2,
  "next_page":null,
  "previous_page":null,
  "page":1,
  "links":{
    "first":"https://api.harvestapp.com/v2/expenses?page=1&per_page=2000",
    "next":null,
    "previous":null,
    "last":"https://api.harvestapp.com/v2/expenses?page=1&per_page=2000"
  }
}

Now that you’ve exported your data from Harvest, you can move this JSON data to BigQuery in the following steps:

• Step 1: Open the Google BigQuery page in the Cloud Console and navigate to the Explorer panel.
• Step 2: Click on Project and choose a dataset.
• Step 3: Expand the Actions option and click Open.
• Step 4: Within the Detail Panel of the console, click on the Create Table button to generate a new Google BigQuery table.
• Step 5: Once the Create Table page opens, you’ll be prompted to fill in three fields.
  • In the source field (Create Table From field), choose Google Cloud Storage.
  • Choose the file you want to upload from the Google Cloud Storage bucket.
  • For the File Format field, choose JSON. For Dataset Name, pick the dataset you’re interested in and keep the table type as Native table.
• Step 6: For the schema section, choose Auto-detection. To manually create a schema, click on Add Field to manually input the schema. Click on the Create Table button.

Using CSV Files for Harvest to BigQuery Integration

The data you can export from Harvest depends on your user permissions. There are three different levels of permissions in Harvest: Administrator, Manager, and Member. You can export data as needed through Reports across all permission levels. You can export these reports as CSV, Excel, or PDF files.

• You can export all your company time data and invoices from the Export data section of Settings. If you click on Export all invoices, you’ll get a zip file with PDFs of all invoices. Similarly, if you click on Export all estimates or Export all time, you’ll get a zip file with PDFs of all estimates and company time data respectively.   
• As an administrator and as a manager, you can take a look at all the projects that you’re handling by exporting said projects from the main Projects section. You can even export more detailed project-specific data from a project’s analysis page.

For exporting Reports as CSV files, execute the following steps:

• Click on the Reports tab and select the name of the report you want to export.
• Next, add all the specifications for the report like filters, timeframe, and projects, to name a few.
• Run the report by clicking on the “Run Report” button. Next, click on the “Export” button and choose the format as CSV.

Similarly, you can export data around projects, estimates, contacts, tasks, invoices, etc., as well.

Now that you have your Harvest data in CSV files, you need to move it to BigQuery.

Say you have a dataset named htbdb and there exists a table named htbtable in it. You can use the bq load command to load the CSV data to BigQuery:

bq load htbdb.htbtable htbsource.txt name:string,count:integer

You can check if the table has been populated by running the following command:

bq show htbdb.htbtable

This method is suitable for the following scenarios:

1. Low-Frequency Data Replication: If your finance team needs data from Harvest yearly, quarterly, or once in a while, then it’s recommended that you use CSV files for Harvest to BigQuery migration.
2. Dedicated Personnel: If your organization has dedicated staff who manually select categories, customize templates, and download and upload CSV files, then replicating data from Harvest using CSV files is much simpler.
3. Low Volume of Data: Repeatedly selecting/customizing categories and templates, and downloading/uploading CSV files can be tedious. Merging these CSV files from various departments to measure the business’s performance also takes time. Therefore, this method is best suited for replicating only a few files.

Stop wasting valuable engineering time and resources manually replicating and transforming data from Harvest. With more data sources, the tedious process of creating custom connectors, transforming and processing the data, tracking the flow, and fixing issues can quickly become a major burden. 

To take back your time and start focusing on more productive tasks, you can…

Automate the Data Replication process using a No-Code Tool

Writing custom scripts for each data connector request is not the best or most cost-effective approach. Lack of data flow monitoring, regular breakdowns, and pipeline issues make it very difficult to expand this system.

You can streamline the Harvest BigQuery migration process by opting for an automated tool:

• It helps you focus on engineering goals while your business teams can quickly produce reports without requiring help from you.
• With just a few clicks, your sales & support teams can effortlessly enrich, filter, aggregate, and segment raw Harvest data.
• The user-friendly UI saves engineers time that would have been wasted on data preparation.
• Armed with no-code data replication tools, your analysts can seamlessly create thorough reports for various business verticals to drive better decisions even without any prior coding knowledge. 
• Business teams will have access to near real-time data without sacrificing accuracy and consistency.
• You can get all your analytics-ready data in one place to measure performance and investigate new opportunities.

Let’s take a look at the simplicity a cloud-based ELT tool like Hevo provides to your workflow:

Step 1: Configure Harvest as a Source

Step 2: Configure BigQuery as a Destination

And that’s it! Based on your inputs, Hevo will start replicating data from Harvest to BigQuery.

The Harvest BigQuery integration pipeline will copy new data from Harvest every hour, by default. But if you’d like a higher replication frequency, Hevo allows you to adjust it as you see fit.

Suppose you’d like to dive deeper into how your pipelines are configured for this use case. In that case, you can read the official documentation for configuring Harvest as a source and Google BigQuery as a destination.

What can you hope to achieve by replicating data from Harvest to BigQuery?

Replication of data from Harvest to BigQuery can help you answer the following questions:

1. How much are we spending on each project and client?
2. What’s the total number of billable hours for each client?
3. Can we identify any patterns in client purchases?
4. What’s the average income per client or project?
5. Who are the vital players in a project?

Key Takeaways

You can replicate data from Harvest to BigQuery in three ways — using REST API, CSV files, and third-party data replication tools like Hevo Data. Each method has its own place and is suitable for different scenarios.

Hevo Data allows you to replicate data in near real-time from 150+ sources to the destination of your choice including Snowflake, BigQuery, Redshift, Databricks, and Firebolt, without writing a single line of code. We’d suggest you use this data replication tool for real-time demands like tracking the sales funnel or monitoring your email campaigns. This’ll free up your engineering bandwidth, allowing you to focus on more productive tasks.

visit our website to explore hevo

For rare times things go wrong, Hevo Data ensures zero data loss. To find the root cause of an issue, Hevo Data also lets you monitor your workflow so that you can address the issue before it derails the entire workflow. Add 24*7 customer support to the list, and you get a reliable tool that puts you at the wheel with greater visibility.

If you don’t want SaaS tools with unclear pricing that burn a hole in your pocket, opt for a tool that offers a simple, transparent pricing model. Hevo Data has 3 usage-based pricing plans starting with a free tier, where you can ingest up to 1 million records.

Schedule a demo to see if Hevo would be a good fit for you, today!

References:

• Expenses Harvest API
• Importing and Exporting Data from Harvest