PGE – Quarterly Personalized URL & Click Counts Report

Purpose

This Knowledge Base article provides Support Team with the steps, data definitions, process flow, and output requirements for generating the PG&E Quarterly Personalized URL & Click Counts Report.

This KB should be followed whenever PG&E requests quarterly or monthly reporting for Personalized URL performance.

---

1. Reporting Objective

Provide PG&E with:

• Personalized URL Counts
• Personalized URL Click Counts

 For quarterly reporting (example period):
October 1, 2025 – December 31, 2025 (Q4 2025)

---

2. Data Source

• Database: billing
• Collection: counts

Support Team engineers must use this collection for all URL and click metrics related to PG&E.

---

3. Protocol Definitions (IMPORTANT)

Support Team MUST use the following mapping when interpreting the data returned from the query:

Protocol Value	Meaning	Use In Report As
"url"	Personalized URL count	URL Counts
"urlImpression"	Personalized URL clicks	URL Click Counts

---

4. MongoDB Query Used

Below is the sample aggregation query used to Personalized URL and click counts raw data:

[
 {
   $match:
     /**
      * query: The query in MQL.
      */
     {
       paid: "250",
       protocol: {
         $in: ["url", "urlImpression"]
       },
       year: 2025,
       month: {
         $lt: 9,
         $gt: 5
       }
     }
 },
 {
   $group:
     /**
      * _id: The id of the group.
      * fieldN: The first field name.
      */
     {
       _id: {
         year: "$year",
         month: "$month",
         day: "$day",
         protocol: "$protocol"
       },
       sum: {
         $sum: "$counter"
       }
     }
 },
 {
   $sort:
     /**
      * Provide any number of field/order pairs.
      */
     {
       year: 1,
       month: 1,
       day: 1
     }
 }
]

Important

month: {

         $lt: 9,

         $gt: 5

       } needs to be modified in the query based on the requested dates by customer.

---

5. Data generated using the above MQL query.

File:
 

RawReport.csv
 

This file contains raw daily data and aggregated data for Q4 2025. 

---

6. Steps Support Team Must Follow Before Sending to Client

Support Team must prepare a clean summary report using the Excel file.

Step‑by‑Step Requirements

Import the Raw Data

1. Download the raw CSV from the database query (example: RawReport.csv).
2. Open in Excel.
3. Verify columns: year, month, day, protocol, sum.

How Support Team Prepares the Client-Facing Report From Raw MQL Output

When the MQL query is executed, Support Team will receive a raw report containing daily records with these fields:

• year
• month
• day
• protocol (url or urlImpression)
• sum (daily count)

Support Team must convert this raw output into the formatted quarterly report delivered to PG&E.

Follow the steps below:

---

1. Import the Raw Data

1. Download the raw CSV from the database query (example: RawReport.csv).
2. Open in Excel.
3. Verify columns: year, month, day, protocol, sum.

---

2. Apply Protocol Mapping

Use the required definitions:

• url → URL Counts
• urlImpression → URL Clicks

Create two new calculated columns or pivot table fields accordingly.

---

3. Create a Pivot Table to Produce Monthly Totals

1. Insert Pivot Table using the raw data.
2. Configure fields as follows:

Rows:

• month
• day

Columns:

• protocol

Values:

• sum (set to SUM)
• Rename pivot column headers:
  • url → URL counts
  • urlImpression → URL clicks
  • month should display October, November, December
  • Daily rows (1, 2, 3, … 31) should appear under each month

This produces the layout identical to the attached client report.

---

4. Ensure Monthly Summary Rows Appear

In the Pivot Table:

• Each month (October, November, December) automatically displays a monthly subtotal
• These subtotals are your monthly totals

Verify they match the expected (example) values:

Month	URL counts	URL clicks
October	1935053	441552
November	1639195	359332
December	3638987	874208

---

5. Add the Grand Total Row

At the bottom of the pivot, Excel will generate:

Grand Total

7213235

1675092

This must be included in the final client-facing report.

---

6. Format the Output to Match the Attached Report

Make it look visually identical to the attached PG&E report. You can also edit the below report manually to fill the counts and share with client.

PGE_Click_Report_Q4_2025.xlsx

---

7. Deliver the Final File

After validating totals and formatting:

• Export the workbook as .xlsx
• Attach to the PG&E ticket or delivery channel.

---

7. Final Monthly Summary (Expected Values)

Support Team should verify these numbers before sending to the client:

Month	URL Counts	URL Click Counts
October	1935053	441552
November	1639195	359332
December	3638987	874208
Q4 Total	7213235	1675092

---

8. Ticket References

• Internal Ticket: ECS‑7313
• Customer Ticket: https://goconvey.zendesk.com/agent/tickets/22305

---

9. Deliverable

Support Team must deliver to the client:

Final File:

PGE_Click_Report_Q4_2025.xlsx (updated and validated)

This includes:

• Monthly totals
• Daily Counts
• Q4 totals
• Click/Count mapping