CREATE OR REPLACE MODEL
Replace {serpstat_data_table_name} with the name of the keyword data table, and {vertex_ai_connection_name} with the name of the previously created connection to Vertex AI. The query may take quite a specific database while to run, depending on the number of keywords you want to categorize. This will result in a table like this:
Key phrases divided by search intent
Now that you have all the necessary data, it's time to create a Looker Studio dashboard and act on it.
Step 3: Time to Take Action! Plan Your Website Optimization with Looker Studio
We've mentioned use cases, so it's time to get started! Use the use cases below as examples and apply the same strategy or get new ideas for your own business.
Some dashboard metrics can be calculated using a custom formula using data exported from Serpstat. For example:
EN volume. To calculate the total search volume, add up the search volumes of keywords from the US, UK, and Canada.
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 for calculating frequency by English-speaking countries
Calculate "EN volume" to estimate the approximate traffic from English-speaking countries.
Budget CPC. To calculate the CPC budget, we multiplied the Google US volume by the Google US CPC/4.
4 is an approximate CTR of 25%, as you can't get all the clicks on your ad alone.