Ever wished you could facet, filter and do other magic on a dataset that also had aggregated data from a summary table in Tableau CRM?
There is a way!
For example, you want to look at all the scores in the Pardot engagement history object, but also would like to use the same report to show summarized campaign information.
The dilemma?
When you bring in the summarized data and join it by Campaign, each Activity row will contain the data for the entire campaign. While it will make your campaign numbers look spectacular, it will also be spectacularly wrong!
What to do?
Use a flag to differentiate summarized data
Tableau CRM has an AMAZING feature that lets you create a row number that you can use as a flag to differentiate between summarized and detailed data.
How? You can use the flag in your dashboard widgets to summarize or exclude data as needed.
Here’s how it works:
- Join your data as usual. Here we are joining on ActivityCampaignID in Pardot Engagement history to the ID in the Campaign table after we aggregate the metrics we want from Campaigns (leads, cost $ and opps $).
- Use a transform in your recipe to create the “flag” / unique identifier.
- Select Add transformation.
- Select Multiple row formula at the top.
The multiple row indicator allows Tableau CRM to “partition” the data so you can look at groups of data within the data and apply metrics to the specific group.
In this instance, we are grouping by the CampaignID to assign a row number for each instance of the campaign (starting from 1).
- Select Row Number.
- Select the field you wish to create row numbers from.
- Select numeric.
- Give it a name and save.
- Complete your recipe and run!
You now have a “flag” that can be used in your recipe. Each #1 in the row can be used to reflect the “unique” campaign aggregate.
The flag gives you access to accurate data
Now you can use this flag in metrics and KPI widgets where you want to see the campaign metrics.
Example
By adding the flag we created to the filter for summary statistics around Campaigns, we are able to limit the distinct Campaigns for our KPI/top level summary. But we are still able to see the row-level detail for non-aggregated data (like score).
Why is this great? Now you can use the SAME filters, toggles and other interaction features to see detail AND the summary.
Using a single dataset, we can now view the Campaign stats (which we aggregated) and all the activity details our marketing teams crave!
Have a fun use case for this solution? Tell us about it in the comments.
