Get started with Snowflake

SELECT
   "PRODUCT_NAME",
   "COMPANY_ID",
   "COMPANY_NAME" ,
   "COMPANY_DOMAIN",
   "VISITOR_COUNTRY",
   --"VISITOR_REGION", --consider if it is possible to have different contracts in different state/region locations for the same company
   MAX("LAST_SEEN") AS G2__MOST_RECENT_VISIT,
   SUM(
        (
            CASE
                WHEN "VISIT_DATE" <= CURRENT_DATE() - 120 THEN 0 --adjust 120 days to fit timeframe where visits are still likely to be within the same buying cycle
                WHEN "VISIT_TYPE" = 'compare' THEN 20
                WHEN "VISIT_TYPE" = 'product_reference_page' THEN 15
                WHEN "VISIT_TYPE" = 'pricing' then 12
                WHEN "VISIT_TYPE" = 'profile' then 10
                WHEN "VISIT_TITLE" = 'Competitors: '|| "PRODUCT_NAME" THEN 10
                WHEN "VISIT_TYPE" = 'vendors' then 7
                WHEN "VISIT_TYPE" = 'competitors' then 5
                WHEN "VISIT_TYPE" = 'category' then 5
                WHEN "VISIT_TYPE" = 'ad' then 0.5
                ELSE 1
            END
            +
            CASE
                WHEN "VISIT_TITLE" LIKE '%[Top Competitor G2 Name]%' THEN 3
                ELSE 0
            END
        )
        * EXP(-0.01 * (CURRENT_DATE() - "VISIT_DATE")) --decays weight of each visit by 1% per day from the current date; evaluate effectiveness and adjust
    ) AS G2__INTENT_SCORE
FROM
    [DATABASE].SELLER_DATA_FEED.BUYER_INTENT
WHERE
    --"SECTOR" IN ('Education','Software and Technology','Business Services','Financial Services','Manufacturing','Retail and Distribution')
    --"COMPANY_EMPLOYEES" BETWEEN 1 AND 100000000
GROUP BY
    "PRODUCT_NAME",
    "COMPANY_ID",
    "COMPANY_NAME" ,
    "COMPANY_DOMAIN",
    "VISITOR_COUNTRY"
    --,"VISITOR_REGION"
ORDER BY
    G2__INTENT_SCORE DESC

SELECT
    CRM.[OPPORTUNITY_ID],
    CRM.[OPPORTUNITY_PRODUCT_LINE],
    CRM.[OPPORTUNITY_TYPE],
    CRM.[OPPORTUNITY_STAGE],
    CRM.[OPPORTUNITY_AMOUNT],
    CRM.[OPPORTUNITY_CREATED_DATE],
    CRM.[OPPORTUNITY_CLOSE_DATE],
    COUNT(DISTINCT
        CASE
            WHEN G2."VISIT_DATE" BETWEEN CRM.[CREATED_DATE] - 180 AND CRM.[CLOSE_DATE]
            AND G2."PRODUCT_NAME" = CRM.[OPPORTUNITY_PRODUCT_LINE] THEN G2."INTERACTION_ID"
        END
    ) AS G2__TOTAL_TOUCHPOINTS,
    COUNT(DISTINCT
        CASE
            G2."VISIT_DATE" BETWEEN CRM.[CREATED_DATE] - 180 AND CRM.[CLOSE_DATE] 
            AND G2."VISIT_TYPE" IN ('compare','profile','pricing','product_reference_page','cta','lead')
            AND G2."PRODUCT_NAME" = CRM.[OPPORTUNITY_PRODUCT_LINE] THEN G2."INTERACTION_ID"
        END
    ) AS G2__INFLUENCE_TOUCHPOINTS,
        COUNT(DISTINCT
            CASE
                WHEN G2."VISIT_DATE" BETWEEN CRM.[CREATED_DATE] - 90 AND CRM.[CREATED_DATE]
                AND G2."PRODUCT_NAME" = CRM.[OPPORTUNITY_PRODUCT_LINE] THEN G2."INTERACTION_ID"
            END
    ) AS G2__SOURCED_TOUCHPOINTS
FROM
    [DATABASE].[SCHEMA].[CRM_OPPORTUNITY_DATA] AS CRM
    LEFT JOIN [DATABASE].SELLER_DATA_FEED.BUYER_INTENT AS G2 ON 
    --Common matching methods:
        G2."COMPANY_DOMAIN" = CRM.[ACCOUNT_WEBSITE]
        OR REGEXP_LIKE(CRM.[ACCOUNT_WEBSITE],'^(https?:\\/\\/(www\\.)?)?'||G2.COMPANY_DOMAIN)
        OR (G2."COMPANY_NAME" = CRM.[ACCOUNT_NAME] AND COALESCE(G2."VISITOR_COUNTRY",G2."COMPANY_COUNTRY") = CRM.[COUNTRY])
GROUP BY
    CRM.[OPPORTUNITY_ID],
    CRM.[OPPORTUNITY_PRODUCT_LINE],
    CRM.[OPPORTUNITY_TYPE],
    CRM.[OPPORTUNITY_STAGE],
    CRM.[OPPORTUNITY_AMOUNT],
    CRM.[OPPORTUNITY_CREATED_DATE],
    CRM.[OPPORTUNITY_CLOSE_DATE]

WITH G2_ORGS as (
    SELECT
        --"PRODUCT_NAME",
        "COMPANY_ID",
        "COMPANY_NAME",
        "COMPANY_DOMAIN",
        "VISITOR_COUNTRY",
        "COMPANY_COUNTRY",
        "COMPANY_SECTOR",
        "COMPANY_INDUSTRY",
        "COMPANY_EMPLOYEES",
        "ORG_EMPLOYEES_RANGE",
        MAX("VISIT_DATE") AS LAST_G2_VISIT_DATE,
        SUM("TOTAL_PAGE_VIEWS"),
        SUM(
            CASE
                WHEN "VISIT_TYPE" IN ('profile','pricing','compare','product_reference_page','vendors') THEN"TOTAL_PAGE_VIEWS"
                ELSE 0
            END
        ) AS G2_ENGAGEMENT
    FROM
        [DATABASE].SELLER_DATA_FEED.BUYER_INTENT
    --WHERE "VISIT_DATE" >= CURRENT_DATE() - 180
    GROUP BY
        --"PRODUCT_NAME",
        "COMPANY_ID",
        "COMPANY_NAME",
        "COMPANY_DOMAIN",
        "VISITOR_COUNTRY",
        "COMPANY_COUNTRY",
        "COMPANY_SECTOR",
        "COMPANY_INDUSTRY",
        "COMPANY_EMPLOYEES",
        "ORG_EMPLOYEES_RANGE"
        )
SELECT
    COALESCE(
        CRM.[ACCOUNT_TYPE],
        CASE --Use ICP or MQL Firmographic criteria to identify net new prospects who signaled intent for solutions that you offer on G2:
            WHEN G2.COMPANY_SECTOR IN (['Software and Technology','Business Services','Financial Services','Information Technology'])
            AND (G2.COMPANY_EMPLOYEES BETWEEN [10] AND [10000000] OR G2.ORG_EMPLOYEES_RANGE IN (['10,000+','5,001-10,000','2,001-5,000','1,001-2,000','501-1,000','251-500','101-250','51-100','21-50','11-20']) --'6-10','1-5'
            ) THEN 'G2 - New Prospect'
            ELSE 'G2 - Other Org'
        END
    ) AS COMPANY_ACCOUNT_TYPE,
    COALESCE(
        CRM.[ACCOUNT_SEGMENT],
        CASE
            WHEN G2.COMPANY_EMPLOYEES >= 1000 THEN 'Enterprise'
            WHEN G2.ORG_EMPLOYEES_RANGE IN ('10,000+','5,001-10,000','2,001-5,000','1,001-2,000') THEN 'Enterprise'
            WHEN G2.COMPANY_EMPLOYEES >= 100 THEN 'Mid Market'
            WHEN G2.ORG_EMPLOYEES_RANGE IN ('501-1,000','251-500','101-250') THEN 'Mid Market'
            ELSE 'Small Business'
        END
    ) AS SEGMENT,
    COUNT(
        DISTINCT CRM.[ACCOUNT_ID]
    ) AS ACCOUNT_RECORDS,
    COUNT(
        DISTINCT 
        CASE 
            WHEN G2.COMPANY_ID IS NULL THEN NULL
            ELSE CRM.[ACCOUNT_ID]
        END
    ) AS ACCOUNTS_WITH_G2_VISITS,
    COUNT(
        DISTINCT 
        CASE 
            WHEN G2.COMPANY_ID IS NULL THEN NULL
            WHEN G2_ENGAGEMENT >= 1 THEN CRM.[ACCOUNT_ID]
        END
    ) AS ACCOUNTS_WITH_G2_ENGAGEMENT,
    COUNT(
        DISTINCT 
        CASE 
            WHEN CRM.[ACCOUNT_ID] IS NULL
            THEN G2.COMPANY_ID
        END
    ) AS NET_NEW_G2_COMPANIES,
    COUNT(
        DISTINCT 
        CASE
            WHEN CRM.[ACCOUNT_ID] IS NULL AND G2_ENGAGEMENT >= 1 THEN G2.COMPANY_ID
        END
    ) AS NEW_ENGAGED_ON_G2
FROM
    [DATABASE].[SCHEMA].[CRM_OPPORTUNITY_DATA] AS CRM
    LEFT JOIN G2_ORGS AS G2 ON 
        --Common matching methods:
        G2."COMPANY_DOMAIN" = CRM.[ACCOUNT_WEBSITE]
        OR REGEXP_LIKE(CRM.[ACCOUNT_WEBSITE],'^(https?:\\/\\/(www\\.)?)?'||G2.COMPANY_DOMAIN)
        OR (G2."COMPANY_NAME" = CRM.[ACCOUNT_NAME] AND COALESCE(G2."VISITOR_COUNTRY",G2."COMPANY_COUNTRY") = CRM.[COUNTRY])
GROUP BY
    COMPANY_ACCOUNT_TYPE,
    SEGMENT

WITH REVIEW_DATA AS (
    SELECT
        DATE_TRUNC(
            'quarter',"SUBMITTED_DATE"
        ) AS REVIEW_DATE_PERIOD,
        "REVIEWED_PRODUCT",
        COALESCE(
            "USER_COMPANY_SEGMENT",'Not Provided'
        ) AS REVIEWER_SEGMENT,
    COALESCE(
        "USER_COMPANY_INDUSTRY",'Not Provided'
    ) AS REVIEWER_INDUSTRY,
    COALESCE(
        "USER_REGION",'Not Provided'
    ) AS REVIEWER_REGION,
    COUNT("REVIEW_ID") AS REVIEW_COUNT,
    SUM("STAR_RATING") AS TOTAL_STARS,
    COUNT(
        CASE WHEN "NPS" >= 9 THEN "REVIEW_ID" END
    ) AS NPS_PROMOTERS,
    COUNT(
        CASE WHEN "NPS" <= 6 THEN "REVIEW_ID" END
    ) AS NPS_DETRACTORS
    FROM
        [DATABASE].SELLER_DATA_FEED.REVIEWS
    GROUP BY
        REVIEW_DATE_PERIOD,
        REVIEWED_PRODUCT,
        REVIEWER_SEGMENT,
        REVIEWER_INDUSTRY,
        REVIEWER_REGION
    )
SELECT
    D.REVIEW_DATE_PERIOD,
    REVIEWED_PRODUCT,
    REVIEWER_SEGMENT,
    REVIEWER_INDUSTRY,
    REVIEWER_REGION,
    SUM(REVIEW_COUNT) AS G2_REVIEWS_TO_DATE,
    SUM(TOTAL_STARS) / SUM(REVIEW_COUNT) AS G2_RATING_TO_DATE,
    SUM(NPS_PROMOTERS) AS PROMOTER_COUNT_TO_DATE,
    SUM(NPS_DETRACTORS) AS DETRACTOR_COUNT_TO_DATE,
    SUM(
        CASE
            WHEN D.REVIEW_DATE_PERIOD = NRS.REVIEW_DATE_PERIOD THEN REVIEW_COUNT
            ELSE 0
        END
    ) AS NEW_REVIEWS,
    SUM(
        CASE
            WHEN D.REVIEW_DATE_PERIOD = NRS.REVIEW_DATE_PERIOD THEN NPS_PROMOTERS
            ELSE 0
        END
    ) AS NEW_PROMOTERS,
    SUM(
        CASE
            WHEN D.REVIEW_DATE_PERIOD = NRS.REVIEW_DATE_PERIOD THEN NPS_DETRACTORS
            ELSE 0
        END
    ) AS NEW_DETRACTORS
FROM
    (
        SELECT
            REVIEW_DATE_PERIOD
        FROM
            NEW_REVIEW_SCORES
        GROUP BY
            REVIEW_DATE_PERIOD
    ) D
    LEFT JOIN NEW_REVIEW_SCORES NRS ON NRS.REVIEW_DATE_PERIOD <= D.REVIEW_DATE_PERIOD
GROUP BY
    D.REVIEW_DATE_PERIOD,
    REVIEWED_PRODUCT,
    REVIEWER_SEGMENT,
    REVIEWER_INDUSTRY,
    REVIEWER_REGION

SELECT
    DATE_TRUNC('quarter',VISIT_DATE) AS VISIT_PERIOD,
    PRODUCT_ID,
    PRODUCT_NAME,
    --VISIT_TITLE,
    REPLACE(
        CASE
            WHEN VISIT_TYPE = 'ad' THEN REPLACE(VISIT_TITLE, PRODUCT_NAME ||' sponsored content on ')
            WHEN VISIT_TYPE = 'compare' THEN REGEXP_REPLACE(VISIT_TITLE, '( vs. )?'||PRODUCT_NAME||'( vs.)?')
            WHEN VISIT_TYPE = 'pricing' THEN REPLACE(VISIT_TITLE,'Pricing Page:')
            ELSE VISIT_TITLE
        END
        ,INITCAP(VISIT_TYPE)||': ') AS PAGE_TOPIC,
    COUNT(DISTINCT COMPANY_ID) AS VISITING_COMPANIES,
    SUM(TOTAL_PAGE_VIEWS) AS TOTAL_VIEWS,
    SUM(
        CASE
            WHEN VISIT_TYPE = 'compare' THEN TOTAL_PAGE_VIEWS
            ELSE 0
        END
    ) AS COMPARISONS,
    COUNT(DISTINCT
        CASE
            WHEN VISIT_TYPE = 'competitors' THEN COMPANY_ID
        END
    ) AS COMPANIES_SEEKING_ALTERNATIVES
FROM
    [DATABASE].SELLER_DATA_FEED.BUYER_INTENT
GROUP BY
    VISIT_PERIOD,
    PRODUCT_ID,
    PRODUCT_NAME,
    PAGE_TOPIC