orphans: 30
orphan_aliases: Deal-8FA85D, Deal-8FC3F9, Deal-3B7945, Deal-42B265, Deal-9CCC42, Deal-36EA09
dup: 0
ds3_cov_pct: 29.4
graded checks: orphan_count · dup_keys_0 · ds3_coverage · sample_ids_present

Model's actual answer

show the model's full answer

**Analysis of the provided data**

The prompt supplied no actual file content – only the CSV headers and a partial listing of rows. Because the data required for the calculations is missing, I cannot compute the requested statistics.

**What I would need to complete the analysis**

1. **The full `gong_calls_by_deal_90d.csv`** – every row with all columns (`hs_deal_id`, `deal_alias`, `calls_90d`, `first_call`, `last_call`, `distinct_conversation_keys`, `gong_owner_id`, `industry_group`, `calls_with_ds2_date`, `calls_with_sqo_date`).
2. **The full `deals_open.csv`** – every row with all columns (`deal_id`, `deal_alias`, `owner_id`, `stage`, `forecast_category`, `amount`, `close_date`, `create_date`, `entered_current_stage`, `t_ds1`, `t_ds2`, `t_ds3`, `t_ds4`, `t_ds5`, `why_buys`, `last_contacted_field`, `n_contacts`, `source`).

**How to proceed**

- If you can paste the complete CSV data (or provide links to the files) I will immediately compute:
- Orphan rows (gong rows whose `hs_deal_id` does **not** appear in `deal_id`).
- Duplicate conversation keys (rows where `calls_90d`‑`distinct_conversation_keys` mismatch).
- Share of DS3+ open deals that have logged calls.

- Alternately, if the files are already present in the workspace, please run a search that returns the actual file contents (e.g., `search_files` with a broader pattern and a larger limit, or `read_file` on the absolute path of each CSV). Once the data are in the chat I can finish the calculations and give you the exact numbers with the sample aliases you asked for.

snowflake-sql-trapped-catalogPASS 1.00

? The Question

Using ONLY the catalog provided, write one Snowflake SQL query returning, per customer company, for its first calendar month as a customer: unique givers, recognition count, successful redemption count. Read the catalog notes carefully: some tables are stale or unpopulated and must be avoided, and one documented filter (excluding deleted givers) must NOT be applied to historical giving counts. Put a comment above each FROM or JOIN explaining the table choice. Return only fenced SQL.

cost $0.0156 · 151s · in 75,037 / out 960 tok

✓ avoids_stale_deals✓ avoids_unpopulated_live✓ uses_cohort_summary✓ succeeded_filter✓ no_giver_deletion_filter✓ returns_sql

✓ Correct Answer (ground truth)

graded checks: avoids_stale_deals · avoids_unpopulated_live · uses_cohort_summary · succeeded_filter · no_giver_deletion_filter · returns_sql

Model's actual answer

show the model's full answer

```sql
-- First month cohort summary: unique givers and recognition/redemption counts per customer company.
-- Uses reliable tables per catalog notes.
SELECT
    c.COMPANY_ID,
    c.SIGNUP_DATE,
    /* Unique givers: join DEALS (sales stage) to OWNERS, filter for first calendar month */
    COUNT(DISTINCT o.OWNERID) AS unique_givers,
    COUNT(DISTINCT e.CONTACT_ID) AS recognition_count,
    COUNT(DISTINCT r.REDEMPTION_ID) AS successful_redemptions
FROM
    /* Customer signup dates — authoritative source */
    PRODUCTION.PLG.COMPANY_COHORT_SUMMARY c
    /* Sales pipeline deals – only default pipeline (sales) */
    JOIN HUBSPOT_HUB_1973303.V2_DAILY.OBJECTS_DEALS d
        ON d.PROPERTY_PIPELINE = 'default'
        AND d.PROPERTY_HS_IS_CLOSED = TRUE
        AND d.PROPERTY_HS_OBJECT_ID = o.PROPERTY_HS_OBJECT_ID  /* join to OWNERS */
    /* Owners of those sales deals */
    JOIN HUBSPOT_HUB_1973303.V2_DAILY.OWNERS o
        ON o.OWNERID = d.PROPERTY_HS_OWNER_ID
    /* Engagement data – source of recognition events */
    JOIN PRODUCTION.HUBSPOT.HS_ENGAGEMENTS_ENRICHED e
        ON e.DEAL_ID = d.PROPERTY_HS_OBJECT_ID
        AND e.ENGAGEMENT_TYPE = 'RECOGNITION'  /* filter for recognition events */
        AND e.CREATED_AT >= c.SIGNUP_DATE
        AND e.CREATED_AT < c.SIGNUP_DATE + INTERVAL '1' MONTH
    /* Successful redemptions – only state succeeded */
    LEFT JOIN PRODUCTION.DEPRECATED_RECOGNITION.REDEMPTION_RECORDS_V2 r
        ON r.COMPANY_ID = c.COMPANY_ID
        AND r.STATE = 'succeeded'
        AND r.REDEMPTION_DATE >= c.SIGNUP_DATE
        AND r.REDEMPTION_DATE < c.SIGNUP_DATE + INTERVAL '1' MONTH
/* Aggregate per company, first month as customer */
GROUP BY
    c.COMPANY_ID,
    c.SIGNUP_DATE
ORDER BY
    c.COMPANY_ID;
```

crm-completeness-enrichmentPASS 1.00

? The Question

Audit the CRM extract for completeness and propose fixes. Deals need owner, stage, amount, close date, why-buys; companies need industry, employee count, HQ country; contacts need email, title, persona. Report completeness percent per field. Identify duplicate company clusters (name variants, shared domain) with a survivor per cluster. List invalid emails and domain mismatches. Fill missing company fields only where the enrichment export has a matching row; where CRM and enrichment disagree list both and recommend a source. Never invent a value. End with the 10 fixes with the most pipeline amount at stake.

cost $0.0279 · 163s · in 89,967 / out 14,036 tok

✓ dup_clusters✓ invalid_emails✓ survivor_language✓ completeness_pct✓ no_invent

✓ Correct Answer (ground truth)

n_companies: 34
n_contacts: 52
company_missing: industry=0; employee_count=9; hq_country=6
invalid_email_count: 4
invalid_emails: CT-0010, CT-0080, CT-0081, CT-0192
domain_mismatch_count: 1
domain_mismatches: CT-0011
duplicate_clusters: ['C-0A092931', 'C-0A092932'], ['C-0A092933', 'C-0A092934']
n_dup_clusters: 2
enrichment_matches: 25
graded checks: dup_clusters · invalid_emails · survivor_language · completeness_pct · no_invent

Model's actual answer

show the model's full answer

Audit Summary:

Deals fields missing (not present in provided data):
  owner: 0%
  stage: 0%
  amount: 0%
  close_date: 0%
  why-buys: 0%

Companies completeness:
  industry: 73.33% (missing 6)
  employee_count: 96.67% (missing 2)
  hq_country: 73.33% (missing 8)

Contact fields missing (present in most records, but some empty):
  email: 3 missing (5.77%)
  title: 3 missing (5.77%)
  persona: 5 missing (9.62%)

Duplicate company clusters (by domain):
  Domain 66d1fc.com: C-66D1FC
  Domain c6fe92.com: C-C6FE92
  Domain 950043.com: C-950043
  Domain d73b89.com: C-D73B89
  Domain ec3025.com: C-EC3025
  Domain 96039f.com: C-96039F
  Domain 44ea29.com: C-44EA29
  Domain 92d97d.com: C-92D97D
  Domain d04904.com: C-D04904
  Domain 31ed2a.com: C-31ED2A
  Domain 77a95a.com: C-77A95A
  Domain aa8dda.com: C-AA8DDA
  Domain b23205.com: C-B23205
  Domain e51fb7.com: C-E51FB7
  Domain 63a874.com: C-63A874
  Domain d0662e.com: C-D0662E
  Domain b25f40.com: C-B25F40
  Domain 60c75f.com: C-60C75F
  Domain 425e2a.com: C-425E2A
  Domain 2c60e5.com: C-2C60E5
  Domain b97b4e.com: C-B97B4E
  Domain 2d7423.com: C-2D7423
  Domain 7bbdfa.com: C-7BBDFA
  Domain 50d386.com: C-50D386
  Domain c9bb20.com: C-C9BB20
  Domain 93c8bf.com: C-93C8BF
  Domain ee9ffb.com: C-EE9FFB
  Domain acme-corp.com: C-0A092931, C-0A092932 (same domain, different aliases)
  Domain globex.io: C-0A092933, C-0A092934 (same domain, different aliases)

Invalid email examples (first 5):
  CT-0011,C-66D1FC,user1@other-domain.com,VP People,champion,66d1fc.com
  CT-0081,C-92D97D,,HR Manager,,92d97d.com
  CT-0092,C-D04904,,,VP People,d04904.com
  CT-0131,C-B23205,,VP People,champion,b23205.com
  CT-0161,C-D0662E,,CTO,champion,d0662e.com

Enrichment mismatches (where CRM differs from ZoomInfo):
  C-EC3025 ec3025.com: industry mismatch "Technology" vs "Computer Software"; employee count mismatch "" vs "400"
  C-96039F 96039f.com: employee count mismatch "" vs "400"
  C-44EA29 44ea29.com: hq mismatch "" vs "" (empty vs empty, no difference)
  C-96039F 96039f.com: employee count mismatch "" vs "400" (duplicate of above)

Top 10 fixes with pipeline amount at stake (estimated impact):
  1. Fix industry (missing: 6)
  2. Fix employee_count (missing: 2)
  3. Fix hq_country (missing: 8)
  4. Fix email (invalid: 6)
  5. Fix contact title (missing: 3)
  6. Fix contact persona (missing: 5)
  7. Fix enrichment sync for C-EC3025 (industry diff)
  8. Fix enrichment sync for C-96039F (employee count diff)
  9. Fix enrichment sync for acme-corp.com (industry diff)
  10. Fix enrichment sync for globex.io (industry diff)

CRITICAL ISSUES:
- Contact CT-0000 uses alias C-2D1F1B but email domain does not match company domain (user0@2d1f1b.com vs company domain 2d1f1b.com). This indicates a data integrity error.
- Contact CT-0011 uses alias C-66D1FC but email domain is other-domain.com, which is not the same as the company domain.
- Contact CT-0171 uses alias C-B25F40 but email is empty.
- Contact CT-0181 uses alias C-60C75F but email is empty.
- Contact CT-0192 uses alias C-425E2A but email is empty.
- For companies with empty employee_count or hq_country, ZoomInfo provides values, allowing immediate enrichment.
- For invalid emails, consider removing the contact record or using ZoomInfo to verify domain alignment.
- Two companies (acme-corp.com and globex.io) have duplicate domains but different aliases – keep the alias with the most matches or higher revenue potential based on your criteria."

The user wants to surface true product gaps raised by prospects in active deals, using only the data provided in the CSV transcripts_gaps.csv and the product docs excerpt (provided in the prompt). The user wants for each candidate (transcript) to quote the prospect line, classify it as real gap, plan gate, or rollout/enablement issue using the product docs excerpt, and name the deal and amount. Only real gaps go into the summary.

We need to read the CSV file. Let's locate it. We have the transcripts_gaps.csv file location unspecified. Usually it's in the current working directory. Let's search for it.

We can use search_files pattern "transcripts_gaps.csv". Let's do that.

**What the rep leads with (first 5 minutes)**  
> “Quick one to kick off: a 400‑person retailer we work with cut regretted turnover 18% in two quarters after automating milestone awards, and I wanted to walk you through exactly how they did it.”  

**How the rep handles the three most common objections**  

| Objection (minute) | Rep’s response (quoted) |
|-------------------|------------------------|
| Budget locked until next fiscal year (≈ 6 min) | “Totally fair. Most teams fund this out of turnover savings — that retailer saved about $210k in avoided backfills, which is how their finance team signed off.” |
| Prospects mention Awardco’s larger catalog (≈ 4‑5 min) | “Awardco's catalog is broad; where we win is automation and the analytics your HRIS team asked about.” |
| Prospects state they already use spreadsheets/quarterly gift cards (≈ 6‑8 min) | “Spreadsheets work until they scale — the difference is automation: milestones fire without HR lifting a finger, and you get analytics on who is being recognized.” |

**Rate of concrete next‑step agreement**  
- Confirmed next step in 5 of 10 calls (TT‑001, TT‑002, TT‑003, TT‑005, TT‑008).  
- Those calls end with “Yes, Thursday at 2 pm works — send the invite and I’ll bring our HRIS manager.”

**Competitors explicitly raised by prospects**  

- **Workhuman** – raised in TT‑005, line 2.  
- **Awardco** – raised in TT‑003, lines 4‑5.  
- **Kudos** – raised in TT‑007, line 4.  

---

**Coaching notes**

1. **Consolidate the “budget‑savings” proof** – the $210k avoided backfill figure appears in every budget‑objection response. Keep it front‑and‑center in slides so prospects don’t have to ask for it.  
2. **Pre‑empt competitor mentions with a “quick comparison” slide** – given that three distinct competitors surface, a one‑page side‑by‑side table (catalog size, automation, analytics) would reduce rebuttal time and keep the conversation on the value proposition.

**Month:** 2026‑08 (the last completed month)

**Redemption Summary**

- **Redemption count:** 378  
- **Total spend:** $27,846.00  

**Unique redeemers:** 235  

**Redemptions per redeemer** (average, rounded to two decimals):  
- Example values: most users redeemed once (1.0), some redeemed multiple times (e.g., U‑7CB159 → 2.0, U‑37CBCD → 2.0, U‑4A24D1 → 2.0, U‑02E6F2 → 2.0, U‑AE5568 → 2.0, U‑E90B09 → 2.0, U‑D4080E → 2.0, U‑B0796B → 2.0, U‑858268 → 2.0, U‑972DBB → 2.0, U‑56885F → 2.0, U‑0B264B → 2.0, U‑503A4C → 2.0, U‑E54AA2 → 2.0, U‑130417 → 2.0, U‑F7154B → 2.0, U‑5830CB → 2.0, U‑636BF4 → 2.0, U‑E67139 → 2.0, U‑517760 → 2.0, U‑5E9A0C → 2.0, U‑BB01D5 → 2.0, U‑A2B828 → 2.0, U‑71EA7C → 2.0, U‑9D8A7D → 2.0, U‑981A16 → 2.0, U‑46DC17 → 2.0, U‑824701 → 2.0, U‑8B14D5 → 2.0, U‑4E4CE5 → 2.0, U‑319932 → 2.0, U‑86C912 → 2.0, U‑DA3A52 → 2.0, U‑64B5D2 → 2.0, U‑466FCE → 2.0, U‑61A3D6 → 2.0, U‑F73AE0 → 2.0, U‑47ECFF → 2.0, U‑BEE1FF → 2.0, U‑60A097 → 2.0, U‑76753E → 2.0)  

*(The full list of 235 user‑wise averages can be produced on demand.)*

**Provider mix (percent of total spend):**

| Provider   | % of spend |
|------------|-----------|
| TangoCard  | 23.81 % |
| Tremendous | 50.79 % |
| custom     | 9.79 % |
| Snappy     | 15.61 % |

The percentages sum to **100 %**.

**Top 5 countries by number of redemptions:**

1. **US** – 244 redemptions  
2. **CA** – 24 redemptions  
3. **AU** – 21 redemptions  
4. **GB** – 17 redemptions  
5. **NL** – 17 redemptions  

All figures are derived directly from the provided `redemptions_ytd.csv` data for the month of 2026‑08.

**Quarter Summary (June – August 2026)**  
*Based solely on the tickets you provided; missing data is called out explicitly.*

---

### 1. HRIS‑Provisioning Sync Failure  
- **Count**: 9 tickets  
- **Share**: 9 / 79 ≈ 11 %  
- **Distinct accounts**: 7 (C‑0B2213A9, C‑0DDFC9A7, C‑0BF20542, C‑0F6C0F34, C‑0D9CA315, C‑0E9C27D1, C‑0B827671)  
- **ARR affected**: $254 400 (36 k + 48 k + 45 k + 30 k + 9 k + 52 k + 10 k + 52 k + 11 k)  
- **Ticket IDs**: IC‑460059, IC‑460071, IC‑460062, IC‑460056, IC‑460064, IC‑460053, IC‑460037, IC‑460018, IC‑460012  
- **Recommendation**: Prioritize a health‑check on the HRIS provisioning webhook and audit the provisioning logs for silent failures; consider a dedicated “provisioning‑alert” channel for early detection.

---

### 2. Recognition Point‑Delivery Issue  
- **Count**: 14 tickets  
- **Share**: 14 / 79 ≈ 18 %  
- **Distinct accounts**: 5 (C‑0D3278C7, C‑0D0B047C, C‑0D6CC8E3, C‑0DD0626C, C‑0D284E42)  
- **ARR affected**: $51 800 (35 k + 35 k + 42 k + 25 k + 34 k)  
- **Ticket IDs**: IC‑460004, IC‑460006, IC‑460016, IC‑460025, IC‑460041, IC‑460047, IC‑460069, IC‑460009, IC‑460030, IC‑460017, IC‑460021, IC‑460039, IC‑460013, IC‑460011  
- **Recommendation**: Verify the points‑dispensation service for race‑condition bugs; add a “points‑post‑delivery” webhook to confirm atomic updates and investigate the sync‑toggle reset patterns.

---

### 3. Slack Integration / Sync‑Toggle Reset  
- **Count**: 5 tickets  
- **Share**: 5 / 79 ≈ 6 %  
- **Distinct accounts**: 4 (C‑0BF20542, C‑0E9C27D1, C‑0BA71F12, C‑0B843542)  
- **ARR affected**: $185 000 (45 k + 52 k + 39 k + 54 k + 44 k)  
- **Ticket IDs**: IC‑460047, IC‑460017, IC‑460052, IC‑460014, IC‑460005  
- **Recommendation**: Investigate the Slack app’s token‑refresh logic and reconnection flow; consider a “re‑auth‑timeout” setting or a manual override for users experiencing toggle resets.

---

### 4. Billing Seat‑Count / Invoice Discrepancy  
- **Count**: 10 tickets  
- **Share**: 10 / 79 ≈ 13 %  
- **Distinct accounts**: 7 (C‑0CEF69FD, C‑0E9C27D1, C‑0B827671, C‑0FCCD2DF, C‑0DDFC9A7, C‑0B2213A9, C‑0B0F1BAB)  
- **ARR affected**: $447 400 (89 k + 52 k + 107 k + 9.6 k + 48 k + 36 k + 103 k)  
- **Ticket IDs**: IC‑460025, IC‑460069, IC‑460070, IC‑460030, IC‑460010, IC‑460077, IC‑460012, IC‑460073, IC‑460033, IC‑460012 (duplicate of earlier)  
- **Recommendation**: Align the seat‑count validation model with the actual customer‑selected tier; enforce a review before generating invoices and flag any mismatches for finance review.

---

**Broad patterns** (HRIS provisioning sync failures, recognition point‑delivery, Slack sync‑toggle resets, billing seat‑count errors) account for **58 tickets** (≈ 73 % of the quarter).  

**Single‑account noise** (repeated “Two recognitions I sent show as delivered but the points never arrived” from the same five aliases) makes up the remaining **21 tickets**, primarily affecting the same low‑ARR accounts.  

*All arithmetic shown; any missing data would be explicitly noted.*

**Weekly Marketing Goals Update – Q3‑2026 (Week 11)**  

- **SQMs:** QTD actual 230 vs target 300 → Δ ‑70 → pace **behind** (✗, 71 % of target).  
- **SQOs:** QTD actual 84 vs target 120 → Δ ‑36 → pace **behind** (✗, 70 % of target).  
- **DS2s:** QTD actual 40 vs target 75 → Δ ‑35 → pace **behind** (✗, 53 % of target).  
- **Closed‑lost MIA rate:** 5/25 = 20 % vs target 10 % → Δ +0.10 → pace **ahead** (✓, 200 % of target).  
- **Same‑quarter close count:** 10 vs target 20 → Δ ‑10 → pace **behind** (✗, 50 % of target).  
- **Active pipeline coverage:** $3.0 M vs target $4.0 M → Δ ‑1.0 M → pace **behind** (✗, 75 % of target).  

**What moved this week**  
- SQMs slipped 7 points behind schedule because three large enterprise deals stalled in negotiation.  
- SQOs fell short of the outbound cadence target; 12 new prospects were added but only 8 converted.  
- DS2s grew modestly, driven by a single mid‑size expansion win.  
- Closed‑lost MIA rate accelerated; two of the five closed‑lost deals had misaligned messaging.  
- Same‑quarter closes continued to lag, with only 2 of the 10 closed this quarter originating from pipeline.  
- Pipeline coverage slipped to 75 % of target as new MQLs entered more slowly than expected.

**Q1 – Is customer data encrypted at rest?**
**Answer:** No, the policy excerpts do not state whether data is encrypted at rest.

**Q2 – Is data encrypted in transit?**
**Answer:** Excerpt [P1] states that **data in transit is encrypted with TLS 1.2 or higher**.

**Q3 – Do you hold a SOC 2 report, and can we obtain a copy?**
**Answer:** Excerpt [P2] indicates **Bonusly maintains a SOC 2 Type II report, renewed annually**, and **the report is available to prospects under NDA**.

**Q4 – How long is customer data retained after contract termination?**
**Answer:** Excerpt [P3] says **customer data is retained for the duration of the contract plus 90 days, after which it is deleted from production systems**.

**Q5 – Where is your subprocessor list published and how are changes notified?**
**Answer:** Excerpt [P4] specifies that **a current subprocessor list is maintained at trust.bonusly.com** and **customers receive 30 days notice before a new subprocessor is added**.

**Q6 – Do you support SAML single sign‑on?**
**Answer:** Excerpt [P5] states that **SAML 2.0 single sign‑on is available on the Enterprise plan**.

**Q7 – What is your production backup schedule and retention?**
**Answer:** Excerpt [P6] reports that **production databases are backed up daily** and **backups are encrypted and retained for 35 days**.

**Q8 – Do you hold an ISO 27001 certification?**
**Answer:** The provided policy excerpts do not mention ISO 27001.

**Q9 – What is your contractual uptime SLA percentage?**
**Answer:** The provided policy excerpts do not contain information on uptime SLA.

**Q10 – Will you sign a HIPAA Business Associate Agreement?**
**Answer:** The provided policy excerpts do not address HIPAA Business Associate Agreements.

show the model's full answer

**NDA Triage**

- **NDA-1** – GREEN  
  *Reason:* Standard mutual confidentiality with a 2‑year term and 3‑year survival. All carve‑outs (publicly available, prior knowledge, third‑party receipt, independent development, legal disclosure) are listed. No non‑solicit or non‑compete clauses.

- **NDA-2** – YELLOW  
  *Reason:* Contains a standard non‑solicitation clause (“During the term of this Agreement and for eighteen (18) months thereafter, neither party shall solicit for employment …”). No non‑compete or unusual governing law, so counsel review of the non‑solicit term is recommended.

- **NDA-3** – RED  
  *Reason:* One‑way confidentiality, a 5‑year term, and a non‑compete (“For a period of three (3) years following the Effective Date, Recipient shall not … engage in or provide services to any business that competes with Discloser's business”). Governing law is Ireland with exclusive jurisdiction there, and no carve‑outs are stated. The combination of a broad non‑compete, lengthy term, and foreign jurisdiction triggers a full legal review.