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

Get started with Snowflake

  • Dark
    Light

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

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]

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

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

Track G2 review trends per region, industry, and market segment

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

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

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