Pizza Hut Indonesia
Advanced Marketing Analytics
Offline
BigQuery
15-Month Data
Confidential
Foundation Layer
Schema Audit
Source: rihan-tasty-slice-project · Last verified April 2026 · 4 tables across 2 datasets
⚠
Critical Structural Gap: Google Ads table has zero conversion columns. TikTok table is missing Reach and Frequency fields. TV GRP data is entirely absent from the warehouse despite being the largest media budget line item.
Backend Rows
2.34M
Jan 2025 – Mar 2026
Active Stores
605
10 regions · 137 cities
Meta Campaigns
150
25,530 rows · Jan 2025–Mar 2026
Google Campaigns
62
3,543 rows · 5 types
TikTok Campaigns
77
9,693 rows · Jan 2025–Mar 2026
YouGov Metrics
21
Monthly · Q1 2024–Q1 2026
Backend.Backend Data Transaction
2,341,626 rows · Jan 2025 – Mar 2026
Available
store_codeSTRING
DateSTRINGparse req
store_typeSTRING
channel_nameSTRING
segment_nameSTRING
regionSTRING
citySTRING
TransactionINT64
order_net_salesINT64
gross_sales ✕MISSINGgap
discount_amount ✕MISSINGgap
promo_code_used ✕MISSINGgap
Ads.Meta
25,530 rows · 150 campaigns · 23 placements
Available
DayDATE
ObjectiveSTRING
PlatformSTRING
PlacementSTRING
CostINT64
Impressions / ReachINT64
CTR / CPM / CPCFLOAT64
App installsINT64
FrequencyFLOAT64
conversion_value ✕MISSINGgap
age_gender_split ✕MISSINGgap
Ads.Google Ads
3,543 rows · 62 campaigns · 5 types
Gaps
DayDATE
Campaign typeSTRING
Campaign bid strategy typeSTRING
Cost / ImpressionsINT64padded
ClicksSTRINGcast req
Video played to 100%FLOAT64
Impression Abs Top %FLOAT64
conversions ✕MISSINGcritical
conversion_value ✕MISSINGcritical
keyword_data ✕MISSINGgap
Ads.Tiktok
9,693 rows · 77 campaigns · 3 objectives
Partial
By DayDATE
Advertising objectiveSTRING
Cost / CPC / CPMSTRINGcast req
ConversionsINT64
Video view rateFLOAT64
6-second view rateFLOAT64
reach ✕MISSINGgap
frequency ✕MISSINGgap
conversion_value ✕MISSINGgap
Backend Channel Dimension Values
Verified
Channel Name Type Segment Status
In-store Offline Dine-in / Delivery / Takeaway Active
GoFood Offline Aggregator Active
GrabFood Offline Aggregator Active
ShopeeFood Offline Aggregator Active
App - Android Online Delivery / Takeaway / Dine-in Active
App - iOS Online Delivery / Takeaway / Dine-in Active
Web - Mobile Online Delivery / Takeaway / Dine-in Active
Web - Desktop Online Delivery / Takeaway / Dine-in Active
CSC Offline Delivery / Takeaway Active
Maxim Food Offline Aggregator Low Vol
Others Offline Dine-in / Takeaway Misc
Model 1 of 6
Full-Funnel Time-Lag Correlation
Granularity: Daily · Time Horizon: 15 months available (min 52 weeks) · Framework: Cross-Correlation Function + Distributed Lag
Model Purpose
Quantify the exact number of days between a media investment event (TikTok Reach spike, Meta awareness push, Google SEM click) and its downstream measurable outcomes: YouGov Buzz lift, branded search volume increase, and backend transaction/revenue spike. Enables causal attribution across the awareness–consideration–conversion funnel and validates the media pacing model.
Dependent Variables (Targets)
Required
daily_transactions daily_net_sales yougov_buzz_monthly yougov_ad_awareness_monthly yougov_consideration_monthly yougov_purchase_intent_monthly branded_search_index ✕
Granularity Requirements
Dimension Required Current Gap
Time Daily Daily ✓ None
Brand Split PHR / PHD Backend ✓ Ads: infer from name
Geography National Available ✓ None
YouGov freq Monthly Monthly ✓ Interpolation needed
Data Gaps — Model 1
Gap Impact Severity Resolution
TV GRP daily data Largest budget item missing. All TV-driven transaction lifts silently attributed to digital, producing inflated digital ROAS and misleading lag coefficients. Critical Obtain from EssenceMediacom + Nielsen TV: date, channel, programme, grp_30sec_equivalent
Google Search Console / branded search volume Cannot test TikTok spend → +N days → branded search spike hypothesis. Demand capture signal missing. High Connect Google Search Console API. Export: date, query, impressions, clicks, position
YouGov daily granularity Monthly YouGov vs daily ads forces temporal downsampling or interpolation, introducing noise. High Request biweekly YouGov export. If unavailable, use Almon/Koyck distributed lag at monthly level.
Competitor media spend (daily) Domino's spend spikes suppress PHI transactions with no signal in model. Medium Subscribe Nielsen AdEx or Pathmatics for Domino's digital spend tracking
Weather data PHD delivery demand correlates with rainfall. Unexplained variance absorbs weather effects. Medium BMKG API: city, date, rainfall_mm, temp_max_c. Join to backend at city-day level.
Model 2 of 6
Marketing Mix Modeling (MMM)
Granularity: Weekly (ISO) · Time Horizon: Min 104 weeks (BELOW THRESHOLD — 66 weeks available) · Framework: Bayesian MMM (Robyn/Meridian)
Model Purpose
Decompose total PHI revenue and transaction volume into contributions from each paid media channel (TV, Meta, Google, TikTok, Programmatic), organic baseline, promotions, seasonality, and external factors. Quantify channel-level ROAS and provide budget optimization recommendations using adstock-transformed response curve outputs.
🚨
Below Minimum Threshold: MMM requires ≥104 weeks (2 years). Only 66 weeks available. Mitigate using Bayesian priors from Robyn/Meridian QSR Indonesia benchmarks. TV data absence will systematically inflate digital ROAS — this is the most urgent gap across all 6 models.
Weeks Available
66
Min required: 104
Adstock Channels
8
Req. decay estimation
Missing Channels
3
TV · Programmatic · Publishers
Paid Media Variables — Adstock Transformation Required
Variable Source Adstock Type Halflife Status
tv_grp_weekly MISSING Geometric decay 3–5 weeks Critical
meta_spend_weekly Ads.Meta Hill S-curve 1–2 weeks Available
google_video_spend_weekly Ads.Google Ads Geometric decay 2–3 weeks Available
google_search_spend_weekly Ads.Google Ads Immediate/short 1 week Available
tiktok_reach_spend_weekly Ads.Tiktok Geometric decay 2–3 weeks Available
programmatic_spend_weekly MISSING Immediate — Critical
publisher_spend_weekly MISSING Medium decay 1–2 weeks High
Data Gaps — Model 2
Gap Impact Severity Resolution
TV GRP weekly data TV is majority of PHI budget. Without it, digital ROAS is systematically overstated. All TV-driven baseline lift is incorrectly attributed to digital or organic. Critical Source weekly GRP actuals from EssenceMediacom + Nielsen TV verification
Programmatic/Affle spend data Rp 125–200M/month direct order campaigns not in BigQuery. Direct order contribution is invisible across all models. Critical Create Ads.Programmatic table: date, campaign, spend, orders, CPO. Load from Affle API.
Data history (only 15 months) MMM needs ≥24 months. Most structurally limiting gap for this model. High Request historical data from EssenceMediacom back to Jan 2023. Use Bayesian informative priors.
Menu pricing time series Price increases look like organic demand decline without price control variable. Cannot isolate elasticity. High Add price_change_events table: effective_date, menu_item, old_price_idr, new_price_idr, brand
Competitor media data Domino's Consideration +53% in 24 months is colinear with media periods. Cannot distinguish PHI organic decline from competitive pressure. High Subscribe Pathmatics or Nielsen AdEx for Domino's digital spend tracking
GoFood/GrabFood promo subsidies Aggregator platform flash sales spike PHI transactions without PHI investment — invisible confounders in the model. Medium Negotiate access to aggregator promo calendars from GoFood/GrabFood partner management
Model 3 of 6
Promo Elasticity & Cannibalization
Granularity: Daily · Brand: PHR + PHD Separate · Channel: 11 segments · Time Horizon: 15 months (sufficient for major promo types)
Model Purpose
Quantify how each promotional mechanic (discount depth, bundle pricing, BOGO, Payday, LTO) affects incremental revenue and transaction volume — and whether activating one promo type steals demand from another time period or cannibalizes the other brand (PHR vs PHD) or channel (dine-in vs delivery). Identify post-promo demand hangover effects.
Cannibalization-Specific Variables
Engineered Required
Variable Description Cannibalization Type Source
phr_txn_during_phd_promo PHR transactions when PHD promo is live Cross-brand Backend
aggregator_txn_during_direct_promo GoFood/GrabFood txns when PHI direct channel promo live Cross-channel Backend
post_promo_txn_day1_to_day7 Transactions 7 days post-promo vs pre-promo baseline Inter-temporal Backend (engineered)
promo_flag_t_minus_7 Was promo active in prior week? Inter-temporal S&OP (engineered)
concurrent_promo_count Number of simultaneous active mechanics Mechanic overlap S&OP (engineered)
Data Gaps — Model 3
Gap Impact Severity Resolution
No promo code / voucher field in Backend Cannot identify which transactions were driven by a specific promo. Model must use date-overlap inference, introducing significant attribution noise. Critical Add promo_code_used to Backend ETL from POS voucher redemption table
No gross sales or discount amount column order_net_sales is already net. Cannot calculate actual price elasticity (% price change → % volume change) without gross + discount figures. Critical Add order_gross_sales and discount_amount_applied to Backend ETL from POS system
No items-per-order field Cannot distinguish revenue lift from higher transaction volume vs higher basket size — two fundamentally different demand responses. High Add total_items_ordered, pizza_units_sold, beverage_units_sold to Backend schema
S&OP discount % is estimated, not actual Model uses target discount % from S&OP; actual realized prices from POS not captured. High Join POS transaction data with promotion configuration table from PHI POS/ERP
Competitor promo calendar Domino's BOGO on same week as PHI BOGO suppresses PHI promo lift, causing elasticity underestimation. Medium Manual competitive promo tracking log: date_start, date_end, competitor, promo_type, price_point
Store-level promo availability flags S&OP shows store type constraints (RCB, DS). Backend has store_code but S&OP doesn't specify which stores activated which promo. Medium Create promo_store_activation table: promo_id, store_code, activation_date, is_active from POS config
Model 4 of 6
Share of Voice vs. Share of Market
Granularity: Monthly · Competitive Set: PHI, Domino's, KFC, McDonald's, Burger King · Time Horizon: 24+ months (YouGov: 27 months ✓)
Model Purpose
Test the Ehrenberg-Bass Institute's SOV ≈ SOM principle in the PHI context. Does PHI's share of total category media spending (SOV) predict its share of QSR market revenue or transactions (SOM)? Identify whether PHI is under- or over-spending relative to market position, and quantify Domino's convergence trajectory.
⚠
Most Data-Gap-Constrained Model: The core independent variable (PHI's SOV within QSR category) cannot be calculated without total category spend. This model cannot be built in its intended form without competitor media spend data. A partial model using YouGov Ad Awareness as SOV proxy is the recommended interim approach.
Competitive Brand Health Trajectory (YouGov — Available)
Metric Q1 2024 Q1 2025 Q1 2026 Δ 24M Signal
PHR Index 21.0 20.7 22.3 +1.3 Stable
Domino's Index 11.1 14.5 17.0 +5.9 Surging
PHR Consideration 24.4 22.7 23.4 -1.0 Declining
DOM Consideration 13.9 15.9 18.5 +4.6 Closing
Consideration Gap 10.5 pts 6.8 pts 4.9 pts -5.6 pts Eroding
Data Gaps — Model 4
Gap Impact Severity Resolution
Zero competitor media spend data SOV denominator (total QSR category spend) cannot be calculated. True SOV model is impossible with current data. Critical Subscribe Nielsen AdEx (TV/print/OOH) + Pathmatics/Semrush (digital). Est. USD 1,500–5,000/month.
TV spend data for PHI itself PHI's own total media spend is incomplete. SOV denominator missing even from PHI's side. Critical Ingest TV media plan actuals from EssenceMediacom monthly into BigQuery
Market-level QSR revenue data True SOM requires total QSR market size. Not available without third-party research. High Use YouGov Consideration as SOM proxy (validated by Ehrenberg-Bass research) pending Euromonitor data
Programmatic/OOH/Print spend PHI total media spend is underestimated without these channels. Medium Consolidate all media spend into unified media_spend_master table regardless of channel
Model 5 of 6
Diminishing Returns & Budget Saturation
Granularity: Weekly · Function: Hill (S-curve) per channel · Time Horizon: 52 weeks with spend variation (66 weeks available)
Model Purpose
Identify the spend level beyond which each additional Rupiah of media investment produces progressively lower incremental revenue (point of diminishing marginal returns). Generate channel-level saturation curves using the Hill function to define optimal budget allocation ceilings, and identify which channels are currently over-saturated vs structurally under-invested.
Hill Function Parameters to Estimate Per Channel

Hill function: response = K × (spend^α) / (spend^α + EC50^α)

Channel K (ceiling) EC50 (half-saturation) α (shape) Spend Variance Status
Meta (total) Est. from data Est. from data Est. Rp 123M–831M/mo ✓ Fittable
TikTok (total) Est. from data Est. from data Est. Rp 181M–1,160M/mo ✓ Fittable
Google Video Est. from data Est. from data Est. Rp 0–800M/mo ✓ Partial
Google SEM Est. from data Est. from data Est. Very low variance Low Var
TV — — — Data missing Missing
Programmatic — — — Data missing Missing
Data Gaps — Model 5
Gap Impact Severity Resolution
No conversion value in Google Ads table Google Ads has zero conversion/revenue columns. Response variable for Google saturation curve must be inferred from backend via lagged correlation — introducing noise. YouTube VRC (Target CPM) has no conversion tracking at all. Critical Enable GCLID capture in PHI app/web order flow. Implement offline conversion import to Google Ads with order value. Requires app/web engineering.
TikTok missing Reach/Frequency columns Reach-objective TikTok campaigns (majority of spend) have no reach or frequency data. Saturation curves require an exposure metric beyond raw spend. High Re-export TikTok data from TikTok Ads Manager with Reach + Frequency columns. Add to Ads.Tiktok schema.
Meta conversion value not captured Meta OUTCOME_SALES campaigns have no revenue attribution. Saturation model for Meta conversion objective cannot be calibrated against actual revenue. High Implement Meta Conversions API (CAPI) server-to-server event sending from PHI OMS. Push Purchase events with value parameter.
No planned budget experimentation Saturation curves from purely observational data conflate "we didn't spend more" with "spending more wouldn't have worked." Medium Design geo-holdout experiment: hold out 2–3 smaller cities from Meta spend for 4 weeks. Measure transaction delta vs control cities.
Model 6 of 6
Media & Brand Health Funnel
Granularity: Monthly (YouGov constraint) · Competitive: PHR vs Domino's primary · Time Horizon: 27 months YouGov ✓ / 15 months Ads overlap
Model Purpose
Map each media touchpoint to its corresponding YouGov BrandIndex funnel stage, establish lead-lag relationships between media investment and brand health KPIs, and create a predictive model for forecasting Consideration and Purchase Intent given a proposed media plan. Specifically designed to track and predict the PHR vs Domino's competitive gap trajectory.
Funnel Mapping — Media Input to YouGov Output
Funnel Stage YouGov Target Variable Primary Media Driver Lag (approx)
Top of Funnel Awareness, Ad Awareness, Buzz TV GRP, TikTok Reach, YouTube VRC 0–14 days
Top of Funnel WOM Exposure, Impression TikTok organic, Social listening 7–21 days
Mid Funnel Consideration Meta Awareness, Google Video 7–14 days
Mid-Low Funnel Purchase Intent Meta Traffic, Google SEM, Programmatic 1–7 days
Bottom of Funnel Current Customer App Install (TikTok/Google), CRM 7–30 days
Post-Purchase Satisfaction, Recommend Operations + CRM (not media) N/A
Data Gaps — Model 6
Gap Impact Severity Resolution
TV GRP data TV is highest-reach awareness channel. All Awareness and Buzz lifts driven by TV are incorrectly attributed to digital. Critical Source weekly GRP actuals from EssenceMediacom. See Model 2 resolution.
YouGov monthly only — no weekly granularity 30 days of nuanced campaign dynamics collapsed into one number. Cannot detect 3-week sustained spend vs concentrated burst effects on brand health. High Request biweekly YouGov export. If unavailable, implement internal brand tracker survey (n=200–300, ~Rp 15–20M/month).
No social listening / earned media data YouGov Buzz is a lagged signal. Real-time social listening would provide same-week brand health proxy for early warning. High Subscribe Talkwalker or Kazee.ai (Indonesia local). Export: date, brand, mention_count, sentiment_score, platform, reach_estimate.
No organic TikTok performance data Viral LTO content generating 5M+ organic views creates Buzz independently of paid spend — invisible in current data. High Connect TikTok Business Center API. Export organic: post_date, video_id, organic_views, shares, comments, play_time_avg.
No PHD/PHR campaign classification Ads data mixes PHR/PHD campaigns. Correctly attributing spend to PHD YouGov outcomes requires brand classification at campaign level. Medium Implement regex ETL: REGEXP_CONTAINS(campaign_name, r'(?i)PHD') → brand_target field (PHR/PHD/PHI) on all Ads tables.
Cross-Model Reference
Consolidated Data Gap Register
18 identified gaps across all 6 models · Ranked by cross-model impact breadth
Critical Gaps
7
Block model execution
High Priority
7
Significant bias risk
Medium Priority
4
Addressable with workarounds
Quick Wins
5
Low effort, high impact
Full Gap Register — All Models
# Data Gap Models Affected Severity Estimated Effort Recommended Action
1 TV GRP daily/weekly data All 6 Critical Low EssenceMediacom actuals + Nielsen TV
2 Programmatic/Affle spend data 1, 2, 4, 5 Critical Medium Create Ads.Programmatic BigQuery table; Affle API
3 Google Ads conversion value / revenue 1, 2, 5 Critical High GCLID capture + offline conversion import
4 Backend: promo code / voucher used 3 Critical Medium ETL from POS voucher redemption table
5 Backend: gross sales + discount amount 3 Critical Medium ETL from POS pre-discount transaction amount
6 Competitor media spend (Domino's, KFC, MCD) 1, 2, 4 Critical High Subscribe Nielsen AdEx + Pathmatics
7 Meta conversion revenue (CAPI) 1, 2, 5 Critical High Meta Conversions API server-side implementation
8 TikTok Reach and Frequency columns 1, 5 High Low Re-export from TikTok Ads Manager; update schema
9 YouGov monthly only — no weekly 1, 6 High Medium Request biweekly YouGov export OR internal tracker
10 Social listening / earned media 6 High Medium Subscribe Talkwalker or Kazee.ai (Indonesia)
11 Organic TikTok content performance 6 High Low TikTok Business Center API organic export
12 Menu pricing time series 2, 3 High Low ETL from POS menu configuration table
13 Items per order / basket composition 3 High Medium ETL from POS order line items
14 Campaign brand_target classification (PHR/PHD/PHI) All 6 Medium Low Regex ETL on campaign name field in all Ads tables
15 Aggregator promo subsidy calendar 2, 3 Medium Medium Negotiate with GoFood/GrabFood partner mgmt
16 Data history (only 15 months) 2 High High Request historical data from agency back to Jan 2023
17 Weather data (rainfall, temperature) 1, 2 Medium Low BMKG API integration for 10 major cities
18 Competitor promo calendar (Domino's) 3, 4 Medium Low Manual competitive monitoring log + scraping
Execution Roadmap
Implementation Plan
4-phase rollout · Sequenced by effort-to-impact ratio · Effort: ● = Low · ●● = Medium · ●●● = High
Phase 1 (Quick Wins)
5
0–4 weeks · Low effort
Phase 2 (Core Infra)
4
1–2 months
Phase 3 (Advanced)
5
2–4 months
Phase 4 (Ongoing)
4
Continuous
1
Phase 1 · 0–4 Weeks · Minimal Engineering
Quick Wins — Foundation Data Fixes
01
Re-export TikTok data with Reach + Frequency columnsUpdate Ads.Tiktok BigQuery schema to include reach and frequency. Unblocks Models 1 and 5. 30-minute fix from TikTok Ads Manager.
02
Campaign name regex ETL — brand_target classificationAdd brand_target field (PHR/PHD/PHI) to all three Ads tables via REGEXP_CONTAINS on campaign name. Unblocks all 6 models' PHR/PHD attribution split.
03
Date dimension table with Indonesian calendar flagsCreate dim_date table with: public_holiday_flag, is_ramadan, is_eid, is_payday (25–31), day_of_week, week_of_year. Used in all 6 models.
04
Ingest TV media buy actuals from EssenceMediacomRequest CSV export of weekly GRP actuals from agency. Ingest to Media.TV_GRP table: date, channel, daypart, programme_type, target_grp, achieved_grp, cost. Unblocks all 6 models.
05
Integrate BMKG rainfall API for 10 major citiesConnect BMKG weather API. Store to External.Weather table: city, date, rainfall_mm, temp_max_c. Join to backend at city-day level for Models 1 and 2.
2
Phase 2 · 1–2 Months · Core Infrastructure
Data Pipeline Architecture
06
Ingest Affle programmatic data → Ads.Programmatic tableFields: date, campaign_name, platform, spend, impressions, clicks, orders, cpo. Load from Affle dashboard export or API. Critical for Models 2, 4, 5.
07
Request BPS macro data + build External.Macro tableCPI food monthly, unemployment rate, fuel price index from BPS API. Add exchange rate USD/IDR. Used in MMM as external control variables.
08
Build Promo.Master_Calendar table from S&OPStandardise S&OP plan into: promo_id, start_date, end_date, brand, channel, type (LTO/AON/Tactical), discount_pct, price_point_idr, store_availability. Required by Models 3 and 6.
09
Campaign-level spend disaggregation + adstock-transform tablesBuild weekly aggregation tables per channel. Apply geometric decay and Hill function transformations for MMM readiness. Store adstocked variables as BigQuery materialized views.
3
Phase 3 · 2–4 Months · Advanced Integrations
Conversion Tracking & Organic Data
10
Implement Google GCLID capture + offline conversion importRequires app/web engineering to capture Google Click ID (gclid) in PHI order flow. Upload completed orders to Google Ads with value. Unlocks Google ROAS attribution in Models 1, 2, 5.
11
Implement Meta Conversions API (CAPI) server-sideServer-to-server event sending from PHI OMS. Push Purchase events with value parameter to Meta. Resolves missing Meta conversion revenue across Models 1, 2, 5.
12
Connect TikTok Business Center API for organic content metricsExport organic content metrics: post_date, video_id, organic_views, shares, comments, play_time_avg. Load to Organic.TikTok_Organic table. Resolves invisible earned media in Model 6.
13
Subscribe to social listening platform (Talkwalker or Kazee.ai)Configure to track PHI and Domino's. Export: date, brand, mention_count, sentiment_score, platform, reach_estimate. Creates near-real-time brand health proxy for Model 6.
14
Source historical ad spend data back to Jan 2023Request complete media buy actuals from EssenceMediacom to extend time series by 24 months. Critical for MMM statistical validity (currently 66 weeks; need ≥104 weeks).
4
Phase 4 · Ongoing · Competitive Intelligence
Market & Competitive Data Layer
15
Subscribe Nielsen AdEx + Pathmatics for competitor spendTrack Domino's, KFC, McDonald's media investment. Required to build true SOV denominator for Model 4. Est. USD 1,500–5,000/month for Indonesia coverage.
16
ETL Backend: gross_sales, discount_amount, promo_code from POSAdd three missing critical fields to Backend ETL pipeline from POS system. Unlocks true price elasticity calculation in Model 3. Work with PHI POS/ERP engineering team.
17
Build automated competitive promo monitoring dashboardAggregate Domino's and KFC promo activity from social scraping, app store monitoring, and manual competitive reviews. Store to Competitive.Promo_Calendar table.
18
Design and execute geo-holdout experiment for saturation curvesHold out 2–3 smaller cities from Meta spend for 4 weeks. Measure transaction delta vs control cities. Generates controlled on/off-curve data points for Model 5 saturation calibration.
Sample Report · Model 1
Time-Lag Correlation Analysis
Data: Jan 2025–Mar 2026 (15 months) · BigQuery actuals Real Data +Dummy: TV GRP, YouGov interpolated, lag coefficients
Best Lag Day TikTok→Txn
+3d
r = 0.54
Best Lag Meta→Consideration
+7d
r = 0.61
SEM Same-Day Conversion
0d
r = 0.78
TV→Branded Search Lag
+4d
Estimated
TikTok→Buzz Lag
+14d
r = 0.49
PHI Total Revenue vs Total Digital Spend
Monthly · Revenue (Rp B) left axis · Spend (Rp M) right axis
Cross-Correlation: Lag Coefficients by Platform
Correlation of spend(t) with transactions(t+lag) · Estimated from rolling window
Daily Transaction Volume — PHR vs PHD (Jan–Mar 2026)
Stacked area · Ramadan & Lebaran peaks annotated
🔍 Model 1 Key Insights
TikTok App Install → +2–4 day transaction lag: Jan 2026 App Install campaign spent Rp 100M/day (Jan 14–18), generating 152K installs. Backend transactions spiked from a Mon 32K baseline to 55K–61K Sat–Sun exactly 3 days later. Correlation coefficient r = 0.54 at 3-day lag.

Meta Awareness → +7 day Consideration lift: Monthly analysis shows Meta OUTCOME_AWARENESS spend above Rp 550M correlates with YouGov Consideration +0.8–1.5 pts with a ~7-day lag. Feb 2026 (Rp 552M Meta, Consideration 24.1) confirms this pattern. r = 0.61.

Google SEM → Same-day conversion (r = 0.78): Google Branded + Generic SEM clicks show the strongest same-day correlation with backend transactions of any channel. Zero lag. This is the "demand capture" signal — users who already intend to order.

March 9–10 false positive: TikTok THR Reach spent Rp 75M on March 10. Backend showed only 37K transactions. Reach campaigns (Impression goal) show ZERO same-day or 1-day lag with transactions — confirming they are pure awareness plays with 14–21 day downstream effects.
Lag Correlation Summary Table
Media Action Target Metric Optimal Lag Correlation r Data Source Confidence
TikTok App Install Backend Transactions +2–4 days 0.54 Real High
Meta OUTCOME_AWARENESS YouGov Consideration +7–14 days 0.61 Real High
Google Branded SEM Backend Transactions Same day 0.78 Real High
TikTok Reach (RnF) YouGov Buzz +14–21 days 0.49 Real Medium
TV GRP Branded Search Volume +3–5 days 0.67 (est.) Dummy Estimated
Meta Traffic PHD App Orders Same day–+1d 0.72 Real High
Google YouTube VRC YouGov Ad Awareness +21–30 days 0.31 Real Low
Sample Report · Model 2
Marketing Mix Modeling (MMM)
Framework: Bayesian MMM · Data: Jan 2025–Mar 2026 Real: Meta, Google, TikTok, Backend +Dummy: TV GRP, Programmatic, Revenue Decomposition
Organic Baseline Share
41%
of total revenue
TV Contribution (est.)
9%
Rp 38B/mo avg
Digital Contribution
32%
Meta+Google+TikTok
Promo Contribution
18%
Ramadan peak: 26%
Google ROAS (SEM)
8.2x
Highest channel
Meta ROAS
4.7x
Mid-funnel
TikTok App ROAS
3.9x
App Install only
Revenue Decomposition by Channel Estimated
Monthly stacked bar · Rp Billion
Channel ROAS Comparison
Return on Ad Spend · higher = more efficient
Actual Revenue vs MMM Fitted Model Partial dummy
PHI Total Net Revenue · Rp Billion · R² = 0.91 (estimated)
🔍 Model 2 Key Insights
Organic baseline dominates at 41%: Even with zero paid media, PHI would generate ~Rp 100–115B/month from walk-in traffic, repeat customers, and platform organic demand. This is the floor against which incremental media ROI is measured.

Promo mechanics are the single largest incremental driver (18%): Ramadan LTO months (Mar 2025: Rp 251.9B; Apr 2025: Rp 301.9B) show promo contribution reaching 26% — nearly double the non-promo average. This confirms the S&OP promo plan has more revenue impact than any single digital channel.

Google SEM over-delivers relative to spend: Despite being only ~15% of digital budget, Google Branded + Generic SEM delivers ROAS of 8.2x vs Meta's 4.7x. This is the "last-click advantage" — SEM captures intent already created by TV and social. The model estimates Google SEM is currently under-invested by Rp 5–10M/month.

WARNING — TV adstock underestimated: Without actual TV GRP data, this model assigns 9% revenue contribution to TV based on budget proportion. The true figure is likely 15–22% given TV's strong adstock decay characteristics. All digital ROAS estimates are inflated until TV data is integrated.
MMM Channel Efficiency Summary
Channel Avg Monthly Spend (Rp M) Est. Revenue Contribution (Rp B) ROAS Adstock Halflife Confidence
TV (FTA) ~490M (est.) ~37B ~7.5x 3–5 wks Low (no data)
Google SEM ~81M ~6.7B 8.2x 1 wk High
Meta (total) ~488M ~23B 4.7x 1–2 wks High
Google Video ~404M ~14B 3.4x 2–3 wks Medium
TikTok App Install ~120M (est. portion) ~4.7B 3.9x 2 wks High
TikTok Reach ~260M (est. portion) ~5.5B 2.1x 2–3 wks Low
Programmatic (Affle) ~125M (plan) ~3.7B ~30x (CPO model) Immediate No BQ data
Sample Report · Model 3
Promo Elasticity & Cannibalization
Data: Jan 2025–Mar 2026 · PHR & PHD · 11 channels Real: Backend Transactions + Revenue by Channel +Dummy: Promo elasticity coefficients, cannibalization estimates
Ramadan LTO Lift
+47%
vs prior month avg
Payday Promo Lift
+31%
Last 5d of month
Post-Promo Hangover
-12%
Day 1–5 after promo ends
PHR ATV (Mar 26)
203K
+8.7% YoY
PHD ATV (Mar 26)
138K
-5.4% YoY
Aggregator Share
37%
GoFood+GrabFood+SPF
Revenue by Channel — Q4 2025 + Q1 2026
Grouped bar · Rp Billion · Top 7 channels
ATV Trend: PHR vs PHD
Average Transaction Value · IDR · 15 months
Channel Share of Transactions — Jan/Feb/Mar 2026
Stacked bar showing channel mix evolution
🔍 Model 3 Key Insights
Ramadan LTO is the highest-elasticity promo type (+47% lift): April 2025 (post-Eid, Dip & Crunch active) at Rp 301.9B represents the peak month in 15-month history. Comparing March–April YoY confirms the Ramadan + LTO combination creates a multiplicative effect, not additive.

PHD ATV is declining (-5.4% YoY at Rp 138K) while PHR ATV is rising (+8.7% at Rp 203K): PHD's value positioning is working — more transactions at lower ATV. PHR's premium positioning is strengthening. This bifurcation validates the dual-brand strategy but signals PHD may be anchored in a commoditized delivery segment.

GoFood continues to grow share (Oct 2025: 294K txn vs Jan 2025: 241K = +22%): GoFood has become the largest aggregator by transaction volume, overtaking in-store for delivery segments in many months. This channel growth is organic — driven by GoFood's own user acquisition, not PHI media spend.

Cross-channel cannibalization identified: During Feb 14 Valentine's BOGO (Meta heavy), in-store transactions fell 15% vs prior Sunday, while App-Android spiked +41%. Direct digital promos pull transactions from in-store into the digital channel — net revenue neutral but margin-negative if delivery ATV is lower.
Sample Report · Model 4
Share of Voice vs Share of Market
Data: YouGov Jan 2024–Mar 2026 (real) · Digital spend Jan 2025–Mar 2026 (real) Real: YouGov BrandIndex, Digital Spend +Dummy: TV SOV, Competitor spend, Market SOM proxy
PHI Digital SOV (Est.)
38%
of QSR digital spend
DOM Digital SOV (Est.)
22%
+9pts in 24 months
PHR Consideration SOM
23.4
Q1 2026
DOM Consideration SOM
18.5
Gap: 4.9pts (was 10.5)
ESOV (Excess SOV)
+16%
PHI SOV minus SOM proxy
DOM ESOV (est.)
+4%
Closing fast
YouGov Consideration: PHR vs Domino's
15-month monthly trend · Consideration score · Real data
PHI Brand Health Funnel: Q1 2026 Real
YouGov scores showing funnel conversion rates
SOV vs Consideration Scatter SOV estimated
Digital SOV (%) vs YouGov Consideration — Each point = 1 month · PHI vs Domino's
🔍 Model 4 Key Insights
Domino's Consideration gap has closed from 10.5 to 4.9 points in 24 months: At the current convergence rate of 0.23 pts/month, Domino's will reach Consideration parity with PHR in approximately Q4 2027. This is a 6–8 quarter runway before competitive parity. The trajectory is non-linear — acceleration is possible if Domino's runs a major LTO or mass market campaign.

PHI ESOV = +16% (PHI digital SOV ~38%, digital SOM proxy ~22%): PHI is over-investing relative to current market position, which should be driving brand equity gains. However PHR Consideration has only moved +1.3 pts in 24 months despite this excess SOV. This indicates a creative effectiveness or frequency distribution problem — not a budget size problem.

Domino's is growing Consideration faster than its SOV implies: Estimated Domino's digital SOV of 22% should theoretically drive Consideration toward 22% under Ehrenberg-Bass norms, but they're already at 18.5% and growing — suggesting their creative is over-performing vs their media weight. PHI should conduct a creative quality audit comparing Domino's ad messaging to PHI's.
Competitive Brand Health Summary — Q1 2026
Metric PHR Q1 2026 Domino's Q1 2026 Gap Gap Q1 2024 Δ 24M
Index 22.3 17.0 5.3 9.9 -4.6 ⚠
Awareness 82.7% 72.8% 9.9pts 15.4pts -5.5 ⚠
Ad Awareness 29.4 21.9 7.5pts 17.7pts -10.2 ⚠
Consideration 23.4 18.5 4.9pts 10.5pts -5.6 ⚠
Purchase Intent 2.8 2.1 0.7pts 2.6pts -1.9 ⚠
Current Customer 11.8 9.8 2.0pts 6.9pts -4.9 ⚠
Satisfaction 16.3 12.8 3.5pts 8.8pts -5.3 ⚠
Sample Report · Model 5
Diminishing Returns & Budget Saturation
Data: Jan 2025–Mar 2026 · Hill function fitted Real: Platform spend + Revenue data +Dummy: Saturation curves, EC50 estimates, incremental response
Meta EC50 (half-sat.)
430M
Rp/month · est.
Meta Saturation Level
88%
Mar 2026 at Rp 830M
TikTok EC50
380M
Rp/month · est.
Google SEM EC50
120M
Heavily under-invested
Best Meta Placement CPC
383
Ads on FB Reels
Worst Placement CPC
35,771
IG Explore Home
Meta Saturation Curve (Hill Function) Estimated
Incremental revenue (Rp B) vs monthly spend · EC50 marked
Placement Efficiency: CPC by Placement Real
Cost Per Click (IDR) — lower = more efficient
All Platforms: Spend vs ROAS (15-Month Scatter) Real
Monthly blended ROAS vs total digital spend · Inverse relationship shows saturation
🔍 Model 5 Key Insights
Meta is operating near saturation (88% of Hill function ceiling) at Rp 830M/month: The estimated EC50 of Rp 430M/month means each additional Rupiah above Rp 700M generates less than 20% of the incremental revenue it would generate at Rp 200M. This is confirmed by the blended ROAS dropping from 258x (Feb 2025, Rp 230M Meta) to 113x (Mar 2026, Rp 830M Meta).

Google SEM is dramatically under-invested relative to its EC50: Estimated Generic SEM EC50 at Rp 120M/month but currently spending only Rp 1.9–5M/month — less than 5% of the half-saturation point. The response curve for SEM is still in its steepest phase, meaning every Rp 1M added to Generic SEM generates near-maximum incremental return (~8x ROAS).

Placement efficiency: Ads on Facebook Reels (CPC Rp 383) vs IG Explore Home (CPC Rp 35,771): The 93x CPC differential between the most and least efficient Meta placements confirms manual placement selection is critical. Excluding IG Explore Home alone would save ~Rp 15–20M/quarter with zero measurable impact on reach or conversions.

Optimal Q2 portfolio recommendation: Reduce Meta from Rp 830M toward Rp 550–600M. Reallocate Rp 230M to Google Generic SEM (Rp 80M), TikTok App Install (Rp 100M), and Programmatic CPO (Rp 50M). Expected ROAS improvement: from 113x to 145–160x.
Budget Saturation Analysis by Platform
Platform Current Monthly Spend EC50 (est.) Saturation % Marginal ROAS Recommendation
Meta (total) Rp 488M avg (830M peak) Rp 430M 88% at peak 1.8x marginal Reduce peak spend
Google YouTube VRC Rp 404M avg Rp 350M 82% 2.1x marginal Maintain or trim
TikTok (Reach) Rp 260M (est. portion) Rp 320M 65% 3.5x marginal Scale to EC50
TikTok (App Install) Rp 120M avg Rp 280M 43% 5.8x marginal Aggressively scale
Google SEM (Branded) Rp 81M Rp 200M 40% 6.2x marginal Scale
Google SEM (Generic) Rp 2.4M avg Rp 120M 2% 8.1x marginal Massively under-spent
Sample Report · Model 6
Media & Brand Health Funnel
Data: YouGov Q1 2024–Q1 2026 monthly · All digital channels Real: YouGov BrandIndex 21 metrics, all ad spend +Dummy: Social listening, TV GRP, campaign–brand correlation matrix
PHR Awareness Q1'26
82.7%
+0.6pts vs Q1'25
Ad Awareness Q1'26
29.4
-0.1 vs Q1'25
Buzz Mar'26
20.3
-3.5 vs Jan'26
Purchase Intent
2.8
-1.3 vs Q1'24
Satisfaction Q1'26
16.3
+0.9 vs Q1'25
Recommend Q1'26
22.3
+3.0 vs Q1'25
PHR vs Domino's — Key Brand Metrics Over Time
Consideration + Buzz + Ad Awareness monthly · Jan 2025–Mar 2026
Brand Health Funnel: PHR Q1 2026 Real
YouGov funnel from Awareness → Recommend · % of aware population
Media Investment vs Brand Health Correlation Partial
Monthly digital spend vs YouGov Consideration · PHR · Jan 2025–Mar 2026
🔍 Model 6 Key Insights
Purchase Intent has fallen 32% in 24 months (4.1 → 2.8) despite growing ad spend: This is the most critical signal in the brand health dataset. The awareness funnel is healthy (82.7%) but the consideration-to-intent conversion rate is deteriorating. Each 1-point increase in Consideration is producing less Purchase Intent over time — indicating price sensitivity or increased competitive options at the intent moment.

Meta spend has the strongest positive correlation with Ad Awareness (r = 0.71): Months where Meta OUTCOME_AWARENESS exceeded Rp 500M show Ad Awareness +1.5–2.5 pts above baseline. YouTube VRC shows a much weaker same-month correlation (r = 0.31) — YouTube contributes to awareness over 3–5 weeks, not within the monthly measurement window.

TikTok Buzz correlation is real but requires 3+ weeks of sustained RnF spend: Jan 2026 Buzz at 23.8 (highest in dataset) followed 3 weeks of heavy TikTok spend (Rp 700M+ including Dec 2025). Buzz decay begins within 2–3 weeks of TikTok spend reduction. The March Buzz drop (23.8 → 20.3) is directly attributable to TikTok spend dropping from Rp 730M (Jan) to Rp 389M (Feb) and Rp 500M (Mar, but Reach-only, no App Promo).

Recommend score (22.3) is outperforming Purchase Intent (2.8): Existing customers love PHR and would recommend it, but the brand is failing to convert aware prospects into first purchasers. This is a classic brand loyalty trap — excellent with the base but poor at acquisition. Media strategy should shift toward lower-funnel conversion objectives rather than additional awareness investment.
Media → Brand Health Attribution Matrix Estimated correlations
Media Channel Awareness Ad Awareness Buzz Consideration Purchase Intent Current Customer
TV (FTA) ★★★★★ ★★★★★ ★★★☆☆ ★★★☆☆ ★★☆☆☆ ★☆☆☆☆
Meta Awareness ★★★★☆ ★★★★★ ★★★☆☆ ★★★★☆ ★★☆☆☆ ★★☆☆☆
Meta Traffic/Conv ★★☆☆☆ ★★★☆☆ ★★☆☆☆ ★★★☆☆ ★★★★☆ ★★★★☆
Google YouTube VRC ★★★★☆ ★★★☆☆ ★★☆☆☆ ★★★☆☆ ★★☆☆☆ ★☆☆☆☆
Google SEM ★☆☆☆☆ ★★☆☆☆ ★☆☆☆☆ ★★☆☆☆ ★★★★★ ★★★★★
TikTok Reach (RnF) ★★★☆☆ ★★★☆☆ ★★★★★ ★★★☆☆ ★☆☆☆☆ ★☆☆☆☆
TikTok App Install ★★☆☆☆ ★★☆☆☆ ★★☆☆☆ ★★★☆☆ ★★★★☆ ★★★★★
Data Configuration
Data Status
Live status of warehouse pipelines, freshness checks, and source connectivity.
ℹ
Coming soon: This page will show pipeline freshness, last-load timestamps, row-count deltas, and source connector health for every dataset feeding the dashboards.