How to Organize a Google Sheet

For the most up-to-date information on how to ensure your Google Sheet is properly organized to work with NinjaCat see the requirements & best practices section of our article Google Sheets Performance & Troubleshooting Documentation.

Connecting a Google Sheets Network

1. In the main navigation bar, navigate to Settings > Data Sources

2. Select the "Add Network" button

3. Search for Google Sheets in the Choose Data Source window and give it a name that will make it easy for you to identify (Example: Google Sheets - your_email_address

4. Select the "Continue With Google" button to select or sign in with the Google Account you would like to connect to NinjaCat

   Note: All Google Sheets that are owned or shared with the Google Account you select will be available to connect to NinjaCat. However, they will not be automatically imported and will require additional setup in NinjaCat's Data Cloud to use.

5. After completing the prompts in the Google Authentication Screen you should now see your Google Sheets Account connected to NinjaCat in your Data Source List

6. Proceed to connect your desired Google Sheets to NinjaCat's Data Cloud following the steps outlined in the Build a Custom Provider Dataset article.

🚧

IMPORTANT NOTICE - LEGACY SETUP OPTIONS BELOW

The items listed below on this page highlight legacy setup options for Google Sheets which are no longer under active developlement or supported. If you have followed the steps above to connect your Google Sheets Account to NinjaCat the next steps for you can be found in the article Build A Custom Provider Dataset

Creating a Google Sheets Mapping

🚧

LEGACY SETUP OPTION

Preserved Legacy Setup Approach for Mappings (Not Recommended, Continue with the steps outlined here):

2. Select the new Google Sheets Network you just created in the previous steps
3. Click on "More Actions" near the top right

Click on "Edit Mapping"

4. Click on the "Add Mapping" button near the top right

5. FILL IN THE NEEDED INFORMATION IN EACH OF THE REQUIRED TABS

INFORMATION Tab

     1. Give the Mapping a distinct name that you will use to identify the data being pulled through it

     2. Scroll through the drop-down menu or use the text window to search for the desired Google Sheet

     3. If your Google spreadsheet file contains multiple tabs of data, select the tab or sheet that contains the specific set of data you need to pull

     4. ONLY IF the data in your Google sheet needs to be filtered before being brought into NinjaCat select the column in the sheet that contains the data you will filter on. If you want to include all rows leave the field at "-- No Filter --" 

     5. ONLY IF you've selected a filter column from your sheet will the "Account ID Type" drop-down menu show up. Select from the drop-down menu what account-specific signifier needs to be matched by data in your sheet in order to get through the filter.

📘

Do you see the "Save" button near the bottom left? DON'T CLICK IT YET! You must follow the instructions to create at least one Dimension and one Metric in this Mapping before clicking Save. That button is the final step in creating the Mapping NOT an incremental "save" step.

DIMENSIONS Tab -- Repeat the instructions below for each dimension you need to pull from your sheet

     1. Click the "Add Dimensions" button near the center or top right

     2. Fill in the needed fields in the "Dimension Editor" pop-up window

• Name - A title for the Dimension that will be visible in the Template Builder
• Header Mapping - Select the actual column from your sheet that contains the right data for the Dimension
• Aggregation Mapping - (If appropriate) The common Dimension set that this data could fall under if being combined with data from other data sources
• Display As - The specific format that the data from the sheet should be represented as

     3. Click the "OK" button near the bottom right of the pop-up window after all the fields are filled

METRICS Tab -- Repeat the instructions below for each dimension you need to pull from your sheet

    1. Click the "Add Metrics" button near the center or top right

    2. Fill in the needed fields in the "Metrics Editor" pop-up window

• Name - A title for the Metric that will be visible in the Template Builder
• Header Mapping - Select the actual column from your sheet that contains the right data for the Metric
• Display As - The specific numeric format that the data from the sheet should be represented as
• Calculate totals as... - For combining metric values choose Summation or Average
• Aggregation Mapping - (If appropriate) The common Metric set that this data could fall under if being combined with data from other data sources

     3. Click the "OK" button near the bottom right of the pop-up window after all the fields are filled

📘

At this point, you can go ahead and click the "Save" button near the bottom left if you are done bringing in your Dimensions and Metrics to your Mapping from your spreadsheet file. Move on to the CONNECTIONS Tab instructions if you'd like to use THIS Mapping to layout a different spreadsheet file whose data is laid out in the same pattern as your initial sheet.

CONNECTIONS Tab -- Please follow the instructions below to use THIS mapping as a pattern to organize other sheets. Keep in mind that any new sheets being mapped must have an identical structure to your original sheet; same column headers, same order, no missing columns, no additional columns.

Connections

🚧

LEGACY SETUP OPTION

    1. Click the "Add Connections" button near the center

    2. Fill in the needed fields in the "Connections Editor" pop-up window

• Unique Name - A specific title for the Connection that will be visible when linking to a NinjaCat Advertiser Account
• Google Spreadsheet - The Google Sheets spreadsheet file you want to organize using this mapping as a pattern
• Google Sheet - The specific tab from the Google Sheets spreadsheet file that contains the data that you want to organize with this mapping and pull into NinjaCat

BACKFILL TAB -- This tab will allow you to reset your data for this specific mapping and do a new backfill by clicking on the "Reset Data and New Backfill" button (which is only accessible from this tab). It will also allow you to select the earliest possible date that you would like to retrieve data from your Google Sheet for use in NinjaCat. By default, if left blank NinjaCat will assume that you want to retrieve data from all dates in your Google Sheet. However, if you would like to adjust this you can select a specific date from the Earliest Reporting Date date selector when initially setting up your Mapping.

After you have initially saved your Mapping, returning to this tab will show some helpful information related to your data:

• Status: This is the data processing status of the Mapping.  Note that this value does not update dynamically. The page needs to be refreshed for this value to be updated.
  • Waiting for Data: No data has been loaded for this mapping
  • Ready: Data is loaded and ready
  • Data Processing: A scheduled sync is in progress. While the sync is in progress, data from the last sync will be used.
  • Fatal Error: There was an error loading data. If data was previously loaded, data from the last sync will still be available.
• Dates Loaded: This is displayed as a date range that represents the valid date range that can be accessed in a template or report for this mapping. Because Google Sheets data is completely replaced with each sync, and not incrementally, the earliest loaded date is set as early as possible. The most useful information in this section is the end range of loaded dates as this tells you when data was last successfully loaded.

📘

Clicking the "Reset Data And New Backfill" button after the first load of data has been backfilled will delete all existing data and do a fresh clean backfill.

ADVANCED TAB -- Once a sheet has been initially set up and saved, this tab will appear upon returning to your mapping. By using this tab you can control how frequently your Google Sheets data is refreshed. If left unadjusted this data will use the recommended connection default refresh schedule of once per day. However, this can be adjusted if desired.

1. From the Refresh Type drop-down select your desired refresh type:

• Connection default: Refresh once a day using the Google Sheets connection default refresh time.
• Once a day: Refresh once a day at a specified Refresh Hour.
  • Selecting this option will show an additional drop-down where you can select your desired Refresh Hour.
• More than once a day: Refresh multiple times per day.
  • Selecting this option will show an additional drop-down where you can select your desired Refresh Interval from the following options:
    • Every 15 Minutes
    • Every 30 Minutes
    • Every Hour
    • Every 2 Hours

📘

The variable "Refresh Interval" is designed to allow you to set a refresh cycle that closely aligns with how frequently the specified Google Sheet used in this mapping is updated. If this sheet is not updated frequently a less frequent refresh interval is recommended.

2. Date Override Format: This field should be left blank unless you require an adjustment to the way NinjaCat will read the date of your data. If required you can adjust the date format based on the documentation here.

3. Click the "Save" button near the bottom left after the INFORMATION, DIMENSIONS, and METRICS, tabs have been completed, and the ADVANCED and BACKFILL tabs have been addressed

Using Google Sheets Data in a Template

🚧

LEGACY SETUP OPTION

2. Add a data widget to the template
3. Click on "Add Data Source" near the top right
4. Click on "Google Sheets" from the list of data sources
5. Select the desired Mapping from the field labeled "Mapping - Select a Mapping" in the "Google Sheets" pop-up window
6. Click the "Close" button near the bottom right of the pop-up window

📘

Once a mapping is selected in the pop-up window, two buttons will now be available below the "Mapping" field. The "View Mapping" button will give you a view of the Dimensions and Metrics available through the Mapping. The "Edit Mapping" button will take you to the Edit Mappings area and allow you to make changes to the Mapping itself. When done you will be returned to the NinjaCat Template builder.