Lore

Manual Hourly Day-Parting Analysis (Pivot Table Method)

A free, no-software technique (credited within Sophie Society to "Zoki") for finding day-parting opportunities using only Amazon's own data and a spreadsheet.

Method

1. Pull an Amazon Hourly Sponsored Ads Report Export for the campaign(s) in question.
2. Load the export into Google Sheets and build a pivot table filtered by portfolio, with hour-of-day as rows and impressions, clicks, orders, sales, and spend as values.
3. Add PPC Efficiency Metric Formulas (ACOS, CTR, CVR, CPC) alongside the pivot table to turn the raw hourly totals into ACOS, CTR, CVR, and CPC.
4. Apply conditional-formatting color-coding (Excel-Based PPC Report Color-Coding Workflow) across the hourly rows to visually surface which hours perform well and which don't, before writing day-parting rules.

Output

The end result is an hour-by-hour picture of ad performance — e.g. a block of hours (such as 4:00 a.m. to noon) that performs well "from every perspective" — that can be turned directly into bid or budget rules for Day Parting (Amazon PPC).

Reading the Pattern: Conditional Formatting

Apply Format > Conditional formatting > Color scale to each metric column in the hourly pivot table, one rule per column since the "good" direction differs by metric: for ACOS and CPC (lower is better) set the scale to green at the minimum and red at the maximum; for CTR and CVR (higher is better) reverse it, red at the minimum and green at the maximum. Collapse the pivot rows down to the 24 hour-of-day buckets so all hours are visible on screen at once — the colored gradient turns a table of numbers into a visual pattern of green (strong) and red (weak) hours that's much faster to scan than reading raw ACOS/CTR/CVR/CPC figures column by column. See PPC Efficiency Metric Formulas (ACOS, CTR, CVR, CPC) for the underlying metrics being color-coded.

Из тем: PPC Optimization, Analytics & Advanced Targeting