ZohoAnalytics: Pivot Campaigns vs Contacts

: Joel Lipman; Zoho; 20 March 2023; Hits: 1882

What?
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:

copy raw

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

The catch is that our campaign names are in data rows not individual columns.

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":

copy raw

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):

copy raw

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 :: Article: 842