For data teams
Building an SEO data warehouse
If ranking data has to sit next to revenue, sessions and product data, the platform's own dashboard is the wrong destination. This is the table model, the loading pattern and the honest limits of pulling rank data out of LemRank into a warehouse.
Written by Deepak Shukla, founder of Pearl Lemon, the London SEO agency that builds and operates LemRank · last reviewed 20 September 2026
The table model
| Table | Source | Grain | Columns |
|---|---|---|---|
| projects | GET /projects | One row per tracked site | id, domain, tracking frequency, default language and location |
| keywords | GET /projects/:id/keywords | One row per keyword, current state | keyword, current_position, previous_position, best_position, current_url, search_volume, cpc, difficulty, engine, location, device, updated_at |
| rankings | GET /projects/:id/rankings | One row per keyword per check — the fact table | keyword_id, position, url, checked_at |
| serp_features | GET /projects/:id/snapshots | One row per stored SERP snapshot for a keyword | keyword_id, captured_at, has_ai_overview, has_featured_snippet, has_local_pack |
The loading pattern
1. Create a scoped API key
Keys are created inside the app on the REST API Documentation page. A key reads only the projects its owner owns, so one key per pipeline keeps the blast radius small and makes revocation cheap.
2. Load dimensions, then facts
Pull projects and keywords first; they are small and change slowly, so a full daily reload is simpler than a diff. Then page through rankings with days and offset.
3. Page to the end, every time
Every list response carries total, limit and offset. Keep requesting until offset + returned rows reaches total. A pipeline that reads only the first page silently under-reports and nobody notices for weeks.
4. Respect the rate limit
60 requests per minute per key. Responses carry X-RateLimit-Remaining and X-RateLimit-Reset; a 429 carries Retry-After. Back off on that header rather than a fixed sleep.
5. Deduplicate on load
Treat keyword_id plus the date of checked_at as the natural key for the rankings fact table. Re-running a day's load then updates rather than duplicates, which matters the first time a job half-fails.
What this does not do
- Ranking history through the API covers the last 90 days per request. Longer history has to be accumulated by your own daily loads — start the pipeline before you need the history.
- There is no push or webhook feed for rankings. The pattern is a scheduled pull, not a stream.
- Snapshots return the 50 most recent per keyword and require a keyword_id, so they are fetched per keyword rather than in bulk.
- There is no native BigQuery, Snowflake or Redshift connector. You write the loader, or run the same pull from a scheduled script or an integration platform.
Read the endpoint reference
Full request and response examples, error codes and rate limit headers are on the developer page. If your pipeline needs a scale of keyword volume beyond the standard plans, tell us the numbers and we will quote against them.