Just finished a project that's been on my to-do list forever: a direct connector between Google Looker Studio and the SEMrush API. Most of the third-party connectors I tried were either too rigid, expensive, or didn't surface the exact metrics I wanted in my dashboards.
So I built a lightweight middleware using Google Cloud Functions (Node.js) to handle the API calls, transform the data, and serve it to Looker Studio via Community Connectors. The main goal was to blend SEMrush keyword position tracking with our own Google Analytics 4 data in a single, real-time view.
Here's the core of the API request function for fetching keyword data:
```javascript
async function getSEMrushKeywordData(domain, database, keywords) {
const endpoint = 'https://api.semrush.com/analytics/v1/';
const params = new URLSearchParams({
type: 'phrase_organic',
key: API_KEY,
display_filter: `+|Po|<=|20`,
export_columns: 'Ph,Po,Pp,Pd,Nq,Cp,Ur',
database: database,
domain: domain,
phrase: keywords.join(',')
});
const response = await fetch(`${endpoint}?${params}`);
const data = await response.text();
// Then parse CSV and structure for Looker Studio...
return formattedData;
}
```
**Why bother? A few immediate benefits:**
* **Cost:** No per-user fees from a generic connector platform.
* **Freshness:** Scheduled Cloud Function runs ensure daily data pulls, way better than the weekly updates some tools offer.
* **Custom Metrics:** I could easily calculate and add derived fields like "ranking difficulty score" or "traffic value change WoW" right in the transformation layer.
Has anyone else built custom connectors for their SEO stack? I'm curious about:
* How you're handling rate limiting and caching for SEMrush/Ahrefs/Majestic APIs.
* Whether you've integrated rank tracking data with business metrics (like conversions from GA4/CRM) in Looker Studio.
* If there's a better approach than Cloud Functions for this—considered Apps Script, but it felt less scalable.
The setup isn't trivial, but the control is fantastic. Next, I'm trying to do the same for the Ahrefs API and run a side-by-side on data freshness for the same set of keywords.
--experiment
Prompt engineering is the new debugging.
Nice work building that yourself. Third-party connectors often lock you into their specific data views, and you end up paying a premium for it.
Have you hit any unexpected API limits from SEMrush with this setup? Their pricing tiers can get steep once you scale up the volume of keyword checks, and that's a hidden cost a lot of DIY builds run into.
Blending that position data with GA4 is a smart move for getting the full funnel picture in one place.
get it in writing
Blending position data with GA4 is so clever for the funnel view. Makes me wonder, when you mix them in the same dashboard, how do you handle attribution? Like, if a keyword's ranking improves and you see more conversions, do you have a way to confidently connect the two? Or is it more of a high-level correlation you're tracking?
That's a great question about attribution. Honestly, with this setup, it's mostly high-level correlation for now - you see a ranking improvement in one widget and a lift in GA4 conversions in another, side-by-side. It flags where to start digging.
To get true attribution, you'd need to stitch the session data together, which gets messy fast. Some folks use UTM parameters on their tracked keyword URLs to try and bridge that gap in GA4, but it's not perfect.
Have you found a clean way to connect SEO performance directly to conversion events in your own dashboards? I'm always looking for better approaches on that front.
MartechMatch