If the title makes you scratch your head, fear not, I scratched mine so much it hurts.
Also, I am as new as it gets to Amplitude, so I might be missing something really basic.
All our marketing spend, coming from a dozen or so partners is stored in Snowflake, where it is also harmonised to our in-house tracking setup. It is not event-like by nature, but I was asked to try to bring it to Amplitude nonetheless, so I tried to make it appear as event-like as possible.
Below my attempt, as simple as pretending all our costs pertain to one user, mr. string-zero.
CREATE OR REPLACE VIEW a_db.a_schema.a_view_exposed_to_amplitude
AS
SELECT
'0' AS user_id,
to_timestamp_ntz(a_date_field) AS ts,
date_part(epoch_millisecond, a_date_field::timestamp) AS epoch,
'costs_per_cid' AS event_type,
object_construct('cid', cost_token, 'clicks',clicks, 'usd_costs', costs, 'impressions', impressions) AS event_properties,
object_construct() AS user_properties
FROM a_db.a_schema.a_cost_table
;
All went well as I created the connection, the SQL in the connection setup is as simple as it gets
SELECT
user_id AS "user_id",
event_type AS "event_type",
event_properties AS "event_properties",
epoch AS "time", -- must be in milliseconds since epoch (timestamp)
user_properties AS "user_properties", -- must be JSON
ts AS "update_time_column"
FROM a_db.a_schema.a_view_exposed_to_amplitude
But once the initial ingestion job kicked in, it brought in 0 events, just a couple million errors :)
All looking the same
"error":"events must have either user_id or device_id or both (Missing device or user id)
From my perspective the dataset I created has a user_id. Granted, it’s ‘0’ for all of them, but is that the issue or am I missing something more fundamental?