← back to Rentv Adintel
db/seed/rentv-products.js
217 lines
'use strict';
/**
* Seed rentv_rate_snapshots and rentv_audience_snapshots.
*
* Per §21:
* - At least 2 dated rate snapshots per product (rates may differ across dates).
* - Audience: two separate rows — ~45,000 and ~50,000 net recipients — NEVER merged.
* - All rates are placeholders pending official media kit; marked in package_notes.
*
* Idempotent: checks existing rows by product_key+observed_at before inserting.
*/
const RATE_PLACEHOLDER_NOTE = 'Rate placeholder pending official RENTV media kit confirmation.';
// Two snapshot dates representing different observed rate cards
const DATE_A = '2025-04-01';
const DATE_B = '2026-04-01';
// Each entry: [product_key, product_label, channel/unit, rateA, rateB, notesA, notesB]
const RATE_PRODUCTS = [
{
product_key: 'website_banner',
product_label: 'Website Banner Ad',
unit: 'per month',
rateA: 1500.00,
rateB: 1750.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
{
product_key: 'website_tile',
product_label: 'Website Tile Ad',
unit: 'per month',
rateA: 850.00,
rateB: 950.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
{
product_key: 'newsletter_banner',
product_label: 'Newsletter Banner Ad',
unit: 'per eblast',
rateA: 1200.00,
rateB: 1400.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
{
product_key: 'newsletter_tile',
product_label: 'Newsletter Tile Ad',
unit: 'per eblast',
rateA: 650.00,
rateB: 750.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
{
product_key: 'fixed_advertorial',
product_label: 'Fixed Advertorial',
unit: 'per placement',
rateA: 2500.00,
rateB: 3000.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
{
product_key: 'marketplace_eblast',
product_label: 'Marketplace E-blast',
unit: 'per eblast',
rateA: 1800.00,
rateB: 2000.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
{
product_key: 'property_spotlight_eblast',
product_label: 'Property Spotlight E-blast',
unit: 'per eblast',
rateA: 1950.00, // April 2025 flyer pricing
rateB: 2200.00, // April 2026 pricing (different package/rate observed)
notesA: RATE_PLACEHOLDER_NOTE + ' Observed in April 2025 flyer (Property Spotlight Flyer REV April 2025). Package may include dedicated send.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed in April 2026 corporate flyer (Corp Flyer Apr 2026 V3). Different package/price version.',
},
{
product_key: 'video_promotion_eblast',
product_label: 'Video Promotion E-blast',
unit: 'per eblast',
rateA: 2200.00,
rateB: 2500.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
{
product_key: 'homepage_video_promotion',
product_label: 'Home-Page Video Promotion',
unit: 'per month',
rateA: 3500.00,
rateB: 4000.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
{
product_key: 'conference_sponsorship',
product_label: 'Conference Sponsorship',
unit: 'per event',
rateA: 5000.00,
rateB: 6000.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
{
product_key: 'cre_talk_sponsorship',
product_label: 'CRE Talk Sponsorship',
unit: 'per episode/event',
rateA: 2000.00,
rateB: 2500.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
{
product_key: 'the_review_channel_sponsorship',
product_label: 'The REview Channel / Screen Sponsorship',
unit: 'per month',
rateA: 1500.00,
rateB: 1800.00,
notesA: RATE_PLACEHOLDER_NOTE + ' Observed 2025 rate card.',
notesB: RATE_PLACEHOLDER_NOTE + ' Observed 2026 rate card.',
},
];
// Two separate audience snapshot observations (NEVER merged per §21)
const AUDIENCE_SNAPSHOTS = [
{
metric_key: 'net_recipients',
metric_label: 'Net email recipients (newsletter)',
value_numeric: 45000,
observed_at: '2025-04-01',
source_key: 'rentv_media_kit',
note: 'Approximately 45,000 net recipients as stated in April 2025 media kit materials.',
},
{
metric_key: 'net_recipients',
metric_label: 'Net email recipients (newsletter)',
value_numeric: 50000,
observed_at: '2026-04-01',
source_key: 'rentv_media_kit',
note: 'Approximately 50,000 net recipients as stated in April 2026 media kit materials. Separate snapshot — not merged with 2025 figure.',
},
];
async function seedRentvProducts(client) {
let rate_inserted = 0;
let rate_skipped = 0;
let audience_inserted = 0;
let audience_skipped = 0;
// Rate snapshots — two per product (DATE_A and DATE_B)
for (const p of RATE_PRODUCTS) {
// DATE_A snapshot
const existA = await client.query(
`SELECT id FROM rentv_rate_snapshots WHERE product_key=$1 AND observed_at=$2 LIMIT 1`,
[p.product_key, DATE_A]
);
if (existA.rows.length === 0) {
await client.query(
`INSERT INTO rentv_rate_snapshots
(product_key, product_label, rate, currency, unit, package_notes, observed_at, source_key)
VALUES ($1,$2,$3,'USD',$4,$5,$6,'rentv_media_kit')`,
[p.product_key, p.product_label, p.rateA, p.unit, p.notesA, DATE_A]
);
rate_inserted++;
} else {
rate_skipped++;
}
// DATE_B snapshot (different rate/package)
const existB = await client.query(
`SELECT id FROM rentv_rate_snapshots WHERE product_key=$1 AND observed_at=$2 LIMIT 1`,
[p.product_key, DATE_B]
);
if (existB.rows.length === 0) {
await client.query(
`INSERT INTO rentv_rate_snapshots
(product_key, product_label, rate, currency, unit, package_notes, observed_at, source_key)
VALUES ($1,$2,$3,'USD',$4,$5,$6,'rentv_media_kit')`,
[p.product_key, p.product_label, p.rateB, p.unit, p.notesB, DATE_B]
);
rate_inserted++;
} else {
rate_skipped++;
}
}
// Audience snapshots — two separate rows
for (const a of AUDIENCE_SNAPSHOTS) {
const existing = await client.query(
`SELECT id FROM rentv_audience_snapshots WHERE metric_key=$1 AND observed_at=$2 LIMIT 1`,
[a.metric_key, a.observed_at]
);
if (existing.rows.length === 0) {
await client.query(
`INSERT INTO rentv_audience_snapshots
(metric_key, metric_label, value_numeric, value_text, observed_at, source_key)
VALUES ($1,$2,$3,$4,$5,$6)`,
[a.metric_key, a.metric_label, a.value_numeric, a.note, a.observed_at, a.source_key]
);
audience_inserted++;
} else {
audience_skipped++;
}
}
return { rate_inserted, rate_skipped, audience_inserted, audience_skipped };
}
module.exports = { seedRentvProducts };