Google Drive

Integrate Google Sheets data with marketing metrics in an Insight Graph

Google Sheets integration makes it possible for you to produce a graph with internal company data (e.g., sales, offline advertising, net profit, etc.)  that is not available via third-party integration. That data can easily be blended in a graph with our search engine rank and backlink data, search traffic, social media and email campaign performance, and more.

In this example, we've used Google Sheet data for the Total Sales and Offline Advertising expense, and added Website Sales (from Google Analytics tracking) and AdWords Spend per month (AdWords must be linked to your Google Analytics profile - refer to Link Google Analytics and AdWords).




The first step to graphing data from Google Sheets is to authorize a Google Drive account and connect it to a campaign.




Important! Dates must be entered in the Google Sheet as numbers only (e.g., MM/DD/YYYY) regardless of the display format when viewing Google Sheets. In the above screenshot, Jan 2019 is displaying, but if you look in the fx cell the date is 1/1/2019.


Value Type: select Decimal or Integer








Refer to Metric Display Settings to learn more about colors, chart types, markers, labels and date options.

Series of Metric Widgets



The first step to creating widgets using data from a Google Sheet is to authorize a Google Drive account and connect it to a campaign.

Add Google Sheets Metric

Open Reports & Tools > Custom Suite > Metric Widget, click the Add New Metric button and select:

• Data Source: Other Integrations
• Type: Google Sheets In the Spreadsheet field, copy and paste the URL of the Google Sheet containing the data
   
  
  

Define the Metric Data

Select the Sheet name from the menu (each tab in Google Sheets has a label name, e.g., Sheet1, Sheet 2, or user assigned custom names).

There are two options on how the data to be retrieved from the Google Sheet depending on the Data Range Source selection:

• GS Specified (Google Sheet Specified - where the exact cell location from the Google Sheet to be specified for the Dates Range and Values Range of the current and Previous Period)
  
  
  
  
  • Dates Range - Enter the start cell and end cell that contain the dates, for example for current A6-A9 and for the Previous Period A2-A5 or just one cell as an example in the image below.
  • Values Range - Enter the start cell and end cell that contain the values, for example for current B6-B9 and for the Previous Period B2-B5 or just one cell as an example in the image below.
    
  
  
• Correlated with Report (The dates are matched to the Metric Widget Date Range selected in the report and data dynamically changes when the report data range is changed. Previous Period Date and Value Ranges are not available because they will be automatically retrieved based on the report date range specified). 
  
  
  Note: The Correlated with Report Data Source allows the Marketing Dashboard Metric Widget to change when the months change.


• Dates Range - The recommended selection should be a letter of the column for both start and end cell in order to capture the future populated dates, for example A 
• Values Range - The recommended selection should be a letter of the column for both start and end cell in order to capture the future populated values, for example B
  

Defining Value Type and Value Calculation:

By default Value Type is set to be an integer and there is an option to display the decimal points.
The Value Calculation is set to be Last Value in the specified Values Range. The additional options are to calculate the Average and Sum of the specified Values Range.


Important! Dates must be entered in the Google Sheet as numbers only (e.g., MM/DD/YYYY) regardless of the display format when viewing Google Sheets. 

Example of Google Sheet Specified Data Range Source
Using the following Google Sheet example, the Metric settings in the previous image correspond to a Date Range from cells A2 - A3; February - March 2017, and Regional Sales for Australia from cells C2 - C3.   By default latest date in the series entered determines the current data unless Value Calculation is defined as an Average or a Sum.

Previous Period need to be switched on and the Sheet name, Compare to Date and Compare to Value Ranges have to be specified.

Google Sheet document can be all in one Sheet or you can use different Sheets.



Refer to Metric Display Settings to learn more about color and theme options.


Troubleshooting Google Sheets data in Metric Widgets
If the Google Sheet data does not appear on the screen at all:

• Confirm that the campaign is connected the Google Drive account that the Sheet is located in.
• Confirm that the connected Google account has not exceeded Google's Service Quota. Every time you or anyone you've shared online reports with loads or refreshes a screen, the data is called from Google's API. Therefore, we recommend only using Google Sheets for data that is not available in Rank Ranger via the variety of integrations that we offer.
• If you see a 0 or a flat line at the 0 position for dates that do have data in your Google Sheet, that means that the selected range of values in the metric is incorrect and you need to replace it with the correct column and row references.
• If the Google Sheet that you originally referenced in a metric has been modified in such a way that row numbers or column letters changed, then you need to update each metric previously added in Rank Ranger to match the new data structure (e.g., if you referenced data in Value Range B1:B12 and then inserted a column to the left of column B that changes it to column C, then you need to change the Value Range to C1:C12 in your Rank Ranger metrics).

If after following the above instructions, your Single Metric Widget doesn't display the data correctly from your Google Sheet:

• Share that Google Sheet with [email protected] and
• Send an email to [email protected] with the campaign name and Metric Widget location, for example:
  • Campaign Single Metric Widgets report screen
  • Marketing Dashboard, along with page name and Metric Widget custom title
  • Client Dashboard
  • PDF Report name, along with Metric Widget custom title
• Include screenshots or video of a specific behavior you experienced, if possible

Export Rank Tracking Report to Google Drive

To utilize the export to Google Drive feature, you must be logged into the Google account for the Drive that is connected to the campaign (learn how to authorize connection to Google Drive and connect Google Drive to a campaign).

In the Rank Tracker Dashboard report, hover over the download icon and select CSV to Drive.
 

Rank Report Export Successful

The exported file displays in the browser and can be opened with a variety of tools or closed and viewed later. 

View Rank Report in Google Sheets

In the above example, I chose to open the rank report data in Google Sheets and am now able to work with that data as needed.
 

View Rank Ranger Campaign Data in Google Drive

After exporting a report from Rank Ranger to Google Drive, you will see a folder for each campaign in that Drive.  You can rename those folders and move them to other locations within that same Drive and Rank Ranger will continue to write to that folder when you export additional reports for that same campaign as long as authorized access exists in your Account & Campaign Settings for that Drive. These folders can be shared with others in the same manner you share any folder in Google Drive.

Rank Data Files in Google Drive for 1 Campaign

In this example you see the Jeni's Splendid Ice Creams campaign with 3 files; 2 CSV files of the Dashboard report for each reflecting a different date, and 1 Google Sheet file for the date that I opened and edited the CSV file data using Google Sheets. Like all Google Drive files, these files can be shared with others and downloaded to a local computer drive.

Connect Google Drive with Link Manager for Link-related Documents

Connecting a Google Drive to your Link Manager will allow you to attach files (e.g., invoices, link exchange agreements, etc.) to individual Link records.

• After connecting a Google Drive account to your Rank Ranger account, open the Account Settings > Default Tool Settings screen
• Expand the Link Manager section
• Select a Drive Connection and click the Save button.  When successfully connected, the Google Drive user's name and usage statistics display.
  

Set Default campaign filter in the report:

• Set either to Current Campaign or to All Campaigns (which will set a default display setting for the links in the Report Options)

Add Files to Link Details

After connecting a Google Drive to Link Manager, open the Reports > Organic Search > Link Manager > Links screen and click the Edit (pencil) icon for an individual link.

• Scroll down the screen to the Files & Documents section.
• Click the Choose File button, navigate to and select the file on your computer.
• Click the Upload button

 

Successful File Upload to Google Drive

When files display in the Files & Documents section of a Link Details screen, they have been successfully uploaded to the Google Drive account connected in the Localization & Settings screen. From here authorized users of the Rank Ranger account can download or delete the files.

Google Drive Link Manager Folders

Each individual Link is assigned an ID in the Link Manager and those IDs become the folder names in the Google Drive connected to the Rank Ranger account.  Folders can be shared, renamed and moved to other locations in Google Drive as needed.  Files can be deleted from the Link Details screen by an authorized user* or from Google Drive.