Google Looker Studio

Written By Rankshift

Getting data from your Rankshift.ai account into Looker Studio (or any other BI solution for that matter) happens through the PostgreSQL connector, postgresql is widely supported across many solutions.

Adding data sources

In Looker studio we can add this via the main top menu: Resources →Manage added data sources. Here we can add a data source and choose the official PostgreSQL connector under “Google Connectors”.

Tip: In Looker studio if we need data from more than one table we need to add a connector per table. In that case it is a good idea to go back to Resources →Manage added data sources and rename the connector to the table name so we can easily distinguish them later on.

The data needed to connect to your data is available in your rankshift account under Integration.

Simple Data Sources

3 existing tables will cover the majority of use cases. See the Example reports below for ready-made reports using these data sources.

fact_visibility_day This this table holds all data to show brand visibility scores and auto calculates the percentage based on the filters. Filterable on Date, Scenario, Prompt, Brand, Persona.


fact_share_of_voice_day this table holds all data to show share of voice and auto calculates the percentage. Filterable on Date, Scenario, Prompt, Brand, Persona.


fact_brand_mentions_day this table holds all brand mentions in sources. Filterable on Date, Domain, URL and Brand.

Advanced Data Sources: Joining data from multiple tables: Blends

In many cases we will need to merge data from multiple tables. In looker studio this is called a blend.

Blends can merge data based on the relevant IDs. For example: the brand_mentions table has a field called ‘tracked_term_id’, this field can be joined with the ‘id’ field in the tracked_terms table.

This gives you a lot of freedom in how you want to query your data as we can make very complex data sets.

Example: Showing a count of all brands mentioned in all sources.

In this example we will visualize the count of mentions per brand per source url.

In Rankshift this is shown on the Sources → Pages page in the ‘# mentions’ column.

1. Adding connectors

For this we will need multiple tables/connectors. Add a connector for the following tables:

  • tracked_terms

  • brand_mentions

  • source_analyses

  • source_scrape_request

  • source_source_scrape_request

  • sources

Going to Resource → Manage added data sources in the main menu should show us the following:

Inspecting these tables you will see a link of id’s that can be matched.

  • In brand_mentions we see tracked_term_id and source_analysis_id

  • In source_analysis we see source_scrape_request_id

  • in source_source_scrape_requests we see source_scrape_request_id and source_id

As you might have noticed both source_analysis and source_source_scrape_requests have a field source_scrape_request_id and link to the same source_scrape_request table, so we can take a shortcut and leave out the source_scrape_request table when blending in looker studio.

2. Creating the blend

If we now want to link everything together we create a ‘blend‘.

In the main menu go to Resources → Manage Blends and click ‘ADD A BLEND’.

Here we add the following 5 tables in this exact order by clicking ‘Join another table‘:

  • tracked_terms

  • brand_mentions

  • source_analysis

  • source_source_scrape_requests

  • sources

Under ‘Dimensions‘ and ‘Metrics‘ we add the appropriate ID’s we need to link each table and any fields we want to visualize:

  • For tracked_terms:

    • Dimensions: term and id

  • For brand_mentions:

    • Dimensions: source_analysis_id and tracked_term_id

    • Metrics: count

  • For source_analysis:

    • Dimensions: id and source_scrape_request_id

  • For source_source_scrape_requests:

    • Dimensions: source_scrape_request_id and source_id

  • for sources:

    • Dimensions: id and url

Now between each table we need to tell Looker Studio how to link these two tables together using the join configuration button.

For the join between tracked_terms and brand_mentions this looks like so:

Once all joins are set up our blend should look like this:

We can save and close the blend screen and go back to our Looker Studio report.

3. Creating the report

Now you can use the fields we defined in the blend to make a report, drag the ‘url‘, ‘term‘ and ‘count‘ fields from the right side of the screen together into a table.

Extras:

  • Add filters to remove ‘null‘ values in the table.

  • Edit the blend to add the ‘created_at’ field on source_analysis under ‘Date range’. We can use this in a control to filter our table data based on data collection date.

  • Add a control based on the tracked_terms data source to filter on brand.

Example reports

This advanced report is available publicly here.

The reports based on the Simple Data Sources can be found here and are easily copyable.

Visibility report: https://lookerstudio.google.com/reporting/4624e03b-69cb-4e9e-93a1-2c7e84ac3510

Share of voice report: https://lookerstudio.google.com/reporting/93d9c640-8974-4fdf-b28e-ff50df5f01b1

Source mentions report: https://lookerstudio.google.com/reporting/220742c1-7b19-4328-a3d4-7e65b5b56de8

These reports can be copied and adjusted as you like, be sure to use your own credentials in the connectors to get your own data instead of the demo data.

Remarks and limitations

Looker studio limits the total amounts of tables that can be joined in a blend to a maximum of 5.

To join more data into a single data source:

  • try to optimize as much as possible, we dont always need all tables

  • use Google’s Big Query or similar tools to pull in unlimited amount of tables, use this as a data source in Looker studio.

  • contact us to look for a solution together.