Performance_Troubleshooting_Instructions.txt

### Performance Troubleshooting Instructions

**Purpose:**
Provide clear guidance for ChatGPT to troubleshoot performance issues using the `Earnings_and_Analytics_by_Day.json` data source. When a user asks a performance-related question, follow these instructions to determine which metrics to pull, how to analyze anomalies, and how to present results.

1. **Initial Data Pull & Site Selection:**

   * **Time Window:** Retrieve data for the last **420 days** relative to today.
   * **Site Dropdown:** Present a dropdown of all unique `Site Name (SITE_EXTENDED)` values. Filter the dataset to the user’s selection.
   * **Metrics & Query:** Use the following SQL template, substituting `{{site}}` from the dropdown and dynamic dates:

   ```sql
   SELECT
     "DATE",
     SUM("PUB NET EARNINGS") AS "PUB_NET_EARNINGS",
     SUM("IMPRESSIONS") AS "IMPRESSIONS",
     SUM("SESSIONS") AS "SESSIONS",
     SUM("PAGEVIEWS") AS "PAGEVIEWS",
    CASE WHEN SUM("IMPRESSIONS") = 0 THEN NULL
         ELSE (SUM("PUB NET EARNINGS") / SUM("IMPRESSIONS")) * 1000 END AS "CPM",
    CASE WHEN SUM("PAGEVIEWS") = 0 THEN NULL
         ELSE (SUM("PUB NET EARNINGS") / SUM("PAGEVIEWS")) * 1000 END AS "PAGE_RPM",
    CASE WHEN SUM("SESSIONS") = 0 THEN NULL
         ELSE (SUM("PUB NET EARNINGS") / SUM("SESSIONS")) * 1000 END AS "RPM",
    CASE WHEN SUM("PAGEVIEWS") = 0 THEN NULL
         ELSE (SUM("PAGEVIEWS") / SUM("SESSIONS")) * 1000 END AS "PPS",
    CASE WHEN SUM("PAGEVIEWS") = 0 THEN NULL
         ELSE (SUM("IMPRESSIONS") / SUM("PAGEVIEWS")) * 1000 END AS "IPPV",
    CASE WHEN SUM("IMPRESSIONS") = 0 THEN NULL
         ELSE (SUM("IMPRESSIONS") / SUM("SESSIONS")) * 1000 END AS "IMP/SESSION"
=======
     CASE WHEN SUM("IMPRESSIONS") = 0 THEN NULL
          ELSE SUM("PUB NET EARNINGS") / (SUM("IMPRESSIONS") / 1000) END AS "CPM",
     CASE WHEN SUM("PAGEVIEWS") = 0 THEN NULL
          ELSE SUM("PUB NET EARNINGS") / (SUM("PAGEVIEWS") / 1000) END AS "PAGE_RPM",
     CASE WHEN SUM("SESSIONS") = 0 THEN NULL
          ELSE SUM("PUB NET EARNINGS") / (SUM("SESSIONS") / 1000) END AS "RPM",
     CASE WHEN SUM("PAGEVIEWS") = 0 THEN NULL
          ELSE SUM("PAGEVIEWS") / (SUM("SESSIONS") / 1000) END AS "PPS",
     CASE WHEN SUM("PAGEVIEWS") = 0 THEN NULL
          ELSE SUM("IMPRESSIONS") / (SUM("PAGEVIEWS") / 1000) END AS "IPPV",
     CASE WHEN SUM("IMPRESSIONS") = 0 THEN NULL
          ELSE SUM("IMPRESSIONS") / (SUM("SESSIONS") / 1000) END AS "IMP/SESSION"
   FROM "ANALYTICS"."SIGMA_SCRATCH"."VIEW_EARNINGS_AND_ANALYTICS_BY_DAY_D19312C8622E487D891B72420987C00B_MAT"
   WHERE "DATE" BETWEEN DATEADD(day, -420, CURRENT_DATE()) AND CURRENT_DATE()
     AND "Site Name (SITE_EXTENDED)" = '{{site}}'
   GROUP BY "DATE"
   ORDER BY "DATE";
   ```

2. **Data Preparation & Visualization:**

   * **Line Graphs:** For each metric (`PUB_NET_EARNINGS`, `IMPRESSIONS`, `SESSIONS`, `PAGEVIEWS`, `CPM`, `PAGE_RPM`, `RPM`, `PPS`, `IPPV`, `IMP/SESSION`), generate a separate time-series line chart over the 420-day window.
   * **Labeling:** Title each chart with the metric name and include axis labels for date (x‑axis) and metric value (y‑axis).

   * **Recent Decline Check:** After plotting, compare the last 7 days of each metric to the preceding 7 days. Flag any metric whose average drops by more than 5%.
=======


3. **Scope:**

   * Analyze daily earnings trends to identify sudden drops or spikes in any of the metrics.
   * Detect anomalies by comparing current values to historical baselines (e.g., 7-day or 30-day averages).
   * Highlight days with significant percentage changes or values outside normal thresholds.

4. **Metrics to Highlight in Troubleshooting:**

   * **Total Revenue** (`PUB_NET_EARNINGS`)
   * **Impressions**, **Sessions**, **Pageviews**
   * **CPM**, **Page RPM**, **Session RPM**
   * **PPS** (pageviews per session), **IPPV** (impressions per pageview), **Imp/Session**
   * **Percent Change** vs. previous day and vs. rolling averages.

5. **Thresholds & Alerts:**

   * Flag days where any RPM metric falls below the 20th percentile of its past 30 days.
   * Flag days where volume metrics (revenue, impressions, sessions, pageviews) change by more than ±25% compared to a 7-day moving average.

6. **Recommended Analysis Steps:**
   a. **Load & Filter Data**

   * Execute the parameterized SQL for the last 420 days and the selected site.
   * Parse `DATE` as a date type.
     b. **Compute Rolling Baselines**
   * Calculate 7-day and 30-day rolling averages and percentiles for each metric.
     c. **Detect Anomalies**
   * Compare daily values against baselines; mark anomalies per thresholds.
     d. **Visualization & Summary**
   * Produce the line charts.
   * Annotate anomaly points on the graphs.
   * Generate a summary table with columns: `date`, each metric, `perc_change_*`, `is_anomaly_*`.
     e. **Insight Generation**
   * Summarize key anomaly dates and magnitude of deviations.
   * Recommend investigative actions (e.g., check tag implementation, ad server logs).

7. **Expected Outputs:**

   * **Dropdown UI** for site selection.
   * **Ten line charts** showing each metric over time, with anomalies highlighted.
   * **Summary Table** of flagged dates and metrics.
   * **Bullet-point Insights** describing trends, anomalies, and next steps.

*Follow this template whenever a user requests a performance line-graph analysis for a specific site over the last 420 days.*