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).
Insight Graph displaying data from Google Sheets, 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.
Add Google Sheets Metric
Open an Insight Graph or Metric Widget 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 only the cells range specified for Dates Range and Values Range from the Google Sheet to be displayed in the Insight Graph.)
- Dates Range - Enter the start cell and end cell that contain the dates
- Values Range - Enter the start cell and end cell that contain the values
- Correlated with Report (The dates are matched to the Insight Graph Date Range selected in the report and the data dynamically changes when the report data range is changed.)
- Dates Range - Recommended to specify the column of the dates in order to capture the future populated dates, for example A for the start and A for the end date
- Values Range - Recommended to specify the column of the values in order to capture the future populated values, for example B for the start and B for the end values
Using the following Google Sheet example, the Insight Graph settings in the previous image correspond to a Date Range from cells A2 - A13; January 2019 - December 2019, and Avg Position from cells B2 - B13.
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
Y-Axis Name and Format Settings
By default each metric has its own separate Y Axis with a label matching the Metric Name field.
- If you want the Y Axis name to be different than the metric name, you can enter the label in the Y Axis name field.
- If you want a group of metrics all
plotted on the same Y axis, then enter the same Y Axis name when adding
each metric.
- You can set the Y-Axis data to display in ascending or descending order.
Y-Axis Format Examples
In this example, the Avg Pos metric has a Y-Axis setting of Ascending, and the Clicks metric's Y-Axis is set to Descending to make a visual correlation between the average position and number of clicks.
In this example, the settings are reversed
In this example, the Avg Pos metric is displayed as a line with Y-Axis in Descending order and Clicks are set to Y-Axis Ascending order.
Insight Graph with Shared Y-Axis
In this example, "Percent" was entered in the Y Axis name field of all 4 metrics to cause them all to be graphed on the same scale.
Refer to Metric Display Settings to learn more about colors, chart types, markers, labels and date options.
Troubleshooting Google Sheets data in an Insight Graph
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 Insight Graph doesn't display the data correctly from your Google Sheet:
- Share that Google Sheet with service@rankranger.com and
- Send an email to service@rankranger.com with the campaign name and Insight Graph location, for example:
- Campaign Insight Graph report screen
- Marketing Dashboard, along with page name and Insight Graph custom title
- Client Dashboard
- PDF Report name, along with Insight Graph custom title
- Include screenshots or video of a specific behavior you experienced, if possible
