PushNetwork served over half a billion impressions a day, and nobody queried the raw rows. pushnetwork-helper, a Node.js app of cron jobs, ran three separate pipelines: it rolled revenue up for reporting, published delivery configuration to S3, and the ad server loaded that configuration into memory.
PushNetwork ETL pipeline
- Node.js
- MariaDB
- SQL
- AWS S3
- ETL
1. Daily revenue rollup
At :50 each hour doEveryHour50 imports revenue from 13 sources for today, yesterday and two days ago, one step at a time so dependent data is in place and nothing deadlocks. It then calls usp_insert_stats_daily_placement for those dates and usp_insert_reporting_control for the last four days. Networks revise recent numbers, so each run overwrites the last.
Each procedure takes one date and ends in ON DUPLICATE KEY UPDATE against a unique key, so a re-run overwrites a day instead of doubling it. It first unions the keys from impressions and revenue, so a placement with impressions and no revenue yet still gets a row. Every five minutes the helper also records the newest dateupdated of each output table in health_cron_records, which the Health screen shows against its schedule.
Union the keys from both sides, join, upsert pushnetwork database, usp_insert_stats_daily_placement
INSERT INTO stats_daily_placement
SELECT NULL id, all_placements.date, all_placements.source, ...,
SUM(IFNULL(i.impressions,0)) AS impressions,
IFNULL(r.clicks,0) AS clicks, IFNULL(r.revenue,0) AS revenue,
NOW() datecreated, NOW() dateupdated
FROM (
-- placements with clicks, conversions, and revenue
SELECT date, source, owner_id, property, placement
FROM revenue_daily_placement WHERE date=dt_start
-- placements with impressions
UNION DISTINCT
SELECT date, source, user_id owner_id, site_id property, placement
FROM impressions_daily i WHERE date=dt_start
) all_placements
LEFT JOIN (...) i ON i.source=all_placements.source AND ...
LEFT JOIN (...) r ON r.source=all_placements.source AND ...
GROUP BY date, source, owner_id, property, placement
-- uniquely keyed on date/source/owner_id/property/placement
ON DUPLICATE KEY UPDATE
impressions = VALUES(impressions),
clicks = VALUES(clicks),
revenue = VALUES(revenue),
dateupdated = NOW();
2. Configuration files
Every hour at :20, right after the weight optimization, refresh_all_configurations_hourly works through about 30 refresh_s3__* methods. Each runs one query and uploads the result as one JSON file to S3: creative_optimizations.json, revenue_source_weight_by_domain.json, placement_rules.json, blacklist.json and so on.
After each upload the helper reads the file back and compares it with what it sent. On a mismatch it retries up to three times, three seconds apart, then logs the refresh time in helper_app_options.
Read it back and compare pushnetwork-helper/maintenance.js:3279-3299
self.get_data_from_s3(optsToLoadFromS3, res => {
if (!res || !res.success || !res.data) {
onComplete(PN.simpleFail(`Failed to load data for verify_s3_data @ "${filename}"`.red, { options }))
return
}
let dataLoaded = JSON.stringify(res.data)
// Ensure that expected data is present in S3 file
if (dataToCompare == dataLoaded) {
onComplete(PN.simpleSuccess(`Successful validation via verify_s3_data`, resOpts))
return
}
onComplete(PN.simpleFail(`Failed to validate via verify_s3_data - data at ${filename}`.red, { ... }))
})
3. Out to delivery
get-ad-content loads every file into memory when it starts and again on a 15-minute cron, with up to three retries per file. A separate five-minute job refreshes users.json. Every loader goes through the ad server's own small S3 reader, get_data_from_s3, which is separate from the helper's method of the same name. An ad request is decided from what is already in memory, so S3 is the whole interface between the two apps, and a new weight reaches delivery within 15 minutes.
Shared S3 read method aimtell-get-ad-content/bin/utils.js:174-218
/**
* Retrieves data from an S3 bucket. Data is expected to be valid JSON
*/
get_data_from_s3(options, onComplete) {
const self = this
// validate options.filename (omitted)
let bucketName = AWS_PN_BUCKET
let filename = options.filename
// Query S3
const s3 = new AWS.S3({ ..., Bucket: bucketName })
let params = { Bucket: bucketName, Key: filename }
s3.getObject(params, function (err, data) {
if (err) {
onComplete(self.simpleFail('ERROR @ ...', { options, err }))
return
}
if (data && data.Body) {
let objectData = JSON.parse(data.Body.toString('utf-8'))
onComplete(self.simpleSuccess('SUCCESS! @ ...', objectData))
}
});
}
Ad service loads config json aimtell-get-ad-content/src/ads/ad-service2.js:183-200
function generateCreativeOptimizations(attempt = 0) {
if (attempt > 3) return
Utils.get_data_from_s3({ filename: 'creative_optimizations.json' }, res => {
if (!res || !res.success) {
logError({ function: 'generateCreativeOptimizations', message: res.message, attempt });
// Wait 1 second before retrying
setTimeout(() => {
generateCreativeOptimizations(attempt + 1)
}, 1000);
return
}
creative_optimizations = res.data
})
}