Cohort Analysis

Analyzing cohorts in Interactive Queries

Interactive Queries (IQ) and Snowflake Data Sharing provide full SQL access to cleansed, processed, and enriched data in Customer Data Platform (CDP). This includes customer summaries, transaction details at the line-level, atomic events, and much more. All data is automatically refreshed daily with zero setup and maintenance on your end.

With a Cohort Analysis subscription, you get access to all data associated with cohorts in IQ and Snowflake data sharing through the audiencehistory table. This table saves all marked cohorts, within the data retention window, with a timestamp. If you have an active IQ subscription, you can use free-form SQL to analyze cohorts with greater flexibility.

For more information on how the audiencehistory table functions, see FAQs and troubleshooting.

The following is a sample query to join audiencehistory table with the customersummary table:

SELECT cohort.audiencename  AS "campaign_name",
      customersummary.totalrevenuegroup  AS "revenue_lifetime",
      Count(DISTINCT customersummary.mastercustomerid) AS
FROM   customersummary
      LEFT JOIN mastercustomer
              ON customersummary.mastercustomerid =
      LEFT JOIN audiencehistory AS cohort
              ON mastercustomer.customerid = cohort.mastercustomerid
GROUP  BY 1, 2
LIMIT  500