display_ads (view)

Data source: simonw/russian-ira-facebook-ads-datasette

3 rows where an ad_target was people_who_match:interests:Amenity sorted by created

This data as JSON, CSV (advanced)

id	img	targeting	impressions	clicks	url	text	spend_usd	spend_amount	spend_currency	created ▼	ended
2893		location_living_in:United States, age:18 - 65+, placements:News Feed on desktop computers, placements:News Feed on mobile devices, people_who_match:interests:Amenity, people_who_match:interests:Jesus Daily, and_must_also_match:interests:Jesus	0	0	https://www.facebook.com/Army-of-Jesus-1195795607160174/	At the Last Supper Jesus says: He said to them, “But now if you have a purse, take it, and also a bag; and if you don’t have a sword, sell your Cloak and buy one.” — Luke 22:36 What does that mean? Jesus always stood for peace and non-violence. But enemies often issued threats towards Him and His disciples. There's always was a risk for them to be arrested and subjected to a great violence, and be followed with rage and persecution; so that they might seem to stand in need of swords to defend them: the phrase is expressive of the danger they would be exposed to, and of their need of protection. And we, American Christians, should remember about it. Sometimes you have to arm yourself to protect your loved ones and friends.	0.0	0.00	USD	2016-10-17T02:14:28-07:00	2016-10-13T03:18:15-07:00
2888		location_living_in:United States, age:18 - 65+, placements:News Feed on desktop computers, placements:News Feed on mobile devices, placements:Right column on desktop computers, people_who_match:interests:Amenity, people_who_match:interests:Jesus Daily, and_must_also_match:interests:Jesus	277317	20426	https://www.facebook.com/Army-of-Jesus-1195795607160174/	Let's stand united to defend our faith as an army of Jesus!	562.056	35128.50	RUB	2016-10-17T02:17:19-07:00
2889		location_living_in:United States, age:18 - 65+, placements:News Feed on desktop computers, placements:Right column on desktop computers, people_who_match:interests:Amenity, people_who_match:interests:Jesus Daily, and_must_also_match:interests:Jesus	131913	5449	https://www.facebook.com/Army-of-Jesus-1195795607160174/	Let's stand united to defend our faith as an army of Jesus! Army Of Jesus	383.1	23943.75	RUB	2016-12-08T00:30:32-08:00

CREATE VIEW display_ads AS 
        select ads.id,
            case when image is not null then
                json_object("img_src", "https://raw.githubusercontent.com/umd-mith/irads/master/site/" || image, "width", 200)
            else
                "no image"
            end as img,
            json_group_array(
                json_object(
                    "label", targets.name,
                    "href", "/russian-ads/display_ads?_target="
                        || urllib_quote_plus(targets.id)
                )
            ) as targeting,
            ads.impressions, ads.clicks, ads.url, ads.text,
            cast(case
                when ads.spend_currency == "RUB" then ads.spend_amount * 0.016
                else ads.spend_amount
            end as float) as spend_usd,
            ads.spend_amount, ads.spend_currency,
            ads.created, ads.ended
        from ads
            join ad_targets on ads.id = ad_targets.ad_id
            join targets on ad_targets.target_id = targets.id
        group by ads.id
        order by ads.id