Guides
Start typing to search…

Google Sheets

Prerequisites

  • To connect Google Sheets to Fivetran, you must have Read / Write access to a Google Sheet.

Setup Instructions

How Fivetran connects to Google Sheets depends on which authentication method you choose. Fivetran supports two authentication methods:

RECOMMENDED: User Oauth

The User OAuth authentication method allows Fivetran read-only access to any Google Sheet your user has access to. This is for organizations that do not allow users from other domains to be added to a Google Sheet.

To connect a Google Sheet to Prescient using OAuth:

  1. Open your Google Sheet and select the range that you want added to your destination. You can change your selected range later if needed. Make sure to select all of the relevant columns you want to sync to Prescient.
  2. With the columns selected, go to Data > Named Ranges
  3. In the Named ranges menu, enter a name for your new range (e.g. "PrescientRange") and click Done.
  4. Go back to Fivetran and click Authorize, then Authorize with a Google account that has access to the sheet you wish to connect.
  5. Copy the spreadsheet's URL from your browser's address bar and paste it into the Sheet URL input in Fivetran.
  6. In the Named Range drop-down menu, select a named range.
  7. Click Save & Test. Fivetran will take it from here and sync your Google Sheets data.
NOT RECOMMENDED: Service Account

The Service Account authentication method provides fine-grained control over access to your data. You add a unique Fivetran service account as a read-only user to specific sheets that you want to sync.

NOTE: You must create a new Google Sheets connector for each named range you want to replicate.

  1. Select Authorize Service Account as your authentication method in your connector setup form.
  2. Find the automatically-generated email address and make a note of it. This is the email address of a Google Service Account created for your Fivetran account. You will need it to share your Google Sheet with Fivetran.
  3. Open your Google Sheet and select the range that you want added to your destination. You can change your selected range later if needed. Make sure to select all of the relevant columns you want to sync to Prescient.
  4. With the columns selected, go to Data > Named Ranges
  5. In the Named ranges menu, enter a name for your new range (e.g. "PrescientRange") and click Done.
  6. In the top right corner of your Google Sheet, click Share.
  7. Enter the email address that you found in your connector setup form as shown below and give it View permissions.
  8. Copy the spreadsheet's URL from your browser's address bar and paste it into the Sheet URL input in Fivetran.
  9. Click Find Sheet to check that the Service Account has access to the spreadsheet.
  10. In the Named Range drop-down menu, select a named range.
  11. Click Save & Test. Fivetran will take it from here and sync your Google Sheets data.

Additional Details

How to Connect Google Sheets

Before you start connecting a Google Sheet powered data source, please reach out to your CSM so that they can make sure everything is set up properly. Once you are ready to connect, follow these steps:

  1. Create your google sheet, using the example sheet and following the data requirements below.
  2. Navigate to your organization's connectors page.
  3. Select the data source you'd like to connect and follow the prompts to link your sheet.
    1. Google Sheets powered data sources can be identified by 'Powered by Google Sheets'

Data Requirements

Required columns

  • Date (formatted as YYYY/MM/DD)
  • Campaign Name
  • Campaign ID (avoid underscores; use numbers if possible)
  • Spend (consistent daily spend recommended)
    • Zero Spend: If a campaign has no spend on a given day, entering 0 is preferred but we will assume that spend is 0 if no entry is given.
  • Tactic Type (optional)
    • Metadata that can be used to help with determining availability of halo effects
  • Revenue (optional) - used for reporting purposes
  • Clicks (optional) - used for reporting purposes
  • Impressions (optional) - used for reporting purposes
📘

Example

We have an example sheet available here. Please make a copy and use it to structure your own sheets.

Additional requirements

  • Daily updates are required, including weekends and holidays, ideally before midnight PST. Missing updates can impact the accuracy of performance modeling.
    • If you are in an R&D phase, daily updates may not be needed initially but could be required closer to launch.
  • A minimum of 30 days of historical spend data is required.
    • 90+ days of data is recommended for better accuracy.
    • If a channel contributes less than 2%-5% of total spend, it may have a limited impact on modeling.
  • Each row should represent a campaign's spend on a specific day. There should be no duplicate rows for the same campaign on the same day.
  • All values must be greater than zero. Negative values are not allowed.

Managing Multiple Ad Formats

Prescient can support multiple ad formats within a single sheet if you let us know ahead of time. If you run campaigns with different ad formats, consider these best practices:

  • Label each ad format clearly within your Google Sheet.
  • Separate spend data by ad format (the tactic type column can be used).
  • If necessary, create multiple connectors for different ad formats.

Handling Influencer Upfront Spend

If you need to track influencer upfront spend in Google Sheets, ensure:

  • The data is included in the Spend column with appropriate timestamps.
  • The spend aligns with daily updates.
  • Your setup does not exceed 500 lifetime campaigns per channel.

Handling Linear TV Spend (Delayed Data)

To support effective modeling while accounting for delays in finalized logs, please follow the guidelines below when submitting linear TV data:

  • Daily updates required: Submit data daily including weekends/holidays, ideally before midnight PST. Missing days can affect model accuracy.
    • During R&D, daily updates may be paused but required closer to launch.
  • Historical data needs: Minimum 30 days of spend data required; 90+ days recommended for better accuracy.
    • Low-spend channels (<2–5% of total) may have limited impact.
  • Data structure: One row per campaign per day — no duplicates. All values must be positive; no negative or zero values.
  • Pre-/Post-log flow: Submit weekly pre-logs for the week ahead. Overwrite with post-logs within a 28-day window (or longer via manual refresh).
  • Estimates matter: Pre-log estimates help guide day-to-day modeling and when omitted can influence cross-channel interactions and model training.

How Long Does Setup Take?

Setting up a Google Sheets connector includes validation and review steps. The process may take time depending on engineering availability and priorities. If you have questions, reach out to support for updates on your connector's status.

What happens if we don’t update our marketing spend Google Sheet on weekends?

Our online commerce (DTC, Amazon) models update daily, while our offline models (Retail, Wholesale) update weekly. If you leave weekend spend at $0, the model reads that as no spend, which can temporarily skew Monday/Tuesday outputs. Once you update the weekend data, the model retroactively corrects. Some users mitigate this by having a VA enter weekend estimates or by entering placeholder spend and adjusting on Monday.

Updated 3 months ago