Spaces:
Sleeping
Sleeping
File size: 5,430 Bytes
0555d94 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
### 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.*
|