New year, new connector: Custom JSON/CSV

4-MINUTE READ ·  By Supermetrics on January 18 2017.

Many of you have requested us to add data sources, that we don’t yet support. And there there’s a big variety in them: from SEO and A/B testing tools to companies’ own CRM systems as well as public data. While our engineers are working hard on the new integrations, we have to admit we can’t make everything happen overnight.

BUT we really want you to be able to fetch data from whichever data sources you need. That’s why we decided to develop a JSON/CSV connector as the first thing in 2017.

This connector allows you to fetch data from any data source in JSON, CSV or text format and transfer it to Google Sheets.

Benefits of the connector

There are three main ways you can use Custom JSON/CSV connector to your advantage:

1. Cross-referencing with public data like weather and public holidays

You can easily get useful data such as weather, public holidays, population and demographics from public sources via their APIs (for example, one list of publicly available sources can be found here). The data can be used in cross-referencing with your campaign and analytics data in numerous ways.

A few ideas on how you can use this data:

• Import weather data to see how the weather affects your traffic and campaign results
• Fetch city population data to see if there are different usage patterns in big cities vs small towns
• Get national holidays’ dates to help analyze dips and spikes in traffic

2. An “ad-hoc” solution to fetch data from SEO tools, CRM systems and other sources

Another significant benefit of JSON/CSV Connector is that it can act as a quick solution to pull data from multiple data sources, connection to which has not been developed by Supermetrics yet. For instance, you’re running lead generation campaign and want to get the leads from your CRM system to the same Google Sheet where you have your campaign data.

Then, you can ask your developers to export that data from the CRM into a JSON or CSV format and then use our new connector to get that data into your Google Sheets.

The files are stored on your server and can be updated on daily basis, with data in Google Sheets being automatically refreshed (if you set this option). That solves the problem of security – there is no need to store data on the external servers, and you can easily share your Google Sheet reports with your clients and colleagues.

3. Connect to Databases that are not supported by Supermetrics yet

In addition, you can use our new connector in case the Supermetrics Database connector does not support your database type (currently there are 4 types that we support: MySQL, SQL Server, Oracle and Google Cloud SQL). You can export that data in JSON or CSV format and pull it into your Google Sheets via this new connector.

How to use the connector

To access the connector, launch Supermetrics for Google Sheets Add-on and choose “Custom JSON/CSV” from the list of the data sources.

Then, in the query type you should write the URL you want to get data from. Do not forget to choose the type of your data (JSON, CSV or TXT). Additionally you can specify the request and the HTTP headers.

For your convenience, you can also put the URL into any cell of the sheet and reference that cell in the “Type URL” field on the sidebar (e.g. “Type URL =J1”).

After you set all the parameters, simply click “Get Data” button.

Bonus feature: JSON Path

If you only want to get certain values from the JSON response, use our JSONpath extractor on the sidebar. Below is an example of how you can take advantage this option

Let’s say you want to get a particular weather condition (e.g. “Light snow”) for a specific city. First you have to specify the city you want to get data from by constructing the API call. For example, the city we want to get data from is London. The API call for London from wunderground.com (containing your API key in the middle) will look like this:

http://api.wunderground.com/api/11111111111111111/conditions/q/AK/Juneau.json

Without specifying any weather conditions in the JSON Path, the weather data for a particular city in your Google Sheets will look like this, with many more rows of data continuing to the right:

We should construct the path to get the weather condition in the following way (according to the wunderground.com guidelines):

$[‘current_observation’][‘weather’]

After we have specified the JSON Path the result of our request will look like this:

You should find guidelines for constructing paths and data features/parameters on every website you want to get publicly available data from.

You can check whether your path is correct by using JSONPath Online Evaluator, available here. The general guidelines for creating JSON paths are available via this link or by clicking the “?” sign next to the “JSON Path” on the sidebar.

Useful Tip: Copy the result as a formula

In addition we have another great feature to offer – now you can create a particular query as a formula with and copy it to the other cells. Let’s take a look at the above mentioned example with the weather data: say, you want to get the particular weather condition (e.g. “Mostly Sunny”) for each city without having to specify the URL in the sidebar multiple times.

First, we input the URL and the JSON Path on the sidebar to specify the data that we want to get for a particular city. Next, you should choose “Get Data Using Function” from the “Get Data” dropdown menu.

After the query is completed you can copy the result as a Google Sheet formula.

This is how the table looks like after you have copied the first formula. The URL’s were replaced in accordance with the cities for which the weather data had to be received.

Conclusion

We built this custom CSV/JSON connector to enable you get meaningful data from the sources Supermetrics doesn’t yet directly integrate with.

This is just one new feature we’re releasing recently. Q1 has much more to come – stay tuned to see what else we have to offer!

We are interested to hear from you – try our CSV/JSON connector and leave a comment below to tell us how you use it.