Importing into Looker Studio

In this guide we discuss options for bringing results of earned media analytics and exports into Looker Studio.

Currently the Meltwater API connector for Looker Studio supports analytics, but not exports. We are looking to add support for exports in future. In the meantime, there are multiple options for bringing in export results as explained below.

Analytics

You can use the Meltwater API connector listed in Looker Studio’s connector library to bring analytics results into the tool.

Before you start

  • Make sure you have at least one Saved Search in your Meltwater account.
  • Make sure you have access to API analytics features in your package.

Finding the Meltwater API connector

Start by choosing to create a new Data Source in Looker Studio.

You will be shown the library of connectors for Looker Studio. Type “meltwater” into the search box to find the connector.

Click on the connector. We recommend that you immediately replace the default name “Untitled Data Source” with something meaningful for your project.

Authorizing the connector

If you have not used the connector before you will be asked authorize the connector. The connector needs to be authorized so that it can access the Meltwater API and bring data into Looker Studio for you.

Authorize the connector to continue.

Providing your API token

Next you will need to enter your API token. You can read more about your API token on the API Credentials page.

Enter your API token and click Submit to continue.

If you use the connector again in future you will not need to do these steps again, unless you choose to revoke the connector’s access.

Configure your analysis

Now that you have entered your credentials, you can now specify the analysis you’d like to perform and bring into Looker Studio.

Start by choosing the search for your analysis, and the type of analysis you’d like to perform.

When you click Next the connector will ask you for additional parameters, appropriate to the analysis type you’ve asked for.

For example, if you choose the Top Locations analysis, you will be asked for a source and location level.

Select options for the parameters, then click Connect to continue.

When you click Connect you will be shown the schema for the analysis data.

When you create a report or open the Explorer tool in Looker Studio, data will be fetched from the API, consuming an analysis call from your allowance.

Choosing a time period for your analysis

You’ll notice that so far you have not been asked for a time period for the analysis. This is because Looker Studio automatically defaults to the last 28 days for the time period.

To learn more about date range controls in Looker Studio see the help guide.

Refreshing data

Looker Studio will automatically refresh the data for your analysis request every 12 hours. Note that this will consume calls from your API analytics allowance.

Exports

Before you start

Make sure you have an ACTIVE (recurring) or FINISHED (one-time) export available in the API.

Creating a new Data Source

To use export results in Looker Studio you will need to create a Data Source.

At this point you will be asked to choose a connector for your data source. There are a number of options you can choose.

Google Sheets connector

One option is the Google Sheets connector. This connector allows you to choose a Google Sheets document in your Google Drive as a source of data. Using the IMPORTDATA function in Google Sheets you can bring in the results of an API export, then use the Google Sheets connector to bring the data in to Looker Studio. Using this method the data will be refreshed automatically for you.

Start by navigating to the Export Console and sign in with your Meltwater credentials.

Right-click the ‘CSV’ link for the export and copy the URL. You’ll need this in the next step.

Import export data into a Google Sheet

Create a new Google Sheet in Google Drive. This sheet will hold the data from the API before it is imported into Looker Studio.

In cell A1 of the new sheet, enter the following formula:

=IMPORTDATA("<LINK TO CSV COPIED IN LAST STEP>", "\t")

Your cell formula should look like this:

Note that in this formula you are:

  • Giving the URL for your exported data in CSV format
  • Specifying that the delimeter for the data is a tab

Google Sheets will now import the data and will create colmuns based upon the headers in the CSV data:

Configure a new Google Sheets data source

In Looker Studio create a new data source, using the Google Sheets connector.

Select the spreadsheet you created in the previous step, and the worksheet where the data is held.

Once you click CONNECT data from the spreadsheet will be imported into Looker Studio for your analysis.

Refreshing data

If you are running a recurring export and want data to be refreshed in Looker Studio, this method will automatically refresh the data for you.

The IMPORTDATA function in Google Sheets will automatically check the URL you gave for the API export every hour. If there is new data this will be imported for you.

The Google Sheets connector will also refresh data every hour by default.

Therefore without any changes following the steps above will refresh your export data in Looker Studio.

CSV Upload connector

Another option is the the File Upload connector. This connector allows you to select a CSV file for upload.

Note that this connector is very sensitive to encodings and formats. It currently only accepts UTF-8 encoded files delimeted using commas.

The CSV files output by the Meltwater API are not in the exact format this connector requires. This means you will need to:

  1. Download the CSV result of the API export.
  2. Open the file in Excel, Numbers or a similar spreadsheet application.
  3. Export the file as CSV in UTF-8 format.

You can now upload the UTF-8 CSV file using the File Upload connector.

Once you click CONNECT data from the file will be imported into Looker Studio for your analysis.

Supermetrics Custom JSON/CSV/XML connector

Supermetrics offer a connector that allows you to ingest CSV or JSON data from a URL.

Note that Supermetrics charge for use of this connector. You can try the connector through an free 14-day trial.

This connector is described in more detail on the Supermetrics blog.

In the options provided by the connector you will need to specify:

  • For Source URL the url for the JSON formatted API export (you can find this from the Export Console).
  • For JSONPath you can specify where the data records can be found in the JSON data. For Meltwater API exports the path is data.
  • Tick Convert numeric-looking values to numbers and Convert date-looking values to dates so that numbers and dates are imported in the correct format.

Once you click CONNECT data from the file will be imported into Looker Studio for your analysis.