MENU
    Get started with Snowflake
    • 26 Mar 2024
    • 9 Minutes to read

    Get started with Snowflake


    Article summary

    The G2 + Snowflake integration helps you make smarter product, marketing, and revenue decisions by enabling you to leverage G2 Buyer Intent and review data alongside your other business-critical data in Snowflake.

    demo

    Already familiar with the basics of the G2 + Snowflake integration? Select one of the following links to skip to data dictionaries or use cases and sample queries:

    Basics of the G2 + Snowflake integration

    About G2 Buyer Intent data

    G2 Buyer Intent captures enriched data about buyers researching your product across G2. Buyer Intent signals can be generated by a variety of buyer actions, including interacting with your G2 product profile page, comparing your product to a competitor, and viewing alternatives for a product in a shared category. This data enables your sales, marketing, and customer success teams to identify a buyer's stage in the buyer journey.

    Refer to the Buyer Intent documentation for more information on signal types, checking the signals included in your subscription, and more.

    How G2 data is provisioned in Snowflake

    G2 uses Snowflake Private Listings to share access to Buyer Intent and review data for your product(s).

    private listing

    For each listing, G2 represents the corresponding data as a View within the SELLER_DATA_FEED schema.

    architecture

    For more information on materialized views, refer to Snowflake's Working with Materialized Views documentation.

    Data delivery frequency

    Your first delivery includes G2 Buyer Intent data starting from the date that your listing was created, and all historical G2 review data. New data is delivered daily.

    Using G2 data in Snowflake

    G2 offers a comprehensive data dictionary describing each column in the Buyer Intent and Reviews listings, as well as sample use cases and SQL queries to demonstrate how you can layer G2 data with your other data in Snowflake.

    After completing the implementation steps you can preview Buyer Intent or review data for your product(s) directly in Snowsight.

    To preview G2 data in Snowflake:

    1. Navigate to Data > Databases, select your database, then select SELLER_DATA_FEED > Views to access the corresponding view.

    navigation to views

    1. From the schema panel, select Data Preview, choose your Warehouse, then select Preview to generate a preview of your data.

    previewing buyer intent or review data

    Implementation

    This section details every step you need to take to implement the G2 + Snowflake integration.

    Before getting started

    Activating the G2 + Snowflake integration enables you to receive data for all of your G2-listed products that receive Buyer Intent data in your Snowflake workspace

    To activate this integration, you will need to provide your organization’s account details.

    Access your Snowflake account details:

    1. Log in to Snowsight.
    2. In the left sidebar, select the account dropdown, then hover over the name of your account to reveal the account details panel.

    access account information

    You can retrieve your cloud provider, region and account locator directly from the account details panel.

    get other account details

    1. Set the Integration Status

    To set your G2 + Snowflake integration status:

    1. Go to my.G2, then Integrations > Integration Hub.
    2. Select the Snowflake tile.
    3. Set the Integration Status slider to Active.

    2. Connect your Snowflake account

    To obtain access to G2’s Snowflake Private Listing you will need to provide your Snowflake account details.

    Entering incorrect account details will delay the implementation process. For more information on how to access your Snowflake account details, refer to the Access your Snowflake account details section.

    image showing required snowflake account details in my.g2

    To enter your account details:

    1. Go to my.G2, then Integrations > Integration Hub.
    2. Select the Snowflake tile.
    3. Enter your Account Name, Organization Name, and Account Locator into the corresponding fields.
    4. Select Save.

    3. Create a database

    After receiving your account details, G2 will provision access to data within eight business days. To be able to query G2 data in Snowflake, you must generate a database from the corresponding listing(s).

    create database

    To create a database from a listing:

    1. Log in to Snowsight, then navigate to Data > Private Sharing. Your G2 listings appear within the Privately Shared Listings section.
    2. Select a listing, then select Get.
    3. (Optional) Expand the Options panel to rename your database and configure access permissions for different Snowflake roles.

    database options

    1. Select Get to create your database.

    Once your database is created, you can preview G2 data directly in Snowsight or begin writing queries.

    Use cases and sample queries

    G2 provides several example use cases and sample queries to help you understand how you can leverage G2 data in Snowflake.

    The following queries use brackets to denote placeholders for information unique to your Snowflake warehouse, including specific values, table names, and database names.

    For example, G2 uses the syntax [DATABASE] to indicate that you should replace this value with the name of your database.

    Create an account scoring and ABM prioritization model

    The following query creates a basic account prioritization model by assigning a score to each Buyer Intent signal type.

    G2 includes example scoring for demonstration purposes. You should adapt these values to your business and use case.

    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
    SQL

    Sample output

    PRODUCT_NAMECOMPANY_IDCOMPANY_NAMECOMPANY_DOMAINVISITOR_COUNTRYVISITOR_REGIONG2_MOST_RECENT_VISITG2_INTENT_SCORE
    {YOUR PRODUCT}123456SaaS Company Xsaascompanyx.comUnited StatesIllinois2023-03-09T01:20:10Z112.923
    {YOUR PRODUCT}78910SaaS Company Ysaascompanyy.comUnited StatesCalifornia2023-03-15T11:20:10Z35.563

    Calculate ROI from your G2 Marketing Solutions investment

    The following query tracks the number of opportunities sourced and influenced by G2 Buyer Intent data, and enables you to measure G2's impact on your win rate, deal size, and time to close.

    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]
    SQL

    Sample output

    OPPORTUNITY_IDOPPORTUNITY_PRODUCT_LINEOPPORTUNITY_TYPEOPPORTUNITY_STAGEOPPORTUNITY_AMOUNTCREATED_DATECLOSE_DATEG2__TOTAL_TOUCHPOINTSG2__INFLUENCE_TOUCHPOINTSG2__SOURCED_TOUCHPOINTS
    0064W000013usCLQAY{YOUR PRODUCT}New BusinessClosed Lost250007/12/218/31/21111
    0064W0000191nZLQAY{YOUR PRODUCT}RenewalWorking227501/31/222/6/24331

    Monitor customer and prospect touchpoints

    This query joins G2 Buyer Intent data with marketing and CRM data to help you assess customer and prospect journeys per market segment. Use this query to analyze how your current customers engage with G2 and the stage at which prospects interact with your G2 profile before becoming customers.

    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
    SQL

    Sample output

    COMPANY_ACCOUNT_TYPESEGMENT         ACCOUNT_RECORDSACCOUNTS_WITH_G2_VISITSACCOUNTS_WITH_G2_ENGAGEMENTNET_NEW_G2_COMPANIESNEW_ENGAGED_ON_G2
    Vendor - ProspectSmall Business9897615501284150100
    Vendor - CustomerSmall Business15012902724923

    The following query enables you to identify quarterly trends in your review volume, star rating, and net promotors for each of your products by grouping review data by region, industry, and market 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
    SQL

    Sample output

    REVIEW_DATE_PERIODREVIEWED_PRODUCTREVIEWER_SEGMENTREVIEWER_INDUSTRYREVIEWER_REGIONG2_REVIEWS_TO_DATEG2_RATING_TO_DATEPROMOTER_COUNT_TO_DATEDETRACTOR_COUNT_TO_DATENEW_REVIEWSNEW_PROMOTERSNEW_DETRACTORS
    1/1/23{YOUR PRODUCT}EnterpriseComputer SoftwareEMEA364.01388889188210
    10/1/22{YOUR PRODUCT}EnterpriseComputer SoftwareEMEA344.01470588178000

    Identify opportunities for competitive positioning

    This query helps you identify opportunities to position your product against competitors in your category by comparing G2 pageviews that exclusively feature your G2 profile versus pageviews that include your competitors, such as compare pageviews and alternatives pageviews.

    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
    SQL

    Sample output

    VISIT_PERIODPRODUCT_ID            PRODUCT_NAMEPAGE_TOPIC        VISITING_COMPANIESTOTAL_VIEWSCOMPARISONSCOMPANIES_SEEKING_ALTERNATIVES
    1/1/23309cf298-0fa3-4bfb{YOUR PRODUCT}Cloud Compliance819206992354
    10/1/22309cf298-0fa3-4bfb{YOUR PRODUCT}Cloud Compliance6971920114340