Snowflake

Snowflake is a cloud-based data platform that provides data warehouse-as-a-service designed for the cloud. The Data Connect integration with Snowflake allows you to automatically sync your Contentsquare data to Snowflake for advanced analysis and joining with other business data.

Before setting up the Snowflake integration, ensure you have:

• A Snowflake account with appropriate access
• Snowflake user credentials with a role that has the following privileges:
  • USAGE on database and schema
  • CREATE TABLE on schema
  • SELECT, INSERT, UPDATE, DELETE on tables
  • CREATE VIEW on schema (for all_events and other views)

To get started, you need to have the following information about your Snowflake account:

• Your Snowflake account name: Your account name can be found in the URL used to access Snowflake: <account_name>.snowflakecomputing.com.

• Your account region: We currently support the following regions:

  AWS:

  • us-west-2 (Oregon): region not included in snowflake URLs for this region only
  • us-east-1 (N. Virginia)
  • us-east-2 (Ohio)
  • eu-west-1 (Ireland)
  • eu-central-1 (Frankfurt)
  • ap-southeast-2 (Sydney)
  • ca-central-1 (Canada Central)

  Azure:

  • east-us-2 (Virginia)
  • west-us-2 (Washington)
  • canada-central (Toronto)
  • west-europe (Netherlands)
  • australia-east (New South Wales)
  • north-europe (Ireland)
  • centralus (Iowa)
  • uksouth (London)

  GCP:

  • us-central1 (Iowa)
  • europe-west4 (Netherlands)

If you are outside of these regions, let us know and we will work to add support for you.

To find this information in your Snowflake account, see the Analysis Setup > Connect > Snowflake page in Contentsquare. This page will only be available once Data Connect has been enabled for you by someone on our end.

1. Log in to Contentsquare.

2. Navigate to Analysis setup > Data Connect.

3. Select Connect next to Snowflake.

4. Create a database in Snowflake by copying and running the generated snippet:

   CREATE DATABASE heap_<project_name>_<environment_name> FROM SHARE heap_uswest.share_<env_id>

5. Select Next.

6. Enter your Snowflake hostname ↗.

7. Select Connect.

The Performance Control Center is a Contentsquare Snowflake Native App available in the Snowflake Marketplace. It delivers a single-page e-commerce performance dashboard purpose-built for category managers and e-merchandisers who need a recurring health check across categories, brands, and SKUs directly inside their Snowflake account.

The dashboard answers three core questions:

• What is underperforming or overperforming in my scope right now?
• Which entities deserve attention this week?
• How are my key merchandising funnel metrics evolving over time?

The app delivers four sections in a single dashboard:

• Performance overview: A sortable table of categories, brands, or products with dynamic period-over-period comparisons (compared to last week, month, quarter, and more)
• Main metrics: Eight KPI cards (Product Exposures, PDP Visited, Add-to-Cart Rate, Conversion Rate, Cart Abandonment Rate, Withdrawn Rate, Total Revenue, Average Order Value) with overall-site benchmarks
• Trend: A multi-metric chart comparing the evolution of selected KPIs over a chosen time period
• Page analysis: Top and bottom 10 non-PDP URLs where selected entities are most exposed in sessions where a key outcome occurred

Data refreshes approximately every 2 hours.

Demo app (mock data): Available directly from the Snowflake Marketplace. Search for "Performance Control Center" by Contentsquare. No Contentsquare account required.

Full app (your Merchandising data):

The following are required to access the full app:

• Your organization must be an active Snowflake customer
• Merchandising must be active on your Contentsquare account
• Data Connect with the Snowflake destination must be enabled

1. Request access via the Snowflake Marketplace listing.
2. Contentsquare validates your eligibility (Merchandising + Data Connect).
3. Contentsquare provisions the app for your Snowflake account via a private listing.
4. Install the app and it connects automatically to your existing Data Connect data sharing.

Contentsquare uses Snowflake's Secure Data Sharing feature to provide you with access to your data without copying or moving it.

How Data Sharing Works:

• Contentsquare creates a share containing your data
• You create a database from this share in your Snowflake account
• Data stays in Contentsquare's Snowflake account (no data transfer)
• You query the data as if it were in your account
• No compute costs for data storage on your end

Benefits:

• Instant access: No wait time for data copying
• Always up-to-date: See data as soon as syncs complete
• No storage costs: Data lives in Contentsquare's account
• Secure: Snowflake's secure sharing with access controls

Managing Shared Data:

-- View available shares
SHOW SHARES;

-- See databases created from shares
SHOW DATABASES LIKE '%heap%';

-- Check when data was last updated
SELECT
  table_name,
  last_altered
FROM heap_<project_name>_<environment_name>.information_schema.tables
WHERE table_schema = 'PUBLIC'
ORDER BY last_altered DESC;

While Snowflake data shares are efficient, following these practices ensures optimal query speed:

1. Use appropriate warehouse sizes:

-- For light queries (< 10K rows)
USE WAREHOUSE small_wh;

-- For heavy analytics (millions of rows)
USE WAREHOUSE large_wh;

2. Filter early and often:

-- Good: Filter before joins
WITH recent_sessions AS (
  SELECT *
  FROM heap_<project_name>_<environment_name>.public.sessions
  WHERE time::date >= DATEADD('day', -30, CURRENT_DATE())
)
SELECT * FROM recent_sessions;

3. Leverage clustering hints:

-- Add clustering information for query optimizer
SELECT /*+ CLUSTER_BY(time) */ *
FROM heap_<project_name>_<environment_name>.public.all_events
WHERE time >= DATEADD('day', -7, CURRENT_DATE());

4. Create materialized views for frequent queries:

CREATE MATERIALIZED VIEW my_database.my_schema.daily_sessions AS
SELECT
  time::date as session_date,
  device_type,
  COUNT(DISTINCT session_id) as session_count,
  COUNT(DISTINCT user_id) as user_count
FROM heap_<project_name>_<environment_name>.public.sessions
GROUP BY session_date, device_type;

Snowflake's native integrations allow you to connect Data Connect data with popular BI tools:

Tableau:

• Use Snowflake connector
• Point to shared database: heap_<project_name>_<environment_name>
• Enable query pushdown for optimal performance

Looker:

• Configure Snowflake connection
• Use heap_<project_name>_<environment_name> as database
• Consider Looker Blocks (see below)

Power BI:

• Use Snowflake connector in Power BI Desktop
• Import mode recommended for smaller datasets
• DirectQuery for larger datasets

Metabase:

• Add Snowflake database connection
• Select heap_<project_name>_<environment_name> database
• Build dashboards directly on shared data