/
russian-ads-metadata.yaml
97 lines (84 loc) · 4.07 KB
/
russian-ads-metadata.yaml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
title: Russian Internet Research Agency Facebook Ads
source: simonw/russian-ira-facebook-ads-datasette
source_url: https://github.com/simonw/russian-ira-facebook-ads-datasette
extra_css_urls:
- "/static/russian-ads.css"
description_html: |-
<div style="max-width: 700px">
<p>The House Intelligence Committee released 3,517 Facebook ads that were
reported to have been bought by the Russian Internet Research Agency as a
set of redacted PDF files.</p>
<p>This tool lets you search and browse through those ads.</p>
<form action="/russian-ads/display_ads" method="GET">
<p><input name="_search" type="search"> <input type="submit" value="Search"></p>
</form>
<p>Example searches: <a href="/russian-ads/display_ads?_search=black+lives+matter">black lives matter</a>,
<a href="/russian-ads/display_ads?_search=patriot">patriot</a></p>
<p>You can <a href="/russian-ads/display_ads?_sort_desc=spend_usd">sort the adverts by $USD equivalent spent</a>
to see where the most money went. Conversion from rubles is calculated at
0.016 dollar per ruble.</p>
<p>One of the most interesting aspects of this data is the Facebook targeting
options that were used. You can <a href="/russian-ads/top_targets">explore the
most frequent targets</a> and see which adverts were assigned based on which
targeting options.</p>
<p>Interested in the total spend? <a href="/russian-ads/total-spend">Here it is, broken down by currency</a>.</p>
<p>Full background on this project can be found on my blog: <a href="https://simonwillison.net/2018/Aug/6/russian-facebook-ads/">Analyzing US Election Russian Facebook Ads</a></p>
<p>A big thanks to Ed Summers who <a href="https://github.com/umd-mith/irads">built the code</a>
that extracts raw information and metadata from the PDFs released by Congress.</p>
<p>The code by Simon Willison that loads that data into this Datasette instance is
<a href="https://github.com/simonw/russian-ira-facebook-ads-datasette">available on GitHub</a>.</p>
<p>Advanced feature: search by Python compatible regular expression:</p>
<form action="/russian-ads/display_ads" method="GET">
<p><input name="_regexp" type="search"> <input type="submit" value="Search"></p>
</form>
</div>
databases:
russian-ads:
tables:
display_ads:
sortable_columns:
- impressions
- clicks
- spend_usd
- created
- ended
fts_table: ads_fts
fts_pk: id
queries:
total-spend: |-
select
spend_currency, sum(spend_usd), sum(spend_amount),
count(*) as num_ads
from display_ads
group by spend_currency
faceted-targets: |-
select
target_id,
targets.name,
count(*) as n,
json_object(
"href", "/russian-ads/faceted-targets?targets=" || urllib_quote_plus(
json_insert(:targets, '$[' || json_array_length(:targets) || ']', target_id)
),
"label", json_insert(:targets, '$[' || json_array_length(:targets) || ']', target_id)
) as apply_this_facet,
json_object(
"href", "/russian-ads/display_ads?_targets_json=" || urllib_quote_plus(
json_insert(:targets, '$[' || json_array_length(:targets) || ']', target_id)
),
"label", "See " || count(*) || " ads matching " || json_insert(:targets, '$[' || json_array_length(:targets) || ']', target_id)
) as browse_these_ads
from ad_targets
join targets on ad_targets.target_id = targets.id
where
json_array_length(:targets) == 0 or
ad_id in (
select ad_id
from "ad_targets"
where "ad_targets".target_id in (select value from json_each(:targets))
group by "ad_targets".ad_id
having count(distinct "ad_targets".target_id) = json_array_length(:targets)
)
and target_id not in (select value from json_each(:targets))
group by
target_id order by n desc