display_ads (view)

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

9 rows where an ad_target was interests:African—American culture

This data as JSON, CSV (advanced)

id	img	targeting	impressions	clicks	url	text	spend_usd	spend_amount	spend_currency	created	ended
1087		location:United States, interests:Black nationalism, interests:Pan—Africanism, interests:African—American culture, interests:African-American Civil Rights Movement (1954—68), interests:African-American history, interests:Black Consciousness Movement, interests:Martin Luther King I”, interests:Black (Color), age:16 - 65+, language:English (UK), language:English (US), placements:News Feed on desktop computers, placements:News Feed on mobile devices	12055	1781	https://www.facebook.com/Blacktivist-128371547505950/	Positive young black men serving this country will not be on any news outlet. That‘s why we decided to post it here.	6.4	400.00	RUB	2016-10-18T06:57:44-07:00	2016-10-19T06:57:43-07:00
1136		location:United States, interests:Black nationalism, interests:Pan—Africanism, interests:African—American culture, interests:African-American Civil Rights Movement (1954—68), interests:African-American history, interests:Black Consciousness Movement, interests:Martin Luther King I”, interests:Black (Color), age:16 - 65+, language:English (UK), language:English (US), placements:News Feed on desktop computers, placements:News Feed on mobile devices	9746	1415	https://www.facebook.com/Blacktivist-128371547505950/	Share this and make it go viral. Media won‘t share this because positive Black male images it‘s not okay to display in America.	6.4	400.00	RUB	2016-10-18T06:58:45-07:00	2016-10-19T06:58:44-07:00
1140		location:United States, interests:Black nationalism, interests:Pan—Africanism, interests:African—American culture, interests:African-American Civil Rights Movement (1954—68), interests:African-American history, interests:Black Consciousness Movement, interests:Martin Luther King I”, interests:Black (Color), age:16 - 65+, language:English (UK), language:English (US), placements:News Feed on desktop computers, placements:News Feed on mobile devices	11817	2744	https://www.facebook.com/BIacktivist-128371547505950/	A black man employed by Comcast being given a ticket for doing his job. This man was working on a house when a bystander reported him to the cops. When police showed up, they drew the gun and told the man to put his hands up because he was accused of walking around the property, looking through windows and searching for a way to steal from home. Despite the fact that the man wore a red Comcast shirt and was standing next to his white Comcast truck searching for some wiring to do his job, the police gave him a ticket for literally doing hisjob. Welcome to the United States of Racism.	6.4	400.00	RUB	2016-10-18T06:44:30-07:00	2016-10-19T06:44:29-07:00
2440		location:united_states:Alabama, location:united_states:Arizona, location:united_states:California, location:united_states:Florida, location:united_states:Georgia, location:united_states:Mississippi, location:united_states:New Mexico, location:united_states:New York, location:united_states:Oklahoma, location:united_states:Texas, location:united_states:Washington, interests:Freckle, interests:Baltimore, interests:Color, interests:African—American culture, interests:Visual perception, interests:Black (Color), age:13 - 40, placements:News Feed on desktop computers, placements:News Feed on mobile devices	2318	146	https://www.facebook.com/BIack-Edification-1772583676310336/	Many of us are poor and struggling due to systemic racisml! 80 fight for something! IT IS OUR DUTY!! WE HAVE NOTHING TO LOSE BUT OUR CHAINS!	1.18	73.75	RUB	2016-07-19T14:16:10-07:00	2016-07-26T14:16:09-07:00
2517		location_living_in:United States, interests:Martin Luther King, interests:Police Brutality is a Crime, interests:Police misconduct, interests:African—American culture, interests:African—American Civil Rights Movement (1954—68), interests:African-American history, interests:Black Consciousness Movement, interests:Police brutality in the United States, interests:Stop Police Brutality, interests:Malcolm X, interests:Black (Color), age:18 - 65+, placements:News Feed on desktop computers, placements:News Feed on mobile devices	0	0	https://www.facebook.com/blackmattersus.mvmnt/	More and more unarmed black people are killed by police. And the cases of abuse and excessive force are simply swept under the rug. #ThatsWhyWeProtest https:l/www.facebook.comlevents/662307087278604/	0.0	0.00	USD	2016-10-21T05:34:54-07:00	2016-10-24T05:34:53-07:00
3124		location_living_in:United States, interests:Martin Luther King, interests:African—American culture, interests:African—American Civil Rights Movement (1954—68), interests:African-American history, interests:Black Consciousness Movement, interests:Malcolm X, age:18 - 65+, placements:News Feed on desktop computers, placements:News Feed on mobile devices	1006	49	https://www.facebook.com/Woke-B|acks-294234600956431/	Have seen good ones, but this is so far, in my opinion, the best #mannequinchallenge. Hands down!	1.104	69.00	RUB	2016-11-11T03:56:47-08:00	2016-11-12T03:56:47-08:00
3220		location_living_in:United States, interests:Martin Luther King, interests:BLACK BUSINESS GLOBAL, interests:Black Business Works, interests:African—American culture, interests:African—American Civil Rights Movement (1954—68), interests:African-American history, interests:Black Business Builders Club, interests:Malcolm X, age:18 - 65+, placements:News Feed on desktop computers, placements:Instagram Feed, placements:News Feed on mobile devices, interest_expansion:On	939	6	https://www.facebook.com/Black4BIaCk-1268698556482143/	—thank you for uplifting Black Kings!	3.19776	199.86	RUB	2016-12-05T06:26:41-08:00	2016-12-06T06:26:41-08:00
3224		location_living_in:United States, interests:Martin Luther King, interests:BLACK BUSINESS GLOBAL, interests:Black Business Works, interests:African—American culture, interests:African—American Civil Rights Movement (1954—68), interests:African-American history, interests:Black Business Builders Club, interests:Malcolm X, age:18 - 65+, placements:News Feed on desktop computers, placements:Instagram Feed, placements:News Feed on mobile devices, interest_expansion:On	1036	3	https://www.facebook.com/Black4BIaCk-1268698556482143/		3.2	200.00	RUB	2016-12-05T06:26:06-08:00	2016-12-06T06:26:06-08:00
3228		location_living_in:United States, interests:Martin Luther King, interests:BLACK BUSINESS GLOBAL, interests:Black Business Works, interests:African—American culture, interests:African—American Civil Rights Movement (1954—68), interests:African-American history, interests:Black Business Builders Club, interests:Malcolm X, age:18 - 65+, placements:News Feed on desktop computers, placements:Instagram Feed, placements:News Feed on mobile devices, interest_expansion:On	1738	7	https://www.facebook.com/Black4BIaCk-1268698556482143/	80 proud of my sisters and brothers! #BlackDoctorsMatter	4.79952	299.97	RUB	2016-12-05T06:25:07-08:00	2016-12-06T06:25:07-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