← back to Rentv Adintel
src/analytics/derive.js
340 lines
'use strict';
/**
* Derived sales-intelligence insights — spec §17 "Sales intelligence derived
* from GA4" and §18 cluster queries.
*
* All functions:
* - query the ga4_* / gsc_* tables directly (aggregates only)
* - never expose user-level data
* - return { rows, formula, demo } where demo:true when all source rows have is_demo=true
* - include a `formula` string documenting derivation (transparent to the UI)
*
* @module src/analytics/derive
*/
const { query } = require('../../db');
// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------
/**
* Check whether all analytics rows in a given table are demo-flagged.
* @param {string} tableName
* @returns {Promise<boolean>}
*/
async function allDemo(tableName) {
const res = await query(
`SELECT bool_and(is_demo) AS all_demo FROM ${tableName} WHERE is_demo IS NOT NULL`
);
return res.rows[0]?.all_demo !== false;
}
// ---------------------------------------------------------------------------
// 1. California & Arizona Audience Concentration
// ---------------------------------------------------------------------------
/**
* Audience concentration by California and Arizona market.
* Aggregates ga4_geo_metrics by region, ranks CA and AZ cities by sessions.
*
* Formula: SUM(sessions) GROUP BY region, city WHERE country = 'United States'
* → percentage of total = city_sessions / total_sessions * 100
*
* @returns {Promise<{ rows: object[], formula: string, demo: boolean }>}
*/
async function caAzAudienceConcentration() {
const res = await query(`
WITH totals AS (
SELECT SUM(sessions) AS grand_total FROM ga4_geo_metrics WHERE is_demo = true
),
by_city AS (
SELECT
region,
city,
SUM(sessions) AS sessions,
SUM(users) AS users,
bool_and(is_demo) AS is_demo
FROM ga4_geo_metrics
WHERE country = 'United States'
AND region IN ('California', 'Arizona')
GROUP BY region, city
)
SELECT
b.region,
b.city,
b.sessions,
b.users,
ROUND(b.sessions::numeric / NULLIF(t.grand_total, 0) * 100, 2) AS pct_of_total,
b.is_demo
FROM by_city b, totals t
ORDER BY b.region, b.sessions DESC
`);
const demo = await allDemo('ga4_geo_metrics');
return {
rows: res.rows,
formula:
'SUM(ga4_geo_metrics.sessions) GROUP BY region, city ' +
'WHERE country=\'United States\' AND region IN (\'California\',\'Arizona\') ' +
'→ pct_of_total = city_sessions / grand_total * 100. Aggregates only; no user-level data.',
demo,
};
}
// ---------------------------------------------------------------------------
// 2. Top Landing Pages by CRE Category
// ---------------------------------------------------------------------------
/**
* Top landing pages grouped into CRE categories inferred from path patterns.
* Categories: news, cre-talk, property-spotlight, conferences, newsletter, advertise, other.
*
* @returns {Promise<{ rows: object[], formula: string, demo: boolean }>}
*/
async function topLandingPagesByCategory() {
const res = await query(`
SELECT
landing_page,
CASE
WHEN landing_page LIKE '/cre-talk%' THEN 'cre_talk_sponsor'
WHEN landing_page LIKE '/property-spotlight%' THEN 'property_spotlight'
WHEN landing_page LIKE '/conferences%' THEN 'conference'
WHEN landing_page LIKE '/newsletter%' THEN 'newsletter_email'
WHEN landing_page LIKE '/advertise%' THEN 'advertise'
WHEN landing_page LIKE '/news%' THEN 'news'
WHEN landing_page = '/' THEN 'homepage'
ELSE 'other'
END AS category,
SUM(sessions) AS sessions,
SUM(users) AS users,
SUM(views) AS views,
SUM(engaged_sessions) AS engaged_sessions,
SUM(key_events) AS key_events,
ROUND(AVG(engagement_rate), 4) AS avg_engagement_rate,
bool_and(is_demo) AS is_demo
FROM ga4_landing_page_metrics
GROUP BY landing_page, category
ORDER BY sessions DESC
LIMIT 50
`);
const demo = await allDemo('ga4_landing_page_metrics');
return {
rows: res.rows,
formula:
'SUM(sessions,users,views,engaged_sessions,key_events) GROUP BY landing_page ' +
'→ category assigned by path prefix pattern ' +
'(/cre-talk→cre_talk_sponsor, /property-spotlight→property_spotlight, etc.). ' +
'Sorted by sessions DESC.',
demo,
};
}
// ---------------------------------------------------------------------------
// 3. Top Referral Domains
// ---------------------------------------------------------------------------
/**
* Top referral domains from ga4_acquisition_metrics.
* Filters to channel_group='Referral' and aggregates by session_source.
*
* @returns {Promise<{ rows: object[], formula: string, demo: boolean }>}
*/
async function topReferralDomains() {
const res = await query(`
SELECT
session_source AS referral_domain,
SUM(sessions) AS sessions,
SUM(users) AS users,
SUM(key_events) AS key_events,
bool_and(is_demo) AS is_demo
FROM ga4_acquisition_metrics
WHERE channel_group = 'Referral'
GROUP BY session_source
ORDER BY sessions DESC
LIMIT 20
`);
const demo = await allDemo('ga4_acquisition_metrics');
return {
rows: res.rows,
formula:
'SUM(sessions,users,key_events) FROM ga4_acquisition_metrics ' +
'WHERE channel_group=\'Referral\' GROUP BY session_source ORDER BY sessions DESC.',
demo,
};
}
// ---------------------------------------------------------------------------
// 4. Brand vs Nonbrand Split (GSC)
// ---------------------------------------------------------------------------
/**
* Brand vs nonbrand query split from gsc_query_metrics.
* Brand = is_brand=true (contains 'rentv').
*
* @returns {Promise<{ rows: object[], formula: string, demo: boolean }>}
*/
async function brandVsNonbrandSplit() {
const res = await query(`
SELECT
is_brand,
COUNT(*) AS query_count,
SUM(clicks) AS total_clicks,
SUM(impressions) AS total_impressions,
ROUND(SUM(clicks)::numeric / NULLIF(SUM(impressions), 0), 4) AS blended_ctr,
ROUND(AVG(position), 2) AS avg_position,
bool_and(is_demo) AS is_demo
FROM gsc_query_metrics
GROUP BY is_brand
ORDER BY is_brand DESC
`);
const demo = await allDemo('gsc_query_metrics');
return {
rows: res.rows,
formula:
'COUNT(query), SUM(clicks,impressions) FROM gsc_query_metrics GROUP BY is_brand. ' +
'is_brand=true when query contains "rentv" (case-insensitive). ' +
'blended_ctr = SUM(clicks) / SUM(impressions). ' +
'Source: organic search only — Search Console does NOT represent paid search.',
demo,
};
}
// ---------------------------------------------------------------------------
// 5. Query Clusters (GSC)
// ---------------------------------------------------------------------------
/**
* Query cluster distribution from gsc_query_metrics.
* Shows which topic clusters drive the most organic traffic.
*
* @returns {Promise<{ rows: object[], formula: string, demo: boolean }>}
*/
async function queryClusters() {
const res = await query(`
SELECT
cluster,
COUNT(*) AS query_count,
SUM(clicks) AS total_clicks,
SUM(impressions) AS total_impressions,
ROUND(SUM(clicks)::numeric / NULLIF(SUM(impressions), 0), 4) AS blended_ctr,
ROUND(AVG(position), 2) AS avg_position,
bool_and(is_demo) AS is_demo
FROM gsc_query_metrics
GROUP BY cluster
ORDER BY total_clicks DESC
`);
const demo = await allDemo('gsc_query_metrics');
return {
rows: res.rows,
formula:
'SUM(clicks,impressions) FROM gsc_query_metrics GROUP BY cluster. ' +
'Cluster taxonomy (§18): california_market, arizona_market, property_type, ' +
'finance_lending, brokerage_deal, conference_event, advertiser_category, other. ' +
'Assigned by keyword heuristics in gsc.clusterQuery(). Organic search only.',
demo,
};
}
// ---------------------------------------------------------------------------
// 6. High Impression / Low CTR Opportunities (GSC)
// ---------------------------------------------------------------------------
/**
* Queries with >=1000 impressions and CTR below 5% — content or title gap
* opportunities that could support advertiser packages.
*
* @param {{ impressionThreshold?: number, ctrThreshold?: number }} options
* @returns {Promise<{ rows: object[], formula: string, demo: boolean }>}
*/
async function highImpressionLowCtr({ impressionThreshold = 1000, ctrThreshold = 0.05 } = {}) {
const res = await query(
`SELECT
query,
cluster,
is_brand,
SUM(clicks) AS clicks,
SUM(impressions) AS impressions,
ROUND(SUM(clicks)::numeric / NULLIF(SUM(impressions), 0), 4) AS ctr,
ROUND(AVG(position), 2) AS avg_position,
bool_and(is_demo) AS is_demo
FROM gsc_query_metrics
GROUP BY query, cluster, is_brand
HAVING SUM(impressions) >= $1
AND SUM(clicks)::numeric / NULLIF(SUM(impressions), 0) < $2
ORDER BY impressions DESC
LIMIT 30`,
[impressionThreshold, ctrThreshold]
);
const demo = await allDemo('gsc_query_metrics');
return {
rows: res.rows,
formula:
`Queries WHERE SUM(impressions) >= ${impressionThreshold} AND blended_ctr < ${(ctrThreshold * 100).toFixed(0)}%. ` +
'These represent content or title gaps: high organic visibility but poor click-through. ' +
'Each represents a potential editorial or advertiser package opportunity. ' +
'Organic search only — does not reflect paid performance.',
demo,
};
}
// ---------------------------------------------------------------------------
// 7. Email / Newsletter Traffic Attribution (GA4)
// ---------------------------------------------------------------------------
/**
* Email and newsletter channel performance from ga4_acquisition_metrics.
* Breaks down UTM campaign attribution.
*
* @returns {Promise<{ rows: object[], formula: string, demo: boolean }>}
*/
async function emailNewsletterAttribution() {
const res = await query(`
SELECT
session_campaign,
session_source,
session_medium,
SUM(sessions) AS sessions,
SUM(users) AS users,
SUM(key_events) AS key_events,
bool_and(is_demo) AS is_demo
FROM ga4_acquisition_metrics
WHERE channel_group = 'Email'
GROUP BY session_campaign, session_source, session_medium
ORDER BY sessions DESC
`);
const demo = await allDemo('ga4_acquisition_metrics');
return {
rows: res.rows,
formula:
'SUM(sessions,users,key_events) FROM ga4_acquisition_metrics ' +
'WHERE channel_group=\'Email\' GROUP BY session_campaign, session_source, session_medium. ' +
'UTM-based attribution. Campaign = utm_campaign value from email links.',
demo,
};
}
module.exports = {
caAzAudienceConcentration,
topLandingPagesByCategory,
topReferralDomains,
brandVsNonbrandSplit,
queryClusters,
highImpressionLowCtr,
emailNewsletterAttribution,
};