Data-Driven Growth: How Global Companies Excel with Serpstat, BigQuery and Looker Studio — Serpstat Blog

Businesses are always looking for better ways to understand data and make smarter decisions. An effective approach is using multiple platforms together to analyze data.

By combining tools like Serpstat, BigQuery, and Looker Studio (ex Data Studio), you can dig deeper into insightful data to find trends and patterns you might have missed before. We’ll show an example of how you can use these tools to create a dashboard that’ll help you market a business.

Why combine Serpstat with BigQuery and Looker Studio?

Using multiple platforms for data analysis allows you to transform raw data into insights for website optimization and SEO strategy. For example:

Here’s more to read on this topic:

The theory is clear – let’s practice!

We’ll show real-life use cases on how to maximize insights and benefits by combining Serpstat, BigQuery, and Looker Studio. 

The aim of the use cases was to optimize an e-commerce global business for several countries.

Before we go to the use cases, let’s go through all steps we did to get an informative dashboard.

Step #1: Collect data

You can do the same with any data (domains, keywords, backlinks) or altogether. We show the case with keyword analysis. A helpful way to collect many keywords is to use Keyword batch analysis. The tool replaces several reports at once and allows to build a table with data, store and update it.

Let’s create a project:

The keyword collection may take some time, depending on the size of the entered keyword list.

As a result, you have keywords within a specific industry, with SEO metrics, for several countries and in different forms of variations.

If there are the “N/A” indicators – that means a keyword isn’t in our database. Hover your cursor to see the explanation of “N/A”. Add keywords to our database to get the data (it’s free!).

Within a project, you’ll get fresh data almost immediately. In Keyword research reports, the information will be available within a couple of days.

Some keywords may still have “N/A” instead of metrics because of the extremely low search volume. Hover the cursor to see the reason why a keyword wasn’t added.

After adding keywords, update the search volume. Upon this operation, you’ll get the search volume history for the last 12 months when you export the results.

Before your next step, make sure you have only the necessary keywords. Filter out those you want to exclude and delete them through Operations.

Step #2: Turn data into insights in BigQuery

Now your list is ready. Be sure you enabled all the necessary metrics in the report in the “Columns” box and exported them to BigQuery.

Now your list is ready. Be sure you enabled all the necessary metrics in the report in the “Columns” box and exported them to BigQuery.

After the export is finished, you get the table with keyword data in the BigQuery:

You can enrich this data with external dimensions and metrics. We categorized keywords based on search intent using BigQuery and Vertex AI. Note that both are paid services with a free tier, so look through their pricing ****** before running any SQL queries.

Let’s assume you use the default export dataset ‘serpstat_export’. To categorize keywords using PaLM 2 for Text model, go to BigQuery to create an external Vertex AI connection, grant permissions to the connection’s service account, and use this SQL query:

# Create a temporary table with keywords
CREATE TEMP TABLE RAW_DATA AS (
  SELECT
    keywords
  FROM
    serpstat_reports.{serpstat_data_table_name}
  LIMIT 1 # Remove this row to categorize all the keywords
);

# Create a remote model connected to Vertex AI large language model
CREATE OR REPLACE MODEL
  serpstat_reports.intent_llm_model 
REMOTE WITH CONNECTION
 `{vertex_ai_connection_name}` 
OPTIONS (ENDPOINT = 'text-bison'); # This specifies the AI model

# Get the intent for each keyword 
SELECT
  keywords,
  ml_generate_text_llm_result
FROM
  ML.GENERATE_TEXT( MODEL serpstat_reports.intent_llm_model,
    (
    SELECT
      CONCAT( 'Classify the intent of the following text as informational or transactional. Return only intent without any explanations. Text: ', keywords) AS prompt,
      keywords
    FROM
      RAW_DATA ),
    STRUCT( 0.1 AS temperature,
      1000 AS max_output_tokens,
      0.1 AS top_p,
      10 AS top_k,
      TRUE AS flatten_json_output));

Now that we have all the required data, it’s time to create a Looker Studio dashboard and make data-driven decisions.

Step #3: Time to act! Plan website optimization in Looker Studio

We mentioned the use cases, so it’s time to cut to the chase! We solved six tasks in the Looker Studio dashboard. Use the following use cases as references and apply the same strategy or new ideas to your business.

We’ve indicated the filters and metrics used for each case. Some dashboard metrics were calculated by a formula using data extracted from the Serpstat interface. They were:

• EN volume. To calculate it, we added up the keyword search volumes from the US, UK, and CA:

IF(Google US (volume) IS NULL, 0, Google US (volume)) + IF(Google UK (volume) IS NULL, 0, Google UK (volume)) + IF(Google CA (volume) IS NULL, 0, Google CA (volume))

Calculate EN volume to estimate approximate traffic from English-speaking countries.

• Budget CPC. To calculate it, we multiplied Google US volume by Google US CPC/4. 

Estimate an approximate budget for ad campaigns. You can repeat this formula for each country.

Feel free to contact us, and we’ll help you solve any use cases.

And here are the use cases, adjust them to meet your needs or just follow to practice your skills!

Promote a product category

1) Filter keywords by a specific topic and metrics:

• Enter a thematic word in ‘Keyword’
• Set medium and large volume (the number depends on an industry, and our indicator is >100)

Now you have the list of thematic keywords without too low-volume keywords. Let’s use the list to create ad campaigns.

2) Run ads to increase conversions:

• Select the transactional intent
• Set keyword difficulty (we set the high difficulty to get traffic by popular keywords to rank for)
• Filter CPC that fits your budget
• Enter branded words in ‘keyword contains’ to get competitors’ traffic in paid search (we entered “amazon”, but there were no keywords by the metrics set)
• Sort volume in descending order (to start ads for best keywords first)

After using the list above, keep the thematic keyword (“clothes” in our case) and the volume filter. We’ll continue to optimize pages for the same product category.

3) Optimize informational pages to get more traffic and increase brand awareness:

• Select the informational intent
  
• Set keyword difficulty (we chose low and medium difficulty)
• Keep volume in descending order (to optimize pages with high-volume keywords first)

Great! You’ve got the most reliable keywords to optimize your pages. Add them to product descriptions, headers, alt tags, etc.

Optimize meta tags for a category of product

1) Filter the most important keywords for SEO:

• Enter a thematic word in ‘keyword’
  
• Set keyword difficulty (we chose low and medium difficulty)
• Select a transactional or informational intent (it depends on the very pages for which you create metatags)
• Keyword length (long-tail keywords may be tricky to use for Title, but consider disabling it when creating Description)
• Set volume to exclude low-volume keywords
• Set keyword difficulty (we set <=80 to exclude keywords that competitors use often)

Add these keywords to the Title meta tag.

2) Prepare the list for the Description optimization:

• Set Keyword length >= 2 (here you can consider long-tail keywords for Description)
  

Increase the local coverage (offline&online)

1) Find out potential locations to expand offline business:

• Filter keywords with toponyms and check the volume to estimate demand
  

2) Check whether you operate for all the locations online:

• Filter keywords with toponyms and check the volume to estimate traffic
  
• Create an FAQ on a website, provide details on delivery and additional information for the specific location

Create a content plan for a product category

1) Filter keywords by a specific topic:

• Enter a thematic word in ‘Keyword’
  

2) Assess the monthly search volume:

• Check the monthly volume trends on the graph
  

Consider creating more thematic content in specific months, launch pop-ups for popular products, and send newsletters to provide actual services.

Estimate PPC budget for high-demand products

1) Filter keywords by a specific topic:

• Enter a thematic word in ‘Keyword’
  

2) Run ads when a product has the highest demand:

• Check the monthly volume trend on the graph
  

3) Repeat steps 1), 2) to estimate the PPC budget for different product categories.

Create an informational hub to increase traffic

1) Filter keywords by a specific topic:

• Enter a thematic word in ‘Keyword’

2) Estimate the maximum potential traffic if you add all these to your website:

• Check the upper blue search volume box

3) Create a knowledge base about your products:

• Select an informational intent
  
• Set search volume or sort volume in descending order to get the maximum results

Highlight the ideas for your content plan.

Add these keywords to a website and track rankings in SERP with Rank Tracker.

Conclusion

So, there you have it — combining Serpstat, BigQuery, and Looker Studio is just one variant for creating an insightful dashboard and increasing your website’s potential. It might seem a bit tricky at first, but each step you take gets you closer to success.
As we’ve dived into hands-on examples, we’ve uncovered a clearer path toward smarter decisions. From gathering data to crafting a dashboard, each step brings you closer to finding out new insights and leveraging them for website optimization.