Piqki Performance Reporting System

Automated Weekly Brief Spec — Last updated: 2026-02-21

Summary

The Piqki Performance Reporting System is an automated Monday morning brief that pulls data from GA4, Klaviyo, GoAffPro, and Shopify into a single Telegram message for Gin. It replaces manual data pulls and ensures Gin sees the right metrics every week without logging into anything.

1. Weekly Brief Format

The Monday morning Telegram message will look exactly like this:

📊 PIQKI WEEKLY PERFORMANCE — [DATE]

MER: [X.X]x (Revenue ÷ Ad Spend) → Target: ≥2.0x | Last week: [X.X]x

Contribution Margin: $[XX.XX]/order → Revenue - COGS - Ad Spend ÷ Orders

🏆 Top Performer:

Creative: "[Hook/Concept Name]"
CTR: [X.X]% | Spend: $[XXX]
[Winner emoji if >2.5% CTR]

📧 Klaviyo Revenue:

Last 7 days: $[X,XXX]
% of Total Revenue: [XX]%

🤝 Affiliate Revenue:

Last 7 days: $[XXX]
Active affiliates: [X]

📬 Email List:

Size: [X,XXX] subscribers
Weekly growth: +[XXX] ([X]%)

⚡ Kill/Scale Flags:

🔴 KILL: [Creative Name] — [X.X]% CTR at $[XX] spend (threshold: <0.8% CTR after $50)
🟢 SCALE: [Creative Name] — [X.X]% CTR (threshold: >2.5% CTR)

💡 MAX'S RECOMMENDATION: [One sentence — e.g., "Double budget on Concept C, kill Concept A, continue testing Concept B."]

2. Data Sources + How to Pull Each

2.1 GA4 (Google Analytics 4)

What we need:

Total revenue (last 7 days)
Traffic/conversion data by source
Ad performance correlation

API Endpoint:

GA4 Data API: runReport method
Dimensions: sessionSource, sessionMedium
Metrics: totalRevenue, sessions, conversions, purchaseRevenue

Field names:

totalRevenue / purchaseRevenue — total ecommerce revenue
sessions — total sessions
conversions — purchase conversions
conversionRate — sessions ÷ conversions

Time window: Last 7 days (dynamic, calculated at runtime)

Confidence: High — GA4 Data API is well-documented and stable.

Reference: Google Analytics 4 Ecommerce measurement (developers.google.com/analytics/devguides/collection/ga4/ecommerce)

2.2 Klaviyo (Email Marketing)

What we need:

Revenue attributed to email (last 7 days)
Total list size
Weekly list growth

API Endpoints:

List Size + Growth:

Endpoint: GET /api/lists — returns all lists with member counts
Metric: member_count for the main list
Growth calculation: Compare current count vs. 7 days ago

Revenue Attribution:

Endpoint: GET /api/metric-export — use the "Placed Order" metric
Alternative: GET /api/report with metric_id for conversion events
Filter: interval: daily, start_date: [7 days ago], end_date: [today]

Flow vs. Campaign breakdown:

Klaviyo's API supports attributed_message dimension to区分 flows vs. campaigns
For simplicity: aggregate total attributed revenue (flows + campaigns combined)

Confidence: Medium — Klaviyo's API is comprehensive but revenue attribution requires the correct metric ID. Must find the "Placed Order" metric ID in the specific Piqki Klaviyo account.

Reference: Klaviyo Developer Docs — Metrics API, Export API (developers.klaviyo.com)

2.3 GoAffPro (Affiliate Program)

What we need:

Affiliate sales revenue (last 7 days)
Number of active affiliates
Total affiliate orders

API Endpoint:

GoAffPro Admin API: GET /api/admin/affiliates/sales
Base URL: https://[store-name].goaffpro.com/api/admin/
Authentication: API key (generated in GoAffPro admin panel → Settings → Advanced → Access Tokens)

Specific endpoints:

/affiliates — list all affiliates with status
/affiliates/sales — sales data by affiliate (supports date filters)
/orders — all orders with affiliate attribution

Field names:

sale_amount — revenue from affiliate sales
commission — affiliate commission paid
order_count — number of orders

Date filter: Query parameters start_date and end_date (format: YYYY-MM-DD)

Confidence: Medium — GoAffPro API exists but documentation is limited. The Swagger UI (api.goaffpro.com/docs/admin/) provides the most reliable endpoint list.

Reference: GoAffPro API Documentation (docs.goaffpro.com), Swagger UI (api.goaffpro.com/docs/admin/)

2.4 Shopify (Orders + Revenue)

What we need:

Total order revenue (last 7 days)
Number of orders
Average order value (AOV)

API Endpoint:

Shopify Admin API: GET /admin/api/2024-01/orders.json
GraphQL alternative: orders query with totalPrice, totalLineItemsPrice

Query parameters:

status=any (include all orders)
created_at_min=[7 days ago] — filter by date
limit=250 — paginate if needed
fields=id,total_price,created_at,line_items — specify fields

Field names:

total_price — total order amount (includes shipping, taxes)
subtotal_price — product total only
total_line_items_price — sum of line items
order_number — readable order ID

Calculations:

Revenue = sum of all total_price in date range
Order count = number of orders
AOV = Revenue ÷ Order count

Confidence: High — Shopify API is mature, well-documented, and stable.

Reference: Shopify Admin API — Orders (shopify.dev/docs/api/admin-graphql/latest/queries/orders)

2.5 Meta Ads (via GA4 or Meta)

What we need:

Total ad spend (last 7 days)
CTR per creative
Top performing creative

Options:

Option A — GA4 (recommended):

GA4 acquisition data includes sessionSource = 'google/cpc' or sessionSource = 'facebook/cpc'
Metrics: adSpend (if available), sessions, conversions
Limitation: GA4 may not have exact ad spend data without Google Ads integration

Option B — Meta Ads API:

Endpoint: GET /v18.0/[ad-account-id]/insights
Fields: spend, impressions, clicks, ctr, cpc
Breakdown: campaign_name, ad_name
Time range: time_range={'since':'[7 days ago]','until':'[today]'}

Confidence: Medium — Meta API requires a Meta app with ads_read permission. Gin must grant access.

Reference: Meta Marketing API — Insights Edge (developers.facebook.com/docs/marketing-api/insights)

3. OpenClaw Skill Spec

3.1 Skill Structure

skills/
├── piqki-weekly-brief/
│   ├── SKILL.md          # This file
│   ├── fetch_data.py      # API calls to all platforms
│   ├── format_message.py # Telegram message formatting
│   └── skill.yaml        # OpenClaw skill definition

3.2 Cron Job Payload

skill: piqki-weekly-brief
trigger:
  type: cron
  schedule: "0 9 * * 1"  # Every Monday at 9am PT
  timezone: America/Los_Angeles

env:
  GA4_PROPERTY_ID: [env var]
  GA4_CLIENT_EMAIL: [env var]
  GA4_PRIVATE_KEY: [env var]
  KLAVIYO_PRIVATE_API_KEY: [env var]
  GOAFFPRO_API_KEY: [env var]
  SHOPIFY_STORE: piqki
  SHOPIFY_ACCESS_TOKEN: [env var]
  TELEGRAM_CHAT_ID: [env var]
  TELEGRAM_BOT_TOKEN: [env var]

3.3 Execution Flow

Calculate date range: Today - 7 days
Fetch GA4 data: Revenue, sessions, conversions
Fetch Klaviyo data: List size, revenue attribution
Fetch GoAffPro data: Affiliate sales, active count
Fetch Shopify data: Total orders, revenue, AOV
Fetch Meta ads: Spend, CTR per creative
Calculate derived metrics:
- MER = Total Revenue ÷ Ad Spend
- Contribution Margin = (Revenue - COGS - Ad Spend) ÷ Order Count
- (COGS estimated at $8/unit based on financial model)
Format Telegram message: Using format in Section 1
Send message: Via Telegram Bot API
Log results: Store in workspace for historical reference

3.4 Error Handling

Scenario	Response
One API fails	Send partial brief with available data. Note: "⚠️ [Platform] unavailable — using [date] data"
All APIs fail	Send error message to Telegram: "❌ Weekly brief failed — APIs unreachable. Check manually."
Partial data (no sales)	Send: "⚠️ No sales data this week — possible tracking issue"
Rate limit hit	Retry once after 30s. If still failing, skip and note in message.

4. Setup Requirements

4.1 API Credentials Needed

Platform	Credential	How to Get
GA4	Service Account	Google Cloud Console → IAM → Create Service Account → Generate JSON key
GA4	Property ID	GA4 Admin → Property Settings → Property ID
Klaviyo	Private API Key	Klaviyo → Account → API Keys → Create Private API Key
GoAffPro	API Key	GoAffPro Admin → Settings → Advanced → Access Tokens → New API Key
Shopify	Admin API Access Token	Shopify Admin → Settings → Apps → Develop Apps → Create App → Configure Admin API scopes
Meta	Ad Account ID	Meta Business Manager → Ad Accounts
Meta	Access Token	Meta for Developers → Marketing API → Access Token (or use long-lived token)
Telegram	Bot Token	@BotFather on Telegram → /newbot
Telegram	Chat ID	@userinfobot or forward message to @my_id_bot

4.2 Permissions Required

GA4:

Read access to the Piqki GA4 property
Service account must be added in GA4 Admin → Property Access Management

Klaviyo:

Read access to metrics, lists, campaigns, flows
Private API key scope must include: metrics:read, lists:read

GoAffPro:

Admin-level API key (not affiliate-level)
Full access to sales and affiliate data

Shopify:

Admin API scopes: read_orders, read_products, read_analytics
Note: Shopify access pending — Gin has not yet granted (see index.html "Shopify Invite" pending)

Meta:

Ads Read permission (ads_read)
Business Manager access

4.3 What Gin Needs to Do (One-Time Setup)

Create GA4 Service Account:
- Go to Google Cloud Console
- Create service account, download JSON key
- Add service account email to GA4 property access
Generate Klaviyo API Key:
- Klaviyo → Account → API Keys → Create Private API Key
- Copy and store securely
Generate GoAffPro API Key:
- GoAffPro → Settings → Advanced → Access Tokens → New API Key
- Copy and store securely
Set up Shopify Admin App:
- Shopify → Settings → Apps → Develop Apps → Create App
- Configure Admin API scopes (read_orders, read_analytics)
- Install app, copy access token
Set up Telegram Bot:
- Message @BotFather on Telegram
- Create new bot, get bot token
- Start chat with bot, get chat ID
Add environment variables to OpenClaw:
- Store all credentials in secure env var storage
- Test each API connection before enabling cron

5. Gin Decides Flags

The following decisions are needed before this system can be fully built:

5.1 Kill/Scale Thresholds

The spec uses these defaults from the Creative Testing System:

Kill threshold: <0.8% CTR after $50 spend
Scale threshold: >2.5% CTR

Gin decides:

Are these thresholds correct for Piqki?
Should we add additional metrics (e.g., ROAS threshold)?

5.2 Contribution Margin Calculation

Current formula: (Revenue - COGS - Ad Spend) ÷ Orders

COGS estimated at $8/unit (from financial model)
Ad spend is weekly total

Gin decides:

Is this formula correct?
Should we include shipping costs? Taxes?
Should we use subtotal_price instead of total_price?

5.3 Meta Ads Integration

Two options:

Option A: Pull ad data from GA4 (simpler, less accurate)
Option B: Pull directly from Meta Ads API (accurate, requires setup)

Gin decides:

Which approach for ad spend data?
Do we need per-creative CTR breakdown?

5.4 Missing Data Fallback

If any API is unavailable:

Option A: Skip that metric, show "N/A"
Option B: Use last week's data with "estimate" label

Gin decides:

Preferred fallback behavior?

6. Metrics Definitions

Metric	Formula	Source
MER	Total Revenue ÷ Total Ad Spend	Shopify + Meta
Contribution Margin/order	(Revenue - COGS - Ad Spend) ÷ Orders	Shopify + Meta + Est. COGS
Klaviyo Revenue	Attributed revenue (7 days)	Klaviyo API
Klaviyo % of Total	Klaviyo Revenue ÷ Total Revenue	Calculated
Affiliate Revenue	GoAffPro sales (7 days)	GoAffPro API
Email List Size	Total subscribers	Klaviyo API
Weekly List Growth	(Current - 7 days ago) ÷ 7 days ago	Klaviyo API
Top Creative	Highest CTR at ≥$50 spend	Meta/GA4
Kill Flag	CTR <0.8% after $50 spend	Meta
Scale Flag	CTR >2.5%	Meta

7. Sources & Validation

[1] GA4 Ecommerce API — revenue tracking Source: Google Analytics 4 Developer Documentation (developers.google.com/analytics/devguides/collection/ga4/ecommerce) Status: High confidence — Official Google documentation. Standard ecommerce implementation.

[2] Klaviyo Metrics API — revenue attribution Source: Klaviyo Community discussions + Developer Docs Status: Medium confidence — API exists and is documented. Requires finding specific metric IDs in Piqki account. Use /export endpoint with "Placed Order" metric.

[3] GoAffPro API — affiliate sales Source: GoAffPro documentation (docs.goaffpro.com) + Swagger UI (api.goaffpro.com/docs/admin/) Status: Medium confidence — API exists. Documentation is sparse. Swagger UI is the most reliable reference.

[4] Shopify Orders API Source: Shopify Admin API Documentation (shopify.dev/docs/api/admin-graphql/latest/queries/orders) Status: High confidence — Mature, well-documented API.

[5] MER (Marketing Efficiency Ratio) Source: DTC practitioner methodology — popularized by Taylor Holiday / Common Thread Collective Status: Industry standard — Widely used in DTC circles. Target ≥2.0 is directional; actual target depends on margin structure.

[6] Kill/Scale thresholds Source: Piqki Creative Testing System (piqki-creative-testing-system.md) Status: Internal standard — Based on DTC best practices. $50 minimum spend threshold ensures statistical significance.

8. Implementation Notes

COGS: Use $8/unit estimate from Piqki Financial Model v4 (confirmed with Gin: unit COGS includes glue, applicator, packaging)
Currency: All amounts in USD
Timezone: All dates/times in America/Los_Angeles (Gin's timezone)
Historical data: Store each week's brief in projects/piqki-weekly-briefs/ for trend analysis
Manual override: Skill can be triggered manually via command for mid-week checks

9. Open Questions

Shopify access: Gin has not yet accepted the Shopify invite. System cannot fetch order data until access is granted.
Meta ads access: No Meta Business access yet (see index.html "Meta Business Invite" pending).
GoAffPro access: No GoAffPro admin access yet (see index.html "GoAffPro Access" pending).
AOV data: Should we include average order value trend? (Added to brief if useful.)
Week-over-week comparison: Should we show % change vs. previous week for each metric?

Document ends. Next step: Build the OpenClaw skill once Gin approves setup and provides credentials.