Sum and Avg Query Library
This article contains a library of insights and measurement metrics that can be calculated with sum/avg queries, as long as the datapoints are available in the Bunker.
For introduction information on how to use the sum and avg function please read this article.
|
Functionality in Beta |
|
Please note that this functionality is in Beta and not available to all users. Please reach out to your InfoSum representative if you wish to use it. Additionally, there are some functionality restrictions:
|
Table of Contents
Metrics that require additional calculations
How to use this library
This library has been created to help you customize the queries for our specific use case. You will need to edit the queries to reflect the specific datasets that you are trying to use, and the name of the categories etc. Here are some pointers to help you:
- The suitability of the below queries is dependent on what data is being brought into a bunker and made available for querying
- Collaborations can take many shapes and forms. So when it says Brand INTERSECT DataProvider, this does not necessarily mean that it has to be a data provider, it could also be Brand to Brand, or Brand to Publisher, etc.
-
Queries can always include WHERE conditions with ANDs and ORs if the querying party wants to be more granular and drill down into specific sub-groups within their own audience, i.e. using their own categories or permissioned categories.
- Queries can often also be made simpler by cutting out the WHERE condition, as it is not always strictly necessary.
- Most queries illustrated involve only 2 datasets. These can be extended to include more, bearing in mind that SUM queries only allow for INTERSECT and EXCLUDE, not UNION.
- These queries also work if a LINK WITH partner is required
SUM and AVG metric library
Measurement
|
Metric |
Description |
Query |
|
Basket value |
SUM & AVG Combined total and average of basket values for all customers exposed to a campaign |
select SUM(Retailer.basket_value) From Retailer INTERSECT Publisher WHERE Publisher.exposed = 'Yes' |
|
Value of sales of specific product |
SUM & AVG Combined total and average spent on a specific product, e.g. orchids, for all customers exposed to a campaign |
select SUM(Retailer.product_spent_orchid) From Retailer INTERSECT Publisher WHERE Publisher.exposed = 'Yes' |
|
Number of sales of specific product |
SUM & AVG Combined count and average of purchases of a specific product, e.g. mozzarella, for all customers exposed to a campaign |
select SUM(Retailer.purchased_product_count_mozzarella) From Retailer INTERSECT Publisher WHERE Publisher.exposed = 'Yes' |
|
Number of repeated actions |
SUM & AVG |
select SUM(Brand.site_visit_count) From Brand INTERSECT Publisher WHERE Publisher.exposed = 'Yes' |
|
CLV - Customer Lifetime Value |
SUM & AVG Combined and average customer lifetime values for all customers exposed to a campaign, especially interesting over time |
select SUM(Brand.customer_lifetime_value) From Brand INTERSECT Publisher WHERE Publisher.exposed = 'Yes' |
|
Impressions Served on Converting Users (Inc AVG frequency of conversion) |
SUM & AVG Total and average number of ad impressions served on users who ended up converting. Average shows the average frequency of converting users. |
select SUM(Publisher.count_impressions_served) From Brand INTERSECT Publisher WHERE Brand.converted = 'Yes' |
|
Engagement - Content |
SUM & AVG Total and average amount of time users who were exposed to ads and ended up converting engaged with publisher content, e.g. per day/week/month in sec/min/hours |
select SUM(Publisher.content_consumption) From Brand INTERSECT Publisher WHERE Brand.converted = 'Yes' AND Publisher.exposed = 'Yes' |
|
Engagement - Advertising |
SUM & AVG Total and average amount of time users who ended up converting were exposed to ads |
select SUM(Publisher.ad_exposure_time) From Brand INTERSECT Publisher WHERE Brand.converted = 'Yes' |
|
Demographics - e.g. Age |
Average age of customers who saw an ad and converted, Option 1 (no data provider) |
select SUM(Publisher.age) From Brand INTERSECT Publisher WHERE Brand.converted = 'Yes' AND Publisher.exposed = 'Yes' |
|
Average age of customers who saw an ad and converted, Option 2 (with data provider) |
select SUM(DataProvider.age) From Brand INTERSECT DataProvider INTERSECT Publisher WHERE Brand.converted = 'Yes' AND Publisher.exposed = 'Yes' |
Insights
|
Metric |
Description |
Additional notes |
Query |
|
Demographics (Age) |
Average age of active customer |
Same analysis for lapsed, dormant, etc. |
select SUM(DataProvider.age)From Brand INTERSECT DataProviderWHERE Brand.customer_status = 'Active' |
|
Demographics (Income) |
Average income of active customer |
Same analysis for lapsed, dormant, etc. |
select SUM(DataProvider.income)From Brand INTERSECT DataProviderWHERE Brand.customer_status = 'Active' |
|
Demographics (Family composition) |
Average number of children of active customer |
Same analysis for lapsed, dormant, etc. |
select SUM(DataProvider.number_of_children)From Brand INTERSECT DataProviderWHERE Brand.customer_status = 'Active' |
|
Competitive Spent |
Average spent of customers on competitors |
Optionally further segmented into specific competitive products or services and/or brand sub-groups |
select SUM(DataProvider.spent_competitor)From Brand INTERSECT DataProviderWHERE DataProvider.competitor_product = 'ABC'AND Brand.product = '123' |
|
Churn Risk |
Average likelihood of a customer of a certain cluster to churn |
E.g. for likelihood of a female existing customer to cancel her subscription |
select SUM(Brand.churn_risk_score)From Brand INTERSECT DataProviderWHERE DataProvider.gender = 'Female' |
|
Upsell Feasibility |
Average feasibility score of a customer for a certain product or service |
E.g. feasibility of a credit card customer with an income of £20k-30k to get a mortgage |
select SUM(Brand.mortgage_feasibility_score)From Brand INTERSECT DataProviderWHERE DataProvider.income BETWEEN 20,000 AND 30,000AND Brand.current_product = 'Credit card' |
|
Frequency of Engagement - "Stickiness" |
Average number of engagements with product or services, e.g. through website or app, of a customer of a certain cluster |
E.g. average number of website visits or app opens of customers who are in market for an SUV |
select SUM(Brand.count_site_visit)From Brand INTERSECT DataProviderWHERE DataProvider.car_intender = 'SUV' |
|
Customer Loyalty |
Average number of days since first known intersection with brand (e.g. registration, purchase, download, etc.) of a customer of a certain cluster |
Aims at determining trends when comparing various clusters, e.g. gen Z customers have used music streaming apps on average for 3000 days, while gen X only for 300 days. |
select SUM(Brand.days_since_first_interaction)From Brand INTERSECT DataProviderWHERE DataProvider.generation = 'Z' |
|
Customer Loyalty |
Average number of days since last known intersection with brand (e.g. registration, purchase, download, etc.) of a customer of a certain cluster |
Aims at determining user clusters with high likelihood to go from dormant / inactive to churned / lapsed. |
select SUM(Brand.days_since_last_interaction)From Brand INTERSECT DataProviderWHERE DataProvider.generation = 'Z' |
|
CLV - Customer Lifetime Value |
Average CLV of customers of a certain cluster |
Optionally further segmented into brand sub-groupsPotentially also interesting in SUM (= total CLV of customers of a certain cluster, optionally further segmented into brand sub-groups) |
select SUM(Brand.customer_lifetime_value)From Brand INTERSECT DataProviderWHERE DataProvider.civil_status = 'Civil Partnered'AND Brand.product = 'Phone' |
Metrics that require additional calculations
The below metrics can also be calculated as AVG with the same queries.
|
Metric |
Description |
Queries |
Additional calculations needed |
|
Total incremental basket value |
Combined incremental total of basket values for all customers exposed to a campaign |
select SUM(Retailer.basket_value)From Retailer INTERSECT PublisherWHERE Publisher.exposed = 'Yes'select SUM(Retailer.basket_value)From Retailer INTERSECT PublisherWHERE Publisher.exposed = 'No' |
Calculation:(Query 1 - Query 2) / Query 2e.g. (30k - 20k) / 20k = 50% incremental sale |
|
Total incremental value of sales of specific product |
Combined incremental total of spent on a specific product, e.g. orchids, for all customers exposed to a campaign |
select SUM(Retailer.product_spent_orchid)From Retailer INTERSECT PublisherWHERE Publisher.exposed = 'Yes'select SUM(Retailer.product_spent_orchid)From Retailer INTERSECT PublisherWHERE Publisher.exposed = 'No' |
Calculation:(Query 1 - Query 2) / Query 2e.g. (500 - 400) / 400 = 25% incremental sale |
|
Total incremental number of sales of specific product |
Combined incremental count of purchases of a specific product, e.g. mozzarella, for all customers exposed to a campaign |
select SUM(Retailer.purchased_product_count_mozzarella)From Retailer INTERSECT PublisherWHERE Publisher.exposed = 'Yes'select SUM(Retailer.purchased_product_count_mozzarella)From Retailer INTERSECT PublisherWHERE Publisher.exposed = 'No' |
Calculation:(Query 1 - Query 2) / Query 2e.g. (5k - 2.5k) / 2.5k = 100% incremental sale |
|
Total incremental number of repeated actions |
Combined incremental count of actions for all customers exposed to a campaign, e.g. number of visits to the brand website or app |
select SUM(Brand.site_visit_count)From Brand INTERSECT PublisherWHERE Publisher.exposed = 'Yes'select SUM(Brand.site_visit_count)From Brand INTERSECT PublisherWHERE Publisher.exposed = 'No' |
Calculation:(Query 1 - Query 2) / Query 2e.g. (2.5k - 5k) / 5k = 200% incremental repeated actions |
|
Incremental CLV - Customer Lifetime Value |
Combined incremental customer lifetime value for all customers exposed to a campaign |
select SUM(Brand.customer_lifetime_value)From Brand INTERSECT PublisherWHERE Publisher.exposed = 'Yes'select SUM(Brand.customer_lifetime_value)From Brand INTERSECT PublisherWHERE Publisher.exposed = 'No' |
Calculation:(Query 1 - Query 2) / Query 2e.g. (1.1k - 1k) / 1k = 10% incremental CLV |
|
Ratio Impressions Served on Converting Users Vs. Non-Converting Users |
select SUM(Publisher.count_impressions_served)From Brand INTERSECT PublisherWHERE Brand.converted = 'Yes'select SUM(Publisher.count_impressions_served)From Brand INTERSECT PublisherWHERE Brand.converted = 'No' |
Calculation:Query 1 / Query 2e.g. 10k / 50k = 20% of impressions served on users who ended up converting |
|
|
Total Engagement - Content |
Total amount of time users who were exposed to ads and ended up converting engaged with publisher content, e.g. per day/week/month in sec/min/hours vs users who did not end up converting |
select SUM(Publisher.content_consumption)From Brand INTERSECT PublisherWHERE Brand.converted = 'Yes'AND Publisher.exposed = 'Yes'select SUM(Publisher.content_consumption)From Brand INTERSECT PublisherWHERE Brand.converted = 'No'AND Publisher.exposed = 'Yes' |
Calculation:Query 1 / Query 2e.g. 300mins / 150mins = users who ended up converting engaged with publisher content twice as much time as users who did not end up converting |
|
Total Engagement - Advertising |
Total amount of time users who ended up converting were exposed to ads vs users who did not end up converting |
select SUM(Publisher.ad_exposure_time)From Brand INTERSECT PublisherWHERE Brand.converted = 'Yes'select SUM(Publisher.ad_exposure_time)From Brand INTERSECT PublisherWHERE Brand.converted = 'No' |
Calculation:Query 1 / Query 2e.g. 600mins / 150mins = users who ended up converting were exposed to ads four times as long as users who did not end up converting |
Time-based measurement
All measurement queries above can be used to generate time-based charts and comparisons, for example:
|
Metric |
Description |
Queries |
Additional calculations needed |
|
Total basket value over time |
The combined total of basket values for all customers exposed to a campaign over time |
select SUM(Retailer.basket_value)From Retailer INTERSECT PublisherWHERE Publisher.exposed = 'Yes'AND Retailer.time = 'January'select SUM(Retailer.basket_value)From Retailer INTERSECT PublisherWHERE Publisher.exposed = 'Yes'AND Retailer.time = 'February'select SUM(Retailer.basket_value)From Retailer INTERSECT PublisherWHERE Publisher.exposed = 'Yes'AND Retailer.time = 'March'select SUM(Retailer.basket_value)From Retailer INTERSECT PublisherWHERE Publisher.exposed = 'Yes'AND Retailer.time = 'April'... |
Build a graph or chart by inputting the results from query 1, query 2, query 3, etc. |
Segment comparison insights
All insight queries above can be used to generate comparisons between segments, for example:
|
Metric |
Description |
Queries |
Additional calculations needed |
|
Demographics - Income |
Average income of active customer vs a lapsed customer vs a dormant customer |
select SUM(DataProvider.income)From Brand INTERSECT DataProviderWHERE Brand.customer_status = 'Active'select SUM(DataProvider.age)From Brand INTERSECT DataProviderWHERE Brand.customer_status = 'Lapsed'select SUM(DataProvider.age)From Brand INTERSECT DataProviderWHERE Brand.customer_status = 'Dormant' |
Either put all results in a graph / chart or further calculate increases or decreases, e.g.Absolute calculation: Query 1 - Query 2 = active customers on average earn X more than lapsed customersRelative calculation: (Query 1 - Query 2) / Query 2 = active customers on average earn X% more than lapsed customers |