How to Integrate Retool with Serpstat

Navigate to the Resources tab in your Retool instance and click Add Resource. Select REST API from the list of resource types. Name the resource 'Serpstat API'. In the Base URL field, enter https://api.serpstat.com/v4. This is the current Serpstat REST API v4 base URL. All endpoint paths will be appended to this base URL. Serpstat authenticates API requests using an API token passed as a URL query parameter named token. Rather than adding it to every individual query, configure it as a default URL parameter at the resource level. In the URL Parameters section, click Add Parameter. Set the parameter name to token and the value to your Serpstat API token, which you can find in Serpstat's account settings under Settings → API. For better security, store the token as a configuration variable in Retool. Go to Settings → Configuration Variables, create a variable named SERPSTAT_API_TOKEN, mark it as Secret, and paste your token. Then in the resource's URL parameter value field, use {{ retoolContext.configVars.SERPSTAT_API_TOKEN }} instead of the raw token. Serpstat's API returns JSON responses. Add a default header: Accept with value application/json to ensure consistent JSON formatting in responses. Leave all other settings at defaults and click Save Changes.

Create your first Serpstat query to retrieve keyword rankings for a domain. In your Retool app, open the Code panel and click + to add a query. Name it getDomainKeywords and select the Serpstat API resource. Set Method to GET. In the Path field, enter /domain-keywords. Add the following URL parameters: - domain: {{ textInput_domain.value }} (the domain to analyze, without protocol, e.g., example.com) - se: g_us (search engine and country — g_us for Google US, g_uk for Google UK, etc.) - page: 1 - size: 100 (number of keywords per page, maximum typically 1000) - sort: traff (sort by traffic estimate, descending) Serpstat returns JSON with a data object containing a result array. Each result item includes: keyword, position (current ranking position), cost (CPC estimate), concurrency (competition score), traff (estimated traffic), features (SERP features present), url (the ranking URL), and previous_pos (previous position for comparison). Add a JavaScript transformer to format the response: Bind the transformed results to a Table component. Add a Text Input component for domain entry. Connect its onChange event to trigger getDomainKeywords so the table updates when the domain input changes. For the summary statistics view, create a query named getDomainSummary: - Path: /domain-summary - URL parameters: domain = {{ textInput_domain.value }}, se = g_us This returns aggregate metrics: total keyword count, estimated monthly traffic, visibility score, and ad keywords count. Display these in Stat components above the keyword table.

1// JavaScript transformer — format Serpstat keyword rankings for display
2const keywords = (data.data?.result || []);
3
4return keywords.map(kw => {
5  const current = parseInt(kw.position) || 0;
6  const previous = parseInt(kw.previous_pos) || current;
7  const change = previous - current; // positive = moved up (improved)
8
9  return {
10    keyword: kw.keyword || 'N/A',
11    position: current || 'Not ranked',
12    previous_position: previous || 'N/A',
13    position_change: change !== 0
14      ? (change > 0 ? `▲ ${change}` : `▼ ${Math.abs(change)}`)
15      : '—',
16    change_direction: change > 0 ? 'improved'
17      : change < 0 ? 'declined' : 'stable',
18    search_volume: parseInt(kw.concurrency || 0) || 0,
19    traffic_estimate: parseInt(kw.traff || 0) || 0,
20    cpc: kw.cost ? `$${parseFloat(kw.cost).toFixed(2)}` : 'N/A',
21    ranking_url: kw.url || 'N/A',
22    serp_features: (kw.features || []).join(', ') || 'None',
23    has_featured_snippet: (kw.features || []).includes('featured_snippet')
24  };
25}).sort((a, b) => b.traffic_estimate - a.traffic_estimate);

Create queries to analyze SERP competition for specific keywords and compare your domain against competitors. Create a query named getSerpCompetitors: - Path: /serp-competitors - URL parameters: - keyword: {{ textInput_keyword.value }} - se: g_us - size: 10 This returns the top 10 ranking domains for the specified keyword with their position, domain, title, description, and SERP features they occupy. Create a second query named getDomainCompetitors: - Path: /domain-competitors - URL parameters: - domain: {{ textInput_domain.value }} - se: g_us - page: 1 - size: 20 This returns the domains with the most keyword overlap with your domain — your true organic competitors in the SERPs. Each result includes the competitor domain, overlap count (keywords where both domains appear), and the competitor's estimated traffic. Bind getDomainCompetitors to a Table named table_competitors. When a competitor row is selected, trigger getDomainKeywords for the competitor domain to compare their ranking keyword set with yours. Add a Chart component showing the traffic overlap between your domain and the selected competitor. For keyword gap analysis, create getKeywordGap: - Path: /domain-keywords - URL parameters for the competitor domain, then use a JavaScript query to find keywords in competitor results not present in your domain results Display the gap keywords sorted by traffic estimate — these are your highest-priority content opportunities.

1// JavaScript query — find keyword gap between your domain and competitor
2const yourKeywords = new Set(
3  (getDomainKeywords.data?.map(k => k.keyword) || [])
4);
5
6const competitorKeywords = (getCompetitorKeywords.data?.data?.result || []);
7
8const gapKeywords = competitorKeywords
9  .filter(kw => !yourKeywords.has(kw.keyword))
10  .filter(kw => parseInt(kw.position) <= 10) // competitor ranks in top 10
11  .map(kw => ({
12    keyword: kw.keyword || 'N/A',
13    competitor_position: parseInt(kw.position) || 0,
14    your_position: 'Not ranking',
15    traffic_estimate: parseInt(kw.traff || 0),
16    cpc: kw.cost ? `$${parseFloat(kw.cost).toFixed(2)}` : 'N/A',
17    opportunity_score: parseInt(kw.traff || 0) + (11 - parseInt(kw.position || 11))
18  }))
19  .sort((a, b) => b.opportunity_score - a.opportunity_score);
20
21return {
22  total_gap_keywords: gapKeywords.length,
23  top_opportunities: gapKeywords.slice(0, 50)
24};

The most valuable use of the Serpstat API in Retool is building a historical rank tracker that stores daily position data and shows trends over time. This requires a database table for storing snapshots and a Retool Workflow to automate daily imports. First, create a table in your connected database (PostgreSQL or Retool Database). The schema needs: id, domain, keyword, position, previous_position, traffic_estimate, serp_features, ranking_url, recorded_date. In Retool, create a Workflow with a Schedule trigger set to run daily at 7 AM. Add a Resource Query block connected to the Serpstat API resource (getDomainKeywords query). Add a second Resource Query block connected to your database with an INSERT statement that writes each keyword's position data with the current date. In your dashboard Retool app, create a getPositionHistory query: - SQL: SELECT keyword, position, recorded_date FROM rank_tracking WHERE domain = '{{ textInput_domain.value }}' AND keyword = '{{ table_keywords.selectedRow.keyword }}' AND recorded_date >= CURRENT_DATE - INTERVAL '30 days' ORDER BY recorded_date ASC Bind this to a Line Chart component with recorded_date on the x-axis and position on the y-axis (note: lower position = better ranking, so invert the axis). When a keyword row is selected in the main table, getPositionHistory runs automatically to show the 30-day trend. Add a separate alert view — a Table showing all keywords where position changed by 5 or more places since last week — that loads on page open as the daily SEO briefing. For complex multi-domain rank tracking pipelines with automated client reporting, RapidDev's team can help architect a scalable SEO monitoring infrastructure.

1-- SQL: create rank tracking table
2CREATE TABLE IF NOT EXISTS rank_tracking (
3  id SERIAL PRIMARY KEY,
4  domain TEXT NOT NULL,
5  keyword TEXT NOT NULL,
6  position INTEGER,
7  previous_position INTEGER,
8  traffic_estimate INTEGER,
9  serp_features TEXT,
10  ranking_url TEXT,
11  recorded_date DATE NOT NULL DEFAULT CURRENT_DATE
12);
13CREATE INDEX idx_rank_domain_date ON rank_tracking(domain, recorded_date);
14CREATE INDEX idx_rank_keyword ON rank_tracking(keyword);
15
16-- SQL: query position history for trend chart
17SELECT
18  keyword,
19  position,
20  traffic_estimate,
21  recorded_date,
22  LAG(position) OVER (PARTITION BY keyword ORDER BY recorded_date) AS prev_day_position,
23  position - LAG(position) OVER (PARTITION BY keyword ORDER BY recorded_date) AS daily_change
24FROM rank_tracking
25WHERE
26  domain = {{ textInput_domain.value }}
27  AND keyword = {{ table_keywords.selectedRow.keyword }}
28  AND recorded_date >= CURRENT_DATE - INTERVAL '30 days'
29ORDER BY recorded_date ASC;

Create a position change monitoring view that gives the SEO team an at-a-glance briefing on keyword movements every morning. This view reads from your stored rank history database rather than calling the Serpstat API directly. Create a query named getPositionChanges: - SQL: SELECT keyword, current.position AS current_pos, previous.position AS prev_pos, (previous.position - current.position) AS position_change, current.traffic_estimate, current.ranking_url FROM rank_tracking current JOIN rank_tracking previous ON current.domain = previous.domain AND current.keyword = previous.keyword AND previous.recorded_date = CURRENT_DATE - 7 WHERE current.domain = '{{ textInput_domain.value }}' AND current.recorded_date = CURRENT_DATE AND ABS(previous.position - current.position) >= 5 ORDER BY ABS(previous.position - current.position) DESC This query finds all keywords where position changed by 5 or more places in the last 7 days, sorted by the magnitude of change. In your Retool app, split the position changes table into three views using Tab components or filtered Tables: 1. Biggest Gainers — keywords that moved up 5+ positions (position_change > 0) 2. Biggest Losers — keywords that dropped 5+ positions (position_change < 0) 3. New Rankings — keywords that weren't ranked previously but now appear in the top 50 Add Stat components showing aggregate counts: number of keywords improved this week, number declined, number stable. Apply red/green conditional formatting to the position change column. For automated alerts, add a Retool Workflow that runs daily after the ranking import and checks if any tracked keywords dropped more than 10 positions. If found, send a Slack notification or email to the SEO team lead using a connected Slack or SendGrid resource in the same Workflow.

1-- SQL: get significant position changes in the last 7 days
2SELECT
3  curr.keyword,
4  curr.position AS current_position,
5  prev.position AS previous_position,
6  (prev.position - curr.position) AS position_change,
7  curr.traffic_estimate,
8  curr.ranking_url,
9  CASE
10    WHEN (prev.position - curr.position) > 0 THEN 'IMPROVED'
11    WHEN (prev.position - curr.position) < 0 THEN 'DECLINED'
12    ELSE 'STABLE'
13  END AS change_type,
14  ABS(prev.position - curr.position) AS change_magnitude
15FROM rank_tracking curr
16JOIN rank_tracking prev
17  ON curr.domain = prev.domain
18  AND curr.keyword = prev.keyword
19  AND prev.recorded_date = CURRENT_DATE - INTERVAL '7 days'
20WHERE
21  curr.domain = {{ textInput_domain.value }}
22  AND curr.recorded_date = CURRENT_DATE
23  AND ABS(prev.position - curr.position) >= 3
24ORDER BY change_magnitude DESC, curr.traffic_estimate DESC
25LIMIT 100;