Create an Apple Search Ads Campaign – Kochava Support
SELECT
DATE
(
i
.
date_occurred
)
AS
install_date
,
CASE
WHEN
COALESCE
(
i
.
site_id
,
”
)
=
”
THEN
‘–no keyword provided–‘
ELSE
i
.
site_id
END
AS
keyword
,
JSON_EXTRACT_SCALAR
(
i
.
match_object
,
‘$.iad-campaign-name’
)
AS
campaign_name
,
JSON_EXTRACT_SCALAR
(
i
.
match_object
,
‘$.iad-adgroup-name’
)
AS
adgroup_name
,
COUNT
(
DISTINCT
i
.
install_id
)
AS
install_count
,
COUNT
(
DISTINCT
(
CASE
WHEN
e
.
event_name
=
‘_SessionBegin’
AND
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
0
AND
0
THEN
e
.
event_id
ELSE
NULL
END
)
)
AS
d0_session_count
,
COUNT
(
DISTINCT
(
CASE
WHEN
e
.
event_name
=
‘_SessionBegin’
AND
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
1
AND
1
THEN
e
.
event_id
ELSE
NULL
END
)
)
AS
d1_session_count
,
COUNT
(
DISTINCT
(
CASE
WHEN
e
.
event_name
=
‘_SessionBegin’
AND
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
2
AND
7
THEN
e
.
event_id
ELSE
NULL
END
)
)
AS
d2_d7_session_count
,
COUNT
(
DISTINCT
(
CASE
WHEN
e
.
event_name
=
‘_SessionBegin’
AND
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
8
AND
30
THEN
e
.
event_id
ELSE
NULL
END
)
)
AS
d8_d30_session_count
,
COUNT
(
DISTINCT
(
CASE
WHEN
e
.
event_name
=
‘Purchase_New’
AND
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
0
AND
0
THEN
e
.
event_id
ELSE
NULL
END
)
)
AS
d0_purchase_count
,
—
Adjust
event
names
here
COUNT
(
DISTINCT
(
CASE
WHEN
e
.
event_name
=
‘Purchase_New’
AND
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
1
AND
1
THEN
e
.
event_id
ELSE
NULL
END
)
)
AS
d1_purchase_count
,
—
Adjust
event
names
here
COUNT
(
DISTINCT
(
CASE
WHEN
e
.
event_name
=
‘Purchase_New’
AND
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
2
AND
7
THEN
e
.
event_id
ELSE
NULL
END
)
)
AS
d2_d7_purchase_count
,
—
Adjust
event
names
here
COUNT
(
DISTINCT
(
CASE
WHEN
e
.
event_name
=
‘Purchase_New’
AND
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
8
AND
30
THEN
e
.
event_id
ELSE
NULL
END
)
)
AS
d8_d30_purchase_count
,
—
Adjust
event
names
here
COUNT
(
DISTINCT
(
CASE
WHEN
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
0
AND
0
THEN
e
.
install_id
ELSE
NULL
END
)
)
AS
d0_retention
,
COUNT
(
DISTINCT
(
CASE
WHEN
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
1
AND
1
THEN
e
.
install_id
ELSE
NULL
END
)
)
AS
d1_retention
,
COUNT
(
DISTINCT
(
CASE
WHEN
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
2
AND
7
THEN
e
.
install_id
ELSE
NULL
END
)
)
AS
d2_d7_retention
,
COUNT
(
DISTINCT
(
CASE
WHEN
TIMESTAMP_DIFF
(
e
.
date_occurred
,
i
.
date_occurred
,
DAY
)
BETWEEN
8
AND
30
THEN
e
.
install_id
ELSE
NULL
END
)
)
AS
d8_d30_retention
FROM
(
(
SELECT
‘_Install’
AS
event_name
,
date_occurred
,
install_id
,
site_id
,
match_object
FROM
installs_primary
WHERE
network_name
=
‘Apple Search Ads’
AND
install_id
<>
‘0’
AND
DATE
(
date_occurred
)
>=
DATE_ADD
(
CURRENT_DATE
(
)
,
INTERVAL
–
60
DAY
)
—
Adjust
install
cohort
start
date
here
AND
DATE
(
date_occurred
)
<=
DATE_ADD
(
CURRENT_DATE
(
)
,
INTERVAL
–
30
DAY
)
—
Adjust
install
cohort
end
date
here
)
i
LEFT
OUTER
JOIN
(
SELECT
event_name
,
date_occurred
,
install_id
,
event_id
,
kochava_device_id
FROM
events_primary
WHERE
network_name
=
‘Apple Search Ads’
AND
install_id
<>
‘0’
AND
DATE
(
date_occurred
)
>=
DATE_ADD
(
CURRENT_DATE
(
)
,
INTERVAL
–
60
DAY
)
—
Adjust
to
match
install
cohort
start
date
here
AND
event_name
IN
(
‘_SessionBegin’
,
‘Purchase_New’
)
—
Adjust
event
names
here
)
e
ON
i
.
install_id
=
e
.
install_id
AND
e
.
date_occurred
BETWEEN
i
.
date_occurred
AND
TIMESTAMP_ADD
(
i
.
date_occurred
,
INTERVAL
30
DAY
)
)
GROUP
BY
install_date
,
keyword
,
campaign_name
,
adgroup_name
ORDER
BY
install_date
,
install_count
DESC
