5164 60
Serpstat updates 12 min read February 1, 2024

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

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

Product Marketing Manager at Serpstat

Get free 7-day access

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:

1.Analyze and collect domains, keywords, or whatever your tasks require, in Serpstat. Then, export it to BigQuery.
2.Process data in BigQuery – enrich and transform data in a customized manner and transfer it to Looker Studio.
3.Convert data into informative dashboards, craft reports for your clients, blend metrics, and collaborate with your team in Looker Studio.

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:

1.Enter or import a list of keywords.
You can add two keywords related to online retail and receive a wide range of diverse results. (“buy” and “buy online”).
2.Choose up to 5 search regions.
3.Choose parameters for analysis and select reports to collect more keywords from. Enter “500” in “The number of results” to get as many results as possible. You can get no more than 500 results per keywords (in one search region and in one report), but as we develop the feature, you'll be able to get more.
4.Create a project.
Setting up a Keyword batch analysis project
You spend 1 credit for collecting keywords from 1 report, from 1 search regions and by 1 keyword. For example:
1 keyword * 1 search region * 1 report = 1 credit
10 keywords * 1 search region * 2 reports = 20 credits
As the keyword collection feature is still in beta, the number of credits charged may change as we develop the feature.

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.

Collecting keywords in batch

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.

Getting a fresh keyword search volume

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

Filtering out unnecessary keywords

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.

Export to Google BigQuery
By the way, export to BigQuery is also available for:

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

BigQuery data set

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:

Keywords categorized by search intent

        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.
        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))
        Formula of EN volume

        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
          Formula for CPC budget

          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)
          Keywords for promoting a product category

          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)
          Keywords for running ads

          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)
          Keywords for increasing brand awareness

          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)
          Keywords for Title and Description

          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
          Keywords with toponyms

          Create a content plan for a product category

          1) Filter keywords by a specific topic.

          2) Assess the monthly search volume trends.

          Keywords for a new content plan

          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.

          Keywords for estimate a PPC budget

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

          Calculated PPC budgetApproximate budget for PPCEstimated budget on PPC ads

          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.

          Keywords for an informational hub

          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.

          Keywords for a knowledge base

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

          If you track rankings on another platform, we can help you transfer your project to Serpstat for free!
          Reach out to our support team for help.

          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!

          Start from the very first step!

          7 days for free

          Collect keywords

          Rate the article on a five-point scale

          The article has already been rated by 4 people on average 5 out of 5
          Found an error? Select it and press Ctrl + Enter to tell us

          Share this article with your friends

          Are you sure?

          Introducing Serpstat

          Find out about the main features of the service in a convenient way for you!

          Please send a request, and our specialist will offer you education options: a personal demonstration, a trial period, or materials for self-study and increasing expertise — everything for a comfortable start to work with Serpstat.

          Name

          Email

          Phone

          We are glad of your comment
          I agree to Serpstat`s Privacy Policy.

          Thank you, we have saved your new mailing settings.

          Report a bug

          Cancel
          Open support chat
          mail pocket flipboard Messenger telegramm