Meeting Title: Attribution Stitching Check Date: 2025-11-13 Meeting participants: Henry Zhao, Zoran Selinger
WEBVTT
1 00:00:06.930 ⇒ 00:00:07.990 Henry Zhao: Here’s a run.
2 00:00:11.740 ⇒ 00:00:12.610 Zoran Selinger: Hi, hi.
3 00:00:13.280 ⇒ 00:00:14.910 Zoran Selinger: Okay.
4 00:00:15.340 ⇒ 00:00:19.169 Henry Zhao: Thank you for your help. We can go through this pretty quickly, I believe.
5 00:00:20.890 ⇒ 00:00:38.249 Henry Zhao: Alright, let’s go through this. So, what my strategy here is to break this up into two pieces, because I don’t know if you were in the meeting with Cutter and everything, but they wanted two last touches. So, one last touch is the order completed last touch, and the other last touch is the thank you page last touch, right?
6 00:00:38.960 ⇒ 00:00:39.780 Zoran Selinger: Okay.
7 00:00:39.780 ⇒ 00:00:50.899 Henry Zhao: So for both pieces, so that’s the two pieces, right? So for both pieces, I first want to make sure that I’m only grabbing the first order completed and first thank you page visit for each transaction ID.
8 00:00:51.490 ⇒ 00:00:56.149 Henry Zhao: Because… Right? I don’t want to double count, first of all, and secondly.
9 00:00:57.000 ⇒ 00:01:02.090 Henry Zhao: if I do joins, it’s gonna, like, duplicate my data if I look at each transaction ID more than once.
10 00:01:03.150 ⇒ 00:01:09.780 Henry Zhao: So for Basque order completed, I’m just taking all of the order completed from BASC, and taking the first transaction ID by timestamp.
11 00:01:10.000 ⇒ 00:01:18.329 Henry Zhao: For attribution thank you page visits, I’m doing the same thing. For Webflow order completed, it’s called order ID, so I’m doing the same thing, but for order ID and anonymous ID.
12 00:01:19.420 ⇒ 00:01:25.500 Henry Zhao: Because I could do it once on my computer, I could do it once on my phone, I want to be able to grab this combination.
13 00:01:26.160 ⇒ 00:01:34.319 Henry Zhao: And get the full attribution, right? So, like, me on my phone and me on my laptop, I should be counted as the same person, so I’m doing it by anonymous ID and order ID, okay?
14 00:01:35.340 ⇒ 00:01:36.170 Zoran Selinger: Okay.
15 00:01:36.720 ⇒ 00:01:39.919 Henry Zhao: Alright, so then what I do is, I do a union all.
16 00:01:40.560 ⇒ 00:01:45.070 Henry Zhao: of two things, right? So one is I look at all of the BASC order completeds.
17 00:01:45.250 ⇒ 00:01:49.770 Henry Zhao: And I join it to basically the combination of Webflow order completed by…
18 00:01:50.180 ⇒ 00:01:52.820 Henry Zhao: Order ID and anonymous ID, right? So…
19 00:01:53.350 ⇒ 00:01:59.009 Henry Zhao: I want to join it to Henry on laptop and Henry on computer. So I’m joining on transaction ID equals order ID,
20 00:01:59.260 ⇒ 00:02:02.449 Henry Zhao: where basically both of them are Rn equals 1.
21 00:02:03.980 ⇒ 00:02:08.329 Henry Zhao: Then, here’s where I get the attribution history, right? So I left joining on Webflow Pages.
22 00:02:08.610 ⇒ 00:02:12.919 Henry Zhao: based on the anonymous ID, so I’m saying, like, what are all the pages that Henry
23 00:02:13.160 ⇒ 00:02:16.599 Henry Zhao: Zhao as a person, which is this anonymous ID, has come from.
24 00:02:16.850 ⇒ 00:02:20.339 Henry Zhao: And I also want only the pages that came before the conversion.
25 00:02:20.670 ⇒ 00:02:35.540 Henry Zhao: So I’m doing a greater than or equal to, and I was gonna ask you, do you think these timestamps are accurate enough for this logic to work? And I think the answer is yes, for two reasons. One, when we did testing, the timestamps were quite accurate. Number two, in the context of Eden.
26 00:02:35.870 ⇒ 00:02:47.630 Henry Zhao: there’s not much that… where it’s like, you get to an intake page, and then you convert within, like, a millisecond, right? Like, usually, it takes some time for you to fill out the intake, and so this should work, right? Because the…
27 00:02:47.630 ⇒ 00:02:48.340 Zoran Selinger: Yes.
28 00:02:49.100 ⇒ 00:03:03.290 Henry Zhao: So, like, at previous companies, if the conversion was fast, I may need to, like, add a buffer here, where I need to add, like, plus 2 minutes, or just do same day, but in the case of Eden, I think this is good enough, just because the intake takes a while, and this should capture most good behavior.
29 00:03:04.810 ⇒ 00:03:06.480 Zoran Selinger: Yes, of course, yes.
30 00:03:06.480 ⇒ 00:03:07.370 Henry Zhao: Right? Okay.
31 00:03:07.550 ⇒ 00:03:14.250 Zoran Selinger: And also, and also, we don’t, when we are, getting this, we are not…
32 00:03:14.300 ⇒ 00:03:28.620 Zoran Selinger: we don’t have to fire tags, necessarily. So for the edge table, it just happens instantly, because it happens even before the page load, right? It happens on the request. So it should be very, very precise.
33 00:03:28.920 ⇒ 00:03:35.350 Henry Zhao: Yes. So the second piece is the edge stuff, right? So I’m doing the exact same thing, where I’m getting the… the, orders.
34 00:03:35.580 ⇒ 00:03:47.560 Henry Zhao: with Webflow, right? And then I’m joining it to the attribution edge layer raw data, where I’m joining on the Webflow order completed edge user ID equaling the edge layer user ID, or…
35 00:03:47.700 ⇒ 00:03:50.609 Henry Zhao: the… User ID being equal.
36 00:03:51.570 ⇒ 00:04:00.719 Henry Zhao: Because sometimes the edge layer user ID is in the user ID field, sometimes it’s in the edge user ID field. I think you made the change on November 7th, but
37 00:04:01.420 ⇒ 00:04:04.219 Henry Zhao: and doing here, right? So is this going good, you think?
38 00:04:05.030 ⇒ 00:04:06.360 Zoran Selinger: I think so, yes.
39 00:04:06.570 ⇒ 00:04:13.460 Henry Zhao: Good. And then same thing, the buck timestamp is greater than or equal to the edge layer timestamp, which should work because of what you just said.
40 00:04:13.950 ⇒ 00:04:14.570 Zoran Selinger: Yep.
41 00:04:14.990 ⇒ 00:04:25.779 Henry Zhao: So this gives me a whole list of, like, history before the conversion, and so what I then calculate is the user ID, which is the edge layer user ID now, right? The transaction ID, which is the PI underscore stuff.
42 00:04:26.220 ⇒ 00:04:37.720 Henry Zhao: Then I grab the pages, like, row number, and then the reverse row number. I grab the campaign, medium, timestamp, source, and then the timestamp conversion, and then the page visit timestamp.
43 00:04:40.530 ⇒ 00:04:44.720 Henry Zhao: And then I summarized that by saying, for each user, for each transaction, and for each conversion.
44 00:04:45.650 ⇒ 00:04:54.229 Henry Zhao: What is the first UTM campaign by using Rn equals 1? What is the first UTM medium source, last UTM campaign, last UTM medium, last UTM source, and then the timestamps of each.
45 00:04:55.640 ⇒ 00:04:56.520 Zoran Selinger: Okay.
46 00:04:56.520 ⇒ 00:04:57.579 Henry Zhao: That make sense to you?
47 00:04:57.860 ⇒ 00:04:58.580 Zoran Selinger: Yes.
48 00:04:59.100 ⇒ 00:05:00.760 Zoran Selinger: Yes, it does.
49 00:05:01.200 ⇒ 00:05:08.469 Henry Zhao: And then thank you page is the same logic, like, covered, but for the inside, right, I’m taking thank you page visits.
50 00:05:08.780 ⇒ 00:05:11.269 Henry Zhao: Again, I’m joining the Webflow order completed.
51 00:05:11.440 ⇒ 00:05:13.070 Henry Zhao: Based on the same logic.
52 00:05:13.290 ⇒ 00:05:20.920 Henry Zhao: Same Webflow pages, but I think now the join is on transaction ID equaling to the wc.orderID, which is what we talked about yesterday.
53 00:05:20.920 ⇒ 00:05:21.740 Zoran Selinger: Yes.
54 00:05:21.740 ⇒ 00:05:23.580 Henry Zhao: And then timestamp should be the same thing, right?
55 00:05:23.580 ⇒ 00:05:24.260 Zoran Selinger: Yep.
56 00:05:24.560 ⇒ 00:05:25.340 Henry Zhao: Okay?
57 00:05:28.230 ⇒ 00:05:35.480 Henry Zhao: Same thing for the edge layer stuff, so I have thank you page visits joining on edge layer raw data. This is more direct, I just use user ID join, right?
58 00:05:36.280 ⇒ 00:05:43.059 Henry Zhao: Same timestamp, also needing our, TYPV.rn equals 1. This I’ll probably just put on the where statement.
59 00:05:44.860 ⇒ 00:05:53.290 Henry Zhao: So the last thing I need you to check is the join is fine, but there are 500 user IDs that are in the thank you page visits that are not in the edge layer raw data. Do you have any idea why that might be?
60 00:05:56.720 ⇒ 00:06:03.739 Zoran Selinger: 500 user IDs in the… In the thank you, and they are not in the edge.
61 00:06:04.250 ⇒ 00:06:05.900 Zoran Selinger: layer of data.
62 00:06:14.270 ⇒ 00:06:24.830 Zoran Selinger: Oh, yeah, okay, yeah, I know why. So we will only save sessions into the raw data if there are campaign parameters.
63 00:06:25.200 ⇒ 00:06:27.360 Henry Zhao: Okay, got it. Is there no…
64 00:06:27.360 ⇒ 00:06:32.919 Zoran Selinger: campaign parameters. We will still create a user ID.
65 00:06:33.630 ⇒ 00:06:37.780 Zoran Selinger: And when they come to the thank you page, we will save them.
66 00:06:38.910 ⇒ 00:06:48.679 Zoran Selinger: we will save it in there, but the raw data will not have that user ID, since they did not come via
67 00:06:49.400 ⇒ 00:06:50.909 Zoran Selinger: one of our…
68 00:06:51.180 ⇒ 00:06:57.440 Zoran Selinger: our, query parameters that we were looking for, like UTMs and click IDs and all the other stuff.
69 00:06:57.440 ⇒ 00:07:00.689 Henry Zhao: Okay, so I’ll just do a left join then. That way I’ll get the nulls.
70 00:07:01.160 ⇒ 00:07:02.230 Zoran Selinger: Yeah, yeah.
71 00:07:02.450 ⇒ 00:07:03.760 Henry Zhao: Okay, so that should work, right?
72 00:07:04.060 ⇒ 00:07:04.740 Zoran Selinger: Yes.
73 00:07:05.900 ⇒ 00:07:14.140 Henry Zhao: Okay, and then afterwards, I would then do a third piece, which is, like, grouping by person. So if I group by person, can I just use the user ID?
74 00:07:15.800 ⇒ 00:07:33.669 Henry Zhao: Right, so this is by transaction, so this is, like, the first and last UTM by transaction. Next, what I would do is group it by person, so I’d just say, for each user ID, get rid of the transaction ID, but just look at the truly first timestamp of each, and last timestamp of each. First, last… so the first, first campaign, and the first, last.
75 00:07:33.940 ⇒ 00:07:35.539 Henry Zhao: As, like, the person one.
76 00:07:37.700 ⇒ 00:07:38.650 Henry Zhao: Make sense?
77 00:07:39.120 ⇒ 00:07:39.950 Zoran Selinger: Yes.
78 00:07:39.950 ⇒ 00:07:41.630 Henry Zhao: Okay, alright, that’s it then.
79 00:07:42.590 ⇒ 00:07:52.480 Zoran Selinger: Okay, excited to see the results and kind of how that looks like, kind of, in a visual form, and what we can do with it.
80 00:07:53.010 ⇒ 00:07:53.710 Henry Zhao: Yup.
81 00:07:54.200 ⇒ 00:07:56.230 Zoran Selinger: Okay, thank you, thank you.
82 00:07:56.230 ⇒ 00:07:56.950 Henry Zhao: Thank you, take care.
83 00:07:56.950 ⇒ 00:07:58.060 Zoran Selinger: Talk soon. Yeah, bye-bye.