Data-Driven Growth: How to Excel with Serpstat, BigQuery and Looker Studio

Businesses are always looking for better ways to understand data and make smarter decisions. One of the approaches is to use 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 use case examples of how you can use these tools to create a dashboard that’ll help you market a business. The aim of the use cases was to optimize an e-commerce website for several countries.

Why combine Serpstat with BigQuery and Looker Studio?

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

If you'd like to transfer Serpstat data directly to Looker Studio, you can try out our Looker Studio connector for this.

The theory is clear – let’s practice!

Step #1: Collect data

Let's collect keywords in the Keyword batch analysis tool. It replaces several reports allowing you to collect keywords in bulk. Instead of collecting them separately, you can do it in one place with just a few clicks. This way, you can easily build, store, and update your keyword list.

You can do the same with domains and backlinks in Domain batch analysis or altogether. 

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 variations.

If there are “N/A” indicators instead of all keyword's metrics – that means a keyword isn’t in our database. 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 same 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. Use it to evaluate seasonal trends adjust your strategy accordingly.

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.

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 models 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));

Replace {serpstat_data_table_name} with the keywords data table name and {vertex_ai_connection_name} with the previously created Vertex AI connection name. The query might take a long time, depending on the number of keywords you want to categorize. As a result, you’ll get a table like this:

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 via Looker Studio

We mentioned the use cases, so it’s time to cut to the chase! 

Create a dashboard using the BigQuery Google connector as a data source. You can calculate dashboard metrics by a custom formula using data exported from Serpstat. They were:

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

Calculate EN search volume to estimate an approximate global search traffic.

• Budget CPC. To calculate it, multiply Google US volume by Google US CPC/4.
  4 represents an estimated Click-Through Rate (CTR) of 25% from ads, as you can't capture all clicks to your ad only. Feel free to use a different number if you have one that's more relevant to your situation. 

Google US (CPC)*Google US (volume)/4

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

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 topic-related keyword
• Set medium to large volume (>100)

Now you have the list of thematic keywords without low-volume keywords. Use the results to create ad campaigns.

2) Run ads to increase revenue:

• Select the transactional intent
• Set a high keyword difficulty (>41)
• Filter CPC that fits your budget
• Filter by branded words in to get competitors’ traffic in paid search (there may be cases when you enter a word and don't find keywords in a selected range)

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 low to medium keyword difficulty (<40)
• Keep volume in descending order (to get high-volume keywords for optimizing pages 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:

• Filter by topic-related keyword
  
• Set low to medium keyword difficulty (<40)
• Choose the intent (it depends on the very pages for which you create meta tags)
• Keyword length (for example, to use long-tail keywords for Title)
• Set volume to exclude low-volume keywords
• Set low to high keyword difficulty (<=80)

2) Prepare the keyword list for the Description optimization:

• Set keyword length (>= 2)
  

Increase the local coverage (offline & online)

Find out potential locations to expand offline and online presence:

• Filter keywords by country or region and check the volume to estimate demand and search 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.

2) Assess the monthly search volume trends.

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

In many cases trends peak in November and December, because of Black Friday and Christmas. Prepare for a trending season in advance: 

• Start preparing paid campaigns a month in advance.
• Start preparing content three months in advance to publish it when trends rise.

Estimate PPC budget for high-demand products

1) Filter keywords by a specific topic.

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

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.

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

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 check SERP rankings in 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.

If you're interested in similar pipelines, dashboards, or building data solutions on top of Serpstat data, let's chat!