Custom - Integrating with Google Sheets

Ninjacat has a very strong integration with Google sheets. Any data that you can get into a Google Sheet can be included as a table in your NinjaCat report. There are many clever and automated ways to get data into a Google Sheet using apps such as Zapier and Supermetrics, opening the possibility to easily incorporate data from sources such as Pinterest, SalesForce, Zillo, etc.

Get started by attaching a Google sheets account in the Networks tab

Connecting a Google Sheets Network

1. Click on your name in the top right corner, then under "Data Sources" click Add Network  and select "Connect" listed under Google Sheets.  (For a 5-minute video demonstrating how to add Google Sheets network, see:  Google Sheets explained: Part 1 - connecting your sheets network)

2. Open another tab in the same browser and log in to the Google Drive/Sheets account you wish to connect. 

3. Go back to your NinjaCat tab and type a unique description to name this Google Sheets connection, then click "Add Network"

4. You will automatically be directed to a Google permissions page to authorize NinjaCat and allow access. Click "Allow":

After you click "Allow", you will be automatically redirected to NinjaCat to continue adding your Google Sheet to your client accounts.  

5. Search for the newly created Google Sheet network under Setup - Data Sources - Google Sheets. Click on the network to add your google sheet connections and header mappings, then click on Edit Mappings, followed by Add Mapping:

6. Under the Information tab, enter in the unique identifier for this google sheet mapping. This is the name that you will be using in your template when you edit your Google Sheet data source.

7. You can add multiple google sheets data sources to an account by giving each sheet a unique Name Identifier.   

8. Click on the "Dimensions" tab to add all of your columns in your google sheet that you wish to use as a dimension. Anything that contains dates or textual data should be added as a dimension. Examples of what should be set up as a dimension are Dates, Campaign Names, Keywords, plus any other information that is not numeric.  (For a demonstration on how to add "Date" dimensions, watch the following 3-minute video: https://share.vidyard.com/watch/xt2nCFhqaRVDsJU1Wj2Kbt )

9.  After all dimensions are added, click on the METRICS tab to map all of the numeric data from your sheet. This could include metrics such as Clicks, Impressions, Conversions, CTR, etc:

10.  (optional) If you have multiple google sheets or multiple tabs that all use the same google sheets headings and metrics, you can add additional google sheets under the "Connections" tab.  Note that if you are adding multiple connections for a single mapping, you MUST add a Google Sheets data source at the account level to specify which connection each account should use.

11.  Click on "Save" to confirm your changes. The next step will be to add the Google Sheets data to your report template. See the following for more details:

1. Adding Google Sheets widgets to shinobi reports (under the tab Reporting - Templates (Beta))
2. Adding Google Sheets widgets to standard reports (under the tab Reporting - Templates)

Google Sheet Requirements and Best Practices

Your Google sheet should adhere to the following requirements:

• Column headings must be in row 1.
• Recommended limit of 10 columns total. This is not a hard/fast limitation, but if you have more than 10 columns the resulting table in your NinjaCat report will start to look very crowded.
• No blank or skipped rows. Your NinjaCat report will include consecutive rows in a Google sheet up until the first row with no content in the first cell/column.
• All "Date" metrics/dimensions must be formatted as a Google Sheet Date.  Any other numerical data where calculations are to be performed must also be formatted as a number, not as a text string.
• Formatting (e.g. color, bold, text alignment) in your Google sheet will not carry through to your NinjaCat report. All cells will be center aligned (standard) or left-aligned (shinobi) unless otherwise specified (see below).
• Cell alignment in tables (standard):
  • You can explicitly control the text alignment of a field in a google sheets table by adding a modifier to the header row:
    • Header|left
    • Header|right
    • Header|center

Getting Data into a Google Sheet

There are many different ways of getting data into Google Sheets, in this example "Pinterest Pins by User Data" was brought in using a Google sheet add-on called Supermetrics. Data can also be imported to sheets using hundreds of Zapier Zaps.

Once you have your data in sheets it will look something like this example below.  

Google Sheet with Pinterest Data: Figure 1

Adding the Google Sheets Widget to Your Shinobi Template
(under the Reporting - Templates (beta) tab) 

See Google Sheets Part 2 - adding Google sheets to your template for a 3-minute video on how to add Google Sheets data to your template.  See below for detailed instructions:

1. Start a new template or edit an existing template under the tab Reporting - Templates (Beta).  
2. Add a data widget to your template or click on an existing data widget to begin configuring your Sheets data.
3. Under "Data Sources", select Google Sheets as a data source. Once added, click on the pencil icon to select your google sheets mapping:
   
   
4. To upgrade an old Google Sheets widget, skip ahead to Step 5. Otherwise, select your google sheet mapping to pick the data you wish to report on, then click close. Skip ahead to step 10.
   
   
   
   
5. To upgrade your google sheets mapping, either click on the "Pick from mappings list" or the "Create New Mappings" list. If creating a new mapping, skip ahead to step 7.
   
6.  Select the mapping you wish to use, then click on the "Use Mapping" button. 
   
   Only AFTER clicking on the "Use Mapping" button, click Close, then skip ahead to step 10.
   
   
7. If creating a new mapping, click on the "Create new mapping" link, then select the Google network that contains the google sheet that you wish to add, and click GO:
   
8.  Add your dimensions and metrics as described in the steps above, give your New Mapping a unique identifier, then click on the "Save" button.
9.  Select your newly created mapping, then click on the USE MAPPING button:
   
10. Once you have selected your mapping by clicking on the Pencil icon next to your Google Sheets data source, you are ready to add in all of your dimensions and metrics to your widget:
    
11. Account Grouping (Optional):To change the grouping of your widget and report on the account group instead of a single account, select "Account Group" from the grouping section.  (See How to report on multiple accounts at once for more information.
    

Adding the Google Sheets Widget to Your Standard Template
(under the Reporting - Templates tab) 

Once your Google Sheets network is established, you can work with the Google Sheets template widget.

1. From the Templates tab the Google Sheets widget can be found under Miscellaneous Widgets.
2. Under the Table section of the widget, select the Network, the Google Sheet, and specify the worksheet tab. You may also optionally specify the data columns to include in the table. If no data columns are specified, all data columns found in your sheet will be included in the table:

Google Sheet Report Template: Figure 2

Once the template is saved and the report is run, the Google sheets data from figure 1 above will look like this in figure 3:

Report Table Containing Pinterest Data from Sheets Integration: Figure 3
 

If the sheet is updated and the report is re-run, the sheets data will update in the report.