We recently wrote a blog on The Power of Web Queries, a type of scheduled report in MarinOne that is hosted on a URL and automatically updated with the most recent data. These are fully customizable reports, right down to the date range, activity type and even how often the data is refreshed.
The flexible nature of Web Queries means that marketers can automatically import their data directly into Microsoft Excel instead of having to manually download their data and then import into Excel, saving you endless hours of time spent generating reports manually. You can even create dashboards and templates in Excel, which get updated with the most recent data at the click of a button.
The New and Improved Web Query Reports
Since our earlier blog post, we’ve made further enhancements to our Web Query reporting capabilities to not only allow data to be automatically imported into Excel, but now into Google Sheets too.
You’re probably asking why use Google Sheets? What’s the benefit? Well, here’s a few…
- Due to the cloud-based nature of Google Sheets, collaboration between multiple users makes a marketers workflow easier and faster
- Built-in revision history
- No need to constantly press “Save” due to Google Sheets’ auto-save functionality
- Real-time chat window with colleagues
- Access to your Google Sheet and data from any computer/device
- Refreshing of data is automatic on an hourly cadence - no manual intervention needed
- Ability to control access levels to the data, i.e. Read-Only, Edit or Comment access
- Share the data easily with management and stakeholders
- The data can also be synced into big data tools from Google Sheets for enhanced customization and reporting i.e. Google Data Studio
- Pricing – Google Sheets is completely free to use
Setting Up Web Query Reports for Google Sheets
Once you’ve generated your Web Query report from MarinOne, copy the URL and open up a Google Sheet then follow the steps below.
Click into a cell and type =IMPORTHTML(
- This function / formula imports data into a Google Sheet from a table within a HTML page such as Marin’s Web Query reports that are hosted on a URL
The syntax format is =IMPORTHTML("url", "query", index)
- url – The URL of the page to be examined, including protocol (e.g. https://).
This is where you paste the Web Query report URL that you generated in MarinOne - The URL must be enclosed in quotation marks
- query – Either "table" or "list" can be used, depending on what type of structure contains the data
For Marin’s Web Query reports, it will be the query "table", and make sure to also enclose it in quotation marks
- index – The index, starting at 1, which identifies which table or list (as defined in the HTML source) should be returned
For Marin’s Web Query reports, there are three tables to choose from (as shown in the image below)
Your formula should look like the example below. Make sure that each syntax is separated with a comma.
=importhtml("https://one.marinsoftware.com","table",3)
- Once you hit enter, the data will be imported into the Google Sheet from the Web Query report
- Once you have the data into the spreadsheet, you’ll need to set the criteria for the data to be refreshed;Click File >> Spreadsheet settings >> in the pop up, click Calculation >> change the recalculation to ‘On change and every hour’ >> click Save Settings
Google will now automatically refresh the data on an hourly cadence, so you can be sure that the most recent data is up-to-date - There’s no need to manually refresh like you have to in Excel
Why not give it a try and enhance your workflow with our latest update? And if you haven’t already, check our earlier blog on Web Query reports: The Power of Web Queries.
