Analyzing Cohorts in Interactive Queries¶

Interactive Queries (IQ) and Snowflake Data Sharing provide full SQL access to cleansed, processed, and enriched data in Acquia 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’ll get access to all data associated with cohorts in IQ and Snowflake Data Sharing through the table: audiencehistory. 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.

Here 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
      "all_individuals_count"
FROM   customersummary
      LEFT JOIN mastercustomer
              ON customersummary.mastercustomerid =
                mastercustomer.mastercustomerid
      LEFT JOIN audiencehistory AS cohort
              ON mastercustomer.customerid = cohort.mastercustomerid
GROUP  BY 1, 2
ORDER  BY 3 DESC
LIMIT  500