Choose a version:

shop_campaign_insights

The shop_campaign_insights schema captures how your Shop Campaigns perform. It brings campaign sales, ad spend, and customer results together in one table, keyed by campaign.

Anchor to Use casesUse cases

Sales vs. spend: Group shop_campaign_sales and shop_campaign_ad_spend by shop_campaign_name to see which campaigns return more than they cost.
Efficiency: Show shop_campaign_return_on_ad_spend with shop_campaign_average_customer_acquisition_cost to see which campaigns bring customers in profitably.
Customer trend: Trend shop_campaign_customers by month to see how many customers campaigns bring in over time.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM shop_campaign_insights

SHOW shop_campaign_sales, shop_campaign_ad_spend,

shop_campaign_return_on_ad_spend,

shop_campaign_average_customer_acquisition_cost

GROUP BY shop_campaign_name WITH PERCENT_CHANGE

DURING this_quarter

COMPARE TO previous_quarter

ORDER BY shop_campaign_sales DESC

LIMIT 50

VISUALIZE shop_campaign_sales TYPE horizontal_bar

Examples

Description
Rank the top 50 Shop Campaigns this quarter by `shop_campaign_sales`, with ad spend and ROAS. This example uses [`WITH PERCENT_CHANGE`](/docs/api/shopifyql/latest/syntax/with#percent-change-columns) for the change column, with [`COMPARE TO previous_quarter`](/docs/api/shopifyql/latest/syntax/compare-to) benchmarking each campaign against last quarter.
ShopifyQL
```
FROM shop_campaign_insights
  SHOW shop_campaign_sales, shop_campaign_ad_spend,
    shop_campaign_return_on_ad_spend,
    shop_campaign_average_customer_acquisition_cost
  GROUP BY shop_campaign_name WITH PERCENT_CHANGE
  DURING this_quarter
  COMPARE TO previous_quarter
  ORDER BY shop_campaign_sales DESC
  LIMIT 50
VISUALIZE shop_campaign_sales TYPE horizontal_bar
```

Description

Track weekly `shop_campaign_ad_spend` for the five highest-spending Shop Campaigns this quarter. This example uses [`GROUP BY TOP 5`](/docs/api/shopifyql/latest/syntax/group-by) to roll the rest into one remainder line.

ShopifyQL

FROM shop_campaign_insights
  SHOW shop_campaign_ad_spend, shop_campaign_return_on_ad_spend
  GROUP BY TOP 5 shop_campaign_name WITH GROUP_TOTALS
  TIMESERIES week
  DURING this_quarter
  ORDER BY week ASC
VISUALIZE shop_campaign_ad_spend TYPE line

Description
Track weekly `shop_campaign_customers` from Shop Campaigns across this quarter, with `shop_campaign_average_order_value`. This example uses [`WITH TOTALS`](/docs/api/shopifyql/latest/syntax/with#total-columns) to add a quarter-to-date column beside the weekly rows.
ShopifyQL
```
FROM shop_campaign_insights
  SHOW shop_campaign_customers, shop_campaign_average_order_value,
    shop_campaign_insights_last_updated_at
  TIMESERIES week WITH TOTALS
  DURING this_quarter
  ORDER BY week ASC
VISUALIZE shop_campaign_customers TYPE line
```
Description
Compare monthly `shop_campaign_ad_spend` this year against last year. This example uses [`COMPARE TO previous_year`](/docs/api/shopifyql/latest/syntax/compare-to) to set last year as the year-over-year baseline.
ShopifyQL
```
FROM shop_campaign_insights
  SHOW shop_campaign_ad_spend
  TIMESERIES month WITH PERCENT_CHANGE
  DURING this_year
  COMPARE TO previous_year
  ORDER BY month ASC
VISUALIZE shop_campaign_ad_spend TYPE bar
```

Anchor to MetricsMetrics

Counts and calculations that let you track key business indicators. Metrics show up as the columns when queried.

Metrics you can use when querying FROM shop_campaign_insights.

Anchor to shop_campaign_ad_spend shop_campaign_ad_spend: The total ad spend tied to orders associated with Shop Campaigns, in your store's currency.
Anchor to shop_campaign_average_customer_acquisition_cost shop_campaign_average_customer_acquisition_cost: Average customer acquisition cost is reported in your store's currency and is based on customers attributed to Shop Campaigns when their last paid click happened within seven days.

Average customer acquisition cost = shop campaign ad spend / shop campaign customers
Anchor to shop_campaign_average_order_value shop_campaign_average_order_value: The average value of orders associated with Shop Campaigns, in your store's currency.

Shop Campaign average order value = shop campaign sales / shop campaign customers
Anchor to shop_campaign_customers shop_campaign_customers: Number of customers that placed orders associated with Shop Campaigns
Anchor to shop_campaign_return_on_ad_spend shop_campaign_return_on_ad_spend: A value of 3 means orders associated with Shop Campaigns produced three times the ad spend in sales.

Shop Campaign return on ad spend = shop campaign sales / shop campaign ad spend
Anchor to shop_campaign_sales shop_campaign_sales: Sales revenue from orders associated with Shop Campaigns, after discounts and including taxes and shipping, in your store's currency. Refunds are excluded.

MONEY

A monetary amount representing currency values such as prices, revenue, costs, and discounts.

INTEGER

A whole number without decimal places, used for counts, quantities, and other discrete numeric values.

MULTIPLIER

A numeric multiplier used to scale another value.

Anchor to DimensionsDimensions

Attributes of your data that let you look more granularly at aspects of the data. Group and filter by dimensions to shape the rows your query returns.

Dimensions you can use when querying FROM shop_campaign_insights.

Anchor to day day: Use this to group Shop Campaign orders by the day they happened.
Anchor to day_of_week day_of_week: Use this to group Shop Campaign orders by day of week, with values Monday through Sunday.
Anchor to hour hour: Use this to group Shop Campaign orders by the hour they happened.
Anchor to hour_of_day hour_of_day: Use this to group Shop Campaign orders by hour of day, with values 0 through 23.
Anchor to month month: Use this to group Shop Campaign orders by the month they happened.
Anchor to month_of_year month_of_year: Use this to group Shop Campaign orders by month of year, with values 1 through 12.
Anchor to quarter quarter: Use this to group Shop Campaign orders by the quarter they happened.
Anchor to shop_campaign_name shop_campaign_name: Name of the Shop Campaign
Anchor to shop_campaign_target_name shop_campaign_target_name: The customer segment targeted by the Shop Campaign. Values include New customers, All other customers, Lapsed - 3 months, and Lapsed - 12 months.
Anchor to shop_id shop_id: The unique Shopify identifier for your store. Use shop_id with shop_name to show readable store labels.
Anchor to week week: Use this to group Shop Campaign orders by the week they happened.
Anchor to week_of_year week_of_year: Use this to group Shop Campaign orders by week of year, with values 1 through 53.
Anchor to year year: Use this to group Shop Campaign orders by the year they happened.
Anchor to shop_name shop_name: Name of your store

DAY_TIMESTAMP

A date value truncated to day precision.

DAY_OF_WEEK

A day within a week, used for weekday-based grouping and filtering.

HOUR_TIMESTAMP

A timestamp truncated to hour precision.

HOUR_OF_DAY

An hour within a day, typically represented as an integer from 0 to 23.

MONTH_TIMESTAMP

A date value representing the start of a month.

MONTH_OF_YEAR

A month number within a year.

QUARTER_TIMESTAMP

A date value representing the start of a fiscal quarter.

STRING

A sequence of characters representing text data.

IDENTITY

A unique identifier for a Shopify resource such as a customer, product, or order.

WEEK_TIMESTAMP

A date value representing the start of a week.

WEEK_OF_YEAR

A week number within a year.

YEAR_TIMESTAMP

A date value representing the start of a year.

Anchor to Related schemasRelated schemas

shop_product_impressions: Switch to this to see which products got exposure from Shop channel impressions.

Was this page helpful?