A very quick article of a quick solution but at least a working example of an ANSI-SQL (ZohoSQL) query pivoting campaign results vs contacts.
Why?
Simply the client wanted a report on customers on each row and then the campaigns as columns.
What we want:
copyraw
The catch is that our campaign names are in data rows not individual columns.Contact Name Lead Source Campaign 1 Campaign 2 Campaign 3 ---------------- --------------- -------------- -------------- -------------- Joel Google Ads Accepted Invited Sent Me Web Invited Accepted Sent Myself Walk-In Accepted Sent Invited
- Contact Name Lead Source Campaign 1 Campaign 2 Campaign 3
- ---------------- --------------- -------------- -------------- --------------
- Joel Google Ads Accepted Invited Sent
- Me Web Invited Accepted Sent
- Myself Walk-In Accepted Sent Invited
How?
There may be ways of doing this using the GUI and a "Pivot View" report but I always resort to a query when I get confused with the GUI:
Query 1
Due to the limit of sub-query levels we can go down, I want to join this to the contacts data table. So I'm going to make this first query and save it as "Contacts vs Campaigns Pivot":
copyraw
SELECT * FROM ( SELECT "Contact Id", "Contact Name", "Campaign Name", "Member Status" FROM "Campaign Members - Contacts" ) t1 PIVOT ( MAX(t1."Member Status") FOR t1."Campaign Name" IN ( "Campaign 1","Campaign 2","Campaign 3" ) ) t2
- SELECT *
- FROM ( SELECT
- "Contact Id",
- "Contact Name",
- "Campaign Name",
- "Member Status"
- FROM "Campaign Members - Contacts"
- ) t1
- PIVOT
- (
- MAX(t1."Member Status")
- FOR t1."Campaign Name" IN ( "Campaign 1","Campaign 2","Campaign 3" )
- ) t2
The bigger query:
Now create another query (better to refresh the page so that the new table and columns will be available to us):
copyraw
SELECT c."Full Name", c."Lead Source", cp."Campaign 1", cp."Campaign 2", cp."Campaign 3" FROM "Contacts" c LEFT JOIN "Contacts vs Campaigns Pivot" cp ON cp."Contact Id"=c."Id"
- SELECT
- c."Full Name",
- c."Lead Source",
- cp."Campaign 1",
- cp."Campaign 2",
- cp."Campaign 3"
- FROM "Contacts" c
- LEFT JOIN "Contacts vs Campaigns Pivot" cp ON cp."Contact Id"=c."Id"
Caveat(s)
You may have noted a few caveats with this solution:
- This query needs each campaign name specified to see the results per contact person.
- You can only use this in another dataset which won't use a sub-query more than 2 levels deep.
- We're only displaying the maximum value alphabetically found in member status per contact per campaign (should be only 1?)
Category: Zoho Analytics :: Article: 371
